結論先講
90% 的慢查詢都是索引問題，但不是「加索引就好」這麼簡單。 加錯索引比沒索引更糟——浪費空間、拖慢寫入，查詢還是慢。這篇教你用 EXPLAIN 看懂資料庫到底在幹嘛，然後再決定該怎麼處理。
優化的第一步永遠是「量測」，不是「猜」。EXPLAIN 就是你的量測工具。
EXPLAIN 到底怎麼看
PostgreSQL 的 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';輸出可能長這樣：
Seq Scan on orders (cost=0.00..1542.00 rows=15 width=120)
Filter: ((user_id = 42) AND (status = 'paid'::text))
重點數字：
| 欄位 | 意義 |
|---|---|
cost=0.00..1542.00 | 啟動成本..總成本（相對單位，不是毫秒） |
rows=15 | 預估回傳列數 |
width=120 | 每列預估大小（bytes） |
Seq Scan | 全表掃描（通常不好） |
EXPLAIN ANALYZE 才是真的
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';Seq Scan on orders (cost=0.00..1542.00 rows=15 width=120)
(actual time=0.025..12.345 rows=18 loops=1)
Filter: ((user_id = 42) AND (status = 'paid'::text))
Rows Removed by Filter: 49982
Buffers: shared hit=542
Planning Time: 0.102 ms
Execution Time: 12.401 ms
多了 actual time 和 Rows Removed by Filter。注意 rows=15（預估）vs rows=18（實際）——如果差很多，代表統計資訊過時了，跑一下 ANALYZE orders;。
MySQL 的 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';| id | select_type | table | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ALL | NULL | NULL | 50000 | Using where |
type=ALL 就是全表掃描。MySQL 8.0+ 可以用 EXPLAIN ANALYZE 拿到實際執行時間。
PostgreSQL vs MySQL EXPLAIN 比較：
| 面向 | PostgreSQL | MySQL |
|---|---|---|
| 預設格式 | 樹狀文字 | 表格 |
| 實際執行時間 | EXPLAIN ANALYZE | MySQL 8.0+ EXPLAIN ANALYZE |
| Buffer 資訊 | BUFFERS 選項 | 需要 Performance Schema |
| 視覺化工具 | explain.depesz.com | MySQL Workbench |
Scan 類型：哪種代表有問題
Seq Scan（Sequential Scan / 全表掃描）
整張表從頭掃到尾。在小表（< 幾千筆）其實很快，Query Planner 可能故意選它。但大表上出現 Seq Scan 通常代表缺索引。
Index Scan
用索引找到資料位置，然後去表裡取完整資料列。最常見的好結果。
Index Only Scan
連表都不用去，索引本身就包含所有需要的欄位。最快。
-- 建一個覆蓋索引
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- 這個查詢就可能走 Index Only Scan
SELECT user_id, status FROM orders WHERE user_id = 42;Bitmap Index Scan + Bitmap Heap Scan
當符合條件的資料列「有點多但又不是太多」時出現。先用索引建一個 bitmap，再批次去表裡取。通常在回傳 5%~20% 資料時使用。
Bitmap Heap Scan on orders
Recheck Cond: (status = 'paid')
-> Bitmap Index Scan on idx_orders_status
Index Cond: (status = 'paid')
索引類型：不是只有 B-tree
| 索引類型 | 適用場景 | 範例 |
|---|---|---|
| B-tree | 等值、範圍查詢（預設） | WHERE price > 100 |
| Hash | 純等值查詢（PostgreSQL 10+ 才穩定） | WHERE email = 'x@y.com' |
| GIN | 全文搜尋、JSONB、陣列 | WHERE tags @> '{tech}' |
| GiST | 地理資料、範圍型別 | WHERE location <-> point(25.0, 121.5) < 1000 |
-- JSONB 欄位用 GIN
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
-- 全文搜尋用 GIN
CREATE INDEX idx_posts_search ON posts USING GIN (to_tsvector('chinese', content));
-- 地理位置用 GiST
CREATE INDEX idx_stores_location ON stores USING GiST (location);常見錯誤： 對低基數欄位（如 status 只有 3 種值）建 B-tree 索引。Query Planner 可能直接無視它，因為掃描索引再回表不一定比 Seq Scan 快。
四大慢查詢元兇
1. 缺索引
最常見。EXPLAIN 看到 Seq Scan + Rows Removed by Filter 數字很大，就是它。
2. 用錯索引
有索引但 Query Planner 不用，常見原因：
-- 對索引欄位做函數運算 → 索引失效
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 解法：建函數索引
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- 隱式型別轉換 → 索引失效
SELECT * FROM orders WHERE order_no = 12345; -- order_no 是 VARCHAR
-- 解法：用正確的型別
SELECT * FROM orders WHERE order_no = '12345';3. N+1 問題
這是 ORM 世界裡的頭號殺手。
# 假設用 SQLAlchemy
# ❌ N+1：1 次查訂單 + N 次查用戶
orders = session.query(Order).limit(100).all()
for order in orders:
print(order.user.name) # 每次都發一個 SELECT 給 users 表
# ✅ Eager Loading：只要 1-2 次查詢
from sqlalchemy.orm import joinedload
orders = session.query(Order).options(joinedload(Order.user)).limit(100).all()
for order in orders:
print(order.user.name) # 已經載入了，不會再查怎麼偵測 N+1？
- PostgreSQL：開
log_min_duration_statement = 0，看短時間內大量相似查詢 - Django：用
django-debug-toolbar或nplusone套件 - Node.js（Prisma）：用
prisma.$on('query')記錄查詢
4. 不必要的 JOIN
-- ❌ 你只需要訂單資料，但 JOIN 了 5 張表
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN brands b ON p.brand_id = b.id
WHERE o.status = 'paid';
-- ✅ 只 JOIN 你真正需要的
SELECT o.* FROM orders o WHERE o.status = 'paid';複合索引：順序很重要
CREATE INDEX idx_orders_user_status ON orders (user_id, status);這個索引能加速：
WHERE user_id = 42（用到第一個欄位）WHERE user_id = 42 AND status = 'paid'（兩個都用到）
但 不能 加速：
WHERE status = 'paid'（跳過第一個欄位）
原則：高選擇性的欄位放前面，等值條件放範圍條件前面。
-- ✅ 等值在前，範圍在後
CREATE INDEX idx_orders_composite ON orders (status, created_at);
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2026-01-01';
-- ❌ 範圍在前，後面的欄位無法用索引
CREATE INDEX idx_orders_bad ON orders (created_at, status);Partial Index：只索引你需要的資料
-- 只索引「待處理」的訂單（可能只佔 5% 資料）
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- 查詢要包含相同的 WHERE 條件才能用到
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-03-01';索引大小可能只有完整索引的 5%，掃描速度也快很多。
實戰案例：電商商品列表慢查詢
原始查詢（3.2 秒）：
SELECT p.*, c.name as category_name, b.name as brand_name,
(SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id) as review_count
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
WHERE p.is_active = true
AND p.price BETWEEN 100 AND 5000
AND c.slug = 'electronics'
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;問題拆解：
- 相關子查詢（correlated subquery）算
review_count→ 每列都跑一次 is_active是布林值，索引效益低ORDER BY created_at DESC+LIMIT但沒有合適的索引OFFSET在分頁深處會很慢
優化後（18ms）：
-- 1. 建複合索引 + Partial Index
CREATE INDEX idx_products_active_cat_price ON products (category_id, price)
WHERE is_active = true;
CREATE INDEX idx_products_active_created ON products (created_at DESC)
WHERE is_active = true;
CREATE INDEX idx_reviews_product ON reviews (product_id);
-- 2. 改用 LEFT JOIN 取代相關子查詢
SELECT p.*, c.name as category_name, b.name as brand_name,
COALESCE(r.review_count, 0) as review_count
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
LEFT JOIN (
SELECT product_id, COUNT(*) as review_count
FROM reviews GROUP BY product_id
) r ON r.product_id = p.id
WHERE p.is_active = true
AND p.price BETWEEN 100 AND 5000
AND p.category_id = (SELECT id FROM categories WHERE slug = 'electronics')
ORDER BY p.created_at DESC
LIMIT 20;
-- 3. 深分頁改用 cursor-based pagination
-- WHERE p.created_at < '上一頁最後一筆的 created_at'工具推薦
| 工具 | 用途 |
|---|---|
pg_stat_statements | 統計所有查詢的執行時間和次數 |
auto_explain | 自動記錄慢查詢的 EXPLAIN |
| explain.depesz.com | 視覺化 PostgreSQL EXPLAIN |
| explain.dalibo.com | 另一個好用的視覺化工具 |
EXPLAIN (FORMAT JSON) | 給程式解析用的格式 |
-- 啟用 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 找出最慢的 10 個查詢
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;FAQ
Q1: EXPLAIN 的 cost 數字是毫秒嗎？
不是。cost 是 Query Planner 的相對單位，用來比較不同執行計畫的優劣。要看實際時間，用 EXPLAIN ANALYZE。但注意 EXPLAIN ANALYZE 會真的執行查詢，對寫入操作要小心（用 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;）。
Q2: 索引越多越好嗎？
不是。每個索引都會：（1）佔用磁碟空間、（2）拖慢 INSERT/UPDATE/DELETE 速度、（3）增加 VACUUM 負擔。只建查詢真正需要的索引，定期用 pg_stat_user_indexes 檢查哪些索引根本沒被用到。
Q3: 為什麼我加了索引 Query Planner 還是不用？
常見原因：表太小（Seq Scan 更快）、統計資訊過時（跑 ANALYZE）、查詢回傳太多資料（超過 ~20% 表資料）、索引欄位被函數包住、隱式型別轉換。
Q4: N+1 問題在 NoSQL 也會發生嗎？
會。例如 MongoDB 裡先查一批文件，再逐一用 _id 去查關聯集合，一樣是 N+1。解法類似：用 $lookup（類似 JOIN）或在應用層做批次查詢。
Q5: OFFSET 分頁到底慢在哪？
OFFSET 10000 代表資料庫要先找到 10000 筆資料然後丟掉，再回傳接下來的資料。越深的頁數越慢。解法是用 cursor-based pagination（keyset pagination），用上一頁最後一筆的排序欄位值當起點。