关于mysql:一次偶然机会发现的MySQL负优化

1次阅读

共计 2482 个字符，预计需要花费 7 分钟才能阅读完成。

文章最开始先给大家两条 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;

构造简单明了，其中 age 和create_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 的优化说起。

在剖析 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.

大抵意思就是 LIMIT 与ORDER BY一起应用 MySQL 会在找到 LIMIT 设定的值后立刻返回。尽管没有找到具体的原理性的解释，然而从上述的这个形容中咱们也可能大抵了解这个思路了。

在 MySQL 中LIMIT与 ORDER 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 的背地做了很多的优化，然而这部分对于不够相熟理解的人来说的确是太黑盒，遇到相似的问题排查也很艰难。兴许这就是程序员成长路上的必经之路吧。

正文完