線上緊急問題之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"，表示排序操作基于索引完成了。
而且在發布之后，不再出現報警，問題得到了解決。
