数据库查询优化：索引、执行计划和分区

单个缺失索引可以将 2 毫秒的查询变成 20 秒的表扫描。 随着数据库从数千行增长到数百万行，优化查询和未优化查询之间的差异就是响应式应用程序与负载超时的应用程序之间的差异。数据库优化可以为您所做的任何性能工作提供最高的工程时间回报。

要点

• EXPLAIN ANALYZE 是您最强大的诊断工具 - 在优化任何内容之前学会阅读执行计划
• 策略性地选择索引类型：用于相等和范围的 B 树、用于全文和 JSONB 的 GIN、用于过滤子集的部分索引
• N+1 查询是基于 ORM 的应用程序中最常见的性能杀手——通过查询日志记录尽早检测到它们
• 当表超过 10-5000 万行时，表分区变得至关重要，从而减少查询规划时间并实现高效的数据生命周期管理

---

使用 EXPLAIN ANALYZE 读取执行计划

在优化任何查询之前，您必须了解 PostgreSQL 当前如何执行它。 EXPLAIN ANALYZE 运行查询并使用实时数据显示实际执行计划。

基本的 EXPLAIN ANALYZE 输出向您显示规划者选择的策略、估计行数与实际行数以及每个步骤所花费的时间。需要关注的关键指标是：

• 顺序扫描 -- 数据库读取表中的每一行。对于小型表（10,000 行以下）来说是可以接受的，但对于较大的表来说是一个危险信号。
• 索引扫描 -- 数据库使用索引来有效地查找匹配的行。这就是您想要对大型表进行过滤查询的结果。
• 仅索引扫描 -- 数据库完全从索引回答查询，而不接触表。最快的扫描类型。
• 嵌套循环 -- 通过在外部表中每行扫描一次内部表来连接表。当内部扫描使用索引时，效率更高。
• 散列连接 -- 从连接的一侧构建哈希表，然后用另一侧探测它。对于较大的结果集非常有效。
• 排序 -- 显式排序步骤，通常用于 ORDER BY。留意溢出到磁盘的排序（由“排序方法：外部合并”指示）。

寻找什么

执行计划中最重要的信号是估计行和实际行之间的差距。当 PostgreSQL 估计 10 行但发现 100,000 行时，它选择了错误的计划。当表统计信息过时时会发生这种情况 - 在表上运行 ANALYZE 来更新它们。

监视大型表上的顺序扫描、无索引排序以及内表上的顺序扫描的嵌套循环。这些模式中的每一个都表示缺少索引或需要重写的查询。

---

索引类型以及何时使用它们

PostgreSQL 提供了多种索引类型，每种索引类型都针对不同的查询模式进行了优化。选择正确的类型至关重要——只需要相等性检查的列上的 GIN 索引会浪费存储空间并减慢写入速度，而不会提高读取速度。

B 树索引

B 树是默认且最通用的索引类型。它支持相等（=）、范围（<、>、BETWEEN）、排序（ORDER BY）和前缀模式匹配（LIKE 'abc%'）。对于 WHERE、JOIN 和 ORDER BY 子句中的大多数列，B 树索引是正确的选择。

复合索引 将多个列组合成一个 B 树。列顺序很重要：(status,created_at) 上的索引有效地支持单独对状态进行查询过滤或对状态和created_at 进行查询过滤，但不能单独对created_at 进行查询过滤。首先放置最具选择性的列，最后放置用于范围过滤的列。

GIN 索引

GIN 索引擅长在复合值中进行搜索。它们对于全文搜索（tsvector 列）、JSONB 包含查询（@>、?）和数组重叠查询（&&、@>）至关重要。 GIN 索引比 B 树索引更大且更新更慢，因此仅在 B 树无法满足查询模式的情况下使用它们。

对于存储灵活属性的 JSONB 列，整个列上的 GIN 索引支持任何基于键的查询。对于仅查询特定键的列，生成的列或表达式上的 B 树索引更有效。

部分索引

部分索引仅索引与 WHERE 条件匹配的行。它们对于查询一致过滤一小部分数据的表来说非常强大。

例如，如果您的订单表有 1000 万行，但您几乎只查询活动订单（表的 5%），则 (customer_id,created_at) WHERE status = 'active' 上的部分索引比完整索引小 20 倍，并且与实际查询一样快。

---

检测并修复 N+1 查询

N+1 查询问题是使用 ORM 的应用程序中最常见的性能问题。当代码加载 N 条记录的列表，然后对每条记录执行一个附加查询来加载相关数据时，就会发生这种情况，导致总共 N+1 个查询，而不是 1-2 个。

N+1 查询是如何发生的

考虑加载带有客户名称的订单列表。简单的实现会加载订单列表（1 个查询），然后对于每个订单加载客户（N 个查询）。对于 100 个订单，这会生成 101 次数据库往返。如果每个查询 1 毫秒，即 101 毫秒，但在连接池争用的并发负载下，它很容易变成 500 毫秒或更长。

检测方法

1. 查询日志记录 -- 暂时启用 PostgreSQL 查询日志记录并查找具有不同参数值的重复相同查询
2. ORM 级别日志记录 -- Drizzle ORM、Prisma 和 TypeORM 都支持查询日志记录，显示执行的每个 SQL 语句
3. APM工具 -- Datadog、New Relic和Sentry可以按端点对查询进行分组并自动突出显示N+1模式
4. pg_stat_statements -- 此 PostgreSQL 扩展跟踪查询执行统计信息并显示经常执行的相同查询模板

