在台湾服务器上运行PostgreSQL 15,如何判断慢查询是否需要增加索引?

发布人:A5数据发布时间:2026-11-14 09:51阅读量:34

在台湾服务器上运行 PostgreSQL 15 时,判断慢查询是否需要增加索引,不能只看一条 SQL 的平均耗时。应先确认延迟主要发生在数据库执行阶段,而不是连接池排队、跨地区网络、TLS 建连或应用线程等待;随后通过执行计划、扫描数据量、缓存命中率、磁盘 I/O 和并发负载,判断索引能否减少实际工作量。

通常可以把“值得加索引”定义为:查询在业务中调用频繁或延迟明显,现有执行计划扫描了大量无关数据,候选索引能够稳定降低 p95/p99 延迟和数据库资源消耗,并且新增索引带来的写入、存储和维护成本处在可接受范围内。仅仅看到执行计划中出现 Seq Scan,或者看到一条查询耗时较高,并不足以直接得出结论。

先确定慢的是数据库还是请求链路

台湾服务器的位置会影响客户端到服务器之间的网络往返时间,但不会改变 PostgreSQL 15 的索引原理。需要把一次请求拆成几个阶段观察:

观测位置通常包含的时间用途
应用端总耗时DNS、连接池取连接、网络、数据库执行、结果处理判断用户实际感受到的延迟
连接池等待时间等待可用数据库连接的时间判断连接池是否过小或数据库连接被长期占用
数据库执行时间PostgreSQL 处理 SQL 和返回结果的时间判断索引、SQL 和执行计划是否是主要问题
网络传输时间请求和结果集在客户端与台湾服务器之间传输的时间判断地域距离、结果集大小和网络质量的影响

例如,应用端 p95 为 380 毫秒,但数据库端执行时间 p95 只有 28 毫秒,剩余时间可能来自连接池或网络。此时增加索引最多只能优化其中的 28 毫秒,不能解决整体延迟。

相反,如果应用端 p95 为 600 毫秒,数据库执行时间也接近 560 毫秒,同时 shared_blks_read 较高、磁盘读延迟明显,且执行计划显示扫描了大量不相关数据,那么索引才有较强的验证价值。

从高负载查询中选择测试对象

如果已经启用 pg_stat_statements,可以先按累计执行时间筛选查询,而不是只按平均耗时排序:

SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_read,
    temp_blks_written,
    LEFT(query, 300) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time 和 mean_exec_time 的单位是毫秒。累计耗时高,说明该查询对整体数据库负载贡献较大;平均耗时高,则说明单次请求可能影响接口响应。两者应结合调用次数、业务优先级和 p95/p99 延迟一起判断。

如果执行 CREATE EXTENSION 时提示扩展不可用,先确认服务器是否允许加载 pg_stat_statements。在 PostgreSQL 15 中,它通常需要出现在 shared_preload_libraries 中:

SHOW shared_preload_libraries;

修改该配置并重启数据库前,应备份配置文件并安排维护窗口。重启会中断现有连接,回滚方式是恢复原配置后再次重启。如果没有权限修改该配置,可以先使用应用访问日志、数据库慢日志和 EXPLAIN 完成单条查询分析。

测试目标:验证索引是否减少了实际工作量

加索引前,先固定一个可重复的测试条件。至少需要记录以下内容:

  • 查询的真实参数分布,而不是只用一个容易命中缓存的参数;
  • 表的大致行数、数据增长速度和关键字段的取值分布;
  • 业务并发数、连接池大小以及读写混合比例;
  • 查询在冷缓存和热缓存下的表现;
  • 当前执行计划、数据库资源曲线和应用端 p95/p99;
  • 现有索引,避免重复建立功能相同的索引。

测试目标不应只写成“让 SQL 更快”,而应尽量具体。例如:

