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

数据库 MySQL
我原本打算用 hint,强制让他走索引,但是实际上强制走索引的执行时间并不能带来满意的效果。结合业务逻辑,来优化 SQL,是最好的方式,也是终极法宝,一定要好好利用。

[[209138]]

一、前言

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

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

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. | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL | 980626 |  Using where  | 
  6. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
  7.  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 =  
  11. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
  12. | id | select_type | table     | type | possible_keys  | key  | key_len | ref   | rows   |  Extra      | 
  13. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
  14.  | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL |  980632  |  Using where 
  15. +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 
  16.  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 =   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 |         | NULL |  490319  |  Using  index condition | 
  24. +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 
  25.  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 =
  29. +----------+ 
  30. count(*) | 
  31. +----------+ 
  32. | 0 | 
  33. +----------+ 
  34.  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.  row in set  ( 0.33  sec) 
  42. mysql > select  count(*)from  testtable; 
  43. +----------+ 
  44. count(*) | 
  45. +----------+ 
  46. 991421  | 
  47. +----------+ 
  48.  row  in set  ( 0.19  sec) 
  49. mysql >  select  distinct biz_status  from  whwtestbuffer; 
  50. +------------+ 
  51. | biz_status | +-
  52. -----------+ 
  53. |  | 
  54. |  | 
  55. |    | 
  56. +------------+ 

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

通过 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. 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. 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 =  
  4. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
  5. | id | select_type | table            | type  | possible_keys  | key            | key_len |  ref   | rows |  Extra              | 
  6. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
  7. | | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st |        | NULL |   789  |  Using index condition | 
  8. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
  9.  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. | | SIMPLE      | testtable        | range | idx_bizdate_st | idx_bizdate_st |         | NULL |  1318  |  Using  index condition | 
  16. +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 
  17. 1 row  in set  ( 0.00  sec) 
  18. -- 即使没有status,也是肯定走索引啦 

 

三、小结

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

责任编辑:庞桂玉 来源: 数据库开发
相关推荐

2017-03-21 08:52:20

神经网络声誉

2013-10-10 13:07:25

方物

2022-01-24 15:57:34

Python返回功能代码

2011-07-18 13:35:14

HTML 5

2013-07-31 15:06:58

未来的WebWebGLWeb

2011-02-23 08:50:22

C#.NETdynamic

2010-07-15 16:21:03

不可思议的服务器

2021-11-10 06:38:01

Python链式操作

2020-07-02 15:40:11

Spring BootJar包Java

2014-01-14 10:33:42

开源硬件开源

2023-04-06 09:44:00

ChatGPT行业质量

2012-05-16 17:28:32

智能手机

2016-07-06 11:56:52

思科汉堡光纤骨干网

2014-07-26 22:18:51

2023-04-04 22:31:11

GPT-5人工智能

2021-03-03 07:12:47

Windows10操作系统微软

2012-02-13 11:01:27

N9Android 4.0

2024-04-07 00:00:00

亿级数据ES

2014-11-13 10:08:21

2020-09-16 10:24:37

物联网环保应用IOT
点赞
收藏

51CTO技术栈公众号