[[209138]]

一、前言

開(kāi)發(fā)需要定期的刪除表里一定時(shí)間以前的數(shù)據(jù)，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前段時(shí)間在優(yōu)化的時(shí)候，已經(jīng)在相應(yīng)的查詢(xún)條件上加上了索引

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

但是實(shí)際執(zhí)行的 SQL 依然非常慢，為什么呢，我們來(lái)一步步分析驗(yàn)證下

二、分析

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

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

我們查看執(zhí)行計(jì)劃，去掉 limit 后，發(fā)現(xiàn)他選擇了走全表掃描。

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) 
-- 只查詢(xún)biz_date 
-- 關(guān)鍵點(diǎn)：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) 
-- 查詢(xún)biz_date + status  
-- 關(guān)鍵點(diǎn)：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) 
-- 查詢(xún)biz_date + status+ limit  
-- 關(guān)鍵點(diǎn)：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  | 
+------------+ 

通過(guò)以上查詢(xún)，我們可以發(fā)現(xiàn)如下幾點(diǎn)問(wèn)題：

通過(guò) biz_date 預(yù)估出來(lái)的行數(shù) 和 biz_date + status=2 預(yù)估出來(lái)的行數(shù)幾乎一樣，為 98w。

實(shí)際查詢(xún)表 biz_date + status=2 一條記錄都沒(méi)有。

整表數(shù)據(jù)量達(dá)到了99萬(wàn)，MySQL 發(fā)現(xiàn)通過(guò)索引掃描需要98w行(預(yù)估)

因此，MySQL 通過(guò)統(tǒng)計(jì)信息預(yù)估的時(shí)候，發(fā)現(xiàn)需要掃描的索引行數(shù)幾乎占到了整個(gè)表，放棄了使用索引，選擇了走全表掃描。

那是不是他的統(tǒng)計(jì)信息有問(wèn)題呢?我們重新收集了下表統(tǒng)計(jì)信息，發(fā)現(xiàn)執(zhí)行計(jì)劃的預(yù)估行數(shù)還是一樣，猜測(cè)只能根據(jù)組合索引的***個(gè)字段進(jìn)行預(yù)估(待確定)

那我們?cè)囅轮苯訌?qiáng)制讓他走索引呢?

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) 

我們發(fā)現(xiàn)，強(qiáng)制指定索引后，查詢(xún)耗時(shí)和沒(méi)有強(qiáng)制索引比較，的確執(zhí)行速度快了很多，因?yàn)闆](méi)有強(qiáng)制索引是全表掃描嘛!但是!依然非常慢!

那么還有什么辦法去優(yōu)化這個(gè)本來(lái)應(yīng)該很快的查詢(xún)呢?

大家應(yīng)該都聽(tīng)說(shuō)過(guò)要選擇性好的字段放在組合索引的最前面?

是的，相對(duì)于 status 字段，biz_date 的選擇性更加不錯(cuò)，那組合索引本身已經(jīng)沒(méi)有好調(diào)整了

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

并且業(yè)務(wù)邏輯上是定期刪除一定日期之前的數(shù)據(jù)。所以邏輯上來(lái)說(shuō)，每次刪除都是只刪除一天的數(shù)據(jù)，直接讓 SQL 掃描一天的范圍。那么我們就可以改寫(xiě) 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) 
-- 即使沒(méi)有status，也是肯定走索引啦 

三、小結(jié)

這個(gè)問(wèn)題，我原本打算用 hint，強(qiáng)制讓他走索引，但是實(shí)際上強(qiáng)制走索引的執(zhí)行時(shí)間并不能帶來(lái)滿(mǎn)意的效果。結(jié)合業(yè)務(wù)邏輯，來(lái)優(yōu)化 SQL，是***的方式，也是***法寶，一定要好好利用。不了解業(yè)務(wù)的 DBA，不是一個(gè)好 DBA... 繼續(xù)去補(bǔ)業(yè)務(wù)知識(shí)去了。