修复 N+1 查询

修复取决于您的 ORM 和查询模式：

• 预加载 -- 告诉 ORM 使用 JOIN 在初始查询中加载相关数据。在 Drizzle 中，在查询构建器中使用 with 选项。
• 批量加载 -- 收集所有外键 ID，然后在单个 WHERE id IN (...) 查询中加载相关记录。这就是 DataLoader 模式。
• 非规范化 -- 对于读取量大的用例，将相关数据直接存储在父记录上。用写入复杂性换取读取性能。

---

查询重写技术

有时查询本身需要重组，而不仅仅是更好的索引。

子查询到 JOIN 转换

相关子查询在外部查询中每行执行一次。将它们转换为 JOIN 允许 PostgreSQL 使用更高效的连接策略。

不要使用查找每个客户的最新订单日期的子查询来选择订单，而是将其重写为带有派生表或窗口函数的 JOIN。 JOIN 版本允许 PostgreSQL 根据数据分布在嵌套循环、散列连接和合并连接之间进行选择。

通用表表达式 (CTE)

在 PostgreSQL 12 及更高版本中，CTE 默认情况下是内联的，这意味着优化器可以将谓词推入其中。使用 CTE 提高可读性，而无需担心性能障碍。对于您明确想要实现的情况（以防止重新执行昂贵的子查询），请添加 MATERIALIZED 关键字。

窗口函数与 GROUP BY

当您同时需要详细行和聚合时，窗口函数可以避免使用自联接或子查询。使用窗口函数计算运行总计、在组内排名或将每行与组平均值进行比较都比使用相关子查询更有效。

---

表分区策略

当表的行数超过 10-5000 万行时，即使索引良好的查询也会由于索引深度、真空开销和规划器复杂性而变慢。分区将大表划分为更小的物理块，同时维护单个逻辑表接口。

分区类型

按日期进行范围分区

最常见的模式是按时间戳列每月分区。每个月的数据都位于自己的分区中。按日期过滤的查询会自动仅扫描相关分区（分区修剪）。

基于时间的分区的好处：

• 查询性能 -- 查询最近的数据仅扫描最近的分区
• 维护 -- VACUUM 和 ANALYZE 在较小的分区上运行得更快
• 数据生命周期 - 与删除数百万行相比，删除旧分区是即时的
• 备份效率 -- 仅备份最近的分区以进行时间点恢复

分区注意事项

分区增加了复杂性。每个查询都必须在其 WHERE 子句中包含分区键，分区修剪才能发挥作用。唯一约束必须包含分区键。引用分区表的外键有限制。仅当您测量到表大小导致性能下降时才开始分区。

---

PostgreSQL 配置调优

默认的 PostgreSQL 配置是保守的，旨在在最少的硬件上运行。生产工作负载受益于调整关键参数。

必须根据您的特定硬件和工作负载调整这些设置。监视 pg_stat_bgwriter、pg_stat_activity 和 pg_stat_user_tables 以验证更改是否可以提高性能。

---

常见问题

一个表应该有多少个索引？

没有固定的限制，但每个索引都会减慢 INSERT、UPDATE 和 DELETE 操作，因为必须维护索引。一个好的经验法则是为最常用查询的 WHERE、JOIN ON 和 ORDER BY 子句中出现的列创建索引。使用 pg_stat_user_indexes 查找可以删除的未使用索引。

为了提高性能，我应该使用 UUID 还是整数主键？

整数主键 (BIGSERIAL) 对于连接和索引来说速度更快，因为它们更小（8 字节与 16 字节）并且自然排序。 UUID 无需协调即可提供全局唯一性，这对于分布式系统很重要。对于大多数应用程序，使用 UUID 作为面向外部的标识符，使用整数作为内部连接。

我什么时候应该从单一数据库切换到只读副本？

当您的读取工作负载超过数据库容量的 70-80% 时，或者当报告查询与事务查询竞争资源时。只读副本处理读取负载，而主副本则专注于写入。对于典型的 Web 应用程序，通常需要 5,000-10,000 个并发用户。

如何在不停机的情况下处理生产中的慢速查询？

使用 CONCURRENTLY 选项创建索引以避免锁定表。使用 pg_stat_statements 来识别最慢的查询。在功能标志后面部署查询优化。对于重写表的架构更改，请使用 pg_repack 等工具在不锁定的情况下重新组织表。

---

下一步是什么

数据库优化是平台性能的基础。首先启用 pg_stat_statements，识别最慢的查询，然后使用 EXPLAIN ANALYZE 系统地处理它们。添加缺失的索引，修复 N+1 模式，并考虑对最大的表进行分区。

有关更广泛的性能情况，请参阅我们关于将您的业务平台从初创企业扩展到企业 的支柱指南。要了解下一层优化，请阅读我们的关于 Redis、CDN 和 HTTP 缓存的缓存策略 的指南。

ECOSIRE 为 PostgreSQL 支持的平台提供专家数据库优化，包括 Odoo ERP 和自定义应用程序。 联系我们 进行数据库性能审核。

---

由 ECOSIRE 发布 — 通过 Odoo ERP、Shopify 电子商务 和 OpenClaw AI 等人工智能驱动的解决方案帮助企业扩展规模。