• 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html
  • 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html

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

技术杂谈 勤劳的小蚂蚁 4个月前 (01-08) 84次浏览 已收录 0个评论 扫描二维码

前言

开发需要定期的删除表里一定时间以前的数据,SQL如下
  1. mysql >deletefrom testtable WHERE biz_date <='2017-08-21 00:00:00'  AND status =2  limit 500G
前段时间在优化的时候,已经在相应的查询条件上加上了索引
  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|Usingwhere|
  6. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+
  7. 1 row inset(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|Usingwhere|
  15. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+
  16. 1 row inset(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 inset(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 inset(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 inset(0.33 sec)
  42. mysql >select count(*)  from testtable;
  43. +----------+
  44. | count(*)|
  45. +----------+
  46. |   991421|
  47. +----------+
  48. 1 row inset(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 一条记录都没有
整表数据量达到了 99MySQL发现通过索引扫描需要 98w(预估)
因此,MySQL通过统计信息预估的时候,发现需要扫描的索引行数几乎占到了整个表,放弃了使用索引,选择了走全表扫描。 那是不是他的 统计信息有问题呢?我们重新收集了下表统计信息,发现执行计划的预估行数还是一样,猜测只能根据组合索引的第一个字段进行预估(待确定)
那我们试下直接强制让他走索引呢?
  1. mysql >select*from testtable   WHERE biz_date <='2017-08-21 00:00:00'and status =2;
  2. Emptyset(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. Emptyset(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. Emptyset(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 inset(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 inset(0.00 sec)
  18. --即使没有status,也是肯定走索引啦

小结

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


丨极客文库, 版权所有丨如未注明 , 均为原创丨
本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
转载请注明原文链接:一个不可思议的MySQL慢查分析与解决
喜欢 (0)
[247507792@qq.com]
分享 (0)
勤劳的小蚂蚁
关于作者:
温馨提示:本文来源于网络,转载文章皆标明了出处,如果您发现侵权文章,请及时向站长反馈删除。

您必须 登录 才能发表评论!

  • 精品技术教程
  • 编程资源分享
  • 问答交流社区
  • 极客文库知识库

客服QQ


QQ:2248886839


工作时间:09:00-23:00