2 个数仓中不等值关联优化案例

2023-10-19
广东
本文字数：1662 字
阅读完需：约 5 分钟

本文分享自华为云社区《GaussDB(DWS)性能调优：不等值关联优化》，作者：门前一棵葡萄树。

场景 1

使用场景：本案例适合满足以下条件的场景

关联条件使用 OR 连接
关联条件中使用同一列做数据筛选

原始语句

SELECT
t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID AS CHANNEL_ID2
FROM
t1
LEFT JOIN
t2
ON
( t2.CHANNEL_ID = t1.CHANNEL_ID AND t1.TYPE = 'DR' )
OR ( t2.PARTNER_CHANNEL_CODE = t1.CHANNEL_ID AND t1.TYPE = 'ALL' )
GROUP BY
t2.PARTNER_CHANNEL_CODE
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID

复制代码

性能分析

通过查询计划分析发现，t1 表和 t2 表关联走了 NEST LOOP，查询整体耗时 45S，NEST LOOP 耗时占用整个查询执行耗时的 96%。因此考虑能否通过 SQL 改写或 HINT 规避 NEST LOOP。观察发现 t1 表和 t2 表包含两个关联关联条件，两个关联条件之间使用 OR 连接，属于非等值关联，因此不能走 HASH JOIN。进一步分析 SQL 发现两个关联条件中都使用 t1.TYPE 进行过滤筛选：

(t2.CHANNEL_ID = t1.CHANNEL_ID AND t1.TYPE='DR')
OR (t2.PARTNER_CHANNEL_CODE = t1.CHANNEL_ID AND t1.TYPE='ALL' )

复制代码

该关联条件包含以下三种关联组合：

t1 表中 t1.TYPE='DR'的行，只能使用第一个关联条件与 t2 表关联；
t1 表中 t1.TYPE='ALL'的行，只能使用第二个关联条件与 t2 表关联；
t1 表中 t1.TYPE NOT IN ('ALL','DR')的行，不与 t2 表关联，直接补空。

t1 表中的一行数据只能选择这三个关联条件中的一个与 t2 表关联，因此该关联条件可以改写为不同关联条件的 UNION ALL（UNION 会去重，不等价）。

优化改写

改写后 SQL 如下所示：

SELECT
CHANNEL_ID
,COUNTRY_CODE
,BRAND
,CHANNEL_ID
FROM
(
SELECT
t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID AS CHANNEL_ID2
FROM
t1
LEFT JOIN
t2
ON
t2.CHANNEL_ID = t1.CHANNEL_ID
WHERE
t1.TYPE = 'DR'
UNION ALL
SELECT
t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID AS CHANNEL_ID2
FROM
t1
t2
ON t2.PARTNER_CHANNEL_CODE = t1.CHANNEL_ID
WHERE t1.TYPE='ALL'
UNION ALL
SELECT
t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID AS CHANNEL_ID2
FROM t1
LEFT JOIN
t2
ON FALSE
WHERE t1.TYPE NOT IN ('ALL','DR')
)
GROUP BY CHANNEL_ID,COUNTRY_CODE,BRAND,CHANNEL_ID

复制代码

改写后 SQL 变为三个子查询的 UNION ALL，执行时间缩减至 1s 以内，性能优化 45 倍。

场景二

使用场景：本案例适合满足以下条件的场景

大表 A 不等值关联小表 B
B 的等值关联字段为主键

【原始语句】

SELECT
T.CREATE_INVOICE_USER,
T.PERIOD_ID,
T.AP_INVOICE_ID,
T.AP_INVOICE_NUM,
T.AP_BATCH_NAME,
EMP1.EMPLOYEE_NO,
EMP1.EMPLOYEE_NAME
FROM DWACTDI.DWR_AP_GLOBAL_INVOICE_DETAIL_F_I T
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1 ON (EMP1.SCD_ACTIVE_IND = 1 AND(T.CREATE_INVOICE_USER = EMP1.EMPLOYEE_NO OR SUBSTR(T.CREATE_INVOICE_USER, 2) = EMP1.EMPLOYEE_NO))

复制代码

【性能分析】

原始语句执行超时(超过 1h)，执行计划如下。可以看到执行语句存在大表 NestLoop 操作

分析发现表 dwrdim_dw1.dwr_dim_employee_d 是维度表，且关联列 employee_no 是主键

【优化改写】

SELECT
T.CREATE_INVOICE_USER,
T.PERIOD_ID,
T.AP_INVOICE_ID,
T.AP_INVOICE_NUM,
T.AP_BATCH_NAME,
nvl(EMP1_0.EMPLOYEE_NO, EMP1_1.EMPLOYEE_NO) AS EMPLOYEE_NO,
nvl(EMP1_0.EMPLOYEE_NAME, EMP1_1.EMPLOYEE_NAME) AS ERP_ACCOUNTANT_ENAME
FROM DWACTDI.DWR_AP_GLOBAL_INVOICE_DETAIL_F_I T
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1_0 ON (EMP1_0.SCD_ACTIVE_IND = 1 AND(T.CREATE_INVOICE_USER = EMP1_0.EMPLOYEE_NO))
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1_1 ON (EMP1_1.SCD_ACTIVE_IND = 1 AND(SUBSTR(T.CREATE_INVOICE_USER, 2) = EMP1_1.EMPLOYEE_NO))