數倉性能調優：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)

改寫後執行計劃如下：

可以看到，執行計劃中雖然51層運算元只快了200ms，但由於減少阻塞，1~7層運算元的執行時間縮短了，總體比原先快了約480ms。

• 附件：供應鏈-88-PLAN.txt
• 附件：供應鏈-88-改-PLAN.txt

點擊關註，第一時間瞭解華為雲新鮮技術~