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

一、前言

10年积累的网站制作、成都做网站经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先建设网站后付款的网站建设流程，更有泸水免费网站建设让你可以放心的选择与我们合作。

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

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

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`) 
    2.

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

二、分析

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

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

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

 
 
 
      
  
  
  1. mysql > desc select *  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' ; 
    2.   
  
  
  2. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
    3.   
  
  
  3. | id | select_type | table     | type | possible_keys  | key  | key_len |  ref   | rows   |  Extra    | 
    4.   
  
  
  4. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
    5.   
  
  
  5. | 1 | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL | 980626 |  Using where  | 
    6.   
  
  
  6. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
    7.   
  
  
  7. 1  row  in set ( 0.00  sec) 
    8.   
  
  
  8. -- 只查询biz_date 
    9.   
  
  
  9. -- 关键点：rows: 980626 ;type:ALL  
    10.   
  
  
  10. mysql > desc   select  *  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' and  status =  2 ; 
    11.   
  
  
  11. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
    12.   
  
  
  12. | id | select_type | table     | type | possible_keys  | key  | key_len | ref   | rows   |  Extra      | 
    13.   
  
  
  13. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
    14.   
  
  
  14. | 1  | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL |  980632  |  Using where | 
    15.   
  
  
  15. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
    16.   
  
  
  16. 1  row  in set ( 0.00  sec) 
    17.   
  
  
  17. -- 查询biz_date + status  
    18.   
  
  
  18. -- 关键点：rows: 980632 ;type:ALL   
    19.   
  
  
  19. mysql > desc  select  *  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' and  status =  2  limit 100 ; 
    20.   
  
  
  20. +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 
    21.   
  
  
  21. | id | select_type | table     | type  | possible_keys  | key            | key_len | ref   | rows   |  Extra               | 
    22.   
  
  
  22. +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 
    23.   
  
  
  23. | 1 | SIMPLE      | testtable | range | idx_bizdate_st | idx_bizdate_st |  6        | NULL |  490319  |  Using  index condition | 
    24.   
  
  
  24. +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 
    25.   
  
  
  25. 1  row  in set ( 0.00  sec) 
    26.   
  
  
  26. -- 查询biz_date + status+ limit  
    27.   
  
  
  27. -- 关键点：rows: 490319 ;   
    28.   
  
  
  28. mysql >  select  count(*)  from  testtable   WHERE biz_date <=  '2017-08-21 00:00:00' and  status =2 ; 
    29.   
  
  
  29. +----------+ 
    30.   
  
  
  30. | count(*) | 
    31.   
  
  
  31. +----------+ 
    32.   
  
  
  32. | 0 | 
    33.   
  
  
  33. +----------+ 
    34.   
  
  
  34. 1  row  in set ( 0.34  sec) 
    35.   
  
  
  35. mysql >  select  count(*)  from  testtable   WHERE biz_date <= '2017-08-21 00:00:00' ; 
    36.   
  
  
  36. +----------+ 
    37.   
  
  
  37. | count(*) | 
    38.   
  
  
  38. +----------+ 
    39.   
  
  
  39. |  970183  | 
    40.   
  
  
  40. +----------+ 
    41.   
  
  
  41. 1  row in set  ( 0.33  sec) 
    42.   
  
  
  42. mysql > select  count(*)from  testtable; 
    43.   
  
  
  43. +----------+ 
    44.   
  
  
  44. | count(*) | 
    45.   
  
  
  45. +----------+ 
    46.   
  
  
  46. |  991421  | 
    47.   
  
  
  47. +----------+ 
    48.   
  
  
  48. 1  row  in set  ( 0.19  sec) 
    49.   
  
  
  49. mysql >  select  distinct biz_status  from  whwtestbuffer; 
    50.   
  
  
  50. +------------+ 
    51.   
  
  
  51. | biz_status | +-
    52.   
  
  
  52. -----------+ 
    53.   
  
  
  53. | 1  | 
    54.   
  
  
  54. | 2  | 
    55.   
  
  
  55. |   4  | 
    56.   
  
  
  56. +------------+ 
    57.

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

通过 biz_date 预估出来的行数 和 biz_date + 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.   
  
  
  2. Empty set (0.79 sec)
    3.   
  
  
  3. mysql > select * from testtable force index(idx_bizdate_st) WHERE biz_date <= '2017-08-21 00:00:00' and status = 2;
    4.   
  
  
  4. Empty set (0.16 sec) 
    5.

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

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

大家应该都听说过要选择性好的字段放在组合索引的最前面?

是的，相对于 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.   
  
  
  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.   
  
  
  4. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
    5.   
  
  
  5. | id | select_type | table            | type  | possible_keys  | key            | key_len |  ref   | rows |  Extra              | 
    6.   
  
  
  6. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
    7.   
  
  
  7. |1  | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st | 6        | NULL |   789  |  Using index condition | 
    8.   
  
  
  8. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
    9.   
  
  
  9. 1  row  in set  ( 0.00  sec) 
    10.   
  
  
  10. -- rows降低了很多，乖乖的走了索引 
    11.   
  
  
  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.   
  
  
  12. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
    13.   
  
  
  13. | id | select_type | table            | type  | possible_keys  | key            | key_len | ref   | rows |  Extra               | 
    14.   
  
  
  14. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
    15.   
  
  
  15. |1  | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st |  5        | NULL |  1318  |  Using  index condition | 
    16.   
  
  
  16. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
    17.   
  
  
  17. 1 row  in set  ( 0.00  sec) 
    18.   
  
  
  18. -- 即使没有status，也是肯定走索引啦 
    19.

三、小结

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

标题名称：一个不可思议的MySQL慢查分析与解决
标题路径：http://www.shufengxianlan.com/qtweb/news30/482730.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

手机网站建设知识

分类信息网站

成都网站建设公司　　 　四川成都展柜定制厂家　　 　资阳网站建设　　 　江油网站建设　　 　珉田数据中心　　 　四川和正源　　 　消防维保　　 　全网整合营销推广　　 　成都网站建设　　 　成都西信机房　　 　虚拟主机　　 　成都网站制作　　 　产品包装设计　　 　鼎尚理发店　　 　企业手机网站建设　　 　成都定制网站　　 　益鑫餐饮　　 　圣月阀门　　 　虚拟主机　　 　大英网站建设