為什么JOIN查詢比子查詢快？—— 驅(qū)動表選擇的秘密
當前位置：點晴教程→知識管理交流
→『 技術(shù)文檔交流 』
摘要： 通過EXPLAIN分析、Nested Loop算法圖解、以及驅(qū)動表選擇的性能對比，揭秘為什么MySQL對子查詢的優(yōu)化很弱、JOIN如何選擇最優(yōu)驅(qū)動表、以及什么時候必須用子查詢。配合時序圖展示查詢流程，給出JOIN優(yōu)化的5個最佳實踐。 一天下午，運營同學反饋數(shù)據(jù)導出功能超時。
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 類型 | 含義 | 性能 | ||||||
| SIMPLE | 簡單查詢（無子查詢） | ????? | ||||||
| PRIMARY | 主查詢 | - | ||||||
| SUBQUERY | 非相關(guān)子查詢 | ??? | ||||||
| DEPENDENT SUBQUERY | 相關(guān)子查詢 | ? 慢 | ||||||
| DERIVED | 派生表（FROM子查詢） | ?? |
示例：
400 186 1886
SELECT u.*, o.*
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 假設(shè)：
-- user表：1000行
-- order_info表：100萬行
-- order_info.user_id有索引
執(zhí)行流程：
1. 掃描user表（1000行）← 驅(qū)動表
2. 每個user.user_id在order_info中查詢（走索引，很快）
3. 總查詢次數(shù)：1000 + 1000 = 2000次（索引查詢很快）
? 小表做驅(qū)動表
? 被驅(qū)動表的JOIN列必須有索引
原則：選擇結(jié)果集小的表做驅(qū)動表
規(guī)則：
1. INNER JOIN：優(yōu)化器自動選擇小表
2. LEFT JOIN：左表是驅(qū)動表（固定）
3. RIGHT JOIN：右表是驅(qū)動表（固定）
-- INNER JOIN（優(yōu)化器自動選擇）
EXPLAIN SELECT * FROM user u INNER JOIN order_info o ON u.user_id = o.user_id\G
*************************** 1. row ***************************
id: 1
table: u ← user表做驅(qū)動表（user表?。?/span>
type: ALL
rows: 1000
*************************** 2. row ***************************
id: 1
table: o ← order_info做被驅(qū)動表
type: ref
key: idx_user_id
rows: 50 ← 每個user_id匹配50個訂單
驅(qū)動表：user（1000行）
被驅(qū)動表：order_info（100萬行）
總掃描次數(shù)：
1000（驅(qū)動表） + 1000 * 1（索引查詢） = 2000次
如果反過來（order_info做驅(qū)動表）：
100萬（驅(qū)動表） + 100萬 * 1（索引查詢） = 200萬次
性能差距：1000倍！
-- LEFT JOIN：左表固定是驅(qū)動表
SELECT * FROM order_info o
LEFT JOIN user u ON o.user_id = u.user_id;
-- 驅(qū)動表：order_info（100萬行）← 大表做驅(qū)動表（危險）
-- 被驅(qū)動表：user（1000行）
EXPLAIN:
table: o
type: ALL
rows: 1000000 ← 掃描100萬行
table: u
type: eq_ref
key: PRIMARY
rows: 1
-- ? 錯誤（大表做驅(qū)動表）
SELECT * FROM order_info o LEFT JOIN user u ON o.user_id = u.user_id;
-- ? 正確（小表做驅(qū)動表）
SELECT * FROM user u LEFT JOIN order_info o ON u.user_id = o.user_id;
-- 或者改成RIGHT JOIN
SELECT * FROM user u RIGHT JOIN order_info o ON u.user_id = o.user_id;
user表：100萬行
order_info表：500萬行
order_info.user_id有索引
-- 子查詢（相關(guān)子查詢）
SELECT * FROM user
WHERE user_id IN (
SELECT user_id FROM order_info
);
-- 執(zhí)行時間：32.5秒
-- 掃描次數(shù)：1 + 100萬次
-- JOIN
SELECT DISTINCT u.*
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 執(zhí)行時間：0.8秒
-- 掃描次數(shù)：1 + 100萬次（但走索引，很快）
-- 性能提升：40倍
-- 小表驅(qū)動大表（正確）
SELECT * FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 執(zhí)行時間：0.8秒
-- 驅(qū)動表：user（100萬行）
-- 大表驅(qū)動小表（錯誤，用STRAIGHT_JOIN強制）
SELECT * FROM user u
STRAIGHT_JOIN order_info o ON u.user_id = o.user_id;
-- 執(zhí)行時間：12.3秒
-- 驅(qū)動表：order_info（500萬行）
-- 性能差距：15倍
測試3：被驅(qū)動表無索引：
?
-- 被驅(qū)動表有索引
SELECT * FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 執(zhí)行時間：0.8秒
-- 刪除索引
ALTER TABLE order_info DROP INDEX idx_user_id;
-- 被驅(qū)動表無索引
SELECT * FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 執(zhí)行時間：85秒（超時）
-- 性能差距：100倍以上
同事C：“看到了嗎？被驅(qū)動表的索引至關(guān)重要！”
場景1：聚合函數(shù)后再過濾
-- 查詢訂單數(shù)超過10的用戶
SELECT u.* FROM user u
WHERE (
SELECT COUNT(*) FROM order_info o WHERE o.user_id = u.user_id
) > 10;
-- 改寫成JOIN很復雜：
SELECT u.* FROM user u
INNER JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM order_info
GROUP BY user_id
HAVING order_count > 10
) o ON u.user_id = o.user_id;
推薦：這種場景用子查詢更簡潔。
場景2：NOT EXISTS
-- 查詢沒有下過訂單的用戶
SELECT * FROM user u
WHERE NOT EXISTS (
SELECT 1 FROM order_info o WHERE o.user_id = u.user_id
);
-- 改寫成LEFT JOIN：
SELECT u.* FROM user u
LEFT JOIN order_info o ON u.user_id = o.user_id
WHERE o.user_id IS NULL;
推薦：NOT EXISTS語義更清晰。
-- 刪除沒有訂單的用戶
DELETE FROM user
WHERE user_id NOT IN (
SELECT DISTINCT user_id FROM order_info
);
-- 必須用子查詢（無法改成JOIN）
實踐1：小表驅(qū)動大表
-- ? 正確
SELECT * FROM small_table s
INNER JOIN large_table l ON s.id = l.s_id;
-- ? 錯誤（如果用LEFT JOIN）
SELECT * FROM large_table l
LEFT JOIN small_table s ON l.s_id = s.id;
-- 改成RIGHT JOIN
SELECT * FROM small_table s
RIGHT JOIN large_table l ON s.id = l.s_id;
實踐2：被驅(qū)動表的JOIN列必須有索引
-- 檢查索引
SHOW INDEX FROM order_info;
-- 如果沒有，創(chuàng)建索引
CREATE INDEX idx_user_id ON order_info(user_id);
實踐3：避免SELECT
-- ? 錯誤
SELECT * FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
-- ? 正確（只查需要的列）
SELECT u.username, u.phone, o.order_no, o.amount
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
實踐4：用STRAIGHT_JOIN強制驅(qū)動表
-- 如果優(yōu)化器選錯了驅(qū)動表，用STRAIGHT_JOIN強制
SELECT * FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.s_id;
-- STRAIGHT_JOIN：強制按寫的順序（small_table做驅(qū)動表）
實踐5：子查詢改寫成JOIN
-- ? 子查詢（慢）
SELECT * FROM user
WHERE user_id IN (
SELECT user_id FROM order_info WHERE status = 1
);
-- ? JOIN（快）
SELECT DISTINCT u.*
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id
WHERE o.status = 1;
主要原因：
1.MySQL對子查詢優(yōu)化很弱
容易產(chǎn)生相關(guān)子查詢（DEPENDENT SUBQUERY）
相關(guān)子查詢會執(zhí)行N次（N=外表行數(shù)）
2.JOIN可以選擇最優(yōu)驅(qū)動表
自動選擇小表做驅(qū)動表
大大減少查詢次數(shù)
3.JOIN可以利用索引
被驅(qū)動表走索引查詢（eq_ref/ref）
子查詢可能無法利用索引
性能對比：
子查詢：1 + 100萬次查詢
JOIN：1000次掃描 + 1000次索引查詢
什么時候用子查詢：
NOT EXISTS（語義清晰）
聚合后再過濾
UPDATE/DELETE中的子查詢
邏輯更清晰的場景
優(yōu)化建議：
小表驅(qū)動大表
被驅(qū)動表JOIN列建索引
能改JOIN就改JOIN
用EXPLAIN檢查執(zhí)行計劃
參考文章：原文鏈接
