優化器核心目標

「盡可能使用索引，並用最嚴格的索引消除最多行」 — 最終目的是**快速定位匹配行**，而非掃描全表。

EXPLAIN 實戰讀法 (核心欄位)

欄位	關鍵含義	優化方向
id	SELECT 識別碼，同 id 從上往下執行	關注衍生表/子查詢順序
select_type	SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION	DERIVED (衍生表) 常需優化
table	存取的表 (含衍生表 )	確認掃描順序
type	system > const > eq_ref > ref > range > index > ALL	**避免 ALL (全表掃描)**，力求 ref/range 以上
possible_keys	理論可用索引	為空則無索引可用
key	**實際使用索引**	NULL = 未用索引
key_len	使用索引長度 (bytes)	判斷複合索引用了幾列
ref	與索引比較的欄位/常數	const/func/欄位名
rows	**預估掃描行數**	越小越好，配合 filtered 看實際過濾率
filtered	按條件過濾後剩餘比例 %	低則條件選擇性差
Extra	Using index/where/temporary/filesort	Using index (覆蓋索引) 佳；Using temporary/filesort 惡

索引選擇決策：為什麼優化器有時「不聽話」？

SELECT col3 FROM mytable
WHERE col1 = 'val1' AND col2 = 'val2';
-- 假設 col1 索引選擇性 900 行，col2 索引選擇性 300 行，交集 30 行
-- 優化器會先用 col2 索引 (掃 300 行) 再回表檢查 col1，而非先掃 900 行

**原則**：優化器估算各索引 rows × 過濾成本，選總成本最低者。

寫出「優化器友好」SQL 的鐵律

1. 比較同類型欄位

-- 好：INT 比 INT
WHERE int_col = 123
-- 差：VARCHAR 比 INT (隱式轉換導致索引失效)
WHERE varchar_col = 123  -- 應改 WHERE varchar_col = '123'

2. 讓索引欄位「裸露」在比較式左側

❌ 索引失效	✅ 索引生效	原因
WHERE mycol * 2 < 4	WHERE mycol < 4/2	函數/運算在索引列上
WHERE YEAR(date_col) = 2024	WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01'	函數包裹索引列
WHERE LEFT(name,3) = 'abc'	WHERE name LIKE 'abc%'	前綴匹配可用索引
WHERE id + 0 = 100	WHERE id = 100	隱式轉換

3. 合理使用複合索引 (最左前綴原則)

INDEX idx_a_b_c (a, b, c)
-- 可用：WHERE a=1 / WHERE a=1 AND b=2 / WHERE a=1 AND b=2 AND c=3
-- 不可用：WHERE b=2 / WHERE c=3 / WHERE a=1 AND c=3 (b 缺失)

不可達查詢自動偵測

EXPLAIN SELECT * FROM sales WHERE name='A' AND name='B';
-- type: NULL, Extra: Impossible WHERE  -- 優化器直接判定無結果，不掃描表

善用此特性：在程式碼中預先檢查互斥條件，避免發送無效 SQL。

進階優化器開關 (MySQL 5.7+)

-- 查看所有開關
SELECT * FROM optimizer_switch;

-- 常用調整
SET SESSION optimizer_switch='index_merge=off,mrr=on,batched_key_access=on';
-- index_merge: 索引合併 (多索引 union/intersect)
-- mrr: Multi-Range Read 將隨機 IO 轉順序 IO
-- bka: Batched Key Access 減少內層表存取次數