JOIN 的執行方式：測試環境 20 毫秒，正式環境 40 秒

在認識任何一種 JOIN 演算法之前，先把成本公式寫下來。後面所有的討論都只是在改這條式子裡的某一項。

JOIN 總成本 ≈ 掃驅動表的成本
            + 驅動表符合條件的列數 × 查一次被驅動表的成本

第二項是重點：它是乘法。這條式子直接推出三件事：

• 驅動表過濾後剩幾列，比表本身有多大重要得多。一張一億列的表如果 WHERE 之後只剩 3 列，它是很好的驅動表。
• 「查一次被驅動表」是否走索引，決定了數量級。走索引是幾次 I/O，沒有索引就是掃一次全表——再乘上前面那個次數。
• ch02 的估算誤差在這裡會被放大。優化器估驅動表剩 214 列、實際剩 120 萬列，那第二項就差了 5,600 倍。

MySQL 只有兩種 JOIN 演算法

比起其他資料庫，MySQL 8.0 的選項意外地少——而這反而讓判斷變簡單：

• Index Nested Loop Join（NLJ）：拿驅動表的每一列，去被驅動表的索引查一次。被驅動表有可用索引時就走這個，也是絕大多數健康查詢的樣子。
• Hash Join：把驅動表整批讀進記憶體建成 hash 表，再掃被驅動表逐列比對。被驅動表沒有可用索引時的救援方案（8.0.18 引進，8.0.20 起全面取代舊的 Block Nested Loop）。

看 EXPLAIN 就能分辨：Extra 出現 Using join buffer (hash join) 就是後者。在 FORMAT=TREE 裡更直接，節點名稱直接寫 Nested loop inner join 或 Inner hash join。

一句話的判斷標準

NLJ 的邏輯就是字面意思——兩層迴圈，只是內層走索引：

for (驅動表 o 中每一列 row) {          // 外層：掃一次
    用 row.user_id 去 users 的主鍵索引查一次;   // 內層：走 B+Tree
    如果找到就組合輸出;
}

在 EXPLAIN 裡的樣子

EXPLAIN SELECT o.id, u.name FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.merchant_id = 8821;

| id | table | type   | key                 | rows | Extra       |
|  1 | o     | ref    | idx_merchant        |  180 | Using where |
|  1 | u     | eq_ref | PRIMARY             |    1 | NULL        |

三個要確認的點，缺一個就不健康：

• 誰在第一列：EXPLAIN 表格的第一列就是驅動表（不是你 SQL 裡寫在前面的那張）。
• 被驅動表的 type：eq_ref（走唯一索引，每次一列）最好，ref（非唯一索引）可以接受。
• 驅動表的 rows：這就是乘數。180 次索引查詢是小事；180 萬次就不是了。

成本怎麼算

驅動表 180 列 × 被驅動表每次 3 次 I/O（B+Tree 三層）
= 540 次 I/O + 掃驅動表的成本  →  幾毫秒

而如果 users 的 id 上沒有索引：

180 × 掃一次 users 全表（假設 65,000 頁）
= 11,700,000 次頁讀取  →  以分鐘計

用 EXPLAIN ANALYZE 看真正的次數

回扣 ch01：loops 才是乘數的實測值。

-> Nested loop inner join  (actual time=0.09..42.3 rows=178 loops=1)
   -> Index lookup on o using idx_merchant  (actual rows=180 loops=1)
   -> Single-row index lookup on u using PRIMARY
      (actual time=0.02..0.02 rows=1 loops=180)   ← 乘數在這裡

loops=180 與估算的 rows=180 一致，代表 ch02 的統計資訊是準的。這兩個數字對不上的時候，問題通常不在 JOIN，在上一章。

當被驅動表的 JOIN 欄位沒有索引，NLJ 就退化成「每一列都掃一次全表」。MySQL 8.0.18 之後改用 Hash Join 來救：

