MySQL 8 新特性之降序索引

我不是女神ヾ 2022-02-18 11:17 216阅读 0赞

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

![复制代码][copycode.gif]

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
    1 row in set (0.00 sec)

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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
    1 row in set (0.00 sec)

![复制代码][copycode.gif]

c2列还是保留了desc子句。

**降序索引的意义**

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

**MySQL 5.7**

![复制代码][copycode.gif]

![复制代码][copycode.gif]

**MySQL 8.0**

![复制代码][copycode.gif]

![复制代码][copycode.gif]

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

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

**MySQL 5.7**

![复制代码][copycode.gif]

![复制代码][copycode.gif]

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

![576154-20180522153109865-1849380543.png][]

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

**MySQL 8.0**

![复制代码][copycode.gif]

![复制代码][copycode.gif]

**终于不再对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**

![复制代码][copycode.gif]

mysql> select * from slowtech.t1 group by id;
    +------+
    | id  |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    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 |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
    1 row in set, 1 warning (0.00 sec)

![复制代码][copycode.gif]

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

**MySQL 8.0**

![复制代码][copycode.gif]

mysql> select * from slowtech.t1 group by id;
    +------+
    | id  |
    +------+
    |    2 |
    |    3 |
    |    1 |
    +------+
    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 |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
    1 row in set, 1 warning (0.01 sec)

![复制代码][copycode.gif]

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

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

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

[copycode.gif]: /images/20220218/e80c5ac635e54c8d8cc278007cfcfda2.png
[http_mysqlserverteam.com_mysql-8-0-labs-descending-indexes-in-mysql]: https://www.cnblogs.com/ivictor/p/%E8%99%BD%E7%84%B6c1%E6%98%AF%E5%8D%87%E5%BA%8F%E7%B4%A2%E5%BC%95%EF%BC%8C%E4%BD%86%E5%9C%A8%E7%AC%AC%E4%BA%8C%E4%B8%AA%E6%9F%A5%E8%AF%A2%E4%B8%AD%EF%BC%8C%E5%AF%B9%E5%85%B6%E8%BF%9B%E8%A1%8C%E9%99%8D%E5%BA%8F%E6%8E%92%E5%88%97%E6%97%B6%EF%BC%8C%E5%B9%B6%E6%B2%A1%E6%9C%89%E8%BF%9B%E8%A1%8C%E9%A2%9D%E5%A4%96%E7%9A%84%E6%8E%92%E5%BA%8F%EF%BC%8C%E4%BD%BF%E7%94%A8%E7%9A%84%E8%BF%98%E6%98%AF%E7%B4%A2%E5%BC%95%E3%80%82%E5%9C%A8%E8%BF%99%E9%87%8C%EF%BC%8C%E5%A4%A7%E5%AE%B6%E5%AE%B9%E6%98%93%E4%BA%A7%E7%94%9F%E8%AF%AF%E5%8C%BA%EF%BC%8C%E4%BB%A5%E4%B8%BA%E5%8D%87%E5%BA%8F%E7%B4%A2%E5%BC%95%E5%B0%B1%E4%B8%8D%E8%83%BD%E7%94%A8%E4%BA%8E%E9%99%8D%E5%BA%8F%E6%8E%92%E5%88%97%EF%BC%8C%E5%AE%9E%E9%99%85%E4%B8%8A%EF%BC%8C%E5%AF%B9%E4%BA%8E%E7%B4%A2%E5%BC%95%EF%BC%8CMySQL%E4%B8%8D%E4%BB%85%E6%94%AF%E6%8C%81%E6%AD%A3%E5%90%91%E6%89%AB%E6%8F%8F%EF%BC%8C%E8%BF%98%E5%8F%AF%E4%BB%A5%E5%8F%8D%E5%90%91%E6%89%AB%E6%8F%8F%E3%80%82%E5%8F%8D%E5%90%91%E6%89%AB%E6%8F%8F%E7%9A%84%E6%80%A7%E8%83%BD%E5%90%8C%E6%A0%B7%E4%B8%8D%E5%B7%AE%E3%80%82%E4%BB%A5%E4%B8%8B%E6%98%AF%E5%AE%98%E6%96%B9%E5%AF%B9%E4%BA%8E%E9%99%8D%E5%BA%8F%E7%B4%A2%E5%BC%95%E7%9A%84%E5%8E%8B%E6%B5%8B%E7%BB%93%E6%9E%9C%EF%BC%8C%E6%B5%8B%E8%AF%95%E8%A1%A8%E4%B9%9F%E5%8F%AA%E6%9C%89%E4%B8%A4%E5%88%97%EF%BC%88a%EF%BC%8Cb%EF%BC%89%EF%BC%8C%E5%BB%BA%E4%BA%86%E4%B8%80%E4%B8%AA%E8%81%94%E5%90%88%E7%B4%A2%E5%BC%95%28a%20desc,b%20asc%29%EF%BC%8C%E6%84%9F%E5%85%B4%E8%B6%A3%E7%9A%84%E7%AB%A5%E9%9E%8B%E5%8F%AF%E4%BB%A5%E7%9C%8B%E7%9C%8B%EF%BC%8Chttp://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/
[576154-20180522153109865-1849380543.png]: /images/20220218/3905b06370044fa39c1c77a2bf91a401.png