MySQL8.0中的降序索引

前言

相信大家都知道，索引是有序的；不过，在MySQL之前版本中，只支持升序索引，不支持降序索引，这会带来一些问题；在最新的MySQL 8.0版本中，终于引入了降序索引，接下来我们就来看一看。

降序索引

单列索引

（1）查看测试表结构

?

1 2 3 4 5 6 7 8 9 10 11 12

mysql> show create table sbtest1\G *************************** 1. row ***************************      Table : sbtest1 Create Table : CREATE TABLE `sbtest1` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `k` int unsigned NOT NULL DEFAULT '0' ,   `c` char (120) NOT NULL DEFAULT '' ,   `pad` char (60) NOT NULL DEFAULT '' ,   PRIMARY KEY (`id`),   KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci MAX_ROWS=1000000 1 row in set (0.00 sec)

（2）执行SQL语句order by ... limit n，默认是升序，可以使用到索引

?

1 2 3 4 5 6 7

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

（3）执行SQL语句order by ... desc limit n，如果是降序的话，无法使用索引，虽然可以相反顺序扫描，但性能会受到影响

?

1 2 3 4 5 6 7

mysql> explain select * from sbtest1 order by k desc limit 10; + ----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+ | id | select_type | table  | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra        | + ----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+ | 1 | SIMPLE   | sbtest1 | NULL    | index | NULL     | k_1 | 4    | NULL |  10 |  100.00 | Backward index scan | + ----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+ 1 row in set , 1 warning (0.00 sec)

（4）创建降序索引

?

1 2 3

mysql> alter table sbtest1 add index k_2(k desc ); Query OK, 0 rows affected (6.45 sec) Records: 0 Duplicates: 0 Warnings: 0

（5）再次执行SQL语句order by ... desc limit n，可以使用到降序索引

?

1 2 3 4 5 6 7

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

多列索引

（1）查看测试表结构

?

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> show create table sbtest1\G *************************** 1. row ***************************      Table : sbtest1 Create Table : CREATE TABLE `sbtest1` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `k` int unsigned NOT NULL DEFAULT '0' ,   `c` char (120) NOT NULL DEFAULT '' ,   `pad` char (60) NOT NULL DEFAULT '' ,   PRIMARY KEY (`id`),   KEY `k_1` (`k`),   KEY `idx_c_pad_1` (`c`,`pad`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci MAX_ROWS=1000000 1 row in set (0.00 sec)

（2）对于多列索引来说，如果没有降序索引的话，那么只有SQL 1才能用到索引，SQL 4能用相反顺序扫描，其他两条SQL语句只能走全表扫描，效率非常低

SQL 1：select * from sbtest1 order by c,pad limit 10;

SQL 2：select * from sbtest1 order by c,pad desc limit 10;

SQL 3：select * from sbtest1 order by c desc,pad limit 10;

SQL 4：explain select * from sbtest1 order by c desc,pad desc limit 10;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> explain select * from sbtest1 order by c,pad limit 10; + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref | rows | filtered | Extra | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | 1 | SIMPLE   | sbtest1 | NULL    | index | NULL     | idx_c_pad_1 | 720   | NULL |  10 |  100.00 | NULL | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ 1 row in set , 1 warning (0.00 sec)   mysql> explain select * from sbtest1 order by c,pad desc limit 10; + ----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table  | partitions | type | possible_keys | key | key_len | ref | rows  | filtered | Extra     | + ----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE   | sbtest1 | NULL    | ALL | NULL     | NULL | NULL  | NULL | 950738 |  100.00 | Using filesort | + ----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set , 1 warning (0.00 sec)   mysql> explain select * from sbtest1 order by c desc ,pad limit 10; + ----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table  | partitions | type | possible_keys | key | key_len | ref | rows  | filtered | Extra     | + ----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE   | sbtest1 | NULL    | ALL | NULL     | NULL | NULL  | NULL | 950738 |  100.00 | Using filesort | + ----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set , 1 warning (0.01 sec)   mysql> explain select * from sbtest1 order by c desc ,pad desc limit 10; + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+ | id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref | rows | filtered | Extra        | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+ | 1 | SIMPLE   | sbtest1 | NULL    | index | NULL     | idx_c_pad_1 | 720   | NULL |  10 |  100.00 | Backward index scan | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+ 1 row in set , 1 warning (0.00 sec)

（3）创建相应的降序索引

?

1 2 3 4 5 6 7 8 9 10 11

mysql> alter table sbtest1 add index idx_c_pad_2(c,pad desc ); Query OK, 0 rows affected (1 min 11.27 sec) Records: 0 Duplicates: 0 Warnings: 0   mysql> alter table sbtest1 add index idx_c_pad_3(c desc ,pad); Query OK, 0 rows affected (1 min 14.22 sec) Records: 0 Duplicates: 0 Warnings: 0   mysql> alter table sbtest1 add index idx_c_pad_4(c desc ,pad desc ); Query OK, 0 rows affected (1 min 8.70 sec) Records: 0 Duplicates: 0 Warnings: 0

（4）再次执行SQL，均能使用到降序索引，效率大大提升

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> explain select * from sbtest1 order by c,pad desc limit 10; + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref | rows | filtered | Extra | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | 1 | SIMPLE   | sbtest1 | NULL    | index | NULL     | idx_c_pad_2 | 720   | NULL |  10 |  100.00 | NULL | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ 1 row in set , 1 warning (0.00 sec)   mysql> explain select * from sbtest1 order by c desc ,pad limit 10; + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref | rows | filtered | Extra | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | 1 | SIMPLE   | sbtest1 | NULL    | index | NULL     | idx_c_pad_3 | 720   | NULL |  10 |  100.00 | NULL | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ 1 row in set , 1 warning (0.00 sec)   mysql> explain select * from sbtest1 order by c desc ,pad desc limit 10; + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref | rows | filtered | Extra | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ | 1 | SIMPLE   | sbtest1 | NULL    | index | NULL     | idx_c_pad_4 | 720   | NULL |  10 |  100.00 | NULL | + ----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+ 1 row in set , 1 warning (0.00 sec)

总结

MySQL 8.0引入的降序索引，最重要的作用是，解决了多列排序可能无法使用索引的问题，从而可以覆盖更多的应用场景。

以上就是MySQL8.0中的降序索引的详细内容，更多关于MySQL 降序索引的资料请关注服务器之家其它相关文章！

原文链接：https://cloud.tencent.com/developer/article/1702060