时间: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,默认是升序,可以使用到索引
?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引入的降序索引,最重要的作用是,解决了多列排序可能无法使用索引的问题,从而可以覆盖更多的应用场景。
2023-03-19
Centos 7.9安装MySQL8.0.32的详细教程2023-03-19
一步步教你利用Mysql存储过程造百万级数据2023-03-19
利用Mysql定时+存储过程创建临时表统计数据的过程问题描述:MYSQL version 5.6.8command 表结构 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE command ( ID INT NOT NULL, NAME VARCHAR(16), D...
2023-03-19
一、Galera Cluster 二、基础环境搭建 三、加入配置参数启动集群 四、 测试 五、ProxySql...
2023-03-17