MySQL 子查詢性能優化：從原理到實戰替換方案

MySQL 子查詢性能優化：從原理到實戰替換方案

子查詢雖然語法直觀，但在 MySQL 中長期存在執行計劃缺陷，導致大數據量下性能急劇下降。本文深入分析子查詢與關聯查詢的性能差異，並提供索引優化、改寫為 JOIN 等實戰方案。

核心概念區分

• 子查詢：在 SELECT 或 WHERE 子句中嵌套 SELECT，如 SELECT a.name, (SELECT b.name FROM b WHERE b.id=a.id) FROM a WHERE a.name LIKE 'a%'
• 表關聯查詢：多表聯合查詢，如 SELECT a.name, b.name FROM a, b WHERE a.id=b.id AND a.name LIKE 'a%'

為什麼子查詢慢？

MySQL 預設對子查詢採用全表掃描執行計劃，總 I/O 次數 ≈ 主表行數 × 子表行數。當雙表均超過 1 萬行，查詢耗時易超過 10 秒，且隨數據增長呈指數級惡化。官方雖早已確認該問題，但穩定版至今未根治。

優化策略

1. 為關聯列建立索引或外鍵

在子表關聯字段（如 b.id）創建索引，可將全表掃描轉為索引查找，大幅降低 I/O。

2. 改寫為 INNER JOIN / LEFT JOIN

將子查詢轉換為等值連接，利用 MySQL 成熟的 JOIN 優化器：

-- 原子查詢
SELECT a.name FROM a WHERE a.id IN (SELECT b.a_id FROM b WHERE b.status=1);

-- 改寫為 JOIN
SELECT a.name FROM a INNER JOIN b ON a.id = b.a_id WHERE b.status = 1;

3. 使用 EXISTS 替代 IN（特定場景）

當子查詢返回結果集較大時，EXISTS 往往比 IN 更高效，因為它只需判斷是否存在匹配行，而非獲取所有值。

實戰驗證建議

1. 使用 EXPLAIN 對比執行計劃，關注 type、rows、Extra 列
2. 在測試環境模擬生產數據量進行壓測
3. 監控 Handler_read_key、Handler_read_rnd_next 狀態變量評估索引命中率

核心原則：能用 JOIN 解決的場景，堅決不用子查詢。MySQL 8.0+ 對子查詢優化有所改善，但生產環境仍建議保守改寫。