索引原理與策略

索引是提升查詢效能最重要的工具。本篇將深入解析 B-Tree 索引的原理，並教你如何制定正確的索引策略。

---

一、 為什麼需要索引？

沒有索引時，資料庫只能「全表掃描 (Sequential Scan)」——從頭到尾逐列檢查。

-- 假設 users 表有 100 萬筆資料
SELECT * FROM users WHERE email = 'alice@example.com';
-- 無索引：掃描 100 萬筆，O(N)
-- 有索引：直接定位，O(log N)

資料量	全表掃描	B-Tree 索引
1,000	1,000 次	~10 次
1,000,000	1,000,000 次	~20 次
1,000,000,000	10 億次	~30 次

---

二、 B-Tree 索引原理

PostgreSQL 預設的索引類型是 B-Tree（平衡樹）。

結構示意

特點：

• 所有葉節點在同一層（平衡）
• 每個節點包含多個鍵值（減少磁碟 I/O）
• 支援等值查詢和範圍查詢

建立索引

-- 基本語法
CREATE INDEX idx_users_email ON users(email);

-- 查看現有索引
\di                    -- psql 內
SELECT * FROM pg_indexes WHERE tablename = 'users';

使用情境

B-Tree 適合：

• 等值查詢：WHERE email = 'xxx'
• 範圍查詢：WHERE age > 18 AND age < 65
• 排序：ORDER BY created_at DESC
• 前綴匹配：WHERE name LIKE 'Alice%'

B-Tree 不適合：

• 後綴匹配：WHERE name LIKE '%alice'
• 全文搜尋：複雜的文本搜尋
• 地理空間查詢

---

三、 何時該建索引？

應該建索引

1. WHERE 條件常用的欄位
2. JOIN 的連接欄位
3. ORDER BY 的排序欄位
4. 高基數欄位（唯一值多，如 email、user_id）

不需要建索引

1. 小表（<1000 筆，全表掃描更快）
2. 低基數欄位（如 gender 只有 2 個值）
3. 頻繁更新的欄位（索引維護成本高）
4. 很少查詢的欄位

檢查索引是否被使用

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

-- 看到 Index Scan 或 Index Only Scan → 使用了索引
-- 看到 Seq Scan → 沒有使用索引

---

四、 複合索引

複合索引包含多個欄位。

欄位順序很重要

CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

這個索引可以加速：

-- ✅ 使用 customer_id（最左欄位）
SELECT * FROM orders WHERE customer_id = 123;

-- ✅ 使用 customer_id AND created_at
SELECT * FROM orders WHERE customer_id = 123 AND created_at > '2024-01-01';

-- ✅ 使用 customer_id 排序 created_at
SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at;

-- ❌ 只使用 created_at（不是最左欄位）
SELECT * FROM orders WHERE created_at > '2024-01-01';

> **最左前綴原則**

複合索引只能從最左邊的欄位開始匹配。(A, B, C) 索引可以加速 A、A+B、A+B+C，但無法加速單獨的 B 或 C。

設計原則

1. 高選擇性欄位放前面（過濾效果好）
2. 等值條件放前面，範圍條件放後面
3. 考慮查詢頻率

-- 如果常用這個查詢
SELECT * FROM orders WHERE customer_id = ? AND status = ? AND created_at > ?;

-- 索引應該是
CREATE INDEX idx ON orders(customer_id, status, created_at);
-- customer_id 和 status 是等值，created_at 是範圍

---

五、 部分索引 (Partial Index)

只對符合條件的列建立索引：

-- 只為活躍用戶建立索引
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- 只為未處理的訂單建立索引
CREATE INDEX idx_pending_orders ON orders(created_at) WHERE status = 'pending';

優點：

• 索引更小，佔用空間少
• 維護成本低
• 查詢時能更快鎖定

查詢條件必須匹配：

-- ✅ 會使用 idx_active_users
SELECT * FROM users WHERE email = 'xxx' AND is_active = true;

