MySQL 5.7带ORDER BY查询更优的原因探究及MySQL 8迁移分析

问题现象 #

在MySQL 5.7中执行查询时，带ORDER BY name的版本仅耗时86ms，移除ORDER BY name后耗时长达6s。两种查询均为全表扫描，带ORDER BY的查询触发Using filesort；但在同结构同数据的MySQL 8.0.29中，有无ORDER BY的查询性能无差异。

查询语句：

select
    *
from
    employee
where
    (admission_date between '2020-01-01 23:59:59' and '2022-07-25 23:59:59')
    and registered = 0
    and payment = 1
order by
    name;

基础信息 #

• 表引擎：InnoDB
• name字段无索引
• 表结构：

CREATE TABLE `employee` (
   `id` int(10) NOT NULL AUTO_INCREMENT,
   `name` varchar(100) DEFAULT '',
   `payment` int(1) DEFAULT '0',
   `registered` int(1) DEFAULT '0',
   `temp_file_1` blob,
   `temp_file_2` blob,
   `temp_file_3` blob,
   `temp_file_4` blob,
   `admission_date` datetime DEFAULT NULL,
   PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=123594854 DEFAULT CHARSET=latin1

• 统计信息：

Row Count: 144183
Row format: Dynamic
Max data length: 0
Index length: 0
Data free: 3M
Data Length: 6.5G
Avg Row Length: 48039

性能差异原因（MySQL 5.7 vs 8.0） #

1. MySQL 5.7优化器的执行逻辑差异 #

• 无ORDER BY时：优化器按主键顺序逐行扫描过滤，由于表包含4个BLOB字段，InnoDB Dynamic行格式会将BLOB数据存放在溢出页，逐行读取时需频繁访问溢出页，产生大量随机IO，导致耗时剧增。
• 带ORDER BY时：触发Using filesort，优化器会先将符合条件的行的**排序键（name）+主键（id）**读取到内存临时表，排序后再通过主键回表获取完整数据。仅读取少量字段（name和id）避免了直接访问BLOB溢出页，IO操作大幅减少，因此耗时显著降低。

2. MySQL 8.0的优化改进 #

MySQL 8.0优化器针对这类场景做了逻辑优化：即使无ORDER BY，也会自动选择“先筛选主键再回表”的执行路径，避免了全表扫描时读取大量BLOB溢出页的开销，因此有无ORDER BY的性能差异消失。

MySQL 5.7下的优化策略 #

1. 添加复合索引 #

创建包含过滤条件+排序字段的复合索引，直接覆盖查询需求，避免全表扫描：

CREATE INDEX idx_admission_registered_payment_name ON employee(admission_date, registered, payment, name);

若不需要查询所有字段，可改为只查询所需字段，实现覆盖索引，彻底避免回表操作。

2. 手动调整执行逻辑 #

通过子查询先获取符合条件的主键，再回表查询，模拟带ORDER BY时的高效执行路径：

SELECT e.*
FROM employee e
JOIN (
    SELECT id
    FROM employee
    WHERE admission_date BETWEEN '2020-01-01 23:59:59' AND '2022-07-25 23:59:59'
      AND registered = 0
      AND payment = 1
) AS t ON e.id = t.id;

3. 调整配置参数 #

• 增大sort_buffer_size：确保排序操作在内存完成，避免磁盘排序的额外开销（注意不要设置过大引发内存竞争）。
• 调整read_rnd_buffer_size：优化随机读取的缓存大小，减少回表时的IO次数。

内容的提问来源于stack exchange，提问作者Marques Karlx