MySQL8.0中的降序索引

数据库 2024/11/23 佚名

3 2 1

前言

相信大家都知道，索引是有序的；不过，在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 降序索引的资料请关注其它相关文章！

MySQL8.0,降序索引,MySQL,索引

标签：

MySQL8.0,降序索引,MySQL,索引

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“MySQL8.0中的降序索引”

MySQL8.0中的降序索引

暂无“MySQL8.0中的降序索引”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

MySQL8.0中的降序索引

MySQL8.0,降序索引,MySQL,索引

在IntelliJ IDEA中使用Java连接MySQL数据库的方法详解

详解mysql中的存储引擎

评论“MySQL8.0中的降序索引”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

MySQL8.0中的降序索引

MySQL8.0,降序索引,MySQL,索引

在IntelliJ IDEA中使用Java连接MySQL数据库的方法详解

详解mysql中的存储引擎

评论“MySQL8.0中的降序索引”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存