MySQL 8.0：filesort 性能退化的问题分析-阿里云开发者社区

MySQL 8.0：filesort 性能退化的问题分析

2024-09-03 78

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： 用户将 RDS MySQL 实例从 5.6 升级到 8.0 后，发现相同 SQL 的执行时间增长了十几倍。本文就该问题逐步展开排查，并最终定位根因。

一、背景

用户将 MySQL 实例从 5.6 升级到 8.0 后，发现相同 SQL 的执行时间增长了十几倍。用户的查询在简化后可以认为是一个对非索引字段做 order by 的全表扫描，理论上性能差异不应该如此巨大。带着这个疑问，本文将逐步展开问题排查的过程，定位性能退化的根因。

二、问题分析

2.1 初分析

我们首先基于问题实例进行分析，直接对比了问题 SQL 在不同版本之间的执行计划差异。

从 EXPALIN 的结果来看，两者相同，均为全表扫描 + filesort；

从 Optimizer Trace 结果来看，除了版本间 trace 信息输出格式的差异外，执行情况基本相同；

从 profiling 信息看，只能看出 8.0 版本在 executing 阶段的耗时大大增加了。

至此，执行计划差异的嫌疑排除。同时，通过对比机型、CPU 代际、相关参数配置等发现，不同版本实例的前述信息基本都是对齐的，可以初步认为是数据库内核中存在的问题。

进一步地，笔者使用perf工具对用户实例 SQL 执行过程中函数堆栈信息进行采集，发现热点函数集中在 row_sel_store_mysql_rec 上，这个函数主要的作用是将数据从 InnoDB 层转换到 Server 层。不论是 5.6 版本还是 8.0 版本，这个函数内部的实现逻辑都是相似的，在数据读取的过程中，这个函数也是绕不开的函数。

2.2 再分析

问题复现

为了能更深入地对这个问题进行分析，笔者对用户查询涉及的表结构和 SQL 进行简化，在开发、测试环境下复现了用户的问题：MySQL 8.0 执行耗时 1.14s，而在 MySQL 5.6 中执行耗时仅为 0.14s。表结构和 SQL 如下所示：

考虑到 RDS MySQL 并没有对涉及到的部分做大的改造，因此测试使用的是 MySQL 社区版本（8.0.38 和 5.6.51）。

# 建表
create table t1 (
  id int auto_increment primary key,
  col2 int,
  col3 char(255),
  col4 varchar(8192),
  paytime int unsigned DEFAULT NULL
) ENGINE=INNODB;

# 导入数据
DELIMITER //
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < 300000 DO
        INSERT INTO t1 VALUES (null, i, repeat('a', 255), repeat('a', 8192), FLOOR(RAND() * 300000));
        SET i = i + 1;
    END WHILE;
END //

CALL insert_data;

# 查询
SELECT * FROM t1 ORDER BY paytime LIMIT 1;

表的特点是：单行记录大，在特定的 row_format 下，溢出的数据会保存到额外的 page 中。

查询的特点是：查询多个字段、order by 非索引字段、只取 1 行数据。

Perf

在简化 SQL 执行的过程中，通过对比 perf 中的信息，我们可以发现热点函数 rew_sel_store_mysql_rec 和线上的扁鹊堆栈吻合。不论是 5.6 版本还是 8.0 版本，数据读取（ha_rnd_next）都占了 80%左右的 CPU，区别在于80 对于row_sel_store_mysql_rec 的处理效率很低，占比 63%，而 56 仅为 28%。

进一步展开后，可以看到 8.0 版本在此操作中做了大量的内存拷贝操作。

bpftrace

为什么 row_sel_store_mysql_rec 在 8.0 版本中执行得这么慢？带着这个问题，笔者使用 bpftrace 对相关的函数进行了分析。

# 监测8.0中row_sel_store_mysql_rec的执行情况
bpftrace -e '
  uprobe:/home/songhuaxiong.shx/huaxiong.shx/code/mysql-community//mysql-8.0.38-linux-glibc2.12-x86_64/bin/mysqld:_Z23row_sel_store_mysql_recPhP14row_prebuilt_tPKhPK8dtuple_tbPK12dict_index_tS9_PKmbPN3lob11undo_vers_tERP16mem_block_info_t {
    @start[tid] = nsecs;
  }

  uretprobe:/home/songhuaxiong.shx/huaxiong.shx/code/mysql-community//mysql-8.0.38-linux-glibc2.12-x86_64/bin/mysqld:_Z23row_sel_store_mysql_recPhP14row_prebuilt_tPKhPK8dtuple_tbPK12dict_index_tS9_PKmbPN3lob11undo_vers_tERP16mem_block_info_t /@start[tid]/ {
    @duration = hist(nsecs - @start[tid]);
    delete(@start[tid]);
    @count++;
  }

  END {
    printf("Function execution count: %d\n", @count);
    print(@duration);
  }
