索引與查詢優化完全指南

目錄

• 什麼是索引？
• 為什麼需要索引？
• 索引的資料結構
• 索引類型
• 複合索引與最左前綴
• 覆蓋索引
• 索引失效的常見情境
• EXPLAIN 查詢計畫
• 索引的代價
• 各資料庫差異
• 最佳實踐
• 常見問題
• 總結

什麼是索引？

索引（Index） 是資料庫為加速查詢而建立的額外資料結構，類似書本末尾的索引頁：不必逐頁翻找，直接從索引定位到目標位置。

沒有索引：WHERE email = 'a@x.com' → 逐列檢查整張表（全表掃描）
有索引：  從索引結構快速定位 → 直接找到該列

核心特點

• 加速查詢：把 O(n) 全表掃描變成 O(log n) 的查找
• 獨立結構：索引是與資料表分開儲存的物件
• 有代價：佔額外空間，且寫入時要同步維護索引
• 不改變結果：索引只影響速度，不影響查詢回傳的資料

為什麼需要索引？

全表掃描的問題

沒有索引時，WHERE 條件查詢得逐列比對：

SELECT * FROM users WHERE email = 'a@x.com';
-- 100 萬列 → 最壞情況檢查 100 萬次

資料量越大，查詢越慢，且耗用大量 I/O。

索引的效果

索引把「線性掃描」變成「對數查找」，差距隨資料量擴大而急遽放大。

索引也用於排序與關聯

除了 WHERE，索引還能加速：

• ORDER BY：索引本身有序，可省去排序步驟
• JOIN：關聯欄位有索引時，比對更快
• GROUP BY、DISTINCT、MIN/MAX

索引的資料結構

B-Tree（最常見，預設）

平衡樹結構，葉節點有序串接。適合等值查詢與範圍查詢：

WHERE age = 30          -- 等值
WHERE age > 30          -- 範圍
WHERE name LIKE 'A%'    -- 前綴（可用）
ORDER BY age            -- 排序

絕大多數索引都是 B-Tree（嚴格說是 B+Tree）。

Hash 索引

用雜湊表，只支援等值查詢（=），不支援範圍與排序。

WHERE id = 5            -- ✅ 適用
WHERE id > 5            -- ❌ 不適用

• PostgreSQL 有 Hash 索引；MySQL InnoDB 只在記憶體中自動建（Adaptive Hash Index），無法手動指定
• 一般用 B-Tree 即可，Hash 索引用途有限

其他特殊索引（PostgreSQL 較豐富）

索引類型

主鍵索引（Primary Key）

主鍵自動建立唯一索引。在 InnoDB 中，主鍵還是叢集索引（資料按主鍵實體排列，見後文）。

唯一索引（Unique）

保證欄位值不重複，同時可加速查詢：

CREATE UNIQUE INDEX idx_users_email ON users(email);

一般（次要）索引

最常見，加速非唯一欄位的查詢：

CREATE INDEX idx_orders_user_id ON orders(user_id);

複合索引（Composite / Multi-column）

涵蓋多個欄位（見下節最左前綴）：

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

部分索引（Partial Index，PostgreSQL）

只對符合條件的列建索引，節省空間：

CREATE INDEX idx_active_users ON users(email) WHERE active = true;

表達式索引（Functional Index）

對運算結果建索引：

CREATE INDEX idx_lower_email ON users(LOWER(email));
-- 之後 WHERE LOWER(email) = '...' 才能用到

複合索引與最左前綴

複合索引 (a, b, c) 的順序非常重要，遵循最左前綴原則（Leftmost Prefix）：查詢條件必須從最左欄位開始連續使用，索引才有效。

CREATE INDEX idx ON t(a, b, c);

範圍查詢會中斷後續欄位

WHERE a = 1 AND b > 10 AND c = 5
-- a 等值 ✅、b 範圍 ✅，但 c 因 b 是範圍而用不到索引排序

欄位順序的設計原則

• 把等值查詢的欄位放前面，範圍查詢的放後面
• 把選擇性高（不同值多、過濾力強）的欄位放前面

覆蓋索引

覆蓋索引（Covering Index）：查詢需要的所有欄位都在索引中，資料庫直接從索引取值，不必回表讀取實際資料列。

CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);

-- 這個查詢只需要 user_id 和 amount，索引就涵蓋了
SELECT amount FROM orders WHERE user_id = 1;   -- 不必回表，很快

• 「回表」：先用索引找到主鍵 / 位置，再去資料表撈完整列，多一次 I/O
• 覆蓋索引省去回表，是常見的優化手段
• PostgreSQL 可用 INCLUDE 把額外欄位「掛」進索引而不參與排序：

CREATE INDEX idx ON orders(user_id) INCLUDE (amount);

索引失效的常見情境

即使建了索引，以下寫法常導致索引用不上、退回全表掃描：

1. 對索引欄位做運算 / 函式

WHERE YEAR(created_at) = 2026          -- ❌ 失效
WHERE created_at >= '2026-01-01'       -- ✅ 改成範圍

2. 開頭模糊匹配

WHERE name LIKE '%abc'                 -- ❌ 前置萬用字元，失效
WHERE name LIKE 'abc%'                 -- ✅ 前綴匹配可用