-- ❌ 不會使用（缺少 is_active = true）
SELECT * FROM users WHERE email = 'xxx';

---

六、 表達式索引 (Expression Index)

對表達式結果建立索引：

-- 查詢常用 LOWER(email)
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 查詢常用年份
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));

-- 查詢常用 JSONB 欄位
CREATE INDEX idx_products_category ON products((attributes->>'category'));

使用時表達式必須完全匹配：

-- ✅ 會使用索引
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- ❌ 不會使用（表達式不同）
SELECT * FROM users WHERE email = 'alice@example.com';

---

七、 唯一索引

-- 隱式建立：PRIMARY KEY 和 UNIQUE 約束會自動建立唯一索引
CREATE TABLE users (
    id SERIAL PRIMARY KEY,    -- 自動建立唯一索引
    email TEXT UNIQUE         -- 自動建立唯一索引
);

-- 顯式建立
CREATE UNIQUE INDEX idx_unique_email ON users(email);

部分唯一索引：

-- 只有活躍用戶的 email 必須唯一
CREATE UNIQUE INDEX idx_unique_active_email ON users(email) WHERE is_active = true;

---

八、 包含索引 (Covering Index)

PostgreSQL 11+ 支援 INCLUDE，將額外欄位存入索引：

CREATE INDEX idx_orders_customer ON orders(customer_id) INCLUDE (total, created_at);

好處：

-- 這個查詢可以「索引唯獨掃描」，完全不需要回表
SELECT total, created_at FROM orders WHERE customer_id = 123;
-- 因為所需欄位都在索引中

注意：

• INCLUDE 欄位不能用於過濾或排序
• 會增加索引大小

---

九、 索引維護

查看索引大小

SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'orders';

查看索引使用率

SELECT
    relname AS table,
    indexrelname AS index,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

刪除未使用的索引

-- 查找從未使用的索引
SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;

-- 刪除索引
DROP INDEX idx_never_used;

重建索引

索引會隨時間碎片化，定期重建可以提升效能：

-- 重建單一索引（會鎖表）
REINDEX INDEX idx_users_email;

-- 並行重建（不鎖表，PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY idx_users_email;

-- 重建整張表的所有索引
REINDEX TABLE users;

---

十、 索引與寫入效能

每個索引都會增加寫入開銷：

操作	影響
INSERT	每個索引都要新增條目
UPDATE	索引欄位變更時要更新索引
DELETE	索引條目需要標記刪除

> **不要過度建索引**

一張表上太多索引會嚴重影響寫入效能。只建立真正需要的索引。

---

十一、 實用技巧

1. 使用 EXPLAIN 驗證

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

2. 強制不使用索引（測試用）

SET enable_indexscan = off;
SET enable_bitmapscan = off;

3. 建立索引時不鎖表

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

> **CONCURRENTLY 較慢但不鎖表**

生產環境建議使用 CONCURRENTLY，避免阻塞讀寫操作。

---

總結

索引類型	用途
B-Tree	等值、範圍、排序（預設）
複合索引	多欄位查詢（注意順序）
部分索引	只索引部分資料
表達式索引	索引計算結果
包含索引	避免回表查詢

建索引原則：

1. 根據查詢模式設計
2. 高選擇性欄位優先
3. 用 EXPLAIN 驗證效果
4. 定期清理未使用的索引

---

進階挑戰

1. 設計一個複合索引，最佳化 WHERE status = ? AND created_at > ? ORDER BY priority 查詢
2. 使用 pg_stat_user_indexes 找出專案中從未使用的索引，並分析原因
3. 比較有無 INCLUDE 欄位時的 EXPLAIN 輸出差異

---

延伸閱讀與資源

• PostgreSQL 索引官方文件
• 索引類型總覽
• CREATE INDEX 語法

---

← 上一章：視窗函數實戰 | 返回專題首頁 | 下一章：進階索引類型 →