美国服务器上的PostgreSQL慢查询,如何判断是否需要调整索引?

发布人:A5数据发布时间:2026-10-17 09:32阅读量:89

接口 P95 延迟从 300 毫秒涨到 900 毫秒,数据库 CPU 却只有 40%,这并不能直接说明 PostgreSQL 缺少索引。请求可能在连接池排队,也可能花在跨境网络、锁等待或磁盘读取上。对于部署在美国服务器上的 PostgreSQL,判断是否需要调整索引,首先要确认:慢的是数据库执行,还是数据库之外的等待。

只有当执行计划显示大量无效扫描、回表或排序,并且这些工作与查询条件、排序方式及现有索引不匹配时,调整索引才有明确依据。反过来,如果数据库只执行了几毫秒,而接口耗时数百毫秒,新增索引通常无法解决主要问题。

一、测试目标:把“接口慢”缩小到一条查询的执行成本

索引优化的测试目标,不是让服务器 CPU 看起来更低,也不是让所有查询都出现 Index Scan,而是在相同业务负载下,减少目标查询的执行时间与资源消耗,同时不明显损害写入性能。

先把一次请求拆成几个阶段:

  • 应用等待数据库连接的时间。
  • 应用与 PostgreSQL 之间的网络往返。
  • 数据库内部的执行及等待时间。
  • 结果传输、应用处理和响应返回时间。

美国服务器的地理位置主要影响网络路径,不会改变 PostgreSQL 的索引机制。如果应用与数据库位于同一美国机房,跨境延迟可能主要发生在用户访问应用这一段;如果应用在国内、数据库在美国,多次串行 SQL 往返就可能放大请求耗时。

例如,示例链路单次往返约为 150 毫秒,一个接口顺序发送四次数据库请求,仅网络往返就可能累计约 600 毫秒。即使每条 SQL 从 20 毫秒降至 5 毫秒,整体收益仍然有限。这里更值得检查的是查询次数、批量读取方式和应用部署位置,而不是继续堆索引。

一、测试目标:把“接口慢”缩小到一条查询的执行成本配图

为了让前后结果可比较,测试至少需要固定以下条件:

测试条件需要固定的内容不固定可能造成的误判
数据表规模、数据分布、查询参数小账户快,不代表大账户也快
负载请求速率、并发数、读写比例低负载优化无法代表高峰表现
环境PostgreSQL 版本、实例资源、存储类型把资源变化误认为索引收益
缓存是否预热、是否存在应用缓存把缓存命中误认为执行计划改善
业务行为返回行数、排序、分页方式实际比较的是不同查询

测试应同时关注目标 SQL 和整个数据库。某条查询变快,如果以写入延迟显著上升为代价,就不能直接判断为优化成功。

二、指标含义:平均耗时找对象,执行计划找原因

查询统计负责筛选,不直接给出索引结论

已启用 pg_stat_statements 的环境,可以先找累计执行时间高、调用频繁或平均执行时间偏大的语句。下面示例使用 PostgreSQL 13 及以后版本中的常见字段;执行前应确认扩展已安装,并使用具备相应统计查看权限的账号。

SELECT
    queryid,
    calls,
    round(total_exec_time::numeric, 1) AS total_exec_ms,
    round(mean_exec_time::numeric, 2) AS mean_exec_ms,
    round(rows::numeric / NULLIF(calls, 0), 2) AS rows_per_call,
    round(shared_blks_hit::numeric / NULLIF(calls, 0), 1)
        AS hit_blocks_per_call,
    round(shared_blks_read::numeric / NULLIF(calls, 0), 1)
        AS read_blocks_per_call,
    query
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_exec_time DESC
LIMIT 10;

这些数据需要结合统计时间窗口解释。一次执行很慢但每天只调用一次的报表,与平均耗时不高却每秒调用数百次的接口,优化优先级可能完全不同。

另外,mean_exec_time 是平均执行时间,不是 P95;pg_stat_statements 也不能直接提供完整的执行耗时分布。P95、P99 应从应用链路监控、查询日志或压测采样中获取。

缓存块命中和读取同样不能直接等同于物理磁盘读写。shared_blks_read 表示 PostgreSQL 需要把块读入共享缓冲区,其中部分读取可能由操作系统缓存满足。因此,要把它与操作系统的存储延迟、吞吐量和等待指标一起看。

执行计划负责判断有没有“多做工作”

