MySQL 查詢優化器核心機制深度解析（上）：索引選擇與條件下推

優化器核心目標

MySQL 查詢優化器的首要目標：盡可能使用索引，並選擇最嚴格（過濾率最高）的索引，儘早排除不符合條件的行。排除得越快，找到匹配行越快。

經典案例：WHERE 條件順序優化

SELECT col3 FROM mytable
WHERE col1 = 'some value' AND col2 = 'some other value';

假設：

• col1 索引匹配 900 行
• col2 索引匹配 300 行
• 兩條件同時匹配僅 30 行

優化器決策： 先掃描 col2（300 行），再過濾 col1，僅 270 次失敗比對；若先掃 col1 則 870 次失敗。優化器自動選擇成本更低的路徑。

助優化器發揮最大效能的編寫準則

1. 比較相同資料型別的欄位

• INT vs BIGINT、CHAR(10) vs VARCHAR(10) 視為相容；CHAR(10) vs CHAR(12) 不相容。
• 型別不同時用 ALTER TABLE 統一，避免隱式轉換導致索引失效。

2. 讓索引欄位在比較表達式中「獨立」

❌ 索引失效寫法：

WHERE mycol * 2 < 4
WHERE YEAR(date_col) < 1990

✅ 索引生效寫法：

WHERE mycol < 4 / 2
WHERE date_col < '1990-01-01'
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL 30 DAY)

原則：索引欄不參與函數運算、不放在表達式左側，使優化器能直接用索引定位範圍。

3. LIKE 模式避免前置通配符

• LIKE 'Mac%' → 可用索引（前綴匹配）。
• LIKE '%Mac%' → 全表掃描。
• 等價改寫：WHERE last_name >= 'Mac' AND last_name < 'Mad'

REGEXP 永不使用索引。

4. 使用 ANALYZE TABLE 更新索引統計資訊

MyISAM/InnoDB/BDB 表執行 ANALYZE TABLE tbl_name;，讓優化器獲得更準確的鍵值分佈，改善執行計劃估算。

EXPLAIN 實戰驗證優化器行為

案例：三種寫法查找 30 天內過期記錄

ALTER TABLE member ADD INDEX (expiration);

-- 寫法 1：函數運算，全表掃描
EXPLAIN SELECT * FROM member WHERE TO_DAYS(expiration) - TO_DAYS(CURDATE()) < 30;
-- type=ALL, key=NULL, rows=102

-- 寫法 2：右側常量預計算，但左側仍有函數，全表掃描
EXPLAIN SELECT * FROM member WHERE TO_DAYS(expiration) < 30 + TO_DAYS(CURDATE());
-- type=ALL, key=NULL, rows=102

-- 寫法 3：索引欄獨立，範圍查詢
EXPLAIN SELECT * FROM member WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY);
-- type=range, key=expiration, rows=6  ✅

案例：聯結查詢索引效果

-- 無索引：雙重全表掃描，估算 1,000,000 行組合
EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
-- t1: type=ALL, rows=1000; t2: type=ALL, rows=1000

-- t2.i2 加索引：t2 變為 ref 類型，估算 10,000 組合
ALTER TABLE t2 ADD INDEX (i2);
EXPLAIN ...;
-- t1: type=ALL, rows=1000; t2: type=ref, key=i2, rows=10

-- 兩表都加索引 + ANALYZE TABLE：t2 rows 降為 1
ANALYZE TABLE t1, t2;
EXPLAIN ...;
-- t2: rows=1  ✅ 最優估算

強制優化器行為（慎用）

• STRAIGHT_JOIN：強制按 FROM 子句順序聯結。
  SELECT STRAIGHT_JOIN ... FROM t1, t2, t3; 或 FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3
• FORCE INDEX/USE INDEX/IGNORE INDEX：指定/建議/忽略索引。

原則： 先用 EXPLAIN 確認優化器確有誤判，再干預；並對比干預前後真實執行時間（多次執行取中位數，避免緩存干擾）。

其他實戰技巧

• 子查詢改寫為聯結： MySQL 4.1+ 支援子查詢，但優化器對聯結更成熟，慢子查詢嘗試改寫為 JOIN。
• 避免隱式型別轉換： num_col = '4' 會觸發字串轉雙精度，導致索引失效；直接寫 num_col = 4。
• 字串欄比較數值同樣失效： str_col = 4 因 '4','4.0','4th' 皆滿足，無法用索引。
• TRUNCATE TABLE vs DELETE： TRUNCATE 重建表極快但重置 AUTO_INCREMENT；需保留序號用 DELETE FROM tbl WHERE 1;（慢但安全）。

下期預告

將深入講解 EXPLAIN 各欄位詳細含義、複雜聯結優化、子查詢優化策略與實戰調優案例。