MySQL中的降序索引（附带实例）

在 MySQL 8.0 之前，MySQL 虽然在语法上已经支持降序索引，但实际上创建的仍然是升序索引。而从 MySQL 8.0 开始，MySQL 支持创建降序索引。

下面通过案例来对比不同的版本中对降序索引的支持情况。

分别在 MySQL 5.7 版本和 MySQL 9.0 版本中创建数据表 ts1：

mysql> CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
Query OK, 0 rows affected (0.13 sec)

在 MySQL 5.7 版本中查看数据表 ts1 的结构，结果如下：

mysql> show create table ts1\G
*************************** 1. row ***************************
      Table: ts1
Create Table: CREATE TABLE `ts1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

从结果中可以看出，索引仍然是默认的升序。

在 MySQL 9.0 版本中查看数据表 ts1 的结构，结果如下：

mysql>show create table ts1\G
*************************** 1. row ***************************
       Table: ts1
Create Table: CREATE TABLE `ts1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

从结果中可以看出，索引已经是降序了。

下面继续测试降序索引在执行计划中的表现。

分别在 MySQL 5.7 版本和 MySQL 9.0 版本的数据表 ts1 中插入 8 万条随机数据，SQL 语句如下：

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE ts_insert ()
mysql> BEGIN
mysql> DECLARE i INT DEFAULT 1;
mysql> WHILE i<80000
mysql> DO
mysql> insert into ts1 select rand()*80000, rand()*80000;
mysql> SET i=i+1;
mysql> END WHILE ;
mysql> commit;
mysql> END;;
mysql> DELIMITER ;
mysql> CALL ts_insert();

在 MySQL 5.7 版本中查看数据表 ts1 的执行计划，结果如下：

mysql> explain select * from ts1 order by a , b desc limit 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_le  | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | ts1   | NULL       | index | NULL          | idx_a_b | 10      |NULL  | 79999 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+

从结果中可以看出，执行计划中扫描数为 79999，而且使用了 Using filesort。

提示　Using filesort 是 MySQL 里一种速度比较慢的外部排序，如果能避免使用它，当然是最好的。多数情况下，管理员可以通过优化索引来尽量避免出现 Using filesort，从而提高数据库执行速度。

在 MySQL 9.0 版本中查看数据表 ts1 的执行计划，结果如下：

mysql> explain select * from ts1 order by a , b desc limit 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ts1   | NULL       | index | NULL          | idx_a_b | 10      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

从结果中可以看出，执行计划中扫描数为 5，而且没有使用 Using filesort。

注意，降序索引只对查询中特定的排列顺序有效，如果使用不当，反而使用查询效率更低。例如上述查询排序条件如果改为“order by a desc, b desc”，则 MySQL 5.7 的执行计划要明显好于 MySQL 9.0。

将排序条件修改为“order by a desc, b desc”后，下面来对比不同版本中的执行计划的效果。

在 MySQL 5.7 版本中查看数据表 ts1 的执行计划，结果如下：

mysql> explain select * from ts1 order by a desc , b desc limit 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ts1   | NULL       | index | NULL          | idx_a_b | 10      |NULL  |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

在 MySQL 9.0 版本中查看数据表 ts1 的执行计划，结果如下：

mysql> explain select * from ts1 order by a desc , b desc limit 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | ts1   | NULL       | index | NULL          | idx_a_b | 10      | NULL | 80133 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+

对比结果可以看出，修改排序条件后，MySQL 5.7 的执行计划要明显好于 MySQL 9.0。