萬盛學電腦網

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

MySQL LEFT JOIN 性能分析與優化實戰

從執行原理到索引優化,徹底搞懂 LEFT JOIN

LEFT JOIN 以左表為基準,右表匹配不到則填 NULL。理解其執行流程、優化器行為、索引依賴,是寫出高性能關聯查詢的關鍵。

執行流程演示

-- user 表
id | name
1  | libk
2  | zyfon
3  | daodao

-- user_action 表
user_id | action
1       | jump
1       | kick
1       | jump
2       | run
4       | swim  -- 右表孤兒行

-- SQL
SELECT u.id, u.name, a.action
FROM user u
LEFT JOIN user_action a ON u.id = a.user_id;

結果集

idnameaction
1libkjump
1libkkick
1libkjump
2zyfonrun
3daodaoNULL

💡 關鍵觀察:

  • 左表行 id=3 無匹配 → action=NULL
  • 右表孤兒行 user_id=4 直接丟棄
  • 左表一行匹配右表多行 → 結果集膨脹(1:3)

MySQL 優化器處理邏輯(官方文檔精簡)

  1. 根據左表及其依賴表設置右表
  2. 根據 LEFT JOIN 條件(不含 WHERE)設置左表
  3. LEFT JOIN 條件決定如何從右表搜索行
  4. 套用標準 JOIN 優化(除循環依賴)
  5. 套用標準 WHERE 優化
  6. 若左表行匹配 WHERE 但右表無匹配 ON 條件 → 生成 NULL 行
  7. 關鍵優化:WHERE 條件對 NULL 行恆為 FALSE → LEFT JOIN 自動轉為 INNER JOIN(如 WHERE t2.col=5)

反模式與優化案例

-- ❌ 反模式:強制左表優先導致全掃描
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;

-- ✅ 優化:調整 FROM 順序,小表/有索引表優先
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;

索引設計鐵律

  • ON 條件列(右表)必須有索引:INDEX (user_id)
  • WHERE 過濾列(左表)建議有索引
  • 組合索引遵循最左前綴:ON a.id = b.a_id AND a.type = b.type → INDEX (a_id, type)

實用技巧

找出「僅在左表不在右表」的行

SELECT u.*
FROM user u
LEFT JOIN user_action a ON u.id = a.user_id
WHERE a.user_id IS NULL;  -- 判斷列必須 NOT NULL

USING 簡化同名列

SELECT * FROM user LEFT JOIN user_action USING (id);

強制連接順序

SELECT * FROM small_table STRAIGHT_JOIN big_table ON ...;

性能對比參考

場景無索引右表有索引雙邊有索引
1萬×1萬全表掃描 ~秒級索引查找 ~毫秒級最優
100萬×10萬不可接受可接受推薦

總結

LEFT JOIN 以左為尊,右表 ON 列必加索引;
WHERE 濾右表 NULL 列,自動轉內連更高效;
大表駕駛小表順序錯,STRAIGHT_JOIN 來糾偏;
只查左表無匹配行,IS NULL 判斷最精準。

copyright © 萬盛學電腦網 all rights reserved