数仓性能调优：row_number() over(p)-rn=1 性能瓶颈发现和改写套路

作者：华为云开发者联盟

2023-11-28
广东
本文字数：1302 字
阅读完需：约 4 分钟

数仓性能调优：row_number() over(p)-rn=1性能瓶颈发现和改写套路

本文分享自华为云社区《GaussDB(DWS)性能调优：row_number() over(p)-rn=1性能瓶颈发现和改写套路》，作者：Zawami 。

1、改写场景

本套路应用于子查询中含有 row_number() over(partition by order by) rn，并仅把 rn 列用于分类排序后筛选最大值的场景。

2、性能分析

GaussDB 中 SQL 语句的执行很多时候是流式的，即对每一条数据进行流水加工，各层算子同时在执行，缩短执行耗时。

但是在一些场景下，需要先取得前一个算子的全部结果集，然后才能够进行下一步的加工；窗口函数就是其中的一种。

观察执行计划可以看到，SQL 会在计算得到 rn 列后，再同本层查询其它列进行关联。由于存在窗口函数，必须先把 51 号算子先执行完，然后才能进行关联，造成性能瓶颈。

通过去窗口函数改写，我们可以使得分类汇总同明细数据之间的关联流水执行。

改写前局部 SQL

SELECT
PROD_EN_NAME,
PROD_LIFE_CYCLE_STATUS
FROM
(
SELECT
PROD_EN_NAME,
LIFE_CYCLE AS PROD_LIFE_CYCLE_STATUS,
DEL_FLAG,
ROW_NUMBER ( ) OVER ( PARTITION BY PROD_EN_NAME ORDER BY RUN_DATE DESC ) RN
FROM
DMISC.DM_DIM_INV_PROD_ATTRI_SNAP_D
WHERE
DATA_TYPE = 1


AND DEL_FLAG = 'N'
AND RUN_DATE <= CAST ( '2023-06-11' || ' 00:00:00' AS TIMESTAMP )
)
WHERE
RN = 1

复制代码

改写后局部 SQL

WITH T AS (
SELECT
PROD_EN_NAME,
MAX ( LIFE_CYCLE ) AS PROD_LIFE_CYCLE_STATUS,
RUN_DATE
FROM
DMISC.DM_DIM_INV_PROD_ATTRI_SNAP_D
WHERE
DATA_TYPE = 1
AND DEL_FLAG = 'N'
AND RUN_DATE <= CAST ( '2023-06-11' || ' 00:00:00' AS TIMESTAMP )
GROUP BY
PROD_EN_NAME,
RUN_DATE
)
SELECT
PROD_EN_NAME,
PROD_LIFE_CYCLE_STATUS
FROM T
WHERE
(PROD_EN_NAME, RUN_DATE) IN (SELECT PROD_EN_NAME, MAX(RUN_DATE) FROM T GROUP BY PROD_EN_NAME)

复制代码

改写解析：这里先把数据根据原 SQL 中 row_number() over()的 partition 列和 order 列进行去重，由于原 SQL 未定义 LIFE_CYCLE 的排序方式，改写既可以使用 MAX 也可以使用 MIN 函数来进行聚合。然后再对去重后的数据进行过滤，过滤条件显然。

使用这种修改方法，修改前后的全量执行计划已在附件中给出。

这种改写方式解决了上层算子等窗口函数的问题。我们发现，一些业务场景下对不涉及聚合的其它列，比如上面例子中的 LIFE_CYCLE 并不敏感，且还需要进行进一步聚合的，那么对本层子查询中的去重其实没有硬性需求。可以进一步去除这层去重。

WITH T AS (
SELECT
PROD_EN_NAME,
LIFE_CYCLE AS PROD_LIFE_CYCLE_STATUS,
RUN_DATE
FROM
DMISC.DM_DIM_INV_PROD_ATTRI_SNAP_D
WHERE
DATA_TYPE = 1
AND DEL_FLAG = 'N'
AND RUN_DATE <= CAST ( '2023-06-11' || ' 00:00:00' AS TIMESTAMP )
)
SELECT
PROD_EN_NAME,
PROD_LIFE_CYCLE_STATUS
FROM T
WHERE
(PROD_EN_NAME, RUN_DATE) IN (SELECT PROD_EN_NAME, MAX(RUN_DATE) FROM T GROUP BY PROD_EN_NAME)