成本從「n × 掃一次 m」變成「掃一次 n + 掃一次 m」——兩張表各掃一次就好。這確實是巨大的改善，但它只適用於等值 JOIN（ON a.x = b.y），範圍條件的 JOIN 用不了。

記憶體不夠的時候

join_buffer_size 預設只有 256KB。裝不下 hash 表時，MySQL 會走 grace hash join：把兩邊都依 hash 值分割成一批批的 chunk 檔案寫到磁碟，再一組一組載回來比對。

調大 join_buffer_size 是解法嗎

是止血，不是解法，而且它有一個很容易踩的性質：

• 它是每個連線、每個 JOIN 各配一份，不是全域共用一塊。設成 256MB、100 個連線同時各跑一個兩表 JOIN，就是 25GB。
• 真正的問題是「被驅動表沒索引」，補上索引之後 hash join 根本不會被選用，這塊記憶體也就不需要了。

-- 診斷用：先在 session 層試，不要直接改全域
SET SESSION join_buffer_size = 8 * 1024 * 1024;

-- 確認它有沒有落磁碟
SHOW STATUS LIKE 'Created_tmp_disk_tables';

它也不是永遠比較差

有一種情況 hash join 確實是對的選擇：兩張大表要做大範圍的關聯（例如報表、對帳、批次）。這時候即使被驅動表有索引，逐列走 B+Tree 的隨機 I/O 反而輸給「兩張表各順序掃一次」。

這正好是 ch01 那個推導的 JOIN 版本：當命中比例夠高，順序掃描會贏過大量隨機 I/O。所以判斷準則不是「看到 hash join 就要修」，而是「這是一次幾列的查詢，還是一次幾百萬列的批次？」——線上 API 出現 hash join 幾乎都是缺索引，報表批次則未必。

「小表驅動大表」是流傳最廣的 JOIN 準則，方向對，但照字面理解會出錯。正確的說法是：讓「經過 WHERE 過濾後結果集最小」的那張表當驅動表。

為什麼差別很大

users:  1 萬列（小表）
orders: 2000 萬列（大表）

SELECT ... FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.city = '台北'          -- 過濾後 8,000 列
  AND o.id = 12345;            -- 過濾後 1 列

照「小表驅動大表」應該用 users 驅動，那就是 8,000 次索引查詢。但實際上該用 orders 驅動——它雖然是大表，過濾後只剩 1 列，只需要 1 次查詢。

還有一個前提常被忽略

這條準則只在被驅動表有索引時成立。如果被驅動表沒索引、要走 hash join，那成本是「兩邊各掃一次」——誰驅動誰的差別就小得多了，這時候糾結驅動表順序是浪費力氣，該做的是補索引。

怎麼看它選了誰、怎麼改

-- EXPLAIN 表格的第一列就是驅動表
-- FORMAT=TREE 裡，外層（縮排較淺）的節點是驅動表

-- 強制順序：照 FROM 的書寫順序來（老寫法）
SELECT ... FROM t1 STRAIGHT_JOIN t2 ON ...;

-- 8.0 的 hint（較推薦，粒度更細）
SELECT /*+ JOIN_ORDER(o, u) */ ...;    -- 指定完整順序
SELECT /*+ JOIN_PREFIX(o) */ ...;      -- 只指定誰先

但這些跟 ch02 的結論一樣是止血：優化器選錯驅動表，十次有九次是因為 rows × filtered 估錯了。先去看估算與實測差多少，而不是直接壓順序。

表一多，它可能連考慮都沒考慮過

回扣 ch02 提過的搜尋深度：n 張表有 n! 種順序，超過門檻後優化器改用貪婪搜尋。五張表以上的 JOIN，「它沒有選到明顯更好的順序」是有可能的，而且不是 bug。這種場景反而是 hint 少數合理的用途之一。

你寫的子查詢，優化器多半不會照字面執行。知道它會被改寫成什麼，才知道為什麼有些子查詢很快、有些會慢到不可思議。

