MySQL8.0中的降序索引

前言

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

降序索引

单列索引

（1）查看测试表结构

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，默认是升序，可以使用到索引

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，如果是降序的话，无法使用索引，虽然可以相反顺序扫描，但性能会受到影响

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）创建降序索引

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，可以使用到降序索引

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）查看测试表结构

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;

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）创建相应的降序索引

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，均能使用到降序索引，效率大大提升

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 降序索引的资料请关注其它相关文章！