索引的真相：為什麼查詢會慢，加了索引又為什麼沒用

在談索引之前，要先知道一件事：資料庫慢，慢的幾乎不是「計算」，而是「去磁碟拿東西」。

數量級的差距

• 記憶體隨機存取：約 100 奈秒
• SSD 隨機讀取：約 100 微秒 —— 是記憶體的 1,000 倍
• 傳統硬碟隨機讀取：約 10 毫秒 —— 是記憶體的 100,000 倍

CPU 比較一百萬次的成本，可能還不如去磁碟拿一次資料。所以優化資料庫查詢，本質上就是想辦法少去磁碟幾次。

關鍵：讀取的單位是「頁」，不是「列」

InnoDB 不會只讀你要的那一列。它讀寫的最小單位是頁（page），預設 16KB。你只要一個欄位，它也是整頁搬進記憶體。

由此推導出索引存在的唯一理由

索引不是為了「比較得比較快」，是為了讓資料庫不必把整張表的頁都搬進記憶體。一張 10GB 的表全表掃描要讀 65 萬頁；如果索引能讓它只讀 3 頁就找到目標，那就是二十萬倍的差距。

後面所有的設計——B+Tree 為什麼長那樣、為什麼要有聚簇索引、為什麼回表很貴——全部都是在回答同一個問題：怎麼少讀幾頁。

資料結構課本會告訴你，有序查找用二元搜尋樹，複雜度 O(log₂ n)，很漂亮。但沒有任何一個關聯式資料庫用它。為什麼？

先算一筆帳

假設一張表有 1.3 億列，用二元搜尋樹：

log₂(130,000,000) ≈ 27

27 次比較——聽起來很少。但問題是：樹有 27 層，就代表最壞情況要往下走 27 個節點，而每個節點可能在磁碟的不同位置。

解法：把樹壓矮

既然每往下一層就是一次 I/O，那就想辦法減少層數。方法是讓每個節點裝更多的 key，分支數變多，樹自然變矮。

一個節點正好就是一頁 16KB。假設 key 是 BIGINT（8 bytes）＋子節點指標（6 bytes）＝ 14 bytes：

16384 bytes ÷ 14 bytes ≈ 1170 個分支

算算三層能裝多少

• 第 1 層（根）：1,170 個分支
• 第 2 層：1,170 × 1,170 ＝ 約 137 萬個分支
• 第 3 層（葉節點，放實際資料，假設一列 1KB，一頁放 16 列）：137 萬 × 16 ＝ 約 2,190 萬列

記住這一句

樹高就是 I/O 次數。整章後面所有的內容，都是這句話的推論。

B+Tree 之所以贏過 B-Tree，靠的是兩個看起來很小、影響卻很大的設計。

設計一：非葉節點只放 key，不放資料

B-Tree 的每一層都可以放資料。B+Tree 不行——資料只存在最底層的葉節點，上面全部只是路標。

好處很直接：一頁 16KB 如果要放資料，一列 1KB 就只能放 16 個；如果只放 key，可以放 1170 個。分支數差了 70 倍，樹高就從 5 層降到 3 層。

設計二：葉節點用雙向鏈結串起來

所有葉節點之間有指標互相連著，形成一條由小到大的有序鏈。這解決了範圍查詢：

SELECT * FROM orders WHERE id BETWEEN 1000 AND 2000;

• B+Tree：從根往下找到 id = 1000 的葉節點，然後順著鏈結一路往右讀，讀到 2000 為止。
• B-Tree：資料散在各層，必須中序遍歷，讀完一個節點要回頭找父節點再往下——來回跳，磁碟位置也跳。

一個常被忽略的推論

因為葉節點本來就是排好序的鏈結，所以：

這是最違反直覺、也最重要的一件事。很多人以為表資料存在一個地方，索引是另外一份「目錄」指過去。在 InnoDB 裡不是這樣。

主鍵索引的葉節點，直接存整列資料