IN (SELECT ...) 通常會變成 semi-join

-- 你寫的
SELECT * FROM users u
WHERE u.id IN (SELECT o.user_id FROM orders o WHERE o.amount > 10000);

-- 優化器實際做的（semi-join）：當成 JOIN 處理，但只要「有沒有配對」，不重複輸出

MySQL 有五種 semi-join 策略，EXPLAIN 的 Extra 會寫出用了哪一種：FirstMatch（找到一筆就跳下一列）、LooseScan、Materialize（把子查詢結果物化成暫存表）、DuplicateWeedout、以及直接把表 pull-out 併進 JOIN。

例外：DEPENDENT SUBQUERY

真正該警覺的是這個。當子查詢引用了外層的欄位（關聯子查詢），它就無法被獨立物化：

EXPLAIN SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id AND o.status = 9);

| id | select_type        | table |
|  1 | PRIMARY            | u     |
|  2 | DEPENDENT SUBQUERY | o     |   ← 外層每一列都要跑一次

DEPENDENT SUBQUERY 的意思就是外層有幾列，這個子查詢就跑幾次——又是一個乘法。它不一定是問題（如果外層只有幾十列、內層走索引，那很快），但如果外層是幾十萬列，就是災難。判斷方式一樣：看外層的 rows，那就是乘數。

NOT IN 與 NULL 的陷阱

-- ❌ 語意與效能都有風險
WHERE u.id NOT IN (SELECT o.user_id FROM orders o)

-- ✅ 語意安全，也比較好被改寫成 anti-join
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

這是 ch01 那個隱式轉換的同類問題：不會報錯、只會回傳錯的結果——這一軌反覆出現的第二種危險。

這一節是語意問題，但它會直接變成效能問題，所以放在這裡。

-- ① 條件放 ON
SELECT u.*, o.id FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 1;

-- ② 條件放 WHERE
SELECT u.*, o.id FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 1;

兩者的結果不一樣

• ①：所有 users 都會出現。沒有符合條件訂單的人，o.id 是 NULL。
• ②：WHERE o.status = 1 會把 o 全為 NULL 的那些列濾掉——LEFT JOIN 退化成 INNER JOIN。

所以 ② 有時候反而更快——但那是回答了另一個問題的快。這種「快了但答案變了」是最糟的組合，因為效能數字會讓人以為改對了。

怎麼記

• ON 決定「怎麼配對」，WHERE 決定「配完之後留誰」。
• 要保留左表全部的列，被驅動表的過濾條件一定要放 ON。
• 如果 WHERE 裡真的需要判斷右表，只能用 IS NULL／IS NOT NULL——那才是「找出沒有訂單的使用者」的正確寫法（anti-join）。

-- 找出沒有任何有效訂單的使用者
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 1
WHERE o.id IS NULL;

在 ORM 裡這個陷阱更常見：JPA 的 @Where、動態條件拼接、Specification 組出來的條件，預設幾乎都會落在 WHERE 而不是 ON。所以 ORM 產生的 LEFT JOIN 要特別檢查（ch08 會再回到這裡）。

徵狀

訂單查詢功能改版，新增了「顯示商家名稱與優惠券資訊」，於是查詢從兩張表變成三張：

SELECT o.id, o.amount, m.name, c.code
FROM orders o
JOIN merchants m ON m.code = o.merchant_code
LEFT JOIN coupons c ON c.id = o.coupon_id
WHERE o.created_at >= '2026-08-01' AND o.status = 1
ORDER BY o.created_at DESC LIMIT 50;

• 測試環境：20 毫秒，計畫漂亮，Code Review 通過，上線。
• 正式環境：40 秒，而且 CPU 直接被打滿。
• 兩邊的索引定義完全一致（有拿 SHOW CREATE TABLE 逐字比對過）。

診斷

照順序來。先看正式環境的計畫：