在峰值并发不变、返回行数不变的条件下,将订单列表查询的数据库执行 p95 从约 500 毫秒降到 100 毫秒以内,同时保持写入吞吐下降不超过团队可接受范围。

这个目标同时包含延迟、并发和写入代价,后续才有明确的决策边界。

需要重点观察的指标

指标说明与索引判断的关系
p50、p95、p99中位数和尾部延迟p95/p99 持续升高时,通常比平均值更能反映并发下的问题
calls、总耗时查询调用次数和累计消耗判断查询是否值得投入优化
实际返回行数SQL 最终返回的结果量结合扫描行数判断是否读取了大量无关数据
shared_blks_hit从 PostgreSQL 缓存中读取的数据块数量较高不一定是坏事,要结合执行时间和扫描范围判断
shared_blks_read需要从存储读取的数据块数量高读量且存储延迟高时,减少扫描通常有价值
CPU 使用率数据库进程的计算消耗索引可能减少过滤、排序和表达式计算,但会增加索引维护开销
I/O 等待和磁盘延迟存储是否成为瓶颈顺序扫描或大量随机读都可能造成 I/O 压力
活跃连接和等待事件是否存在锁、连接或资源等待连接池排队、锁等待不能靠普通索引直接解决
临时文件读写排序、哈希操作是否溢出到磁盘索引有时可以避免排序,但不一定能解决所有临时文件问题
写入吞吐和 WAL 增长新索引对 INSERT、UPDATE、DELETE 的影响索引不是免费优化,写入表的索引越多,维护成本越高

应用监控通常可以提供 p95 和 p99,而 pg_stat_statements 更适合提供数据库侧的累计执行情况。两者的采样窗口应尽量一致,否则可能出现应用延迟已经改善,但数据库统计仍混入旧数据的情况。

通过执行计划判断索引有没有切入点

对只读查询,可以先执行不带 ANALYZE 的计划查看:

EXPLAIN (BUFFERS, SETTINGS)
SELECT
    id,
    status,
    total_amount,
    created_at
FROM public.orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= now() - interval '1 day'
ORDER BY created_at DESC
LIMIT 50;

确认查询不会产生业务副作用后,再使用:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, SUMMARY)
SELECT
    id,
    status,
    total_amount,
    created_at
FROM public.orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= now() - interval '1 day'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN ANALYZE 会真正执行查询。对于包含写入、调用外部函数或具有副作用的 SQL,不能直接在生产环境执行;应先使用不带 ANALYZE 的计划,或在经过确认的隔离环境中测试。

阅读计划时,重点不是看到哪一种扫描名称,而是观察以下关系:

  1. 估算行数与实际行数是否接近

如果估算返回 50 行,实际却扫描或返回几十万行,说明统计信息、参数分布或列之间的相关性可能存在问题。

  1. 扫描行数与返回行数是否相差过大

Rows Removed by Filter 很高,表示数据库读取了大量数据后才过滤掉。若过滤条件稳定且选择性较高,索引可能减少这部分工作。

通过执行计划判断索引有没有切入点配图

  1. 是否发生了不必要的排序

如果查询需要 ORDER BY ... LIMIT 50,合适的索引可能直接按所需顺序提供前 50 行,避免扫描大量结果后再排序。

  1. 缓存命中和存储读取是否明显

Buffers: shared read=... 说明需要从存储读取数据;shared hit=... 表示从数据库缓存中读取。缓存命中高并不代表扫描合理,因为大量缓存读取同样会消耗 CPU 和内存带宽。

  1. 执行计划是否受参数影响

某个租户只有几百行,另一个租户有几千万行,同一条参数化 SQL 可能适合不同计划。不能只用一个小租户的参数判断所有业务场景。

执行计划中的 cost 是规划器内部的相对成本,不是毫秒。判断实际效果时,应优先看 actual time、实际行数、缓冲区统计和资源监控。

影响索引判断的几个变量

表规模与过滤条件的选择性

索引的价值取决于它能否显著缩小扫描范围。

