从Hash Join的执行计划的细节中能看到点啥

数据库 其他数据库
不过任何事情都是有利有弊,如果你的服务器的CPU资源十分紧张,那么过多的并行HASH JOIN可能会导致你的CPU资源经常出现不足,引发其他问题。如果存在这种情况,那么关闭并行HASH JOIN,让每个HASH JOIN变得略微慢一点,但是确保CPU资源不过载,也是一种策略。​

​HASH JOIN是大数据量表连接中最为常用的方式,与最为常用的NESTED LOOP相比,其应用场景不同。对于两张表的连接,NESTED LOOP适合于类似查电话号码的应用场景,如果领导给你一张清单,让你去查一下几家企业的电话号码,那么你要做的是找到一本电话号码本,根据公司名称的索引,挨个查一遍,很快就可以完成了。这种方式就是著名的NESTED LOOP,通过数个快速的循环,完成两个行源的关联操作(待查清单,电话号码簿)。

如果这个任务改一下,领导给你的清单上有几万家企业,那么我们还这么一条条的去查,那不傻了。这时候,就不适合用NESTED LOOP循环了,HASH JOIN是比较快速的解决方法。很多SQL的执行计划出现错误,有很大一部分就是选择错误使用了NESTED LOOP和HASH JOIN。因此现在一些CBO的优化器中,都有针对NESTED LOOP和HASH JOIN的主动纠偏技术。Oracle 19C的可调节执行计划主要就是在执行NESTED LOOP的过程中一旦发现循环数量超出评估预期,则可以动态改为HASH JOIN。

刚开始就有点扯远了,今天我们的重点不是讨论NL和HASH JOIN的差异,而是带大家看看PG数据库的HASH JOIN执行计划中的一些容易被忽略的点,在查看执行计划的时候,如果能够比较好的抓住这些关注点,对于SQL优化来说很有帮助。

图片

可能有朋友要说了,反正都是HASH JOIN,执行计划都差不多,有啥可看的。那么我们来看看上面的执行计划里的红框里的内容吧,Batches :32,这个是啥意思?如果你以前是Oracle DBA,那么优化排序、one-pass 排序,multi-pass排序的概念应该还有印象吧。当需要做排序或者HASH TABLE的数据量太大,超出了SORT AREA SIZE的限制,那么这次排序/HASH join就无法一次完成,必须切分为多个分区,一个个的完成。在PG的HASH JOIN里,就是把HASH JOIN切分为多个BATCHES。因为某个BATCH完成后需要暂存在临时文件中,因此遇到这种情况我们一般都可以看到temp written这个内容,这部分内容我也用红框标注出来了。

图片

这种排序区不足导致的问题会带来什么样的性能问题呢?我们来看这个例子,BATCHES:1,也就是无需通过分区完成,此时使用了4540KB的WORK_MEM。实际上我给大家演示这个案例的时候,第一个例子用了256KB的work_mem设置,当然无法满足4M多的内存需求了。而第二个例子我使用了一个极大的work_mem(256MB),当然实际上的内存使用以执行计划中的为准。一次性在内存中完成HASH JOIN的好处是什么呢?当然是执行效率,我们可以看出第二个执行只用了90毫秒,而分裂为32个BATCH的执行花了239毫秒。

看到这里可能有朋友要说了,既然效果那么好,那么我们把WORK_MEM参数设的足够大不就行了。实际上设置过大的WORK_MEM也是存在隐患的。如果我们的物理内存不是很大,那么设置过大的WORK_MEM可能导致极端情况下,物理内存过度消耗而导致更严重的问题。

WORK_MEM参数是可会话级动态设置的,如果我们的某些要做大型排序或者HASH JOIN的SQL能够在应用层面做设置,执行大型SQL的时候设置一个较大的值,SQL执行完毕RESET一下参数,这样WORK_MEM的使用效率是最高的。否则我们为了满足大型SQL的需求,就需要设置一个做大值。当然虽然我们设置了WORK_MEM并不一定就会消耗那么多的内存,不过活跃会话数*WORK_MEM这个数字还是需要关注的,确保我们的物理内存有那么多的空闲可用(参考可用内存,而不是FREE内存)是十分必要的。如果我们不确定系统最大的内存使用量,并且物理内存比较紧张,那么设置大一点的SWAP是十分必要的,在极端情况下可以确保系统不会因为OOM而出大问题。

图片

上面的这个执行计划也是我们经常看到的,PG数据库支持并行HASH JOIN,并且默认是打开的。如果我们的系统中的CPU资源是充足的,那么enable_parallel_hash参数确保打开状态就行了。并行HASH JOIN可以通过过parallel seq scan和parallel hash join两种机制来进一步提高HASH JOIN的性能。我们可以看到,通过并发,这个SQL的执行效率进一步的提升了。

不过任何事情都是有利有弊,如果你的服务器的CPU资源十分紧张,那么过多的并行HASH JOIN可能会导致你的CPU资源经常出现不足,引发其他问题。如果存在这种情况,那么关闭并行HASH JOIN,让每个HASH JOIN变得略微慢一点,但是确保CPU资源不过载,也是一种策略。​

责任编辑:武晓燕 来源: 白鳝的洞穴
相关推荐

2021-12-13 22:15:29

SQLOracle共享池

2011-09-14 17:03:17

数据库执行计划解析

2020-09-15 08:44:57

MySQL慢日志SQL

2009-11-10 16:00:05

Oracle执行计划

2010-04-16 09:27:18

Ocacle执行计划

2021-04-28 22:42:36

SaaS软件技术

2022-08-15 15:09:26

SQL数据库MySQL

2011-03-14 17:36:12

DB2更新执行计划

2022-02-15 07:36:21

SQLEXPLAIN数据库

2021-03-17 09:35:51

MySQL数据库explain

2015-04-22 14:17:45

SQL SERVERMSSQL SERVE缓冲区

2023-09-21 10:55:51

MysqlSQL语句

2021-05-28 10:46:36

MySQL执行计划

2011-05-17 09:32:25

DB2

2022-08-08 08:03:44

MySQL数据库CBO

2011-08-18 14:10:51

Oracle不走索引

2009-11-13 16:28:02

Oracle生成执行计

2010-09-07 14:11:04

DB2更新

2010-10-27 15:26:42

Oracle执行计划

2010-07-27 14:46:34

DB2执行计划
点赞
收藏

51CTO技术栈公众号