表结构和数据
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;
| id | a | b | c |
|---|---|---|---|
| 1 | 1 | 5 | 3 |
| 2 | 5 | 3 | 3 |
| 3 | 4 | 5 | 9 |
| 4 | 2 | 6 | 1 |
| 5 | 4 | 3 | 2 |
| 6 | 5 | 5 | 5 |
| 7 | 1 | 2 | 1 |
| 8 | 5 | 5 | 8 |
| 9 | 5 | 3 | 9 |
| 10 | 5 | 5 | 1 |
| 11 | 5 | 7 | 7 |
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 使用索引排序，需要至少满足以下条件：
- where条件+排序项符合
最佳左前缀原则 - 不能使用条件查询