不是指標，不是位址——就是那一列本身。所以主鍵索引這棵樹，同時就是這張表的全部資料。這棵樹叫聚簇索引（Clustered Index）。

如果沒有指定主鍵會怎樣

InnoDB 一定要有聚簇索引，所以它會依序找：

• 有主鍵 → 用主鍵
• 沒主鍵 → 找第一個 NOT NULL 的唯一索引
• 都沒有 → 自己偷偷生一個 6 bytes 的隱藏欄位 row_id，你看不到也用不到

真正的代價：主鍵決定了整張表的物理排列

因為資料是按主鍵順序存放的，主鍵選什麼，就決定了新資料要插在哪裡。

所以自增主鍵為什麼被推薦

AUTO_INCREMENT 保證新資料永遠是最大的，只會往最後一頁追加，不會插進中間、不會頁分裂。這不是什麼慣例，是由聚簇索引的結構直接推導出來的結果。

如果業務上非要用 UUID（例如分散式系統要避免 ID 衝突），常見做法是：主鍵仍用自增，UUID 當作一個有唯一索引的普通欄位。

除了主鍵之外，你自己建的索引都叫二級索引（Secondary Index），也叫輔助索引。它們也是 B+Tree，但葉節點放的東西不一樣。

二級索引的葉節點放的是「主鍵值」

不是整列資料，也不是磁碟位址，是主鍵。所以一次二級索引查詢，其實走了兩棵樹：

SELECT * FROM users WHERE email = 'andy@example.com';

① 在 email 索引樹裡找 'andy@example.com'  →  拿到 id = 42
② 拿著 id = 42 回到主鍵樹（聚簇索引）再查一次  →  拿到整列資料
   ↑ 這一步就叫「回表」（back to table）

為什麼要設計成這樣

如果二級索引直接存資料，那有 5 個索引就有 5 份資料副本，更新一列要改 5 個地方。存主鍵值只佔幾 bytes，而且資料搬動時（例如頁分裂）不用更新每個二級索引——因為主鍵值不會變。這是空間與維護成本的取捨。

回表有多貴

回表是隨機 I/O——找到的每一個主鍵值，都要各自到主鍵樹走一趟。如果一個查詢命中 10,000 列，那就是 10,000 次回表。

怎麼看出有沒有回表

EXPLAIN SELECT * FROM users WHERE email = ?;

• Extra: Using index → 沒有回表（用了覆蓋索引，見下一張卡）
• 沒有這一行，只有 Using where → 回表了

覆蓋索引：讓查詢不必回表

如果你 SELECT 的所有欄位，索引裡通通都有，那就不用回表了——這叫覆蓋索引（Covering Index）。

-- email 索引的葉節點裡有：email（key）＋ id（主鍵值）

SELECT id FROM users WHERE email = ?;      -- ✅ 覆蓋，不回表
SELECT id, email FROM users WHERE email = ?; -- ✅ 覆蓋，不回表
SELECT * FROM users WHERE email = ?;       -- ❌ 要 name、phone…，得回表

複合索引的排序規則

建立 INDEX idx (a, b, c) 時，B+Tree 是這樣排的：先按 a 排序；a 相同的，再按 b 排；a 和 b 都相同的，才按 c 排。

最左前綴原則

因為是這種排法，索引只能從最左邊開始連續使用：

• ✅ WHERE a = 1
• ✅ WHERE a = 1 AND b = 2
• ✅ WHERE a = 1 AND b = 2 AND c = 3
• ❌ WHERE b = 2 —— 用不到
• ❌ WHERE c = 3 —— 用不到
• ⚠️ WHERE a = 1 AND c = 3 —— 只有 a 用得到，c 用不到（中間斷了）

範圍查詢會截斷後面的欄位

-- 索引 (a, b, c)
WHERE a = 1 AND b > 5 AND c = 3

a 和 b 用得到，但 c 用不到。因為 b 是範圍，符合 b > 5 的那一堆列裡面，c 的順序是亂的（c 只在 b 相同時才有序）。

