MySQL8.0之降序索引(descending index)
|
freeflydom
2025年12月4日 9:28
本文热度 102
|
MySQL8.0引入了降序索引(descending index)，今天我们来说说这个特性。
降序索引，顾名思义是指索引是按照从大到小降序排列的，和升序索引的顺序相反，平时我们创建的普通索引都是默认升序的。
当我们的查询SQL，只包含一个列的时候，无论是使用降序索引还是升序索引，整个查询过程的性能是一样的。当SQL中有多个列，但是每个列的排序顺序不一样的时候，降序所以就能起到比较重要的作用，下面我们慢慢分析。
首先来看，MySQL5.7和MySQL8.0中针对索引的排序语法。
MySQL5.7和MySQL8.0降序索引差异
MySQL5.7创建降序索引：
mysql> create table test (c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到，我们的SQL里面创建的索引指定了c2为降序排列，但是实际创建的索引里面并没有按照c2降序排列。
MySQL8.0创建降序索引：
mysql> create table test (c1 int,c2 int,index idx_c1_c2(c1,c2 desc))
-> ;
Query OK, 0 rows affected (0.10 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
在MySQL8.0中创建了降序索引之后，可以看到，表结构中的索引已经降序排列了。
01
降序索引对SQL的影响
首先给出test和test1的表结构，方便下面测试结果对照：
test的表结构
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
test1的表结构
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
KEY `idx_c1_c2` (`c1` DESC,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
情况一：SQL仅包含单个字段
我们的test表中是升序索引asc，test1中是降序索引desc，降序索引和升序索引的执行计划如下：
升序索引
mysql> explain select * from test order by c1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
降序索引
mysql> explain select * from test1 order by c1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | test1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到，降序的索引的执行计划中多了个Backward index scan反向索引扫描。
这两个执行计划，在性能上的差别很小。
情况二：多个字段，排序方向不同
执行计划如下：
c1列升序排列，升序查询
mysql> explain select * from test order by c1,c2 desc;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
c1列降序排列，升序查询
mysql> explain select * from test1 order by c1 ,c2 desc;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | test1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
当我们使用同样的SQL来升序查询c1列的时候，由于test1中的c1列是降序排列的，所以test1的执行计划中多了个using filesort的结果，用到了文件排序，而在一些大型表的排序过程中，使用文件排序是非常消耗性能的。使用降序索引可以避免文件排序，这一点，就是降序索引能够带来的直观收益。
官方文档对这点也做了说明，翻译的结果如下：
当一个查询 SQL，需要按多个字段，以不同的顺序进行排序时，8.0 之前无法使用索引已排序的特性，因为 order by 的顺序与索引的顺序不一致，而使用降序索引，就能够指定联合索引中每一个字段的顺序，以适应 SQL 语句中的 order by 顺序，让 SQL 能够充分使用索引已排序的特性，提升 SQL 性能
情况三：相同的SQL，由于降序索引的存在，group by不再主动进行隐式排序。
下面分别是在MySQL5.7和MySQL8.0中使用group by语句对c2进行聚合，产生的执行计划：
MySQL 5.7 中执行group by语句，自动排序，filesort
mysql> explain select c2 from test group by c2;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE | test | NULL | index | idx_c1_c2 | idx_c1_c2 | 10 | NULL | 3 | 100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
MySQL 8.0中执行group by语句，不使用filesort
mysql> explain select c2 from test group by c2;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | test | NULL | index | idx_c1_c2 | idx_c1_c2 | 10 | NULL | 3 | 100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
02
一些使用限制
- 降序索引只能在innodb存储引擎中使用，其他存储引擎不支持。
- change buffer 不支持二级索引或者主键包含降序字段。这可能会一定程度影响插入的性能。
- 升序索引支持的数据类型，降顺索引都支持。
- 降序索引支持普通的字段和不可见字段。
- 使用聚合函数如果没有使用 group by 子句，不能使用降序索引进行优化。
- 降序索引只支持 BTREE 索引，不支持 HASH 索引。
转自https://cloud.tencent.com/developer/article/1832961
该文章在 2025/12/4 9:33:45 编辑过
400 186 1886