以下查询作为后续分析对象:订单列表按账户和状态筛选,再按创建时间倒序取前 50 条。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_amount
FROM orders
WHERE account_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC, id DESC
LIMIT 50;

EXPLAIN ANALYZE 会真实执行语句。这里仅针对只读查询,仍可能消耗大量 CPU、I/O 和执行时间,应优先在具有代表性数据的测试环境进行,或在生产低峰设置会话级超时后执行。不要把同样的方式直接用于有副作用的函数或写入语句。

阅读结果时,重点不是有没有索引扫描,而是:

  • 返回 50 行之前,实际检查了多少行。
  • Rows Removed by Filter 是否很大。
  • 是否存在代价较高的排序,是否使用临时文件。
  • 预计行数与实际行数是否严重偏离。
  • 哪个节点消耗了主要时间,缓冲区访问集中在哪里。

对于重复执行的节点,实际行数通常是每次循环的平均值,必须结合 loops 理解总工作量。节点时间还存在包含关系,不能把所有节点耗时直接相加。

返回行数少但扫描范围大,是调整索引的重要线索;出现顺序扫描本身,不是缺少索引的充分证据。

三、影响变量:为什么已有索引,查询仍然慢

索引是否同时支持过滤和排序

对于前面的订单查询,仅有 account_id 索引,可能仍需读取该账户的大量订单,过滤状态后再排序。如果某个账户有几十万条订单,这种执行方式即使使用了索引,也可能很慢。

一个值得验证的候选是:

CREATE INDEX CONCURRENTLY idx_orders_account_status_created_id
ON orders (account_id, status, created_at DESC, id DESC);

该 B-tree 索引把等值过滤字段放在前面,再按查询需要的顺序组织数据。在条件匹配时,数据库有机会较早找到所需的 50 行,而不是读取全部候选记录再排序。

三、影响变量:为什么已有索引,查询仍然慢/索引是否同时支持过滤和排序配图

这条建索引语句仅是候选设计,不应直接复制到生产执行。上线前需要备份或确认可恢复能力,并核对现有索引、可用磁盘空间及复制延迟。并发建索引通常允许正常读写继续,但仍会扫描数据、增加 I/O 和 CPU 消耗,也可能等待长事务;它不能放在事务块内执行。

如果构建失败,应检查是否留下无效索引。若验证后决定撤销,下面的操作仅适用于本次新建、未用于约束的索引:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_account_status_created_id;

删除会使相关查询失去该索引路径,也不能放在事务块内执行。索引删除后只能重新构建,不能像事务回滚一样立即恢复。因此,测试阶段应保留原有索引,不要在收益尚未确认时一并删除旧索引。

数据分布比字段数量更重要

如果业务几乎只查询待处理订单,可以考虑以 status = 'pending' 为条件的部分索引,减少索引体积。但它只适用于查询条件能够证明满足索引谓词的场景。

预备语句采用通用计划时,参数化状态条件未必能匹配固定状态的部分索引。因此,不能只测试一次字面量 SQL 就判断它适合应用实际使用方式。

数据倾斜也会改变结果:一个账户只有 200 条订单,另一个账户有 80 万条,二者可能适合不同执行计划。如果预计只返回几十行,实际却匹配数十万行,应先检查统计信息是否陈旧,以及账户、状态等字段是否存在相关性。必要时评估扩展统计信息,而不是立即强制使用索引。

资源压力可能改变计划收益

工作集能否进入内存,会影响同一个索引的表现。频繁回表、数据分散时,索引扫描可能产生较多随机读取;查询需要读取表中很大比例的数据时,顺序扫描反而可能更合理。

判断资源影响时,应建立关联:

  • 查询缓冲区访问量很大、CPU 升高,可能是扫描和过滤工作过多。
  • 数据文件读取等待增加、存储延迟上升,可能是缓存不足或读取范围过大。
  • 临时文件读写增加,可能是排序或哈希操作溢出。
  • 锁等待突出,应该先定位阻塞事务,不能只归因于索引。
  • 虚拟机 CPU steal 较高,可能存在宿主机争用,需与 SQL 执行成本分开判断。

看到排序落盘也不应直接全局调大 work_mem。它可能被同一查询的多个操作、多个并发会话分别使用,总内存占用远大于单个配置值。

围绕美国服务器上的PostgreSQL数据库负载,A5数据提供Xeon及AMD EPYC物理服务器,涵盖不同计算、内存与存储配置。其中,美国AMD系列提供64GB DDR5、128GB内存及NVMe存储等配置组合,可为热点数据缓存、索引与表数据访问、并发查询处理提供硬件基础;另有双路EPYC方案,承载数据库与接口服务、多任务计算等业务负载。

