MySQL索引排序

  • Z时代
  • 2024-01-10
  • 分类：综合

database

开发过程中发现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 使用索引排序，需要至少满足以下条件：

  1. where条件+排序项符合最佳左前缀原则

  2. 不能使用条件查询

以上是 MySQL索引排序 的全部内容， 来源链接： utcz.com/z/536261.html

回到顶部