CREATE TABLE `user` (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO `user` VALUES (1,'libk'),(2,'zyfon'),(3,'daodao');
CREATE TABLE `user_action` (user_id INT, action VARCHAR(20));
INSERT INTO `user_action` VALUES (1,'jump'),(1,'kick'),(1,'jump'),(2,'run'),(4,'swim');
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
1 libk kick
1 libk jump
2 zyfon run
3 daodao NULL
user 讀取一行(如 id=1)。user_action 中按 ON 條件查找匹配行(找到 3 行)。-- 官方優化規則摘要
1. 根據左表及其依賴表設置右表
2. 根據 LEFT JOIN 條件中使用的所有表(除右表)設置左表
3. LEFT JOIN 條件決定如何從右表搜索行(WHERE 條件不參與)
4. 標準 JOIN 優化適用,循環依賴報錯
5. 所有標準 WHERE 優化
6. 若左表行匹配 WHERE 但右表無 ON 匹配,生成 NULL 行
7. WHERE 含 right.col IS NULL 且 right.col 為 NOT NULL,匹配第一行後停止掃描右表
-- 無索引:全表掃描 user_action,rows=5
EXPLAIN SELECT * FROM user u LEFT JOIN user_action a ON u.id = a.user_id;
-- 加索引後:ref 類型,rows=1
ALTER TABLE user_action ADD INDEX idx_user_id (user_id);
-- ❌ 錯誤:WHERE 過濾右表列,導致 NULL 行被過濾,退化為 INNER JOIN
SELECT * FROM user u LEFT JOIN user_action a ON u.id = a.user_id
WHERE a.action = 'run';
-- ✅ 正確:右表過濾條件放 ON 子句
SELECT * FROM user u LEFT JOIN user_action a ON u.id = a.user_id AND a.action = 'run';
-- 優化器可能先讀大表 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 先讀,再按 a→c→d 順序
SELECT * FROM b STRAIGHT_JOIN a LEFT JOIN c ON c.key=a.key LEFT JOIN d ON d.key=a.key WHERE b.key=d.key;
| 排名 | JOIN 類型 | 適用場景 | 優化建議 |
|---|---|---|---|
| 1 | INNER JOIN |
雙邊必須匹配,業務允許丟棄不匹配 | 優化器自由選擇驅動表,效能最好 |
| 2 | LEFT JOIN(小表驅動大表) |
保留左表全量,右表補充 | 左表加 WHERE 過濾縮小驅動集,右表 ON 列建索引 |
| 3 | LEFT JOIN(大表驅動小表) |
業務強制保留大表全量 | 考慮改寫為 UNION 或應用層組裝 |
| 4 | RIGHT JOIN |
極少用,可改寫為 LEFT JOIN | 統一改 LEFT JOIN 降低認知負荷 |
-- 強制使用 idx_user_id
SELECT * FROM user u LEFT JOIN user_action a USE INDEX (idx_user_id) ON u.id = a.user_id;
-- 忽略錯誤索引
SELECT * FROM user u LEFT JOIN user_action a IGNORE INDEX (bad_idx) ON u.id = a.user_id;
-- 強制使用索引否則全表掃描(慎用)
SELECT * FROM user u LEFT JOIN user_action a FORCE INDEX (idx_user_id) ON u.id = a.user_id;
IN 過濾,避免大表關聯。