线上紧急问题之Using filesort 能优化吗，怎么优化？

上一篇文章中，线上提到了如何分析 SQL 的紧急执行计划，从而更好的问题应对 SQL 性能过低等问题。但是化优化我们也常遇到Extra字段是Using filesort的时候，上篇文章有描述：

图片

在 InnoDB 存储引擎中，线上当执行计划中出现"Using filesort"时，紧急表示 MySQL 需要对结果集进行外部排序，问题以满足查询中的化优化 ORDER BY 条件。

比如，线上下面这个执行计划中的紧急"Extra"部分出现了"Using filesort"，表明需要进行文件排序。问题

复制+----+-------+----------------------+---------------------+----------------------------------------------------+ | id | type | possible_keys | key | Extra | +----+-------+----------------------+---------------------+----------------------------------------------------+ | 1 | range | idx_subject_product | idx_subject_product | Using index condition; Using where; Using filesort | +----+-------+----------------------+---------------------+----------------------------------------------------+1.2.3.4.5.

在下面这篇文章中，化优化我们已经介绍了 ORDER BY 的线上实现原理。通常情况下，紧急"Using filesort"发生在无法直接利用索引完成排序的问题情况下，需要额外的排序步骤。这可能会导致查询性能下降，特别是在处理大量数据时。优化"Using filesort"的目标是减少排序所需的资源和时间，从而提高查询效率。企商汇

因此，当执行计划中出现"Using filesort"时，这是我们可以优化的一个方向。（但是，并不是说一定要优化！要看是否有必要以及收益是否够大）。

针对"Using filesort"的优化，可以有以下几个方向：

优化方向

尽量使用索引排序：

索引是天然有序的，所以当我们在使用 order by 的时候，如果能借助索引，那么效率一定是最高的。

那么我们就可以确保 ORDER BY 子句中的字段是索引的一部分。并且如果可能，使 ORDER BY 中的列顺序与索引中的列顺序一致（order by a,b,c ， idx_a_b_c(a,b,c）)。并且考虑使用复合索引。亿华云计算如果 ORDER BY 子句涉及多个列，创建一个包含这些列的复合索引可能会有助于消除 Using filesort。

优化 MySQL 配置：

我们还可以调整 sort_buffer_size 参数。这个参数决定了排序操作可以使用的内存量。增加其值可以提高处理大型排序操作的能力（但设置过大可能会消耗过多内存资源，影响系统性能）。

根据 sort_buffer_size 的大小不同，会在不同的地方进行排序操作：

如果要排序的数据量小于 sort_buffer_size，那么排序就在内存中完成。如果排序数据量大于 sort_buffer_size，则需要利用磁盘临时文件辅助排序。

在内存中排序肯定会更快一点的。

实战优化之 Sort aborted 问题排查过程

问题发现

我们的定时任务是扫描表，但最近经常收到定时任务扫描处理失败的警报。登录到服务器后，发现了数据库层面的错误信息：

复制Caused by: com.taobao.tddl.common.exception.TddlRuntimeException: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP FIN_RISK_XXX_GROUP ATOM cn-zhangjiakou_i-xxxxx_fin_risk_xxx_3028: Sort aborted: Query execution was interrupted More: [http://xxx.alibaba-inc.com/ faq/faqByFaqCode.html?faqCode=XXX-4614]1.2.3.4.5.

以上日志已经进行了简单的脱敏，其中最关键的一句是：

复制Sort aborted: Query execution was interrupted1.

这是数据库查询执行过程中的错误信息，香港云服务器通常在数据库系统中会出现。该错误消息表示数据库查询中的排序操作被中断或终止了。

问题排查

通常情况下，导致这个问题发生的原因有几个：

慢 SQL 导致查询超时，此时为了避免数据库连接长时间被占用而中断查询。查询被手动终止，数据库管理员手动中止正在执行的查询操作也会导致这个异常。资源不足，查询排序操作可能需要大量的计算和内存资源。如果数据库服务器的资源不足以执行排序操作，查询可能会被中断。这种情况可能发生在高负载或资源不足的环境中。

总的来说，以上三个原因是导致问题发生的主要原因。接下来，我们将分析导致查询失败的 SQL 语句。这个语句在上述的错误日志中已经打印出来了，我对其中一些无关紧要的内容进行了隐藏，大致 SQL 如下：

复制### The error occurred while setting parameters### SQL: select business_type_enum, product_type_enum, subject_id, subject_id_enum, GROUP_CONCAT(distinct (number) SEPARATOR ,) as risk_case_numbers, GROUP_CONCAT(distinct (risk_level_enum) SEPARATOR ,) as risk_level_enums, from fraud_risk_case WHERE product_type_enum = ? and risk_case_status_enum = DRAFT and subject_id like "23%" group by subject_id_enum, subject_id limit ?, ?1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.

大致来说，这个 SQL 语句是基于 product_type_enum、risk_case_status_enum 和 subject_id 进行条件查询，并且基于 subject_id_enum 和 subject_id 两个字段进行了分组。

看了一下这条 SQL 的执行计划：

复制+----+-------+------------------------------+---------------------+----------------------------------------------------+ | id | type | possible_keys | key | Extra | +----+-------+------------------------------+---------------------+----------------------------------------------------+ | 1 | range | idx_subject_product | idx_subject_product | Using index condition; Using where; Using filesort | +----+-------+------------------------------+---------------------+----------------------------------------------------+1.2.3.4.5.

通过这个 SQL 语句的分析，我们可以看出它确实使用了索引，命中了 idx_subject_product 这个索引，其中包含了 subject_id 和 product_type_enum 字段。

另外，由于这条 SQL 语句包含了 GROUP BY 操作，因此需要进行排序，但并没有使用索引排序，而是基于 filesort 进行的。

此时可以利用我之前的文章（首先想到的优化方式就是提升这个 sort 的性能。

问题解决

在这种情况下，考虑到需要兼顾 WHERE 条件的查询性能以及排序操作的性能，可以创建一个包含 risk_case_status_enum、subject_id_enum 和 subject_id 三个字段的联合索引，并按照 risk_case_status_enum、subject_id_enum 和 subject_id 的顺序排列。

这样的索引设计可以让 WHERE 条件的判断走索引，同时也能让排序操作利用索引。索引建立后，执行计划可能会如下所示：

复制+----+-------+-------------------------------------------------+---------------------+----------------------------------------------------+ | id | type | possible_keys | key | Extra | +----+-------+-------------------------------------------------+---------------------+----------------------------------------------------+ | 1 | range | idx_subject_product ,idx_status_subject | idx_status_subject | Using index condition; | +----+-------+-------------------------------------------------+---------------------+----------------------------------------------------+1.2.3.4.5.

这样的话，执行计划中的 Extra 字段就只会显示"Using index condition"，表示排序操作基于索引完成了。

而且在发布之后，不再出现报警，问题得到了解决。