多表關聯查詢帶 ORDER BY ... LIMIT N 時，MySQL 優化器傾向先 Join 再排序，導致中間結果集膨脹、filesort 開銷巨大。本文通過實測對比演示「先排序取 Top-N 再 Join」的重寫模式，實現從 3 秒降至 0.16 秒的 20 倍效能飛躍。
-- 表結構
CREATE TABLE t_team (id INT PK, tname VARCHAR(100));
CREATE TABLE t_people (id INT PK, pname VARCHAR(100), team_id INT, FK(team_id) REFERENCES t_team(id));
-- 數據量：team 1,000 行，people 100,000 行
-- 查詢：按人名排序，取前 10 人及其所屬團隊名SELECT *
FROM t_people p
LEFT JOIN t_team t ON p.team_id = t.id
ORDER BY p.pname
LIMIT 10;EXPLAIN：type=ALL（people 全掃），Extra=Using temporary; Using filesort。先 Join 產生 10 萬行中間表，再排序，再取 10 行。
SELECT * FROM t_people p LEFT JOIN t_team t ON p.team_id = t.id LIMIT 10;無排序，直接取前 10 行 Join，極快。
SELECT * FROM t_people p ORDER BY p.pname LIMIT 10;單表索引排序（pname 有索引），極快。
SELECT *
FROM (
SELECT * FROM t_people p ORDER BY p.pname LIMIT 10
) AS p
LEFT JOIN t_team t ON p.team_id = t.id
LIMIT 10; -- 外層 LIMIT 保障語義執行邏輯：
SELECT * FROM t_people ORDER BY pname LIMIT 10 → 走 pname 索引，僅讀 10 行，生成 10 行臨時表。LEFT JOIN t_team → 10 次主鍵查找，極快。EXPLAIN 顯示：衍生表 select_type=DERIVED、type=index、rows=10；外層 type=eq_ref。
「我在實際開發中就碰到了這樣的問題，使用寫法 ① 耗時 80 多秒，但使用寫法 ④ 只需 1 秒以內。」
表結構越複雜（多表 Join、寬字段、大中間集），優化收益越明顯。
| 條件 | 說明 |
|---|---|
| ORDER BY 列屬於驅動表 | 示例中 p.pname 屬於 t_people（驅動表），可先排序。若按被驅動表列排序（ORDER BY t.tname），需調整驅動表或用其他策略。 |
| 驅動表有排序索引 | pname 有索引，否則衍生表仍需 filesort，收益遞減但通常仍優於全 Join 排序。 |
| LIMIT 較小 | Top-N 查詢（N < 1000）效果最佳。大 LIMIT 接近全表時，優化器可能自動選擇更好計劃。 |
| 無 GROUP BY / DISTINCT 干擾 | 衍生表內若含聚合/去重，需驗證語義等價性。 |
WITH top_people AS (
SELECT * FROM t_people ORDER BY pname LIMIT 10
)
SELECT tp.*, t.tname
FROM top_people tp
LEFT JOIN t_team t ON tp.team_id = t.id;CTE（Common Table Expression）語義等同衍生表，可讀性更強，8.0+ 推薦使用。
連接查詢 + ORDER BY + LIMIT 性能殺手鐵律：
1. 先在驅動表上排序取 Top-N（衍生表/CTE）。
2. 再用小結果集 Join 其它表。
3. 驗證 EXPLAIN：衍生表 rows=N，外層 type=eq_ref/ref。
這是「將排序下推到單表」的經典模式，適用于 MySQL 5.6+ 所有版本，收益與中間結果集大小正相關。
