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)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 两种查询对比

MySQL 8.0查询三个字段 1 秒左右，查询所有字段则为 4 秒左右。

MySQL 8.0 两种查询对比

在 MySQL 8.0.30 查询的字段越多，时间越长。

对比查询 7 个字段和 11 个字段

若在 MySQL 5.7.44 中，把 max_length_for_sort_data 参数的值设置大于所有列的大小时，查询时间也会变慢（全字段排序）。

3.结论及优化方案

MySQL 8.0.20 及之后的版本，针对无索引的排序方式发现改变。不会再通过 max_length_for_sort_data 参数来判断，而是通过查询的字段和排序的字段大小动态来进行排序。所以在查询列较多时会导致比 MySQL 5.7 更慢。

最好的解决方式是源码库给排序字段加上索引 :)

参考资料

[1]order-by-optimization: https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

作者：龚唐杰，爱可生 DBA 团队成员，主要负责 MySQL 技术支持，擅长 MySQL、PG、国产数据库。