✅线上紧急问题之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 |
+----+-------+----------------------+---------------------+----------------------------------------------------+在下面这篇文章中，我们已经介绍了 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]以上日志已经进行了简单的脱敏，其中最关键的一句是：
Sort aborted: Query execution was interrupted这是数据库查询执行过程中的错误信息，通常在数据库系统中会出现。该错误消息表示数据库查询中的排序操作被中断或终止了。
问题排查
通常情况下，导致这个问题发生的原因有几个：
- 慢 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
?, ?大致来说，这个 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 |
+----+-------+------------------------------+---------------------+----------------------------------------------------+通过这个 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; |
+----+-------+-------------------------------------------------+---------------------+----------------------------------------------------+这样的话，执行计划中的 Extra 字段就只会显示"Using index condition"，表示排序操作基于索引完成了。
而且在发布之后，不再出现报警，问题得到了解决。
如有问题，欢迎微信搜索【码上遇见你】。
我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
