轉載自 For 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依然非常慢，為什麼呢，我們來一步步分析驗證下
二、分析
表上的字段既然都有索引，那麼按照之前的文章分析，是兩個字段都可以走上索引的。如果有疑問，請參考文章
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 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字段，biz_date 的選擇性更加不錯，那組合索引本身已經沒有好調整了
修正：選擇性好的索引在前面并不是對所有的場景都通用的，這個場景可以将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... 繼續去補業務知識去了。。
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)