数据库索引与查询优化：从慢查询到可解释的性能

📅 2026-10-08 📂 后端 🏷️ PostgreSQL, 数据库索引, SQL, 性能分析
10 分钟阅读 · 2,562 字

加索引前先问：哪个实际请求慢、频率多高、返回多少行、慢在数据库还是连接池/应用层？索引让某些读查询更快，也会占空间并增加写放大、缓存占用和迁移风险。本文以 PostgreSQL 为例，执行前要核对实际数据库版本、数据分布和查询计划。

建立可复现基线

从慢查询日志、应用追踪和数据库统计中找高总耗时、高 P95 或高调用量的 SQL，记录参数范围、表规模、返回行数和并发。开发环境的小数据集常让顺序扫描看起来足够快，不能证明生产索引无用。先保留一份代表性查询和结果基线。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 50;

EXPLAIN ANALYZE 会真正执行语句。用于 SELECT 时也要限制扫描和超时；不能把有副作用的语句当成纯计划检查。重点比较估算行数与实际行数、扫描类型、过滤比例、排序、Buffers 命中/读取、loops 和耗时分布。计划选择顺序扫描未必错误：表很小或要读取大部分行时，它可能更便宜。

逐层读懂执行计划

计划是一棵树：先从底部扫描节点向上看数据如何经过过滤、连接、排序和聚合，再确认顶层节点输出。先找“估算行数与实际行数第一次明显分叉”的节点，它往往比单看最上层总耗时更能解释后续计划为什么变重。

Index Scan ... (cost=启动成本..总成本 rows=估算输出行数 width=估算行宽)
              (actual time=首行毫秒..末行毫秒 rows=实际输出行数 loops=执行次数)
  • cost 不是毫秒：它是优化器按成本参数计算的相对估值；上层节点的成本已包含子节点，不要把父子成本相加，也不要拿它直接和实际耗时比较。
  • rows 是节点输出行数：它不是扫描或检查过的总行数。实际 rows 和 actual time 在 loops 大于 1 时是每次执行的平均值；要理解总工作量需结合循环次数，但父子节点耗时仍有包含关系，不能简单相加。
  • 区分 Index Cond 与 Filter：Index Cond 用于索引访问，Filter 是行取回后再判断。若实际扫描/取回远多于最终输出，且 Rows Removed by Filter 很高，可把谓词形状和索引候选列列入验证清单，但仍要以真实选择率和读写成本决定是否建索引。
  • 查看排序与哈希的运行状态：Sort 的 Sort Method 若显示外部归并或磁盘用量，说明发生了磁盘排序；Hash 的 Batches 大于 1 时也要检查批次和磁盘开销。先确认行数估算、查询投影和并发，再评估会话级 work_mem；它可能被同一查询的多个节点及并发连接同时消耗，不要只为一条计划全局调大。
  • Buffers 不等于唯一页数或物理磁盘耗时：父节点统计包含子节点，重复访问也会重复计数；shared hit/read 描述缓冲区活动，不是可直接相加的唯一页面数或端到端时延。结合缓存冷热、执行次数和应用追踪解读。
  • 注意提前停止：LIMIT、半连接等节点可能只消费子节点的一部分输出。比较实际工作量时应看节点关系与实际行数，不要假设每个子节点都跑到估算总行数。

EXPLAIN ANALYZE 不只适用于“无副作用”的 SELECT：查询中调用的函数也可能产生副作用。对写语句，在受控测试库中可以用事务包住执行并回滚表数据变更：

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'ARCHIVED' WHERE created_at < DATE '2020-01-01';
ROLLBACK;

回滚不保证撤销序列消耗，也不能撤销触发器/函数已经发往外部系统的请求、消息或文件操作。PostgreSQL 的 nextval 序列取值不会因回滚而回收，详见序列函数文档。因此不要在生产库用“执行后回滚”当作安全沙箱；优先使用脱敏恢复的隔离库和代表性参数。

按访问模式设计索引

PostgreSQL 18 的 B-tree 支持 skip scan。比如索引顺序是 status、tenant_id、created_at，而查询没有 status 等值条件、只过滤 tenant_id 时，若前导 status 的不同值足够少，规划器可能通过重复搜索跳过无关区间。它仍取决于数据分布、统计信息与成本估算；前导列基数高时可能不划算，不能代替按常见访问模式设计索引。PostgreSQL 17 及更早版本没有这项能力。

partial index 的谓词需要在规划阶段被查询条件证明。针对本节固定读取已付款订单的查询，可以评估：

