数据库索引

为查询模式加对的索引，移不用或害写性能的索引。

为什么

无查询列索引的数据库做全表扫描——读每行找匹配。1000 万行表是 1000 万读而非 10，查询从 2ms 到 2 秒。对索引把扫描变 seek，I/O 降数量级。但索引非免费——每个索引给每个 INSERT、UPDATE、DELETE 加开销（索引也须更新），太多索引慢写浪费盘。索引是平衡：为慢且频繁的查询加索引，移从不用的索引，多列 WHERE 用复合索引。它是最高杠杆数据库优化，且最常被漏。

何时使用

用查询分析（EXPLAIN ANALYZE、慢查询日志）识别慢查询后。查询扫描多于返回行时加索引。每季审计移不用索引。

如何做

1. 找慢查询
   - 启慢查询日志（PostgreSQL: log_min_duration_statement = 100ms）
   - 或用 pg_stat_statements 看最耗时查询
   - 找做 Seq Scan（全表扫描）而非 Index Scan 的查询

2. 用 EXPLAIN (ANALYZE, BUFFERS) 分析每个慢查询
   - EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
   - 找 "Seq Scan"——意味全表扫描，无索引用
   - 看 "Rows Removed by Filter"——高数意味扫描读多无关行
   - cost 和 actual time 告诉多慢

3. 为查询模式创建对索引
   - 单列: CREATE INDEX idx_orders_user_id ON orders(user_id);
   - 复合（多列 WHERE）: CREATE INDEX idx_orders_user_status ON orders(user_id, status);
   - 列顺序要紧: 等值列先，范围列后
   - 部分索引（若大多查询滤条件）: CREATE INDEX ... WHERE status = 'active';

4. 验证索引被用
   - 重跑 EXPLAIN ANALYZE——应现显 "Index Scan" 替代 "Seq Scan"
   - cost 和 actual time 应大降
   - 若仍显 Seq Scan，规划器选不用（列序错或表太小）

5. 审计和移不用索引
   - 查 pg_stat_user_indexes 找上次重置后 0 扫描的索引
   - 每个不用索引加写开销无益——删它
   - 小心: 有些索引仅被罕见查询（月报）用——查完整周期

示例

-- 找慢查询（PostgreSQL）
-- pg_stat_statements: 按总时间前 10
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 分析慢查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending' ORDER BY created_at DESC;
-- 索引前输出:
--   Seq Scan on orders  (cost=0.00..45231.00 rows=15 actual time=234.567..234.567)
--   Rows Removed by Filter: 999985  ← 扫 100 万行找 15！

-- 创建复合索引（等值列先，排序列后）
CREATE INDEX idx_orders_user_status_created
  ON orders(user_id, status, created_at DESC);

-- 重新分析
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending' ORDER BY created_at DESC;
-- 索引后输出:
--   Index Scan using idx_orders_user_status_created  (cost=0.42..12.50 rows=15 actual time=0.045..0.089)
--   234ms → 0.089ms——快 2600 倍

-- 审计不用索引（完整业务周期后跑）
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- 统计重置后从未用
-- DROP INDEX idx_never_used;

常见问题

⚠在 user_id 建单列索引和 status 建另一个，而非 (user_id, status) 复合索引——规划器只能用一个，查询仍扫多行按另一列滤。
⚠复合索引把范围列放前（created_at, user_id）而非等值列先（user_id, created_at）——索引不能用于等值查找，规划器退回扫描。
⚠每列加索引"以防万一"——每索引慢每次写浪费盘，大多从不被任何查询用，故写性能无益退化。
⚠建索引但不跑 ANALYZE（或 autovacuum 太慢）——规划器统计陈旧，不知索引存在或选择性，仍选扫描。

最佳实践

✓每个慢查询用 EXPLAIN (ANALYZE, BUFFERS) 理解实际计划——不看计划猜索引导致规划器不用的索引。
✓复合索引列按等值先、范围后、排序最后——这跟随访问模式让一个索引服务 WHERE、范围和 ORDER BY。
✓总滤条件的查询用部分索引（WHERE status = 'active'）——索引更小、更快、维护更便宜。
✓每季用 pg_stat_user_indexes 审计不用索引删零扫描的——每个不用索引给每次写无益加税。