数据库索引艺术：超越基础

数据库索引：大多数开发者只做一次就不再优化的优化

每个开发者都知道索引能加速查询。但较少人理解为什么有些索引不会被使用，为什么某些查询即使有索引仍然很慢，多列索引究竟是如何工作的，或者什么时候索引反而会让你的应用变慢。本指南超越了”在WHERE子句列上添加索引”的层面，深入探讨了索引的机制和权衡，帮助你做出关于索引策略的明智决策。

索引的实际工作原理：B-Tree

Postgres、MySQL和大多数关系型数据库中的默认索引类型是B-Tree（平衡树）。理解它的结构可以解释大多数索引行为。

B-Tree索引是一种排序的数据结构，其中每个节点包含键和指向子节点或堆页的指针。当你查询WHERE user_id = 4821时，数据库以O(log n)的时间复杂度从根节点遍历到叶节点，而不是以O(n)的时间复杂度扫描每一行。

关键含义：

• B-Tree索引支持等值(=)、范围(<、>、BETWEEN)和前缀匹配(LIKE 'foo%')
• 它们不支持后缀匹配(LIKE '%foo')——索引是从左到右排序的，而不是从右到左
• 它们支持ORDER BY——如果你按某列建立索引并按该列排序，数据库可以直接按索引顺序返回行，无需额外的排序步骤

阅读EXPLAIN ANALYZE：改变一切的关键技能

在添加任何索引之前，先理解为什么现有查询很慢。EXPLAIN ANALYZE显示了实际的执行计划：

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

示例输出及其解读：

