MySQL8.0中的降序索引

2023-07-13 00:52:03 253

前言

相信大家都知道，索引是有序的；不过，在MySQL之前版本中，只支持升序索引，不支持降序索引，这会带来一些问题；在最新的MySQL8.0版本中，终于引入了降序索引，接下来我们就来看一看。

降序索引

单列索引

（1）查看测试表结构

mysql>showcreatetablesbtest1\G
***************************1.row***************************
Table:sbtest1
CreateTable:CREATETABLE`sbtest1`(
`id`intunsignedNOTNULLAUTO_INCREMENT,
`k`intunsignedNOTNULLDEFAULT'0',
`c`char(120)NOTNULLDEFAULT'',
`pad`char(60)NOTNULLDEFAULT'',
PRIMARYKEY(`id`),
KEY`k_1`(`k`)
)ENGINE=InnoDBAUTO_INCREMENT=1000001DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciMAX_ROWS=1000000
1rowinset(0.00sec)

（2）执行SQL语句orderby...limitn，默认是升序，可以使用到索引

mysql>explainselect*fromsbtest1orderbyklimit10;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
|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|
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
1rowinset,1warning(0.00sec)

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

mysql>explainselect*fromsbtest1orderbykdesclimit10;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
|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|Backwardindexscan|
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
1rowinset,1warning(0.00sec)

（4）创建降序索引

mysql>altertablesbtest1addindexk_2(kdesc);
QueryOK,0rowsaffected(6.45sec)
Records:0Duplicates:0Warnings:0

（5）再次执行SQL语句orderby...desclimitn，可以使用到降序索引

mysql>explainselect*fromsbtest1orderbykdesclimit10;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
|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|
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
1rowinset,1warning(0.00sec)

多列索引

（1）查看测试表结构

mysql>showcreatetablesbtest1\G
***************************1.row***************************
Table:sbtest1
CreateTable:CREATETABLE`sbtest1`(
`id`intunsignedNOTNULLAUTO_INCREMENT,
`k`intunsignedNOTNULLDEFAULT'0',
`c`char(120)NOTNULLDEFAULT'',
`pad`char(60)NOTNULLDEFAULT'',
PRIMARYKEY(`id`),
KEY`k_1`(`k`),
KEY`idx_c_pad_1`(`c`,`pad`)
)ENGINE=InnoDBAUTO_INCREMENT=1000001DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciMAX_ROWS=1000000
1rowinset(0.00sec)

（2）对于多列索引来说，如果没有降序索引的话，那么只有SQL1才能用到索引，SQL4能用相反顺序扫描，其他两条SQL语句只能走全表扫描，效率非常低

SQL1：select*fromsbtest1orderbyc,padlimit10;

SQL2：select*fromsbtest1orderbyc,paddesclimit10;

SQL3：select*fromsbtest1orderbycdesc,padlimit10;

SQL4：explainselect*fromsbtest1orderbycdesc,paddesclimit10;

mysql>explainselect*fromsbtest1orderbyc,padlimit10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|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|
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1rowinset,1warning(0.00sec)

mysql>explainselect*fromsbtest1orderbyc,paddesclimit10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|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|Usingfilesort|
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1rowinset,1warning(0.00sec)

mysql>explainselect*fromsbtest1orderbycdesc,padlimit10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|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|Usingfilesort|
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1rowinset,1warning(0.01sec)

mysql>explainselect*fromsbtest1orderbycdesc,paddesclimit10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|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|Backwardindexscan|
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1rowinset,1warning(0.00sec)

（3）创建相应的降序索引

mysql>altertablesbtest1addindexidx_c_pad_2(c,paddesc);
QueryOK,0rowsaffected(1min11.27sec)
Records:0Duplicates:0Warnings:0

mysql>altertablesbtest1addindexidx_c_pad_3(cdesc,pad);
QueryOK,0rowsaffected(1min14.22sec)
Records:0Duplicates:0Warnings:0

mysql>altertablesbtest1addindexidx_c_pad_4(cdesc,paddesc);
QueryOK,0rowsaffected(1min8.70sec)
Records:0Duplicates:0Warnings:0

（4）再次执行SQL，均能使用到降序索引，效率大大提升

mysql>explainselect*fromsbtest1orderbyc,paddesclimit10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|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|
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1rowinset,1warning(0.00sec)

mysql>explainselect*fromsbtest1orderbycdesc,padlimit10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|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|
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1rowinset,1warning(0.00sec)

mysql>explainselect*fromsbtest1orderbycdesc,paddesclimit10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|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|
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1rowinset,1warning(0.00sec)

总结

MySQL8.0引入的降序索引，最重要的作用是，解决了多列排序可能无法使用索引的问题，从而可以覆盖更多的应用场景。

以上就是MySQL8.0中的降序索引的详细内容，更多关于MySQL降序索引的资料请关注毛票票其它相关文章！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL8.0中的降序索引

热门推荐

随机推荐