数据倾斜导致的 Hash Join 性能问题

适用版本

OceanBase 数据库 V2.x、V3.x 版本。

问题现象

两个表的 LEFT OUTER JOIN 在 OceanBase 数据库中的执行时间比在 Oracle 中慢很多倍，其在 Oracle 和 OceanBase 数据库中的执行计划基本相同，均使用了 Hash Join。

问题 SQL：

SELECT count(*) 
FROM CUST_INFO T1  LEFT JOIN CUST_DATA T4 
ON T1.ORG_CODE=T4.ORG_CODE 
AND T4.DATA_DATE='20220621'
WHERE T1.DATA_DATE='20220621';

注：两个表均使用 DATA_DATE 作为分区列。

Oracle 数据库中的执行计划：

OceanBase 数据库中的执行计划：

问题原因

OceanBase 数据库下性能较差的原因：

HASH JOIN 的关联条件为 T1.ORG_CODE=T4.ORG_CODE，ORG_CODE 字段在 T1 表和 T4 表中默认值为 NULL，两表中均存在大量的记录为 NULL。Hahs Join 时 T1 表中 ORG_CODE 为 NULL 的大量记录倾斜在 Hash Table 中，T4 表中大量 ORG_CODE 为 NULL 的记录去 Join Hash Table，执行效率低下。

Oracle 下性能较好的原因：

根据 SQL 规范，对 NULL 值的判断应该用 COL IS NULL 或者 COL IS NOT NULL，COL = NULL 或者 NULL = NULL 的结果是 False。在本案例中，LEFT OUTER JOIN 的关联条件 T1.ORG_CODE=T4.ORG_CODE 隐含着 T4.ORG_CODE IS NOT NULL 的语义。Oracle 数据库会自动增加 T4.ORG_CODE IS NOT NULL，因而在 Hash Join 时可以避免使用 NULL 值的记录去匹配 Hash Table。

解决方法

OceanBase 数据库 V4.x 版本对 Hash Join 做了优化，对于本案例中的场景会自动增加 COL IS NOT NULL 的条件，同时对于存在数据倾斜的场景也进行了自适应的 Join 方式的改进，来提升 Hash Join 的性能。

在 OceanBase 数据库 V2.x、V3.x 版本，可以通过以下方式来提升 SQL 性能。

1. 改写 SQL，增加 IS NOT NULL 的判定。添加 IS NOT NULL 判断需要遵守以下原则。

   • 对于 INNER JOIN，可以在关联条件中（ON 或 WHERE）对左表和右表同时添加 IS NOT NULL。

   • 对于 LEFT JOIN，只可以在关联条件（ON）对右表添加 IS NOT NULL，不能对左表添加 IS NOT NULL，否则可能会导致结果集不正确。

     SELECT count(*) 
     FROM CUST_INFO T1  LEFT JOIN CUST_DATA T4 
     ON T1.ORG_CODE=T4.ORG_CODE 
     AND T4.DATA_DATE='20220621' 
     AND T4.ORG_CODE IS NOT NULL       # 新增
     WHERE T1.DATA_DATE='20220621';
     

2. 考虑使用 Nest-loop Join 或 Merge Join 来代替 Hash Join。

   SELECT /*+ user_nlj(t4) leading(t1 t4)*/count(*) 
   FROM CUST_INFO T1  LEFT JOIN CUST_DATA T4 
   ON T1.ORG_CODE=T4.ORG_CODE 
   AND T4.DATA_DATE='20220621'
   WHERE T1.DATA_DATE='20220621';
   
   SELECT /*+ user_merge(t4) leading(t1 t4)*/count(*) 
   FROM CUST_INFO T1  LEFT JOIN CUST_DATA T4 
   ON T1.ORG_CODE=T4.ORG_CODE 
   AND T4.DATA_DATE='20220621'
   WHERE T1.DATA_DATE='20220621';