索引与访问模式（Index & Access Pattern）
参考来源：Markus Winand《Use The Index, Luke!》、PostgreSQL Indexes 官方文档、High Performance MySQL（O'Reilly）、Stripe / GitHub 索引实战。
适用场景
- 新表上线前设计索引
- 根据 API 列表页、筛选、排序、搜索设计访问路径
- 判断复合索引字段顺序
- 评估唯一索引、覆盖索引、局部索引、前缀索引
- 排查索引缺失或过度索引
- 大表索引重建 / 在线建索引
核心原则
1. 索引来自真实访问模式，不来自字段清单
先列查询场景，再决定索引
2. 索引不是越多越好
每加一个索引：
- 写入慢 5%~30%
- 占空间（约表大小 30%~100%）
- 维护成本（重建 / 迁移）
3. 复合索引顺序：等值 → 范围 → 排序
遵循 ESR（Equality, Sort, Range）
4. 覆盖索引省一次表查
把高频查询字段都放进 INCLUDE
5. 部分索引省空间
`WHERE deleted_at IS NULL` 的查询用部分索引
6. 不为低选择性字段单独建索引
性别、布尔、状态值少 → 单独建索引几乎无用
7. 唯一约束 = 唯一索引（自动）
不要重复建
8. 高并发写入慎用 UUID v4 主键
B-tree 写放大严重，用 ULID / UUID v7
索引类型对比
B-tree（默认 / 最常用）
CREATE INDEX idx_orders_user_id ON orders(user_id);
适合：
- 等值（=）
- 范围（<, >, BETWEEN）
- 排序（ORDER BY）
- 前缀匹配（LIKE 'abc%'）
不适合：
- 后缀匹配（LIKE '%abc'）→ 改用 GIN trigram
- 全文搜索 → 用 GIN tsvector
Hash（PostgreSQL 10+）
CREATE INDEX idx_users_email_hash ON users USING hash(email);
适合：
- 仅等值查找
- 大字段（hash 后只占 4~8 字节）
不适合：
- 范围查询
- 排序
GIN（倒排索引，PG 专用）
-- 数组
CREATE INDEX idx_post_tags ON posts USING gin(tags);
SELECT * FROM posts WHERE tags @> ARRAY['rust'];
-- JSON
CREATE INDEX idx_metadata ON orders USING gin(metadata);
SELECT * FROM orders WHERE metadata @> '{"source": "mobile"}';
-- 全文搜索
CREATE INDEX idx_post_search ON posts USING gin(to_tsvector('english', body));
SELECT * FROM posts WHERE to_tsvector('english', body) @@ plainto_tsquery('rust');
适合：
- 数组 / JSON 字段
- 全文搜索
- 多值字段
BRIN（块范围索引，PG）
CREATE INDEX idx_logs_created_at ON logs USING brin(created_at);
适合：
- 时间序列数据（自然有序）
- 超大表（占空间极小）
- 范围扫描
不适合：
- 随机访问
空间索引（GiST）
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
适合：
- 地理位置（PostGIS）
- 范围查询（点在多边形内）
ESR 复合索引顺序原则
原则：Equality → Sort → Range
示例：
SELECT * FROM orders
WHERE tenant_id = ? AND status = ? AND created_at > ?
ORDER BY created_at DESC
LIMIT 20;
错误索引：
CREATE INDEX ON orders(created_at, tenant_id, status);
→ 范围放前面，过滤时还要扫大量行
正确索引：
CREATE INDEX ON orders(tenant_id, status, created_at DESC);
↑等值 ↑等值 ↑排序+范围
查询-索引映射模板
| 查询场景 | 过滤条件 | 排序 | 分页 | 频率 | 建议索引 | 风险 |
|---|---|---|---|---|---|---|
| 列表查询 | tenant_id, status | created_at desc | cursor | 高 | (tenant_id, status, created_at DESC) |
写入慢 |
| 详情查询 | id | - | - | 极高 | 主键自带 | - |
| 搜索 | name LIKE '%xxx%' | - | - | 中 | gin(name gin_trgm_ops) |
占空间 |
| 时间范围 | created_at > '...' | created_at | - | 低 | (created_at) 或 BRIN |
- |
| 关联查询 | user_id | - | - | 高 | (user_id) 必加 |
- |
高级索引技巧
覆盖索引（INCLUDE）
-- 查询：SELECT id, status, total FROM orders WHERE user_id = ?
-- 普通索引：还要回表查 status, total
-- 覆盖索引：包含所有需要的字段，避免回表
CREATE INDEX idx_orders_user_covering
ON orders(user_id) INCLUDE (status, total);
部分索引（Partial Index）
-- 只有 5% 的订单是 pending，但查询 99% 找的就是 pending
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- 软删除常用
CREATE UNIQUE INDEX uq_users_email
ON users(email)
WHERE deleted_at IS NULL;
表达式索引（Expression Index）
-- 大小写不敏感搜索
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = LOWER(?);
-- JSON 字段索引
CREATE INDEX idx_orders_status ON orders((metadata->>'status'));
反向索引（Descending）
-- 时间倒序分页
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);
部分覆盖索引（PG 专属神器）
-- 只对活跃用户的查询建索引
CREATE INDEX idx_users_active_name
ON users(name)
INCLUDE (email, avatar_url)
WHERE is_active = true;
标准流程
1. 收集读写路径和频率
- API 端点 → SQL 模式
- 查询频率 / 写入频率
↓
2. 标注每个查询的：
- 过滤条件（等值 / 范围 / IN / LIKE）
- 排序字段和方向
- 分页方式（offset / cursor）
- join 关系
- SELECT 字段（用于覆盖索引）
↓
3. 识别唯一性约束和业务去重需求
↓
4. 设计候选索引
- 主键 / 外键 自带或必加
- 高频查询的复合索引（ESR 原则）
- 唯一约束 → 唯一索引
- 部分 / 覆盖 / 表达式索引（看情况）
↓
5. 评估写入成本、存储成本和迁移成本
- 单表索引数 ≤ 5（经验值）
- 总索引大小 ≤ 表大小（经验值）
↓
6. 输出索引清单和验证方式
- EXPLAIN 验证用上索引
- 大表用 CREATE INDEX CONCURRENTLY（PG）
索引选择性分析
-- 选择性 = distinct 值数 / 总行数
-- 越接近 1 越好（每个索引值定位行少）
SELECT
COUNT(DISTINCT status) * 1.0 / COUNT(*) AS selectivity_status, -- 0.0001（差）
COUNT(DISTINCT user_id) * 1.0 / COUNT(*) AS selectivity_user_id, -- 0.5（中）
COUNT(DISTINCT id) * 1.0 / COUNT(*) AS selectivity_id -- 1（满）
FROM orders;
-- 选择性 < 0.001 一般不单独建索引
-- 但放复合索引前面 + 等值过滤 OK
索引维护
找无用索引
-- PostgreSQL：找从未使用的索引
SELECT
schemaname, tablename, indexname,
idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
找重复索引
-- 同一字段被多个索引覆盖，可能有冗余
SELECT
indrelid::regclass AS table_name,
array_agg(indexrelid::regclass) AS indexes,
indkey
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;
在线建索引（PostgreSQL）
-- 不锁表
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- 注意：失败时会留下 INVALID 状态索引，需手动清理
在线建索引（MySQL 5.6+）
-- 大部分情况自动 ONLINE
ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;
配套模板
templates/index-review-template.md— 索引方案 + 查询场景映射 + 选择性分析 + 验证方式
质量自检
□ 每个索引对应明确查询场景或唯一约束
□ 复合索引顺序符合 ESR（等值 → 排序 → 范围）
□ 高频列表查询有覆盖索引（INCLUDE）
□ 软删除场景用部分索引保留唯一性
□ 全文搜索用 GIN（PG）/ FULLTEXT（MySQL）
□ 时间序列大表评估 BRIN
□ 没有重复索引
□ 单表索引数 ≤ 5
□ 索引总大小 ≤ 表大小（经验值）
□ 大表建索引用 CONCURRENTLY / ONLINE=ALGORITHM
□ 标注可删除或需观察的冗余索引
□ 验证指标：执行计划 / 耗时 / 扫描行数
常见坑
- 为每个查询字段建单列索引——写入慢、空间大、优化器选错
- 复合索引顺序错——范围放前面，过滤效果差
- 忽略排序和分页——filesort / 大范围扫描
- OFFSET 深分页——offset 100000 时仍要扫 100000 行
- 只优化读，忽略写——TPS 砍半
- 新增索引没有迁移窗口——大表建索引锁 30 分钟
- JSON 字段未建 GIN 索引——
metadata->>'k'全表扫 - LIKE '%xxx%' 用 B-tree——必须 GIN trigram
- 低选择性字段单独建索引——is_active 索引几乎无效
- 唯一约束 + 唯一索引重复建——浪费空间
- 不用 EXPLAIN 验证——以为用上了实际没用
- CREATE INDEX 默认锁表——PG 必须 CONCURRENTLY
- UUID v4 主键写放大——B-tree 大量页分裂
- 索引建好不维护——半年后冗余 / 无用索引堆积
与其他 skill 的协作
上游：
schema-design → 提供表结构和约束
query-review → 提供真实 SQL 和执行路径
api-designer → 提供查询模式（pagination / filter / sort）
下游：
migration-rollout → 处理新增/删除索引的上线计划
data-operations-safety → 评估生产建索引风险
query-review → 验证索引被用上
相关参考
references/index-design-guide.md— B-tree 内部结构、ESR 原则深度、PostgreSQL 索引类型完整对比、大表索引重建策略、慢查询索引诊断流程
