手把手教你定位線上MySQL慢查詢問題，包教包會

1. 慢查詢紀錄檔的作用

慢查詢紀錄檔預設不開啟，建議手動開啟，方便我們定位線上問題。

執行時間超過閾值的SQL會被寫入到慢查詢紀錄檔當中，這樣可以幫助我們記錄執行時間過長的SQL語句，定位線上慢SQL問題，方便我們進行SQL效能調優。

2. 慢查詢紀錄檔的設定

2.1 檢視是否開啟了慢查詢紀錄檔

show variables like 'slow_query_log';

預設是OFF，不開啟，可以手動開啟。

2.2 開啟慢查詢紀錄檔

一種方法是可以使用MySQL命令開啟：

set global slow_query_log=1;

另一種方法是修改MySQL組態檔，重新MySQL服務後，開啟。

修改組態檔my.cnf，加入下面一行命令

slow_query_log = ON

2.3 設定慢查詢紀錄檔的閾值

慢查詢紀錄檔的閾值預設是10，單位是秒。

對於線上服務來說，10秒太長了，我們可以手動修改。

一種是通過MySQL命令修改，比如修改為1秒：

set long_query_time=1;

另一種方法是修改MySQL組態檔，重新MySQL服務後，開啟。

修改組態檔my.cnf，加入下面一行命令

long_query_time = 1

2.4 修改慢查詢紀錄檔位置

使用MySQL命令檢視慢查詢紀錄檔位置：

show variables like '%slow_query_log_file%';

想要修改慢查詢紀錄檔位置，可以修改MySQL組態檔，重新MySQL服務後，開啟。

修改組態檔my.cnf，加入下面一行命令

slow_query_log_file = /usr/local/mysql/data/localhost_slow.log

2.5 記錄更多慢查詢SQL

預設情況下管理語句是不會被記錄到慢查詢紀錄檔中，管理語句包括ALTER TABLE、 ANALYZE TABLE、 CHECK TABLE、 CREATE INDEX、 DROP INDEX、 OPTIMIZE TABLE和 REPAIR TABLE等。

管理語句也是非常重要的，如果想要被記錄，可以通過MySQL命令修改：

set global log_slow_admin_statements=ON;

預設情況下，不使用索引的語句，也是不會被記錄的。

夠坑人吧！一不留神就掉坑裡了，不記錄不使用索引的語句，還要慢查詢紀錄檔幹嘛？

想要記錄不使用索引的語句，可以通過命令修改：

set global log_queries_not_using_indexes=ON;

3. 慢查詢紀錄檔的使用

手動造一條慢SQL，測試一下效果，user表中有100萬表資料：

select * from user;

然後看一下慢查詢紀錄檔檔案的內容：

cat /usr/local/mysql/data/localhost_slow.log

SQL語句和執行時間都被記錄了。

4. 分析慢查詢紀錄檔

有時候慢查詢紀錄檔較多，手動檢視起來並不是很方便，好在MySQL提供了分析慢查詢紀錄檔的工具mysqldumpslow。

常用引數有

-s: 表示按何種方式排序：
　　c: 存取次數
　　l: 鎖定時間
　　r: 返回記錄
　　t: 查詢時間
　　al: 平均鎖定時間
　　ar: 平均返回記錄數
　　at: 平均查詢時間
-t: 返回前面多少條的資料；

4.1 查詢返回結果最多的10條SQL：

mysqldumpslow -s r -t 10 /usr/local/mysql/data/localhost_slow.log

4.2 查詢耗時最長的10條SQL：

mysqldumpslow -s t -t 10 /usr/local/mysql/data/localhost_slow.log

文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。