MySQL LEFT JOIN 調優實戰：從 3.7 秒到 0.1 秒的四步優化

MySQL LEFT JOIN 調優實戰：從 3.7 秒到 0.1 秒的四步優化

場景

• A 表（大表）數萬行，B 表（小表）約 1,000 行。
• 需求：SELECT * FROM A a LEFT JOIN B b ON a.JOB_ID = b.ID

步驟 0：基線 SQL（3.712s）

EXPLAIN SELECT * FROM A a LEFT JOIN B b ON a.JOB_ID = b.ID;
-- type=ALL, rows=22711*1230 ≈ 2.8 億掃描行（近乎笛卡爾積）

原理： MySQL 使用 Block Nested-Loop Join (BNL)，驅動表（前表）結果集逐行到被驅動表掃描，**驅動表行數決定總掃描量**。

步驟 1：業務語義優化 → INNER JOIN（2.745s）

發現 A 表中 JOB_ID 為 NULL 的行無業務意義，改為 INNER JOIN，過濾無效行。
EXPLAIN 仍為 type=ALL，但掃描行數下降。

步驟 2：被驅動表加主鍵（2.672s）

B 表 ID 原無主鍵，ALTER TABLE B ADD PRIMARY KEY (ID);
EXPLAIN 顯示 type=ALL 未變，**因為主鍵建立後需重新分析統計信息或建立索引才能生效**。

步驟 3：被驅動表加索引（0.109s）🎯

ALTER TABLE B ADD INDEX idx_id (ID);  -- 或直接用主鍵
-- EXPLAIN: type=ref, rows=22711*1 = 2.2 萬掃描行，耗時 0.109s

質變時刻： 索引生效後，BNL 退化為 Index Nested-Loop Join，被驅動表從全表掃描變為索引查找，**總掃描行數 = 驅動表行數 × 1**。

優化總結表

核心心法

1. 小表驅動大表：若可能，把小表放左邊（INNER JOIN 可由優化器自動調換）。
2. 被驅動表連接列必須有索引：否則 BNL 全表掃描，性能隨數據量線性惡化。
3. 業務允許時優先 INNER JOIN：減少外連接保留 NULL 行的開銷，優化器選擇更多。
4. ANALYZE TABLE 及時更新統計信息，讓優化器看到索引。

一句話總結： JOIN 調優核心 = 「被驅動表連接列建索引」+「業務語義選對連接類型」+「統計信息保持新鮮」。