定位SQL慢查詢_ZenDei技術網路在線

一、概念 MySQL的慢查詢（慢查詢日誌）：是MySQL提供的一種日誌記錄，用來記錄在MySQL中響應時間超過閾值的語句。具體環境中，運行時間超過long_query_time值的SQL語句，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是記錄運行10秒以上的語句。 ...

一、概念

MySQL的慢查詢（慢查詢日誌）：是MySQL提供的一種日誌記錄，用來記錄在MySQL中響應時間超過閾值的語句。
具體環境中，運行時間超過long_query_time值的SQL語句，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是記錄運行10秒以上的語句。
預設情況下，MySQL資料庫並不啟動慢查詢日誌，需要手動來設置這個參數。（如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日誌會或多或少帶來一定的性能影響）
慢查詢日誌支持將日誌記錄寫入文件和資料庫表。

二、在資料庫中設置SQL慢查詢

開啟慢查詢

方式一：

修改配置文件。在 my.ini 增加幾行：**主要是慢查詢的定義時間（超過2秒就是慢查詢），以及慢查詢log日誌記錄（ slow_query_log）

[mysqlld]
;定義查過多少秒的查詢算是慢查詢（比如2秒）
long_query_time=2

;5.0、5.1等版本配置如下選項
log_slow_queries='mysql_slow_query.log'
;5.5以上版本配置如下選項
slow_query_log='ON'
slow_query_log_file='mysql_slow_query.log'

;記錄沒有使用索引查詢語句
log-queries-not-using-indexe

MySQL和慢查詢相關的系統變數如下：

參數	含義
slow_query_log	是否啟用慢查詢日誌， ON為啟用，OFF為沒有啟用，預設為OFF
log_output	日誌輸出位置，預設為FILE，即保存為文件，若設置為TABLE，則將日誌記錄到mysql.show_log表中，支持設置多種格式
slow_query_log_file	指定慢查詢日誌文件的路徑和名字
long_query_time	執行時間超過該值才記錄到慢查詢日誌，單位為秒，預設為10

方式二：

通過MySQL資料庫開啟慢查詢。

mysql>set global slow_query_log = ON;
mysql>set global long_query_time = 3600;
mysql>set global log_queries_not_using_indexes = ON;

分析慢查詢日誌

直接分析mysql慢查詢日誌 ,利用explain關鍵字可以模擬優化器執行SQL查詢語句，來分析sql慢查詢語句
```
explain SQL語句
```
比如：

explain的參數：

