MySQL 升级后查询性能跳水，排序竟成“罪魁祸首”？-51CTO.COM

1.背景及分析

近期，某客户完成对数据库 MySQL 5.7 到 8.0 的版本升级，升级后查询性能显著变慢。

原来是 MySQL 8.0 对某些 ORDER BY 相关的参数修改，导致了优化器不生效。下面我们进入本次的 SQL 优化分析，也建议升级后有类似情况的读者自检。

分析过程

首先，查看慢日志及对应的表结构。

慢 SQL：select * from xx where xx order by xx limit xx.
执行计划：发现 order by 的字段没有索引，若加上索引可从原来的 4 秒变为毫秒级别。

在升级前（MySQL 5.7），该字段没有索引，查询只需 1 秒左右，需要找到这个原因。

通过执行计划（profile，trace 等方式）对比了升级前后的区别，发现只有 profile 会有明显的区别。其中，MySQL 5.7 的耗时主要在 Creating sort index 阶段，而 MySQL 8.0 的耗时都是在执行阶段。

在 MySQL 8.0 中 SELECT 少数字段时间也在 1 秒左右。随着 SELECT 查询的字段增多，时间也越来越长。当 select * 时能达到 4 秒，而 MySQL 5.7 中不管多少字段都是 1 秒左右。

根据以上信息可以推测，变慢主要在排序环节，需要进一步了解 MySQL 8.0 的排序方式发生了哪些改变。

通过 MySQL 官网文档^[1] 可知：

MySQL 8.0.20 之前的版本：排序跟 max_length_for_sort_data 参数有关。当需要排序的行的大小大于参数设置对应的值时（byte），会使用 row_id 排序，反之使用全字段排序。通过测试，在 MySQL 5.7 版本时，设置参数的值若大于所有列对应的大小，select * 查询也需要耗时 4 秒左右。
MySQL 8.0.20 及之后的版本：max_length_for_sort_data 参数被废弃，不再生效。

官网中对该参数的调整说明

分析完毕，下面我们将进行验证。

2.验证测试

在本次升级涉及的具体版本是 MySQL 5.7.44 和 MySQL 8.0.30。根据上面的分析过程，推断 MySQL 5.7.44 版在涉及到排序查询时会受到 max_length_for_sort_data 的影响，而 MySQL 8.0.30 则不会。

数据准备

在 MySQL 5.7 和 8.0 版本库中建表并插入 400W 行数据。

CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`create_date` datetime DEFAULT NULL,
`status` int DEFAULT NULL,
`col1` varchar(50) DEFAULT NULL,
`col2` varchar(50) DEFAULT NULL,
`col3` varchar(50) DEFAULT NULL,
`col4` varchar(50) DEFAULT NULL,
`col5` varchar(50) DEFAULT NULL,
`col6` varchar(50) DEFAULT NULL,
`col7` varchar(50) DEFAULT NULL,
`col8` varchar(50) DEFAULT NULL,
`col9` varchar(50) DEFAULT NULL,
`col10` varchar(50) DEFAULT NULL,
`col11` varchar(255) DEFAULT NULL,
`col12` varchar(255) DEFAULT NULL,
`col13` varchar(255) DEFAULT NULL,
`col14` varchar(255) DEFAULT NULL,
`col15` varchar(255) DEFAULT NULL,
`col16` varchar(255) DEFAULT NULL,
`col17` varchar(255) DEFAULT NULL,
`col18` varchar(255) DEFAULT NULL,
`col19` varchar(255) DEFAULT NULL,
`col20` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 插入数据过程略

select count(*) from `t`;
+-------------+
|  count( * ) |
+-------------+
|     4194304 |
+-------------+
1 row in set (0.11 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

在 MySQL 5.7 和 8.0 版本环境中执行（参数配置一致），分别执行查询三个字段和查询所有字段两种 SELECT 语句。

-- 查询三个字段
select id,create_date,status from t  where status=1 order by create_date desc limit 1;

-- 查询所有字段
select * from t  where status=1 order by create_date desc limit 1;1.
2.
3.
4.
5.

MySQL 5.7 的两种 SELECT 语句执行时间均为 1 秒左右。

MySQL 5.7 两种查询对比