作為一個(gè) DBA，想必都有過(guò)被慢查詢(xún)折騰的經(jīng)歷，一個(gè)慢查詢(xún)有時(shí)候真的很讓人抓狂，本文對(duì)常規(guī)和非常規(guī)手段進(jìn)行了整理，由淺及深，簡(jiǎn)單介紹幾個(gè)慢查詢(xún)的分析手段。

需要說(shuō)明的是，下面所有的手段都是原生支持的功能（≥MySQL 5.6），因此在各類(lèi) RDS 和原生數(shù)據(jù)庫(kù)中都不會(huì)有什么使用上的差異，這里圖方便就用騰訊云數(shù)據(jù)庫(kù) MySQL 來(lái)作為測(cè)試環(huán)境了，版本為 5.7。

第一步：EXPLAIN

最先登場(chǎng)的毫無(wú)疑問(wèn)就是 EXPLAIN 語(yǔ)句了，用過(guò) MySQL 的人應(yīng)該都知道這個(gè)查看 SQL 語(yǔ)句執(zhí)行計(jì)劃的命令，詳細(xì)的資料在網(wǎng)上有很多，這里就略過(guò)了。**一般來(lái)說(shuō)，95% 的慢查詢(xún)問(wèn)題只需要 EXPLAIN 就可以解決了。**手工執(zhí)行的時(shí)候，在 Extra 列里面，避免出現(xiàn)Use Temporary Table和Using file sort這類(lèi)關(guān)鍵字，TYPE 列中也盡量避免 ALL 類(lèi)型（全表掃描）出現(xiàn)。

其實(shí)目前這個(gè)最常用的功能在騰訊云上可以直接用 DBbrain 來(lái)進(jìn)行操作了。DBbrain 會(huì)分析 SQL 語(yǔ)句并給出加索引的建議。在DBbrain中選擇對(duì)應(yīng)的實(shí)例，進(jìn)入 SQL 診斷的 tab 下，點(diǎn)擊具體的慢查詢(xún)就可以看到加索引的建議了：

第二步：PROFILE

既然 EXPLAIN 能看到 SQL 的執(zhí)行計(jì)劃，能判斷出來(lái)有沒(méi)有好好利用索引，DBbrain 也能給出索引的優(yōu)化建議，那么慢查詢(xún)的分析為什么還會(huì)有三步曲？

原因很簡(jiǎn)單，MySQL 慢查詢(xún)，并不一定慢在有沒(méi)有索引；SQL 的執(zhí)行環(huán)節(jié)中任意一環(huán)出了問(wèn)題都會(huì)表現(xiàn)為查詢(xún)變慢，所以用了索引，EXPLAIN 的結(jié)果也很完美，但是還是慢，怎么辦？

這時(shí)候，就需要 PROFILE 來(lái)幫忙了，這個(gè)命令可以詳細(xì)的列出 SQL 語(yǔ)句在每一個(gè)步驟消耗的時(shí)間，前提（缺點(diǎn)）是先執(zhí)行一遍語(yǔ)句。

PROFILE 默認(rèn)是關(guān)閉的，所以需要在 client 端先打開(kāi)，操作如下：

set session profiling = 1;

在實(shí)際的生產(chǎn)環(huán)境中，可能會(huì)需要加大profile的隊(duì)列，保證想要查看的 PROFILE 結(jié)果還保存著，因此可以用如下操作來(lái)增加 PROFILE 的隊(duì)列大小

set session profiling_history_size = 50;

到這一步，PROFILE 的功能就開(kāi)啟了，這里先刪除索引，簡(jiǎn)單試一下 SQL 語(yǔ)句，EXPLAIN 一下看看輸出

TYPE 列是 ALL，顯然這種語(yǔ)句是不合格的，“假設(shè)”索引“覺(jué)得”沒(méi)問(wèn)題，但是這個(gè)語(yǔ)句還是比預(yù)想的要慢，那么可以看看這條語(yǔ)句各個(gè)階段的耗時(shí)，先執(zhí)行一次 select，然后再查看 PROFILE 的結(jié)果：

