大家好，我是秀才，今天跟大家一起來看一個實際工作和面試中經(jīng)常出現(xiàn)的線上問題：慢SQL問題的定位與排查。大家在網(wǎng)上可能看到的大都是一些方法的總結(jié)，這次我們來點不一樣的，我們一起來實操看一下，線上慢SQL問題究竟是如何定位和解決的。紙上得來終覺淺，絕知此事須躬行，相信經(jīng)過真正實踐之后，大家的感觸和印象一定更深，在后續(xù)的面試工作中也會更加的游刃有余。

在系統(tǒng)開發(fā)中，慢SQL查詢經(jīng)常會遇到，尤其是數(shù)據(jù)量大的業(yè)務(wù)場景下，慢SQL查詢是一個非常常?的性能瓶頸，可能導(dǎo)致應(yīng)用響應(yīng)緩慢，嚴(yán)重影響用戶體驗。這就需要開發(fā)者能夠有效地排查慢SQL問題，慢SQL問題的排查也是有一個比較系統(tǒng)的路徑，從識別問題到優(yōu)化解決。

一、案例分析

我們以一張訂單表，插入一些數(shù)據(jù)，來模擬一次慢sql的整體優(yōu)化路徑

1. 測試環(huán)境

Linux服務(wù)器：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;

表建好以后，看一下表設(shè)計：

3. 數(shù)據(jù)插入

通過存儲過程，向表里插入500w條數(shù)據(jù)：

-- 如果存在該存儲過程，則刪除存儲過程
DROPPROCEDUREIFEXISTS batchInsertBigData;

-- 創(chuàng)建存儲過程

DELIMITER $$

CREATEPROCEDURE batchInsertBigData(IN args INT)
BEGIN
DECLARE i INTDEFAULT1;
DECLARE order_id BIGINTDEFAULT0;

-- 開啟事務(wù)
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')), -- 假設(shè)有1000個客戶編號循環(huán)使用
      CONCAT('Customer-', (i % 1000) + 1), -- 客戶名稱
      ROUND((RAND() * 100) + 100, 2), -- 隨機(jī)生成100到200的訂單金額
      CASEWHEN i % 5 = 0THEN0WHEN i % 3 = 0THEN1ELSE2END, -- 隨機(jī)分配幾種狀態(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%';

結(jié)果顯示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';

查詢結(jié)果如下，耗時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的結(jié)果重點需要關(guān)注哪些字段

select_type：查詢類型，主要用來分辨查詢的類型是普通查詢還是聯(lián)合查詢還是子查詢。

• SIMPLE（簡單的查詢，不包含子查詢和 union）
• PRIMARY（查詢中若包含任何復(fù)雜的子查詢，最外層查詢則被標(biāo)記為 primary）
• SUBQUERY（在 select 或者 where 列表中包含子查詢，不在from子句中），
• DERIVED（復(fù)雜查詢的FROM子句中的查詢標(biāo)記為DERIVED，MySql會將結(jié)果放在一個臨時表中、也稱為派生表）
• UNION（復(fù)雜查詢中出現(xiàn)在UNION后面的查詢標(biāo)記為UNION）

?type（重要）訪問類型，表示以何種方式去訪問數(shù)據(jù)庫，從這個子段可以看出是否走索引，還是全表掃描，查詢效率從高到低為：

• NULL：表示在優(yōu)化階段就可以分解語句，此時在執(zhí)行分段都不需要掃描表和索引，直接就可以拿到結(jié)果。如：查詢主鍵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)可以確定結(jié)果就只可能最多為一個數(shù)據(jù)。
• ref：表示使用了某個索引與一個具體的值比較，如：select * from table where name='tom' 這時不能確定結(jié)果的數(shù)量，但是這種查詢可以掃描索引來得到結(jié)果，且結(jié)果在索引中一定是連續(xù)的
• rang：表示使用索引時是一個范圍掃描，select * from table where id > 50 or id<10 ,這里就需要對索引進(jìn)行范圍掃描。
• index：索引掃描，直接掃描一個二級索引拿到結(jié)果，一般為覆蓋索引查詢。
• all：全表掃描

?key（重要）：這一列顯示 Mysql 實際采用哪個索引來優(yōu)化對該表的訪問，即實際使用的索引，如果為 null ，則表示沒有使用索引

?rows（重要）：查詢所需要讀取數(shù)據(jù)的行數(shù)，這個參數(shù)很重要，直接反映 sql 查找了多少數(shù)據(jù)，在完成目的的情況下越少越好

?extra：額外信息

• using index : 查詢使用了覆蓋索引
• using where：表示儲存引擎會把結(jié)果返回的Mysql查詢服務(wù)器，然后使用where條件進(jìn)行過濾
• using temporary： 對查詢結(jié)果進(jìn)行排序或者去重等操作時使用了臨時表來保存中間結(jié)果，查詢完成之后刪除
• using filesort：說明 mysql 無法利用索引進(jìn)行排序，只能利用外部文件排序算法進(jìn)行排序，會消耗額外的位置

②  explain分析原始sql

對慢sql執(zhí)行以下語句進(jìn)行分析：

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í)行結(jié)果如下：

從結(jié)果可以看到，查詢語句是一個簡單查詢語句， 但是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

而這里說明查詢條件導(dǎo)致了這個聯(lián)合索引沒有生效，進(jìn)一步分析，sql語句中的customer_id like '%000922'條件出現(xiàn)了問題，like語句導(dǎo)致了聯(lián)合索引不遵從最左匹配原則，導(dǎo)致索引失效。進(jì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，查詢結(jié)果如下：

查詢耗時0.003s，整體查詢性能了500多倍。

進(jìn)一步分析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';

分析結(jié)果如下：

優(yōu)化之后，可以看到但是type字段顯示為ref，走了索引，并且rows字段為11，不再是使用的全表掃描，檢索的行數(shù)大量減少，很大程度提升了效率。

二、小結(jié)

慢SQL是業(yè)務(wù)場景中非常常見且很重要的一個問題，掌握系統(tǒng)化的慢SQL排查方法非常關(guān)鍵。以下是一個體系化的排查路徑圖：

總的來說，我們首先要開啟慢查詢?nèi)罩荆缓笸ㄟ^慢查詢?nèi)罩径ㄎ坏铰膕ql語句，最后通過explain工具分析出慢sql的根因，最后再針對性的來優(yōu)化sql語句，如果是對于表數(shù)據(jù)量太大的情況，還需要結(jié)合一些其他的策略，比如分庫分表。總之，有了體系的方法之后，就可以有效解決業(yè)務(wù)場景下的慢SQL問題。