本文分享自华为云社区《GaussDB(DWS)性能调优：常量标量子查询做全连接导致整体慢》，作者： Zawami 。
问题描述
由于SQL中存在标量子查询同另一查询做笛卡尔积使SQL整体慢。标量子查询，即结果集只有一行一列的子查询。这里导致的SQL语句执行慢不只是在于做笛卡尔积慢，也会使后续聚合更慢。
原始语句
WITH TMP AS(
SELECT
case
when length('[“202309“]') = 6 then '[“202309“]' || '01'
WHEN length('[“202309“]') 8 THEN TO_CHAR(CURRENT_DATE, 'YYYYMMDD')
END AS V_DATE
from
DUAL
)
SELECT
BG_CODE,
BG_CN_NAME,
BG_EN_NAME,
METRIC_CODE --指标ID
,
METRIC_CN_NAME --指标中文名称
,
METRIC_EN_NAME --指标英文名称
,
CURRENCY --币种
,
OVERSEAS_FLAG,
REGION_CODE,
REGION_CN_NAME,
REGION_EN_NAME,
REPOFFICE_CODE,
REPOFFICE_CN_NAME,
REPOFFICE_EN_NAME,
OFFICE_CODE,
OFFICE_CN_NAME,
OFFICE_EN_NAME,
REGION_CUSTCATG_CODE,
REGION_CUSTCATG_CN_NAME,
REGION_CUSTCATG_EN_NAME,
TOP_CUST_CATEGORY_CODE,
TOP_CUST_CATEGORY_EN_NAME,
TOP_CUST_CATEGORY_CN_NAME,
ACCTCUST_HQ_CODE,
ACCTCUST_HQ_CN_NAME,
ACCTCUST_HQ_EN_NAME,
ACCTCUST_BRANCH_CODE,
ACCTCUST_BRANCH_CN_NAME,
ACCTCUST_BRANCH_EN_NAME,
ACCTCUST_SUBSIDIARY_CODE,
ACCTCUST_SUBSIDIARY_CN_NAM,
ACCTCUST_SUBSIDIARY_EN_NAM,
COUNTRY_CODE --新增加入参
,
COUNTRY_CN_NAME --新增加入参
,
COUNTRY_EN_NAME --新增加入参
,
AGREE_AMOUNT --BUSI_DSCT_00001 总优惠
,
AGREE_REMAIN_AMOUNT --BUSI_DSCT_00002 即期优惠
,
SIGN_AMOUNT --BUSI_DSCT_00003 即期优惠/一次性优惠
,
USE_AMOUNT --BUSI_DSCT_00004 即期优惠/单价量折扣
,
NOT_USED_VALID_AMOUNT --BUSI_DSCT_00005 延期优惠
,
NOT_USED_INVALID_AMOUNT --BUSI_DSCT_00006 voucher
,
NEW_SIGN_AMOUNT --BUSI_DSCT_00007 其他延期优惠
,
NEW_USE_AMOUNT --BUSI_DSCT_00008 本月新使用金额
,
EXPIRED_AMOUNT --BUSI_DSCT_00009 本月已过期金额
,
IMMED_EXPIRED_AMOUNT --BUSI_DSCT_00010 半年内即将过期金额
FROM
(
SELECT
C.BG_CODE,
C.BG_CN_NAME,
C.BG_EN_NAME,
C.M_ID AS METRIC_CODE --指标ID
,
C.M_CN AS METRIC_CN_NAME --指标中文名称
,
C.M_EN AS METRIC_EN_NAME --指标英文名称
,
C.CURRENCY_CODE AS CURRENCY --币种
,CASE
WHEN 1 = 0 THEN C.OVERSEA_FLAG
ELSE NULL
END AS OVERSEAS_FLAG,CASE
WHEN 1 = 0 THEN C.REGION_CODE
ELSE NULL
END AS REGION_CODE,CASE
WHEN 1 = 0 THEN C.REGION_CN_NAME
ELSE NULL
END AS REGION_CN_NAME,CASE
WHEN 1 = 0 THEN C.REGION_EN_NAME
ELSE NULL
END AS REGION_EN_NAME,CASE
WHEN 1 = 0 THEN C.REPOFFICE_CODE
ELSE NULL
END AS REPOFFICE_CODE,CASE
WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME
ELSE NULL
END AS REPOFFICE_CN_NAME,CASE
WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME
ELSE NULL
END AS REPOFFICE_EN_NAME,CASE
WHEN 1 = 0 THEN C.OFFICE_CODE
ELSE NULL
END AS OFFICE_CODE,CASE
WHEN 1 = 0 THEN C.OFFICE_CN_NAME
ELSE NULL
END AS OFFICE_CN_NAME,CASE
WHEN 1 = 0 THEN C.OFFICE_EN_NAME
ELSE NULL
END AS OFFICE_EN_NAME,CASE
WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE
ELSE NULL
END AS REGION_CUSTCATG_CODE,CASE
WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME
ELSE NULL
END AS REGION_CUSTCATG_CN_NAME,CASE
WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME
ELSE NULL
END AS REGION_CUSTCATG_EN_NAME,CASE
WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE
ELSE NULL
END AS TOP_CUST_CATEGORY_CODE,CASE
WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME
ELSE NULL
END AS TOP_CUST_CATEGORY_EN_NAME,CASE
WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME
ELSE NULL
END AS TOP_CUST_CATEGORY_CN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE
ELSE NULL
END AS ACCTCUST_HQ_CODE,CASE
WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME
ELSE NULL
END AS ACCTCUST_HQ_CN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME
ELSE NULL
END AS ACCTCUST_HQ_EN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE
ELSE NULL
END AS ACCTCUST_BRANCH_CODE,CASE
WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME
ELSE NULL
END AS ACCTCUST_BRANCH_CN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME
ELSE NULL
END AS ACCTCUST_BRANCH_EN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE
ELSE NULL
END AS ACCTCUST_SUBSIDIARY_CODE,CASE
WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM
ELSE NULL
END AS ACCTCUST_SUBSIDIARY_CN_NAM,CASE
WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM
ELSE NULL
END AS ACCTCUST_SUBSIDIARY_EN_NAM,CASE
WHEN 1 = 0 THEN C.COUNTRY_CODE
ELSE NULL
END AS COUNTRY_CODE --新增加入参
,CASE
WHEN 1 = 0 THEN C.COUNTRY_CN_NAME
ELSE NULL
END AS COUNTRY_CN_NAME --新增加入参
,CASE
WHEN 1 = 0 THEN C.COUNTRY_EN_NAME
ELSE NULL
END AS COUNTRY_EN_NAME --新增加入参
,
SUM(C.AGREE_AMOUNT) AS AGREE_AMOUNT --协议金额
,
SUM(C.AGREE_REMAIN_AMOUNT) AS AGREE_REMAIN_AMOUNT --协议剩余金额
,
SUM(C.SIGN_AMOUNT) AS SIGN_AMOUNT --可用金额
,
SUM(C.USE_AMOUNT) AS USE_AMOUNT --已使用金额
,
SUM(
CASE
WHEN C.DSCT_TYPE = 'VOUCHER'
AND NVL(
C.EXPIRED_DATE,
add_months(C.EFFECTIVE_DATE, C.VALID_MONTH)
) >= to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT
WHEN C.DSCT_TYPE in (
'FOC',
'Volume Based List Price Adjustment',
'One-Time Discount'
)
AND C.DSCT_END_DATE >= to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT
ELSE NULL
END
) AS NOT_USED_VALID_AMOUNT --未使用金额(有效期外)
,
SUM(
CASE
WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.SIGN_AMOUNT
ELSE C.EFFECTIVE_TOTAL_AMOUNT
END - CASE
WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.USE_AMOUNT
ELSE C.USED_TOTAL_AMOUNT
END - CASE
WHEN C.DSCT_TYPE = 'VOUCHER'
AND NVL(
C.EXPIRED_DATE,
add_months(C.EFFECTIVE_DATE, C.VALID_MONTH)
) >= to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT
WHEN C.DSCT_TYPE in (
'FOC',
'Volume Based List Price Adjustment',
'One-Time Discount'
)
AND C.DSCT_END_DATE >= to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT
ELSE NULL
END
) AS NOT_USED_INVALID_AMOUNT --未使用金额(有效期内)
,
SUM(
CASE
WHEN C.DSCT_TYPE = 'VOUCHER'
AND C.EXPIRED_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm')
and C.EXPIRED_DATE = to_date(substr(T.V_DATE, 1, 6), 'yyyymm')
and C.DSCT_START_DATE = to_date(substr(T.V_DATE, 1, 6), 'yyyymm')
and C.EFFECTIVE_DATE = to_date(substr(T.V_DATE, 1, 6), 'yyyymm')
and C.DSCT_START_DATE
从SQL中可以看到TMP为标量子查询，并且在子查询T中和物理表C做了笛卡尔积。 下面是该SQL的执行计划：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+-------------------------------------------------------------------------+----------------------+---------+---------+------------+----------------+----------+-----------+---------+-----------
1 | -> Row Adapter | 3037.648 | 7 | 245 | | 419KB | | | 1318 | 117210.62
2 | -> Vector Streaming (type: GATHER) | 3037.633 | 7 | 245 | | 777KB | | | 1318 | 117210.62
3 | -> Vector Hash Aggregate | [3031.872, 3032.516] | 7 | 245 | | [4MB, 4MB] | 16MB | [0,870] | 557 | 117128.41
4 | -> Vector Streaming(type: REDISTRIBUTE) | [3031.560, 3032.232] | 112 | 3920 | | [1MB, 1MB] | 2MB | | 557 | 116852.33
5 | -> Vector Hash Aggregate | [2728.059, 2909.255] | 112 | 3920 | | [8MB, 8MB] | 16MB | [833,833] | 557 | 116699.48
6 | -> Vector Nest Loop Left Join (7, 8) | [441.050, 471.725] | 3007901 | 2106919 | | [1MB, 1MB] | 1MB | | 237 | 67316.28
7 | -> CStore Scan on dmsalesw.dm_sale_busi_dsct_sum_f c | [145.354, 158.560] | 3007901 | 2106919 | | [5MB, 5MB] | 1MB | | 205 | 65011.82
8 | -> Vector Materialize | [32.034, 38.902] | 3007901 | 1 | | [288KB, 288KB] | 16MB | [21,21] | 32 | 0.03
9 | -> Vector Subquery Scan on dual | [0.067, 0.093] | 16 | 1 | | [128KB, 128KB] | 1MB | | 32 | 0.02
10 | -> Vector Adapter | [0.005, 0.006] | 16 | 1 | | [40KB, 40KB] | 1MB | | 0 | 0.01
11 | -> Result | [0.001, 0.002] | 16 | 1 | | [8KB, 8KB] | 1MB | | 0 | 0.01
把TMP作为一列放到T中后，性能有明显提升。
EXPLAIN PERFORMANCE
SELECT
BG_CODE,
BG_CN_NAME,
BG_EN_NAME,
METRIC_CODE --指标ID
,
METRIC_CN_NAME --指标中文名称
,
METRIC_EN_NAME --指标英文名称
,
CURRENCY --币种
,
OVERSEAS_FLAG,
REGION_CODE,
REGION_CN_NAME,
REGION_EN_NAME,
REPOFFICE_CODE,
REPOFFICE_CN_NAME,
REPOFFICE_EN_NAME,
OFFICE_CODE,
OFFICE_CN_NAME,
OFFICE_EN_NAME,
REGION_CUSTCATG_CODE,
REGION_CUSTCATG_CN_NAME,
REGION_CUSTCATG_EN_NAME,
TOP_CUST_CATEGORY_CODE,
TOP_CUST_CATEGORY_EN_NAME,
TOP_CUST_CATEGORY_CN_NAME,
ACCTCUST_HQ_CODE,
ACCTCUST_HQ_CN_NAME,
ACCTCUST_HQ_EN_NAME,
ACCTCUST_BRANCH_CODE,
ACCTCUST_BRANCH_CN_NAME,
ACCTCUST_BRANCH_EN_NAME,
ACCTCUST_SUBSIDIARY_CODE,
ACCTCUST_SUBSIDIARY_CN_NAM,
ACCTCUST_SUBSIDIARY_EN_NAM,
COUNTRY_CODE --新增加入参
,
COUNTRY_CN_NAME --新增加入参
,
COUNTRY_EN_NAME --新增加入参
,
AGREE_AMOUNT --BUSI_DSCT_00001 总优惠
,
AGREE_REMAIN_AMOUNT --BUSI_DSCT_00002 即期优惠
,
SIGN_AMOUNT --BUSI_DSCT_00003 即期优惠/一次性优惠
,
USE_AMOUNT --BUSI_DSCT_00004 即期优惠/单价量折扣
,
NOT_USED_VALID_AMOUNT --BUSI_DSCT_00005 延期优惠
,
NOT_USED_INVALID_AMOUNT --BUSI_DSCT_00006 voucher
,
NEW_SIGN_AMOUNT --BUSI_DSCT_00007 其他延期优惠
,
NEW_USE_AMOUNT --BUSI_DSCT_00008 本月新使用金额
,
EXPIRED_AMOUNT --BUSI_DSCT_00009 本月已过期金额
,
IMMED_EXPIRED_AMOUNT --BUSI_DSCT_00010 半年内即将过期金额
FROM
(
SELECT
case
when length('[“202309“]') = 6 then '[“202309“]' || '01'
WHEN length('[“202309“]') 8 THEN TO_CHAR(CURRENT_DATE, 'YYYYMMDD')
END AS V_DATE,
C.BG_CODE,
C.BG_CN_NAME,
C.BG_EN_NAME,
C.M_ID AS METRIC_CODE --指标ID
,
C.M_CN AS METRIC_CN_NAME --指标中文名称
,
C.M_EN AS METRIC_EN_NAME --指标英文名称
,
C.CURRENCY_CODE AS CURRENCY --币种
,CASE
WHEN 1 = 0 THEN C.OVERSEA_FLAG
ELSE NULL
END AS OVERSEAS_FLAG,CASE
WHEN 1 = 0 THEN C.REGION_CODE
ELSE NULL
END AS REGION_CODE,CASE
WHEN 1 = 0 THEN C.REGION_CN_NAME
ELSE NULL
END AS REGION_CN_NAME,CASE
WHEN 1 = 0 THEN C.REGION_EN_NAME
ELSE NULL
END AS REGION_EN_NAME,CASE
WHEN 1 = 0 THEN C.REPOFFICE_CODE
ELSE NULL
END AS REPOFFICE_CODE,CASE
WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME
ELSE NULL
END AS REPOFFICE_CN_NAME,CASE
WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME
ELSE NULL
END AS REPOFFICE_EN_NAME,CASE
WHEN 1 = 0 THEN C.OFFICE_CODE
ELSE NULL
END AS OFFICE_CODE,CASE
WHEN 1 = 0 THEN C.OFFICE_CN_NAME
ELSE NULL
END AS OFFICE_CN_NAME,CASE
WHEN 1 = 0 THEN C.OFFICE_EN_NAME
ELSE NULL
END AS OFFICE_EN_NAME,CASE
WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE
ELSE NULL
END AS REGION_CUSTCATG_CODE,CASE
WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME
ELSE NULL
END AS REGION_CUSTCATG_CN_NAME,CASE
WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME
ELSE NULL
END AS REGION_CUSTCATG_EN_NAME,CASE
WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE
ELSE NULL
END AS TOP_CUST_CATEGORY_CODE,CASE
WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME
ELSE NULL
END AS TOP_CUST_CATEGORY_EN_NAME,CASE
WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME
ELSE NULL
END AS TOP_CUST_CATEGORY_CN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE
ELSE NULL
END AS ACCTCUST_HQ_CODE,CASE
WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME
ELSE NULL
END AS ACCTCUST_HQ_CN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME
ELSE NULL
END AS ACCTCUST_HQ_EN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE
ELSE NULL
END AS ACCTCUST_BRANCH_CODE,CASE
WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME
ELSE NULL
END AS ACCTCUST_BRANCH_CN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME
ELSE NULL
END AS ACCTCUST_BRANCH_EN_NAME,CASE
WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE
ELSE NULL
END AS ACCTCUST_SUBSIDIARY_CODE,CASE
WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM
ELSE NULL
END AS ACCTCUST_SUBSIDIARY_CN_NAM,CASE
WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM
ELSE NULL
END AS ACCTCUST_SUBSIDIARY_EN_NAM,CASE
WHEN 1 = 0 THEN C.COUNTRY_CODE
ELSE NULL
END AS COUNTRY_CODE --新增加入参
,CASE
WHEN 1 = 0 THEN C.COUNTRY_CN_NAME
ELSE NULL
END AS COUNTRY_CN_NAME --新增加入参
,CASE
WHEN 1 = 0 THEN C.COUNTRY_EN_NAME
ELSE NULL
END AS COUNTRY_EN_NAME --新增加入参
,
SUM(C.AGREE_AMOUNT) AS AGREE_AMOUNT --协议金额
,
SUM(C.AGREE_REMAIN_AMOUNT) AS AGREE_REMAIN_AMOUNT --协议剩余金额
,
SUM(C.SIGN_AMOUNT) AS SIGN_AMOUNT --可用金额
,
SUM(C.USE_AMOUNT) AS USE_AMOUNT --已使用金额
,
SUM(
CASE
WHEN C.DSCT_TYPE = 'VOUCHER'
AND NVL(
C.EXPIRED_DATE,
add_months(C.EFFECTIVE_DATE, C.VALID_MONTH)
) >= to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT
WHEN C.DSCT_TYPE in (
'FOC',
'Volume Based List Price Adjustment',
'One-Time Discount'
)
AND C.DSCT_END_DATE >= to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT
ELSE NULL
END
) AS NOT_USED_VALID_AMOUNT --未使用金额(有效期外)
,
SUM(
CASE
WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.SIGN_AMOUNT
ELSE C.EFFECTIVE_TOTAL_AMOUNT
END - CASE
WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.USE_AMOUNT
ELSE C.USED_TOTAL_AMOUNT
END - CASE
WHEN C.DSCT_TYPE = 'VOUCHER'
AND NVL(
C.EXPIRED_DATE,
add_months(C.EFFECTIVE_DATE, C.VALID_MONTH)
) >= to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT
WHEN C.DSCT_TYPE in (
'FOC',
'Volume Based List Price Adjustment',
'One-Time Discount'
)
AND C.DSCT_END_DATE >= to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT
ELSE NULL
END
) AS NOT_USED_INVALID_AMOUNT --未使用金额(有效期内)
,
SUM(
CASE
WHEN C.DSCT_TYPE = 'VOUCHER'
AND C.EXPIRED_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm')
and C.EXPIRED_DATE = to_date(substr(V_DATE, 1, 6), 'yyyymm')
and C.DSCT_START_DATE = to_date(substr(V_DATE, 1, 6), 'yyyymm')
and C.EFFECTIVE_DATE = to_date(substr(V_DATE, 1, 6), 'yyyymm')
and C.DSCT_START_DATE
下面是执行计划：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+-------------------------------------------------------------------------+----------------------+---------+---------+------------+----------------+----------+-----------+---------+-----------
1 | -> Row Adapter | 1139.637 | 7 | 245 | | 419KB | | | 1318 | 117002.27
2 | -> Vector Streaming (type: GATHER) | 1139.616 | 7 | 245 | | 777KB | | | 1318 | 117002.27
3 | -> Vector Subquery Scan on t | [1129.463, 1130.072] | 7 | 245 | | [504KB, 504KB] | 1MB | | 1318 | 116920.22
4 | -> Vector Hash Aggregate | [1129.459, 1130.067] | 7 | 245 | | [4MB, 4MB] | 16MB | [0,898] | 523 | 116920.07
5 | -> Vector Streaming(type: REDISTRIBUTE) | [1129.142, 1129.918] | 112 | 3920 | | [1MB, 1MB] | 2MB | | 523 | 116643.28
6 | -> Vector Hash Aggregate | [882.194, 987.474] | 112 | 3920 | | [8MB, 8MB] | 16MB | [861,861] | 523 | 116498.95
7 | -> CStore Scan on dmsalesw.dm_sale_busi_dsct_sum_f c | [126.343, 142.697] | 3080954 | 2135243 | | [5MB, 5MB] | 1MB | | 203 | 66116.77
可以看到，不但省去了Nest Loop的耗时，而且后面Aggregate的耗时也减少了不少。整体从3s+优化到1.2s。
点击关注，第一时间了解华为云新鲜技术~
