MySQL慢查詢?nèi)罩究偨Y(jié)
慢查詢?nèi)罩靖拍?/strong>
MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，它用來(lái)記錄在MySQL中響應(yīng)時(shí)間超過(guò)閥值的語(yǔ)句，具體指運(yùn)行時(shí)間超過(guò)long_query_time值的SQL，則會(huì)被記錄到慢查詢?nèi)罩局?。long_query_time的默認(rèn)值為10，意思是運(yùn)行10S以上的語(yǔ)句。默認(rèn)情況下，Mysql數(shù)據(jù)庫(kù)并不啟動(dòng)慢查詢?nèi)罩?，需要我們手?dòng)來(lái)設(shè)置這個(gè)參數(shù)，當(dāng)然，如果不是調(diào)優(yōu)需要的話，一般不建議啟動(dòng)該參數(shù)，因?yàn)殚_(kāi)啟慢查詢?nèi)罩緯?huì)或多或少帶來(lái)一定的性能影響。慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，也支持將日志記錄寫入?shù)據(jù)庫(kù)表。
官方文檔，關(guān)于慢查詢的日志介紹如下(部分資料，具體參考官方相關(guān)鏈接)：
The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.
By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed usinglog_slow_admin_statements and log_queries_not_using_indexes, as described later.
慢查詢?nèi)罩鞠嚓P(guān)參數(shù)
MySQL 慢查詢的相關(guān)參數(shù)解釋：
slow_query_log ：是否開(kāi)啟慢查詢?nèi)罩荆?表示開(kāi)啟，0表示關(guān)閉。
log-slow-queries ：舊版(5.6以下版本)MySQL數(shù)據(jù)庫(kù)慢查詢?nèi)罩敬鎯?chǔ)路徑?？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會(huì)默認(rèn)給一個(gè)缺省的文件host_name-slow.log
slow-query-log-file：新版(5.6及以上版本)MySQL數(shù)據(jù)庫(kù)慢查詢?nèi)罩敬鎯?chǔ)路徑。可以不設(shè)置該參數(shù)，系統(tǒng)則會(huì)默認(rèn)給一個(gè)缺省的文件host_name-slow.log
long_query_time ：慢查詢閾值，當(dāng)查詢時(shí)間多于設(shè)定的閾值時(shí)，記錄日志。
log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢?nèi)罩局?可選項(xiàng))。
log_output：日志存儲(chǔ)方式。log_output=’FILE’表示將日志存入文件，默認(rèn)值是’FILE’。log_output=’TABLE’表示將日志存入數(shù)據(jù)庫(kù)，這樣日志信息就會(huì)被寫入到mysql.slow_log表中。MySQL數(shù)據(jù)庫(kù)支持同時(shí)兩種日志存儲(chǔ)方式，配置的時(shí)候以逗號(hào)隔開(kāi)即可，如：log_output=’FILE,TABLE’。日志記錄到系統(tǒng)的專用日志表中，要比記錄到文件耗費(fèi)更多的系統(tǒng)資源，因此對(duì)于需要啟用慢查詢?nèi)罩?，又需要能夠獲得更高的系統(tǒng)性能，那么建議優(yōu)先記錄到文件。
慢查詢?nèi)罩九渲?/strong>
默認(rèn)情況下slow_query_log的值為OFF，表示慢查詢?nèi)罩臼墙玫?，可以通過(guò)設(shè)置slow_query_log的值來(lái)開(kāi)啟，如下所示：
- mysql> show variables like '%slow_query_log%';
- +---------------------+-----------------------------------------------+
- | Variable_name | Value |
- +---------------------+-----------------------------------------------+
- | slow_query_log | OFF |
- | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
- +---------------------+-----------------------------------------------+
- 2 rows in set (0.00 sec)
- mysql> set global slow_query_log=1;
- Query OK, 0 rows affected (0.09 sec)
- mysql> show variables like '%slow_query_log%';
- +---------------------+-----------------------------------------------+
- | Variable_name | Value |
- +---------------------+-----------------------------------------------+
- | slow_query_log | ON |
- | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
- +---------------------+-----------------------------------------------+
- 2 rows in set (0.00 sec)
- mysql>
使用set global slow_query_log=1開(kāi)啟了慢查詢?nèi)罩局粚?duì)當(dāng)前數(shù)據(jù)庫(kù)生效，如果MySQL重啟后則會(huì)失效。如果要永久生效，就必須修改配置文件my.cnf(其它系統(tǒng)變量也是如此)。例如如下所示：
- mysql> show variables like 'slow_query%';
- +---------------------+-----------------------------------------------+
- | Variable_name | Value |
- +---------------------+-----------------------------------------------+
- | slow_query_log | OFF |
- | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
- +---------------------+-----------------------------------------------+
- 2 rows in set (0.01 sec)
- mysql>
修改my.cnf文件，增加或修改參數(shù)slow_query_log 和slow_query_log_file后，然后重啟MySQL服務(wù)器，如下所示
- slow_query_log =1
- slow_query_log_file=/tmp/mysql_slow.log
- mysql> show variables like 'slow_query%';
- +---------------------+---------------------+
- | Variable_name | Value |
- +---------------------+---------------------+
- | slow_query_log | ON |
- | slow_query_log_file | /tmp/mysql_slow.log |
- +---------------------+---------------------+
- 2 rows in set (0.00 sec)
- mysql>
關(guān)于慢查詢的參數(shù)slow_query_log_file ，它指定慢查詢?nèi)罩疚募拇娣怕窂?，系統(tǒng)默認(rèn)會(huì)給一個(gè)缺省的文件host_name-slow.log(如果沒(méi)有指定參數(shù)slow_query_log_file的話)
- mysql> show variables like 'slow_query_log_file';
- +---------------------+-----------------------------------------------+
- | Variable_name | Value |
- +---------------------+-----------------------------------------------+
- | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
- +---------------------+-----------------------------------------------+
- 1 row in set (0.00 sec)
那么開(kāi)啟了慢查詢?nèi)罩竞螅裁礃拥腟QL才會(huì)記錄到慢查詢?nèi)罩纠锩婺? 這個(gè)是由參數(shù)long_query_time控制，默認(rèn)情況下long_query_time的值為10秒，可以使用命令修改，也可以在my.cnf參數(shù)里面修改。關(guān)于運(yùn)行時(shí)間正好等于long_query_time的情況，并不會(huì)被記錄下來(lái)。也就是說(shuō)，在mysql源碼里是判斷大于long_query_time，而非大于等于。從MySQL 5.1開(kāi)始，long_query_time開(kāi)始以微秒記錄SQL語(yǔ)句運(yùn)行時(shí)間，之前僅用秒為單位記錄。如果記錄到表里面，只會(huì)記錄整數(shù)部分，不會(huì)記錄微秒部分。
- mysql> show variables like 'long_query_time%';
- +-----------------+-----------+
- | Variable_name | Value |
- +-----------------+-----------+
- | long_query_time | 10.000000 |
- +-----------------+-----------+
- 1 row in set (0.00 sec)
- mysql> set global long_query_time=4;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show variables like 'long_query_time';
- +-----------------+-----------+
- | Variable_name | Value |
- +-----------------+-----------+
- | long_query_time | 10.000000 |
- +-----------------+-----------+
- 1 row in set (0.00 sec)
如上所示，我修改了變量long_query_time，但是查詢變量long_query_time的值還是10，難道沒(méi)有修改到呢?注意：使用命令 set global long_query_time=4修改后，需要重新連接或新開(kāi)一個(gè)會(huì)話才能看到修改值。你用show variables like ‘long_query_time’查看是當(dāng)前會(huì)話的變量值，你也可以不用重新連接會(huì)話，而是用show global variables like ‘long_query_time’; 如下所示：
在MySQL里面執(zhí)行下面SQL語(yǔ)句，然后我們?nèi)z查對(duì)應(yīng)的慢查詢?nèi)罩荆蜁?huì)發(fā)現(xiàn)類似下面這樣的信息。
- mysql> select sleep(3);
- +----------+
- | sleep(3) |
- +----------+
- | 0 |
- +----------+
- 1 row in set (3.00 sec)
- [root@DB-Server ~]# more /tmp/mysql_slow.log
- /usr/sbin/mysqld, Version: 5.6.20-enterprise-commercial-advanced-log (MySQL Enterprise Server - Advanced Edition(Commercial)). started with:
- Tcp port: 0 Unix socket: (null)
- Time Id Command Argument
- /usr/sbin/mysqld, Version: 5.6.20-enterprise-commercial-advanced-log (MySQL Enterprise Server - Advanced Edition(Commercial)). started with:
- Tcp port: 0 Unix socket: (null)
- Time Id Command Argument
- # Time: 160616 17:24:35
- # User@Host: root[root] @ localhost [] Id: 5
- # Query_time: 3.002615 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
- SET timestamp=1466069075;
- select sleep(3);
log_output 參數(shù)是指定日志的存儲(chǔ)方式。log_output=’FILE’表示將日志存入文件，默認(rèn)值是’FILE’。log_output=’TABLE’表示將日志存入數(shù)據(jù)庫(kù)，這樣日志信息就會(huì)被寫入到mysql.slow_log表中。MySQL數(shù)據(jù)庫(kù)支持同時(shí)兩種日志存儲(chǔ)方式，配置的時(shí)候以逗號(hào)隔開(kāi)即可，如：log_output=’FILE,TABLE’。日志記錄到系統(tǒng)的專用日志表中，要比記錄到文件耗費(fèi)更多的系統(tǒng)資源，因此對(duì)于需要啟用慢查詢?nèi)罩?，又需要能夠獲得更高的系統(tǒng)性能，那么建議優(yōu)先記錄到文件。
- mysql> show variables like '%log_output%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_output | FILE |
- +---------------+-------+
- 1 row in set (0.00 sec)
- mysql> set global log_output='TABLE';
- Query OK, 0 rows affected (0.00 sec)
- mysql> show variables like '%log_output%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_output | TABLE |
- +---------------+-------+
- 1 row in set (0.00 sec)
- mysql> select sleep(5) ;
- +----------+
- | sleep(5) |
- +----------+
- | 0 |
- +----------+
- 1 row in set (5.00 sec)
- mysql>
- mysql> select * from mysql.slow_log;
- +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
- | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id| server_id | sql_text | thread_id |
- +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
- | 2016-06-16 17:37:53 | root[root] @ localhost [] | 00:00:03 | 00:00:00 | 1 | 0 | | 0 | 0 | 1 |select sleep(3) | 5 |
- | 2016-06-16 21:45:23 | root[root] @ localhost [] | 00:00:05 | 00:00:00 | 1 | 0 | | 0 | 0 | 1 |select sleep(5) | 2 |
- +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
- 2 rows in set (0.00 sec)
- mysql>
系統(tǒng)變量log-queries-not-using-indexes：未使用索引的查詢也被記錄到慢查詢?nèi)罩局?可選項(xiàng))。如果調(diào)優(yōu)的話，建議開(kāi)啟這個(gè)選項(xiàng)。另外，開(kāi)啟了這個(gè)參數(shù)，其實(shí)使用full index scan的sql也會(huì)被記錄到慢查詢?nèi)罩尽?/p>
This option does not necessarily mean that no index is used. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.
- mysql> show variables like 'log_queries_not_using_indexes';
- +-------------------------------+-------+
- | Variable_name | Value |
- +-------------------------------+-------+
- | log_queries_not_using_indexes | OFF |
- +-------------------------------+-------+
- 1 row in set (0.00 sec)
- mysql> set global log_queries_not_using_indexes=1;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show variables like 'log_queries_not_using_indexes';
- +-------------------------------+-------+
- | Variable_name | Value |
- +-------------------------------+-------+
- | log_queries_not_using_indexes | ON |
- +-------------------------------+-------+
- 1 row in set (0.00 sec)
- mysql>
系統(tǒng)變量log_slow_admin_statements表示是否將慢管理語(yǔ)句例如ANALYZE TABLE和ALTER TABLE等記入慢查詢?nèi)罩?/p>
- mysql> show variables like 'log_slow_admin_statements';
- +---------------------------+-------+
- | Variable_name | Value |
- +---------------------------+-------+
- | log_slow_admin_statements | OFF |
- +---------------------------+-------+
- 1 row in set (0.00 sec)
- mysql>
系統(tǒng)變量log_slow_slave_statements 表示
By default, a replication slave does not write replicated queries to the slow query log. To change this, use thelog_slow_slave_statements system variable.
When the slow query log is enabled, this variable enables logging for queries that have taken more than long_query_time seconds to execute on the slave. This variable was added in MySQL 5.7.1. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START SLAVE statements.
參數(shù)–log-short-format
The server writes less information to the slow query log if you use the –log-short-format option.
另外，如果你想查詢有多少條慢查詢記錄，可以使用系統(tǒng)變量。
- mysql> show global status like '%Slow_queries%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Slow_queries | 2104 |
- +---------------+-------+
- 1 row in set (0.00 sec)
- mysql>
日志分析工具mysqldumpslow
在生產(chǎn)環(huán)境中，如果要手工分析日志，查找、分析SQL，顯然是個(gè)體力活，MySQL提供了日志分析工具mysqldumpslow
查看mysqldumpslow的幫助信息：
- [root@DB-Server ~]# mysqldumpslow --help
- Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
- Parse and summarize the MySQL slow query log. Options are
- --verbose verbose
- --debug debug
- --help write this text to standard output
- -v verbose
- -d debug
- -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
- al: average lock time
- ar: average rows sent
- at: average query time
- c: count
- l: lock time
- r: rows sent
- t: query time
- -r reverse the sort order (largest last instead of first)
- -t NUM just show the top n queries
- -a don't abstract all numbers to N and strings to 'S'
- -n NUM abstract numbers with at least n digits within names
- -g PATTERN grep: only consider stmts that include this string
- -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
- default is '*', i.e. match all
- -i NAME name of server instance (if using mysql.server startup script)
- -l don't subtract lock time from total time
-s, 是表示按照何種方式排序，
c: 訪問(wèn)計(jì)數(shù)
l: 鎖定時(shí)間
r: 返回記錄
t: 查詢時(shí)間
al:平均鎖定時(shí)間
ar:平均返回記錄數(shù)
at:平均查詢時(shí)間
-t, 是top n的意思，即為返回前面多少條的數(shù)據(jù);
-g, 后邊可以寫一個(gè)正則匹配模式，大小寫不敏感的;
比如
得到返回記錄集最多的10個(gè)SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到訪問(wèn)次數(shù)最多的10個(gè)SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建議在使用這些命令時(shí)結(jié)合 | 和more 使用 ，否則有可能出現(xiàn)刷屏的情況。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
