企业官网建设流程全解析

每日一句正能量

“慢下来，但不要停下来。”
慢，是为了校准方向、恢复觉察，让每一步都扎实。不停，则是守护那份最宝贵的“动量”——一旦完全停止，重新启动所需的心理能量是巨大的。

1. 背景与问题

生产交易系统在业务高峰收到连续告警：接口超时、数据库响应升高、TPS下降。CPU利用率约55%，内存和网络均正常，单一资源指标无法直接解释故障。运维团队采用KDDM（数据库诊断方法）建立“告警→监控→SQL→执行计划→参数→根因”的分析流程，最终定位到索引失效叠加排序落盘导致的综合性能异常。

2. 环境与数据

环境：

• PostgreSQL 16
• Linux 9
• 64 Core / 256GB RAM
• NVMe SSD
• 峰值并发2200

告警指标：

SQL：

SELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_time>=CURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;

优化前执行计划：

Gather Merge Sort Method: external merge Disk: 960MB Parallel Seq Scan on orders Execution Time: 12.63 s

3. 复现过程

1. 收集告警时间窗口的监控数据。
2. 查看KDDM诊断报告，确认等待事件与Top SQL。
3. 使用EXPLAIN (ANALYZE, BUFFERS)分析执行计划。
4. 对照pg_stat_statements、IO监控和业务日志建立证据链。

证据链：

• 告警触发时间与DB Time同步上升。
• Top SQL耗时占比超过45%。
• 排序落盘导致IO Wait增加。
• 缺少复合索引造成全表扫描。

索引失效原因分析：

• 日期范围过大导致索引选择性低：WHERE create_time >= CURRENT_DATE - 30覆盖了最近 30 天的数据，在订单表数据量较大的场景下，该范围可能命中表中绝大部分行。优化器评估后认为走索引回表的代价高于全表扫描，因此放弃索引，选择Parallel Seq Scan。
• 排序字段未包含在索引中：SQL 中ORDER BY SUM(amount) DESC的排序键是聚合结果，而现有索引并未覆盖customer_id与amount的联合结构，无法直接提供有序输出，优化器只能对聚合结果做external merge排序，数据量超出work_mem后落盘，产生 960MB 的磁盘排序，直接推高 IO Wait。

复合索引idx_orders_time_customer(create_time, customer_id)的覆盖逻辑：

• 过滤条件：索引首列create_time与WHERE条件匹配，优化器可通过索引快速定位目标时间范围内的数据，避免全表扫描。
• 排序条件：索引第二列customer_id与GROUP BY customer_id对齐，索引天然按(create_time, customer_id)有序排列，聚合过程可借助索引顺序完成，无需额外排序。
• 消除排序落盘：优化后执行计划由external merge Disk: 960MB变为quicksort Memory: 30MB，排序完全在内存中完成，IO Wait 随之大幅下降。

索引失效排查步骤：

1. 检查索引使用率：通过pg_stat_user_indexes视图查看索引的扫描次数与命中情况，确认idx_orders_time_customer是否被实际使用。

SELECTschemaname,relname,indexrelname,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexesWHERErelname='orders'ORDERBYidx_scanDESC;

若idx_scan长期为 0，说明优化器从未选择该索引，需要进一步分析原因。

2. 对比走索引与全表扫描的代价：使用EXPLAIN分别查看两种执行计划的代价估算，确认优化器为何放弃索引。

-- 强制走索引SETenable_seqscan=off;EXPLAIN(ANALYZE,BUFFERS)SELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_time>=CURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;-- 恢复默认，对比全表扫描RESET enable_seqscan;EXPLAIN(ANALYZE,BUFFERS)SELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_time>=CURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;

对比两次执行计划中的cost、Execution Time与Buffers，即可量化索引回表与全表扫描的代价差异。

3. 验证复合索引是否被优化器采用：创建索引后，重新执行EXPLAIN并观察执行计划中的访问路径。

EXPLAIN(ANALYZE,BUFFERS)SELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_time>=CURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;

若执行计划中出现Index Scan using idx_orders_time_customer且排序方式为quicksort Memory，说明复合索引已被优化器采用，排序落盘问题得到解决。

