萬盛學電腦網

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

MySQL 一張表 LEFT JOIN 關聯兩張表:語法、執行邏輯與效能分析

標準語法

SELECT p.price, pd.name, m.manufacturers_name
FROM products p
LEFT JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id;

執行邏輯圖解

以 user (左) 關聯 user_action (右) 為例:

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;

結果

+----+--------+--------+
| id | name   | action |
+----+--------+--------+
| 1  | libk   | jump   |  -- 匹配 3 行 → 3 列結果
| 1  | libk   | kick   |
| 1  | libk   | jump   |
| 2  | zyfon  | run    |  -- 匹配 1 行
| 3  | daodao | NULL   |  -- 無匹配 → NULL
+----+--------+--------+

關鍵觀察

  • 左表每行對應右表 **0~N 行**,產生 0~N 行結果(0 行時右側全 NULL)
  • 右表孤兒行 (user_id=4) **完全不出現**
  • 結果行數 = Σ(左表每行匹配的右表行數),最少 = 左表行數

ON 條件與 WHERE 的執行階段差異

-- 階段 1:匹配階段(只看 ON)
FROM A LEFT JOIN B ON A.id = B.id AND B.status = 1
-- B.status=1 參與匹配,不匹配則 B 欄位填 NULL

-- 階段 2:過濾階段(看 WHERE)
WHERE A.type = 2
-- 在匹配結果上過濾,可能過濾掉整行(含 NULL 行)

口訣: ON 決定「怎麼關聯」,WHERE 決定「保留哪些行」。

效能分析與優化

EXPLAIN 關鍵指標

EXPLAIN SELECT ... FROM user u LEFT JOIN user_action a ON u.id = a.user_id;
-- 重點看:
-- type: ref / eq_ref (理想) vs ALL (全表掃描)
-- key: 使用的索引
-- rows: 預估掃描行數
-- Extra: Using where; Using index 等

優化清單

  1. 關聯欄位建索引:ALTER TABLE user_action ADD INDEX idx_user_id (user_id);
  2. 小表驅動大表:若 user 小、user_action 大,現狀佳;反之考慮 RIGHT JOIN 或交換順序
  3. 只選需要欄位:避免 SELECT * 回表
  4. 覆蓋索引:INDEX idx_user_id_action (user_id, action) 讓查詢只走索引
  5. 大表分頁:先 SELECT id FROM user LIMIT 10000, 20 再 JOIN 詳情

常見錯誤模式

錯誤寫法問題修正
WHERE b.col = 'x'將 LEFT JOIN 退化為 INNER JOIN移至 ON: AND b.col = 'x'
SELECT * FROM A LEFT JOIN B ON A.id=B.id LEFT JOIN C ON A.id=C.id若 B、C 都大,中間結果爆炸分步查詢或應用層組裝
未給關聯欄位加索引全表掃描,鎖表、慢查詢加索引
copyright © 萬盛學電腦網 all rights reserved