MySQL8.0新特性之降序索引

简介

MySQL从8.0开始终于支持真正的降序索引了，实际上，在以前的版本当中，语法上可以通过desc来指定索引为降序，但实际上创建的仍然是常规的升序索引。
如代码中所示，在MySQL5.7中，虽然通过desc关键字指定了索引中的c2字段为降序，但通过show create table语句查看索引并没有真正的降序，c2字段仍然为升序。

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.05 sec)
mysql> CREATE TABLE `t1` (`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,KEY `idx_c1_c2` (`c1`,`c2` desc));
Query OK, 0 rows affected (0.20 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+------------------------------------------------------------------------------------------------
1 row in set (0.06 sec)

但在MySQL8.0中却真正的实现了这一点,如代码中所示，通过show create table语句查看，索引的顺序与我们在建表语句中指定的顺序是一致的，c1字段升序，c2字段降序。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `t1` (`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,KEY `idx_c1_c2` (`c1`,`c2` desc));
Query OK, 0 rows affected (0.63 sec)
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------
| Table | Create Table                                                                  
+-------+-------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------
1 row in set (0.04 sec)

降序索引在执行计划中的表现

环境信息

• MySQL版本：5.7.22和8.0.15
• 操作系统版本：Centos6.6
  使用如下存储过程在t1表插入1000万条随机数据，

use test
set sql_log_bin=off;
drop procedure if exists test_insert;
delimiter ;;
create procedure test_insert()
begin
declare i int default 0;
while i<10000000
do
insert into t1 select rand()*10000000,rand()*10000000;
set i=i+1;
end while;
commit;
end;;
delimiter ;
call test_insert();

查看select * from t1 order by c1 , c2 desc limit 10;的执行计划。从执行计划上可以看出，5.7的扫描行数数9364304远远大于8.0的10行，并且使用了filesort。

#MySQL5.7
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.40 sec）

mysql> explain select * from t1 order by c1 , c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 9364304 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.08 sec)
#MySQL8.0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.14 sec)

mysql> explain select * from t1 order by c1 , c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

降序索引最大的应用场景便是如上例这样对多字段排序的场景，这种场景下，能够最大的发挥降序索引的作用。在对单字段排序时，无论是升序还是降序都是可以用到索引的。因为数据库不仅可以对索引进行从前向后的扫描，也可以对索引进行从后向前的扫描。
如图所示，在一条对t1表的查询语句中只对c1字段进行降序排序，无论是MySQL5.7还是MySQL8.0都用到了索引，在8.0中，对于索引的反向扫描，有一个专门的词进行描述“Backward index scan”。

#MySQL5.7
mysql> explain select * from t1 order by c1 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#MySQL8.0
mysql> explain select * from t1 order by c1 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

虽然在对多字段进行排序时，能够最大发会降序索引的作用，但也尤其要注重使用的场景，否则仍然可能适得其反。例如对于如下两条查询语句，降序索引的表现就并没有那么乐观。从执行计划中可以看出，扫描的行数将近1000w，并且使用了filesort。

#MySQL8.0
mysql> explain select * from t1 order by c1,c2 limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 9742403 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 order by c1 desc,c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 9742403 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

但是在MySQL5.7中正常的升序索引对这两条语句的执行却有着上佳的表现，两条查询语句在MySQL5.7中均只扫描了10行。

#MySQL5.7
mysql> explain select * from t1 order by c1,c2 limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from t1 order by c1 desc,c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

为什么降序索引此时失去了它的魔力呢？因为我们执行的这两条查询语句对排序的两个字段要么全部升序，要么全部倒序。而在MySQL5.7中，由于组成联合索引的c1字段和c2字段都是升序排列的，那么在使用order by c1,c2排序时，MySQL可以对索引进行正向扫描，在使用by c1 desc,c2 desc，MySQL对索引进行完全的反向扫描即可。意识到降序索引在这两种排序场景下的缺陷。根据同样的道理，我们也能够发现降序索引在另一种场景下的优势。在文章中的第一个例子中，我们使用的排序顺序为order by c1 , c2 desc，这种场景下，MySQL对索引进行的是正向扫描，那么，让数据库对降序索引进行反向扫描，同样可以发挥降序索引的优势。如下代码所示，MySQL用到了索引，且扫描行数只有10行，并且使用了Backward index scan。

#MySQL8.0
mysql> explain select * from t1 order by c1 desc,c2 limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

使用条件及限制

1. 只有InnoDB存储引擎支持降序索引，且具有以下限制：
   • 如果索引包含一个降序索引列或主键包含降序索引列，则辅助索引无法用到change buffer。
   • InnoDB SQL解析器不使用降序索引。对于InnoDB全文搜索，这意味着索引表的FTS_DOC_ID列上所需的索引不能定义为降序索引。
2. 适用于升序索引的所有数据类型也都支持降序索引。
3. 普通（非生成）和生成列（VIRTUAL和STORED）都支持降序索引。
4. DISTINCT可以使用任何一个索引包含的字段，包括索引中降序的字段。
5. 对于调用了聚合函数但没有GROUP BY子句的这一类查询，索引的降序列不能用于优化MIN()/MAX()这样的聚合函数。
6. BTREE支持降序索引，但HASH索引、FULLTEXT或SPATIAL索引不支持降序索引。

总结

降序索引的引入，使得在查询中，需要对多字段进行排序，且顺序要求不一致的场景，能够极大的提升查询的效率。但是需要注意的是，降序索引的引入，只是多提供给我们一种索引的使用方法，它并不能做到赢家通吃，无法适用所有的排序场景。因此，在工作中，我们还是要贴合业务的需求，合理的运用索引，这样写出的SQL执行起来才能事半功倍。