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

雲栖号資訊：【 點選檢視更多行業資訊

】

在這裡您可以找到不同行業的第一手的上雲資訊，還在等什麼，快來！

前言

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

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依然非常慢，為什麼呢，我們來一步步分析驗證下。

分析

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

既然能夠利用索引，表的總大小也就是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 testtable;
+------------+
| 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放前面，sql速度會更快。

那，能不能讓他不要掃描索引的那麼多範圍呢？之前的索引模型中也說過，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 = 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)

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

小結

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

【雲栖号線上課堂】每天都有産品技術專家分享！

課程位址：

https://yqh.aliyun.com/zhibo

立即加入社群，與專家面對面，及時了解課程最新動态！

【雲栖号線上課堂 社群】

https://c.tb.cn/F3.Z8gvnK

原文釋出時間：2020-04-24

本文作者：網際網路架構師

本文來自：“

網際網路架構師 微信公衆号

”，了解相關資訊可以關注“

網際網路架構師

”