您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 索引優化實戰：從 5 秒到 0.0027 秒的查詢加速之路

編輯：MySQL 綜合教學

MySQL 索引優化實戰：從 5 秒到 0.0027 秒的查詢加速之路

真實案例：文章庫 20 萬筆資料，單表查詢 5 秒優化至 2.7 毫秒，再解決 IN/OR 導致的 11 秒慢查詢。掌握複合索引、最左前綴、UNION ALL 重寫等核心技巧。

場景與問題

-- 表結構
category: 10 筆分類
article: 20 萬筆文章，article_category 對應 category_id，已建單列索引
DB 大小：1.3GB

問題 1：基礎查詢慢

SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5;
-- 執行時間：~5 秒

原因：單列索引 article_category 可快速定位分類，但 ORDER BY article_id DESC 需額外排序（filesort），掃描大量行。

解決：複合索引消除排序

CREATE INDEX idx_u ON article (article_category, article_id);
-- 再次執行：0.0027 秒（提升 1800 倍）

原理：複合索引 (article_category, article_id) 滿足 最左前綴，WHERE 使用第 1 列，ORDER BY 使用第 2 列，索引天然有序，直接回表取前 5 行，零排序開銷。

問題 2：IN/OR 導致全表掃描

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

SELECT * FROM article WHERE article_category=2 OR article_category=3 ORDER BY article_id DESC LIMIT 5;
-- 11.0777 秒

原因：MySQL 5.6 優化器對 IN/OR 的範圍查詢無法有效利用複合索引排序，退化為全表掃描 + filesort。

解決：UNION ALL 分拆查詢

(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 秒（提升 430 倍）

關鍵點：

每個子查詢獨立使用複合索引 (article_category, article_id)，無 filesort
UNION ALL 不去重，效能遠優於 UNION
外層 ORDER BY ... LIMIT 5 僅合併 10 行資料，開銷極小

UNION vs UNION ALL 深度對比

特性	UNION	UNION ALL
去重	是（排序+唯一化）	否（直接合併）
效能	較慢，可能用臨時表/磁盤	極快，流式合併
適用場景	結果集可能重複	確定無重複（如分區表、不同條件）

實務上 90%+ 場景適用 UNION ALL（如歷史表+當前表、不同分類查詢）。

UNION ALL + ORDER BY 正確寫法

❌ 錯誤：子查詢各自 ORDER BY，外層再 ORDER BY（MySQL 不保證子查詢順序）

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

✅ 正確：包裝子查詢為衍生表

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