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

前言

开发需要定期的删除表里一定时间以前的数据,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 whwtestbuffer;
+------------+
| biz_status |
+------------+
|          1 |
|          2 |
|          4 |
+------------+

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

通过 bizdate 预估出来的行数 和 bizdate + 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 的选择性更加不错,那组合索引本身已经没有好调整了

那,能不能让他不要扫描索引的那么多范围呢?之前的索引模型中也说过,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... 继续去补业务知识去了。。

原文发布于微信公众号 - 程序猿DD(didispace)

原文发表时间:2017-09-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏企鹅号快讯

数据分析师必备的数据提取技能

数据分析师必备技能SQL 在数据分析的整个流程中,数据获取是不可或缺的一环,那么作为数据分析师,我们不仅仅需要了解如何获取二手数据,还必须掌握如何从数据库中获取...

27410
来自专栏数据之美

关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)

1、两个同样结构的语句一个没有用到索引的问题: 查1到20号的就不用索引,查1到5号的就用索引,为什么呢?不稳定? mysql> expla...

3338
来自专栏互联网开发者交流社区

SQL触发器实例(下)

1384
来自专栏后端技术探索

MySQL优化的奇技淫巧之STRAIGHT_JOIN

通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下:

1041
来自专栏小怪聊职场

MySQL(八)|MySQL中In与Exists的区别(2)

1723
来自专栏一个爱吃西瓜的程序员

学习SQL【6】-复杂查询

到目前为止,我们学习了表的创建、查询和更新等数据库的基本操作方法。现在我们将会在这些基本方法的基础上,学习一些实际应用的方法。 一:视图 1:视图和表 表中存...

3409
来自专栏数据和云

SQL Server 2008处理隐式数据类型转换在执行计划中的增强

作者 | 邹建,资深数据库专家,精通各项 SQL Server 技术,具有丰富的管理、维护、优化能力以及业务应用经验。他一直热心于技术知识的分享、传播,持续活跃...

773
来自专栏杨建荣的学习笔记

通过图表简化sql语句的表关联(r4笔记第70天)

在之前的博文中分享过一个执行了两天的一条sql语句,走了两个大表的扫描,导致执行时间很长,通过简化sql做了不小的改进,今天我们来看看还可以做些什么。 上次简化...

3614
来自专栏杨建荣的学习笔记

相差数十倍的SQL性能分析(r11笔记第98天)

今天处理开发同学提交的一个数据查询需求,看起来是一个很常规的SQL,但是有一点不同的是,他们提供了两份文件,一份是一个id列表,大概有3000多个id值,...

3458
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4496

扫码关注云+社区

领取腾讯云代金券