優化數倉業務視圖：過濾條件傳遞-有解無憂

摘要：在業務功能實作時，經常會用到視圖簡化查詢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左右，

點擊關注，第一時間了解華為云新鮮技術~

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/543345.html

標籤：SQL Server

上一篇：資料庫SqlServer遷移PostgreSql實踐

下一篇：資料庫SqlServer遷移PostgreSql實踐