萬盛學電腦網

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

MySQL 查詢優化器深度解析:LEFT JOIN、巢狀 JOIN、ORDER BY 與 LIMIT 優化策略

一、 LEFT JOIN / RIGHT JOIN 執行邏輯與優化

執行順序

  1. 根據左表 A 及其依賴表設定右表 B。
  2. 根據 LEFT JOIN 條件中使用的所有表 (除 B 外) 設定左表 A。
  3. ON 條件僅用於從 B 尋找匹配行,WHERE 條件不參與此階段。
  4. 執行標準聯接優化 (受限於依賴順序)。
  5. 套用標準 WHERE 優化。
  6. 若 A 有行符合 WHERE 但 B 無匹配,生成全 NULL 的 B 行。
  7. 若查詢為「找出不存在的行」且 WHERE 有 col IS NULL (col 為 NOT NULL),匹配到第一行即停止掃描後續行。

RIGHT JOIN 邏輯鏡像,表角色互換。

優化器限制與 STRAIGHT_JOIN

優化器計算 JOIN 順序,LEFT JOIN 強制讀取順序可減少排列組合,加速優化。但錯誤順序會導致全表掃描:

-- 低效:強制先讀 a,再左連 c,d,最後才關聯 b
SELECT * FROM a, b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;

-- 高效:將 b 移至 FROM 最前,優化器可先用 b 過濾
SELECT * FROM b, a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;

NULL 拒絕轉內部聯接

若 WHERE 條件對 LEFT JOIN 產生的 NULL 行為 FALSE/UNKNOWN (如 t2.col=5),MySQL 自動轉為內部聯接:

SELECT * FROM t1 LEFT JOIN t2 ON (t1.col=t2.col) WHERE t2.col2=5;
-- 等價於
SELECT * FROM t1, t2 WHERE t2.col2=5 AND t1.col=t2.col;

此時優化器可自由選擇 t2 為驅動表。若需強制順序用 STRAIGHT_JOIN。

二、 巢狀 JOIN (Nested Joins) 處理規則

語法擴展

MySQL 允許括號巢狀:t1 LEFT JOIN (t2, t3) ON ... 等價 t1 LEFT JOIN (t2 CROSS JOIN t3) ON ...。CROSS JOIN 語法上等同 INNER JOIN。

括號移除規則

  • 純內部聯接: 可忽略括號,任意順序評估 (左結合)。
  • 含外部聯接: 不可 隨意移除括號,會改變語義與結果。

範例差異演示

假設:t1={1,2}, t2={1,101}, t3={101}

查詢結果行
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a{1,1,101,101}, {2,NULL,NULL,NULL}
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL{1,1,101,101}, {2,NULL,NULL,101}

第二種查詢中,t3 仍能匹配到 101,導致結果不同。

優化器執行策略

  • 外部聯接強制外表在外層迴圈、內表在內層 (Nested Loop Join)。
  • 引入旗標 (f1, f2) 標記是否找到匹配,未匹配時輸出 NULL 補齊行。
  • 條件下推 (Pushed Conditions):WHERE 條件拆解為 C1(T1) AND C2(T2)...,盡早在外層迴圈過濾,但外部聯接需受旗標保護,避免過濾掉 NULL 補齊行。

三、 ORDER BY 優化:索引消除排序

可使用索引排序的條件

  • ORDER BY 列順序符合索引前綴 (允許 WHERE 中常數填補前綴缺口)。
  • 全部 ASC 或全部 DESC (混合排序不可用)。
  • 查詢行來源為第一張非 const 表。
  • 索引類型支援有序 (B-Tree 是,Hash 否)。

無法使用索引排序的情況

  • 多索引混合:ORDER BY key1, key2
  • 非連續索引鍵:WHERE key2=const ORDER BY key_part2
  • 混合 ASC/DESC
  • ORDER BY 列非驅動表
  • GROUP BY 與 ORDER BY 表達式不同
  • HEAP 表 Hash 索引

Filesort 演算法改進

現代版本在排序元組中直接攜帶查詢所需欄位,避免二次回表。受 max_length_for_sort_data 限制,超過則退回舊模式 (磁碟活動頻繁、CPU 低)。

調優建議

  • 優先讓優化器用索引 (調整索引順序/ WHERE 常數)。
  • 增大 sort_buffer_size, read_rnd_buffer_size。
  • tmpdir 指向獨立物理磁碟 (非同磁碟分區),支援多路徑輪詢。
  • GROUP BY 隱含排序,若不需排序加 ORDER BY NULL 禁用。

四、 LIMIT 優化 (詳見獨立章節)

核心:LIMIT n 讓排序/去重/分組提前終止;LIMIT 0 快速驗證;臨時表大小由 LIMIT 估算。

copyright © 萬盛學電腦網 all rights reserved