文章目录每日一句正能量1. 背景与问题Hash Join慢到底是Join算法不行还是哈希表被迫写盘2. 环境与数据先构造一个能稳定复现“多批Hash”的批量分析场景2.1 第一份基线必须同时保存计划和监控数据2.2 执行计划重点不只是“Hash Join”四个字3. 复现过程从 16MB 到 256MB观察 Hash Join 怎么从落盘变成驻留内存3.1 基线work_mem16MB3.2 检查基数估算3.3 临时文件必须直接观察3.4 第二轮64MB3.5 第三轮256MB4. 方案实施不要把“单SQL最佳参数”直接变成“全库参数”4.1 work_mem不是“每连接最多这么多内存”4.2 优先使用会话级实验4.3 优先减少构建侧行数4.4 减少哈希行宽也有价值4.5 先修统计信息再评价 Join 算法4.6 Hash Join不是越少越好5. 结果对比参数优化和SQL优化哪个更划算5.1 E2看似最好为什么不直接采用5.2 E3为什么更适合作为生产方案5.3 还要做结果一致性验证5.4 并发验证6. 风险与复盘最危险的调优是用大内存把坏计划暂时“养活”6.1 风险一把work_mem全局放大6.2 风险二忽略统计信息6.3 风险三只看临时文件不看是谁写的6.4 风险四log_temp_files长期设06.5 风险五Batches1仍然慢却继续加内存6.6 风险六预聚合改变语义6.7 风险七只在空闲系统测试参数回退方案推荐诊断顺序附录 A诊断计划附录 B临时文件诊断附录 C会话级参数实验附录 D最低验收门禁每日一句正能量“生活的糖要自己加别人把握不了你的口感。”幸福是极个人的配方。他人递来的甜或太腻或寡淡唯有自己调试的才能恰好落在味蕾的渴处。我不等谁赐我甜我自己就是糖罐。主题内存与落盘 / Hash Join / 批量分析重点EXPLAIN ANALYZE、Hash 构建侧、Buckets/Batches、work_mem、临时文件、log_temp_files、统计信息、SQL 改写、并发内存预算适用场景KingbaseES 上的大表关联、批量分析、报表、ETL SQL 中出现 Hash Join 执行缓慢、临时文件暴增、磁盘 IO 高、同一 SQL 在不同work_mem下性能差异明显的问题。1. 背景与问题Hash Join慢到底是Join算法不行还是哈希表被迫写盘批量分析系统里经常会看到这样的执行计划Hash Join Hash Cond: ... - Seq Scan ... - Hash Buckets: ... Batches: ... Memory Usage: ...一看到Hash Join有些团队会直接尝试禁用Hash Join 改Nested Loop 强制Merge Join还有一些团队会直接把work_mem从16MB调成2GB这两种做法都有一个共同问题没有先证明真正瓶颈是什么。KingbaseES 官方 SQL 调优指南对 Hash Join 的原理描述很明确Hash Join 分为构建和探测两个阶段。构建阶段会把内表数据组织到哈希桶里探测阶段再扫描外表根据哈希值去对应桶查找匹配项。哈希操作主要依赖工作内存当工作内存无法承载操作时就可能借助临时磁盘文件。KingbaseES 官方参数文档对work_mem的定义同样非常关键它是排序和哈希等内部操作在使用临时磁盘文件之前可使用的工作内存并且一个复杂查询可能同时有多个排序或哈希节点每个节点都可能使用该额度。所以 Hash Join 调优不能只问work_mem够不够而要依次回答构建侧到底有多少行 优化器原来估了多少 每行有多宽 Hash节点分了多少Batch 临时文件到底写了多少 这个SQL同时有几个Hash/Sort节点 线上同时有多少会话跑这类SQL如果Batches 1 temp file 0即使 SQL 仍然慢也不能继续把问题归咎于“Hash内存不足”。真正合理的诊断标准应该是通过执行计划、临时文件和参数实验形成证据链而不是根据 Join 名称猜根因。2. 环境与数据先构造一个能稳定复现“多批Hash”的批量分析场景示例环境数据库 KingbaseES V9 业务 交易分析平台 fact_order 8亿行 dim_customer 5000万行 分析周期 最近180天 典型SQL 订单事实表 JOIN 客户维表 GROUP BY region示例 SQLSELECTc.region_id,COUNT(*)AScnt,SUM(o.amount)AStotal_amountFROMfact_order oJOINdim_customer cONo.customer_idc.customer_idWHEREo.order_dateDATE2026-01-01ANDo.status1GROUPBYc.region_id;2.1 第一份基线必须同时保存计划和监控数据单独保存Execution Time 42s价值不够。建议最少保存estimated rows actual rows Hash构建侧 Buckets Batches Memory Usage Buffers temp file size CPU 磁盘读写 P50/P95/P99 并发数这样后面每次调参都有可比较依据。2.2 执行计划重点不只是“Hash Join”四个字官方执行计划示例中Hash 节点会显示类似Buckets: 1024 Batches: 1 Memory Usage: 45kB这里最值得关注的是Batches直观理解Batches1说明当前 Hash 构建数据基本可以按一个批次完成。当哈希表装不下系统可能需要增加批次数把部分数据分批处理。这通常意味着更多临时文件 更多磁盘读写 更多重复探测成本所以在 Hash Join 内存问题里Batch 数是比“有没有 Hash Join”更有诊断价值的信号。3. 复现过程从 16MB 到 256MB观察 Hash Join 怎么从落盘变成驻留内存3.1 基线work_mem16MB会话级设置SETLOCALwork_mem16MB;执行EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;实验记录Hash Batches 32 临时文件 ≈ 9.6GB Execution Time 42.0s这时已经有一条很强的证据Hash多批处理 大量temp 执行时间长但仍然不能立即下结论。因为还要看为什么构建侧这么大3.2 检查基数估算假设计划estimated rows 80万 actual rows 520万偏差6.5倍那优化器原本认为Hash表只需要装80万行实际却进了520万内存自然更容易失控。所以第一动作往往不是加内存而是ANALYZEfact_order;ANALYZEdim_customer;KingbaseES 官方ANALYZE文档明确指出查询规划器会使用统计信息选择有效执行计划执行计划分析指南也建议发现估算不准确时先检查和更新统计信息。3.3 临时文件必须直接观察KingbaseES 官方性能调优文档提供log_temp_files用于记录排序、Hash 和临时查询结果创建的临时文件。例如ALTERSYSTEMSETlog_temp_filesTO0;SELECTSYS_RELOAD_CONF();之后执行目标 SQL。日志会记录temp file path temp file size这一步很关键因为它把“我觉得它在落盘”变成“这个SQL实际写了9.6GB临时文件”的可量化证据。3.4 第二轮64MB保持SQL不变 参数不变 数据快照不变只改SETLOCALwork_mem64MB;实验Batches 8 temp ≈ 2.7GB Execution 25.4s这时可以看到内存增加 → Batch减少 → 临时文件下降 → 执行时间下降说明内存确实是瓶颈之一。3.5 第三轮256MBSETLOCALwork_mem256MB;结果Batches 1 temp ≈ 0 Execution 13.1s单条 SQL 看起来已经非常理想。如果只做单会话测试团队很容易直接宣布生产work_mem改成256MB这里正是最危险的一步。4. 方案实施不要把“单SQL最佳参数”直接变成“全库参数”4.1 work_mem不是“每连接最多这么多内存”官方文档特别提醒复杂查询可能同时运行多个Sort 多个Hash每个操作都可能使用work_mem额度。假设一条 SQL 有3个Hash 2个Sort理论上的瞬时工作内存风险不能简单看256MB而要考虑多个节点 × 多个并发查询如果有32并发那么全局把work_mem调大可能把磁盘瓶颈变成内存压力甚至OOM4.2 优先使用会话级实验对批量任务可以采用BEGIN;SETLOCALwork_mem64MB;SELECT...;COMMIT;而不是ALTERSYSTEMSETwork_mem256MB;直接影响全部在线业务。这样可以把参数控制在特定批任务 特定会话范围。4.3 优先减少构建侧行数假设 Hash 构建侧输入520万行真正 Join 以后只需要60万有效客户可以考虑过滤前推 预聚合 只选择必要列例如WITHfiltered_orderAS(SELECTcustomer_id,amountFROMfact_orderWHEREorder_date:start_dateANDstatus1)SELECT...更激进的分析 SQL 可以WITHorder_aggAS(SELECTcustomer_id,COUNT(*)cnt,SUM(amount)amount_sumFROMfact_orderWHERE...GROUPBYcustomer_id)SELECT...先把事实表压缩再 Join 维表。4.4 减少哈希行宽也有价值如果构建侧SELECT*每行可能包含几十列 大VARCHAR JSON LOB引用而 Join 实际只需要customer_id region_id就应该让进入 Hash 节点的数据更窄。Hash 内存需求不仅和行数有关也与每行宽度高度相关。4.5 先修统计信息再评价 Join 算法如果estimated1万 actual500万优化器做出的 Join 顺序、构建侧选择甚至 Join 类型都可能建立在错误基数上。所以统计信息 → 基数估算 → Join选择 → work_mem有明确先后逻辑。不能统计完全错还继续凭计划名称调参数。4.6 Hash Join不是越少越好对大量、无序、等值连接Hash Join本来就是非常合理的算法。如果强制 Nested Loop外表几百万行 × 内表重复探测可能比 Hash 更差。官方执行计划分析文档同样强调不同 Join 算法有自己的适用条件应结合行数和数据特征判断。所以调优目标不是消灭Hash Join而是让Hash Join在合适输入规模和合适内存里执行5. 结果对比参数优化和SQL优化哪个更划算实验数据实验work_memSQLBatchesTemp执行时间E016MB原SQL329.6GB42.0sE164MB原SQL82.7GB25.4sE2256MB原SQL1013.1sE364MB过滤预聚合107.8s这些是方法演示数据不是生产实测。5.1 E2看似最好为什么不直接采用因为256MB只是单条 SQL 的优秀值。如果32并发 每条多个Hash/Sort内存风险很高。5.2 E3为什么更适合作为生产方案E3work_mem64MB但通过 SQL 改写Hash构建输入 520万 →60万最终Batches1 Temp0 Execution7.8s这说明减少需要哈希的数据往往比无上限增加哈希内存更有工程价值。5.3 还要做结果一致性验证SQL 改写以后必须比较region_id COUNT SUM(amount) NULL处理 重复行确保结果差异0性能优化不能改变业务口径。5.4 并发验证建议1并发 4并发 8并发 16并发 32并发记录TPS P95 P99 内存 Swap IO Temp GB/min可能发现256MB单会话最快 64MB并发吞吐最好因此真正的生产最优值应是系统级最优而不是单SQL最优6. 风险与复盘最危险的调优是用大内存把坏计划暂时“养活”6.1 风险一把work_mem全局放大单SQL从42s →13s很诱人。但如果全库256MB在线交易、报表、批处理都受到影响。复杂 SQL 的多个节点和并发会话可能同时消耗大量内存。所以优先会话级 任务级调节。6.2 风险二忽略统计信息Hash Join 落盘有时候不是内存本身太小而是优化器严重低估构建侧统计修复可能同时改变Join顺序 构建侧 Join算法6.3 风险三只看临时文件不看是谁写的log_temp_files会记录排序 Hash 临时查询结果所以看到10GB temp不能直接说全部是Hash Join必须按PID 时间 SQL 执行计划关联。6.4 风险四log_temp_files长期设0诊断时0会记录所有临时文件。高并发生产可能产生很多日志。更合理限定诊断窗口或者使用正阈值只记录较大的临时文件。6.5 风险五Batches1仍然慢却继续加内存如果Batches1 Temp0再从256MB →1GB通常不会解决根因。这时应转向CPU Join键 数据倾斜 构建侧宽度 输出规模 并发6.6 风险六预聚合改变语义不是所有 Join 都可以先GROUP BY再JOIN如果 Join 会产生一对多 过滤条件依赖维表错误预聚合可能改变 COUNT/SUM。所以每个 SQL 改写都要结果校验6.7 风险七只在空闲系统测试生产里Buffer Cache IO CPU 并发完全不同。最终调优必须在代表性并发下验证。参数回退方案如果生产灰度后内存上涨 Swap OOM风险 其他SQL P95恶化应立即停止扩大灰度 恢复旧SQL模板 恢复旧会话work_mem不要在事故现场同时修改shared_buffers work_mem 并行度 Join开关多个参数。保留旧计划 新计划 temp日志 内存监控做单变量分析。推荐诊断顺序慢SQL ↓ EXPLAIN ANALYZE ↓ estimated vs actual ↓ Hash构建侧 ↓ Batches/Memory Usage ↓ log_temp_files ↓ 统计信息 ↓ 会话级work_mem实验 ↓ SQL缩小输入 ↓ 并发验收如果只记住一句话Hash Join 内存调优的目标不是“给它更多内存”而是让正确规模的构建侧数据在可控内存预算中尽量少分批、少落盘并且这个内存预算在真实并发下仍然安全。附录 A诊断计划EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;重点estimated rows actual rows Buckets Batches Memory Usage Buffers Execution Time附录 B临时文件诊断ALTERSYSTEMSETlog_temp_filesTO0;SELECTSYS_RELOAD_CONF();诊断结束后按生产策略恢复。附录 C会话级参数实验BEGIN;SETLOCALwork_mem64MB;SELECT...;COMMIT;附录 D最低验收门禁[ ] 基数估算无重大未解释偏差 [ ] 关键Hash Batches达到预期 [ ] 非预期Temp落盘0或在预算内 [ ] P95/P99达到SLA [ ] 并发峰值内存在预算内 [ ] SQL改写结果差异0 [ ] 参数/SQL回退方案已验证转载自https://blog.csdn.net/u014727709/article/details/163863413欢迎 点赞✍评论⭐收藏欢迎指正