MySQL8.0之降序索引(descending index)

//

mysql8.0之降序索引(descending index)

//

MySQL8.0引入了降序索引(descending index)，今天我们来说说这个特性。

降序索引，顾名思义是指索引是按照从大到小降序排列的，和升序索引的顺序相反，平时我们创建的普通索引都是默认升序的。

当我们的查询SQL，只包含一个列的时候，无论是使用降序索引还是升序索引，整个查询过程的性能是一样的。当SQL中有多个列，但是每个列的排序顺序不一样的时候，降序所以就能起到比较重要的作用，下面我们慢慢分析。

首先来看，MySQL5.7和MySQL8.0中针对索引的排序语法。

00

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 testG
*************************** 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 testG
*************************** 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 testG
*************************** 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 test1G
*************************** 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，由于降序索引的存在，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

一些使用限制