MySQL含索引字段的SELECT LIMIT 1查询返回异常结果原因咨询

问题背景 #

你有一张带id主键的InnoDB表，执行仅查询id的LIMIT 1语句时，返回的是id=73而非插入的第一行id=1：

SELECT cropvarietyname.id FROM `seedcalendar-test`.cropvarietyname LIMIT 1;

EXPLAIN显示查询使用了IDX_crop二级索引：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'cropvarietyname', NULL, 'index', NULL, 'IDX_crop', '8', NULL, '433', '100.00', 'Using index'

但查询包含非索引字段（如lastModified）时，能正确返回插入第一行的值；同时查询索引与非索引字段时也正常。添加ORDER BY id ASC可解决问题，但你想知道背后的原因。

核心原因解析 #

1. InnoDB的索引与存储逻辑 #

InnoDB采用聚簇索引架构：

• 聚簇索引（主键索引）：存储表的完整数据，数据按主键顺序物理排列；
• 二级索引：仅包含索引字段和主键值，按索引字段排序存储。

2. 覆盖索引的优化选择 #

MySQL优化器会优先选择成本最低的执行计划：

• 当查询仅包含id时，IDX_crop二级索引包含FK_CROP和主键id，完全满足查询需求（即覆盖索引）。二级索引的体积远小于聚簇索引，扫描成本更低，因此优化器选择该索引。此时返回的是IDX_crop排序后的第一行，而非主键顺序的第一行，这就是为什么会得到id=73。
• 当查询包含非索引字段时，二级索引无法提供所需数据，必须通过主键回表查询聚簇索引，因此优化器直接扫描聚簇索引，返回的是主键顺序的第一行（即插入的id=1的行）。

3. 无ORDER BY时的结果顺序无保证 #

SQL标准明确规定：未指定ORDER BY的查询，不保证返回结果的顺序。MySQL返回的顺序完全依赖于执行计划中选择的扫描路径，可能是二级索引顺序、聚簇索引顺序，甚至是其他存储顺序，不能依赖插入顺序或主观认知的“首行”。

解决方案 #

要稳定获取id最小的行，必须显式指定排序规则：

SELECT cropvarietyname.id FROM `seedcalendar-test`.cropvarietyname ORDER BY id ASC LIMIT 1;

若需强制使用聚簇索引（不推荐，因为优化器逻辑可能随版本变化），可使用FORCE INDEX：

SELECT cropvarietyname.id FROM `seedcalendar-test`.cropvarietyname FORCE INDEX(PRIMARY) LIMIT 1;

总结 #

• 无ORDER BY的LIMIT查询结果顺序不可靠，完全由优化器的执行计划决定；
• 覆盖索引的选择会改变结果的返回顺序，这是优化器的正常行为；
• 只有显式添加ORDER BY子句，才能保证结果顺序符合预期。

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