MySQL 索引排序

发表于： 2023-09-27 分类于：技术学习

字数： 576 阅读：≈ 2分钟浏览：

开发过程中发现SQL一直filesort，所以研究一下到低怎么才能使用索引进行排序。

表结构和数据

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条件+排序项符合最佳左前缀原则
不能使用条件查询