为什么ORDER BY和LIMIT一起使用 会导致查询变得异常缓慢？

一、背景

最近在Stack Overflow 上看到一个有趣的问题：

Why does ORDER BY and LIMIT 1 slow down a MySQL query so much?

看似差别不大的SQL执行效率却天差地别，尤其是 加了 order by 和 limit 后 执行时间是之前的上百倍，这是为什么呢？

SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874));
/* Rows: 0  Time: 0,094 sec. */
SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874)) LIMIT 1;
/* Rows: 0  Time: 0,063 sec. */
SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874)) ORDER BY mailing;
/* Rows: 0  Time: 0,125 sec. */
SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874)) ORDER BY mailing LIMIT 1;
/* Rows: 0  Time: 45,500 sec. */

数据库信息如下：

CCREATE TABLE `mytable` (
    `data` VARCHAR(32) NOT NULL COLLATE 'ascii_bin',
    `mailing` INT(10,0) NOT NULL,
    `token` VARCHAR(64) NULL DEFAULT NULL COLLATE 'ascii_bin',
    `section` INT(10,0) NOT NULL,
    `expiry` INT(10,0) NULL DEFAULT NULL,
    PRIMARY KEY (`data`) USING BTREE,
    INDEX `mailing_CS` (`mailing`) USING BTREE,
    INDEX `section_CS` (`section`) USING BTREE,
    CONSTRAINT `mailing_CS` FOREIGN KEY (`mailing`) REFERENCES `mydata`.`mailings` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE,
    CONSTRAINT `section_CS` FOREIGN KEY (`section`) REFERENCES `mydata`.`sections` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)ENGINE=InnoDB;

该数据库运行在MySQL 8.0.20和Ubuntu 20.04上，总数据量： 两百万 ，section=210874 的数据有 5.000 条。

二、初步分析

SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874));
/* Rows: 0 Time: 0,094 sec. */
SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874)) LIMIT 1;

前两条 SQL很好理解，使用了section字段上的索引；

/* Rows: 0  Time: 0,125 sec. */
SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874)) ORDER BY mailing;

这一条SQL已明显比前两条慢，猜测大概率 是 受 ORDER BY 影响 使用了mailing字段上的索引；

SELECT * FROM `mydata`.`mytable` WHERE ((token='XFRA1NMDU9XY') AND (section=210874)) ORDER BY mailing LIMIT 1;
/* Rows: 0 Time: 45,500 sec. */

最后一条SQL就有意思了，执行时间是之前的上百倍，猜测大概的执行流程如下：

MySQL看到 order by + limit 组合，就会用 mailing索引，然后逐条回表过滤满足where条件的数据，将这些数据进行排序（看数据量大小，可能会用filesort），最后取第一条；

逐条回表 这个操作就要了老命，会产生大量的随机IO，执行效率甚至 比全表扫描还差，至少全表扫描是按照顺序取整块数据。

三、引申

通过上边的例子，我们可以得到一个重要的结论：

索引并不一定能提高查询性能，极端情况下甚至会产生 "负向优化"的效果。

朋友们千万不要迷信索引，在大表上执行 多条件查询+order by limit 语句时，一定要用

explain看看执行计划，有多个索引时 MySQL 优化器选择的索引可能不是最优的。

四、解决方案

回到原问题，解决方案就比较简单了：

一 、创建 （section,token,mailing）或者 （token,section,mailing）联合索引，使MySQL在索引阶段就能完成 数据过滤+排序，而不是大量回表 + filesort。

二、使用子查询，先过滤后排序；

（由于本人比较懒没有在本地实验，感兴趣的朋友可以在本地试试给我留言）

后记：

Bug: https://bugs.mysql.com/bug.php?id=73837
From: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html

For queries that combine ORDER BY with LIMIT, the optimizer may switch to an index that applies to the ORDER BY. In some cases, the decision to switch was based on a heuristic rather than on cost. The optimizer now uniformly makes the decision whether to switch on a cost basis. This should result in better performance when switching would cause a query to read an entire index or a large part of it to find qualifying rows.
References: See also: Bug #78993, Bug #22108385, Bug #73837, Bug #19579507, Bug #16522053.

翻译后大概意思就是： 对于结合ORDER BY和LIMIT的查询，优化器可能会切换到适用于ORDER BY的索引。在某些情况下，这种索引选择是基于启发式的，而不是基于成本。现在，优化器统一决定是否在成本基础上进行切换。当切换会导致查询读取整个索引或大部分索引来寻找符合条件的行时，这应该会带来更好的性能。

上述问题找到了理论依据，哈哈。