MySQL LIMIT执行机制与结果不一致排查

一、LIMIT的执行过程与索引选择

为了理解LIMIT的行为，我们首先构建一个测试场景。创建包含主键id和二级索引列key1的表：

CREATE TABLE `t_null_index` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `key1` char(1) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

表中预置了大量数据。当执行按key1排序取第一条时：

此时EXPLAIN显示使用了idx_key1索引。因为二级索引有序，MySQL只需定位首条记录并回表即可返回，效率极高。

第1步：深分页（OFFSET较大）时的行为反转

若将查询改为跳过5000条：SELECT * FROM t_null_index ORDER BY key1 LIMIT 5000, 1;

观察执行计划，发现MySQL放弃了使用idx_key1索引，转而进行全表扫描（ALL）并使用文件排序（Using filesort）。这似乎违背直觉：明明索引有序，为何不直接用？

原因分析： MySQL的LIMIT处理发生在Server层。如果使用二级索引idx_key1来提取SELECT *：

1. Server向存储引擎请求第1行；
2. 引擎从索引找到对应主键ID，**回表**获取完整数据行；
3. Server收到后，发现当前是第1条，未达到LIMIT偏移量，丢弃该行；
4. 重复上述过程直到第5001条。

这意味着需要进行约5000次回表操作。对于Server层来说，这种“浅层迭代+深层回表”的成本远高于一次全表扫描后的内存排序（Filesort）。因此，Optimizer选择了全表扫描+文件排序。

二、为什么两条SQL结果不一致？

回到最初的疑惑：为什么SELECT id ... LIMIT 5000, 1和SELECT * ... LIMIT 5000, 1返回的主键id不同？关键在于是否发生了回表。
由于SELECT id可以直接从二级索引idx_key1中获取到(key1, id)组合，无需回表，Server层可以极低成本遍历前5001条索引记录。因此它依然选择了走索引扫描（Using index）。

核心矛盾点：
在二级索引idx_key1中，排序规则是按(key1, id)进行的。但在SELECT *的全表扫描场景中，如果没有明确指定ORDER BY，MySQL默认按照聚集索引（即主键id）的顺序读取数据。

• SELECT id FROM ... LIMIT 5000, 1：走索引idx_key1，实际排序为 ORDER BY key1, id。
• SELECT * FROM ... LIMIT 5000, 1：走全表扫描，隐式排序为 ORDER BY id。

由于两组数据的排序维度不同，自然会在相同的位置截取出不同的ID值。

三、验证排序规则的影响

为了验证这一点，我们可以显式指定排序规则进行对比：

1. 显式指定与索引一致的排序规则：
执行SELECT * FROM t_null_index ORDER BY key1, id LIMIT 5000, 1;。虽然此时可能仍因成本考量走全表扫描（取决于数据量和优化器判断），但逻辑上其结果应与直接查ID的结果一致。

2. 仅指定ORDER BY key1的情况：
执行SELECT * FROM t_null_index ORDER BY key1 LIMIT 5000, 1;。即使显式要求按key1排序，由于缺少id作为第二排序条件，当key1相同时，数据库无法保证id的稳定顺序（尽管InnoDB底层通常是有序的，但这属于未定义行为或未明确的排序规范），结果往往也与预期不符或呈现乱序。

总结而言，要确保LIMIT分页结果的稳定性，必须遵循以下原则：

1. 复合排序：在ORDER BY中必须包含唯一键（如主键），构成确定性排序，例如ORDER BY column_a, id。
2. 避免深分页：当OFFSET过大时，全表扫描+文件排序的成本极高，应改用基于游标（Last-Seen-ID）的方式进行关联查询优化。

以上就是关于MySQL Limit执行过程及结果不一致原因的详细内容，更多关于MySQL性能优化的资料请关注本站其它相关文章！