| id | table | type | key          | rows    | Extra                              |
|  1 | o     | range| idx_created  | 84200   | Using where                        |
|  1 | m     | ALL  | NULL         | 12400   | Using where; Using join buffer (hash join) |
|  1 | c     | eq_ref| PRIMARY     | 1       | NULL                               |

merchants 走了 ALL + hash join——但 merchants.code 上明明有唯一索引。第二張表的 JOIN 欄位有索引卻用不到，回到 ch01 的清單：欄位被什麼東西包住了。

SHOW CREATE TABLE orders\G
  merchant_code varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci

SHOW CREATE TABLE merchants\G
  code          varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci   ← 不一樣

為什麼測試環境不會出現

修法

• 治本：統一 collation。ALTER TABLE merchants CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;——但這是重建整張表的 DDL，大表要用線上 DDL 工具、要挑離峰、要先確認排序規則變更不會影響既有的比對語意（ch06 主講）。
• 止血：在 JOIN 條件上明確指定定序 ON m.code = o.merchant_code COLLATE utf8mb4_0900_ai_ci——但要讓被轉換的是驅動表那一邊，否則索引照樣失效。這種寫法很容易寫反，改完一定要用 EXPLAIN 確認 type 回到 eq_ref。
• 防再犯：建表模板固定 charset／collation；上線前的 schema diff 檢查納入欄位屬性，不要只比對索引與欄位名。

這個案例真正的教訓

把前面幾節收成一個可以照著跑的順序。每一步都是為了回答那條成本公式裡的某一項。

什麼時候不要用 JOIN

• 資料在不同的資料庫或服務裡——那就沒得選，只能在應用層組裝。此時要注意的是不要組成 N+1（ch08）。
• JOIN 出來的結果會大量重複：一對多的 JOIN 會把「一」那邊的欄位重複很多次。主表有幾個大欄位時，網路傳輸與 ORM 映射的成本可能超過查兩次資料庫。
• 分頁 + 一對多 JOIN：LIMIT 20 限制的是 JOIN 之後的列數，不是主表的列數——結果會少於 20 筆主資料。這是 ORM 分頁最經典的錯誤，ch08 會完整拆解。

帶到下一章

你可能已經注意到，前面的例子裡反覆出現 ORDER BY ... LIMIT，而我一直沒有處理它。當 JOIN 的結果需要排序、分組、或翻到第五千頁時，成本會用另一種方式爆炸——那是下一章的主題。

這一章給的每個解法都有帳單。第二條主線「代價守恆」在 JOIN 這裡特別明顯。

四筆帳

• 補索引讓 NLJ 走得動 → 寫入變慢、空間變大。被驅動表如果是寫入頻繁的表，這筆帳不小（ch06 會算）。
• 調大 join_buffer_size → 它是每連線每 JOIN 各一份，不是共用。乘上連線數就是實際記憶體用量，而那塊記憶體是從 Buffer Pool 搶來的（ch07）。
• 用 hint 壓驅動表順序 → 與 ch02 同一筆帳：資料分佈變了它不會跟著變，而且會掩蓋統計資訊失準這個根因。
• 反範式冗餘欄位以避開 JOIN → 讀變快，但從此要維護一致性：來源改了誰負責同步？同步失敗怎麼辦？這是把「查詢複雜度」換成「寫入複雜度 + 資料一致性風險」（ch06 主講）。

JOIN 診斷看不到的東西

• 它不會告訴你這句 JOIN 被呼叫了幾次。一個計畫完美的兩表 JOIN，在迴圈裡跑 200 次，一樣會拖垮系統（ch08）。
• 它不會告訴你 JOIN 期間持有了什麼鎖。SELECT ... FOR UPDATE 的 JOIN 會依序在多張表上加鎖，而加鎖順序不一致正是死鎖的來源（ch05）。
• 它不會告訴你結果集有多大。一對多 JOIN 回傳 50 萬列時，資料庫端可能只花 200 毫秒，應用端 ORM 映射花掉 10 秒。

把這一章收成三句