如果表只有几千行,顺序扫描可能比随机访问索引和数据页更快。如果查询条件能匹配表中大部分行,索引也未必有优势。规划器可能主动选择 Seq Scan,这不一定表示缺少索引。

例如,status 只有 paid、pending、cancelled 三种值,单独为 status 建立普通 B-tree 索引,通常不能有效支持匹配大多数行的查询。更有价值的候选可能是把租户、时间和状态结合起来,或者针对稳定业务条件建立部分索引。

复合索引的列顺序

对于类似下面的查询:

SELECT
    id,
    status,
    total_amount,
    created_at
FROM public.orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= TIMESTAMP '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

一个可测试的候选索引是:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON public.orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_amount);

这里的思路是把等值过滤列放在前面,再利用时间列完成范围过滤和排序。INCLUDE 可以在部分场景下支持覆盖查询,但它会增加索引体积;即使索引包含了所需列,也不代表一定能使用 Index Only Scan,还要看表页面的可见性信息和数据更新情况。

影响索引判断的几个变量|复合索引的列顺序配图

列顺序不能机械套用。若 status 取值非常集中,且查询更常见的是按租户读取最新订单,那么下面这种候选也可能需要对照测试:

CREATE INDEX CONCURRENTLY idx_orders_tenant_created
ON public.orders (tenant_id, created_at DESC);

如果业务中大量查询都固定使用 status = 'paid',且该条件变化不频繁,可以评估部分索引:

CREATE INDEX CONCURRENTLY idx_orders_paid_tenant_created
ON public.orders (tenant_id, created_at DESC)
WHERE status = 'paid';

部分索引通常更小,但只适用于能够匹配其谓词的查询。建立多个相似索引前,应先检查现有索引和典型查询,避免让写入操作同时维护几套重复结构。

统计信息和数据分布

在批量导入、集中更新或数据增长较快的表上,统计信息可能滞后。可以在业务低峰期更新统计信息:

ANALYZE public.orders;

该操作不会删除数据,但会消耗一定 CPU 和 I/O,执行时间较长的大表应避开业务高峰。更新后重新检查执行计划,如果估算行数明显改善,问题可能主要是统计信息,而不是缺少索引。

当多个过滤列存在相关性时,单列统计信息可能不足。例如某些租户几乎只有 paid 订单,另一些租户的订单状态分布完全不同。可以在确认查询模式后评估扩展统计信息:

CREATE STATISTICS st_orders_tenant_status
    (dependencies, mcv)
ON tenant_id, status
FROM public.orders;

ANALYZE public.orders;

这类统计信息用于帮助规划器估算列之间的关系,不会代替索引。若实际扫描范围本来就无法减少,增加统计信息也不会让顺序扫描自动变成更快的索引扫描。

缓存、存储与并发

同一条查询在热缓存和冷缓存下可能表现不同。第一次执行时读取大量数据,之后重复执行可能全部来自缓存。如果只在热缓存下测试,容易低估存储压力;如果只在冷缓存下测试,也可能高估线上常态。

索引还会改变访问模式:

  • 大范围查询可能从顺序读取变成大量随机读取;
  • 小范围查询通常可以减少读取页数;
  • 高并发下,多个索引扫描可能争用缓存和存储队列;
  • 对频繁更新的表,索引会增加写入维护和 WAL 活动;
  • UPDATE 修改了索引涉及的列时,维护成本通常更明显。

连接池也必须纳入测试。如果数据库执行 p95 只有 40 毫秒,但应用端大量请求在连接池等待,单纯增加索引不会消除排队。可以用下面的查询观察数据库侧连接状态和等待事件:

SELECT
    state,
    wait_event_type,
    wait_event,
    count(*) AS connection_count
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, wait_event_type, wait_event
ORDER BY connection_count DESC;

