这篇文章将为大家详细讲解有关MySQL8.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中降序索引使用方法就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
