MySQL 8.0新特性—降序索引

降序索引（Descending Indexes）

先看下MySQL 8.0官方文档介绍：

DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient.

大概意思是说在以前的版本是支持降序的方式扫描索引的，但是会带来额外的开销，降低性能。MySQL 8.0版本对此进行了改进，支持降序索引，降序索引可以高效率地以从后向前的顺序进行索引扫描。

测试

MySQL版本号: 8.0.1

测试表结构：

CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u1` int(10) unsigned NOT NULL DEFAULT 0,
  `u2` int(10) unsigned NOT NULL DEFAULT 0,
  `u3` varchar(20) NOT NULL DEFAULT ,
  `u4` varchar(35) NOT NULL DEFAULT ,
  PRIMARY KEY (`id`),
  KEY `u1` (`u1` DESC,`u2`)
) ENGINE=InnoDB AUTO_INCREMENT=131054 DEFAULT CHARSET=utf8mb4

表统计信息：

show table status like t1G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 101970
 Avg_row_length: 87
    Data_length: 8929280
Max_data_length: 0
   Index_length: 3686400
      Data_free: 4194304
 Auto_increment: 131054
    Create_time: 2020-08-17 11:57:48
    Update_time: 2020-08-17 12:08:09
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

参考： https://imysql.com/2020/07/14/mysql-8-0-new-index-features.shtml https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html