MySQL 的慢查詢?nèi)罩居涗浀膬?nèi)容是：在 MySQL 中響應(yīng)時(shí)間超過參數(shù) long_query_time（單位秒，默認(rèn)值 10）設(shè)置的值并且掃描記錄數(shù)不小于 min_examined_row_limit（默認(rèn)值0）的語句。

NOTE：默認(rèn)情況下，慢查詢?nèi)罩局胁粫?huì)記錄管理語句，如果需要記錄的請(qǐng)做如下設(shè)置，設(shè)置log_slow_admin_statements = on 讓管理語句中的慢查詢也會(huì)記錄到慢查詢?nèi)罩局小ＤJ(rèn)情況下，也不會(huì)記錄查詢時(shí)間不超過 long_query_time 但是不使用索引的語句，可通過配置
log_queries_not_using_indexes = on 讓不使用索引的 SQL 都被記錄到慢查詢?nèi)罩局校词共樵儠r(shí)間沒超過 long_query_time 配置的值）。

慢查詢?nèi)罩臼褂貌襟E：

使用慢查詢?nèi)罩荆话惴譃樗牟剑?/p>

開啟慢查詢?nèi)罩尽?/p>

設(shè)置慢查詢閥值。

確定慢查詢?nèi)罩韭窂健?/p>

確定慢查詢?nèi)罩镜奈募?/p>

開啟慢查詢?nèi)罩荆J(rèn)是關(guān)閉的）：

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

設(shè)置慢查詢時(shí)間限制（查詢時(shí)間只要大于這個(gè)值都將記錄到慢查詢?nèi)罩局校瑔挝唬好耄?/p>

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

確定慢查詢?nèi)罩韭窂剑?/p>

mysql> show global variables like "datadir"; 

確定慢查詢?nèi)罩疚募?/p>

mysql> show global variables like "slow_query_log_file"; 

NOTE：慢查詢query time設(shè)置小技巧：線上業(yè)務(wù)一般建議把 long_query_time 設(shè)置為 1 秒，如果某個(gè)業(yè)務(wù)的 MySQL 要求比較高的 QPS，可設(shè)置慢查詢?yōu)?0.1 秒。發(fā)現(xiàn)慢查詢及時(shí)優(yōu)化或者提醒開發(fā)改寫。一般測(cè)試環(huán)境建議 long_query_time 設(shè)置的閥值比生產(chǎn)環(huán)境的小，比如生產(chǎn)環(huán)境是 1 秒，則測(cè)試環(huán)境建議配置成 0.5 秒。便于在測(cè)試環(huán)境及時(shí)發(fā)現(xiàn)一些效率低的 SQL。甚至某些重要業(yè)務(wù)測(cè)試環(huán)境 long_query_time 可以設(shè)置為 0，以便記錄所有語句。并留意慢查詢?nèi)罩镜妮敵觯暇€前的功能測(cè)試完成后，分析慢查詢?nèi)罩久款愓Z句的輸出，重點(diǎn)關(guān)注 Rows_examined（語句執(zhí)行期間從存儲(chǔ)引擎讀取的行數(shù)），提前優(yōu)化。

接下來在確定慢查詢?nèi)罩竞罂梢酝ㄟ^：tail -n5
/data/mysql/mysql-slow.log 命令查看

這里對(duì)上方的執(zhí)行結(jié)果詳細(xì)描述一下：

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

Time：慢查詢發(fā)生的時(shí)間

User@Host：客戶端用戶和IP

Query_time：查詢時(shí)間

Lock_time：等待表鎖的時(shí)間

Rows_sent：語句返回的行數(shù)

Rows_examined：語句執(zhí)行期間從存儲(chǔ)引擎掃描的行數(shù)

上面這種方式是用系統(tǒng)自帶的慢查詢?nèi)罩静榭吹模绻X得系統(tǒng)自帶的慢查詢?nèi)罩静环奖悴榭矗』锇閭兛梢允褂?pt-query-digest 或者 mysqldumpslow 等工具對(duì)慢查詢?nèi)罩具M(jìn)行分析，這不是本節(jié)重點(diǎn)，不演示了。

通過 show processlist定位慢查詢

有時(shí)慢查詢正在執(zhí)行，已經(jīng)導(dǎo)致數(shù)據(jù)庫負(fù)載偏高了，而由于慢查詢還沒執(zhí)行完，因此慢查詢?nèi)罩具€看不到任何語句。此時(shí)可以使用 show processlist 命令判斷正在執(zhí)行的慢查詢。show processlist 顯示哪些線程正在運(yùn)行。如果有 PROCESS 權(quán)限，則可以看到所有線程。否則，只能看到當(dāng)前會(huì)話的線程。

知識(shí)擴(kuò)展：如果不使用 FULL 關(guān)鍵字，在 info 字段中只顯示每個(gè)語句的前 100 個(gè)字符，如果想看語句的全部內(nèi)容可以使用 full 修飾（show full processlist）。

這里對(duì)上面結(jié)果重點(diǎn)參數(shù)解釋一下：

Time：表示執(zhí)行時(shí)間

Info：表示 SQL 語句

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

EXLPAIN分析慢查詢

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

Explain 可以獲取 MySQL 中 SQL 語句的執(zhí)行計(jì)劃，比如語句是否使用了關(guān)聯(lián)查詢、是否使用了索引、掃描行數(shù)等。可以幫我們選擇更好地索引和寫出更優(yōu)的 SQL 。使用方法：在查詢語句前面加上 explain 運(yùn)行就可以了。

創(chuàng)建一個(gè)測(cè)試表并且插入部分?jǐn)?shù)據(jù)用于測(cè)試

在上圖表中我們創(chuàng)建了3個(gè)索引

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

執(zhí)行三個(gè)SQL分別得到如下結(jié)果

Explain字段詳解（重點(diǎn)關(guān)注加粗項(xiàng)）：

這幾列重點(diǎn)解讀：

1. select_type重點(diǎn)解讀

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

3. extra重點(diǎn)解讀

總結(jié)

今天我分享的關(guān)于定位慢 SQL 及使用 explain 分析慢 SQL 到這里就結(jié)束了。

本節(jié)知識(shí)點(diǎn)總結(jié)如下：

學(xué)習(xí)了兩種慢查詢定位方法。掌握了explain關(guān)鍵列的含義以及使用方法，這也是工作中最常用的方法。在工作中及面試時(shí)，SQL 性能優(yōu)化都是我們經(jīng)常遇到的問題，要想做好性能優(yōu)化，我們必須學(xué)會(huì)使用 SQL 優(yōu)化時(shí)需要的工具，進(jìn)行定位和分析。

由于篇幅的問題，本小節(jié)只介紹了 explain 工具的使用，在下節(jié)將補(bǔ)充另外兩種分析慢查詢的工具：show profile 和 trace。在后面我會(huì)再講解 SQL 優(yōu)化的一些知識(shí)點(diǎn)，相信小伙伴們 SQL 性能優(yōu)化時(shí)一定可以越來越熟練。