MySQL8.0中的降序索引

时间:2022-11-06 20:31:56

前言

相信大家都知道,索引是有序的;不过,在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