百木园摘要：通常跑批加工场景下，都是大数量做关联操作，通常不建议使用索引。有些时候因为计划误判导致使用索引的可能会导致严重的性能问题。本文从一个典型的索引导致性能的场景重发，剖析此类问题的特征，定位方法和解决方法
本文分享自华为云社区《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.cnblogs.com/huaweiyun/p/17119085.html
本站部分图文来源于网络，如有侵权请联系删除。