MySQL 降序索引简介

使用过Oracle、SQLServer数据库的降序索引的同学，可能在使用MySQL8.0之前版本时有个疑惑，明明我已经创建了将需要索引，但是为何执行时走不了索引或者效果不理想？

1. 创建环境

分别在MySQL5.7 及MySQL8.0版本中创建如下表及数据

# 创建表
 create table   test1(
 id int primary key auto_increment,
 name  varchar(100),
 create_time datetime
 );


# 插入部分测试数据，有条件的创建更多数据更佳
 insert into  test1(name,creatE_time) values('anniuadaOAIFAPUHIA','2020-07-01 12:00:00');
 insert into  test1(name,creatE_time) values('CWQSsar3qcssg','2020-07-01 15:00:00');
 insert into  test1(name,creatE_time) values('vxfqrt2adafz','2020-07-01 21:30:00');
 insert into  test1(name,creatE_time) values('etxzwrwbdhegqgaheqhag','2020-07-02 01:30:00');
 insert into  test1(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 03:30:00');
 insert into  test1(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 07:32:00');
 insert into  test1(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 10:32:00');
 insert into  test1(name,creatE_time) values('tuilklmdadq','2020-07-02 15:32:00');
 insert into  test1(name,creatE_time) values('wesv2wqdshehq','2020-07-02 20:32:00');
 insert into  test1(name,creatE_time) values('89yoijnlkwr1','2020-07-03 02:56:00');
 insert into  test1(name,creatE_time) values('olj;nsaaq','2020-07-03 08:41:00');
 insert into  test1(name,creatE_time) values('ygo;jkdsaq','2020-07-03 16:20:00');

2. MySQL5.7中创建索引并查看执行计划

2.1 MySQL5.7中创建升序索引

在MySQL5.7中创建升序索引，并执行SQL查看执行计划

# 升序索引
alter table test1 add key  idx_nameAsc_createtimeAsc( name,create_time);

执行语句查看执行计划

mysql> explain  select * from  test1 order by  name desc ,create_time ;
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | test1 | NULL       | index | NULL          | idx_nameAsc_createtimeAsc | 309     | NULL |   12 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+

2.2 MySQL5.7中创建降序索引

在MySQL5.7中创建降序索引，并执行SQL查看执行计划

# 创建降序索引
alter table test1 add key  idx_nameDesc_createtimeAsc( name desc ,create_time);

执行SQL并查看执行计划

mysql> explain  select * from  test1 order by  name desc ,create_time;
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | test1 | NULL       | index | NULL          | idx_nameAsc_createtimeAsc | 309     | NULL |   12 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+

发现使用的仍是升序索引，且用到了filesort

2.3 MySQL5.7中查看索引情况

查看索引情况会发现，MySQL5.7中，即使创建了降序索引，但是，排序方式依旧是升序（A[sc]）

mysql> show index from test1;
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 |          0 | PRIMARY                    |            1 | id          | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| test1 |          1 | idx_nameAsc_createtimeAsc  |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| test1 |          1 | idx_nameAsc_createtimeAsc  |            2 | create_time | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| test1 |          1 | idx_nameDesc_createtimeAsc |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| test1 |          1 | idx_nameDesc_createtimeAsc |            2 | create_time | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3. MySQL8.0中创建索引并查看执行计划

3.1 MySQL5.7中创建升序索引

在MySQL8.0中创建升序索引，并执行SQL查看执行计划

# 升序索引
alter table test1 add key  idx_nameAsc_createtimeAsc( name,create_time);

执行语句查看执行计划

mysql> explain  select * from  test1 order by  name desc ,create_time ;
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
|1| SIMPLE      | test1 | NULL       | index | NULL          | idx_nameAsc_createtimeAsc | 309     | NULL |   12 |100.00| Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+

结果和MySQL5.7 一致，也是需要进行filesort

3.2 MySQL8.0中创建降序索引

在MySQL8.0中创建降序索引，并执行SQL查看执行计划

# 创建降序索引
alter table test1 add key  idx_nameDesc_createtimeAsc( name desc ,create_time);

执行SQL并查看执行计划

mysql> explain  select * from  test1 order by  name desc ,create_time ;
+----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test1 | NULL       | index | NULL          | idx_nameDesc_createtimeAsc | 409     | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+

可见，MySQL8.0中的降序索引被使用到了，且排序无需进行filesort

3.3 MySQL8.0中查看索引情况

查看索引情况会发现，MySQL8.0中，升序索引及降序索引的排序方式出现了区分了

+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1 |          0 | PRIMARY                    |            1 | id          | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test1 |          1 | idx_nameAsc_createtimeAsc  |            1 | name        | A         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test1 |          1 | idx_nameAsc_createtimeAsc  |            2 | create_time | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test1 |          1 | idx_nameDesc_createtimeAsc |            1 | name        | D         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test1 |          1 | idx_nameDesc_createtimeAsc |            2 | create_time | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------

4. 小结

• MySQL5.7中，可以创建降序索引，但只是停留在语法层面上，到MySQL8.0才能使用上降序索引
• 另外，如果在MySQL5.7及之前版本，order by 多个字段时，建议排序方式一致（可以均升序或均降序），这样方可无需filesort