最佳化數倉業務檢視：過濾條件傳遞

華為雲開發者聯盟發表於2023-02-08
摘要：在業務功能實現時，經常會用到檢視簡化查詢SQL。但有時候會因為檢視降低查詢效率，本文主要分析在業務需求滿足的情況下，將有效的過濾條件傳遞到基表，減少運算過程中資料庫需要處理的資料量，提升SQL執行效率。

本文分享自華為雲社群《GaussDB(DWS)業務檢視最佳化-過濾條件傳遞》，作者：衛小毛 。

在業務功能實現時，經常會用到檢視簡化查詢SQL。但有時候會因為檢視降低查詢效率，本文主要分析在業務需求滿足的情況下，將有效的過濾條件傳遞到基表，減少運算過程中資料庫需要處理的資料量，提升SQL執行效率。

SQL舉例

SELECT
    count(1) AS have_done_num,
    t1.task_def_key_ AS menuguid
FROM
    vw_pay_voucher_bill t2
LEFT JOIN xact_hi_taskinst t1 ON t1.business_key_ = t2.id
AND t1.proc_def_key_ = 'pay_voucher_bill'
AND t1.operation_flag_ IN ('NORMAL', 'WITHDRAW')
AND t1.suspension_state_ = 1
AND t1.org_code_ = t2.mof_div_code
AND delete_reason_ = 'completed'
AND ext1_ IS NULL
WHERE
    t2.is_deleted = '2'
AND t2.fiscal_year = '2022'
AND t2.mof_div_code = 'xxxxxxxx0'
AND (
    agency_id = '5A1xxxxxxxxxxxxxxxxxxx4T5'
)
GROUP BY
    t1.task_def_key_
HAVING
    t1.task_def_key_ IS NOT NULL;

sql 分析：以上SQL vw_pay_voucher_bill t2 、xact_hi_taskinst t1 檢視和表進行關聯查詢

根據業務特性分析過濾效果較好的欄位為 agency_id

最佳化前耗時： 22s

最佳化數倉業務檢視：過濾條件傳遞

分析執行計劃：

時間主要耗時在 seq scan on pay_voucher_bill v 這一步

看到該表過濾條件僅有mof_div_code、fiscal_year、is_deleted 過濾效果差，幾乎全表資料參與過程運算，執行代價高

檢視及表結構分析：

最佳化數倉業務檢視：過濾條件傳遞

檢視中關聯條件較為有效的過濾條件，bgt_id 欄位查詢時不會應用。分析檢視中“v”和“t”表都存在agency_id 欄位，當前t表過濾使用了agency_id欄位，可以考慮檢視定義中量表關聯條件增加 agency_id 欄位關聯條件需要考慮業務需求。

同業務溝通後可進行最佳化

最佳化數倉業務檢視：過濾條件傳遞

最佳化後耗時：0.4s

最佳化數倉業務檢視：過濾條件傳遞

對比最佳化前後SQL查詢結果一致

最佳化總結：

同業務側研發溝通客戶實際需要僅需要查詢本單位 （agency_id） 下的資料，但因為SQL和檢視設計時，並未將這一有效條件傳遞給每張表。導致資料庫在針對 pay_voucher 進行資料過濾時需要將全表64萬+ 資料篩選出來進行運算，僅僅這一步開銷就佔用了20s+。在最佳化後（檢視中增加agency_id關聯資訊後，該操作可將agency_id 過濾條件傳遞給基表 pay_voucher），僅需從pay_voucher 表中獲取738行資料進行運算，最終sql耗時降為 0.4s左右。

 

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

相關文章