慢查詢(xún) MySQL 定位優(yōu)化技巧，從10s優(yōu)化到300ms
今天分享一下如何快速定位慢查詢(xún)SQL以及優(yōu)化。
一、如何定位并優(yōu)化慢查詢(xún)SQL？
一般有3個(gè)思考方向：
- 根據(jù)慢日志定位慢查詢(xún)sql。
- 使用explain等工具分析sql執(zhí)行計(jì)劃。
- 修改sql或者盡量讓sql走索引。
二、如何使用慢查詢(xún)?nèi)罩荆?/h3>
先給出步驟，后面說(shuō)明，有3個(gè)步驟
1. 開(kāi)啟慢查詢(xún)?nèi)罩?/h4>
首先開(kāi)啟慢查詢(xún)?nèi)罩荆蓞?shù)slow_query_log決定是否開(kāi)啟，在MySQL命令行下輸入下面的命令：
set global slow_query_log=on;
默認(rèn)環(huán)境下，慢查詢(xún)?nèi)罩臼顷P(guān)閉的，所以這里開(kāi)啟。
2. 設(shè)置慢查詢(xún)閾值
set global long_query_time=1;
只要你的SQL實(shí)際執(zhí)行時(shí)間超過(guò)了這個(gè)閾值，就會(huì)被記錄到慢查詢(xún)?nèi)罩纠锩妗＿@個(gè)閾值默認(rèn)是10s，線上業(yè)務(wù)一般建議把long_query_time設(shè)置為1s，如果某個(gè)業(yè)務(wù)的MySQL要求比較高的QPS，可設(shè)置慢查詢(xún)?yōu)?.1s。
發(fā)現(xiàn)慢查詢(xún)及時(shí)優(yōu)化或者提醒開(kāi)發(fā)改寫(xiě)。一般測(cè)試環(huán)境建議long_query_time設(shè)置的閥值比生產(chǎn)環(huán)境的小，比如生產(chǎn)環(huán)境是1s，則測(cè)試環(huán)境建議配置成0.5s。便于在測(cè)試環(huán)境及時(shí)發(fā)現(xiàn)一些效率的SQL。
甚至某些重要業(yè)務(wù)測(cè)試環(huán)境long_query_time?可以設(shè)置為0，以便記錄所有語(yǔ)句。并留意慢查詢(xún)?nèi)罩镜妮敵觯暇€前的功能測(cè)試完成后，分析慢查詢(xún)?nèi)罩久款?lèi)語(yǔ)句的輸出，重點(diǎn)關(guān)注Rows_examined（語(yǔ)句執(zhí)行期間從存儲(chǔ)引擎讀取的行數(shù)），提前優(yōu)化。
3.確定慢查詢(xún)?nèi)罩镜奈募吐窂?/h4>show global variables like 'slow_query_log_file'
show global variables like 'slow_query_log_file'
結(jié)果會(huì)發(fā)現(xiàn)慢日志默認(rèn)路徑就是MySQL的數(shù)據(jù)目錄，我們可以來(lái)看一下MySQL數(shù)據(jù)目錄。
show global variables like 'datadir';
不用關(guān)注這里為什么不是MySQL 8.0，這和版本沒(méi)什么關(guān)系的。
來(lái)，直接上菜，干巴巴的定義我自己都看不下去。
我們先來(lái)查看一下變量，我框出了需要注意的點(diǎn)。
查詢(xún)帶有quer的相關(guān)變量：
show global variables like '%quer%';
這里設(shè)置慢查詢(xún)閾值為1s：
set global long_query_time=1;
可以看到已經(jīng)修改過(guò)來(lái)了：
但是重啟mysql客戶(hù)端設(shè)置和統(tǒng)計(jì)慢查詢(xún)?nèi)罩緱l數(shù)就會(huì)清零，即所有配置修改會(huì)還原。
命令修改配置之后，在命令行net stop mysql?關(guān)閉MySQL服務(wù)，再net start mysql?開(kāi)啟MySQL服務(wù)，接著執(zhí)行show global variables like '%quer%';會(huì)發(fā)現(xiàn)配置還原了。
在配置文件修改才能永久改變，否則重啟數(shù)據(jù)庫(kù)就還原了。
3.慢查詢(xún)例子演示，新手都能看懂
數(shù)據(jù)表結(jié)構(gòu)，偷懶沒(méi)寫(xiě)comment：
CREATE TABLE `person_info_large` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`account` VARCHAR (10),
`name` VARCHAR (20),
`area` VARCHAR (20),
`title` VARCHAR (20),
`motto` VARCHAR (50),
PRIMARY KEY (`id`),
UNIQUE(`account`),
KEY `index_area_title`(`area`,`title`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
這里的數(shù)據(jù)是200W條。請(qǐng)注意表結(jié)構(gòu)，記住哪幾個(gè)字段有索引即可，后續(xù)圍繞這個(gè)表進(jìn)行分析。
這個(gè)3.36s并不是實(shí)際執(zhí)行時(shí)間，實(shí)際執(zhí)行時(shí)間得去慢查詢(xún)?nèi)罩救タ碤uery_time參數(shù)。
可以看到Query_time: 6.337729s，超過(guò)了1s，所以會(huì)被記錄，一個(gè)select語(yǔ)句查詢(xún)這么久，簡(jiǎn)直無(wú)法忍受。
圖中其他的參數(shù)解釋如下：
- Time：慢查詢(xún)發(fā)生的時(shí)間
- Query_time：查詢(xún)時(shí)間
- Lock_time：等待鎖表的時(shí)間
- Rows_sent：語(yǔ)句返回的行數(shù)
- Rows_exanined：語(yǔ)句執(zhí)行期間從存儲(chǔ)引擎讀取的行數(shù)。
上面這種方式是用系統(tǒng)自帶的慢查詢(xún)?nèi)罩静榭吹模绻X(jué)得系統(tǒng)自帶的慢查詢(xún)?nèi)罩静环奖悴榭矗梢允褂胮t-query-digest?或者mysqldumpslow等工具對(duì)慢查詢(xún)?nèi)罩具M(jìn)行分析。
：有的慢查詢(xún)正在執(zhí)行，結(jié)果已經(jīng)導(dǎo)致數(shù)據(jù)庫(kù)負(fù)載過(guò)高，而由于慢查詢(xún)還沒(méi)執(zhí)行完，因此慢查詢(xún)?nèi)罩究床坏饺魏握Z(yǔ)句，此時(shí)可以使用show processlist?命令查看正在執(zhí)行的慢查詢(xún)。show processlist?顯示哪些線程正在運(yùn)行，如果有PROCESS權(quán)限，則可以看到所有線程。否則，只能看到當(dāng)前會(huì)話(huà)線程。
四、查詢(xún)語(yǔ)句慢怎么辦？explain帶你分析sql執(zhí)行計(jì)劃
根據(jù)上一節(jié)的表結(jié)構(gòu)可以知道，account是添加了唯一索引的字段。explain分析一下執(zhí)行計(jì)劃。
我們重點(diǎn)需要關(guān)注select_type、type、possible_keys、key、Extra?這些列，我們來(lái)一一說(shuō)明，看到select_type?列，這里是SIMPLE簡(jiǎn)單查詢(xún)，其他值下面給大家列出。
type列，這里是index，表示全索引掃描。
表格從上到下代表了sql查詢(xún)性能從最優(yōu)到最差，如果是type類(lèi)型是all，說(shuō)明sql語(yǔ)句需要優(yōu)化。
注意：如果type = NULL?，則表明個(gè)MySQL不用訪問(wèn)表或者索引，直接就能得到結(jié)果，比如explain select sum(1+2);
possible_keys代表可能用到的索引列，key表示實(shí)際用到的索引列，以實(shí)際用到的索引列為準(zhǔn)，這是查詢(xún)優(yōu)化器優(yōu)化過(guò)后選擇的，然后我們也可以根據(jù)實(shí)際情況強(qiáng)制使用我們自己的索引列來(lái)查詢(xún)。
Extra列，這里是Using index
一定要注意，Extra中出現(xiàn)Using filesort、Using temporary代表MySQL根本不能使用索引，效率會(huì)受到嚴(yán)重影響，應(yīng)當(dāng)盡可能的去優(yōu)化。
出現(xiàn)Using filesort說(shuō)明MySQL對(duì)結(jié)果使用一個(gè)外部索引排序，而不是從表里按索引次序讀到相關(guān)內(nèi)容，有索引就維護(hù)了B+樹(shù)，數(shù)據(jù)本來(lái)就已經(jīng)排好序了，這說(shuō)明根本沒(méi)有用到索引，而是數(shù)據(jù)讀完之后再排序，可能在內(nèi)存或者磁盤(pán)上排序。也有人將MySQL中無(wú)法利用索引的排序操作稱(chēng)為“文件排序”。
出現(xiàn)Using temporary?表示MySQL在對(duì)查詢(xún)結(jié)果排序時(shí)使用臨時(shí)表，常見(jiàn)于order by?和分組查詢(xún)group by。
回到上一個(gè)話(huà)題，我們看到account是添加了唯一索引的字段。explain分析了執(zhí)行計(jì)劃后。
直接按照account降序來(lái)查：
查看慢查詢(xún)?nèi)罩景l(fā)現(xiàn)，使用索引之后，查詢(xún)200W條數(shù)據(jù)的速度快了2s。
接著我們分析一下查詢(xún)name的sql執(zhí)行計(jì)劃。
然后給name字段加上索引：
加上索引之后，繼續(xù)看看查詢(xún)name的sql執(zhí)行計(jì)劃：
對(duì)比一下前面name不加索引時(shí)的執(zhí)行計(jì)劃就會(huì)發(fā)現(xiàn)，加了索引后，type由ALL全表掃描變成index索引掃描。order by?并沒(méi)有 using filesort?，而是using index，這里B+樹(shù)已經(jīng)將這個(gè)非聚集索引的索引字段的值排好序了，而不是等到查詢(xún)的時(shí)候再去排序。
接著我們繼續(xù)執(zhí)行查詢(xún)語(yǔ)句，此時(shí)name已經(jīng)是添加了索引的。
結(jié)果發(fā)現(xiàn)，name添加索引之前，降序查詢(xún)name是花費(fèi)6.337729s，添加索引之后，降序查詢(xún)name花費(fèi)了3.479827s，原因就是B+樹(shù)的結(jié)果集已經(jīng)是有序的了。
五、當(dāng)主鍵索引、唯一索引、普通索引都存在，查詢(xún)優(yōu)化器如何選擇？
查詢(xún)一下數(shù)據(jù)的條數(shù)，這里count(id)，分析一下sql執(zhí)行計(jì)劃：
這里實(shí)際使用的索引是account唯一索引。
分析一下：實(shí)際使用哪個(gè)索引是查詢(xún)優(yōu)化器決定的，B+樹(shù)的葉子結(jié)點(diǎn)就是鏈表結(jié)構(gòu)，遍歷鏈表就可以統(tǒng)計(jì)數(shù)量，但是這張表，有主鍵索引、唯一索引、普通索引，優(yōu)化器選擇了account這個(gè)唯一索引，這肯定不會(huì)使用主鍵索引，因?yàn)橹麈I索引是聚集索引，每個(gè)葉子包含具體的一個(gè)行記錄（很多列的數(shù)據(jù)都在里面），而非聚集索引每個(gè)葉子只包含下一個(gè)主鍵索引的指針，很顯然葉子結(jié)點(diǎn)包含的數(shù)據(jù)是越少越好，查詢(xún)優(yōu)化器就不會(huì)選擇主鍵索引。
當(dāng)然，也可以強(qiáng)制使用主鍵索引，然后分析sql執(zhí)行計(jì)劃。
我們看一下優(yōu)化器默認(rèn)使用唯一索引大致執(zhí)行時(shí)間676ms：
強(qiáng)制使用主鍵索引大致執(zhí)行時(shí)間779ms：
我們可以用force index強(qiáng)制指定索引，然后去分析執(zhí)行計(jì)劃看看哪個(gè)索引是更好的，因?yàn)椴樵?xún)優(yōu)化器選擇索引不一定是百分百準(zhǔn)確的，具體情況可以根據(jù)實(shí)際場(chǎng)景分析來(lái)確定是否使用查詢(xún)優(yōu)化器選擇的索引。
