摘要：通常跑批加工場景下，都是大數量做關聯操作，通常不建議使用索引，有些時候因為計劃誤判導致使用索引的可能會導致嚴重的性能問題，本文從一個典型的索引導致性能的場景重發，剖析此類問題的特征，定位方法和解決方法
本文分享自華為云社區《GaussDB(DWS)性能調優：indexscan導致的性能問題識別與優化 #【玩轉PB級數倉GaussDB(DWS)】》，作者： 譡里個檔 ，
通常跑批加工場景下，都是大數量做關聯操作，通常不建議使用索引，有些時候因為計劃誤判導致使用索引的可能會導致嚴重的性能問題，本文從一個典型的索引導致性能的場景重發，剖析此類問題的特征，定位方法和解決方法，
1）在某局點POC測驗時發現某SQL陳述句比較慢，原始SQL如下
WITH /** etl_116583_7960703_994644 **/ LOADABLE as (select "boq_rel_type_id","to_pu_id","to_version","cycle_id", "part_offset_flag","to_boq_id","descr","from_contract_id", "from_version","from_pu_id","ss_id","to_contract_id", "from_boq_id","enable_flag","last_update_date" from (SELECT /*+ PARALLEL(4)*/ BOQ_REL.FROM_BOQ_ID, BOQ_REL.TO_BOQ_ID, BOQ_REL.FROM_PU_ID, BOQ_REL.TO_PU_ID, BOQ_REL.PART_OFFSET_FLAG, BOQ_REL.DESCR, BOQ_REL.SS_ID, BOQ_REL.CYCLE_ID, NVL(BOQ_REL.FROM_VERSION, 'SNULL') FROM_VERSION, NVL(BOQ_REL.TO_VERSION, 'SNULL') TO_VERSION, BOQ_REL.LAST_UPDATE_DATE, FROM_CON.CONTRACT_ID AS FROM_CONTRACT_ID, TO_CON.CONTRACT_ID AS TO_CONTRACT_ID, CLA.CLASS_ID AS BOQ_REL_TYPE_ID, BOQ_REL.ENABLE_FLAG FROM (SELECT A.FROM_BOQ_ID, A.TO_BOQ_ID, A.FROM_PU_ID, A.TO_PU_ID, A.FROM_CONTRACT_NUMBER, A.TO_CONTRACT_NUMBER, A.BOQ_REL_TYPE_CODE, A.PART_OFFSET_FLAG, A.DESCR, A.SS_ID, A.FROM_VERSION, A.TO_VERSION, A.LAST_UPDATE_DATE, A.CYCLE_ID, A.ENABLE_FLAG, DECODE(A.SS_ID, 2820, 2600, A.SS_ID) SS_ID_TMP, ROW_NUMBER() OVER(PARTITION BY FROM_BOQ_ID, TO_BOQ_ID, FROM_PU_ID, TO_PU_ID, FROM_CONTRACT_NUMBER, TO_CONTRACT_NUMBER, BOQ_REL_TYPE_CODE, FROM_VERSION, TO_VERSION ORDER BY DECODE(A.SS_ID, 2820, 1, 2600, 2, 3)) RN FROM LDB_MD_BOQ_REL A) BOQ_REL, (SELECT CONTRACT_ID, HW_CONTRACT_NUM, SS_ID FROM DWI_MD_CONTRACT WHERE CONTRACT_ID IS NOT NULL AND END_TIME = TO_DATE('4712-12-31', 'YYYY-MM-DD')) FROM_CON, (SELECT CONTRACT_ID, HW_CONTRACT_NUM, SS_ID FROM DWI_MD_CONTRACT WHERE CONTRACT_ID IS NOT NULL AND END_TIME = TO_DATE('4712-12-31', 'YYYY-MM-DD')) TO_CON, (SELECT CLASS_ID, CODE, CLASS_TYPE_ID, SS_ID FROM DWI_MD_CLASS WHERE CLASS_TYPE_ID = 193) CLA WHERE BOQ_REL.RN = 1 AND BOQ_REL.FROM_CONTRACT_NUMBER = FROM_CON.HW_CONTRACT_NUM AND BOQ_REL.SS_ID = FROM_CON.SS_ID AND BOQ_REL.TO_CONTRACT_NUMBER = TO_CON.HW_CONTRACT_NUM AND BOQ_REL.SS_ID = TO_CON.SS_ID AND BOQ_REL.BOQ_REL_TYPE_CODE = CLA.CODE AND BOQ_REL.SS_ID_TMP = CLA.SS_ID ) t ), BEFORE_TARGET as (select "from_contract_id","from_pu_id","ss_id","from_boq_id","from_version","to_version", "crt_cycle_id","to_pu_id","to_boq_id","del_flag","last_upd_cycle_id","last_update_date", "descr","enable_flag","crt_job_instance_id","dq_improve_flag","upd_job_instance_id", "to_contract_id","part_offset_flag","boq_rel_type_id" from (SELECT /*+PARALLEL(4)*/ FROM_BOQ_ID, TO_BOQ_ID, FROM_PU_ID, TO_PU_ID, FROM_CONTRACT_ID, TO_CONTRACT_ID, BOQ_REL_TYPE_ID, PART_OFFSET_FLAG, DESCR, SS_ID, CRT_CYCLE_ID, LAST_UPD_CYCLE_ID, DEL_FLAG, DQ_IMPROVE_FLAG, CRT_JOB_INSTANCE_ID, UPD_JOB_INSTANCE_ID, NVL(FROM_VERSION, 'SNULL') FROM_VERSION, NVL(TO_VERSION, 'SNULL') TO_VERSION, LAST_UPDATE_DATE, ENABLE_FLAG FROM DWI_MD_BOQ_REL ) t ), CDC as (select LOADABLE."ss_id",LOADABLE."from_version",LOADABLE."from_boq_id", LOADABLE."part_offset_flag",LOADABLE."from_pu_id", case when BEFORE_TARGET.BOQ_REL_TYPE_ID is null and BEFORE_TARGET.FROM_BOQ_ID is null and BEFORE_TARGET.FROM_CONTRACT_ID is null and BEFORE_TARGET.FROM_PU_ID is null and BEFORE_TARGET.FROM_VERSION is null and BEFORE_TARGET.TO_BOQ_ID is null and BEFORE_TARGET.TO_CONTRACT_ID is null and BEFORE_TARGET.TO_PU_ID is null and BEFORE_TARGET.TO_VERSION is null then 1 else 3 end as "change_code", LOADABLE."to_version",LOADABLE."boq_rel_type_id", LOADABLE."from_contract_id",LOADABLE."to_contract_id", LOADABLE."descr",LOADABLE."last_update_date", LOADABLE."to_pu_id",LOADABLE."enable_flag",LOADABLE."cycle_id", LOADABLE."to_boq_id" from LOADABLE left join BEFORE_TARGET on LOADABLE.BOQ_REL_TYPE_ID = BEFORE_TARGET.BOQ_REL_TYPE_ID and LOADABLE.FROM_BOQ_ID = BEFORE_TARGET.FROM_BOQ_ID and LOADABLE.FROM_CONTRACT_ID = BEFORE_TARGET.FROM_CONTRACT_ID and LOADABLE.FROM_PU_ID = BEFORE_TARGET.FROM_PU_ID and LOADABLE.FROM_VERSION = BEFORE_TARGET.FROM_VERSION and LOADABLE.TO_BOQ_ID = BEFORE_TARGET.TO_BOQ_ID and LOADABLE.TO_CONTRACT_ID = BEFORE_TARGET.TO_CONTRACT_ID and LOADABLE.TO_PU_ID = BEFORE_TARGET.TO_PU_ID and LOADABLE.TO_VERSION = BEFORE_TARGET.TO_VERSION ), TFM_FILTER_DATA_TARGET_OUTPUT_U as (select CDC."to_pu_id",CDC."boq_rel_type_id",CDC."ss_id", Current_Timestamp() as "dw_last_update_date",CDC."to_version", CDC."from_version",20230104000000 as "last_upd_cycle_id", CDC."from_contract_id",CDC."last_update_date",CDC."descr", 'N' as "del_flag",CDC."from_boq_id",CDC."to_boq_id", CDC."enable_flag",CDC."from_pu_id",-1 as "upd_job_instance_id", 'N' as "dq_improve_flag",CDC."to_contract_id", CDC."part_offset_flag" from CDC where CDC.change_code=3 ) update DWI_MD_BOQ_REL TARGET_U set "dq_improve_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."dq_improve_flag", "dw_last_update_date" = TFM_FILTER_DATA_TARGET_OUTPUT_U."dw_last_update_date", "upd_job_instance_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."upd_job_instance_id", "descr" = TFM_FILTER_DATA_TARGET_OUTPUT_U."descr", "part_offset_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."part_offset_flag", "last_update_date" = TFM_FILTER_DATA_TARGET_OUTPUT_U."last_update_date", "del_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."del_flag", "last_upd_cycle_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."last_upd_cycle_id", "enable_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."enable_flag", "ss_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."ss_id" from TFM_FILTER_DATA_TARGET_OUTPUT_U where TARGET_U."boq_rel_type_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."boq_rel_type_id" and TARGET_U."to_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_version" and TARGET_U."to_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_version" and TARGET_U."to_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_pu_id" and TARGET_U."to_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_pu_id" and TARGET_U."to_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_contract_id" and TARGET_U."to_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_contract_id" and TARGET_U."to_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_boq_id" and TARGET_U."to_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_boq_id" and TARGET_U."from_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_version" and TARGET_U."from_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_version" and TARGET_U."from_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_pu_id" and TARGET_U."from_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_pu_id" and TARGET_U."from_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_contract_id" and TARGET_U."from_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_contract_id" and TARGET_U."from_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_boq_id" and TARGET_U."from_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_boq_id" and TARGET_U."boq_rel_type_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."boq_rel_type_id" ;
2）查詢此query的topSQL資訊的warning欄位，發現SQL自診斷資訊中有索引相關告警資訊，
3）查詢此query的topSQL資訊(如下圖)，分析歷史執行資訊，發現id=20的CStore Index Scan算子的耗時為90796.980ms，SQL執行總時長137135.658ms，CStore Index Scan算子的耗時占比為 66%
4）找到原始SQL陳述句，對查詢陳述句中出現的表dwimd.dwi_md_contract 進行hint，強制其走順序掃描，避免走indexscan（全量陳述句見附件）
5）對陳述句進行explain verbose，查看計劃，發現計劃符合預期（即表dwimd.dwi_md_contract走tablescan，對于列存表計劃上顯式為CStore Scan）
6）對陳述句執行EXPLAIN ANALYZE操作(即實際執行陳述句)，查看實際執行時間如下，發現SQL陳述句性能提升近10倍，全量的執行資訊見附件
- 附件：hint后的explain analyze.txt14.76KB
- 附件：hint后的query.txt7.69KB
點擊關注，第一時間了解華為云新鮮技術~
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/543937.html
標籤：MySQL