萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 執行流程深度解析:標準流程 vs 優化器重排,為何推薦 JOIN 而非子查詢

前言

網絡流傳「MySQL 多表查詢優先用 JOIN,避免子查詢」,但鮮少講清底層原理。本文從關係代數的標準執行流程出發,對比優化器重排後的實際執行路徑,揭示 JOIN 在優化器加持下的性能優勢本質。

一、標準 SQL 執行流程(關係代數邏輯)

標準流程遵循關係代數操作順序,每步產生一個虛擬表(VT):

SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = 'name' LIMIT 5;
  1. FROM + JOIN:T1 × T2 笛卡爾積 → VT1(行數 = T1行數 × T2行數,極其巨大)。
  2. ON:過濾 VT1 中滿足 T1.id = T2.t1_id 的行 → VT2。
  3. WHERE:過濾 VT2 中滿足 T1.name = 'name' 的行 → VT3。
  4. LIMIT:取 VT3 前 5 行 → VT4。
  5. 返回 VT4。

痛點:笛卡爾積在大表下資源消耗指數級增長,標準流程無法接受。

二、子查詢在標準流程下的執行

SELECT * FROM (SELECT * FROM T1 WHERE T1.name = 'name') AS TMP
INNER JOIN T2 ON TMP.id = T2.t1_id LIMIT 5;
  1. 先執行子查詢 WHERE 過濾 T1 → 臨時表 TMP(行數大幅減少)。
  2. TMP × T2 笛卡爾積 → VT1(體積遠小於 T1×T2)。
  3. 後續 ON、WHERE、LIMIT 同標準流程。

結論:標準流程下,子查詢通過「先過濾再連接」縮小中間結果集,**理論上比 JOIN 更快**。

三、MySQL 優化器重排:為何 JOIN 反而更快

MySQL 優化器在執行前會對查詢樹進行等價變換,核心策略:謂詞下推與連接順序重排。

同一 JOIN 查詢經優化器處理後的實際流程

SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = 'name' LIMIT 5;
  1. 識別駕駛表:優化器統計信息發現 T1 有 name 索引且選擇率高,選 T1 為駕駛表。
  2. 謂詞下推:優先在 T1 上應用 WHERE T1.name = 'name',過濾出符合條件的行 → VT1(小結果集)。
  3. 連接驅動:以 VT1 為外層循環,通過 ref 訪問 T2(利用 t1_id 索引) → VT2。
  4. 剩餘條件:應用 ON 條件(已在連接時隱式完成)、LIMIT。
  5. 返回結果。

關鍵差異:優化器**自動將 WHERE 條件下推到駕駛表**,實現了「先過濾再連接」的效果,**且不產生額外臨時表**(子查詢會物化臨時表 TMP,帶來開銷)。

四、優化器選擇駕駛表的規律

  • 優化器不以 SQL 書寫順序決定駕駛表,而是綜合評估:表大小、索引選擇率、WHERE 條件涉及的表。
  • 例:WHERE T2.name = 'x' 時,優化器會選 T2 為駕駛表,先過濾 T2 再反向 JOIN T1。
  • 可用 EXPLAIN 觀察 type、rows、Extra: Using where; Using index 判斷實際執行計劃。

五、子查詢仍有優勢的場景

  • 衍生表物化後可複用:同一子查詢結果被多次引用(如多個 JOIN 同一衍生表)。
  • 複雜邏輯封裝:多層聚合、窗口函數、CTE(8.0+)等難以平鋪為 JOIN 的邏輯。
  • 優化器誤判時:統計信息過期導致優化器選錯駕駛表,強制子查詢物化可繞過誤判(需 EXPLAIN 驗證)。

六、最佳實踐建議

  1. 優先編寫 JOIN,讓優化器自由選擇最優路徑。
  2. 保持統計信息新鮮:ANALYZE TABLE、innodb_stats_persistent=ON、定時 ANALYZE。
  3. 為 JOIN/WHERE 列建立合適索引(覆蓋索引、聯合索引順序匹配查詢模式)。
  4. 必要時用 CTE (WITH) 替代子查詢:MySQL 8.0+ 支持 CTE,可避免衍生表物化開銷,優化器可直接下推謂詞。
  5. 用 EXPLAIN ANALYZE (8.0.18+) 驗證實際執行時間與行數,而非憑經驗猜測。

總結

標準流程下子查詢因「先過濾」而理論更優,但 MySQL 優化器通過謂詞下推與連接重排,將 WHERE 條件提前應用於駕駛表,**在不產生臨時表開銷的前提下達到同樣效果**,且保留了 JOIN 的語義清晰度與優化器進一步優化空間(如 Batched Key Access、Hash Join 8.0.18+)。因此,**信任優化器、寫標準 JOIN、給好索引、更新統計信息**,是高性能 SQL 的正道。

copyright © 萬盛學電腦網 all rights reserved