mysql 降序_使用MySQL8降序索引解决排序性能问题

性能问题简介

在MySQL8之前的版本，如果order by中既有升序，又有降序两种混合排序，数据库走了索引之后，还需要使用文件排序，才能获得应用所需要的结果，由于多了文件排序操作，所以SQL语句的执行效率会很低。

索引排序

在某个字段上创建普通索引之后，大家都知道，字段的值在索引结构里是排过序的，在MySQL8以前的版本，只支持升序排序(asc)，不支持降序排序(desc)。虽然在MySQL5.7的版本中，可以创建降序索引的语法，可是在数据库底层是没有实现其功能的，真正降序功能是在MySQL8。

优化案例

创建模拟表和数据

分别在MySQL8.0.18和MySQL5.7.28上创建好测试表

mysql> select version();+-----------+| version() |+-----------+| 5.7.28    |+-----------+1 row in set (0.01 sec)mysql> show create table t_test_1G;*************************** 1. row ***************************       Table: t_test_1Create Table: CREATE TABLE `t_test_1` (  `id` int(11) NOT NULL,  `col_1` int(11) DEFAULT NULL,  `col_2` char(10) NOT NULL DEFAULT '',  `col_3` varchar(10) NOT NULL DEFAULT '',  `check_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.01 sec)mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.01 sec)mysql> show create table t_test_1G;*************************** 1. row ***************************       Table: t_test_1Create Table: CREATE TABLE `t_test_1` (  `id` int(11) NOT NULL,  `col_1` int(11) DEFAULT NULL,  `col_2` char(10) NOT NULL DEFAULT '',  `col_3` varchar(10) NOT NULL DEFAULT '',  `check_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)

使用存储过程进行造数，调用CALL p_create_data(15000);，可以制造15000条记录。

delimiter $$create PROCEDURE p_create_data(IN loop_times INT)begindeclare var int default 1;while var < loop_times DOset var=var+1;INSERT INTO `t_test_1`( `id`,`col_1`, `col_2`, `col_3`) VALUES       (var,var,var,var);end while;end$$delimiter ;CALL p_create_data(15000);

测试SQL

在不使用降序索引的情况下，来看看SQL语句在MySQL8.0.18和MySQL5.7.28执行计划

测试SQL语句，col_2字段是升序排序，而col_3字段按照降序排序。

select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;

分别在MySQL8.0.18和MySQL5.7.28创建col_2,col_3的复合索引

mysql> alter table t_test_1 add index (col_2,col_3);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0  Duplicates: 0  Warnings: 1

MySQL8.0.18执行计划

mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.00 sec)mysql> explain select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+| id | select_type | table    | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+|  1 | SIMPLE      | t_test_1 | NULL       | range | col_2         | col_2 | 40      | NULL |  100 |   100.00 | Using index condition; Using filesort |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.01 sec)

MySQL5.7.28执行计划

mysql> select version();+-----------+| version() |+-----------+| 5.7.28    |+-----------+1 row in set (0.00 sec)mysql> explain select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+| id | select_type | table    | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+|  1 | SIMPLE      | t_test_1 | NULL       | range | col_2,col_2_2 | col_2 | 40      | NULL |  100 |   100.00 | Using index condition; Using filesort |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.01 sec)

当在MySQL8.0.18和MySQL5.7.28创建索引时，不制定列的排列顺序，字段默认都是升序排序的，所以这种情况，无论是在5.7还是8的版本，都需要做filesort。

降序索引

在MySQL8.0.18里创建索引可以制定列的排列顺序，所以按照SQL的order by字段排列顺序，我们可以在创建复合索引时，制定列的排列顺序和order by字段排序顺序一致，这样SQL就不用再做filesort了，来一起看看效果吧。

在MySQL8.0.18中创建降序索引

mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.00 sec)mysql> alter table t_test_1 add index (col_2,col_3 desc);Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0

查看执行计划

mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.00 sec)mysql> explain select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | t_test_1 | NULL       | range | col_2_2       | col_2_2 | 40      | NULL |  100 |   100.00 | Using index condition |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

可以看到使用了降序索引的SQL语句，已经不再需要做filesort了，MySQL8.0.18的这个降序索引新特性真的能解决一些排序场景的性能问题，还是一个很使用的新功能。

关注

1.如果您喜欢这篇文章，请点赞+转发。

2.如果您特别喜欢，请加关注。