四、结果解释:索引有效,需要形成完整证据链

下面是一组用于解释判断方法的示例对照数据:约 2000 万行订单,应用与数据库位于同一美国机房,固定每秒 120 次请求,并发上限和读写比例保持一致。两组均在预热后采样。

指标原执行计划候选索引启用后
目标 SQL P95420 毫秒28 毫秒
接口 P95680 毫秒290 毫秒
单次查询缓冲区访问量约 34000 块约 90 块
数据库 CPU 平均占用78%43%
排序临时文件存在未出现
写入事务 P9518 毫秒23 毫秒

这组结果支持索引调整的理由,不只是“SQL 变快了”,而是查询访问范围明显缩小、排序工作减少、CPU 消耗随之下降,机制与指标相互吻合。

但接口 P95 仍有 290 毫秒,说明还有其他延迟来源。下一步应检查连接等待、应用处理和网络路径,而不是继续给这条查询加索引。不同阶段的 P95 通常不能直接相加或相减,上表也不能用来精确推算网络耗时。

写入 P95 从 18 毫秒升到 23 毫秒,则提醒我们:新增索引需要维护,收益必须与写入成本一起验收。如果高峰以写入为主,还应复测更新状态、批量导入和复制延迟。

另一种结果是:新增索引后目标 SQL 只从 35 毫秒降至 30 毫秒,接口仍接近 800 毫秒;同时连接池等待 P95 达到 500 毫秒。此时主要瓶颈在排队。扩大连接池也未必正确,因为更多活动连接可能进一步增加 CPU、锁和内存压力,应先确认数据库是否有剩余处理能力。

还有一种情况是首次测试很慢,重复执行明显变快。若两次执行计划和访问块数基本一致,差异更可能来自缓存预热,而不是索引设计变化。应分别保留预热后和低缓存命中场景的结果,不要在生产服务器上主动清空缓存来制造对照。

五、决策边界:什么时候调整,什么时候停止加索引

可以把判断压缩为以下几类:

观察结果优先判断后续动作
少量结果对应大量扫描、过滤或排序索引匹配不足验证复合索引或部分索引
预计行数与实际行数严重偏离统计信息或数据相关性问题检查统计信息,再比较计划
返回表中较大比例的数据顺序扫描可能合理检查查询范围、聚合方式和返回量
SQL 很快,接口很慢数据库外部延迟检查网络、连接等待和应用处理
锁等待占主要时间事务阻塞处理长事务和访问顺序
大量读取伴随存储延迟上升扫描范围或资源瓶颈同时验证索引收益与存储能力

值得调整索引的条件,是主要耗时确实来自可减少的数据访问或排序工作,候选索引能在代表性参数下稳定降低这些成本,并且写入、空间和运维代价可接受。

不必为了追求 Index Only Scan 把所有输出列都塞进索引。覆盖列会增加索引体积和维护成本;即使包含了查询所需列,是否还需访问表页,也与可见性映射和清理状态有关。高频更新表不一定能持续获得预期收益。

同样,如果慢查询来自深分页,大量 OFFSET 行仍需被处理,索引通常只能缓解,不能消除这类成本。应进一步验证基于排序键的游标分页,而不是认为“再加一个索引”就足够。

六、复测条件:在高峰负载下确认容量收益

索引上线后,应使用相同数据规模、参数分布和读写比例复测,并覆盖高频账户、大账户、不同状态及不同时间范围。对美国服务器上的数据库,还要保持应用访问位置和网络路径一致,避免把链路变化混入数据库收益。

容量判断可以采用分级增加请求速率的方法:每档保持足够采样时间,记录吞吐量、P95/P99、连接池等待、CPU、内存、存储延迟和复制延迟。当请求速率继续增加,而吞吐量不再明显增长、尾延迟持续上升时,系统开始接近当前负载下的处理边界。

六、复测条件:在高峰负载下确认容量收益配图

这时应回到执行计划核对:如果访问量已经显著下降,但瓶颈转移到 CPU、写入或连接排队,就应停止围绕同一条 SQL 继续加索引。一次有价值的索引调整,最终要证明的不是空载下查询更快,而是在业务高峰仍能满足延迟目标,并保留可接受的资源余量和写入能力。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/4148.html

相关推荐