MySQL 查詢優化實戰：OR、IN 與 UNION ALL 效率對比測試分析

核心結論先行

不要迷信「UNION ALL 永遠優於 OR/IN」。實際性能取決於：是否命中索引、掃描行數、執行計劃類型。三者在合理使用索引時性能接近；濫用 UNION ALL 反而可能導致多次全表掃描，效果適得其反。

實驗環境與數據準備

• 表結構：BT (ID PK, VName VARCHAR(20))，InnoDB。
• 數據量：1000 萬行（存儲過程批量插入，每 2000 行提交）。
• 工具：EMS SQL Manager 監控毫秒級耗時。

場景一：索引列（主鍵 ID）查詢

-- 查詢 ID IN (98, 85220, 9888589)
SELECT * FROM BT WHERE ID IN (...);
SELECT * FROM BT WHERE ID = 98 UNION ALL SELECT * FROM BT WHERE ID = 85220 ...

• 耗時：三者均 0.00s (約 93ms)，差異可忽略。
• 執行計劃：UNION ALL 顯示 type=ref（非唯一索引掃描），OR/IN 顯示 type=range（索引範圍掃描）。ref 理論優於 range，但單次範圍掃描 vs 多次 ref 掃描，實際耗時持平。

場景二：非索引列（VName）查詢

-- 查詢 VName IN ('M98', 'M85220', 'M9888589')
SELECT * FROM BT WHERE VName IN (...);
SELECT * FROM BT WHERE VName = 'M98' UNION ALL ...

• 耗時：UNION ALL 耗時約為 OR/IN 的 3 倍。
• 原因：OR/IN 只需 一次全表掃描（rows=966萬），UNION ALL 執行 三次全表掃描（rows=966萬 × 3）。
• 執行計劃 type=ALL 一致，但掃描行數決定性能。

場景三：混合查詢（索引列 + 非索引列）

SELECT * FROM BT WHERE VName = 'M98' OR ID = 9888589;
SELECT * FROM BT WHERE VName = 'M98' UNION ALL SELECT * FROM BT WHERE ID = 9888589;

• 推薦使用 OR/IN。UNION ALL 雖能利用索引快速定位 ID，但 VName 仍需全表掃描，且 UNION ALL 增加了結果合併開銷。
• 若索引列查詢極慢（如大範圍篩選），則 OR/IN 更優。

總結與最佳實踐建議

1. 索引列有效：優先考慮 UNION ALL（避免複雜條件導致優化器放棄索引），但需確認執行計劃為 ref/range。
2. 純非索引列：堅決使用 OR/IN，單次全表掃描優於多次掃描。
3. 混合條件：傾向 OR/IN，除非索引列極其高效且非索引列選擇性極高。
4. 複雜查詢/多表 Join：需綜合評估連接方式、數據分布、索引覆蓋，結合單表策略靈活選擇。