mySQL定义降序索引_MySQL8新特性：降序索引详解

前言

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: t1

create table: create table `t1` (

`c1` int(11) default null,

`c2` int(11) default null,

key `idx_c1_c2` (`c1`,`c2`)

) engine=innodb default charset=latin1

row in set (0.00 sec)

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

mysql> show create table slowtech.t1\g

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

table: t1

create 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_ci

row 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/

总结

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

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！