当前位置：首页 > 数据库 > Mysql > 详细页面

MySQL8.0中的降序索引

时间：2020-10-30来源：www.pcxitongcheng.com作者：电脑系统城

前言

相信大家都知道，索引是有序的；不过，在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，默认是升序，可以使用到索引

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

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

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;

（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