References: Markus Winand, "Use The Index, Luke!", the official PostgreSQL Indexes documentation, High Performance MySQL (O'Reilly), Stripe / GitHub indexing in practice.
Applicable Scenarios
- Designing indexes before a new table goes live
- Designing access paths based on API list pages, filtering, sorting, and search
- Determining the field order of a composite index
- Evaluating unique indexes, covering indexes, partial indexes, and prefix indexes
- Troubleshooting missing or excessive indexes
- Index rebuilds / online index creation on large tables
Core Principles
1. 索引来自真实访问模式,不来自字段清单
先列查询场景,再决定索引
-
More indexes is not better Each index you add:
- Writes slow down by 5%~30%
- Takes up space (about 30%~100% of table size)
- Maintenance cost (rebuild / migration)
-
Composite index order: equality → range → sort Follow ESR (Equality, Sort, Range)
-
Covering indexes save a table lookup Put all high-frequency query fields into INCLUDE
-
Partial indexes save space
WHERE deleted_at IS NULLqueries use partial indexes -
Do not create separate indexes for low-selectivity fields Gender, boolean, few status values → a separate index is almost useless
-
Unique constraint = unique index (automatic) Do not create it again
-
Be cautious with UUID v4 primary keys under high-concurrency writes B-tree write amplification is severe; use ULID / UUID v7
## 索引类型对比
### B-tree(默认 / 最常用)
```sql
CREATE INDEX idx_orders_user_id ON orders(user_id);
Suitable for:
- Equality (=)
- Range (<, >, BETWEEN)
- Sorting (ORDER BY)
- Prefix matching (LIKE 'abc%')
Not suitable for:
- Suffix matching (LIKE '%abc') → use GIN trigram instead
- Full-text search → use GIN tsvector
Hash(PostgreSQL 10+)
CREATE INDEX idx_users_email_hash ON users USING hash(email);
Suitable for:
- Equality lookups only
- Large fields (only 4~8 bytes after hashing)
Not suitable for:
- Range queries
- Sorting
GIN (inverted index, PostgreSQL-specific)
-- 数组
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');
Suitable for:
- Array / JSON fields
- Full-text search
- Multi-value fields
BRIN (block range index, PostgreSQL)
CREATE INDEX idx_logs_created_at ON logs USING brin(created_at);
Suitable for:
- Time-series data (naturally ordered)
- Very large tables (takes up very little space)
- Range scans
Not suitable for:
- Random access
Spatial index (GiST)
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
Suitable for:
- Geolocation (PostGIS)
- Range queries (point within polygon)
ESR Composite Index Order Principle
原则: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);
↑等值 ↑等值 ↑排序+范围
Query-Index Mapping Template
| Query Scenario | Filter Conditions | Sorting | Pagination | Frequency | Recommended Index | Risk |
|---|---|---|---|---|---|---|
| List query | tenant_id, status | created_at desc | cursor | High | (tenant_id, status, created_at DESC) |
Slow writes |
| Detail query | id | - | - | Very high | Built into primary key | - |
| Search | name LIKE '%xxx%' | - | - | Medium | gin(name gin_trgm_ops) |
Space usage |
| Time range | created_at > '...' | created_at | - | Low | (created_at) or BRIN |
- |
| Join queries | user_id | - | - | High | (user_id) mandatory |
- |
Advanced Indexing Techniques
Covering Index (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 Index
-- 时间倒序分页
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);
Partial Covering Index (PostgreSQL Exclusive Weapon)
-- 只对活跃用户的查询建索引
CREATE INDEX idx_users_active_name
ON users(name)
INCLUDE (email, avatar_url)
WHERE is_active = true;
Standard Procedure
1. 收集读写路径和频率
- API 端点 → SQL 模式
- 查询频率 / 写入频率
↓
2. 标注每个查询的:
- 过滤条件(等值 / 范围 / IN / LIKE)
- 排序字段和方向
- 分页方式(offset / cursor)
- join 关系
- SELECT 字段(用于覆盖索引)
↓
3. 识别唯一性约束和业务去重需求
↓
4. 设计候选索引
- 主键 / 外键 自带或必加
- 高频查询的复合索引(ESR 原则)
- 唯一约束 → 唯一索引
- 部分 / 覆盖 / 表达式索引(看情况)
↓
5. 评估写入成本、存储成本和迁移成本
- 单表索引数 ≤ 5(经验值)
- 总索引大小 ≤ 表大小(经验值)
↓
6. 输出索引清单和验证方式
- EXPLAIN 验证用上索引
- 大表用 CREATE INDEX CONCURRENTLY(PG)
Index Selectivity Analysis
-- 选择性 = 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
Index Maintenance
Find Unused Indexes
-- 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;
Find Duplicate Indexes
-- 同一字段被多个索引覆盖,可能有冗余
SELECT
indrelid::regclass AS table_name,
array_agg(indexrelid::regclass) AS indexes,
indkey
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;
Online Index Creation (PostgreSQL)
-- 不锁表
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- 注意:失败时会留下 INVALID 状态索引,需手动清理
Online Index Creation (MySQL 5.6+)
-- 大部分情况自动 ONLINE
ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;
Supporting Templates
templates/index-review-template.md— Index plan + Query scenario mapping + Selectivity analysis + Verification method
Quality Self-Check
□ 每个索引对应明确查询场景或唯一约束
□ 复合索引顺序符合 ESR(等值 → 排序 → 范围)
□ 高频列表查询有覆盖索引(INCLUDE)
□ 软删除场景用部分索引保留唯一性
□ 全文搜索用 GIN(PG)/ FULLTEXT(MySQL)
□ 时间序列大表评估 BRIN
□ 没有重复索引
□ 单表索引数 ≤ 5
□ 索引总大小 ≤ 表大小(经验值)
□ 大表建索引用 CONCURRENTLY / ONLINE=ALGORITHM
□ 标注可删除或需观察的冗余索引
□ 验证指标:执行计划 / 耗时 / 扫描行数
Common Pitfalls
- Create single-column indexes for every query field——Slow writes, large space, optimizer chooses wrong
- Wrong composite index order——Range condition placed first, poor filtering effect
- Ignoring sorting and pagination——filesort / large range scan
- OFFSET deep pagination——With offset 100000, still must scan 100000 rows
- Only optimizing reads, ignoring writes——TPS halved
- No migration window for adding indexes——Index creation on large table locks for 30 minutes
- JSON fields without GIN index——
metadata->>'k'full table scan - LIKE '%xxx%' using B-tree——Must use GIN trigram
- Creating separate indexes for low-selectivity fields——is_active index is almost useless
- Duplicate creation of unique constraint + unique index——Wastes space
- Not verifying with EXPLAIN——Assume it's used but actually not
- CREATE INDEX locks table by default——PostgreSQL must use CONCURRENTLY
- UUID v4 primary key write amplification——Large number of B-tree page splits
- Indexes created but not maintained——Redundant/unused indexes accumulate after six months
Collaboration with Other skills
上游:
schema-design → 提供表结构和约束
query-review → 提供真实 SQL 和执行路径
api-designer → 提供查询模式(pagination / filter / sort)
下游:
migration-rollout → 处理新增/删除索引的上线计划
data-operations-safety → 评估生产建索引风险
query-review → 验证索引被用上
Related References
references/index-design-guide.md— B-tree internal structure, in-depth ESR principle, complete comparison of PostgreSQL index types, large table index rebuild strategies, slow query index diagnosis process