SuperAPI AI Directory

index-access-pattern

General & other

View repository

Updated 10-16 09:30

About this skill

Indexes & Access Patterns

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. 索引来自真实访问模式,不来自字段清单
   先列查询场景,再决定索引
  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)
  2. Composite index order: equality → range → sort Follow ESR (Equality, Sort, Range)

  3. Covering indexes save a table lookup Put all high-frequency query fields into INCLUDE

  4. Partial indexes save space WHERE deleted_at IS NULL queries use partial indexes

  5. Do not create separate indexes for low-selectivity fields Gender, boolean, few status values → a separate index is almost useless

  6. Unique constraint = unique index (automatic) Do not create it again

  7. 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

  1. Create single-column indexes for every query field——Slow writes, large space, optimizer chooses wrong
  2. Wrong composite index order——Range condition placed first, poor filtering effect
  3. Ignoring sorting and pagination——filesort / large range scan
  4. OFFSET deep pagination——With offset 100000, still must scan 100000 rows
  5. Only optimizing reads, ignoring writes——TPS halved
  6. No migration window for adding indexes——Index creation on large table locks for 30 minutes
  7. JSON fields without GIN index——metadata->>'k' full table scan
  8. LIKE '%xxx%' using B-tree——Must use GIN trigram
  9. Creating separate indexes for low-selectivity fields——is_active index is almost useless
  10. Duplicate creation of unique constraint + unique index——Wastes space
  11. Not verifying with EXPLAIN——Assume it's used but actually not
  12. CREATE INDEX locks table by default——PostgreSQL must use CONCURRENTLY
  13. UUID v4 primary key write amplification——Large number of B-tree page splits
  14. 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