MySQL慢查詢

MySQL的慢查詢，全名是慢查詢日誌，是MySQL提供的一種日誌記錄，用來記錄在MySQL中響應時間超過閥值的語句。

MySQL的慢查詢，全名是慢查詢日誌，是MySQL提供的一種日誌記錄，用來記錄在MySQL中響應時間超過閥值的語句。

具體環境中，執行時間超過long_query_time值的SQL語句，則會被記錄到慢查詢日誌中。

long_query_time的預設值為10，意思是記錄執行10秒以上的語句。

預設情況下，MySQL資料庫並不啟動慢查詢日誌，需要手動來設定這個引數。

當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案和資料庫表。

MySQL 慢查詢的相關引數解釋：

slow_query_log：是否開啟慢查詢日誌，1表示開啟，0表示關閉。

log-slow-queries ：舊版（5.6以下版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該引數，系統則會預設給一個預設的檔案host_name-slow.log

slow-query-log-file：新版（5.6及以上版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該引數，系統則會預設給一個預設的檔案host_name-slow.log

long_query_time：慢查詢閾值，當查詢時間多於設定的閾值時，記錄日誌。

log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。

log_output：日誌儲存方式。log_output='FILE'表示將日誌存入檔案，預設值是'FILE'。log_output='TABLE'表示將日誌存入資料庫。

預設情況下slow_query_log的值為OFF，表示慢查詢日誌是禁用的，可以透過設定slow_query_log的值來開啟，如下所示：

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)

使用set global slow_query_log=1開啟了慢查詢日誌只對當前資料庫生效，MySQL重啟後則會失效。

如果要永久生效，就必須修改配置檔案my.cnf（其它系統變數也是如此）。

my.cnf要增加或修改引數slow_query_log 和slow_query_log_file，如下所示

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log

然後重啟MySQL伺服器。

這個引數用於指定慢查詢日誌的存放路徑，預設情況是host_name-slow.log檔案，

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)

開啟了慢查詢日誌後，什麼樣的SQL才會記錄到慢查詢日誌裡面呢？

這個是由引數long_query_time控制，預設情況下long_query_time的值為10秒，可以使用 修改，也可以在my.cnf引數裡面修改。

關於執行時間正好等於long_query_time的情況，並不會被記錄下來。

也就是說，在mysql原始碼裡是判斷大於long_query_time，而非大於等於。

從MySQL 5.1開始，long_query_time開始以微秒記錄SQL語句執行時間，之前僅用秒為單位記錄。

如果記錄到表裡面，只會記錄整數部分，不會記錄微秒部分。

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，難道沒有修改到呢？

注意：使用  set global long_query_time=4修改後，需要重新連線或新開一個會話才能看到修改值。

用 show variables like 'long_query_time' 檢視是當前會話的變數值。

也可以不用重新連線會話，而是用  show global variables like 'long_query_time';。

log_output引數指定日誌的儲存方式。

log_output='FILE'表示將日誌存入檔案，預設值也是'FILE'。

log_output='TABLE'表示將日誌存入資料庫，這樣日誌資訊就會被寫入到mysql.slow_log表中。

同時也支援兩種日誌儲存方式，配置的時候以逗號隔開即可，如：log_output='FILE,TABLE'。

日誌記錄到系統的專用日誌表中，要比記錄到檔案耗費更多的系統資源。

因此對於需要啟用慢查詢日誌，又需要能夠獲得更高的系統效能，那麼建議優先記錄到檔案。

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)

該系統變數指定未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。

如果調優的話，建議開啟這個選項。

另外，開啟了這個引數，其實使用full index scan的SQL也會被記錄到慢查詢日誌。

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)
3.6 log_slow_admin_statements

這個系統變數表示，是否將慢管理語句例如ANALYZE TABLE和ALTER TABLE等記入慢查詢日誌。

mysql> show variables like 'log_slow_admin_statements';
 +---------------------------+-------+
 | Variable_name             | Value |
 +---------------------------+-------+
 | log_slow_admin_statements | OFF   |
 +---------------------------+-------+
 1 row in set (0.00 sec)
3.7 Slow_queries

如果你想查詢有多少條慢查詢記錄，可以使用Slow_queries系統變數。

mysql> show global status like '%Slow_queries%';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | Slow_queries  | 2104  |
 +---------------+-------+
 1 row in set (0.00 sec)

另外，還有log_slow_slave_statements 和 --log-short-format 引數，可到MySQL網站了解。

在生產環境中，如果要手工分析日誌，查詢、分析SQL，顯然是個體力活。

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（返回前面n條資料）
   -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

比如，得到返回記錄集最多的10個SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到訪問次數最多的10個SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照時間排序的前10條裡面含有左連線的查詢語句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現刷屏的情況。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

原文地址：