MySQL 8.0 中 LIMIT 优化新特性使用场景及最佳实践

在 MySQL 查询优化中，LIMIT子句的使用非常普遍，尤其在分页场景中。但当LIMIT与ORDER BY、GROUP BY结合时，优化器对索引的选择往往直接影响查询性能。MySQL 8.0.21 版本引入的prefer_ordering_index参数，为解决这类场景的性能问题提供了新的控制手段。本文将深入解析该参数的作用机制、实践效果及适用场景。

一、背景：LIMIT 与排序的索引选择困境

在包含LIMIT N、ORDER BY和GROUP BY的查询中，优化器的核心目标是减少排序操作—— 这通常意味着优先选择与ORDER BY字段相关的索引（“排序索引”），利用索引的有序性避免额外排序。

但实际场景中，这种 “最优解” 可能适得其反：若排序索引与WHERE条件中的过滤字段无关，优化器可能会放弃过滤性更好的索引，转而扫描排序索引并回表过滤，最终导致全表扫描式的低效查询。

例如，一张表同时存在主键索引（id1）和过滤字段索引（id2），当查询为SELECT c2 FROM t WHERE id2>8 ORDER BY id1 LIMIT 2时：

在 MySQL 8.0.21 之前，这种索引选择行为无法通过参数干预，只能通过改写 SQL（如延迟关联）优化，灵活性较差。

二、新特性：prefer_ordering_index 参数的作用

MySQL 8.0.21 新增的prefer_ordering_index参数，通过optimizer_switch系统变量控制，用于调整优化器对 “排序索引” 的偏好：

参数设置方式：

-- 开启（默认）
SET optimizer_switch = "prefer_ordering_index=on";
-- 关闭
SET optimizer_switch = "prefer_ordering_index=off";

三、实践验证：参数对执行计划的影响

1. 测试环境与数据准备

CREATE TABLE t (
  id1 BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,  -- 主键索引
  id2 BIGINT NOT NULL,
  c1 VARCHAR(50) NOT NULL,
  c2 VARCHAR(50) NOT NULL,
  INDEX i (id2, c1)  -- 联合索引（过滤字段id2）
);
-- 插入测试数据
INSERT INTO t(id2, c1, c2) VALUES
(1,'a','xfvs'), (2,'bbbb','xfvs'), (3,'cdddd','xfvs'),
(4,'dfdf','xfvs'), (12,'bbbb','xfvs'), (23,'cdddd','xfvs'),
(14,'dfdf','xfvs'), (11,'bbbb','xfvs'), (13,'cdddd','xfvs'),
(44,'dfdf','xfvs'), (31,'bbbb','xfvs'), (33,'cdddd','xfvs'),
(34,'dfdf','xfvs');

2. 参数开启时（默认行为）

-- 确认参数状态
SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';  -- 返回1（开启）
-- 查看执行计划
EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G

执行计划关键信息：

问题：主键索引与id2无关，需扫描大量无关记录后过滤，在大表中会导致严重性能问题。

3. 参数关闭时（优化后）

-- 关闭参数
SET optimizer_switch = "prefer_ordering_index=off";
-- 查看执行计划
EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G

执行计划关键信息：

优势：通过过滤性更好的id2索引减少扫描范围，即使增加排序步骤，整体效率仍高于全表扫描。

四、适用场景与最佳实践

prefer_ordering_index参数并非 “银弹”，需根据具体场景选择是否关闭：

五、总结

MySQL 8.0 引入的prefer_ordering_index参数，为LIMIT与排序结合的查询提供了更精细的优化控制。它的核心价值在于：允许开发者干预优化器对 “排序索引” 的偏好，在 “避免排序” 和 “减少扫描范围” 之间找到平衡。

随着 MySQL 优化器的不断进化，这类参数的出现体现了从 “自动最优” 到 “可控优化” 的趋势。掌握这类特性，能帮助开发者在复杂业务场景中更精准地提升查询性能，避免因优化器的 “想当然” 导致的性能陷阱。

到此这篇关于MySQL 8.0 中 LIMIT 优化新特性 的文章就介绍到这了,