MySQL ORDER BY 優化深度解析：索引排序、Filesort 避坑與深分頁改寫實戰

MySQL ORDER BY 優化深度解析：索引排序、Filesort 避坑與深分頁改寫實戰

官方文檔：可用索引排序的場景

SELECT * FROM t1 ORDER BY key_part1, key_part2...;
SELECT * FROM t1 WHERE key_part1 = const ORDER BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1 > const ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1 < const ORDER BY key_part1 DESC;
SELECT * FROM t1 WHERE key_part1 = const1 AND key_part2 > const2 ORDER BY key_part2;

無法使用索引排序的反例

• 兩個無關聯索引排序：ORDER BY key1, key2
• 非連續索引鍵部分：WHERE key2=const ORDER BY key_part2
• 混用 ASC/DESC：ORDER BY key_part1 DESC, key_part2 ASC
• 搜索索引與排序索引不同：WHERE key2=const ORDER BY key1

核心鐵律：一條 SQL 不能同時使用兩個索引，需建立聯合索引。

優化策略

1. 增大 sort_buffer_size、read_rnd_buffer_size。
2. 設計索引順序匹配 ORDER BY 列，消除 Filesort。

實戰案例：二級索引導致的排序抉擇

SELECT * FROM a ORDER BY subject_code;        -- 不用索引
SELECT id FROM a ORDER BY subject_code;       -- 用索引
SELECT subject_code FROM a ORDER BY subject_code; -- 用索引
SELECT * FROM a WHERE subject_code = XX ORDER BY subject_code; -- 用索引

原因解析

InnoDB 二級索引存 (subject_code, PK)。
SELECT * 若走二級索引排序，需按索引順序回主鍵查詢，隨機 IO 成本高於 Filesort，優化器選擇全表掃描 + Filesort。
SELECT id 或 SELECT subject_code 只需掃描二級索引（覆蓋索引），無回表，極快。

深分頁優化改寫

-- 原查詢：深分頁掃描大量行再丟棄
SELECT * FROM payment ORDER BY rental_id LIMIT 100,10;

-- 方案一：子查詢覆蓋索引定位主鍵，再回表
SELECT * FROM `XXX` AS x
INNER JOIN (SELECT id FROM `XXX` ORDER BY id DESC LIMIT M,N) AS t
USING (id);

-- 方案二：BETWEEN ... AND（需連續 ID，適用場景窄）

-- 方案三：業務協作，前端傳 last_page_record，下一頁直接範圍查找

子查詢利用覆蓋索引無需回表即可定位主鍵，再 JOIN 回表取數據，大幅降低 IO。