上一篇文章中，提到了如何分析 SQL 的執(zhí)行計(jì)劃，從而更好的應(yīng)對(duì) SQL 性能過低等問題。但是我們也常遇到Extra字段是Using filesort的時(shí)候，上篇文章有描述：

圖片

在 InnoDB 存儲(chǔ)引擎中，當(dāng)執(zhí)行計(jì)劃中出現(xiàn)"Using filesort"時(shí)，表示 MySQL 需要對(duì)結(jié)果集進(jìn)行外部排序，以滿足查詢中的 ORDER BY 條件。

比如，下面這個(gè)執(zhí)行計(jì)劃中的"Extra"部分出現(xiàn)了"Using filesort"，表明需要進(jìn)行文件排序。

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

在下面這篇文章中，我們已經(jīng)介紹了 ORDER BY 的實(shí)現(xiàn)原理。通常情況下，"Using filesort"發(fā)生在無法直接利用索引完成排序的情況下，需要額外的排序步驟。這可能會(huì)導(dǎo)致查詢性能下降，特別是在處理大量數(shù)據(jù)時(shí)。優(yōu)化"Using filesort"的目標(biāo)是減少排序所需的資源和時(shí)間，從而提高查詢效率。

因此，當(dāng)執(zhí)行計(jì)劃中出現(xiàn)"Using filesort"時(shí)，這是我們可以優(yōu)化的一個(gè)方向。（但是，并不是說一定要優(yōu)化！要看是否有必要以及收益是否夠大）。

針對(duì)"Using filesort"的優(yōu)化，可以有以下幾個(gè)方向：

優(yōu)化方向

盡量使用索引排序：

索引是天然有序的，所以當(dāng)我們?cè)谑褂?order by 的時(shí)候，如果能借助索引，那么效率一定是最高的。

• 那么我們就可以確保 ORDER BY 子句中的字段是索引的一部分。
• 并且如果可能，使 ORDER BY 中的列順序與索引中的列順序一致（order by a,b,c ， idx_a_b_c(a,b,c）)。
• 并且考慮使用復(fù)合索引。如果 ORDER BY 子句涉及多個(gè)列，創(chuàng)建一個(gè)包含這些列的復(fù)合索引可能會(huì)有助于消除 Using filesort。

優(yōu)化 MySQL 配置：

我們還可以調(diào)整 sort_buffer_size 參數(shù)。這個(gè)參數(shù)決定了排序操作可以使用的內(nèi)存量。增加其值可以提高處理大型排序操作的能力（但設(shè)置過大可能會(huì)消耗過多內(nèi)存資源，影響系統(tǒng)性能）。

根據(jù) sort_buffer_size 的大小不同，會(huì)在不同的地方進(jìn)行排序操作：

• 如果要排序的數(shù)據(jù)量小于 sort_buffer_size，那么排序就在內(nèi)存中完成。
• 如果排序數(shù)據(jù)量大于 sort_buffer_size，則需要利用磁盤臨時(shí)文件輔助排序。

在內(nèi)存中排序肯定會(huì)更快一點(diǎn)的。

實(shí)戰(zhàn)優(yōu)化之 Sort aborted 問題排查過程

問題發(fā)現(xiàn)

我們的定時(shí)任務(wù)是掃描表，但最近經(jīng)常收到定時(shí)任務(wù)掃描處理失敗的警報(bào)。登錄到服務(wù)器后，發(fā)現(xiàn)了數(shù)據(jù)庫層面的錯(cuò)誤信息：

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]

以上日志已經(jīng)進(jìn)行了簡單的脫敏，其中最關(guān)鍵的一句是：

Sort aborted: Query execution was interrupted

這是數(shù)據(jù)庫查詢執(zhí)行過程中的錯(cuò)誤信息，通常在數(shù)據(jù)庫系統(tǒng)中會(huì)出現(xiàn)。該錯(cuò)誤消息表示數(shù)據(jù)庫查詢中的排序操作被中斷或終止了。

問題排查

通常情況下，導(dǎo)致這個(gè)問題發(fā)生的原因有幾個(gè)：

1. 慢 SQL 導(dǎo)致查詢超時(shí)，此時(shí)為了避免數(shù)據(jù)庫連接長時(shí)間被占用而中斷查詢。
2. 查詢被手動(dòng)終止，數(shù)據(jù)庫管理員手動(dòng)中止正在執(zhí)行的查詢操作也會(huì)導(dǎo)致這個(gè)異常。
3. 資源不足，查詢排序操作可能需要大量的計(jì)算和內(nèi)存資源。如果數(shù)據(jù)庫服務(wù)器的資源不足以執(zhí)行排序操作，查詢可能會(huì)被中斷。這種情況可能發(fā)生在高負(fù)載或資源不足的環(huán)境中。

總的來說，以上三個(gè)原因是導(dǎo)致問題發(fā)生的主要原因。接下來，我們將分析導(dǎo)致查詢失敗的 SQL 語句。這個(gè)語句在上述的錯(cuò)誤日志中已經(jīng)打印出來了，我對(duì)其中一些無關(guān)緊要的內(nèi)容進(jìn)行了隱藏，大致 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
  ?, ?

大致來說，這個(gè) SQL 語句是基于 product_type_enum、risk_case_status_enum 和 subject_id 進(jìn)行條件查詢，并且基于 subject_id_enum 和 subject_id 兩個(gè)字段進(jìn)行了分組。

看了一下這條 SQL 的執(zhí)行計(jì)劃：

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

通過這個(gè) SQL 語句的分析，我們可以看出它確實(shí)使用了索引，命中了 idx_subject_product 這個(gè)索引，其中包含了 subject_id 和 product_type_enum 字段。

另外，由于這條 SQL 語句包含了 GROUP BY 操作，因此需要進(jìn)行排序，但并沒有使用索引排序，而是基于 filesort 進(jìn)行的。

此時(shí)可以利用我之前的文章（首先想到的優(yōu)化方式就是提升這個(gè) sort 的性能。

問題解決

在這種情況下，考慮到需要兼顧 WHERE 條件的查詢性能以及排序操作的性能，可以創(chuàng)建一個(gè)包含 risk_case_status_enum、subject_id_enum 和 subject_id 三個(gè)字段的聯(lián)合索引，并按照 risk_case_status_enum、subject_id_enum 和 subject_id 的順序排列。

這樣的索引設(shè)計(jì)可以讓 WHERE 條件的判斷走索引，同時(shí)也能讓排序操作利用索引。索引建立后，執(zhí)行計(jì)劃可能會(huì)如下所示：

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

這樣的話，執(zhí)行計(jì)劃中的 Extra 字段就只會(huì)顯示"Using index condition"，表示排序操作基于索引完成了。

而且在發(fā)布之后，不再出現(xiàn)報(bào)警，問題得到了解決。