一次偶然机会发现的MySQL“负优化”

文章最开始先给大家两条sql,请猜猜他们执行会有什么区别?

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 1
SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 2

这两条sql看似只是limit的数值不同,但是第一个执行耗时3ms,第二个执行耗时66s,相差2000多倍

故事的起因

今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个离奇的“索引失效”支配的恐惧。

场景复现

由于事情已经过去多年,因此我只能凭借记忆在本地的数据库进行模拟。首先创建数据库school,数据表student

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `student_age_IDX` (`age`) USING BTREE,
  KEY `student_create_time_IDX` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

结构简单明了,其中agecreate_time使用BTREE构建了索引。

在使用存储过程往数据库填充了500w条左右的数据后,我们使用如下的sql来进行测试:

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 1

结果如下:

之后尝试执行如下sql:

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 2

这就是我们开篇提到的那两个sql,性能差距是2000多倍。那么问题来了,为什么limit的值会影响sql性能,并且会差别如此之大?故事要从MySQL的优化说起。

MySQL的“负优化”

在分析sql性能的时候,我们当然最常用的是EXPLAIN,将两个sql分别EXPLAIN,结果如下:


可以看到sql执行计划并无二致,那么为什么执行时间却相差这么远呢?

查找相关文档就可以在MySQL的官网找到如下的解释:

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

大致意思就是LIMITORDER BY一起使用MySQL会在找到LIMIT设定的值后立即返回。虽然没有找到具体的原理性的解释,但是从上述的这个描述中我们也能够大致理解这个思路了。

MySQLLIMITORDER BY是特殊的组合,尤其是当ORDER BY中的存在BTREE索引的情况下。

普通的查询是根据条件进行筛选,然后在结果集中排序,然后获取LIMIT条数的数据,但是在具备上述条件的特殊sql中执行逻辑是这样的,根据ORDER BY字段的B+树索引来查找满足条件的数据,直到凑满LIMIT设定的数值为止,这就存在一个问题,在结果集中的数据大于LIMIT的场景下,这个性能固然是非常棒的,但是如果最后的结果集中的数据小于LIMIT,就会存在永远凑不满的情况,所以最终这个MySQL的性能优化就会变成全表扫描的“负优化”。

根据上述的情况来看的话我们可以大胆猜测,既然是索引导致的优化问题,那么是不是把age字段的索引去掉反而会更快?

手动执行DROP INDEX student_age_IDX ON school.student删除索引,然后执行语句,果然执行速度变成了毫秒级:

查看执行计划发现在执行时使用了create_time的索引,因此其速度也能保持在毫秒级。

然后我们干脆把create_time的索引也去除掉:


可以看到没有索引的情况下耗时也不过是1秒出头,远远不是66秒。可见在这种情况下MySQL的性能优化甚至远远比不上无索引的查询。

总结

其实出现这个问题的场景也不算十分特殊,但是排查原因相当困难。当初是花了好几天查资料翻文档加上不断实验才找到了问题所在。只能说MySQL在解析和执行sql的背后做了很多的优化,但是这部分对于不够熟悉了解的人来说确实是太黑盒,遇到类似的问题排查也很困难。也许这就是程序员成长路上的必经之路吧。

1.1k 声望
14 粉丝
0 条评论
推荐阅读
万字长文~vue+express+mysql带你彻底搞懂项目中的权限控制(附所有源码)
所谓的权限,其实指的就是:用户是否能看到,以及是否允许其对数据进行增删改查的操作,因为现在开发项目的主流方式是前后端分离,所以整个项目的权限是后端权限控制搭配前端权限控制共同实现的

水冗水孚7阅读 1k

分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.2k评论 2

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 13.8k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 656

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.4k

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.2k

Mybatis源码解析之执行SQL语句
通过调用 session.getMapper (AccountMapper.class) 所得到的 AccountMapper 是一个动态代理对象,所以执行 accountMapper.selectByPrimaryKey (1) 方法前,都会被 invoke () 拦截,先执行 invoke () 中的逻辑。

京东云开发者3阅读 768评论 1

封面图
1.1k 声望
14 粉丝
宣传栏