表结构和数据

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SQL

explain select * from t where a = 5 order by c desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const
-- Extra:Using where; Using index; Using filesort

explain select * from t where a = 5 order by b desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const
-- Extra:Using where; Using index

explain select * from t where a = 5 and c = 5 order by b desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const
-- Extra:Using where; Using index

explain select * from t where a = 5 and b = 5 order by c desc;
-- type:ref
-- key:idx_a_b_c
-- ref:const,const
-- Extra:Using where; Using index

explain select * from t where a = 5 and b >= 5 order by c desc;
-- type:range
-- key:idx_a_b_c
-- ref:NULL
-- Extra:Using where; Using index; Using filesort

索引分析

通过观察联合索引的数据结构，很明显就能发现索引都是有序的，使用索引进行排序就是利用了这个特性。

我们来观察 a = 5 的这一段索引，很容易就能发现，在 a 确定的情况下，b 是有序的，但c 是无序的。a 和 b 命中索引，a 和 c 不命中索引

在 a,b 都确定的情况下，c 是有序的。a,b,c 命中索引

这就是老生常谈的 最佳左前缀原则 也叫 最左前缀匹配原则

因此，要让排序项使用索引进行排序

第一个条件就是：where条件+排序项符合最佳左前缀原则

第二个条件：不能使用条件查询

这个也可以通过观察联合索引得出结论

a = 5 AND b >= 5 显然是无法保证 c 是有序的

结论

要让order by 使用索引排序，需要至少满足以下条件：

来源：https://www.cnblogs.com/weirwei/p/15846609.html
图文来源于网络，如有侵权请联系删除。