MySQL中怎么定位慢查詢

本篇文章給大家分享的是有關MySQL中怎么定位慢查詢，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

NO.1 慢查詢日志定位解析

MySQL 的慢查詢日志記錄的內容是：在 MySQL 中響應時間超過參數 long_query_time（單位秒，默認值 10）設置的值并且掃描記錄數不小于 min_examined_row_limit（默認值0）的語句。

NOTE：默認情況下，慢查詢日志中不會記錄管理語句，如果需要記錄的請做如下設置，設置log_slow_admin_statements  = on 讓管理語句中的慢查詢也會記錄到慢查詢日志中。默認情況下，也不會記錄查詢時間不超過 long_query_time  但是不使用索引的語句，可通過配置
log_queries_not_using_indexes = on 讓不使用索引的 SQL 都被記錄到慢查詢日志中（即使查詢時間沒超過 long_query_time 配置的值）。

慢查詢日志使用步驟：

使用慢查詢日志，一般分為四步：

開啟慢查詢日志。

設置慢查詢閥值。

確定慢查詢日志路徑。

確定慢查詢日志的文件名。

開啟慢查詢日志（默認是關閉的）：

mysql> set global slow_query_log = on;  Query OK, 0 rows affected (0.00 sec)

設置慢查詢時間限制（查詢時間只要大于這個值都將記錄到慢查詢日志中，單位：秒）：

mysql> set global long_query_time = 1;  Query OK, 0 rows affected (0.00 sec)

確定慢查詢日志路徑：

mysql> show global variables like "datadir";

確定慢查詢日志文件名：

mysql> show global variables like "slow_query_log_file";

NOTE：慢查詢query  time設置小技巧：線上業務一般建議把 long_query_time 設置為 1 秒，如果某個業務的 MySQL 要求比較高的  QPS，可設置慢查詢為 0.1 秒。發現慢查詢及時優化或者提醒開發改寫。一般測試環境建議 long_query_time  設置的閥值比生產環境的小，比如生產環境是 1 秒，則測試環境建議配置成 0.5 秒。便于在測試環境及時發現一些效率低的  SQL。甚至某些重要業務測試環境 long_query_time 可以設置為  0，以便記錄所有語句。并留意慢查詢日志的輸出，上線前的功能測試完成后，分析慢查詢日志每類語句的輸出，重點關注  Rows_examined（語句執行期間從存儲引擎讀取的行數），提前優化。

接下來在確定慢查詢日志后可以通過：tail -n5
/data/mysql/mysql-slow.log 命令查看

這里對上方的執行結果詳細描述一下：

tail -n5：只查看慢查詢文件的最后5行

Time：慢查詢發生的時間

User@Host：客戶端用戶和IP

Query_time：查詢時間

Lock_time：等待表鎖的時間

Rows_sent：語句返回的行數

Rows_examined：語句執行期間從存儲引擎掃描的行數

上面這種方式是用系統自帶的慢查詢日志查看的，如果覺得系統自帶的慢查詢日志不方便查看，小伙伴們可以使用 pt-query-digest 或者 mysqldumpslow 等工具對慢查詢日志進行分析，這不是本節重點，不演示了。

通過 show processlist定位慢查詢

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

知識擴展：如果不使用 FULL 關鍵字，在 info 字段中只顯示每個語句的前 100 個字符，如果想看語句的全部內容可以使用 full 修飾（show full processlist）。

這里對上面結果重點參數解釋一下：

Time：表示執行時間

Info：表示 SQL 語句

我們這里可以通過它的執行時間（Time）來判斷是否是慢 SQL。

EXLPAIN分析慢查詢

分析  SQL 執行效率是優化 SQL 的重要手段，通過上面講的兩種方法，定位到慢查詢語句后，我們就要開始分析 SQL  執行效率了，子曾經曰過：“工欲善其事，必先利其器”，我們可以通過 explain、show profile 和 trace  等診斷工具來分析慢查詢。本節先講解 explain 的使用，在下節將分享 show profile 和 trace 的使用。

Explain 可以獲取 MySQL 中 SQL 語句的執行計劃，比如語句是否使用了關聯查詢、是否使用了索引、掃描行數等。可以幫我們選擇更好地索引和寫出更優的 SQL 。使用方法：在查詢語句前面加上 explain 運行就可以了。

創建一個測試表并且插入部分數據用于測試

在上圖表中我們創建了3個索引

PRIMARY KEY (`id`), 聚集索引 KEY `idx_a` (`a`),非聚集索引 KEY `idx_b_c` (`b`,`c`)非聚集索引 d列沒有創建索引

執行三個SQL分別得到如下結果

Explain字段詳解（重點關注加粗項）：

這幾列重點解讀：

1. select_type重點解讀

2. type重點解讀：查詢性能從上到下依次是最好到最差

3. extra重點解讀

以上就是MySQL中怎么定位慢查詢，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。