一绳姨、前言
上一篇我們說(shuō)了 騰訊一面：說(shuō)一說(shuō) MySQL 中索引的底層原理检柬，相信你對(duì)索引有個(gè)很清晰的認(rèn)識(shí)了钞澳，這一篇我們來(lái)說(shuō)一說(shuō)慢 SQL 的排查以及調(diào)優(yōu)现恼。為啥面試官要問(wèn)這個(gè)問(wèn)題，其實(shí)跟上一篇的索引底層原理有一定關(guān)聯(lián)關(guān)系的库糠，一般慢 SQL 很大一部分原因?qū)λ饕讓釉聿粔蛱貏e了解導(dǎo)致的沙兰，比如沒(méi)建索引、索引失效抒钱、索引沒(méi)滿(mǎn)足最左前綴匹配原則導(dǎo)致慢 SQL蜓肆，像騰訊這樣數(shù)據(jù)量很大的公司颜凯，人家肯定有專(zhuān)門(mén)的 DBA 去做優(yōu)化的，面試官考察的主要是你排查問(wèn)題的能力以及知道索引的底層原理仗扬、以及知道優(yōu)化的方向症概，不至于讓你進(jìn)來(lái)把人家規(guī)規(guī)矩矩的數(shù)據(jù)庫(kù)搞亂了。
我們下面直接進(jìn)入正題了早芭，首先來(lái)說(shuō)下怎么排查慢 SQL 的彼城。
二、開(kāi)啟慢查詢(xún)?nèi)罩?/h2>
MySQL 中與慢 SQL 有關(guān)的幾個(gè)重要系統(tǒng)變量如下：
| 參數(shù) | 含義 |
|---|---|
| slow_query_log | 是否啟用慢查詢(xún)?nèi)罩就烁觯琌N 為啟用募壕，OFF 為未啟用，默認(rèn)為 OFF语盈。開(kāi)啟會(huì)影響性能舱馅，MySQL 重啟會(huì)失效。 |
| slow_query_log_file | 指定慢查詢(xún)?nèi)罩疚募穆窂胶兔值痘模笔∥募?host_name-slow.log代嗤。 |
| long_query_time | 執(zhí)行時(shí)間超過(guò)該值才記錄到慢查詢(xún)?nèi)罩荆瑔挝粸槊胝掌澹J(rèn)為 10资溃。 |
| log_output | 日志輸出位置，默認(rèn)為 FILE烈炭，即保存為文件溶锭，若設(shè)置為 TABLE，則將日志記錄到 mysql.show_log 表中符隙，支持設(shè)置多種格式趴捅。 |
執(zhí)行如下語(yǔ)句看是否啟用了慢查詢(xún)?nèi)罩荆琌N 為啟用霹疫，OFF 為未啟用拱绑，默認(rèn)為 OFF。
SHOW VARIABLES LIKE '%slow_query_log%';
[圖片上傳失敗...(image-e1bea9-1649685069878)]
可以看到丽蝎，我這里是已經(jīng)開(kāi)啟了的猎拨。如果你的沒(méi)有開(kāi)啟，可以使用如下兩種方式來(lái)開(kāi)啟慢查詢(xún)屠阻。
2.1 修改配置文件
修改配置文件 vim /etc/my.cnf红省，在 [mysqld] 段落在加入如下配置：
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/data/slow.log
long_query_time=3
log_output=FILE,TABLE
需要重啟 MySQL 才可以生效，命令為 service mysqld restart
2.2 設(shè)置全局變量
如下打開(kāi)慢查詢(xún)?nèi)罩竟酰O(shè)置超時(shí)時(shí)間為 3 秒吧恃，并且將日志記錄到文件以及 mysql.show_log 表中。
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file='/var/lib/mysql/data/slow.log';
SET GLOBAL long_query_time=3;
SET GLOBAL log_output='FILE,TABLE';
想要永久生效得用上面那個(gè)配置文件里配置麻诀，否則數(shù)據(jù)庫(kù)重啟后痕寓，這些配置失效傲醉。
三、分析慢查詢(xún)?nèi)罩?/h2>
3.1 獲取慢 SQL 信息
查看慢查詢(xún)?nèi)罩居涗洈?shù)：
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
[圖片上傳失敗...(image-2b432b-1649685069878)]
模擬語(yǔ)句：
select sleep(5);
查看日志：
cat /var/lib/mysql/data/slow.log
[圖片上傳失敗...(image-7c7307-1649685069878)]
3.2 mysqldumpslow
MySQL 內(nèi)置了 mysqldumpslow 這個(gè)工具來(lái)幫我們分析慢查詢(xún)?nèi)罩尽?/p>
[圖片上傳失敗...(image-a2e3e1-1649685069878)]
#得到返回記錄集最多的10個(gè)SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log
#得到訪(fǎng)問(wèn)次數(shù)最多的10個(gè)SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/data/slow.log
#得到按照時(shí)間排序的前10條里面含有左連接的查詢(xún)語(yǔ)句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/data/slow.log
#結(jié)合| more使用呻率，防止爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log | more
s：表示按何種方式排序
c：訪(fǎng)問(wèn)次數(shù)
l：鎖定時(shí)間
r：返回記錄
t：查詢(xún)時(shí)間
al：平均鎖定時(shí)間
ar：平均返回記錄數(shù)
at：平均查詢(xún)時(shí)間
t：返回前面多少條的數(shù)據(jù)
g：后邊搭配一個(gè)正則匹配模式硬毕，大小寫(xiě)不敏感
3.3 pt-query-digest
pt-query-digest 是一款很強(qiáng)大的慢查詢(xún)?nèi)罩痉治龉ぞ撸梢苑治?MySQL 數(shù)據(jù)庫(kù)的 binary log 筷凤、 general log 日志昭殉，同時(shí)也可以使用 show processlist 或從 tcpdump 抓取的 MySQL 協(xié)議數(shù)據(jù)來(lái)進(jìn)行分析。
這里老周不帶大家搭建以及使用了哈藐守，想進(jìn)一步了解的可以看這份文檔：pt-query-digest
四挪丢、Explain 執(zhí)行計(jì)劃分析慢 SQL
上一篇我們非常詳細(xì)的去介紹了，一文讀懂 MySQL Explain 執(zhí)行計(jì)劃
五卢厂、Show Profile 分析慢 SQL
Show Profile 也可以分析慢 SQL乾蓬，比 explain 獲取的信息更詳細(xì)，比如能分析當(dāng)前會(huì)話(huà)中語(yǔ)句執(zhí)行的資源消耗情況慎恒，能分析這條 SQL 整個(gè)生命周期的耗時(shí)任内。但沒(méi)有上面 pt-query-digest 那款慢查詢(xún)?nèi)罩痉治龉ぞ邚?qiáng)大，但 pt-query-digest 是外置的需要單獨(dú)下載融柬，如果你想用內(nèi)置的話(huà)死嗦，能夠滿(mǎn)足你的需求的話(huà)，選擇 Show Profile 就行粒氧。
5.1 如何開(kāi)啟
默認(rèn)關(guān)閉越除。開(kāi)啟后，會(huì)在后臺(tái)保存最近 15 次的運(yùn)行結(jié)果外盯，然后通過(guò) Show Profile 命令查看結(jié)果摘盆。
-- 開(kāi)啟
SET profiling = ON;
-- 查看
SHOW VARIABLES LIKE 'profiling%';
[圖片上傳失敗...(image-93584a-1649685069878)]
5.2 SHOW profiles 查看 SQL 的耗時(shí)
[圖片上傳失敗...(image-b31d7d-1649685069878)]
5.3 SQL 整個(gè)生命周期的耗時(shí)
通過(guò) Query_ID 可以得到具體 SQL 從連接——服務(wù)——引擎——存儲(chǔ)四層結(jié)構(gòu)完整生命周期的耗時(shí)
SHOW profile CPU, BLOCK IO FOR QUERY 4;
[圖片上傳失敗...(image-d195e1-1649685069878)]
可用參數(shù) type:
ALL # 顯示所有的開(kāi)銷(xiāo)信息
BLOCK IO # 顯示塊IO相關(guān)開(kāi)銷(xiāo)
CONTEXT SWITCHES # 上下文切換相關(guān)開(kāi)銷(xiāo)
CPU # 顯示CPU相關(guān)開(kāi)銷(xiāo)信息
IPC # 顯示發(fā)送和接收相關(guān)開(kāi)銷(xiāo)信息
MEMORY # 顯示內(nèi)存相關(guān)開(kāi)銷(xiāo)信息
PAGE FAULTS # 顯示頁(yè)面錯(cuò)誤相關(guān)開(kāi)銷(xiāo)信息
SOURCE # 顯示和 Source_function，Source_file饱苟，Source_line 相關(guān)的開(kāi)銷(xiāo)信息
SWAPS # 顯示交換次數(shù)相關(guān)開(kāi)銷(xiāo)的信息
5.4 危險(xiǎn)狀態(tài)
SHOW profile CPU, BLOCK IO FOR QUERY 2;
GROUP BY 可能創(chuàng)建了臨時(shí)表
[圖片上傳失敗...(image-1387bc-1649685069878)]
危險(xiǎn)狀態(tài)：
converting HEAP to MyISAM # 查詢(xún)結(jié)果太大孩擂，內(nèi)存不夠用了，在往磁盤(pán)上搬箱熬。
Creating tmp table # 創(chuàng)建了臨時(shí)表类垦，回先把數(shù)據(jù)拷貝到臨時(shí)表，用完后再刪除臨時(shí)表城须。
Copying to tmp table on disk # 把內(nèi)存中臨時(shí)表復(fù)制到磁盤(pán)
locked # 記錄被鎖了
看到這些危險(xiǎn)狀態(tài)可以進(jìn)行相應(yīng)的調(diào)優(yōu)护锤，然后我們線(xiàn)上也會(huì)針對(duì)慢 SQL 進(jìn)行監(jiān)控，存在慢 SQL 的話(huà)會(huì)觸發(fā)告警機(jī)制酿傍，通知相應(yīng)的人員快速定位慢 SQL 并優(yōu)化。
