一個奇怪的MySQL慢查詢，打懵了不懂業務的DBA！

前言

最近，開發人員需要定期的刪除表裡一定時間以前的數據，SQL如下：

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

前段時間在優化的時候，我們已經在相應的查詢條件上加上了索引，如下：

<code>KEY 
`idx_bizdate_st`
 (
`biz_date`
,
`status`
)/<code>

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

分析

表上的字段既然都有索引，那麼按照之前的文章分析，是兩個字段都可以走上索引的。

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

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

<code>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)

 
 

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)

 
 

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)

 
 

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 testtable;
+ 
| biz_status |
+ 
|          
1
 |
|          
2
 |
|          
4
 |
+/<code>

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

• 通過 biz_date 預估出來的行數 和 biz_date + status=2 預估出來的行數幾乎一樣，為98w。
• 實際查詢表 biz_date + status=2 一條記錄都沒有。
• 整表數據量達到了99萬，MySQL發現通過索引掃描需要98w行（預估）

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

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

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

<code>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
)
/<code>

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

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

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

選擇性好的索引在前面並不是對所有的場景都通用的，這個場景可以將status放前面，sql速度會更快。

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

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

<code>mysql > select * from testtable WHERE biz_date >= 
'2017-08-20 00:00:00'
 
and
 biz_date <= 
'2017-08-21 00:00:00'
 
and
 status = 
2
;
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)/<code>

-- 即使沒有status，也是肯定走索引啦

小結

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