可以看到 id 為 11 的那一行就是執(zhí)行過(guò)的語(yǔ)句，這時(shí)候使用show profile block io,cpu,memory,source for query 11;來(lái)查看統(tǒng)計(jì)信息：

Sending data 并不只是在服務(wù)器端和客戶(hù)端之間 Sending data，還包括了從磁盤(pán)讀取數(shù)據(jù)的時(shí)間，因?yàn)檫@個(gè)查詢(xún)執(zhí)行了全表掃描，所以這個(gè)時(shí)間會(huì)比較高，當(dāng)然索引的效率不高也會(huì)導(dǎo)致這部分時(shí)間比較久。

如果還有 order by 的話(huà)，這里面也會(huì)出現(xiàn) Sort 相關(guān)的信息。

經(jīng)過(guò)了這兩部曲之后，基本上一個(gè) SQL 為什么慢，慢在哪里基本上可以定位出來(lái)了，那么最后的手段主要是解決什么問(wèn)題呢？

第三步：OPTIMIZER_TRACE

OPTIMIZER_TRACE 是 MySQL 5.6 添加的新功能，顧名思義，這個(gè)功能可以看到內(nèi)部查詢(xún)計(jì)劃的 TRACE 信息，從而可以知道 MySQL 是如何在眾多索引中選中最“棒”的那個(gè)。一般來(lái)說(shuō)，這個(gè)最“棒”的索引選錯(cuò)了，就需要根據(jù) OPTIMIZER_TRACE 的信息來(lái)判斷為什么會(huì)選錯(cuò)，是 MySQL 的配置原因，還是 SQL 某些地方寫(xiě)的不好導(dǎo)致 MySQL 誤判了。

開(kāi)啟這個(gè)功能的方式如下：

set session optimizer_trace='enabled=on';

隨便執(zhí)行一個(gè) EXPLAIN 語(yǔ)句，生成一個(gè)執(zhí)行計(jì)劃，然后在information_chema.optimizer_trace的表里面查找這一條語(yǔ)句對(duì)應(yīng)的信息：

內(nèi)容是非常長(zhǎng)的 JSON 格式，所以推薦把結(jié)果轉(zhuǎn)存到其他地方，然后用 JSON 的轉(zhuǎn)換工具來(lái)輔助查看，如果要看索引的選擇情況，就重點(diǎn)關(guān)注這個(gè) JSON 的ref_optimizer_key_uses，rows_estimation 及之后的部分，這里會(huì)展示索引選擇相關(guān)的信息，截取一部分結(jié)果作為示例：

在這里面能看到詳細(xì)的統(tǒng)計(jì)信息，包括 cost，預(yù)計(jì)的 rows，在之后的內(nèi)容中也會(huì)顯示最終選擇的索引：

通常來(lái)說(shuō)，cost 數(shù)值越低，代表這個(gè)執(zhí)行計(jì)劃的執(zhí)行速度越快。

總結(jié)

其實(shí)在絕大多數(shù)的情況下，EXPLAIN 完全可以勝任，在騰訊云平臺(tái)上的話(huà)，用 DBbrain 即可，PROFILE 一般是用來(lái)決定分析和判斷的方向，看看是哪個(gè)階段比較慢。OPTIMIZER_TRACE 主要用來(lái)分析各種疑難雜癥，比如說(shuō)優(yōu)化器為什么沒(méi)有選擇索引而是全表掃描？為什么優(yōu)化器沒(méi)有選擇效率較好的索引，而是選擇了一個(gè)效率較差的索引（order by，limit）等等。

總而言之，通過(guò)這三步曲的排查，基本上 SQL 的問(wèn)題就都能找出來(lái)了，好好掌握這些基本技能對(duì)于 DBA 來(lái)說(shuō)還是很有用的。