說明

  • Mysql的查詢訊日誌是Mysql提供的一種日誌記錄，它用來記錄在Mysql中響應時間超過閾值的語句
  • 具體指運行時間超過long_query_time值得SQL，則會被記錄到慢查詢日誌中。long_query_time的默認為10，意識是運行10秒以上的語句。
  • 由它來看那些SQL語句超出了我們的最大忍耐值，比如一條SQL執行超過了5秒，我們就算慢查詢，我們就可以結合Explain進行分析。

查看是否開啟及如何開啟

默認Mysql沒有開啟慢查詢，需要我們說動設置這個參數。當然，如果不是調優需要的話，一般不建議開啟該參數，因為開啟慢查詢日誌會或多或少帶來一定的性能影響。慢查詢日誌支持將日誌寫入文件。

進入mysql終端查看是否開啟（我這裡都是自己改過的）

mysql> show variables like %slow_query_log%;
+---------------------+----------------------------+
| Variable_name | Value |
+---------------------+----------------------------+
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/mysql-slow.log |
+---------------------+----------------------------+

上面查詢結果第一行，這裡是開啟的，第二行是默認查詢路徑文件名。

mysql> set global slow_query_log = 0|1; //設置開啟或者關閉，0為關閉，1為開啟

如果使用set global slow_query_log 命令開啟慢查詢日誌，只對當前資料庫生效，如果Mysql重啟後則會失效。

補充：如果通過終端命令設定的話，再查詢是看不到修改結果的，需要新開啟一個窗口查看即可。

如果要永久生效，必須修改my.cnf配置文件(其他系統變數也是如此)，

修改my.cnf文件，[mysqld]下增加和修改參數slow_query_log和slow_query_log_file後，然後重啟MySQL伺服器，也即將如下兩行配置進my.cnf文件

slow_query_log = 1

slow_query_log_file = /data/mysql/mysql-slow.log

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log

關於慢查詢的參數slow_query_log_file，它指定慢查詢日誌文件的存放路徑，系統默認會給一個預設的host_name_show.log（如果沒有指定參數slow_query_log_file的話）。

那麼開啟了慢日誌後，怎麼樣的SQL才會記錄到慢查詢當中呢？

  • 這個是由參數long_query_time控制，默認情況下long_query_time的值是10秒。
  • 命令SHOW VARIABLES LIKE long_query_time;

mysql> show variables like long_query_time;
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 10.00000 |
+-----------------+----------+

可以使用命令修改，也可以在my.cnf參數裡面修改，修改完記得習慣性重啟服務。還是那句話，不建議生產環境開啟。

mysql> show variables like long_query_time;
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

假如SQL運行時間剛好等於long_query_time的情況，並不會被記錄下來，也就是說，在Mysql源碼裏是判斷大於long_query_time，而非大於等於。

Case

  • 查看當前多少秒算慢：SHOW VARIABLES LIKE long_query_time%;
  • 設置慢的闕值時間：set global long_query_time=3;
  • 為什麼設置後看不出變化：
  1. 需要重新連接或新開一個回話才能看到修改值； SHOW VARIABLES LIKE long_query_time%;
  2. SHOW GLOBAL VARIABLES LIKE long_query_time%;
  • 記錄慢SQL並後續分析：select sleep(4);

查看慢查詢日誌文件

# cat /data/mysql/mysql-slow.log
#
# select sleep(5);
# Time: 2018-01-20T18:10:24.443517Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 5.000564 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test_db; #使用的資料庫
SET timestamp=1524276624; #時間戳
select sleep(5); #問題SQL

以上信息包括實際查詢時間5.000564秒，時間戳timestamp=1524276624，還有庫test_db，出問題的SQL。

  • 查看當前系統中多少條滿記錄：show global status like %Slow_queries%;

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

可以作為系統健康檢查度來查詢。(什麼意思：如果系統裡面充滿了大量的慢SQL，都超過闕值，這時系統就需要及時優化)

配置版

show_query_log = 1;
show_query_log_file=/var/lib/mysql/mysql_slow.log
log_query_time=3;
log_output=FILE

mysqldumpslow用法講解

  • 在生產環境中，如果要手動分析日誌，查找、分析SQL，顯然是一個體力活，MySQL提供了日誌分析工具mysqldumpslow。

上面測試的慢查詢SQL只有一條，假如在實際的生產環境中，慢查詢SQL遠遠高於測試的數量，十幾條甚至幾十條，假如幾條慢查詢出現的頻率很高，我們能做到根據輕重優先順序來分析並排除那是不是更好？那麼就用到了mysqldumpslow

[root@lig mysql]# 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 dont 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 dont subtract lock time from total time

s:是表示按照何種方式排序

c:訪問次數

i：鎖定時間

r：返回記錄

t：查詢時間

al:平均鎖定時間

ar：平均返回記錄數

at：平均查詢時間

t：即為返回前面多少條數據

g：後邊搭配一個正則匹配模式，大小寫不敏感

mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log //得到返回記錄集最多的10個SQL
mysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log //得到訪問次數最多的10個SQL
mysqldumpslow -s t -t 10 -g "left join" /data/mysql/mysql-slow.log //得到按照時間排序的前10條裡面含有做了連接的查詢SQL
mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log | more //另外建議在使用這些命令時結合|和more使用，否則有可能出現爆屏情況

OK~既然學習了，就運用到實際工作中吧~


推薦閱讀：
相關文章