mysql降序索引未启用_MySQL 8 新特性之降序索引

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

无图无真相,同一个建表语句,看看MySQL 5.7和8.0的区别。create table slowtech.t1(c1int,c2int,index idx_c1_c2(c1,c2 desc));

提示,类似代码可左右滑动

MySQL 5.7mysql> show create table slowtech.t1\G

***************************1.row ***************************

Table: t1

CreateTable: CREATE TABLE`t1`(

`c1`int(11) DEFAULT NULL,

`c2`int(11) DEFAULT NULL,

KEY`idx_c1_c2`(`c1`,`c2`)

) ENGINE=InnoDBDEFAULT CHARSET=latin1

1rowinset(0.00sec)

虽然c2列指定了desc,但在实际的建表语句中还是将其忽略了。再来看看MySQL 8.0的结果。mysql> show create table slowtech.t1\G

***************************1.row ***************************

Table: t1

CreateTable: CREATE TABLE`t1`(

`c1`int(11) DEFAULT NULL,

`c2`int(11) DEFAULT NULL,

KEY`idx_c1_c2`(`c1`,`c2`DESC)

) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1rowinset(0.00sec)

c2列还是保留了desc子句。

降序索引的意义

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

MySQL 5.7mysql>explainselect*fromslowtech.t1 orderbyc1,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|Usingindex;Usingfilesort|

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+

1rowinset,1warning(0.00sec)

MySQL 8.0mysql>explainselect*fromslowtech.t1 orderbyc1,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|Usingindex|

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1rowinset,1warning(0.00sec)

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

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

MySQL 5.7mysql>explainselect*fromslowtech.t1 orderbyc1;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

|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|Usingindex|

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1rowinset,1warning(0.00sec)

mysql>explainselect*fromslowtech.t1 orderbyc1 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|Usingindex|

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1rowinset,1warning(0.00sec)

虽然c1是升序索引,但在第二个查询中,对其进行降序排列时,并没有进行额外的排序,使用的还是索引。

在这里,大家容易有个误区,以为升序索引就不能用于降序排列,实际上,对于索引,MySQL不仅支持正向扫描,还可以反向扫描。反向扫描的性能同样不差。

以下是官方对于降序索引的压测结果,测试表也只有两列(a,b),建了一个联合索引(a desc,b asc),感兴趣的童鞋可以看看,http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

27aebfca61f9f3cfdb79efb03da3cb08.png

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

MySQL 8.0mysql>explainselect*fromslowtech.t1 orderbyc1;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

|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|Usingindex|

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1rowinset,1warning(0.00sec)

mysql>explainselect*fromslowtech.t1 orderbyc1 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|Backwardindex scan;Usingindex|

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+

1rowinset,1warning(0.00sec)

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

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

下面看看MySQL 5.7和8中的测试情况create table slowtech.t1(idint);

insertintoslowtech.t1 values(2);

insertintoslowtech.t1 values(3);

insertintoslowtech.t1 values(1);

MySQL 5.7mysql>select*fromslowtech.t1groupbyid;

+------+

|id|

+------+

|1|

|2|

|3|

+------+

3rowsinset(0.00sec)

mysql>explainselect*fromslowtech.t1groupbyid;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

|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|Usingtemporary;Usingfilesort|

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

1rowinset,1warning(0.00sec)

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

MySQL 8.0mysql>select*fromslowtech.t1groupbyid;

+------+

|id|

+------+

|2|

|3|

|1|

+------+

3rowsinset(0.00sec)

mysql>explainselect*fromslowtech.t1groupbyid;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+

|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|Usingtemporary|

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+

1rowinset,1warning(0.01sec)

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

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

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

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页