使用过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);
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 |+----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
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);
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 |+----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
+-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| 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
2. mysql8.0新增用户及加密规则修改的那些事
3. 比hive快10倍的大数据查询利器-- presto
4. 监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库
5. PostgreSQL主从复制--物理复制
6. MySQL传统点位复制在线转为GTID模式复制
原文作者：数据库干货铺；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