輸出欄位	含義
id	執行編號，標誌select所屬的行。如果在語句中沒有子查詢或關聯查詢，只有唯一的select,每行都將顯示1。否則內層的select語句一般會順序編號，在嵌套查詢中id越大的語句越先執行。
select_type	顯示本行是簡單或複雜select。 simple：簡單子查詢，不包含子查詢和union primary：包含union或者子查詢，最外層的部分標記為primary subquery：一般子查詢中的子查詢被標記為subquery，也就是子查詢中第一個select語句 derived：派生表--該臨時表是從子查詢中派生出來的，也就是位於select列表中的查詢 union：位於union中第二個及其以後的子查詢被標記為union,第一個就被標記為primary如果是位於from中則標記為derived union result：用來從匿名臨時表裡檢索結果的select被標記為union result dependent union：顧名思義，首先需要滿足union的條件，及union中第二個以及後面的select語句，同時該語句依賴外部的查詢 dependent subquery：子查詢中第一個select語句，且該語句依賴外部的查詢
table	對應行正在訪問哪一個表，表名或者別名。 ·關聯查詢優化器會為查詢選擇關聯順序，左側深度優先 ·當form中有子查詢的時候，表名是derivedN的形式,N指向子查詢,也就是explain結果中的下一列 ·當有union result的時候，表名是union 1,2等的形式，1,2表示參與uion的query id
type	訪問類型，是較為重要的一個指標，結果值從好到壞依次是： system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL，一般來說，得保證查詢至少達到range級別，最好能達到ref 訪問類型詳解： ALL：最壞的情況，全表掃描 index：和全表掃描一樣。只是掃描表的時候按照索引次序進行而不是行。主要優點就是避免了排序，但是開銷仍然非常大。如在Extra列看到Using index,說明正在使用覆蓋索引，只掃描索引的數據，它比按索引次序全表掃描的開銷要小很多 range：範圍掃描，一個有限制的索引掃描。key列顯示使用了哪個索引。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range ref：一種索引訪問訪問，它返回所有匹配某個單個值的行。此類索引訪問只有當使用非唯一性索引或唯一性索引首碼時才會發生。這個類型跟eq_ref不同的是，它用在關聯操作只是用了索引的最左首碼，或者索引不是UNIQUE和PRIMARY KEY。ref可以用於使用=或<=>操作符的帶索引的列。 eq_ref：最多只返回一條符合條件的記錄。使用唯一性索引或主鍵查找時會發生（高效） const：當確定最多只會有一行匹配的時候，MySQL優化器會在查詢前讀取它而且只讀取一次，因此非常快。當主鍵放入where子句時，mysql把這個查詢轉為一個常量（高效） system：這是const連接類型的一種特例，表僅有一行滿足條件。 Null：意味說mysql能在優化階段分解查詢語句，在執行階段甚至用不到訪問表或索引（高效）
possible_keys	顯示查詢使用了哪些索引，表示該索引可以進行高效地查找，但是列出來的索引對於後續優化過程可能是沒有用的
key	顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len	顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL。使用的索引的長度。在不瞬時精確性的情況下，長度越短越好。
ref	顯示使用哪個列或常數與key儀器從表中選擇行。
rows	顯示MySQL認為它執行查詢時必須檢查的行數。多行之間的數據相乘可以估算要處理的行數。（預估值）
filtered	顯示通過條件過濾出的行數的百分比估計值。
Extra	Extra是EXPLAIN輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細信息，MySQL查詢優化器執行查詢的過程中對查詢計劃的重要補充信息。 Extra類型詳解： Distinct：MySQL發現第1個匹配行後停止為當前的行組合搜索更多的行。 Not exists：MySQL能夠對查詢進行LEFT JOIN優化發現1個匹配LEFT JOIN標準的行後,不再為前面的的行組合在該表內檢查更多的行。 range checked for each record (index map: #)：MySQL沒有發現好的可以使用的索,但發現如果來自前面的表的列值已知,可能部分索引可以使用。 Using hlesort：MySQL需要額外的一次傳遞以找出如何按排序順序檢索行 Using index：從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。 Using temporary：為瞭解決查詢MySQL需要創建一個臨時表來容納結果 Using where：WHERE子句用於限制哪一個行匹配下一個表或發送到客戶 Using sort_union(...), Using union(...), Using intersect(...)：這些函數說明如何為index merge聯接類型合併索引掃描。 Using index for group-by：類似於訪問表的Using index方式,Using index for group-by表示MVSQL發現了一個索引,可以用來查詢GROUPBY或DISTINCT查詢的所有列而不要額外搜索硬碟訪問實際的表。

常見的慢查詢優化
1. 索引沒起作用的情況
  - 使用like關鍵字的查詢語句時“%”不在第一個欄位
  - 使用多列索引時遵從最左原則
2. 優化資料庫結構
  - 將欄位很多的表分解成多個表
  - 增加中間表
    
    對於需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，把需要經常聯合查詢的數據插入到中間表中，然後將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。
3. 分解關聯查詢
  例如：
```
SELECT * FROM tag 
        JOIN tag_post ON tag_id = tag.id
        JOIN post ON tag_post.post_id = post.id
        WHERE tag.tag = 'mysql';
#分解為：
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);
```
4. 優化limit分頁
5. 分析具體的SQL語句

三、定位慢查詢

1、查看慢查詢日誌確定已經執行完的慢查詢

確定慢查詢日誌路徑（預設是 MySQL 的數據目錄）
```
mysql> show global cariables like "datadir";
```

確定慢查詢日誌的文件名

show global variables like "slow_query_log_file";

獲取慢日誌文件已經執行完的慢查詢
```
tail -n5 log文件路徑
```

2、show processlist 查看正在執行的慢查詢

有時慢查詢正在執行，已經導致資料庫負載偏高了，而由於慢查詢還沒執行完，因此慢查詢日誌還看不到任何語句。此時可以使用 show processlist 命令判斷正在執行的慢查詢。show processlist 顯示哪些線程正在運行。如果有 PROCESS 許可權，則可以看到所有線程。否則，只能看到當前會話的線程。

如果不使用 FULL 關鍵字，在 info 欄位中只顯示每個語句的前 100 個字元，如果想看語句的全部內容可以使用 full 修飾（show full processlist）。

mysql> show processlist\G`

`*************************** 10. row ***************************`

   `Id: 7651833`

   `User: one`

   `Host: 192.168.1.251:52154`

   `db: ops`

   `Command: Query`

   `Time: 3`

  `State: User sleep`

  `Info: select sleep(10)`

`......`

`10 rows in set (0.00 sec)`

Time表示執行時間，Info表示SQL語句。

參考資料：https://zhuanlan.zhihu.com/p/149338523#:~:text=MySQL的慢查詢，全名是慢查詢日誌，是MySQL提供的一種日誌記錄，用來記錄在MySQL中響應時間超過閥值的語句。,具體環境中，運行時間超過long_query_time值的SQL語句，則會被記錄到慢查詢日誌中。 long_query_time的預設值為10，意思是記錄運行10秒以上的語句。

https://www.jianshu.com/p/7c9e2c479c15