MySQL带/不带ORDER BY查询结果不同及非预期索引选择原因

你的判断是对的，两条查询返回顺序不一致的核心原因是优化器为两条语句选择了不同的索引：

• 带ORDER BY id的查询走主键聚簇索引，返回结果按主键顺序排列
• 无排序的查询走idx_var_name二级索引，返回结果按var_name的索引顺序排列，自然和主键顺序不匹配

为什么查询id字段会选择二级索引 #

这是InnoDB覆盖索引机制+优化器成本估算共同导致的：

1. InnoDB的二级索引叶子节点存储的是「索引列值 + 对应行的主键值」，你执行的SELECT id FROM record_temp_var LIMIT 10不需要查询除id外的其他字段，idx_var_name索引已经包含了所有需要查询的内容，属于覆盖索引场景，执行时不需要回表查询聚簇索引，本身就具备走二级索引的前提。
2. 优化器选择索引时会优先选IO成本更低的路径：主键聚簇索引的叶子节点存储的是整行数据，单个16KB的索引页能存放的行数量很少；而idx_var_name是单字段二级索引，叶子节点只存var_name和id两个字段，单个索引页能存放的条目数是聚簇索引的数倍。同样取前10条记录，扫二级索引需要读取的索引页更少，估算成本远低于扫主键聚簇索引，所以优化器会主动选择走idx_var_name。

为什么加了ORDER BY id就会走主键 #

带ORDER BY id LIMIT 10的语句如果选择走idx_var_name，拿到的id是按var_name排序的乱序值，需要额外做内存/磁盘排序（filesort），排序产生的额外成本远高于走二级索引省下来的IO成本。而主键聚簇索引本身就是按id递增顺序有序存储的，走主键只需要顺序读取前10条记录即可，不需要额外排序，整体成本更低，所以优化器这时候会切换为主键索引。

注意：MySQL官方从未承诺不带ORDER BY子句的查询会按主键顺序返回结果。无排序查询的返回顺序完全由执行计划选择的索引、数据物理存储顺序决定，随时可能因为数据增删、统计信息变化、版本升级发生改变，业务逻辑绝对不能依赖这种隐式顺序。

你可以用EXPLAIN直接验证两个语句的索引选择：

-- 执行后key列显示为 idx_var_name，证明走了二级索引
EXPLAIN SELECT id FROM `record_temp_var` LIMIT 10;

-- 执行后key列显示为 PRIMARY，证明走了主键聚簇索引
EXPLAIN SELECT id FROM `record_temp_var` ORDER BY id LIMIT 10;

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