線上緊急問題之Using filesort 能優(yōu)化嗎，怎么優(yōu)化？
上一篇文章中，提到了如何分析 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ù)庫(kù)層面的錯(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)行了簡(jiǎn)單的脫敏，其中最關(guān)鍵的一句是：
Sort aborted: Query execution was interrupted這是數(shù)據(jù)庫(kù)查詢執(zhí)行過程中的錯(cuò)誤信息，通常在數(shù)據(jù)庫(kù)系統(tǒng)中會(huì)出現(xiàn)。該錯(cuò)誤消息表示數(shù)據(jù)庫(kù)查詢中的排序操作被中斷或終止了。
問題排查
通常情況下，導(dǎo)致這個(gè)問題發(fā)生的原因有幾個(gè)：
- 慢 SQL 導(dǎo)致查詢超時(shí)，此時(shí)為了避免數(shù)據(jù)庫(kù)連接長(zhǎng)時(shí)間被占用而中斷查詢。
- 查詢被手動(dòng)終止，數(shù)據(jù)庫(kù)管理員手動(dòng)中止正在執(zhí)行的查詢操作也會(huì)導(dǎo)致這個(gè)異常。
- 資源不足，查詢排序操作可能需要大量的計(jì)算和內(nèi)存資源。如果數(shù)據(jù)庫(kù)服務(wù)器的資源不足以執(zhí)行排序操作，查詢可能會(huì)被中斷。這種情況可能發(fā)生在高負(fù)載或資源不足的環(huán)境中。
總的來說，以上三個(gè)原因是導(dǎo)致問題發(fā)生的主要原因。接下來，我們將分析導(dǎo)致查詢失敗的 SQL 語(yǔ)句。這個(gè)語(yǔ)句在上述的錯(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 語(yǔ)句是基于 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 語(yǔ)句的分析，我們可以看出它確實(shí)使用了索引，命中了 idx_subject_product 這個(gè)索引，其中包含了 subject_id 和 product_type_enum 字段。
另外，由于這條 SQL 語(yǔ)句包含了 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)警，問題得到了解決。
