MySQL8.0中的降序索引
16lz
2021-12-15
前言
相信大家都知道，索引是有序的；不过，在MySQL之前版本中，只支持升序索引，不支持降序索引，这会带来一些问题；在最新的MySQL 8.0版本中，终于引入了降序索引，接下来我们就来看一看。
降序索引
单列索引
（1）查看测试表结构
mysql> show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create 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=10000001 row in set (0.00 sec)
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)
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)
mysql> alter table sbtest1 add index k_2(k desc);Query OK, 0 rows affected (6.45 sec)Records: 0 Duplicates: 0 Warnings: 0
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: sbtest1Create 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=10000001 row in set (0.00 sec)
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)
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: 0mysql> 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: 0mysql> 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
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引入的降序索引，最重要的作用是，解决了多列排序可能无法使用索引的问题，从而可以覆盖更多的应用场景。
更多相关文章
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- [android源码下载索引贴】微信+二维码那都不是事......
- android触控,先了解MotionEvent
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- Android(安卓)自定义Gallery
- android 知识点汇总