3. 隱含型別轉換

WHERE phone = 12345678                 -- phone 是字串 → 隱含轉型 → 可能失效
WHERE phone = '12345678'               -- ✅

4. 違反最左前綴

-- 索引 (a, b)，卻只查 b
WHERE b = 2                            -- ❌

5. 低選擇性欄位

性別、布林這類只有少數值的欄位，索引過濾效果差，優化器可能直接選擇全表掃描（不一定算「失效」，而是不划算）。

6. OR 連接非索引欄位

WHERE indexed_col = 1 OR non_indexed = 2   -- 整體可能退回全表掃描

EXPLAIN 查詢計畫

EXPLAIN 顯示資料庫打算怎麼執行查詢，是判斷索引有沒有生效的核心工具。

EXPLAIN SELECT * FROM users WHERE email = 'a@x.com';

-- 實際執行並回報真實耗時
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@x.com';

重點觀察項

PostgreSQL：

• Seq Scan（全表掃描）→ 通常代表沒用到索引
• Index Scan / Index Only Scan（覆蓋索引）→ 用到索引
• rows、cost：預估列數與成本

MySQL（EXPLAIN 的 type 欄位，由好到壞）：

system > const > eq_ref > ref > range > index > ALL
                                                  ↑
                                          ALL = 全表掃描，要警惕

• key：實際用到的索引（NULL 表示沒用索引）
• rows：預估掃描列數
• Extra：Using index（覆蓋索引，好）、Using filesort（額外排序，可能要優化）

索引的代價

索引不是免費的，建太多會反受其害：

1. 寫入變慢

每次 INSERT / UPDATE / DELETE 都要同步維護所有相關索引。索引越多，寫入越慢。

2. 佔用空間

索引是額外儲存，大表的多個索引可能佔用可觀空間。

3. 維護成本

更新索引欄位時要調整索引結構；在 MVCC 下也會產生索引的舊版本，需清理。

原則：索引服務於查詢，不是越多越好。針對實際慢查詢、依 EXPLAIN 結果建立必要索引，而非盲目對每個欄位都建。

各資料庫差異

叢集索引 vs 非叢集索引

• 叢集索引（Clustered）：資料列實體按索引鍵順序儲存，一張表只能有一個
• 非叢集索引（Non-clustered / Secondary）：索引與資料分開，葉節點指向資料位置

InnoDB 次要索引存的是主鍵值，所以主鍵不宜太長（如用長字串當主鍵），否則每個次要索引都跟著變大。

最佳實踐

1. 為查詢條件、JOIN、排序欄位建索引

WHERE、JOIN ON、ORDER BY 常用的欄位是首要候選。

2. 子表外鍵欄位記得建索引

尤其 PostgreSQL 不會自動建，否則父表刪除 / CASCADE 會全表掃描（詳見外鍵與參照完整性）。

3. 複合索引順序：等值在前、範圍在後、高選擇性在前

並善用覆蓋索引避免回表。

4. 用 EXPLAIN 驗證，而非憑感覺

建索引前後都用 EXPLAIN ANALYZE 確認真的有用到、真的變快。

5. 避免讓索引失效的寫法

不要對索引欄位包函式、避免前置萬用字元 LIKE '%x'、注意型別一致。

6. 定期檢視沒用到的索引

無用索引只增加寫入與空間成本，應移除。

-- PostgreSQL：查索引使用次數
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

常見問題

問題 1：建了索引為什麼查詢還是慢？

可能索引失效（函式、前置萬用字元、型別轉換、違反最左前綴），或優化器判斷全表掃描更划算（低選擇性 / 小表）。用 EXPLAIN 看實際計畫。

問題 2：索引是不是越多越好？

不是。索引加速讀、但拖慢寫並佔空間。應針對實際查詢需求建立，定期清掉沒用到的。

問題 3：複合索引 (a, b) 和兩個單欄索引 (a)、(b) 一樣嗎？

不一樣。複合索引能同時用 a、b 過濾並支援 ORDER BY a, b；兩個單欄索引通常只能各自過濾，效果較差（部分 DB 能做索引合併，但不如複合索引）。

問題 4：什麼是回表？怎麼避免？

用次要索引找到主鍵 / 位置後，再去資料表撈完整列，多一次 I/O。用覆蓋索引（查詢欄位都在索引內）可避免回表。

問題 5：主鍵該用什麼？

InnoDB 中主鍵是叢集索引、且被所有次要索引引用，宜用短且遞增的鍵（如自增整數 / 有序 ID）。隨機長字串主鍵會放大索引並造成插入時頁分裂。

總結

核心要點

• 索引用額外結構把全表掃描（O(n)）變成查找（O(log n)），也加速排序與 JOIN
• 預設結構是 B-Tree，支援等值與範圍；Hash 只支援等值
• 複合索引遵循最左前綴：條件要從最左欄位連續使用；等值在前、範圍在後
• 覆蓋索引讓查詢不必回表，是重要優化
• 索引會因函式運算、前置 LIKE %、型別轉換、違反最左前綴而失效
• 用 EXPLAIN / EXPLAIN ANALYZE 驗證計畫，別憑感覺
• 索引有代價（拖慢寫入、佔空間），不是越多越好

快速參考

建立日期：2026-06-17