MySQL BTREE 複合索引 ASC/DESC 混用導致 filesort 優化實戰

MySQL BTREE 複合索引 ASC/DESC 混用導致 filesort 優化實戰

問題背景

表 tbl_direct_pos_201506 含 190 萬行數據，主鍵定義為 PRIMARY KEY (`sys_date`,`trans_datetime`,`acq_ins_code`,`trace_num`)，所有列默認升序（ASC）存儲。一條分頁查詢 SQL 執行時間高達 2.478 秒，執行計劃顯示 Using filesort，嚴重影響性能。

SELECT ...
FROM tbl_direct_pos_201506 t
LEFT JOIN ...
WHERE t.sys_date BETWEEN '20150622' AND '20150628'
ORDER BY t.sys_date DESC, t.trans_datetime DESC, t.acq_ins_code, t.trace_num
LIMIT 0, 20;

執行計劃分析

雖然 WHERE 條件與 ORDER BY 列均包含在主鍵中，且順序符合最左前綴原則，但 MySQL 仍選擇 filesort。根因在於：

MySQL 官方文檔明確指出：索引列定義可跟隨 ASC/DESC 關鍵字，但該語法僅被解析並忽略，索引列始終以升序存儲（MySQL 8.0 前版本）。

由於主鍵各列均為 ASC 存儲，而查詢中前兩列使用 DESC，導致索引順序與排序需求不一致，MySQL 無法利用索引直接返回有序結果，被迫在內存中進行文件排序。

驗證與優化過程

1. 測試全 ASC 排序：將 ORDER BY 改為全部 ASC，執行時間驟降至 0.023 秒，執行計劃中 Using filesort 消失，確認索引生效。
2. 業務確認：溝通發現 sys_date 和 trans_datetime 必須降序，其餘兩列順序不敏感。
3. 最終優化：將四個 ORDER BY 列統一改為 DESC，使排序方向與索引存儲方向完全一致（均為降序邏輯），執行時間 0.029 秒，完美解決。

ORDER BY t.sys_date DESC, t.trans_datetime DESC, t.acq_ins_code DESC, t.trace_num DESC

核心經驗總結

• MySQL 5.7 及之前版本：複合索引所有列物理存儲均為升序，ORDER BY 中若混用 ASC/DESC，將導致 filesort。
• 優化原則：確保 ORDER BY 列的排序方向與索引定義順序完全一致（全 ASC 或全 DESC），或調整索引列順序以匹配查詢模式。
• MySQL 8.0+：支持真正的降序索引（INDEX idx (a DESC, b ASC)），可從根本上解決此類問題，建議升級版本並重建索引。

本案例再次證明：深入理解存儲引擎索引實現機制，是 SQL 性能調優的關鍵前提。