為什麼後端工程師要會這個
ORM 把 SQL 藏得很深，寫 Product.findAll({ where: { status: 'active' } }) 不用懂 index 也能跑。但查詢從 10ms 變 3s，通常不是 code 寫錯，是 index 沒加——或加錯了。
這不是 DBA 專屬的問題。後端工程師在寫 migration 時就要決定：
- 這張 table 會怎麼被查？
- 哪些欄位需要 index？
- 複合 index 欄位順序對嗎？
等到上線、資料量大了再跑 EXPLAIN，改 index 要 lock table（或用 CREATE INDEX CONCURRENTLY），成本高很多。
新建 Table 的 Index 規劃流程
Step 1：先問「這張 table 會被怎麼查」
在寫 CREATE TABLE 之前，列出這張 table 預計會有的查詢模式：
orders table：
1. 查某個用戶的所有訂單（WHERE user_id = ?）
2. 查某段時間內的訂單（WHERE created_at BETWEEN ? AND ?）
3. 查某個用戶最近 X 天的訂單（WHERE user_id = ? AND created_at > ?）
4. 後台查特定狀態的訂單（WHERE status = 'pending' ORDER BY created_at）
5. 訂單詳情頁（WHERE id = ?）← PK 自動處理
先有查詢清單，才能規劃 index，不是反過來。
Step 2：自動有 Index 的欄位（不用自己加）
| 情況 | Index 類型 | 說明 |
|---|---|---|
PRIMARY KEY | unique btree | 自動建立，不用寫 |
REFERENCES foreign key（PostgreSQL） | 無！ | PostgreSQL 不自動加，要自己加 |
UNIQUE constraint | unique btree | 自動建立 |
PostgreSQL 的 FK 沒有自動 index 是最常踩的坑。REFERENCES users(id) 只保證 constraint，不建 index。如果你常用 WHERE user_id = ? 查子表，這個欄位一定要手動加 index。
-- ❌ 只有 FK constraint，沒有 index
user_id UUID REFERENCES users(id) ON DELETE CASCADE
-- ✅ 加上 index
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
CREATE INDEX idx_orders_user_id ON orders(user_id);Step 3：單欄位 Index
規則很簡單：WHERE 條件常出現的欄位、ORDER BY 常用的欄位，優先考慮加 index。
-- orders table 的單欄位 index
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);但不要無腦加——每個 index 都有寫入成本。INSERT / UPDATE / DELETE 時每個 index 都要維護。
Step 4：複合 Index（最重要也最容易搞錯）
欄位順序決定一切。
規則：選擇性高的欄位（cardinality 高）在前，等值條件（=）在前，範圍條件（>、<、BETWEEN）在後。
-- 場景：WHERE user_id = ? AND created_at > ?
-- ✅ 正確順序：等值 user_id 在前，範圍 created_at 在後
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
-- ❌ 反過來：created_at 在前，這個 index 對 WHERE user_id 無效
CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);複合 index 的最左前綴原則：
(user_id, status, created_at) 這個 index 可以服務：
WHERE user_id = ?✅WHERE user_id = ? AND status = ?✅WHERE user_id = ? AND status = ? AND created_at > ?✅WHERE status = ?❌（跳過 user_id，index 無效）WHERE user_id = ? AND created_at > ?✅ 但只用到 user_id 部分，created_at 需要額外過濾
-- 這一個 index 覆蓋多個查詢場景
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);Step 5：Covering Index（覆蓋 index）
如果查詢只需要 index 裡的欄位（不需要回到主表取其他欄位），就是 covering index，可以省掉一次 heap fetch，效能差距在大表上很明顯。
-- 查詢：SELECT id, status, created_at FROM orders WHERE user_id = ?
-- 如果 index 包含 id、status、created_at，就不需要回表
-- PostgreSQL: 用 INCLUDE 加入非搜尋欄位
CREATE INDEX idx_orders_user_covering
ON orders(user_id)
INCLUDE (status, created_at);不是每個 index 都要做 covering，增加 index 大小。當某個查詢是熱點路徑時才考慮。
Step 6：Partial Index（局部 index）
只對部分資料建 index，index 更小、更快、更省空間：
-- 只 index 未完成的訂單（通常遠少於總訂單數）
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- 後台查詢：SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at
-- 這個 index 比全表 status index 小很多，且 PostgreSQL 可以直接用軟刪除的 partial index：
-- 沒有軟刪除過濾的 index 會掃到所有已刪除資料
CREATE INDEX idx_products_name ON products(name) WHERE deleted_at IS NULL;
-- unique constraint 也要配合
CREATE UNIQUE INDEX idx_products_sku_active
ON products(sku) WHERE deleted_at IS NULL;實際案例：電商 orders table
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
amount INTEGER NOT NULL,
currency VARCHAR(3) NOT NULL DEFAULT 'TWD',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
-- FK：user_id 沒有自動 index，手動加
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 後台：查 pending 訂單，按時間排序
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- 用戶訂單列表：按用戶 + 時間
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
-- 軟刪除：只 index 未刪除的資料
-- （如果 deleted_at IS NULL 是 WHERE 條件的話）
CREATE INDEX idx_orders_user_id_active
ON orders(user_id)
WHERE deleted_at IS NULL;什麼時候不加 Index
Index 有成本：寫入（INSERT / UPDATE / DELETE）都要維護所有 index，index 越多，寫入越慢。
不加或延後加的情況：
| 情況 | 原因 |
|---|---|
| 資料量小（< 10 萬筆） | Seq scan 通常比 index scan 快，optimizer 會自己選 |
| 很少被查的欄位 | 維護成本 > 收益 |
| 高寫入比例的 table（log、事件流） | 每次寫入都要維護 index，成本高 |
| 重複值極多（true/false 的布林欄位） | Cardinality 低，index 幾乎沒用 |
-- ❌ is_deleted BOOLEAN 做 index 沒意義
-- 只有兩個值，PostgreSQL 通常選 seq scan
CREATE INDEX idx_orders_is_deleted ON orders(is_deleted);
-- ✅ 改用 partial index
CREATE INDEX idx_orders_not_deleted ON orders(id) WHERE is_deleted = false;Migration 時的注意事項
生產環境加 index 要用 CONCURRENTLY，否則會 lock table：
-- ❌ 鎖表，生產環境不能跑（除非能停服）
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- ✅ 不鎖表，build 時間更長但安全
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);CONCURRENTLY 的限制：
- 不能在 transaction 裡跑（
BEGIN/COMMIT包著會報錯） - Build 時間是一般的 2–3 倍
- 如果 build 中途失敗，會留下
INVALID狀態的 index，要手動DROP INDEX再重試
驗證 Index 有沒有被用到
加完 index 後，用 EXPLAIN 確認：
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 'xxx' ORDER BY created_at DESC LIMIT 20;看輸出：
Index Scan using idx_orders_user_created→ index 被用了 ✅Seq Scan on orders→ index 沒被用（可能欄位順序錯或資料量太小）
如果查詢計畫不符預期，用 SET enable_seqscan = off 強制走 index，確認 index 存在且欄位正確。