一、前言

上一篇我們說(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)滿足最左前綴匹配原則導(dǎo)致慢 SQL，像騰訊這樣數(shù)據(jù)量很大的公司，人家肯定有專門(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)啟慢查詢?nèi)罩?/h2>

MySQL 中與慢 SQL 有關(guān)的幾個(gè)重要系統(tǒng)變量如下：

參數(shù)	含義
slow_query_log	是否啟用慢查詢?nèi)罩荆琌N 為啟用，OFF 為未啟用，默認(rèn)為 OFF。開(kāi)啟會(huì)影響性能，MySQL 重啟會(huì)失效。
slow_query_log_file	指定慢查詢?nèi)罩疚募穆窂胶兔?，缺省文件?host_name-slow.log。
long_query_time	執(zhí)行時(shí)間超過(guò)該值才記錄到慢查詢?nèi)罩荆瑔挝粸槊?，默認(rèn)為 10。
log_output	日志輸出位置，默認(rèn)為 FILE，即保存為文件，若設(shè)置為 TABLE，則將日志記錄到 mysql.show_log 表中，支持設(shè)置多種格式。

執(zhí)行如下語(yǔ)句看是否啟用了慢查詢?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)啟慢查詢。

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)慢查詢?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ù)重啟后，這些配置失效。

三、分析慢查詢?nèi)罩?/h2>

3.1 獲取慢 SQL 信息

查看慢查詢?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)幫我們分析慢查詢?nèi)罩尽?/p>

[圖片上傳失敗...(image-a2e3e1-1649685069878)]

#得到返回記錄集最多的10個(gè)SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log
#得到訪問(wèn)次數(shù)最多的10個(gè)SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/data/slow.log
#得到按照時(shí)間排序的前10條里面含有左連接的查詢語(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：訪問(wèn)次數(shù)
l：鎖定時(shí)間
r：返回記錄
t：查詢時(shí)間
al：平均鎖定時(shí)間
ar：平均返回記錄數(shù)
at：平均查詢時(shí)間
t：返回前面多少條的數(shù)據(jù)
g：后邊搭配一個(gè)正則匹配模式，大小寫(xiě)不敏感

3.3 pt-query-digest

pt-query-digest 是一款很強(qiáng)大的慢查詢?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ì)話中語(yǔ)句執(zhí)行的資源消耗情況，能分析這條 SQL 整個(gè)生命周期的耗時(shí)。但沒(méi)有上面 pt-query-digest 那款慢查詢?nèi)罩痉治龉ぞ邚?qiáng)大，但 pt-query-digest 是外置的需要單獨(dú)下載，如果你想用內(nèi)置的話，能夠滿足你的需求的話，選擇 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)銷信息
BLOCK IO # 顯示塊IO相關(guān)開(kāi)銷
CONTEXT SWITCHES # 上下文切換相關(guān)開(kāi)銷
CPU # 顯示CPU相關(guān)開(kāi)銷信息
IPC # 顯示發(fā)送和接收相關(guān)開(kāi)銷信息
MEMORY # 顯示內(nèi)存相關(guān)開(kāi)銷信息
PAGE FAULTS # 顯示頁(yè)面錯(cuò)誤相關(guān)開(kāi)銷信息
SOURCE # 顯示和 Source_function，Source_file，Source_line 相關(guān)的開(kāi)銷信息
SWAPS # 顯示交換次數(shù)相關(guān)開(kāi)銷的信息

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 # 查詢結(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)，然后我們線上也會(huì)針對(duì)慢 SQL 進(jìn)行監(jiān)控，存在慢 SQL 的話會(huì)觸發(fā)告警機(jī)制，通知相應(yīng)的人員快速定位慢 SQL 并優(yōu)化。