一個不可思議的MySQL慢查分析與解決

作者：Harvey 2017-11-08 14:07:45

我原本打算用 hint，強制讓他走索引，但是實際上強制走索引的執行時間并不能帶來滿意的效果。結合業務邏輯，來優化 SQL，是最好的方式，也是終極法寶，一定要好好利用。

[[209138]]

一、前言

開發需要定期的刪除表里一定時間以前的數據，SQL 如下

mysql > delete from testtable WHERE biz_date <= '2017-08-21 00:00:00' AND status = 2 limit 500\G

mysql > delete from testtable WHERE biz_date <= '2017-08-21 00:00:00' AND status = 2 limit 500\G前段時間在優化的時候，已經在相應的查詢條件上加上了索引

KEY `idx_bizdate_st` (`biz_date`,`status`)

但是實際執行的 SQL 依然非常慢，為什么呢，我們來一步步分析驗證下

二、分析

表上的字段既然都有索引，那么按照之前的文章分析，是兩個字段都可以走上索引的。如果有疑問，請參考文章 10 分鐘讓你明白 MySQL 是如何利用索引的

既然能夠利用索引，表的總大小也就是 200M 左右，那么為什么形成了慢查呢?

我們查看執行計劃，去掉 limit 后，發現他選擇了走全表掃描。

mysql > desc select *  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' ; 
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
| id | select_type | table     | type | possible_keys  | key  | key_len |  ref   | rows   |  Extra    | 
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
| 1 | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL | 980626 |  Using where  | 
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
1  row  in set ( 0.00  sec) 
-- 只查詢biz_date 
-- 關鍵點：rows: 980626 ;type:ALL  
mysql > desc   select  *  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' and  status =  2 ; 
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
| id | select_type | table     | type | possible_keys  | key  | key_len | ref   | rows   |  Extra      | 
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
| 1  | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL |  980632  |  Using where | 
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
1  row  in set ( 0.00  sec) 
-- 查詢biz_date + status  
-- 關鍵點：rows: 980632 ;type:ALL   
mysql > desc  select  *  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' and  status =  2  limit 100 ; 
+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 
| id | select_type | table     | type  | possible_keys  | key            | key_len | ref   | rows   |  Extra               | 
+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 
| 1 | SIMPLE      | testtable | range | idx_bizdate_st | idx_bizdate_st |  6        | NULL |  490319  |  Using  index condition | 
+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 
1  row  in set ( 0.00  sec) 
-- 查詢biz_date + status+ limit  
-- 關鍵點：rows: 490319 ;   
mysql >  select  count(*)  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' and  status =2 ; 
+----------+ 
| count(*) | 
+----------+ 
|        0 | 
+----------+ 
1  row  in set ( 0.34  sec) 
mysql >  select  count(*)  from  testtable   WHERE biz_date <= '2017-08-21 00:00:00' ; 
+----------+ 
| count(*) | 
+----------+ 
|  970183  | 
+----------+ 
1  row in set  ( 0.33  sec) 
mysql > select  count(*)from  testtable; 
+----------+ 
| count(*) | 
+----------+ 
|  991421  | 
+----------+ 
1  row  in set  ( 0.19  sec) 
mysql >  select  distinct biz_status  from  whwtestbuffer; 
+------------+ 
| biz_status | +-
-----------+ 
|         1  | 
|         2  | 
|         4  | 
+------------+

通過以上查詢，我們可以發現如下幾點問題：

通過 biz_date 預估出來的行數和 biz_date + status=2 預估出來的行數幾乎一樣，為 98w。

實際查詢表 biz_date + status=2 一條記錄都沒有。

整表數據量達到了99萬，MySQL 發現通過索引掃描需要98w行(預估)

因此，MySQL 通過統計信息預估的時候，發現需要掃描的索引行數幾乎占到了整個表，放棄了使用索引，選擇了走全表掃描。

那是不是他的統計信息有問題呢?我們重新收集了下表統計信息，發現執行計劃的預估行數還是一樣，猜測只能根據組合索引的***個字段進行預估(待確定)

那我們試下直接強制讓他走索引呢?

mysql > select * from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;
Empty set (0.79 sec)
mysql > select * from testtable force index(idx_bizdate_st) WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;
Empty set (0.16 sec)

我們發現，強制指定索引后，查詢耗時和沒有強制索引比較，的確執行速度快了很多，因為沒有強制索引是全表掃描嘛!但是!依然非常慢!

那么還有什么辦法去優化這個本來應該很快的查詢呢?

大家應該都聽說過要選擇性好的字段放在組合索引的最前面?

是的，相對于 status 字段，biz_date 的選擇性更加不錯，那組合索引本身已經沒有好調整了

那，能不能讓他不要掃描索引的那么多范圍呢?之前的索引模型中也說過，MySQL 是通過索引去確定一個掃描范圍，如果能夠定位到盡可能小的范圍，那是不是速度上會快很多呢?

并且業務邏輯上是定期刪除一定日期之前的數據。所以邏輯上來說，每次刪除都是只刪除一天的數據，直接讓 SQL 掃描一天的范圍。那么我們就可以改寫 SQL 啦!

mysql > select  *  from  testtable WHERE biz_date >=  '2017-08-20 00:00:00' and  biz_date <= '2017-08-21 00:00:00' and  status =  ; 
Empty set ( 0.00  sec) 
mysql > desc  select  *  from  testtable WHERE biz_date >= '2017-08-20 00:00:00' and  biz_date <=  2017-08-21 00:00:00' and  status =  2 ; 
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
| id | select_type | table            | type  | possible_keys  | key            | key_len |  ref   | rows |  Extra              | 
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
|1  | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st | 6        | NULL |   789  |  Using index condition | 
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
1  row  in set  ( 0.00  sec) 
-- rows降低了很多，乖乖的走了索引 
mysql > desc select  *  from  testtable WHERE biz_date >= '2017-08-20 00:00:00' and biz_date <='2017-08-21 00:00:00'  ; 
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
| id | select_type | table            | type  | possible_keys  | key            | key_len | ref   | rows |  Extra               | 
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
|1  | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st |  5        | NULL |  1318  |  Using  index condition | 
+----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
1 row  in set  ( 0.00  sec) 
-- 即使沒有status，也是肯定走索引啦

三、小結

這個問題，我原本打算用 hint，強制讓他走索引，但是實際上強制走索引的執行時間并不能帶來滿意的效果。結合業務邏輯，來優化 SQL，是***的方式，也是***法寶，一定要好好利用。不了解業務的 DBA，不是一個好 DBA... 繼續去補業務知識去了。

責任編輯：龐桂玉來源：數據庫開發