MYSQL8.0新特性---降序索引

数据库知多少 2021-04-26

936

MYSQL8.0新特性---降序索引

数据库版本

Server version: 8.0.18 MySQL Community Server -GPL

降序索引简介

在上一篇文章，我们介绍了MySQL8.0中新增的不可见索引，今天我们还是来谈一谈跟索引相关的内容---降序索引。其实降序索引在MySQL 4中就已经支持这个语法了，但是在实际创建中，MySQL数据库会忽略掉该索引，并不会真正的创建降序索引；但是可以按照反向顺序扫描索引，不过会有一定的性能牺牲。从MySQL8.0开始，真正支持降序索引，通过在索引定义中使用desc关键字，来创建降序索引，并且存储的索引键值按照降序的方式进行存储；目前仅支持InnoDB引擎上的降序索引。

MySQL supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order.
https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

降序索引使用

我们我们从数据库层面来实际看看降序索引。

首先，在不同版本中降序索引的展现形式：

5.7版本：

root@MYSQL:[DB(dkf)]>CREATE TABLE dkf_test(
    ->   charid int,
    ->   charage int,
    ->   KEY idx_id_age (charid asc,charage desc)
    -> );
Query OK, 0 rows affected (0.03 sec)

看下索引的定义，我们看到，5.7版本的创建表的语句中虽然指定了desc关键字，但是在实际的表定义中，数据库将desc关键字忽略掉了，并没有按照索引降序的规则去创建该索引，还是按照升序(asc)的方式进行了索引创建。

root@MYSQL:[DB(dkf)]>show create table dkf_test\G
*************************** 1. row ***************************
       Table: dkf_test
Create Table: CREATE TABLE `dkf_test` (
  `charid` int(11) DEFAULT NULL,
  `charage` int(11) DEFAULT NULL,
  KEY `idx_id_age` (`charid`,`charage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

8.0版本：

同样的建表语句，我们看看在MySQL8.0中的表定义。8.0版本的表定义语句中包含了创建创建表语句中的索引降序(desc)的关键字，也就是MySQL8.0开始真正的支持降序索引的使用了。

root@MYSQL:[DB(dkf)]>show create table dkf_test\G
*************************** 1. row ***************************
       Table: dkf_test
Create Table: CREATE TABLE `dkf_test` (
  `charid` int(11) DEFAULT NULL,
  `charage` int(11) DEFAULT NULL,
  KEY `idx_id_age` (`charid`,`charage` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

其实降序索引的使用场景并不是很大，主要是在查询中针对多列需要查询在不同列顺序情况下的排序方式的场景，通过使用降序索引，就可以避免数据库额外的 "filesort"排序操作。

我们看下在不同排序方式下不同数据库版本中的表现：

5.7版本：

root@MYSQL:[DB(dkf)]>explain select * from dkf.dkf_test order by charid,charage desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dkf_test
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_id_age
      key_len: 10
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

8.0版本：

root@MYSQL:[DB(dkf)]>explain select * from dkf_test order by charid,charage desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dkf_test
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_id_age
      key_len: 10
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

通过查看两个版本中同样SQL的执行计划，我们可以看出，MySQL 8.0因为使用了降序索引，真正避免了“filesort”操作。如果仅仅只对单个列进行排序，降序索引的意义就不大了，因为无论索引是升序还是降序，只不过是数据扫描的方向是不同的(叶子节点是双向链表)，简单的升序索引完全可以应付。我们可以在数据库中直观的观察下：

root@MYSQL:[DB(dkf)]>create index idx_charage on dkf_test(charage);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

我们创建了一个单独的升序索引，idx_charage，可以看下不同排序方式下的索引使用情况（8.0版本）：

charage的正向排序：

root@MYSQL:[DB(dkf)]>explain select charage from dkf_test order by charage\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dkf_test
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_charage
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

charage的反向排序：

root@MYSQL:[DB(dkf)]>explain select charage from dkf_test order by charage desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dkf_test
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_charage
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Backward index scan; Using index
1 row in set, 1 warning (0.00 sec)

通过对比，我们看到，单独的索引条件下，如果按照查找字段的降序排序的话，还是通过使用升序索引的方式进行的查找，不过查找的方向改为了“Backward index scan”（8.0之前的版本不会提示Backward index scan），也就是对索引进行的反向查找，并没有进行额外的排序。

注：所谓排序，就是按照数据/字符的排列规则进行组织的方式。对于数值类型来说，升序就是对数值进行大小比较后，小的在前，大的在后，在数据库中如果是B+树索引的话就是小的在左，大的在右；降序就是对数值进行大小比较后，大的在前，小的在后，数据库中如果是B+树的话就是大的在左，小的在右。

总结

对于降序索引的使用场景目前来说还是比较少的，但是对于特定场景的SQL提升还是很明显的，我们在使用降序索引的时候也要注意一些使用的条件。

降序索引的使用:

降序索引和升序索引一样，支持所有的数据类型
distinct可以使用降序索引
降序索引支持btree，但是不支持hash索引
降序索引不支持全文索引和空间索引
降序索引支持常规索引，和gernerated列(virtual\stored类型的列)
对于使用了聚合函数，在无group by时，min()/max()中不会使用带有降序键的索引
由于降序索引的引入，MySQL8.0不会对group by操作进行隐式排序。

完

=end=

mysql

文章转载自数据库知多少，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MYSQL8.0新特性---降序索引

降序索引简介

降序索引使用

总结

完

评论