不要为了消除排队就直接把 max_connections 或连接池调得很大。连接数增加后,CPU 上下文切换、内存使用和锁竞争也可能加重。应先区分“查询执行慢”和“没有及时拿到连接”。

一组可执行的对照测试方法

1. 记录基线

先记录现有索引和表规模:

SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'orders'
ORDER BY indexname;
SELECT
    reltuples::bigint AS estimated_rows,
    pg_size_pretty(pg_relation_size('public.orders')) AS table_size
FROM pg_class
WHERE oid = 'public.orders'::regclass;

然后使用真实参数执行基线查询,保存以下信息:

  • actual time 和 p95/p99;
  • 实际返回行数;
  • shared_blks_hit、shared_blks_read;
  • CPU、I/O 等待、磁盘延迟;
  • 并发请求数、连接池等待时间;
  • 读写吞吐以及其他查询是否受到影响。

2. 只提出一个候选索引

不要一次增加三到五个索引,否则无法判断究竟是哪一个产生了效果,也会同时放大写入成本。根据过滤条件、排序方式和返回列,先选择一个候选。

对大表,优先考虑 CREATE INDEX CONCURRENTLY,它通常可以减少对日常读写的阻塞,但并不代表完全没有影响。该操作可能进行较长时间、增加磁盘读写,并等待长事务结束;它不能放在事务块中执行。

创建前应确认备份或恢复方案、磁盘剩余空间和业务低峰窗口。索引本身不修改业务字段,但会占用额外存储,创建失败时还可能留下无效索引。

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON public.orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_amount);

可以观察创建进度:

SELECT
    pid,
    phase,
    lockers_done,
    lockers_total,
    blocks_done,
    blocks_total
FROM pg_stat_progress_create_index
WHERE relid = 'public.orders'::regclass;

如果创建失败,应先检查是否留下无效索引,再决定是否清理。确认该索引不是约束所依赖的结构后,可以使用并发方式删除:

DROP INDEX CONCURRENTLY IF EXISTS
    public.idx_orders_tenant_status_created;

删除前要确认没有其他重要查询依赖它,并保留变更记录。若新索引已经被业务使用,删除后应重新观察执行计划和尾部延迟。

3. 重新收集计划和负载数据

索引建好后,先用同样的参数重新执行 EXPLAIN (ANALYZE, BUFFERS),再进行与基线相同的并发测试。测试过程要包含预热阶段和稳定阶段,不要把刚启动服务、缓存尚未建立时的数据与稳定运行数据直接混在一起。

可以把结果整理成如下形式。下面的数字只是用于说明分析方法的示例,不代表台湾服务器或某个具体实例的实测结果:

指标增加索引前增加索引后观察
数据库执行 p95620 ms48 ms尾部延迟明显下降
数据库执行 p99980 ms110 ms高并发下仍有改善
每次执行读取块数18,400120扫描范围显著缩小
CPU 使用率82%57%过滤和排序成本下降
I/O 等待21%5%存储压力降低
应用端 p95710 ms135 ms网络和连接池仍占一部分时间
写入吞吐基线约下降 6%需要评估是否可接受
新增索引体积—约 1.8 GB纳入磁盘容量规划

如果数据库执行时间从 620 毫秒降到 48 毫秒,但应用端只从 710 毫秒降到 680 毫秒,说明主要瓶颈可能在连接池、网络或结果处理,而不是数据库扫描。这个结果不应继续盲目增加索引。

结果解释:哪些现象支持增加索引

适合增加索引的情况

以下条件同时满足得越多,增加索引的依据越充分:

  • 查询调用频繁,累计耗时占数据库负载较大;
  • 实际返回行数较少,但执行计划扫描了大量行或数据块;
  • 过滤条件具有稳定且足够的选择性;
  • 查询存在固定的排序和 LIMIT,候选索引可以减少排序或提前停止;
  • 增加索引后,p95/p99、读取块数和 CPU/I/O 至少有两类指标同步改善;
  • 改善在不同参数、冷缓存和热缓存下都具有一定稳定性;
  • 写入吞吐下降、索引体积和创建维护时间处于可接受范围。

