mysql降序索引

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

1. 创建环境

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

# 创建表 create tabletest1( id int primary key auto_increment, namevarchar(100), create_time datetime );# 插入部分测试数据，有条件的创建更多数据更佳 ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘anniuadaOAIFAPUHIA‘,‘2020-07-01 12:00:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘CWQSsar3qcssg‘,‘2020-07-01 15:00:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘vxfqrt2adafz‘,‘2020-07-01 21:30:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘etxzwrwbdhegqgaheqhag‘,‘2020-07-02 01:30:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘awrs433fsgvsfwtwg‘,‘2020-07-02 03:30:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘awrs433fsgvsfwtwg‘,‘2020-07-02 07:32:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘awrs433fsgvsfwtwg‘,‘2020-07-02 10:32:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘tuilklmdadq‘,‘2020-07-02 15:32:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘wesv2wqdshehq‘,‘2020-07-02 20:32:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘89yoijnlkwr1‘,‘2020-07-03 02:56:00‘); ｉｎｓｅｒｔ intotest1(name,creatE_time) values(‘olj;nsaaq‘,‘2020-07-03 08:41:00‘); ｉｎｓｅｒｔ intotest1(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 keyidx_nameAsc_createtimeAsc( name,create_time);

执行语句查看执行计划

mysql> explainselect * fromtest1 order byname 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 keyidx_nameDesc_createtimeAsc( name desc ,create_time);

执行SQL并查看执行计划

mysql> explainselect * fromtest1 order byname 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 keyidx_nameAsc_createtimeAsc( name,create_time);

执行语句查看执行计划

mysql> explainselect * fromtest1 order byname 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 keyidx_nameDesc_createtimeAsc( name desc ,create_time);

执行SQL并查看执行计划

mysql> explainselect * fromtest1 order byname 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