MySQL联合索引(col1,col2)下order by col2为何无法利用索引排序？

问题结论 #

你对联合索引B+树结构的认知完全正确，你看到的案例存在事实错误。

原理说明 #

创建联合索引(col1, col2)时，B+树的叶子节点本身就是先按col1排序，col1值相等时再按col2排序的结构。当where条件固定col1为常量时，所有满足条件的索引项天然连续且按col2有序，此时order by col2完全可以直接利用索引避免额外排序，没有必要在order by子句中额外增加col1。

你可以直接用explain执行计划验证：运行explain select col3 from table where col1 = 'col1' order by col2，只要Extra字段中没有出现Using filesort，就说明已经通过索引完成了排序，不需要改写SQL。

常见误区说明 #

这个错误案例大概率是混淆了查询场景：如果where条件中col1是范围匹配（比如col1 > 'xxx'），此时order by col2无法直接利用索引排序，才需要改写为order by col1,col2才能复用联合索引的排序特性，该规则并不适用于col1等值匹配的场景。

索引优化实战技巧 #

• 优先使用覆盖索引：如果查询涉及的所有列都能包含在联合索引中，可以完全避免回表操作，消除回表带来的随机IO开销，性能提升幅度远高于单纯的排序优化。
• 联合索引排序遵循「等值前置、排序紧随、范围最后」规则：创建索引时把where子句中的所有等值匹配列放在最前面，紧随其后放置order by子句的排序列，最后再放范围查询的列，避免范围条件截断索引的生效范围。
• 保证排序方向与索引定义一致：如果联合索引定义为(col1 asc, col2 asc)，那么order by col1 desc, col2 desc也可以复用索引排序，但order by col1 asc, col2 desc这类方向不一致的写法无法利用索引排序，会触发额外的文件排序。
• 高过滤性条件前置：where子句中如果有多个过滤条件，把能过滤掉更多数据的列放在联合索引更靠前的位置，减少需要扫描的索引项总数。
• 避免索引列触发隐式转换：如果索引列是字符串类型，查询时用数字类型传参，或是对索引列使用函数计算，都会直接导致索引失效，连索引扫描都无法触发，更谈不上利用索引排序。

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