★ FEATURED ARTICLE 前阵子帮同事排查一条慢SQL原本30毫秒的查询突然掉到9秒EXPLAIN ANALYZE里明晃晃写着Batches: 8。经验告诉我这是work_mem设小了hash join被挤出内存但真正让我心虚的是如果被追问一句为什么Batches变成8就会慢这么多我未必能立刻把整个过程讲清楚。所以这篇文章想把PostgreSQL里的hybrid hash join从头讲透。为什么要强调无状态机讲解因为网上的教程动不动就画状态转移图build phase、probe phase、done看着严谨看完还是不知道数据到底往哪走、内存和磁盘之间发生了什么。这里不用状态机就用分批次配对的思路配合真实SQL把构建、探测、批次划分、动态扩容、EXPLAIN怎么看、参数怎么调全部串起来。数据库开发、DBA、对执行计划好奇的同学应该都能对号入座。1. hash join的本质一次内存中的对号入座1.1 从嵌套循环到hash join先回答一个基础问题为什么数据库要用hash join两表等值连接最朴素的做法是嵌套循环外层表每取一行就去内层表全表扫一遍。如果两张表各有100万行最坏情况就是1万亿次比对没有人受得了。hash join的思路就聪明在把比对变成索引式查找先拿一张表内表的连接键做哈希把数据散到一张哈希表里再拿另一张表外表的每一行用同一个哈希函数算出位置直接去哈希表里找匹配。平均复杂度降到O(NM)完全不在一个量级。第一次接触这个概念的人可以想象成图书馆里有两套卡片一套按书名编号一套按作者编号。嵌套循环是每拿起一张作者卡就把整盒书名卡翻一遍hash join则是先把书名卡按首字母分成26堆作者卡也按首字母分堆只在同一堆里比对。这个分堆的直觉恰好就是后面批次batch概念的雏形。1.2 内存放得下时一切都很美好纯内存hash join的流程非常干净把内表全部读进内存哈希表完整构建外表逐行探测。整个过程不碰磁盘速度极快。它唯一的约束是——哈希表必须完整放进内存。这里要澄清一个高频误解PostgreSQL里的work_mem并不是整个数据库能用的内存上限而是每个排序或哈希操作可以使用的内存额度。一个连接里同时跑多个hash join每个操作都可以独立占用自己的work_mem额度所以一条复杂SQL吃下几百MB内存是很正常的。PostgreSQL 13之后又引入了hash_mem_multiplier默认值2.0也就是说哈希操作实际可以膨胀到work_mem * 2的内存用量但普通排序仍然只用work_mem。很多人以为改work_mem只影响排序其实哈希连接同样受它制约而且是更常见的内存杀手。那如果内表比内存还大呢硬塞塞不进去全盘写磁盘再慢慢读又等于自杀官方执行器总得想个折中办法。1.3 内存放不下hybrid就来了hybrid hash join的核心思路概括起来就一句话把大数据切分成若干小批次batch每次只让一个批次驻留内存批次之间用磁盘文件做周转处理完一批再换下一批。它叫hybrid混合是因为它结合了两种模式一部分数据完全走内存哈希另一部分数据走磁盘暂存内存与磁盘协同完成整个连接。教科书上一般叫grace hash joinPostgreSQL的实现算是在这个框架上做了不少改进但底层逻辑一致。我一直觉得用一个生活场景就能把hybrid hash join讲明白假设你手上有一千张名片要按省份归类配对但桌上只能摊开一百张。你先把名片按省份分成十堆把北京这一堆留在桌上其余九堆装进纸箱。然后逐一处理手头待匹配的名单是北京的直接在桌上比对是其他省份的也按省份投进纸箱。北京处理完清空桌面从纸箱里取出河北那堆再从另一个箱子里取出河北的待匹配名单继续比对。如此循环直到十个省份全部配完。这就是hybrid hash join的完整心智模型。接下来看看PostgreSQL在这个模型上具体是怎么落地的。2. PostgreSQL里hybrid hash join的完整逻辑2.1 构建阶段第一批留在内存其它批次先存档整个hash join的第一步是构建build。优化器会先决定哪张表作为内表build side通常选择估算行数更小的那张因为构建哈希表的成本更小。真正的执行过程是这样的PostgreSQL先估算内表的数据量结合work_mem和hash_mem_multiplier算出需要多少个批次nbatch。nbatch一定是2的幂比如1、2、4、8、16。nbatch等于1就是普通的内存hash join大于1才进入hybrid模式。开始扫描内表逐行计算连接键的哈希值再根据哈希值算出这行属于哪个批次。批次0的数据直接插入内存中的哈希表。批次1及以后的数据追加写入对应的临时文件每个批次一个独立文件。构建阶段结束时内存里是完整的batch 0哈希表磁盘上躺着1到nbatch-1的内表数据。这些临时文件位于base/pgsql_tmp目录下查询结束会自动清理不需要人工干预。这个阶段有个容易忽略的细节内表数据被写进临时文件时并不是把原始行原样lossless保存就完了而是按执行器的slot格式序列化后再写。所以这批文件的大小通常比表实际占用还要略大一点IO量比想象中更重。2.2 探测阶段先配第一批再配后续批次构建完成进入探测probe阶段。外表逐行扫描计算出该行的哈希值和批次编号。如果是batch 0直接到内存哈希表里找匹配命中就输出没命中就跳过。如果是非batch 0也把它写入对应的外表batch临时文件排队等待。等外表全部扫描完batch 0的配对工作已经全部结束。此时把内存哈希表清空进入下一轮循环从磁盘加载内表batch 1的文件重新构建内存哈希表。扫描外表batch 1的临时文件逐行探测哈希表。输出所有命中然后再次清空哈希表。下一个批次重复同样的步骤。直到所有批次处理完毕。这个机制里有一个特别重要的性能认知外表同样要写磁盘。很多人以为只有内表会溢写但为了每一轮循环都能严格按批次配对外表的数据也必须按相同规则分区。换句话说一旦Batches大于1IO开销就是内表写一次读一次外表写一次读一次。这比纯内存版本重得多也是Batches一涨性能就崩的核心原因。2.3 批次怎么编号hash值里的位运算到现在最关键的问题来了一个数据行怎么决定自己属于哪个批次很多人以为是哈希值对批次数取模。方向上没错但实际实现比取模更巧妙。PostgreSQL用的是位运算哈希值是一个32位整数而批次数量是2的幂所以对nbatch取模等价于取哈希值的低若干比特位。更具体地理解可以把哈希值想象成一串二进制数字。低位的一些比特用来在哈希表内部定位桶bucket剩下的高位比特用来切分批次。因为nbatch和桶数nbuckets都是2的幂所有的切分都是一次掩码操作执行效率极高完全不需要昂贵的整数除法。这种低位定桶、高位定批次的设计还带来一个额外好处一个批次的数据加载进内存后在内部重新分配桶时仍然分布得比较均匀不会出现某个桶特别挤的情况。数据散列质量在这个算法里被反复依赖所以PostgreSQL选用的哈希函数在抗碰撞和均匀性上做了很多考量。2.4 批次数不够就翻倍动态扩容的细节估算永远可能出错。表膨胀了、统计信息过期了、连接键的数据分布和优化器预期不一致都会导致构建过程中内存提前告急。PostgreSQL的应对策略不是报错也不是把整张表直接丢给磁盘而是动态地把批次数翻倍。触发时机在构建阶段当batch 0的哈希表内存占用即将超过work_mem * hash_mem_multiplier时执行器调用相应的扩容逻辑将nbatch翻倍。旧的数据需要按新的批次规则重新计算并写到新的临时文件之后新读入的数据也按新规则划分。这个操作非常昂贵因为它要把已经建好的哈希表整个遍历一遍逐行改判批次归属同时涉及临时文件的重组。如果估算偏差过大、扩容连续发生多次查询的执行时间会被明显拉长。这也能解释为什么会看到EXPLAIN里的Batches数目很高而实际执行计划估算时却显示正常——因为容量是在运行时动态爆发的。为了避免这种尴尬最实用的手段还是让优化器的统计信息足够新鲜。很多突然变慢的hash join其实不是算法出了问题而是ANALYZE没跑优化器拿着过期的行数估计做出了乐观判断。2.5 数据倾斜的缓冲skew hash做了什么数据倾斜是hash join最头疼的场景之一。如果内表的连接键上某个值占了超高比例比如某个城市ID占了四成数据那么这个值的所有数据会集中在少数几个bucket里并发起反复的磁盘读写。PostgreSQL为这种情况留了一个后手skew hash。实现思路是在执行前先根据统计信息里的MCV列表最常见值列表识别出高频倾斜值为这些值单独构建一个常驻内存的小哈希表。探测时如果某行命中了倾斜值直接查这块专用哈希表完全不用走批次流程不是倾斜值才按常规批次处理。这样设计的好处很直观高频值被隔离在内存中无论批次数怎么涨都不会因为倾斜值触发额外的磁盘I/O。代价则是内存多了一块专用区域而且收益严重依赖统计信息准确性。如果MCV列表和真实分布对不上这块内存就白占了。我在生产环境里真正看到skew hash生效的机会并不多但凡是碰上的场景效果都立竿见影。3. 用EXPLAIN把它看个明白3.1 复现一个多批次join理论讲了半天不如自己跑一个例子来得直接。先造两张足够大的表CREATE TABLE t_a ( id int, payload text ); CREATE TABLE t_b ( id int, payload text ); INSERT INTO t_a SELECT g, md5(g::text) || repeat(x, 200) FROM generate_series(1, 1000000) g; INSERT INTO t_b SELECT g, md5(g::text) || repeat(x, 200) FROM generate_series(1, 1000000) g;payload字段故意塞了200多个字符目的是让每张表的数据量远超work_mem构造出溢写条件。然后把work_mem调小一点强制hash join进入多批次模式SET work_mem 512kB; SET enable_nestloop off; SET enable_mergejoin off; EXPLAIN (ANALYZE, BUFFERS, SUMMARY) SELECT count(*) FROM t_a JOIN t_b ON t_a.id t_b.id;不同版本、不同机器环境的输出会有差异但核心字段是稳定的大致看到的样子类似这样Hash Join (cost... rows1000000 width8) (actual time... rows1000000 loops1) Hash Cond: (t_a.id t_b.id) - Seq Scan on t_b ... - Hash Buckets: 16384 Batches: 64 Memory Usage: 1024kBBatches从1变成64说明这张查询已经真正触发了hybrid hash join内外表都被切成了64批内存里永远只保留其中一批。3.2 EXPLAIN输出逐项拆解EXPLAIN里Hash节点的几个字段经常被误读逐个说清楚EXPLAIN字段含义判断要点Hash Cond哈希连接条件必须是等值条件非等值无法hash joinBuckets内存哈希表的桶数量2的幂越大代表桶越密冲突越少Batches批次数量1表示纯内存2以上代表有磁盘溢写Memory Usage哈希表实际内存占用通常接近work_mem与hash_mem_multiplier的乘积特别注意Batches字段。Batches: 2的含义是数据被切成了2批一批常驻内存一批在磁盘上排队。Batches: 64自然就是64批。很多人一看到Batches大于1就紧张这不对——少量批次溢写并不一定是灾难真正要警惕的是批次数持续增长且临时文件体积巨大。另一个观察维度是数据库级别的临时文件统计。可以看系统视图SELECT datname, temp_files, temp_bytes FROM pg_stat_database;如果temp_bytes在某个查询后暴涨基本可以断定hash join或排序发生了严重的溢写。这时候再去结合EXPLAIN里的Batches做对照定位会非常快。3.3 调整work_mem看批次数变化同一个查询把work_mem从16MB调到4MB、1MB、512kBBatches几乎会按比例增长。我自己的测试里一次简单的百万行等值连接work_mem给足时Batches是1压到512kB后变成64执行时间翻了十几倍。于是很多人的第一反应是把work_mem调成1GB不就行了。这种全局调整非常危险因为work_mem是按操作而不是按会话计算的一个连接里多个hash join叠加再加上连接池的并发内存可能瞬间被打爆。更稳妥的做法是BEGIN; SET LOCAL work_mem 256MB; SELECT ...; COMMIT;只有这条事务拿到大内存额度事务结束就释放。如果是通过连接池访问还可以考虑按特定数据库用户单独设置避免拖累其他业务。另外PostgreSQL 13以后可以关注hash_mem_multiplier。把它从默认的2.0往下调哈希操作会更早开始溢写往上调则允许哈希表占用更多内存。这个参数是全局的改动前一定要评估整个实例的并发情况。调优的原则永远是优先减少参与join的数据量其次才考虑给hash join更多内存。4. 实战中容易踩的坑4.1 批次数暴涨查询从几十毫秒变成几十秒这是最典型的事故现场。现象是某条查询平时秒回突然变成几十秒甚至几分钟EXPLAIN ANALYZE一看Batches: 256。排查步骤我建议按这个顺序走先看Batches是不是远大于1这是最直接的线索。查pg_stat_database里的temp_files和temp_bytes确认是不是产生了大量临时文件IO。看执行计划里内表是否因为过滤条件失效而扫描了过多行常见原因是统计信息过期。结合实际情况决定是更新统计信息还是给当前会话临时调大work_mem。有个容易被忽略的点Batches翻倍并不代表IO一定翻倍但趋势是确定的——批次数越大内外表重复读写的次数越多。批次数一旦上了三位数再快的磁盘也扛不住。4.2 估算乐观导致的内存反复rehash另一种让人头疼的情况是EXPLAIN里估算的Batches不大实际执行中途内存却频频告急反复触发动态翻倍。整个过程CPU居高不下临时文件不断增长但SQL计划本身看起来非常正常。根因多半是统计信息失真或者work_mem设得太小导致初始批次数估计过于乐观。解决手段有两个方向第一对新导入大批数据的大表及时ANALYZE第二如果连接列的数据分布本身就不均匀可以考虑提高统计信息采样粒度让MCV列表更准确。这种问题最坑的地方在于它不是每次执行都出现而是跟数据增长节奏相关。如果一条SQL在每个月末都变慢一次可以重点检查是不是月末数据量达到某个阈值撞上了work_mem水位线。4.3 这个执行计划到底行不行和其他join方式怎么权衡hash join不是万能的hybrid hash join更不是所有内存不足场景的最优解。做方案时可以参考这个粗略的判断逻辑场景推荐join关键理由两表等值连接内存充足hash join稳定、高效一侧数据量极小且带索引nested loop可以走索引避免全表扫描两侧数据已按连接键有序merge join省去排序成本等值连接但内存紧张hybrid hash join用磁盘IO换内存非等值连接nested loop / mergehash无法处理非等值条件我自己踩过的教训是千万不要为了省内存直接关掉hash join。把enable_hashjoin设成off优化器会退而选择嵌套循环如果两侧数据量都不小结果往往是内存问题变成CPU和IO问题更糟。真正应该做的是在SQL层面减少参与join的行数比如尽早过滤、预聚合、分区裁剪把问题消灭在源头。5. 我的一点实操心得最后分享几个这几年养成的工作习惯不一定适合所有人但大概率能帮你在数据库性能诊断上少走弯路。第一看执行计划先看Batches。Batches大于1就说明hash join已经溢写到磁盘这时候讨论为什么查询慢必须先确认这个前提。很多人在执行计划上纠结单个算子的cost忽略了最上面的Batches才是真正的性能分水岭。第二把work_mem理解成每个操作而不是整个连接。这是性能诊断里最常见的认知偏差。一个会话同时跑多个hash join内存是成倍叠加的全局调大work_mem之前一定要算并发。以前我在某次压测里把work_mem调成1GB结果40个并发连接直接吃满内存把数据库搞崩这个教训非常深刻。第三调优之前先确认hash join是不是真有必要。减少输入行数永远比增加内存便宜。一条SQL如果先过滤掉90%的数据再做join就算work_mem只有16MB也几乎不会触发溢写反之如果带着几百万行中间结果去join给再多内存也只是用资源换时间。第四回到无状态机的视角。hybrid hash join再复杂抽象到最后就是一句话任何时候内存里只保存一个批次的哈希表其余批次在磁盘上排队等待。理解这个模型之后再去读EXPLAIN的输出、调batch参数、看源码里那些循环逻辑都会顺畅很多。数据在内存和磁盘之间的流转比任何状态图都更接近执行器的真实心跳。
阅读完成 · 觉得有帮助？
