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
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2022-12-13，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。