面試官：線上慢 SQL 如何排查？
大家好，我是秀才，今天跟大家一起來看一個實際工作和面試中經(jīng)常出現(xiàn)的線上問題：慢SQL問題的定位與排查。大家在網(wǎng)上可能看到的大都是一些方法的總結，這次我們來點不一樣的，我們一起來實操看一下，線上慢SQL問題究竟是如何定位和解決的。紙上得來終覺淺，絕知此事須躬行，相信經(jīng)過真正實踐之后，大家的感觸和印象一定更深，在后續(xù)的面試工作中也會更加的游刃有余。
在系統(tǒng)開發(fā)中，慢SQL查詢經(jīng)常會遇到，尤其是數(shù)據(jù)量大的業(yè)務場景下，慢SQL查詢是一個非常常?的性能瓶頸，可能導致應用響應緩慢，嚴重影響用戶體驗。這就需要開發(fā)者能夠有效地排查慢SQL問題，慢SQL問題的排查也是有一個比較系統(tǒng)的路徑，從識別問題到優(yōu)化解決。
一、案例分析
我們以一張訂單表，插入一些數(shù)據(jù)，來模擬一次慢sql的整體優(yōu)化路徑
1. 測試環(huán)境
Linux服務器：2G運行內(nèi)存
Mysql版本：MySQL 8.0.37
2. 創(chuàng)建表
執(zhí)行以下sql創(chuàng)建一張訂單表t_order，
DROP TABLEIFEXISTS`t_order`;
CREATETABLE`t_order` (
`id`bigintNOTNULL AUTO_INCREMENT COMMENT'主鍵',
`order_id`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'訂單編碼',
`product_id`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'產(chǎn)品編碼',
`product_name`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'產(chǎn)品名稱',
`customer_id`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'客戶編碼',
`customer_name`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'客戶名稱',
`amount`decimal(10, 2) NOTNULLCOMMENT'訂單金額',
`status`tinyintNOTNULLCOMMENT'訂單狀態(tài)碼，0：待付款 1：已付款，待發(fā)貨 2：已發(fā)貨 3：已完成 4：已取消',
`create_time` datetime NOTNULLCOMMENT'創(chuàng)建日期',
`update_time` datetime NULLDEFAULTNULLCOMMENT'更新時間',
PRIMARY KEY (`id`) USING BTREE,
INDEX`idx_custnum_status_createtime`(`customer_id`ASC, `status`ASC, `create_time`ASC) USING BTREE,
INDEX`idx_order_id`(`order_id`ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1CHARACTERSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '訂單表' ROW_FORMAT = DYNAMIC;表建好以后，看一下表設計：
3. 數(shù)據(jù)插入
通過存儲過程，向表里插入500w條數(shù)據(jù)：
-- 如果存在該存儲過程，則刪除存儲過程
DROPPROCEDUREIFEXISTS batchInsertBigData;
-- 創(chuàng)建存儲過程
DELIMITER $$
CREATEPROCEDURE batchInsertBigData(IN args INT)
BEGIN
DECLARE i INTDEFAULT1;
DECLARE order_id BIGINTDEFAULT0;
-- 開啟事務
STARTTRANSACTION;
WHILE i <= args DO
-- 用時間戳*1000 +i 來生成生成唯一的訂單編號
SET order_id = (UNIX_TIMESTAMP() * 1000) + i;
-- 插入數(shù)據(jù)
INSERTINTO t_order (
`order_id`,
`product_id`,
`product_name`,
`customer_id`,
`customer_name`,
`amount`,
`status`,
`create_time`,
`update_time`
) VALUES (
order_id, -- 前面生成的唯一訂單編號
CONCAT('PDT', LPAD(i, 6, '0')), -- 產(chǎn)品編號，左填充為6位數(shù)字
CONCAT('Product-', i), -- 產(chǎn)品名稱
CONCAT('CUST', LPAD((i % 1000) + 1, 6, '0')), -- 假設有1000個客戶編號循環(huán)使用
CONCAT('Customer-', (i % 1000) + 1), -- 客戶名稱
ROUND((RAND() * 100) + 100, 2), -- 隨機生成100到200的訂單金額
CASEWHEN i % 5 = 0THEN0WHEN i % 3 = 0THEN1ELSE2END, -- 隨機分配幾種狀態(tài)
NOW(), -- 創(chuàng)建時間
NOW() -- 更新時間
);
SET i = i + 1;
ENDWHILE;
COMMIT;
END$$
DELIMITER ;
-- 調(diào)用存儲過程
CALL batchInsertBigData(5000000);
執(zhí)行完所有的數(shù)據(jù)插入，大約耗時10分鐘
4. 開啟慢查詢?nèi)罩?/h4>
查看Mysql慢查詢?nèi)罩臼欠耖_啟：
show variables like'%slow_query_log%';
結果顯示OFF，表示未開啟慢查詢?nèi)罩尽?/p>
接下來開啟慢查詢?nèi)罩荆?/p>
set global slow_query_log = on修改慢查詢sql的限制時間，為了方便測試，這里把時間調(diào)整為1s，也就是說超過1s的sql都會被記錄到這個慢查詢?nèi)罩疚募校?/p>
set global long_query_time=1;注意：在修改上述幾個配置之后需要退出Mysql，重新登錄，才會生效：
驗證修改配置是否生效：
慢查詢?nèi)罩疽呀?jīng)開啟，慢查詢?nèi)罩緯涗浀轿募?var/lib/mysql/fd2f83b86d9b-slow.log中：
慢查詢sql的時長限制已經(jīng)修改為1s。
5. 聯(lián)合索引優(yōu)化
(1) 原始sql查詢
現(xiàn)有如下sql語句，想要查詢customer_id 最后編號為“000922”且create_time = '2024-10-29 10:13:49'以及`status` = '1' 的所有記錄：
select * from t_order t where customer_id like '%000922' and t.create_time = '2024-10-29 10:13:49' and t.`status` = '1';查詢結果如下，耗時1.536s：
(2) 提取慢查詢sql
分析慢查詢?nèi)罩疚募?/p>
cat fd2f83b86d9b-slow.log
通過分析這個文件，可以找到所有的慢查詢sql。我們剛剛執(zhí)行的sql也在其中，查詢耗時1.535383s。
(3) 分析sql執(zhí)行計劃
① Explain字段分析
?Ⅰsql的執(zhí)行計劃主要是用Mysql提供的explain工具命令來分析，首先來看一下explain分析sql的結果重點需要關注哪些字段
select_type：查詢類型，主要用來分辨查詢的類型是普通查詢還是聯(lián)合查詢還是子查詢。
- SIMPLE（簡單的查詢，不包含子查詢和 union）
- PRIMARY（查詢中若包含任何復雜的子查詢，最外層查詢則被標記為 primary）
- SUBQUERY（在 select 或者 where 列表中包含子查詢，不在from子句中），
- DERIVED（復雜查詢的FROM子句中的查詢標記為DERIVED，MySql會將結果放在一個臨時表中、也稱為派生表）
- UNION（復雜查詢中出現(xiàn)在UNION后面的查詢標記為UNION）
?type（重要）訪問類型，表示以何種方式去訪問數(shù)據(jù)庫，從這個子段可以看出是否走索引，還是全表掃描，查詢效率從高到低為：
- NULL：表示在優(yōu)化階段就可以分解語句，此時在執(zhí)行分段都不需要掃描表和索引，直接就可以拿到結果。如：查詢主鍵id的最小值（select min(id) from table ），這里不需要掃描就可以從索引中直接拿到第一個id就是最小id
- system/const: 表示在優(yōu)化階段可以直接優(yōu)化為常量，比如：select * from table where id = 5 這個查詢可以優(yōu)化為 select 5 as id , name,... from table where id =5 ,這時id列的值其實已經(jīng)確定了
- eq_ref：表示使用了唯一索引或者主鍵索引時，如：select * from table where id =3 ,因為id是主鍵索引，已經(jīng)可以確定結果就只可能最多為一個數(shù)據(jù)。
- ref：表示使用了某個索引與一個具體的值比較，如：select * from table where name='tom' 這時不能確定結果的數(shù)量，但是這種查詢可以掃描索引來得到結果，且結果在索引中一定是連續(xù)的
- rang：表示使用索引時是一個范圍掃描，select * from table where id > 50 or id<10 ,這里就需要對索引進行范圍掃描。
- index：索引掃描，直接掃描一個二級索引拿到結果，一般為覆蓋索引查詢。
- all：全表掃描
?key（重要）：這一列顯示 Mysql 實際采用哪個索引來優(yōu)化對該表的訪問，即實際使用的索引，如果為 null ，則表示沒有使用索引
?rows（重要）：查詢所需要讀取數(shù)據(jù)的行數(shù)，這個參數(shù)很重要，直接反映 sql 查找了多少數(shù)據(jù)，在完成目的的情況下越少越好
?extra：額外信息
- using index : 查詢使用了覆蓋索引
- using where：表示儲存引擎會把結果返回的Mysql查詢服務器，然后使用where條件進行過濾
- using temporary： 對查詢結果進行排序或者去重等操作時使用了臨時表來保存中間結果，查詢完成之后刪除
- using filesort：說明 mysql 無法利用索引進行排序，只能利用外部文件排序算法進行排序，會消耗額外的位置
② explain分析原始sql
對慢sql執(zhí)行以下語句進行分析：
explain select * from t_order t where customer_id like '%000922' and t.create_time = '2024-10-29 10:13:49' and t.`status` = '1';執(zhí)行結果如下：
從結果可以看到，查詢語句是一個簡單查詢語句， 但是type字段顯示為ALL，表明這個sql查詢并沒有走索引，并且rows字段顯示查詢了4831176行數(shù)據(jù)，查詢的數(shù)據(jù)量大，所以查詢效率很低。
(4) 檢查表和索引
從上一步分析來看這個查詢并沒有走索引，所以會很慢。而我們是要查詢customer_id 最后編號為“000922”且create_time = '2024-10-29 10:13:49'以及`status` = '1'的記錄，回顧我們剛才所建的表，其實我們是在customer_id，status，以及create_time字段建立了聯(lián)合索引
INDEX `idx_custnum_status_createtime`(`customer_id` ASC, `status` ASC, `create_time` ASC) USING BTREE而這里說明查詢條件導致了這個聯(lián)合索引沒有生效，進一步分析，sql語句中的customer_id like '%000922'條件出現(xiàn)了問題，like語句導致了聯(lián)合索引不遵從最左匹配原則，導致索引失效。進一步分析表數(shù)據(jù)的特點，這里的customer_id后面的編號是遞增的，且唯一的，但是前綴都是一樣的。這里我們完全可以用=查詢來代替like語句查詢，讓聯(lián)合索引符合最左匹配原則
(5) sql優(yōu)化
將原始sql的where條件中的customer_id like '%000922'改為customer_id = 'CUST000922'，修改完以后，sql如下：
select * from t_order t where customer_id = 'CUST000922' and t.create_time = '2024-10-29 10:13:49' and t.`status` = '1';(6) 方案驗證
執(zhí)行優(yōu)化后的查詢sql，查詢結果如下：
查詢耗時0.003s，整體查詢性能了500多倍。
進一步分析sql的執(zhí)行計劃：
explain select * from t_order t where customer_id = 'CUST000922' and t.create_time = '2024-10-29 10:13:49' and t.`status` = '1';分析結果如下：
優(yōu)化之后，可以看到但是type字段顯示為ref，走了索引，并且rows字段為11，不再是使用的全表掃描，檢索的行數(shù)大量減少，很大程度提升了效率。
二、小結
慢SQL是業(yè)務場景中非常常見且很重要的一個問題，掌握系統(tǒng)化的慢SQL排查方法非常關鍵。以下是一個體系化的排查路徑圖：
總的來說，我們首先要開啟慢查詢?nèi)罩荆缓笸ㄟ^慢查詢?nèi)罩径ㄎ坏铰膕ql語句，最后通過explain工具分析出慢sql的根因，最后再針對性的來優(yōu)化sql語句，如果是對于表數(shù)據(jù)量太大的情況，還需要結合一些其他的策略，比如分庫分表。總之，有了體系的方法之后，就可以有效解決業(yè)務場景下的慢SQL問題。
