GaussDB(DWS)性能调优：indexscan导致的性能问题识别与优化

海涛他哥 · 发表于 2023-2-14 12:43:08

摘要：通常跑批加工场景下，都是大数量做关联操作，通常不建议使用索引。有些时候因为计划误判导致使用索引的可能会导致严重的性能问题。本文从一个典型的索引导致性能的场景重发，剖析此类问题的特征，定位方法和解决方法

本文分享自华为云社区《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
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！

GaussDB(DWS)性能调优：indexscan导致的性能问题识别与优化

本帖子中包含更多资源