這是實務上最常遇到的狀況。索引在那裡，但資料庫就是不用。以下是五種最常見的原因，全部都有同一個本質：索引是對「欄位原值」排序的，只要你動了欄位，順序就失效了。

1. 用函數包住了索引欄位

-- ❌ 索引失效
WHERE DATE(created_at) = '2026-08-03'

-- ✅ 改寫成範圍
WHERE created_at >= '2026-08-03'
  AND created_at <  '2026-08-04'

索引是按 created_at 原值排的，不是按 DATE(created_at) 排的。資料庫沒辦法在有序結構裡找一個「被加工過的值」，只能每一列都算一次。

2. 隱式型別轉換（最陰險的一個）

-- phone 欄位是 VARCHAR
-- ❌ 傳了數字
WHERE phone = 13800138000

-- ✅ 傳字串
WHERE phone = '13800138000'

型別不一致時 MySQL 會把欄位轉成數字再比較，等於對每一列都套了一個函數——退化成第 1 種情況。SQL 完全合法、結果也正確，只是慢了幾百倍。

3. 前綴模糊查詢

WHERE name LIKE 'abc%'   -- ✅ 用得到（知道從哪開始找）
WHERE name LIKE '%abc'   -- ❌ 用不到
WHERE name LIKE '%abc%'  -- ❌ 用不到

索引是按開頭排序的。不知道開頭是什麼，就沒有起點可以定位。真的需要全文檢索，該用的是 Elasticsearch 或全文索引，不是 B+Tree。

4. OR 連接了沒有索引的欄位

-- email 有索引，nickname 沒有
WHERE email = ? OR nickname = ?   -- ❌ 整條失效

因為 nickname 那一半無論如何都要全表掃描，掃都掃了，優化器乾脆整條走全表。

5. 選擇性太低（這個不是錯，是正確決策）

WHERE gender = 'M'   -- 全表 50% 都符合

命中一半的列，代表要回表幾百萬次。優化器算過之後認為全表掃描（順序 I/O）比回表幾百萬次（隨機 I/O）更快，於是放棄索引。這是對的。

前面都在講索引怎麼讓查詢變快。這一張卡講反面——因為「再加個索引試試」是實務上最常見的誤判。

每個索引都是一棵獨立的 B+Tree

它要佔空間、要維護、要跟著資料變動而更新。一張大表上的一個索引，實際大小可能是幾百 MB 到幾 GB。

代價一：寫入放大

INSERT INTO users VALUES (...);

實際發生的事：
  ① 主鍵樹（聚簇索引）寫入一次
  ② email 索引寫入一次
  ③ phone 索引寫入一次
  ④ created_at 索引寫入一次
  → 一次 INSERT ＝ 四次 B+Tree 寫入

UPDATE 更糟：改到哪個欄位，那個欄位的索引就要刪掉舊值、插入新值（因為位置變了）。

代價二：頁分裂

只要插入位置不是遞增的，就有機會撐爆某一頁而觸發分裂——搬資料、改指標，而且分裂後兩頁都只有一半滿，空間利用率直接掉到 50%。

什麼時候不該加

• 選擇性低的欄位：性別、狀態旗標、是否刪除——重複值太多，加了也不會被用。
• 寫多讀少的表：log 表、事件表，每秒幾千筆寫入，索引是純負擔。
• 已經被前綴涵蓋：已經有 (a, b, c) 就不需要再建 (a) 和 (a, b)——最左前綴已經涵蓋了。這是很常見的重複索引。
• 整張表很小：幾百列的設定表，全表掃描可能只要 1 次 I/O，比走索引還快。

還有一個常被忘記的選項

有時候正確答案不是加索引，而是改寫查詢（避開失效條件）、改資料模型（拆表、加冗餘欄位），或接受它就是慢（後台報表跑 3 秒沒關係）。索引是工具之一，不是唯一解。