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时：

• 优化器可能优先选择主键索引（因ORDER BY id1），遍历索引后逐行判断id2>8，导致大量无效扫描；
• 更优的选择是使用id2索引过滤出符合条件的记录，再对结果排序后取前 2 条，但优化器可能因 “避免排序” 而忽略此方案。

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

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

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

• 开启（默认）：prefer_ordering_index=on，优化器优先选择排序相关索引，以减少排序操作；
• 关闭：prefer_ordering_index=off，优化器弱化对排序索引的偏好，更倾向于选择过滤性好的索引，即使需要额外排序。

参数设置方式：

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

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

1. 测试环境与数据准备

• MySQL 版本：8.0.30

• 测试表结构：

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');

• 测试查询：SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2

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

执行计划关键信息：

• type: index：使用索引扫描（主键索引PRIMARY）；
• key: PRIMARY：选择主键索引；
• Extra: Using where：通过主键索引扫描后，逐行过滤id2>8。

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

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

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

执行计划关键信息：

• type: range：使用范围扫描（索引i）；
• key: i：选择id2的联合索引；
• Extra: Using index condition; Using filesort：利用索引过滤id2>8（ICP 特性减少 IO），再对结果排序取前 2 条。

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

四、适用场景与最佳实践

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

• 建议关闭的场景：

  • WHERE条件有高效过滤索引（如id2），但ORDER BY字段为其他索引（如主键）；
  • 表数据量大，排序索引与过滤字段无关，优先过滤可大幅减少数据量；
  • 执行计划显示type: index且rows值过大（全表扫描风险）。

• 建议开启的场景：

  • ORDER BY字段的索引同时包含过滤条件（如联合索引(id1, id2)），可同时满足过滤和排序；
  • 数据量小，排序索引扫描的成本低于 “过滤 + 排序”。

• 运维建议：

  通过EXPLAIN对比参数开关时的执行计划，判断是否存在 “无效排序索引偏好”；

  仅在确认性能问题时临时关闭参数（会话级别），避免全局设置影响其他查询；

  结合慢查询日志，定位因LIMIT+ORDER BY导致的低效查询，针对性优化。

五、总结

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

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

到此这篇关于MySQL 8.0 中 LIMIT 优化新特性 的文章就介绍到这了,更多相关mysql limit优化内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

最新评论