開發中常遇到「IN、OR、BETWEEN 哪個查詢效能最好？」的問題。本文基於 MySQL 5.6+ 的 optimizer_trace 機制，從執行計劃、成本模型、底層存取路徑三個維度深度剖析，給出實戰優化建議。

測試環境與資料準備

-- 建立測試表
CREATE TABLE `tin` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 寫入 5000 筆測試資料
DELIMITER //
CREATE PROCEDURE init_tin(cnt INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  REPEAT
    INSERT INTO tin(c2) VALUES(REPEAT('a', 100));
    SET i := i + 1;
  UNTIL i > cnt END REPEAT;
END//
DELIMITER ;
CALL init_tin(5000);

三種等價查詢語句

-- 1. BETWEEN 範圍查詢（單一 range）
SELECT * FROM tin WHERE c1 >= 100 AND c1 <= 104;

-- 2. IN 列表查詢（5 個離散值）
SELECT * FROM tin WHERE c1 IN (100, 101, 102, 103, 104);

-- 3. OR 多條件查詢
SELECT * FROM tin WHERE c1 = 100 OR c1 = 101 OR c1 = 102 OR c1 = 103 OR c1 = 104;

EXPLAIN 執行計劃對比

三條語句的 EXPLAIN 輸出完全一致：

id: 1
select_type: SIMPLE
table: tin
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 5
filtered: 100.00
Extra: Using where

僅看 EXPLAIN 無法區分效能差異，需深入 optimizer_trace。

Optimizer Trace 成本模型揭秘

查詢方式	Range 個數	Rows for Plan	Cost for Plan	Chosen
BETWEEN (c1 >= 100 AND c1 <= 104)	1	5	2.0188	true
IN (100,101,102,103,104)	5	5	6.0188	true
OR (c1=100 OR ... OR c1=104)	5	5	6.0188	true

深度原理分析

1. I/O 成本差異

MySQL 成本模型中，I/O 成本與 n_ranges（範圍個數）正相關。
BETWEEN 僅 1 個 range → 1 次 read_first + 5 次 read_next。
IN/OR 被優化為 5 個單點 range → 5 次 read_first + 5 次 read_next，調用次數翻倍。

2. CPU 過濾成本差異

• BETWEEN：僅需比對上下邊界，O(1)。
• IN：MySQL 將常量存入有序數組，採用二分查找，O(log n)。
• OR：逐條件線性掃描，O(n)，最慢。

3. 實際 I/O 操作一致

三者掃描的物理頁面完全相同（主鍵 100~104），差異僅在「定位次數」與「CPU 判斷邏輯」。

實戰優化建議

1. 優先使用 BETWEEN / >= <= 連續範圍：單一 range，I/O 與 CPU 成本最低。
2. IN 列表優於 OR：IN 經過優化器轉換為有序數組二分查找，OR 退化為線性判斷。
3. IN 列表值過多（> 1000）時：考慮改用臨時表 JOIN 或 EXISTS 子查詢，避免優化器耗時過長。
4. 確保欄位有索引：無索引時三者皆為全表掃描，差異可忽略。
5. 離散值較多但可合併為區間：應用層合併為 BETWEEN 或 UNION ALL 多個區間。

結論

BETWEEN > IN > OR（在索引列、離散值可合併為連續區間時）。
實際專案中，若值本身不連續，IN 仍是可讀性與效能的平衡最佳選；避免長 OR 鏈。