CREATE INDEX CONCURRENTLY orders_paid_timeline_idx
ON orders (tenant_id, created_at DESC, id DESC)
WHERE status = 'PAID';

如果应用使用 status = $1 这样的预备语句，规划器不能假设参数永远是 PAID，不能依赖该 partial index 一定可用；需要用生产实际的预备语句形态检查计划，并比较普通复合索引。CREATE INDEX CONCURRENTLY 不能放在事务块内，构建失败可能留下无效索引；重试前应检查索引有效状态并按版本化迁移流程安全清理。

版本差异依据：PostgreSQL 18 multicolumn indexes、PostgreSQL 18 partial indexes、PostgreSQL 18 release notes。

针对上面的条件，候选 B-tree 可以从 (tenant_id, status, created_at, id) 评估。多列 B-tree 的左侧列与真实过滤/排序条件很重要；对不同查询组合不要盲目复制同一排列。PostgreSQL 文档指出，多列索引应谨慎使用，除非数据访问模式确实需要。

  • 选择性与频率：低基数布尔列单独建 B-tree 常帮助有限；高频组合条件、时间范围和排序要一起分析。
  • 部分索引：若大量查询固定只取未归档行，可测试 WHERE archived_at IS NULL 的 partial index，需确保查询谓词能够匹配。
  • 覆盖索引：INCLUDE 可包含返回字段以争取 index-only scan，但会增加索引大小，且可见性信息仍影响是否回表。
  • JSON/全文：按查询操作符评估 GIN/表达式索引；不要把所有字段都塞进索引。
  • 约束索引：唯一性应由数据库唯一约束兜底；外键列是否需要索引，要看查询/删除更新模式和数据库自身行为。

分页与排序

深页 OFFSET 需要扫描并跳过此前行数，延迟会随页码增长；并发变更还可能让页间重复或漏项。时间线可使用 keyset pagination，并用唯一字段补足稳定排序：

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 30;

游标必须与筛选、排序和访问权限范围绑定，服务端校验或签名；用户更改筛选后应开始新游标。游标不是授权凭证。排序字段应避免 NULL 语义不一致，或显式定义 NULLS FIRST/LAST 并让索引匹配。

统计信息和谓词形状

优化器依赖统计信息估算行数。批量导入或数据分布显著变化后，检查 ANALYZE/autovacuum；若估算偏差大，先调查统计信息、倾斜分布、相关列和参数化计划，再决定是否调整统计目标。对列套函数、类型不匹配、前导通配符或隐式转换可能使普通索引无法被有效使用；不要机械地把谓词改写成另一种形式，需验证语义和计划。

EXPLAIN ANALYZE 的节点耗时不包含客户端网络传输与应用后续处理。PostgreSQL 18+ 可尝试 EXPLAIN (ANALYZE, BUFFERS, SERIALIZE TEXT) 测量结果值转成文本的序列化开销；它仍不会真的把数据发送给客户端，而且 EXPLAIN ANALYZE 的逐节点计时本身会带来测量开销。旧版本不支持 SERIALIZE。对照应用端响应时间，拆分数据库执行、结果序列化、网络、连接池和应用渲染。慢查询可能是一次返回几十万行，而非索引缺失。

安全地创建或移除索引

在 PostgreSQL 大表上评估 CREATE INDEX CONCURRENTLY，它避免阻塞正常写入，但有额外扫描和失败残留索引等注意事项，而且不能在事务块里执行。迁移工具可能默认包裹事务，需明确配置为单独执行。建完确认索引有效、使用率和写入影响；IF NOT EXISTS 只按名字跳过，并不能证明同名索引定义正确。

删除索引前检查约束依赖和真实工作负载。可先考虑不可见/不参与计划的验证方式（数据库支持时），观察计划、慢查询和写入负载；最终变更仍需要回滚办法。不要仅凭某段时间统计使用量为零就删掉低频但关键的索引。

优化闭环

  1. 记录查询 fingerprint、参数形态、执行计划和 P95 基线。
  2. 仅提出一个候选改动；在生产相似数据规模的环境比较读写延迟、磁盘和索引大小。
  3. 通过真实服务路径和并发负载回归；检查结果正确性、租户隔离和稳定排序。
  4. 上线观察查询耗时、缓存、锁、写入、复制/存储指标，并定义退出阈值。
  5. 文档记录为什么建索引、支持哪些查询、复核时间和移除风险。

权威参考：PostgreSQL multicolumn indexes、Using EXPLAIN、CREATE INDEX。

Live Notes

读者弹幕

留言提交后会先进入审核，审核通过后对其他读者公开。