文章目录每日一句正能量1. 背景与问题报表只返回几十行为什么数据库却要处理3亿行2. 环境与数据构造一个典型的经营报表场景2.1 先建立性能基线2.2 GROUP BY首先要看“分组基数”2.3 HashAggregate与GroupAggregate不是简单“快慢关系”HashAggregateGroupAggregate3. 复现过程一条96秒的GROUP BY真正慢在哪里3.1 基线执行计划3.2 先看统计信息3.3 work_mem实验4. 方案实施先减少聚合输入再讨论聚合参数4.1 第一步过滤前推4.2 第二步联合索引真正价值是“缩小输入”4.3 第三步只把必要列送进聚合4.4 第四步预聚合——真正的大杀器4.5 预聚合不是“随便先GROUP BY”可加指标半可加/不可直接加4.6 AVG不要简单“平均的平均”4.7 COUNT(DISTINCT)是经营报表里的高风险指标4.8 第五步考虑Group Key顺序与已有序输入4.9 第六步表达式可能破坏索引顺序利用4.10 第七步预聚合表比每次实时聚合更适合稳定经营报表4.11 第八步不要忽略HashAggregate与GroupAggregate对照实验4.12 第九步监控临时文件4.13 第十步高并发下必须做内存预算5. 结果对比从96秒到2.9秒最大的收益并不是调内存E0原SQLE1ANALYZEE2work_mem256MBE3增加过滤联合索引E4预聚合E5预聚合 合理索引5.1 示例汇总5.2 Buffer Read比耗时更有复用价值5.3 结果一致性要专门验证5.4 新索引要测试写入成本5.5 预聚合表还要验证刷新成本6. 风险与复盘GROUP BY调优最容易“跑得快但口径错”6.1 风险一预聚合粒度选错6.2 风险二AVG的平均值陷阱6.3 风险三盲目提高work_mem6.4 风险四强制GroupAggregate6.5 风险五索引列顺序拍脑袋6.6 风险六汇总表数据延迟6.7 风险七缓存计划与日期参数推荐调优顺序回退方案最终复盘附录 A最小计划诊断附录 B诊断性Hash聚合对照附录 C会话级内存实验附录 D最低验收门禁每日一句正能量感情因珍惜而美好友情因真诚而长久亲情因相依而温暖。一份感情的美好不在于它本身多么华丽而在于我们是否用心呵护。长久的友情是经过时间淬炼后那份信任依然毫发无伤。家人的温暖来自于彼此的依靠和陪伴来自这份无需言说的“在场”。主题聚合优化 / GROUP BY / 经营报表重点HashAggregate、GroupAggregate、预聚合、联合索引、统计信息、work_mem、临时文件、执行计划、P95/P99 与结果校验适用场景KingbaseES 上的经营日报、月报、销售分析、门店排名、客户分群、财务汇总等大数据量聚合场景。1. 背景与问题报表只返回几十行为什么数据库却要处理3亿行经营报表类 SQL 有一个非常典型的特点最终结果很小但输入数据极大例如一个“区域月销售汇总”最终只返回 30 个区域 × 12 个月大约几百行。SQL 看起来也很普通SELECTs.region_id,date_trunc(month,d.biz_date)ASmonth_no,COUNT(DISTINCTd.order_id)ASorder_cnt,SUM(d.amount)ASsales_amountFROMsales_detail dJOINshop_dim sONs.shop_idd.shop_idWHEREd.biz_date:start_dateANDd.biz_date:end_dateANDd.status1GROUPBYs.region_id,date_trunc(month,d.biz_date);最终只返回几百行但数据库可能执行3亿明细行扫描 → 大规模Join → 3亿行进入Aggregate → Hash表或Sort → 最终输出几百行真正决定性能的是进入聚合节点的数据量而不是聚合完成后的结果量KingbaseES 官方执行计划文档把聚合节点区分为HashAggregate、GroupAggregate等HashAggregate使用哈希桶对无序数据分组GroupAggregate基于有序输入执行聚合。官方性能参数文档进一步指出Hash 聚合在分组唯一值较多时内存消耗会明显增加而 GroupAggregate 的内存表现相对稳定但如果下层输入没有排序则可能付出额外 Sort 成本。因此经营报表优化不能只问work_mem要调多大应该先问为什么要让3亿行进入聚合 能不能让它先变成3000万 能不能先变成40万 GROUP BY之前是否做了不必要的大Join 是否可以利用索引缩小扫描范围 分组基数估算是否准确这篇文章的核心观点是GROUP BY真正高价值的优化通常不是“让聚合节点跑快一点”而是让尽可能少、尽可能窄、尽可能符合业务粒度的数据进入聚合节点。2. 环境与数据构造一个典型的经营报表场景示例环境数据库 KingbaseES V9 事实表 sales_detail 数据量 3亿行 日新增 500万~800万 维表 shop_dim 门店 12万 报表 区域月销售额 区域订单数 门店经营汇总事实表CREATETABLEsales_detail(idBIGINTPRIMARYKEY,shop_idBIGINT,product_idBIGINT,biz_dateDATE,amountNUMERIC(18,2),order_idBIGINT,statusINT);维表CREATETABLEshop_dim(shop_idBIGINTPRIMARYKEY,region_idBIGINT,shop_nameVARCHAR(200),statusINT);2.1 先建立性能基线不要只记SQL耗时96秒至少保存SQL指纹 日期参数 实际扫描行数 Aggregate输入行数 Group估算行数 Group实际行数 Sort Method Hash内存/Batches Temp GB Buffers hit/read CPU IO P50/P95/P99 最终结果行数原因很简单如果优化后从96s →20s但不知道是扫描少了 Hash少落盘了 还是缓存碰巧更热这个结论无法复用。2.2 GROUP BY首先要看“分组基数”假设GROUPBYregion_id实际只有30组和GROUPBYcustomer_id实际3000万组虽然语法都是 GROUP BY但 HashAggregate 的内存行为完全不同。官方 SQL 调优文档特别提到HashAggregate 受到 GROUP BY 字段唯一值数量的明显影响唯一值越多需要维护的 Hash 状态越多内存和性能压力都会上升。因此计划里estimated groups actual groups也需要和普通行数估算一样重点关注。2.3 HashAggregate与GroupAggregate不是简单“快慢关系”HashAggregate优势输入无需有序 通常对低/中等分组基数效率高风险分组基数高 聚合状态多 内存不足 临时文件/批处理成本GroupAggregate优势如果输入已经有序 可以按组流式处理 内存行为比较稳定风险如果输入无序 需要Sort于是可能变成Seq Scan → Sort 3亿行 → GroupAggregate新的瓶颈只是从Hash转成Sort3. 复现过程一条96秒的GROUP BY真正慢在哪里3.1 基线执行计划EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...假设核心计划HashAggregate Group Key: s.region_id, date_trunc(...) - Hash Join - Seq Scan sales_detail - Hash - Seq Scan shop_dim监控sales_detail actual rows: 300,000,000 Aggregate input: 300,000,000 estimated groups: 5,000 actual groups: 28,000 temp: 18GB P95: 96s这里至少有三个问题1. 聚合输入太大 2. 分组基数低估 3. Hash可能在大量写临时文件不能直接进入work_mem调优3.2 先看统计信息官方 SQL 优化建议指出如果表统计信息过旧、缺少多列统计或数据严重倾斜优化器可能选择非最优计划。执行ANALYZEsales_detail;ANALYZEshop_dim;再次执行。假设estimated groups: 26,000 actual: 28,000估算明显改善。P9596s →81s说明统计确实有问题但输入仍然3亿行所以只能得到有限收益。这一步很关键估算正确不等于数据量合理。3.3 work_mem实验固定SQL 参数 数据快照只调整会话级SETLOCALwork_mem32MB;SETLOCALwork_mem128MB;SETLOCALwork_mem256MB;示例32MB: Temp15GB P9581s 128MB: Temp8GB P9563s 256MB: Temp5.2GB P9551s可以证明内存不足确实存在但 256MB 仍然要处理3亿行所以继续堆内存的边际收益会下降。而且官方文档明确提醒work_mem是每个内部 Sort/Hash 操作的工作内存复杂 SQL 可以同时拥有多个节点多会话还可能并发执行因此单 SQL 最佳参数不能直接变成生产全局参数。4. 方案实施先减少聚合输入再讨论聚合参数4.1 第一步过滤前推原 SQL3亿历史明细实际报表只查最近一个月 status1如果没有合适的过滤访问路径就会扫描太多数据。可以评估CREATEINDEXidx_sales_date_status_shopONsales_detail(biz_date,status,shop_id);或者在status选择性更高的系统CREATEINDEXidx_sales_status_date_shopONsales_detail(status,biz_date,shop_id);不要机械同时创建。真实选择要根据status分布 日期范围 写入成本 索引体积做实验。4.2 第二步联合索引真正价值是“缩小输入”假设索引优化后进入聚合的明细 3亿 →4200万P9551s →19s这时候收益不是因为索引让GROUP BY本身变快而是索引让前面的扫描少了2.58亿行这也是索引设计里一个很重要的工程原则对于 GROUP BY 查询索引最优先的目标通常是把不参与本次报表的数据挡在聚合节点之前。4.3 第三步只把必要列送进聚合不要SELECTd.*,s.*然后再聚合。聚合阶段真正需要的可能只有shop_id biz_date order_id amount行越宽扫描 缓存 Hash Sort成本都越高。因此中间结果要尽量窄。4.4 第四步预聚合——真正的大杀器原来3亿订单明细如果业务最终按区域 月份汇总可以先考虑最低安全粒度shop_id biz_date预聚合。WITHdaily_shopAS(SELECTshop_id,biz_date,COUNT(DISTINCTorder_id)ASorder_cnt,SUM(amount)ASsales_amountFROMsales_detailWHEREbiz_date:start_dateANDbiz_date:end_dateANDstatus1GROUPBYshop_id,biz_date)SELECTs.region_id,date_trunc(month,d.biz_date)ASmonth_no,SUM(d.order_cnt)ASorder_cnt,SUM(d.sales_amount)ASsales_amountFROMdaily_shop dJOINshop_dim sONs.shop_idd.shop_idGROUPBYs.region_id,date_trunc(month,d.biz_date);示例3亿明细 → 40万shop-day聚合行 → 再Join维表 → 最终区域月聚合这会同时降低Join输入 Hash输入 Sort输入 Buffer Read 临时文件4.5 预聚合不是“随便先GROUP BY”这是最容易犯错的地方。例如COUNT(DISTINCT order_id)如果一个order_id可能跨多个shop_id 或多个biz_date那么先分别计算COUNT(DISTINCT)再SUM()可能重复计数。所以预聚合必须先证明中间粒度满足业务可加性。可加指标通常SUM(amount) COUNT(rows)比较容易二次聚合。半可加/不可直接加例如COUNT(DISTINCT user_id) AVG() 余额 库存快照 去重客户数必须重新设计。所以预聚合的第一条件不是性能而是业务指标可加性。4.6 AVG不要简单“平均的平均”错误区域AVG 各门店AVG再AVG如果门店样本数不同结果会错。正确预聚合保存SUM(value) COUNT(value)最终SUM(sum_value) / SUM(count_value)这类语义问题必须进入数据校验。4.7 COUNT(DISTINCT)是经营报表里的高风险指标原COUNT(DISTINCTcustomer_id)预聚合后不能简单SUM(各门店distinct customer)因为同一个客户可能跨门店。可选方式更高粒度去重 独立客户集合 bitmap/近似算法若业务允许 专门汇总层但不能为了快改变口径。4.8 第五步考虑Group Key顺序与已有序输入如果查询经常GROUPBYshop_id,biz_date并且前面的访问路径已经按shop_id,biz_date有序那么优化器有机会选择GroupAggregate减少额外 Sort/Hash 成本。但是否生效必须看实际计划不能因为索引列顺序相同就假设一定不用Sort还要考虑WHERE条件 扫描方向 函数表达式 Collation NULL排序4.9 第六步表达式可能破坏索引顺序利用例如GROUPBYdate_trunc(month,biz_date)普通biz_date索引不一定直接等价于date_trunc(month)如果这个报表极高频可以评估表达式索引 生成列 预聚合月字段但需要结合 KingbaseES 实际版本、表达式索引支持和写成本测试。4.10 第七步预聚合表比每次实时聚合更适合稳定经营报表如果报表每天8点被几千人打开而数据只要求T5分钟就没有必要每个请求都扫描几千万甚至几亿明细可以构建sales_shop_day_summary按shop_id biz_date增量维护。查询汇总表 →维表 →最终GROUP BY这本质上是把CPU成本从查询时 搬到数据准备时并换取稳定低延迟4.11 第八步不要忽略HashAggregate与GroupAggregate对照实验KingbaseES 提供enable_hashagg参数用于影响优化器对 HashAggregate 的选择。在测试会话可以BEGIN;SETLOCALenable_hashaggoff;EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;ROLLBACK;用来比较HashAggregate vs GroupAggregate Sort但不要把全局关闭HashAggregate作为永久修复。因为低分组基数、无序输入时HashAggregate本来可能是最优方案。4.12 第九步监控临时文件GROUP BY慢时临时文件可能来自Hash Sort 中间结果可以在短时间诊断窗口ALTERSYSTEMSETlog_temp_filesTO0;SELECTSYS_RELOAD_CONF();结合PID 时间 SQL 计划判断。不要看到Temp 18GB就直接断定全部是HashAggregate4.13 第十步高并发下必须做内存预算单条报表work_mem256MB运行很快。如果50并发 每条包含2个Hash 1个Sort理论工作内存需求可能很大。因此经营报表更适合报表连接池 专用角色 会话级work_mem 并发限制而不是所有业务全局256MB5. 结果对比从96秒到2.9秒最大的收益并不是调内存E0原SQLSeq Scan → Hash Join → HashAggregate 输入 3亿 Temp 18GB P95 96sE1ANALYZEestimated groups: 5000 → 26000 actual: 28000P9596s →81s有改善但有限。E2work_mem256MBTemp 15GB →5.2GB P95 81s →51s证明聚合内存不足确实存在。但仍然很慢。E3增加过滤联合索引输入3亿 →4200万P9551s →19sE4预聚合输入到最终 Join/Aggregate约40万Temp≈0P954.8sE5预聚合 合理索引输入28万P952.9s5.1 示例汇总实验主要方案聚合输入TempP95E0原SQL3亿18GB96sE1ANALYZE3亿15GB81sE2work_mem 256MB3亿5.2GB51sE3联合索引4200万1.6GB19sE4预聚合40万04.8sE5预聚合索引28万02.9s以上为方法示例数据不是本文声称的生产实测。真正值得注意的是96 →51秒主要来自更多内存而51 →2.9秒主要来自少处理数据这就是两类优化的本质区别。5.2 Buffer Read比耗时更有复用价值如果优化后P95下降同时Buffers Read: 4800万 →39万说明IO工作量真正下降而不是仅仅缓存更热所以每次报告都应该同时保存Execution Time Buffers Temp CPU5.3 结果一致性要专门验证经营报表最危险的不是SQL慢而是SQL快了但数字变了至少比较总销售额 总订单数 各区域金额 月度金额 NULL 退款/取消状态 跨月边界 COUNT DISTINCT对于金额必须定义允许误差通常精确财务报表差异05.4 新索引要测试写入成本联合索引查询收益巨大但事实表每天写入 500万~800万行。必须测INSERT TPS WAL 磁盘空间 批量装载 索引维护否则报表快了 交易写入慢了不是完整优化。5.5 预聚合表还要验证刷新成本如果维护shop_day_summary就要记录刷新延迟 刷新TPS 补数能力 重跑范围 去重 幂等不能只看查询从96s →2.9s却忽略汇总层每天需要1小时才能算完6. 风险与复盘GROUP BY调优最容易“跑得快但口径错”6.1 风险一预聚合粒度选错如果在shop_id day层先计算COUNT(DISTINCT customer_id)再把门店结果相加同一客户跨店就会重复计数。因此预聚合前必须把指标分成可加 半可加 不可加三类。6.2 风险二AVG的平均值陷阱错误AVG(门店平均客单价)不一定等于全区域平均客单价正确设计通常保留sum count最终再求平均。6.3 风险三盲目提高work_memwork_mem是每个内部排序/哈希操作的预算多节点、多会话可能同时使用。所以256MB单SQL最优不代表生产安全。6.4 风险四强制GroupAggregate关闭enable_hashagg可能让计划变成Sort海量数据 →GroupAggregate反而更差。算法开关只能用于诊断性对照6.5 风险五索引列顺序拍脑袋(biz_date,status,shop_id)和(status,biz_date,shop_id)哪一个好取决于选择性 查询模式 日期范围 status分布必须用实际业务参数测试。6.6 风险六汇总表数据延迟实时明细T0汇总T5min业务是否接受必须在产品层明确数据新鲜度SLA不能由 DBA 默认决定。6.7 风险七缓存计划与日期参数月初查1天月底查31天参数范围差异巨大。同一报表缓存计划未必始终最佳。需要用短周期 普通周期 超长周期分别测试。推荐调优顺序经营报表遇到慢 GROUP BY 时可以固定为1. EXPLAIN ANALYZE 2. 查Aggregate输入行数 3. 查estimated groups / actual groups 4. 查Hash/Sort/Temp 5. ANALYZE 6. 过滤前推 7. 索引减少扫描 8. 预聚合减少输入 9. 会话级work_mem实验 10. 并发 结果一致性验证顺序非常重要。不要第一步就把work_mem改成1GB因为你很可能只是在用内存硬扛一个本不该处理3亿行的SQL回退方案如果预聚合/索引/参数方案上线后出现数据口径差异 P95回归 写TPS下降 内存过高 汇总延迟执行1. 停止扩大新SQL流量 2. Feature Flag切回原SQL 3. 恢复原会话级work_mem 4. 暂停新汇总表读流量 5. 保留新索引先确认是否被其他SQL使用 6. 保存新旧计划和监控 7. 做金额/订单数/Distinct结果复核如果汇总层本身有错误不要直接修最终结果应该按batch_id/date范围重算保持可审计。最终复盘GROUP BY 优化的成本可以粗略理解为输入行数 × 每行宽度 × 分组基数 × 聚合函数复杂度真正高效的优化通常按优先级减少扫描 减少聚合输入 减少行宽 让输入更适合聚合 合理增加工作内存如果只记住一句话经营报表 GROUP BY 优化的关键不是让数据库更努力地聚合3亿行而是尽可能在聚合之前把这3亿行压缩成真正符合业务分析粒度的几十万行。这也是为什么预聚合 合理索引 正确统计往往比单纯提高work_mem更稳定、更节省资源也更适合高并发报表系统。附录 A最小计划诊断EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;重点Aggregate Node Input actual rows estimated groups actual groups Sort Method Hash Memory / Batches Buffers Execution Time附录 B诊断性Hash聚合对照BEGIN;SETLOCALenable_hashaggoff;EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;ROLLBACK;只用于实验不应当作长期全局配置。附录 C会话级内存实验BEGIN;SETLOCALwork_mem64MB;SELECT...;ROLLBACK;附录 D最低验收门禁[ ] 分组基数估算无重大未解释偏差 [ ] 聚合输入量已量化 [ ] 临时文件在预算内 [ ] P95/P99达到SLA [ ] 并发内存在预算内 [ ] 销售额/订单数结果差异0 [ ] COUNT DISTINCT语义确认 [ ] 新索引写成本可接受 [ ] 汇总刷新延迟达到SLA [ ] SQL/索引/参数回退已准备转载自https://blog.csdn.net/u014727709/article/details/163948782欢迎 点赞✍评论⭐收藏欢迎指正 文章目录每日一句正能量1. 背景与问题报表只返回几十行为什么数据库却要处理3亿行2. 环境与数据构造一个典型的经营报表场景2.1 先建立性能基线2.2 GROUP BY首先要看“分组基数”2.3 HashAggregate与GroupAggregate不是简单“快慢关系”HashAggregateGroupAggregate3. 复现过程一条96秒的GROUP BY真正慢在哪里3.1 基线执行计划3.2 先看统计信息3.3 work_mem实验4. 方案实施先减少聚合输入再讨论聚合参数4.1 第一步过滤前推4.2 第二步联合索引真正价值是“缩小输入”4.3 第三步只把必要列送进聚合4.4 第四步预聚合——真正的大杀器4.5 预聚合不是“随便先GROUP BY”可加指标半可加/不可直接加4.6 AVG不要简单“平均的平均”4.7 COUNT(DISTINCT)是经营报表里的高风险指标4.8 第五步考虑Group Key顺序与已有序输入4.9 第六步表达式可能破坏索引顺序利用4.10 第七步预聚合表比每次实时聚合更适合稳定经营报表4.11 第八步不要忽略HashAggregate与GroupAggregate对照实验4.12 第九步监控临时文件4.13 第十步高并发下必须做内存预算5. 结果对比从96秒到2.9秒最大的收益并不是调内存E0原SQLE1ANALYZEE2work_mem256MBE3增加过滤联合索引E4预聚合E5预聚合 合理索引5.1 示例汇总5.2 Buffer Read比耗时更有复用价值5.3 结果一致性要专门验证5.4 新索引要测试写入成本5.5 预聚合表还要验证刷新成本6. 风险与复盘GROUP BY调优最容易“跑得快但口径错”6.1 风险一预聚合粒度选错6.2 风险二AVG的平均值陷阱6.3 风险三盲目提高work_mem6.4 风险四强制GroupAggregate6.5 风险五索引列顺序拍脑袋6.6 风险六汇总表数据延迟6.7 风险七缓存计划与日期参数推荐调优顺序回退方案最终复盘附录 A最小计划诊断附录 B诊断性Hash聚合对照附录 C会话级内存实验附录 D最低验收门禁每日一句正能量感情因珍惜而美好友情因真诚而长久亲情因相依而温暖。一份感情的美好不在于它本身多么华丽而在于我们是否用心呵护。长久的友情是经过时间淬炼后那份信任依然毫发无伤。家人的温暖来自于彼此的依靠和陪伴来自这份无需言说的“在场”。主题聚合优化 / GROUP BY / 经营报表重点HashAggregate、GroupAggregate、预聚合、联合索引、统计信息、work_mem、临时文件、执行计划、P95/P99 与结果校验适用场景KingbaseES 上的经营日报、月报、销售分析、门店排名、客户分群、财务汇总等大数据量聚合场景。1. 背景与问题报表只返回几十行为什么数据库却要处理3亿行经营报表类 SQL 有一个非常典型的特点最终结果很小但输入数据极大例如一个“区域月销售汇总”最终只返回 30 个区域 × 12 个月大约几百行。SQL 看起来也很普通SELECTs.region_id,date_trunc(month,d.biz_date)ASmonth_no,COUNT(DISTINCTd.order_id)ASorder_cnt,SUM(d.amount)ASsales_amountFROMsales_detail dJOINshop_dim sONs.shop_idd.shop_idWHEREd.biz_date:start_dateANDd.biz_date:end_dateANDd.status1GROUPBYs.region_id,date_trunc(month,d.biz_date);最终只返回几百行但数据库可能执行3亿明细行扫描 → 大规模Join → 3亿行进入Aggregate → Hash表或Sort → 最终输出几百行真正决定性能的是进入聚合节点的数据量而不是聚合完成后的结果量KingbaseES 官方执行计划文档把聚合节点区分为HashAggregate、GroupAggregate等HashAggregate使用哈希桶对无序数据分组GroupAggregate基于有序输入执行聚合。官方性能参数文档进一步指出Hash 聚合在分组唯一值较多时内存消耗会明显增加而 GroupAggregate 的内存表现相对稳定但如果下层输入没有排序则可能付出额外 Sort 成本。因此经营报表优化不能只问work_mem要调多大应该先问为什么要让3亿行进入聚合 能不能让它先变成3000万 能不能先变成40万 GROUP BY之前是否做了不必要的大Join 是否可以利用索引缩小扫描范围 分组基数估算是否准确这篇文章的核心观点是GROUP BY真正高价值的优化通常不是“让聚合节点跑快一点”而是让尽可能少、尽可能窄、尽可能符合业务粒度的数据进入聚合节点。2. 环境与数据构造一个典型的经营报表场景示例环境数据库 KingbaseES V9 事实表 sales_detail 数据量 3亿行 日新增 500万~800万 维表 shop_dim 门店 12万 报表 区域月销售额 区域订单数 门店经营汇总事实表CREATETABLEsales_detail(idBIGINTPRIMARYKEY,shop_idBIGINT,product_idBIGINT,biz_dateDATE,amountNUMERIC(18,2),order_idBIGINT,statusINT);维表CREATETABLEshop_dim(shop_idBIGINTPRIMARYKEY,region_idBIGINT,shop_nameVARCHAR(200),statusINT);2.1 先建立性能基线不要只记SQL耗时96秒至少保存SQL指纹 日期参数 实际扫描行数 Aggregate输入行数 Group估算行数 Group实际行数 Sort Method Hash内存/Batches Temp GB Buffers hit/read CPU IO P50/P95/P99 最终结果行数原因很简单如果优化后从96s →20s但不知道是扫描少了 Hash少落盘了 还是缓存碰巧更热这个结论无法复用。2.2 GROUP BY首先要看“分组基数”假设GROUPBYregion_id实际只有30组和GROUPBYcustomer_id实际3000万组虽然语法都是 GROUP BY但 HashAggregate 的内存行为完全不同。官方 SQL 调优文档特别提到HashAggregate 受到 GROUP BY 字段唯一值数量的明显影响唯一值越多需要维护的 Hash 状态越多内存和性能压力都会上升。因此计划里estimated groups actual groups也需要和普通行数估算一样重点关注。2.3 HashAggregate与GroupAggregate不是简单“快慢关系”HashAggregate优势输入无需有序 通常对低/中等分组基数效率高风险分组基数高 聚合状态多 内存不足 临时文件/批处理成本GroupAggregate优势如果输入已经有序 可以按组流式处理 内存行为比较稳定风险如果输入无序 需要Sort于是可能变成Seq Scan → Sort 3亿行 → GroupAggregate新的瓶颈只是从Hash转成Sort3. 复现过程一条96秒的GROUP BY真正慢在哪里3.1 基线执行计划EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...假设核心计划HashAggregate Group Key: s.region_id, date_trunc(...) - Hash Join - Seq Scan sales_detail - Hash - Seq Scan shop_dim监控sales_detail actual rows: 300,000,000 Aggregate input: 300,000,000 estimated groups: 5,000 actual groups: 28,000 temp: 18GB P95: 96s这里至少有三个问题1. 聚合输入太大 2. 分组基数低估 3. Hash可能在大量写临时文件不能直接进入work_mem调优3.2 先看统计信息官方 SQL 优化建议指出如果表统计信息过旧、缺少多列统计或数据严重倾斜优化器可能选择非最优计划。执行ANALYZEsales_detail;ANALYZEshop_dim;再次执行。假设estimated groups: 26,000 actual: 28,000估算明显改善。P9596s →81s说明统计确实有问题但输入仍然3亿行所以只能得到有限收益。这一步很关键估算正确不等于数据量合理。3.3 work_mem实验固定SQL 参数 数据快照只调整会话级SETLOCALwork_mem32MB;SETLOCALwork_mem128MB;SETLOCALwork_mem256MB;示例32MB: Temp15GB P9581s 128MB: Temp8GB P9563s 256MB: Temp5.2GB P9551s可以证明内存不足确实存在但 256MB 仍然要处理3亿行所以继续堆内存的边际收益会下降。而且官方文档明确提醒work_mem是每个内部 Sort/Hash 操作的工作内存复杂 SQL 可以同时拥有多个节点多会话还可能并发执行因此单 SQL 最佳参数不能直接变成生产全局参数。4. 方案实施先减少聚合输入再讨论聚合参数4.1 第一步过滤前推原 SQL3亿历史明细实际报表只查最近一个月 status1如果没有合适的过滤访问路径就会扫描太多数据。可以评估CREATEINDEXidx_sales_date_status_shopONsales_detail(biz_date,status,shop_id);或者在status选择性更高的系统CREATEINDEXidx_sales_status_date_shopONsales_detail(status,biz_date,shop_id);不要机械同时创建。真实选择要根据status分布 日期范围 写入成本 索引体积做实验。4.2 第二步联合索引真正价值是“缩小输入”假设索引优化后进入聚合的明细 3亿 →4200万P9551s →19s这时候收益不是因为索引让GROUP BY本身变快而是索引让前面的扫描少了2.58亿行这也是索引设计里一个很重要的工程原则对于 GROUP BY 查询索引最优先的目标通常是把不参与本次报表的数据挡在聚合节点之前。4.3 第三步只把必要列送进聚合不要SELECTd.*,s.*然后再聚合。聚合阶段真正需要的可能只有shop_id biz_date order_id amount行越宽扫描 缓存 Hash Sort成本都越高。因此中间结果要尽量窄。4.4 第四步预聚合——真正的大杀器原来3亿订单明细如果业务最终按区域 月份汇总可以先考虑最低安全粒度shop_id biz_date预聚合。WITHdaily_shopAS(SELECTshop_id,biz_date,COUNT(DISTINCTorder_id)ASorder_cnt,SUM(amount)ASsales_amountFROMsales_detailWHEREbiz_date:start_dateANDbiz_date:end_dateANDstatus1GROUPBYshop_id,biz_date)SELECTs.region_id,date_trunc(month,d.biz_date)ASmonth_no,SUM(d.order_cnt)ASorder_cnt,SUM(d.sales_amount)ASsales_amountFROMdaily_shop dJOINshop_dim sONs.shop_idd.shop_idGROUPBYs.region_id,date_trunc(month,d.biz_date);示例3亿明细 → 40万shop-day聚合行 → 再Join维表 → 最终区域月聚合这会同时降低Join输入 Hash输入 Sort输入 Buffer Read 临时文件4.5 预聚合不是“随便先GROUP BY”这是最容易犯错的地方。例如COUNT(DISTINCT order_id)如果一个order_id可能跨多个shop_id 或多个biz_date那么先分别计算COUNT(DISTINCT)再SUM()可能重复计数。所以预聚合必须先证明中间粒度满足业务可加性。可加指标通常SUM(amount) COUNT(rows)比较容易二次聚合。半可加/不可直接加例如COUNT(DISTINCT user_id) AVG() 余额 库存快照 去重客户数必须重新设计。所以预聚合的第一条件不是性能而是业务指标可加性。4.6 AVG不要简单“平均的平均”错误区域AVG 各门店AVG再AVG如果门店样本数不同结果会错。正确预聚合保存SUM(value) COUNT(value)最终SUM(sum_value) / SUM(count_value)这类语义问题必须进入数据校验。4.7 COUNT(DISTINCT)是经营报表里的高风险指标原COUNT(DISTINCTcustomer_id)预聚合后不能简单SUM(各门店distinct customer)因为同一个客户可能跨门店。可选方式更高粒度去重 独立客户集合 bitmap/近似算法若业务允许 专门汇总层但不能为了快改变口径。4.8 第五步考虑Group Key顺序与已有序输入如果查询经常GROUPBYshop_id,biz_date并且前面的访问路径已经按shop_id,biz_date有序那么优化器有机会选择GroupAggregate减少额外 Sort/Hash 成本。但是否生效必须看实际计划不能因为索引列顺序相同就假设一定不用Sort还要考虑WHERE条件 扫描方向 函数表达式 Collation NULL排序4.9 第六步表达式可能破坏索引顺序利用例如GROUPBYdate_trunc(month,biz_date)普通biz_date索引不一定直接等价于date_trunc(month)如果这个报表极高频可以评估表达式索引 生成列 预聚合月字段但需要结合 KingbaseES 实际版本、表达式索引支持和写成本测试。4.10 第七步预聚合表比每次实时聚合更适合稳定经营报表如果报表每天8点被几千人打开而数据只要求T5分钟就没有必要每个请求都扫描几千万甚至几亿明细可以构建sales_shop_day_summary按shop_id biz_date增量维护。查询汇总表 →维表 →最终GROUP BY这本质上是把CPU成本从查询时 搬到数据准备时并换取稳定低延迟4.11 第八步不要忽略HashAggregate与GroupAggregate对照实验KingbaseES 提供enable_hashagg参数用于影响优化器对 HashAggregate 的选择。在测试会话可以BEGIN;SETLOCALenable_hashaggoff;EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;ROLLBACK;用来比较HashAggregate vs GroupAggregate Sort但不要把全局关闭HashAggregate作为永久修复。因为低分组基数、无序输入时HashAggregate本来可能是最优方案。4.12 第九步监控临时文件GROUP BY慢时临时文件可能来自Hash Sort 中间结果可以在短时间诊断窗口ALTERSYSTEMSETlog_temp_filesTO0;SELECTSYS_RELOAD_CONF();结合PID 时间 SQL 计划判断。不要看到Temp 18GB就直接断定全部是HashAggregate4.13 第十步高并发下必须做内存预算单条报表work_mem256MB运行很快。如果50并发 每条包含2个Hash 1个Sort理论工作内存需求可能很大。因此经营报表更适合报表连接池 专用角色 会话级work_mem 并发限制而不是所有业务全局256MB5. 结果对比从96秒到2.9秒最大的收益并不是调内存E0原SQLSeq Scan → Hash Join → HashAggregate 输入 3亿 Temp 18GB P95 96sE1ANALYZEestimated groups: 5000 → 26000 actual: 28000P9596s →81s有改善但有限。E2work_mem256MBTemp 15GB →5.2GB P95 81s →51s证明聚合内存不足确实存在。但仍然很慢。E3增加过滤联合索引输入3亿 →4200万P9551s →19sE4预聚合输入到最终 Join/Aggregate约40万Temp≈0P954.8sE5预聚合 合理索引输入28万P952.9s5.1 示例汇总实验主要方案聚合输入TempP95E0原SQL3亿18GB96sE1ANALYZE3亿15GB81sE2work_mem 256MB3亿5.2GB51sE3联合索引4200万1.6GB19sE4预聚合40万04.8sE5预聚合索引28万02.9s以上为方法示例数据不是本文声称的生产实测。真正值得注意的是96 →51秒主要来自更多内存而51 →2.9秒主要来自少处理数据这就是两类优化的本质区别。5.2 Buffer Read比耗时更有复用价值如果优化后P95下降同时Buffers Read: 4800万 →39万说明IO工作量真正下降而不是仅仅缓存更热所以每次报告都应该同时保存Execution Time Buffers Temp CPU5.3 结果一致性要专门验证经营报表最危险的不是SQL慢而是SQL快了但数字变了至少比较总销售额 总订单数 各区域金额 月度金额 NULL 退款/取消状态 跨月边界 COUNT DISTINCT对于金额必须定义允许误差通常精确财务报表差异05.4 新索引要测试写入成本联合索引查询收益巨大但事实表每天写入 500万~800万行。必须测INSERT TPS WAL 磁盘空间 批量装载 索引维护否则报表快了 交易写入慢了不是完整优化。5.5 预聚合表还要验证刷新成本如果维护shop_day_summary就要记录刷新延迟 刷新TPS 补数能力 重跑范围 去重 幂等不能只看查询从96s →2.9s却忽略汇总层每天需要1小时才能算完6. 风险与复盘GROUP BY调优最容易“跑得快但口径错”6.1 风险一预聚合粒度选错如果在shop_id day层先计算COUNT(DISTINCT customer_id)再把门店结果相加同一客户跨店就会重复计数。因此预聚合前必须把指标分成可加 半可加 不可加三类。6.2 风险二AVG的平均值陷阱错误AVG(门店平均客单价)不一定等于全区域平均客单价正确设计通常保留sum count最终再求平均。6.3 风险三盲目提高work_memwork_mem是每个内部排序/哈希操作的预算多节点、多会话可能同时使用。所以256MB单SQL最优不代表生产安全。6.4 风险四强制GroupAggregate关闭enable_hashagg可能让计划变成Sort海量数据 →GroupAggregate反而更差。算法开关只能用于诊断性对照6.5 风险五索引列顺序拍脑袋(biz_date,status,shop_id)和(status,biz_date,shop_id)哪一个好取决于选择性 查询模式 日期范围 status分布必须用实际业务参数测试。6.6 风险六汇总表数据延迟实时明细T0汇总T5min业务是否接受必须在产品层明确数据新鲜度SLA不能由 DBA 默认决定。6.7 风险七缓存计划与日期参数月初查1天月底查31天参数范围差异巨大。同一报表缓存计划未必始终最佳。需要用短周期 普通周期 超长周期分别测试。推荐调优顺序经营报表遇到慢 GROUP BY 时可以固定为1. EXPLAIN ANALYZE 2. 查Aggregate输入行数 3. 查estimated groups / actual groups 4. 查Hash/Sort/Temp 5. ANALYZE 6. 过滤前推 7. 索引减少扫描 8. 预聚合减少输入 9. 会话级work_mem实验 10. 并发 结果一致性验证顺序非常重要。不要第一步就把work_mem改成1GB因为你很可能只是在用内存硬扛一个本不该处理3亿行的SQL回退方案如果预聚合/索引/参数方案上线后出现数据口径差异 P95回归 写TPS下降 内存过高 汇总延迟执行1. 停止扩大新SQL流量 2. Feature Flag切回原SQL 3. 恢复原会话级work_mem 4. 暂停新汇总表读流量 5. 保留新索引先确认是否被其他SQL使用 6. 保存新旧计划和监控 7. 做金额/订单数/Distinct结果复核如果汇总层本身有错误不要直接修最终结果应该按batch_id/date范围重算保持可审计。最终复盘GROUP BY 优化的成本可以粗略理解为输入行数 × 每行宽度 × 分组基数 × 聚合函数复杂度真正高效的优化通常按优先级减少扫描 减少聚合输入 减少行宽 让输入更适合聚合 合理增加工作内存如果只记住一句话经营报表 GROUP BY 优化的关键不是让数据库更努力地聚合3亿行而是尽可能在聚合之前把这3亿行压缩成真正符合业务分析粒度的几十万行。这也是为什么预聚合 合理索引 正确统计往往比单纯提高work_mem更稳定、更节省资源也更适合高并发报表系统。附录 A最小计划诊断EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;重点Aggregate Node Input actual rows estimated groups actual groups Sort Method Hash Memory / Batches Buffers Execution Time附录 B诊断性Hash聚合对照BEGIN;SETLOCALenable_hashaggoff;EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;ROLLBACK;只用于实验不应当作长期全局配置。附录 C会话级内存实验BEGIN;SETLOCALwork_mem64MB;SELECT...;ROLLBACK;附录 D最低验收门禁[ ] 分组基数估算无重大未解释偏差 [ ] 聚合输入量已量化 [ ] 临时文件在预算内 [ ] P95/P99达到SLA [ ] 并发内存在预算内 [ ] 销售额/订单数结果差异0 [ ] COUNT DISTINCT语义确认 [ ] 新索引写成本可接受 [ ] 汇总刷新延迟达到SLA [ ] SQL/索引/参数回退已准备转载自https://blog.csdn.net/u014727709/article/details/163948782欢迎 点赞✍评论⭐收藏欢迎指正 
面试全解析：从原理到实战)
