文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程4. 方案实施5. 结果对比6. 风险与复盘7. 常见问题与回退方案7.1 执行计划回退7.2 work_mem 设置不当导致内存溢出8. 常见问题 FAQ每日一句正能量“人生的画布是空白的你的每个选择都是一抹颜色。”不要恐惧下笔也不要轻率涂抹。你是自己作品的唯一创作者。1. 背景与问题数据库性能优化完成后经常会出现“感觉变快了”却无法量化证明的情况。没有统一基线、缺少对照实验或未执行回归测试都可能导致错误判断优化收益。本文以订单系统查询优化为例介绍如何建立可复现的验证体系确保优化结果真实、稳定且可持续。2. 环境与数据环境PostgreSQL 16Linux 932 Core / 128GB RAMNVMe SSD数据规模1.5亿行测试SQLSELECTuser_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYuser_idORDERBYSUM(amount)DESCLIMIT100;优化前执行计划Gather Merge - Sort Sort Method: external merge Disk: 780MB Execution Time: 9.34 s基线指标指标优化前TPS18200P99136msSQL耗时9.34sBuffer Hit96.5%3. 复现过程建立统一测试环境固定硬件、数据库版本、参数和数据模板连续执行五轮压测记录中位数保存EXPLAIN(ANALYZE,BUFFERS)、pg_stat_statements及系统IO数据作为后续对照基线。4. 方案实施新增复合索引并调整work_memCREATEINDEXidx_orders_time_userONorders(create_time,user_id);work_mem64MB优化后执行计划GroupAggregate - Index Scan using idx_orders_time_user Sort Method: quicksort Memory: 28MB Execution Time: 3.18 s实施验证基线对照A/B环境验证回归测试覆盖核心业务连续观察72小时监控5. 结果对比指标优化前优化后SQL耗时9.34s3.18sTPS1820023600P99136ms61msBuffer Hit96.5%99.1%CPU利用率68%54%下图以柱状图形式直观对比优化前后三个核心指标的变化优化前后性能对比SQL耗时(s)TPS(万)P99(ms)240002200020000180001600014000120001000080006000400020000数值说明为便于在同一坐标系下展示TPS 原始值18200 / 23600已按万为单位换算为 1.82 / 2.36SQL 耗时单位为秒P99 单位为毫秒。蓝色柱为优化前橙色柱为优化后。从柱状图可以更直观地看出SQL 耗时由 9.34s 降至 3.18s降幅约66%TPS由 18200 提升至 23600增长约30%P99 延迟由 136ms 降至 61ms降幅约55%。三个指标均呈现明显改善其中响应速度与长尾延迟的优化幅度最为突出。从对比数据可以看出本次优化带来了显著的性能提升SQL 耗时从 9.34s 降至 3.18s降幅约66%查询响应速度大幅提升TPS从 18200 提升至 23600吞吐量增长约30%系统承载能力明显增强P99 延迟从 136ms 降至 61ms降幅约55%长尾请求的稳定性得到有效改善。整体来看复合索引与 work_mem 调整的组合方案在吞吐量、响应速度和稳定性三个维度均取得了可量化的收益且执行计划保持一致未引入新的性能回退。监控显示TPS持续稳定慢SQL数量下降执行计划保持一致未出现业务回退。6. 风险与复盘风险未建立基线无法证明优化收益。测试环境与生产差异过大会影响结论。未做回归测试可能引入新的性能问题。复盘建议优化前必须保存参数、执行计划和监控快照。使用对照组或A/B验证避免偶然因素影响。结合TPS、P95/P99、IO、CPU、等待事件形成完整证据链。优化上线后持续观察确认执行计划未发生回退。通过基线、对照实验和回归测试可以将“性能变好了”转化为可量化、可复现、可审计的证据为生产优化提供可靠依据。7. 常见问题与回退方案优化上线后并非一劳永逸执行计划回退、参数设置不当等问题仍可能出现。下面列出两类高频问题及对应的排查与回退步骤。下面是执行计划回退时的排查与回退决策流程否是是是否否否是发现 SQL 耗时回升EXPLAIN 查看执行计划是否走回 Gather Merge / 全表扫描执行计划正常检查其他瓶颈检查统计信息是否过期last_analyze 是否过旧执行 ANALYZE orders 刷新统计信息执行计划是否恢复观察并回归测试检查索引使用情况idx_scan 是否持续为 0索引仍被使用检查数据分布变化确认索引失效DROP INDEX 删除复合索引重新执行基线压测确认耗时回到优化前水平评估是否调整索引设计决策要点先刷新统计信息排除优化器误判再确认索引是否真正失效只有确认索引失效时才删除复合索引删除后务必重新压测验证。7.1 执行计划回退现象优化后运行一段时间SQL 耗时回升EXPLAIN显示重新走回Gather Merge - Sort或全表扫描复合索引未被使用。排查命令-- 查看当前 SQL 实际执行计划EXPLAIN(ANALYZE,BUFFERS)SELECTuser_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYuser_idORDERBYSUM(amount)DESCLIMIT100;-- 检查索引是否仍存在且可用SELECTindexrelname,idx_scan,idx_tup_readFROMpg_stat_user_indexesWHERErelnameorders;-- 查看表统计信息是否过期SELECTrelname,last_analyze,last_autoanalyzeFROMpg_stat_user_tablesWHERErelnameorders;回退操作步骤若统计信息过期导致优化器误判先执行ANALYZE orders;刷新统计信息观察执行计划是否恢复。若仍回退检查是否因数据分布变化导致索引选择性下降可对比pg_stat_user_indexes中idx_scan是否持续为 0。确认索引确实失效时回退方案为删除复合索引并恢复原状DROPINDEXIFEXISTSidx_orders_time_user;回退后重新执行基线压测确认 SQL 耗时回到优化前水平再评估是否调整索引设计。7.2 work_mem 设置不当导致内存溢出现象work_mem64MB在并发较高时每个排序/哈希操作都可能占用 64MB多会话叠加导致内存压力骤增出现 OOM 或 swap 抖动。下面是 work_mem 内存溢出时的排查与回退决策流程否是是否发现 OOM / swap 抖动检查 temp_files 与内存监控是否超过安全阈值内存压力正常检查其他瓶颈回退 work_mem 至 4MB重载配置内存压力是否回落观察并回归测试进一步降低 work_mem 或限制并行度重新压测验证评估并重新调整参数决策要点先通过pg_stat_database.temp_files与系统内存监控确认是否真的超过安全阈值再将会话级work_mem回退至 4MB 并重载配置只有确认内存压力回落后才结合并发数重新评估参数。排查命令-- 查看当前 work_mem 配置SHOWwork_mem;-- 查看临时文件落盘情况临时文件过多说明 work_mem 偏小SELECTdatname,temp_files,temp_bytesFROMpg_stat_databaseWHEREdatnamecurrent_database();-- 查看活跃会话内存相关等待事件SELECTpid,wait_event_type,wait_event,stateFROMpg_stat_activityWHEREstateactive;回退操作步骤若出现 OOM 风险立即将会话级参数调回安全值SETwork_mem4MB;若需持久化回退修改配置文件并重载work_mem4MB# 重载配置无需重启pg_ctl reload观察pg_stat_database.temp_files与系统内存监控确认内存压力回落。若确认 64MB 确实过大可结合并发数重新评估例如在 32 Core / 128GB 环境下将work_mem调整为 16MB~32MB 并配合max_parallel_workers_per_gather限制并行度再逐步压测验证。8. 常见问题 FAQQ1复合索引是否适用于所有查询不是。复合索引(create_time, user_id)主要针对按时间范围过滤并按用户聚合的查询场景。若查询条件不包含create_time或过滤列与索引列顺序不匹配优化器可能不会使用该索引。建议结合EXPLAIN验证实际执行计划避免盲目建索引。Q2work_mem 设置多大合适没有固定值需结合并发数与内存总量评估。经验公式work_mem ≈ 可用内存 / (预估并发排序/哈希操作数)。例如 32 Core / 128GB 环境下若并发约 50可先设为 16MB~32MB再通过pg_stat_database.temp_files观察临时文件落盘情况逐步调整并压测验证。Q3如何判断优化是否有效建立统一基线对比优化前后的核心指标SQL 耗时、TPS、P99/P95 延迟、Buffer Hit、CPU 利用率等。建议连续执行多轮压测取中位数并结合EXPLAIN (ANALYZE, BUFFERS)确认执行计划是否按预期走索引同时观察 72 小时以上监控确认无回退。Q4优化后执行计划回退了怎么办先执行ANALYZE orders;刷新统计信息排除优化器误判若仍回退检查pg_stat_user_indexes中idx_scan是否持续为 0确认索引是否失效。确认失效后再删除复合索引并重新压测回到优化前水平后再评估是否调整索引设计。Q5索引会拖慢写入性能吗会。每次 INSERT/UPDATE/DELETE 都需要同步维护索引索引越多写入开销越大。若业务以写入为主或索引长期未被使用idx_scan持续为 0建议评估删除该索引避免为查询优化牺牲写入性能。转载自https://blog.csdn.net/u014727709/article/details/164124885欢迎 点赞✍评论⭐收藏欢迎指正 性能优化复盘：如何证明优化真实有效
文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程4. 方案实施5. 结果对比6. 风险与复盘7. 常见问题与回退方案7.1 执行计划回退7.2 work_mem 设置不当导致内存溢出8. 常见问题 FAQ每日一句正能量“人生的画布是空白的你的每个选择都是一抹颜色。”不要恐惧下笔也不要轻率涂抹。你是自己作品的唯一创作者。1. 背景与问题数据库性能优化完成后经常会出现“感觉变快了”却无法量化证明的情况。没有统一基线、缺少对照实验或未执行回归测试都可能导致错误判断优化收益。本文以订单系统查询优化为例介绍如何建立可复现的验证体系确保优化结果真实、稳定且可持续。2. 环境与数据环境PostgreSQL 16Linux 932 Core / 128GB RAMNVMe SSD数据规模1.5亿行测试SQLSELECTuser_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYuser_idORDERBYSUM(amount)DESCLIMIT100;优化前执行计划Gather Merge - Sort Sort Method: external merge Disk: 780MB Execution Time: 9.34 s基线指标指标优化前TPS18200P99136msSQL耗时9.34sBuffer Hit96.5%3. 复现过程建立统一测试环境固定硬件、数据库版本、参数和数据模板连续执行五轮压测记录中位数保存EXPLAIN(ANALYZE,BUFFERS)、pg_stat_statements及系统IO数据作为后续对照基线。4. 方案实施新增复合索引并调整work_memCREATEINDEXidx_orders_time_userONorders(create_time,user_id);work_mem64MB优化后执行计划GroupAggregate - Index Scan using idx_orders_time_user Sort Method: quicksort Memory: 28MB Execution Time: 3.18 s实施验证基线对照A/B环境验证回归测试覆盖核心业务连续观察72小时监控5. 结果对比指标优化前优化后SQL耗时9.34s3.18sTPS1820023600P99136ms61msBuffer Hit96.5%99.1%CPU利用率68%54%下图以柱状图形式直观对比优化前后三个核心指标的变化优化前后性能对比SQL耗时(s)TPS(万)P99(ms)240002200020000180001600014000120001000080006000400020000数值说明为便于在同一坐标系下展示TPS 原始值18200 / 23600已按万为单位换算为 1.82 / 2.36SQL 耗时单位为秒P99 单位为毫秒。蓝色柱为优化前橙色柱为优化后。从柱状图可以更直观地看出SQL 耗时由 9.34s 降至 3.18s降幅约66%TPS由 18200 提升至 23600增长约30%P99 延迟由 136ms 降至 61ms降幅约55%。三个指标均呈现明显改善其中响应速度与长尾延迟的优化幅度最为突出。从对比数据可以看出本次优化带来了显著的性能提升SQL 耗时从 9.34s 降至 3.18s降幅约66%查询响应速度大幅提升TPS从 18200 提升至 23600吞吐量增长约30%系统承载能力明显增强P99 延迟从 136ms 降至 61ms降幅约55%长尾请求的稳定性得到有效改善。整体来看复合索引与 work_mem 调整的组合方案在吞吐量、响应速度和稳定性三个维度均取得了可量化的收益且执行计划保持一致未引入新的性能回退。监控显示TPS持续稳定慢SQL数量下降执行计划保持一致未出现业务回退。6. 风险与复盘风险未建立基线无法证明优化收益。测试环境与生产差异过大会影响结论。未做回归测试可能引入新的性能问题。复盘建议优化前必须保存参数、执行计划和监控快照。使用对照组或A/B验证避免偶然因素影响。结合TPS、P95/P99、IO、CPU、等待事件形成完整证据链。优化上线后持续观察确认执行计划未发生回退。通过基线、对照实验和回归测试可以将“性能变好了”转化为可量化、可复现、可审计的证据为生产优化提供可靠依据。7. 常见问题与回退方案优化上线后并非一劳永逸执行计划回退、参数设置不当等问题仍可能出现。下面列出两类高频问题及对应的排查与回退步骤。下面是执行计划回退时的排查与回退决策流程否是是是否否否是发现 SQL 耗时回升EXPLAIN 查看执行计划是否走回 Gather Merge / 全表扫描执行计划正常检查其他瓶颈检查统计信息是否过期last_analyze 是否过旧执行 ANALYZE orders 刷新统计信息执行计划是否恢复观察并回归测试检查索引使用情况idx_scan 是否持续为 0索引仍被使用检查数据分布变化确认索引失效DROP INDEX 删除复合索引重新执行基线压测确认耗时回到优化前水平评估是否调整索引设计决策要点先刷新统计信息排除优化器误判再确认索引是否真正失效只有确认索引失效时才删除复合索引删除后务必重新压测验证。7.1 执行计划回退现象优化后运行一段时间SQL 耗时回升EXPLAIN显示重新走回Gather Merge - Sort或全表扫描复合索引未被使用。排查命令-- 查看当前 SQL 实际执行计划EXPLAIN(ANALYZE,BUFFERS)SELECTuser_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYuser_idORDERBYSUM(amount)DESCLIMIT100;-- 检查索引是否仍存在且可用SELECTindexrelname,idx_scan,idx_tup_readFROMpg_stat_user_indexesWHERErelnameorders;-- 查看表统计信息是否过期SELECTrelname,last_analyze,last_autoanalyzeFROMpg_stat_user_tablesWHERErelnameorders;回退操作步骤若统计信息过期导致优化器误判先执行ANALYZE orders;刷新统计信息观察执行计划是否恢复。若仍回退检查是否因数据分布变化导致索引选择性下降可对比pg_stat_user_indexes中idx_scan是否持续为 0。确认索引确实失效时回退方案为删除复合索引并恢复原状DROPINDEXIFEXISTSidx_orders_time_user;回退后重新执行基线压测确认 SQL 耗时回到优化前水平再评估是否调整索引设计。7.2 work_mem 设置不当导致内存溢出现象work_mem64MB在并发较高时每个排序/哈希操作都可能占用 64MB多会话叠加导致内存压力骤增出现 OOM 或 swap 抖动。下面是 work_mem 内存溢出时的排查与回退决策流程否是是否发现 OOM / swap 抖动检查 temp_files 与内存监控是否超过安全阈值内存压力正常检查其他瓶颈回退 work_mem 至 4MB重载配置内存压力是否回落观察并回归测试进一步降低 work_mem 或限制并行度重新压测验证评估并重新调整参数决策要点先通过pg_stat_database.temp_files与系统内存监控确认是否真的超过安全阈值再将会话级work_mem回退至 4MB 并重载配置只有确认内存压力回落后才结合并发数重新评估参数。排查命令-- 查看当前 work_mem 配置SHOWwork_mem;-- 查看临时文件落盘情况临时文件过多说明 work_mem 偏小SELECTdatname,temp_files,temp_bytesFROMpg_stat_databaseWHEREdatnamecurrent_database();-- 查看活跃会话内存相关等待事件SELECTpid,wait_event_type,wait_event,stateFROMpg_stat_activityWHEREstateactive;回退操作步骤若出现 OOM 风险立即将会话级参数调回安全值SETwork_mem4MB;若需持久化回退修改配置文件并重载work_mem4MB# 重载配置无需重启pg_ctl reload观察pg_stat_database.temp_files与系统内存监控确认内存压力回落。若确认 64MB 确实过大可结合并发数重新评估例如在 32 Core / 128GB 环境下将work_mem调整为 16MB~32MB 并配合max_parallel_workers_per_gather限制并行度再逐步压测验证。8. 常见问题 FAQQ1复合索引是否适用于所有查询不是。复合索引(create_time, user_id)主要针对按时间范围过滤并按用户聚合的查询场景。若查询条件不包含create_time或过滤列与索引列顺序不匹配优化器可能不会使用该索引。建议结合EXPLAIN验证实际执行计划避免盲目建索引。Q2work_mem 设置多大合适没有固定值需结合并发数与内存总量评估。经验公式work_mem ≈ 可用内存 / (预估并发排序/哈希操作数)。例如 32 Core / 128GB 环境下若并发约 50可先设为 16MB~32MB再通过pg_stat_database.temp_files观察临时文件落盘情况逐步调整并压测验证。Q3如何判断优化是否有效建立统一基线对比优化前后的核心指标SQL 耗时、TPS、P99/P95 延迟、Buffer Hit、CPU 利用率等。建议连续执行多轮压测取中位数并结合EXPLAIN (ANALYZE, BUFFERS)确认执行计划是否按预期走索引同时观察 72 小时以上监控确认无回退。Q4优化后执行计划回退了怎么办先执行ANALYZE orders;刷新统计信息排除优化器误判若仍回退检查pg_stat_user_indexes中idx_scan是否持续为 0确认索引是否失效。确认失效后再删除复合索引并重新压测回到优化前水平后再评估是否调整索引设计。Q5索引会拖慢写入性能吗会。每次 INSERT/UPDATE/DELETE 都需要同步维护索引索引越多写入开销越大。若业务以写入为主或索引长期未被使用idx_scan持续为 0建议评估删除该索引避免为查询优化牺牲写入性能。转载自https://blog.csdn.net/u014727709/article/details/164124885欢迎 点赞✍评论⭐收藏欢迎指正