MySQL 索引實戰案例：聯合索引優化 ORDER BY LIMIT 與 IN/OR 改 UNION ALL

場景背景

• 表 article 20 萬行，article_category 已建單列索引。
• 查詢：SELECT * FROM article WHERE article_category=11 ORDER BY article_id DESC LIMIT 5 耗時 5 秒。

優化一：建立聯合索引消除 filesort

CREATE INDEX idx_cat_id ON article (article_category, article_id);

原理：WHERE category=常數 + ORDER BY id 符合最左前綴，索引已按 id 有序，直接倒序掃描前 5 行，耗時 0.0027 秒。

優化二：IN / OR 導致全表掃描

SELECT * FROM article WHERE article_category IN (2,3) ORDER BY article_id DESC LIMIT 5;  -- 11 秒
SELECT * FROM article WHERE article_category=2 OR article_category=3 ORDER BY article_id DESC LIMIT 5; -- 11 秒

原因：MySQL 對 IN/OR 的範圍優化較弱，退化為全表掃描 + filesort。

優化三：UNION ALL 替代 IN/OR

(SELECT * FROM article WHERE article_category=2 ORDER BY article_id DESC LIMIT 5)
UNION ALL
(SELECT * FROM article WHERE article_category=3 ORDER BY article_id DESC LIMIT 5)
ORDER BY article_id DESC LIMIT 5;

耗時 0.0261 秒。每個子查詢命中聯合索引 (category, id)，再合併去重（UNION ALL 不去重，性能更高）。

UNION vs UNION ALL

• UNION：合併後排序去重，開銷大。
• UNION ALL：直接拼接結果，無去重，速度快。確定無重複數據時首選。

UNION ALL 中 ORDER BY 陷阱

子查詢不能直接帶 ORDER BY，需包裝子查詢：

SELECT * FROM (SELECT * FROM t WHERE c=1 ORDER BY id DESC LIMIT 5) AS a
UNION ALL
SELECT * FROM (SELECT * FROM t WHERE c=2 ORDER BY id DESC LIMIT 5) AS b
ORDER BY id DESC LIMIT 5;

總結

1. 聯合索引 (category, id) 解決 WHERE + ORDER BY + LIMIT 深分頁問題。
2. IN/OR 易導致掃表，改用 UNION ALL 分而治之。
3. UNION ALL 避免去重開銷，子查詢用派生表包裝 ORDER BY LIMIT。