MySQL同LIMIT条件下全字段与主键查询返回不同行问题咨询

核心根源是两条查询都没有显式指定 ORDER BY 子句，SQL 标准和 MySQL 官方规则都明确说明：不带排序的 LIMIT 仅要求返回指定数量的行，不保证返回行的顺序和结果一致性。

具体到你的场景，差异来自两条查询的执行计划不同：

• SELECT * FROM page LIMIT 10 需要读取整行数据，InnoDB 引擎默认会走聚集索引（主键索引，存储了整行数据）的全表扫描，返回的是聚集索引排序下的前10行。
• SELECT page.id FROM page LIMIT 10 只需要读取主键值，属于覆盖索引查询，不需要回表读取整行数据。优化器会优先选择扫描体积更小、IO成本更低的索引结构：
  • 如果表中存在其他二级索引，二级索引的叶子节点本身就存储了主键值，扫描二级索引的成本远低于扫描存了整行数据的聚集索引，此时查询返回的id是按二级索引的排序规则排列的，和聚集索引的排序结果不同，自然LIMIT 10拿到的行就不一样。
  • 就算没有二级索引，MySQL 8.x的优化器也可能因为统计信息、存储层的页存储顺序等因素，选择不同的读取顺序，导致结果出现差异。

解决方案 #

只要在LIMIT前加上稳定的排序规则即可保证结果一致，例如修改为：

SELECT * FROM page ORDER BY id ASC LIMIT 10;
SELECT id FROM page ORDER BY id ASC LIMIT 10;

内容的提问来源于stack exchange，提问作者Liza