一个不可思议的MySQL慢查分析与解决

作者：Harvey 编辑：Moon 原文：http://fordba.com/optimize-an-amazing-mysql-slowlog.html

前言

开发需要定期的删除表里一定时间以前的数据，SQL如下

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

前段时间在优化的时候，已经在相应的查询条件上加上了索引

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

但是实际执行的SQL依然非常慢，为什么呢，我们来一步步分析验证下

---

分析

表上的字段既然都有索引，那么按照之前的文章分析，是两个字段都可以走上索引的。如果有疑问，请参考文章 10分钟让你明白MySQL是如何利用索引的

既然能够利用索引，表的总大小也就是200M左右，那么为什么形成了慢查呢？

我们查看执行计划，去掉limit 后，发现他选择了走全表扫描。

1. mysql > desc  select * from testtable   WHERE biz_date <= '2017-08-21 00:00:00';

2. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+

3. | id | select_type | table     | type | possible_keys  | key  | key_len | ref  | rows   | Extra       |

4. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+

5. |  1 | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL | 980626 | Using where |

6. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+

7. 1 row in set (0.00 sec)

8. -- 只查询biz_date

9. -- 关键点：rows:980626;type:ALL

10. mysql > desc  select * from testtable   WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;

11. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+

12. | id | select_type | table     | type | possible_keys  | key  | key_len | ref  | rows   | Extra       |

13. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+

14. |  1 | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL | 980632 | Using where |

15. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+

16. 1 row in set (0.00 sec)

17. -- 查询biz_date + status

18. -- 关键点：rows:980632;type:ALL  

19. mysql > desc  select * from testtable   WHERE biz_date <= '2017-08-21 00:00:00' and status = 2 limit 100;

20. +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+

21. | id | select_type | table     | type  | possible_keys  | key            | key_len | ref  | rows   | Extra                 |

22. +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+

23. |  1 | SIMPLE      | testtable | range | idx_bizdate_st | idx_bizdate_st | 6       | NULL | 490319 | Using index condition |

24. +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+

25. 1 row in set (0.00 sec)

26. -- 查询biz_date + status+ limit

27. -- 关键点：rows:490319;  

28. mysql > select count(*)  from testtable   WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;

29. +----------+

30. | count(*) |

31. +----------+

32. |        0 |

33. +----------+

34. 1 row in set (0.34 sec)

35. mysql > select count(*)  from testtable   WHERE biz_date <= '2017-08-21 00:00:00';

36. +----------+

37. | count(*) |

38. +----------+

39. |   970183 |

40. +----------+

41. 1 row in set (0.33 sec)

42. mysql > select count(*)  from testtable;

43. +----------+

44. | count(*) |

45. +----------+

46. |   991421 |

47. +----------+

48. 1 row in set (0.19 sec)

49. mysql > select distinct biz_status from whwtestbuffer;

50. +------------+

51. | biz_status |

52. +------------+

53. |          1 |

54. |          2 |

55. |          4 |

56. +------------+

通过以上查询，我们可以发现如下几点问题：

通过 bizdate 预估出来的行数 和 bizdate + status=2 预估出来的行数几乎一样，为98w。

实际查询表 biz_date + status=2 一条记录都没有。

整表数据量达到了 99万，MySQL发现通过索引扫描需要 98w行（预估）

因此，MySQL通过统计信息预估的时候，发现需要扫描的索引行数几乎占到了整个表，放弃了使用索引，选择了走全表扫描。 那是不是他的 统计信息有问题呢？我们重新收集了下表统计信息，发现执行计划的预估行数还是一样，猜测只能根据组合索引的第一个字段进行预估（待确定）

那我们试下直接强制让他走索引呢？

1. mysql > select * from testtable   WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;

2. Empty set (0.79 sec)

3. mysql > select * from testtable force index(idx_bizdate_st)  WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;

4. Empty set (0.16 sec)

我们发现，强制指定索引后，查询耗时和没有强制索引比较，的确执行速度快了很多，因为没有强制索引是全表扫描嘛！但是！依然非常慢！

那么还有什么办法去优化这个本来应该很快的查询呢？

大家应该都听说过要选择性好的字段放在组合索引的最前面？ 是的，相对于status字段，biz_date 的选择性更加不错，那组合索引本身已经没有好调整了

那，能不能让他不要扫描索引的那么多范围呢？之前的索引模型中也说过，MySQL是通过索引去确定一个扫描范围，如果能够定位到尽可能小的范围，那是不是速度上会快很多呢？

并且业务逻辑上是定期删除一定日期之前的数据。所以逻辑上来说，每次删除都是只删除一天的数据，直接让SQL扫描一天的范围。那么我们就可以改写SQL啦!

1. 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;

2. Empty set (0.00 sec)

3. 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;

4. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+

5. | id | select_type | table            | type  | possible_keys  | key            | key_len | ref  | rows | Extra                 |

6. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+

7. |  1 | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st | 6       | NULL |  789 | Using index condition |

8. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+

9. 1 row in set (0.00 sec)

10. -- rows降低了很多，乖乖的走了索引

11. mysql > desc select * from testtable WHERE biz_date >= '2017-08-20 00:00:00' and biz_date <= '2017-08-21 00:00:00' ;

12. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+

13. | id | select_type | table            | type  | possible_keys  | key            | key_len | ref  | rows | Extra                 |

14. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+

15. |  1 | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st | 5       | NULL | 1318 | Using index condition |

16. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+

17. 1 row in set (0.00 sec)

18. -- 即使没有status，也是肯定走索引啦

---

小结

这个问题，我原本打算用hint，强制让他走索引，但是实际上强制走索引的执行时间并不能带来满意的效果。结合业务逻辑，来优化SQL，是最好的方式，也是终极法宝，一定要好好利用。不了解业务的DBA，不是一个好DBA... 继续去补业务知识去了。。

推荐阅读

长按指纹

一键关注