'

可以发现，8.0 中函数 row_sel_store_mysql_rec 的执行时间区间直接比 5.6 翻了一倍！进一步地，结合 Perf 热点来看：

btr_copy_externally_stored_field

这个函数非常可疑，这个函数是对溢出页做处理的函数，而用户的表和线下构造的表中确实页是包含了溢出页的。自然地，笔者对：

btr_copy_externally_stored_field

这个函数执行情况也进行了监测，关键点终于浮出水面。

8.0 中，btr_copy_externally_stored_field 执行了 300000 次，这和全表扫描的数量是匹配的；5.6 中btr_copy_externally_stored_field 函数仅执行了 1 次，这和 limit 1 的操作是匹配的。

row_sel_store_mysql_rec 执行次数相同而 btr_copy_externally_stored_field 执行次数不同，这说明在 8.0 中，所有的字段都会参与到 InnoDB 到 Server 的转换中，其中就包括了 col4 这个超长的、会溢出的字段。而在 5.6 中，col4 字段的转换只发生了 1 次。

至此，笔者得出一个猜测：对于本案例中的 limit 操作，80 版本在排序过程会完成所有字段的转换，最后再去取 limit 条数据；56 在排序的过程并不会对不相关的 field 做转换（构造的 SQL 中 order by 涉及的字段仅有 paytime），只在筛选完成后对最后的 limit 条的数据去做完整 field 转换（构造的 SQL 中最终需要返回所有字段）。

2.3 源码分析

实际上通过阅读 5.6 和 8.0 的源码，我们可以发现，row_sel_store_mysql_rec 的处理逻辑几乎是相同的，聚焦问题相关的逻辑，可以简单理解为：row_sel_store_mysql_rec 会根据上层传入的标记（read_set，一个用于标记字段使用情况的 bitmap），决定对那些字段做数据从 InnoDB 到 Server 的转换。对于溢出字段，会单独调用 btr_copy_externally_stored_field 进行处理。

不论是 filesort 过程还是最后 limit 数据的读取过程，都会走到 row_sel_store_mysql_rec 的逻辑中。

发生问题的根源在于：

5.6 版本的 filesort 实现中，在正式读取数据并进行排序前，重置了 read_set，只把排序涉及的 filed 注册到了 read_set 中；

在 8.0 中，并没有对 read_set 做特殊的处理。这直接影响了后续row_sel_store_mysql_rec 的行为。

在这个问题中，我们将这一过程简化为 3 个阶段：排序前、排序过程和排序完成。各阶段主要工作如下：

1.排序前：read_set 中保存了本次查询涉及到的所有字段，对于本例，read_set 对应的是所有字段（SELECT *）；

2.排序过程：MySQL 5.6 会做 read_set 的修改，在进入row_sel_store_mysql_rec 前会将 read_set 设置为 tmp_set（全 0），随后在 read_set 重新标记上本次需要访问的字段，对于本例，重新标记的 read_set 对应主键字段和 paytime 字段（order by 字段）。因此在 filesort -> row_sel_store_mysql_rec 的执行过程中，就不会再对未标记的溢出字段（本例中为 col4）做数据的转换；

3.排序完成：MySQL 5.6 重新将 read_set 设置为排序前的值，开始数据读取。此时由于 read_set 是全部字段，因此这里发生了唯一的一次对溢出字段的转换处理。

对于 80 来说，filesort 逻辑和前后的流程做了大的改动，设置 read_set 和重置 read_set 在 80 版本的 filesort 中已经不存在了。因此，不论是在 filesort 过程还是在最后的 read limit 数据过程，都会触发所有字段的数据转换，造成查询低效。

三、小结

8.0 排序过程中发生了对无关列的数据转换，导致了性能退化。用户的实例中包含了溢出列，由于溢出列的数据转换格外耗时，最终将这个性能退化的问题放大。

除了上述提到的问题，笔者使用 SEELCT * FROM t1 ORDER BY col4 LIMIT 1;这条 SQL 进行查询（涉及溢出列），发现了 filesort 还存在其他的热点问题。在这个查询下，5.6 的有效操作（read）的占比有 62%，而 8.0 中有效操作占比仅有 16%，非核心的操作占用了大量的 CPU 时间。

实际上，笔者也测试了 5.7 版本的 MySQL 并阅读了相关源码，该版本也不存在上述的性能问题。在和社区后续的沟通中，社区验证了该问题在 8.0 、8.4 和 9.0 中均存在，且该问题在 8.4 中的影响更为剧烈。

在实际的使用中，我们建议用户在查询中合理地去使用索引以提高执行效率，但同时需要明确的是，该问题在 MySQL 8.0 及后续版本中是客观存在的，有待进一步的优化。

来源 | 阿里云开发者公众号
作者 | 桦雄

MySQL 8.0：filesort 性能退化的问题分析

一、背景