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

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)

在 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;

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

MySQL 5.7 两种查询对比