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

在 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导致的低效查询，针对性优化。