高性能 MySQL 進化論：查詢優化器 8 大局限性與繞過方案

前言

MySQL 優化器雖然智能，但存在固有局限，隨版本迭代逐步改善。理解這些「盲區」能幫助寫出更穩定的高性能 SQL。

1. 關聯子查詢 → 全表掃描陷阱

-- 原始：IN 子查詢
SELECT * FROM t1 WHERE t1.id IN (SELECT t2.fk FROM t2 WHERE t2.id='...');

-- 優化器轉換為 EXISTS 相關子查詢
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.id='...' AND t2.fk=t1.id);
-- 問題：t1 值未定時無法利用 t2 索引 → t1 全表掃描

解決： 改用 INNER JOIN：

SELECT t1.* FROM t1 INNER JOIN t2 ON t1.id = t2.fk WHERE t2.id = '...';

2. UNION 限制：外層條件不下推

-- 問題：外層 LIMIT 不下推到子查詢
(SELECT name FROM student ORDER BY name LIMIT 20)
UNION ALL
(SELECT name FROM member ORDER BY name LIMIT 20)
LIMIT 20;
-- 實際掃描：student 全表 + member 全表 → 合併 → 取 20

解決： 子查詢各自加 LIMIT，外層再 LIMIT (如上例已正確)，但 WHERE/ORDER BY 等外層條件**不會自動下推**，需手動寫入每個子查詢。

3. 等值傳遞：大 IN 列表複製開銷

IN/ON/USING 的值列表在多表間共享時，優化器為便利會為每表複製一份。超大列表 (如 IN (1,2,...10000)) 會增加解析/優化開銷。目前無完美策略，建議：

• 拆分為臨時表 + JOIN
• 應用層分批
• 使用 EXISTS 相關子查詢 (視版本優化)

4. 並行執行：不支援

單查詢單線程執行 (MySQL 8.0 引入 innodb_parallel_read_threads 僅針對全表掃描/索引掃描並行讀取，非通用並行執行)。

5. Hash Join：不支援 (8.0.18+ 支援 Hash Join!)

僅支援 Nested Loop Join (Block Nested Loop / Batched Key Access)。
MySQL 8.0.18+ 引入 Hash Join，針對無索引關聯大幅提速，可用 HASH_JOIN/NO_HASH_JOIN Hint 控制。

6. 鬆散索引掃描：支援極有限

索引 (a,b)，查詢 WHERE b BETWEEN 2 AND 3：

• 理想：跳過 a 不同值的區間，只掃描 b 滿足範圍的葉子頁
• 現實：MySQL 僅在 MIN/MAX、GROUP BY 特定模式下觸發 (參考 Loose Index Scan 文檔)

7. MAX()/MIN() 無索引 → 全表掃描

-- 問題：name 無索引
SELECT MAX(id) FROM t1 WHERE name='sun';  -- 全表掃描

繞過： 強制用主鍵索引 + LIMIT：

SELECT id FROM t1 USE INDEX (PRIMARY) WHERE name='sun' LIMIT 1;

8. 同表查詢與更新互斥

-- 報錯: ERROR 1093: You can't specify target table 't1' for update in FROM clause
UPDATE t1 SET cnt = (SELECT COUNT(*) FROM t1);

解決： 包裝為派生表 / 多表 UPDATE：

UPDATE t1
INNER JOIN (SELECT id, COUNT(*) AS cnt FROM t1 GROUP BY id) AS d USING (id)
SET t1.cnt = d.cnt;

版本演進補充

實戰建議

• 優先寫 JOIN 而非 IN/EXISTS 子查詢
• 大 IN 列表改臨時表 JOIN
• 關鍵查詢用 EXPLAIN ANALYZE (8.0.18+) 驗證執行計劃
• 善用 USE INDEX/IGNORE INDEX Hint 引導優化器
• 升級 MySQL 8.0 獲得 Hash Join、CTE、窗口函數等現代特性