建站教学网,让你建站更便捷和轻松!
您当前的位置:首页 > 网络编程 > Mysql教程

MySQL8新特性:降序索引详解

来源:建站教学网  时间:2019-07-05 14:56:29

前言

MySQL 8.0终于支持降序索引了。其实,从语法上,MySQL 4就支持了,但正如官方文档所言,"they are parsed but ignored",实际创建的还是升序索引。

无图无真相,同一个建表语句,看看MySQL 5.7和8.0的区别。

create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

MySQL 5.7

mysql> show create table slowtech.t1/G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx_c1_c2` (`c1`,`c2`)) ENGINE=InnoDB DEFAULT CHARSET=latin1row in set (0.00 sec)

虽然c2列指定了desc,但在实际的建表语句中还是将其忽略了。再来看看MySQL 8.0的结果。 

mysql> show create table slowtech.t1/G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx_c1_c2` (`c1`,`c2` DESC)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cirow in set (0.00 sec)

c2列还是保留了desc子句。

降序索引的意义

如果一个查询,需要对多个列进行排序,且顺序要求不一致。在这种场景下,要想避免数据库额外的排序-“filesort”,只能使用降序索引。还是上面这张表,来看看有降序索引和没有的区别。

MySQL 5.7

mysql> explain select * from slowtech.t1 order by c1,c2 desc;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+| 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+row in set, 1 warning (0.00 sec)

MySQL 8.0

mysql> explain select * from slowtech.t1 order by c1,c2 desc;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+row in set, 1 warning (0.00 sec)

两者的对比可以看出,MySQL 8.0因为降序索引的存在,避免了“filesort”。

这其实是降序索引的主要应用场景。如果只对单个列进行排序,降序索引的意义不是太大,无论是升序还是降序,升序索引完全可以应付。还是同样的表,看看下面的查询。

MySQL 5.7

mysql> explain select * from slowtech.t1 order by c1;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+row in set, 1 warning (0.00 sec)mysql> explain select * from slowtech.t1 order by c1 desc;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+row in set, 1 warning (0.00 sec)

虽然c1是升序索引,但在第二个查询中,对其进行降序排列时,并没有进行额外的排序,使用的还是索引。在这里,大家容易产生误区,以为升序索引就不能用于降序排列,实际上,对于索引,MySQL不仅支持正向扫描,还可以反向扫描。反向扫描的性能同样不差。以下是官方对于降序索引的压测结果,测试表也只有两列(a,b),建了一个联合索引(a desc,b asc),感兴趣的童鞋可以看看,http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

 

而在8.0中,对于反向扫描,有一个专门的词进行描述“Backward index scan”。

mysql> explain select * from slowtech.t1 order by c1;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+row in set, 1 warning (0.00 sec)mysql> explain select * from slowtech.t1 order by c1 desc;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+| 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Backward index scan; Using index |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+row in set, 1 warning (0.00 sec)

终于不再对group by进行隐式排序

由于降序索引的引入,MySQL 8.0再也不会对group by操作进行隐式排序。

下面看看MySQL 5.7和8中的测试情况    

create table slowtech.t1(id int);insert into slowtech.t1 values(2);insert into slowtech.t1 values(3);insert into slowtech.t1 values(1);

MySQL 5.7

mysql> select * from slowtech.t1 group by id;+------+| id |+------+| 1 || 2 || 3 |+------+rows in set (0.00 sec)mysql> explain select * from slowtech.t1 group by id;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| 1 | SIMPLE  | t1 | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+row in set, 1 warning (0.00 sec)

“Using filesort”,代表查询中有排序操作,从结果上看,id列确实也是升序输出。

MySQL 8.0

mysql> select * from slowtech.t1 group by id;+------+| id |+------+| 2 || 3 || 1 |+------+rows in set (0.00 sec)mysql> explain select * from slowtech.t1 group by id;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+| 1 | SIMPLE  | t1 | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | Using temporary |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+row in set, 1 warning (0.01 sec)

不仅结果没有升序输出,执行计划中也没有“Using filesort”。

可见,MySQL 8.0对于group by操作确实不再进行隐式排序。

从5.7升级到8.0,依赖group by隐式排序的业务可要小心咯。

参考文档

http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

责任编辑:自由哥

免责声明:建站教学网内容来源于本网和互联网,如媒体、公司、企业或个人对该部分主张知识产权,请与本站联系,本网站将迅速采取适当措施,否则,与之有关的知识产权纠纷本网站不承担任何责任。
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
CopyRight 2008-2019, JZREAD.COM, Inc. All Rights Reserved
皖ICP备07015033号