优秀的编程知识分享平台

网站首页 > 技术文章 正文

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

nanyue 2025-03-06 17:54:43 技术文章 6 ℃

性能问题简介

在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_1\G;
*************************** 1. row ***************************
       Table: t_test_1
Create 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
1 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_1\G;
*************************** 1. row ***************************
       Table: t_test_1
Create 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_ci
1 row in set (0.01 sec)

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

delimiter $
create PROCEDURE p_create_data(IN loop_times INT)
begin
declare var int default 1;

while var < loop_times DO
set 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.如果您特别喜欢，请加关注。

Tags：

猜你喜欢

最近发表
标签列表