SQL性能优化经验分享

2010年11月SQL性能优化经验分享21/10/2020SQL跑得慢？1.系统慢？2.还是SQL语句慢？拥堵争抢系统资源任务延时工作压力大客户不满加班加点31/10/2020一点经验•首先，做好准备（查好口径、“前人的遗产”…）•第一步，测试验证SQL可行性•第二步，把SQL再写得好些•最终，时间能大大减少•一个例子B_NBRBETWEEN号段开始AND号段结束VsLEFT(B_NBR,1,7)=LEFT(号段开始,1,7)大家都合适利用系统资源，提高自己效率，减少相互拥堵41/10/2020这个SQL有哪里可以提升性能吗？createtablepmarttemp.tb_zh_grp_mem_list0911as(selmem.c_usr_nbr...from(sel*frompmarttemp.tb_grp_prev_all_items_memwhereint_bill_month=201007)memleftouterjoin(sel*frompmarttemp.tb_grp_prev_all_itemswhereint_bill_month=201007)grpongrp.c_groupid=mem.c_groupidleftouterjoin(sel*frompdata15.TB_CUS_GCUS_HISwheredt_chgstartdate='2010-07-31'anddt_chgenddate'2010-07-31'andc_source_sysin('zh')andsi_cust_stat='stcmNml')ghisonmem.c_groupid=ghis.c_groupid)withdata原来跑了2个小时！51/10/2020这个SQL有哪里可以提升性能吗？createtablepmarttemp.tb_zh_grp_mem_list0911as(selmem.c_usr_nbr...from(sel*frompmarttemp.tb_grp_prev_all_items_memwhereint_bill_month=201007)memleftouterjoin(sel*frompmarttemp.tb_grp_prev_all_itemswhereint_bill_month=201007)grpongrp.c_groupid=mem.c_groupidleftouterjoin(sel*frompdata15.TB_CUS_GCUS_HISwheredt_chgstartdate='2010-07-31'anddt_chgenddate'2010-07-31'andc_source_sysin('zh')andsi_cust_stat='stcmNml')ghisonmem.c_groupid=ghis.c_groupid)withdata历史表大，没有收集统计信息。能放到临时表，这样数据量也清楚了，也可以方便以后使用不是所有子查询的字段都用到，但用了SEL*。加大了IO量，无用功，完成时间加长61/10/2020这个SQL有哪里可以提升性能吗？createtablepmarttemp.tb_zh_grp_mem_list0911as(selmem.c_usr_nbr...from((sel*frompmarttemp.tb_grp_prev_all_items_memwhereint_bill_month=201007)memleftouterjoin(selc_groupid,VC_COUNTYCOMPID_DESC,vc_phototypefrompmarttemp.tb_grp_prev_all_itemswhereint_bill_month=201007)grpongrp.c_groupid=mem.c_groupidleftouterjoin(selc_usr_nbr,mem_kindfrompmarttemp.TB_grp_keyman_infowhereint_bill_month=201007)infoonmem.c_usr_nbr=info.c_usr_nbrleftouterjoinpmarttemp.tb_grp_his_df1102ghisonmem.c_groupid=ghis.c_groupid)withdataprimaryindex(c_usr_nbr)历史数据放到临时表优化后跑了不到4分钟！只取需要的字段71/10/202010个指引概述1.子查询可以用临时表替代2.SELECT子句只应该包含需要的字段3.WHERE条件尽早使用限制结果子集4.不确定大小的查询结果，先执行SAMPLEN5.临时跑数前先做统计信息收集6.不需要用FALLBACK7.显式用ACCESSLOCK8.关联字段重复严重先取需要字段GROUPBY9.选择正确的字段类型10.表尽量建成multiset81/10/20201.子查询可以用临时表替代•避免滥用spoolspace•性能更好•结果大小可预期•保留结果方便以后查数或重用•临时表需要在使用完后进行清理，droptable目前限制开发用户最大500G临时表库pmarttemp将定期清理，请合适定义表名91/10/2020固化临时表•固化临时表，就是把查询结果存放到一张物理表。•供下次分析或他人使用•Session断开之后，仍然可以使用。•示例1：CREATEMULTISETTABLEtttemp.TMP_BOSS_VOICas（select***frompview.vw_net_gsm_nl)WITHnoDATAPRIMARYINDEX(subs_id);INSERTINTOtttemp.TMP_BOSS_VOICSELECT***FROMpview.vw_net_gsm_nlWHERE****;•示例2：CREATEMULTISETTABLEtttemp.TMP_BOSS_VOICas（select***frompview.vw_net_gsm_nlWHERE***)WITHDATAPRIMARYINDEX(subs_id);•示例3：(复制表，数据备份）CREATEMULTISETTABLEtttemp.TMP_BOSS_VOICASpdata.tb_net_gsm_nlWITHDATA;101/10/2020创建可变临时表•它仅存活于同一个Session之内•注意指定可变临时表为multiset（通常也要指定PI）•可变临时表不能带有PPI•例子1:createvolatilemultisettablevt_RETAIN_ANLY_MONas(selectcol1,col2,…from…where…groupby….）withdataPRIMARYINDEX(PI_Cols)ONCOMMITPRESERVEROWS;•例子2:createvolatilemultisettablevt_RETAIN_ANLY_MON(col1char(2),col2varchar(12)NOTNULL）PRIMARYINDEX(PI_Cols)ONCOMMITPRESERVEROWS;明确指定PI是好的习惯111/10/2020Teradata扩展SQL–可变临时表•可变临时表（VolatileTable）是一种比较常用的Teradata临时表•一般用它来存储公共部分的数据，以提高程序的执行效率。它仅存活于同一个Session之内INSERTINTOTarget_tableSelect*From(selproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='02‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='03unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='04‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='14‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='15‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='16‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='22‘)T1Wheretx_date=‘20070701’需要访问表pview.vw_kpi_day7次，效率低下！121/10/2020Teradata扩展SQL–可变临时表（cont.）Createvolatilemultisettablevt_kpi_dayas(Selectproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywheretx_date=‘20070701’)WITHDATAPrimaryIndex(kpi_code)ONCOMMITPRESERVEROWS;INSERTINTOTarget_tableSelect*From(selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='02‘unionselproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='03’unionselproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='04‘unionselproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='14‘unionselproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='15‘unionselproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='16‘unionselproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='22‘)T1只需要访问表pview.vw_kpi_day1次，效率提高！131/10/20202.SELECT子句只应该包含需要的字段•子查询SELECT子句只应该包含需要的字段，不使用*•减少关联子集的大小，节省spoolspace•性能更好141/10/20202.SELECT子句只应该包含需要的字段•减少目标列，可以少消耗SPOOL空间，从而提高SQL的效率当系统任务繁忙，系统内存少的时候，效果尤为明显。•举例：GSM语言话单表，PDATA.TB_NET_GSM_NL共有73字段，以下SQL供返回1.6亿条记录左边的SQL，记录最长为：698字节，平均399字节右边的SQL，记录最长为：59字节，平均30字节两者相差400多GB的SPOOL空间，IO次数也随着相差甚大！SPOOL空间估计：497GBSPOOL空间估计：42GBSELECTSUBS_ID,MSISDN,Begin_Date,Begin_Time,Call_DUR,CHRG_DURFROMPDATA.TB_NET_GSM_NLWHEREPROC_DATEBETWEEN‘20