团队可以把“p95 至少下降 30%,并且数据库资源没有转移成新的明显瓶颈”作为某类查询的内部验收标准,但这不是所有业务都适用的固定规则。对支付、库存等低延迟场景,绝对延迟目标可能比百分比更重要;对报表场景,则可能更重视吞吐和资源占用。

不适合直接增加普通索引的情况

数据库执行时间很短,应用端仍然很慢。 优先检查连接池取连接、网络往返、TLS、结果集大小和应用序列化。

过滤条件匹配大多数数据。 索引无法显著减少读取量,顺序扫描可能更适合。此时应考虑减少返回列、增加更有区分度的条件或调整查询用途。

计划估算严重错误,但索引并没有明确缺口。 先检查 ANALYZE 是否及时、参数分布是否倾斜,以及是否需要扩展统计信息。直接增加索引可能只是掩盖规划问题。

已有索引被使用,但仍然很慢。 可能是索引选择性不足、回表读取过多、排序或连接操作占主要时间,也可能是磁盘延迟过高。需要看完整计划,而不是再添加一套相似索引。

索引只改善单条查询,却导致写入明显下降。 对高频写入表,新增索引会增加维护成本。应对比查询收益和 INSERT、UPDATE、DELETE 的损失,必要时删除重复或低收益索引。

同一条参数化查询在不同参数下表现差异很大。 需要分别测试小租户、大租户、热点时间段和冷门时间段,确认是否存在通用计划与自定义计划不匹配的情况。不能用单个参数的结果代表全部请求。

不要用 SET enable_seqscan = off 作为“这个索引一定有效”的证据。它只会改变当前会话的规划偏好,不能代表真实负载下的最佳计划,也不应作为生产环境的长期解决方案。

复测条件与容量判断

索引上线后的复测应尽量保持以下条件一致:

  1. 使用相同或相近的数据规模,并覆盖真实参数分布。
  2. 保持相同的并发量、连接池大小和读写比例。
  3. 分别记录冷缓存、预热阶段和稳定阶段。
  4. 同时采集数据库执行时间与应用端总耗时。
  5. 观察查询本身和其他查询,确认没有把 I/O、CPU 或连接等待转移给其他业务。
  6. 在业务增长后再次检查执行计划、统计信息和索引膨胀情况。

容量判断可以用一个简单关系辅助估算:

并发中的数据库操作数 ≈ 每秒请求数 × 单次数据库执行时间(秒)

例如,某查询稳定负载为每秒 80 次,数据库执行时间约 50 毫秒,则理论上约有 4 个查询同时处于数据库执行阶段。若执行时间升到 500 毫秒,即使请求速率不变,执行中的查询数也可能接近 40 个,连接池和数据库等待会迅速放大。索引如果能把执行时间从 500 毫秒降到 50 毫秒,释放的不只是单条查询延迟,还包括连接、CPU 和 I/O 的并发占用。

最终决策可以归纳为四个问题:数据库是否真的在扫描过多数据?候选索引能否减少扫描或排序?改善是否能在实际并发和参数分布下重复出现?新增索引的存储与写入成本是否低于性能收益?四个问题都有明确答案后,再把索引变更纳入正式发布、监控和回滚流程,比根据单次慢查询日志直接创建索引更可靠。

复测条件与容量判断配图

围绕 PostgreSQL 15 数据库及其业务应用,A5idc数据提供中国台湾台北等地区的物理服务器方案,覆盖不同 CPU、存储及网络线路需求,为数据库部署提供硬件与网络资源基础。处理器、内存和存储资源可用于承载数据库计算、缓存及数据读写;面向不同访问地区的线路选择,也可匹配应用请求的网络需求,为数据库业务部署与资源规划提供支持。

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

相关推荐