網絡流傳「MySQL 多表查詢優先用 JOIN,避免子查詢」,但鮮少講清底層原理。本文從關係代數的標準執行流程出發,對比優化器重排後的實際執行路徑,揭示 JOIN 在優化器加持下的性能優勢本質。
標準流程遵循關係代數操作順序,每步產生一個虛擬表(VT):
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = 'name' LIMIT 5;
T1.id = T2.t1_id 的行 → VT2。T1.name = 'name' 的行 → VT3。痛點:笛卡爾積在大表下資源消耗指數級增長,標準流程無法接受。
SELECT * FROM (SELECT * FROM T1 WHERE T1.name = 'name') AS TMP
INNER JOIN T2 ON TMP.id = T2.t1_id LIMIT 5;
WHERE 過濾 T1 → 臨時表 TMP(行數大幅減少)。結論:標準流程下,子查詢通過「先過濾再連接」縮小中間結果集,**理論上比 JOIN 更快**。
MySQL 優化器在執行前會對查詢樹進行等價變換,核心策略:謂詞下推與連接順序重排。
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = 'name' LIMIT 5;
name 索引且選擇率高,選 T1 為駕駛表。WHERE T1.name = 'name',過濾出符合條件的行 → VT1(小結果集)。ref 訪問 T2(利用 t1_id 索引) → VT2。關鍵差異:優化器**自動將 WHERE 條件下推到駕駛表**,實現了「先過濾再連接」的效果,**且不產生額外臨時表**(子查詢會物化臨時表 TMP,帶來開銷)。
WHERE T2.name = 'x' 時,優化器會選 T2 為駕駛表,先過濾 T2 再反向 JOIN T1。EXPLAIN 觀察 type、rows、Extra: Using where; Using index 判斷實際執行計劃。EXPLAIN 驗證)。ANALYZE TABLE、innodb_stats_persistent=ON、定時 ANALYZE。標準流程下子查詢因「先過濾」而理論更優,但 MySQL 優化器通過謂詞下推與連接重排,將 WHERE 條件提前應用於駕駛表,**在不產生臨時表開銷的前提下達到同樣效果**,且保留了 JOIN 的語義清晰度與優化器進一步優化空間(如 Batched Key Access、Hash Join 8.0.18+)。因此,**信任優化器、寫標準 JOIN、給好索引、更新統計信息**,是高性能 SQL 的正道。