下面是 KDDM 诊断流程的完整步骤，从告警触发到根因确认形成闭环：

4. 方案实施

新增索引：

CREATEINDEXidx_orders_time_customerONorders(create_time,customer_id);

参数优化：

work_mem=64MB effective_cache_size=96GB random_page_cost=1.1

优化后执行计划：

GroupAggregate -> Index Scan using idx_orders_time_customer Sort Method: quicksort Memory: 30MB Execution Time: 3.41 s

持续监控：

• DB Time
• Top SQL
• 等待事件
• P95/P99
• Buffer Hit

5. 结果对比

优化后告警消失，业务恢复稳定，证据链各项指标相互印证。

下面是优化前后各项关键指标的直观对比：

说明：Top SQL耗时、DB Time、IO Wait、P99延迟均为越低越好，TPS为越高越好。从图中可以直观看到，优化后各项指标均显著改善——Top SQL耗时下降约73%，DB Time下降约44%，IO Wait下降约65%，P99延迟下降约62%，TPS提升约27%，整体性能得到大幅提升。

6. 风险与复盘

风险：

• 仅凭单一告警容易误判。
• 参数调优需结合压测，不可直接套用。
• 索引增加会带来写入成本，应评估维护开销。

复盘建议：

1. 建立统一诊断流程，从告警开始逐步缩小范围。
2. 结合监控、日志、SQL、执行计划和等待事件形成完整证据链。
3. 每次优化保留前后参数、执行计划和压测结果，方便回溯。
4. 定期开展巡检，持续跟踪DB Time、Top SQL及等待事件变化趋势。

本文通过完整案例展示了KDDM诊断流程，从告警到根因定位形成闭环，结合执行计划、监控数据、参数调整和结果对比，为生产环境数据库综合故障诊断提供参考。

转载自：https://blog.csdn.net/u014727709/article/details/164124633
欢迎 👍点赞✍评论⭐收藏，欢迎指正

7. 常见问题 FAQ

Q1：为什么 work_mem 设置为 64MB 而不是更大？

work_mem是每个排序、哈希等操作可使用的内存上限，并非全局共享。在峰值并发 2200 的场景下，若同时有大量会话执行排序操作，过大的work_mem会成倍放大内存占用，极易触发 OOM。64MB 是在「排序不落盘」与「整体内存可控」之间取得的平衡——本案例排序数据量约 30MB，64MB 已足够容纳，同时为并发会话预留了充足的内存余量。实际调优时应结合压测逐步上调，并监控内存水位，而非盲目加大。

Q2：如果索引仍然未被使用，下一步排查方向是什么？

若创建复合索引后执行计划仍走全表扫描，建议按以下方向排查：

• 检查统计信息是否过期：执行ANALYZE orders;刷新统计信息，优化器依赖统计信息估算代价，过期统计会导致错误选择。
• 确认查询写法是否匹配索引：例如对索引列使用函数或隐式类型转换（如create_time::date），会导致索引失效，应改写为create_time >= CURRENT_DATE - 30这类可匹配索引首列的写法。
• 对比强制走索引与全表扫描的真实代价：用SET enable_seqscan = off;强制走索引后对比Execution Time与Buffers，若索引回表代价确实更高，说明该查询本身不适合此索引，需重新设计索引或改写 SQL。
• 检查索引维护状态：通过pg_stat_user_indexes观察idx_scan是否持续为 0，并结合pg_stat_all_indexes确认索引是否因大量写入而膨胀，必要时REINDEX重建。

Q3：如何判断排序是否落盘？

最直接的方式是查看执行计划中的Sort Method字段：

• quicksort Memory: 30MB：排序完全在内存中完成，未落盘。
• external merge Disk: 960MB：数据量超出work_mem，排序已落盘到临时文件，会显著推高 IO Wait。

此外，还可以通过EXPLAIN (ANALYZE, BUFFERS)观察Buffers: temp read/written是否有非零值，或监控pg_stat_database中的temp_files与temp_bytes字段，两者持续增长即说明存在排序落盘。

转载自：https://blog.csdn.net/u014727709/article/details/164124633
欢迎 👍点赞✍评论⭐收藏，欢迎指正