萬盛學電腦網

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

MySQL 聯合查詢效能深度分析:從執行原理到優化實戰

測試場景

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');

LEFT JOIN 執行流程拆解

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

逐步執行邏輯(MySQL 官方文檔流程)

  1. 從左表 user 讀取一行(如 id=1)。
  2. 在右表 user_action 中按 ON 條件查找匹配行(找到 3 行)。
  3. 左右表列拼接產生 3 行結果(含重複左表列)。
  4. 右表無匹配時(id=3),右表列填 NULL 產生 1 行。
  5. 重複上述直到左表掃描完畢。

優化器對 LEFT/RIGHT JOIN 的處理

-- 官方優化規則摘要
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 條件位置陷阱

-- ❌ 錯誤: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';

STRAIGHT_JOIN 強制順序

-- 優化器可能先讀大表 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 類型效能排序(典型場景)

排名 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;

大數據量 JOIN 替代方案

  • 應用層組裝:分兩次查詢,ID 列表 IN 過濾,避免大表關聯。
  • Redis/ES 預聚合:寫入時同步冗余字段,查詢時零 JOIN。
  • ClickHouse/StarRocks:OLAP 引擎原生支援高效大寬表 JOIN。
  • 物化視圖:定時刷新預計算關聯結果。

總結

  1. LEFT JOIN 以左表為驅動,右表按 ON 條件匹配,無匹配填 NULL。
  2. ON 條件列務必建索引,WHERE 條件區分左右表放置位置。
  3. 優化器可通過 STRAIGHT_JOIN、USE INDEX 干預,但需經壓測驗證。
  4. 百萬級以上 JOIN 建議架構層面解耦,避免單查詢過載。
copyright © 萬盛學電腦網 all rights reserved