Limit  (cost=1842.34..1842.47 rows=50 width=52)
       (actual time=28.432..28.441 rows=50 loops=1)
  ->  Sort  (cost=1842.34..1842.62 rows=111 width=52)
             (actual time=28.430..28.434 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 30kB
        ->  Hash Join  (cost=412.50..1838.71 rows=111 width=52)
                        (actual time=5.231..27.891 rows=423 loops=1)
              Hash Cond: (o.user_id = u.id)
              Buffers: shared hit=891 read=243
              ->  Seq Scan on orders o
                    (cost=0.00..1422.17 rows=111 width=36)
                    (actual time=0.012..22.341 rows=423 loops=1)
                    Filter: ((status = 'pending') AND
                             (created_at > (now() - '7 days'::interval)))
                    Rows Removed by Filter: 38291

需要注意的危险信号：

• Seq Scan 在大表上 — 扫描每一行
• Rows Removed by Filter 远大于返回的行数 — 选择性低
• Buffers: read= 数值大 — 从磁盘而非缓存读取
• actual time 远大于 cost — 统计信息过期，运行 ANALYZE

多列索引：列顺序至关重要

在 (status, created_at) 上的多列索引与 (created_at, status) 上的索引不同。最左前缀规则决定了哪些查询可以使用该索引。

-- 这个索引：
CREATE INDEX orders_status_created_idx ON orders (status, created_at DESC);

-- 可用于：
WHERE status = 'pending'                              -- 使用索引
WHERE status = 'pending' AND created_at > '2026-01-01' -- 使用索引（两列）
WHERE status = 'pending' ORDER BY created_at DESC     -- 使用索引，避免排序

-- 不能用于：
WHERE created_at > '2026-01-01'                       -- 跳过第一列
WHERE status LIKE '%pending%'                          -- 不是前缀搜索

多列索引中列排序的经验法则：

1. 等值条件优先（使用 = 过滤的列）
2. 范围条件最后（使用 <、>、BETWEEN 过滤的列）
3. 如果希望获得索引排序的结果，排序列放在最后

部分索引：只索引你查询的内容

部分索引只包含符合特定条件的行。它们更小，更新更快，并且通常选择性更高：

-- 在 status 上的完整索引 — 包含 'completed'、'cancelled'、'refunded' 行
-- 但 99% 的查询只查看 'pending' 订单
CREATE INDEX orders_created_idx ON orders (created_at DESC);

-- 部分索引 — 只索引实际查询的行
CREATE INDEX orders_pending_created_idx ON orders (created_at DESC)
  WHERE status = 'pending';

-- 这用于具有匹配 WHERE 条件的查询
SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

如果你的订单中 1% 是待处理状态，99% 已完成，那么一个待处理订单的部分索引比完整索引小约 100 倍，维护速度快约 100 倍。

覆盖索引：消除表堆获取

进行索引查找后，数据库通常需要从表堆中获取实际行来获取非索引列。覆盖索引包含了查询所需的所有列，从而消除了第二次获取：

-- 从orders表中查询user_id, status, total, created_at
-- 在user_id上的常规索引需要为其他列进行堆获取
CREATE INDEX orders_user_idx ON orders (user_id);

-- 覆盖索引：包含所有需要的列
-- 查询可以完全从索引中满足
CREATE INDEX orders_user_covering_idx
  ON orders (user_id)
  INCLUDE (status, total, created_at);

-- PostgreSQL 11+ 使用INCLUDE的语法
-- INCLUDE列位于叶页面中但不用于排序/搜索

-- 显示仅索引扫描（无堆获取）的EXPLAIN输出
Index Only Scan using orders_user_covering_idx on orders
  (cost=0.43..8.45 rows=1 width=32)
  (actual time=0.021..0.023 rows=3 loops=1)
  Index Cond: (user_id = 4821)
  Heap Fetches: 0  -- <-- 零次表获取

B-Tree之外的索引类型

GIN索引：数组、JSONB和全文搜索

-- 用于JSONB包含查询的GIN索引
CREATE INDEX events_payload_gin ON events USING GIN (payload);

-- 支持的操作符
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';
SELECT * FROM events WHERE payload ? 'user_id';

-- 用于全文搜索的GIN
CREATE INDEX articles_search_gin ON articles USING GIN (to_tsvector('english', content));
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & index');

BRIN索引：大型仅追加表

-- BRIN（块范围索引）用于自然排序的时间序列数据
-- 索引大小很小：每个块范围一个条目，而不是每行一个
CREATE INDEX events_created_brin ON events USING BRIN (created_at)
  WITH (pages_per_range = 128);

-- 对于仅追加的时间序列，比B-tree小10-100倍
-- 之所以有效是因为行按时间戳顺序物理存储
-- 只有当物理顺序与索引列相关时才有用

索引何时会使事情变慢

每个索引都有写入成本。每次INSERT、UPDATE或DELETE都必须更新所有适用的索引。在写入密集型场景中：

• 一个包含10个索引的表批量加载数据的时间比没有索引的表长约10倍
• 对索引列进行高频UPDATE操作会导致索引膨胀
• OLTP表上的索引过多可能会使写入成为瓶颈

-- 批量加载数据时，先删除索引
DROP INDEX orders_status_created_idx;
DROP INDEX orders_user_covering_idx;

-- 加载数据
COPY orders FROM '/tmp/orders_backup.csv' WITH CSV;

-- 之后再重建索引（比在加载期间进行增量更新更快）
CREATE INDEX CONCURRENTLY orders_status_created_idx ON orders (status, created_at DESC);
CREATE INDEX CONCURRENTLY orders_user_covering_idx ON orders (user_id) INCLUDE (status, total, created_at);

使用 CREATE INDEX CONCURRENTLY 可以在不锁定表的情况下构建索引。

查找未使用的索引

-- 查找自上次统计重置以来未被使用的索引
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint  -- 不要删除约束索引
  )
ORDER BY pg_relation_size(indexrelid) DESC;

未使用的索引纯粹是开销——它们减慢写入速度而不加快读取速度。删除它们。

索引工作流程

1. 从查询监控（pg_stat_statements、Datadog等）中识别慢查询
2. 运行 EXPLAIN ANALYZE 来理解执行计划
3. 为查询模式添加选择性最高的索引（或部分索引）
4. 在生产环境中使用 CREATE INDEX CONCURRENTLY
5. 再次使用 EXPLAIN ANALYZE 验证计划是否已更改
6. 每月：审核未使用的索引并删除它们

良好的索引是一项持续实践，而非一次性设置。查询模式会变化，数据分布会转移，随着数据增长，在启动时合理的索引会成为负担。像对待架构的其他部分一样对待您的索引清单：定期审查，并移除那些没有发挥作用的索引。