生产环境大型sql语句调优实战第一篇（二）
本文共 8524 字，大约阅读时间需要 28 分钟。
对这条大sql的性能瓶颈进行了分析。主要瓶颈在于一个很大的业务表，数据量在亿级。如果通过时间条件来过滤，会有5%以内的数据被过滤出来。
但是没有时间相关的索引字段，所以会走全表扫描，在目前的产品线中，这个大分区表的索引时严格控制的，所以最后经过测试和比对，还是考虑加并行来提高数据的查取速度。--查找性能瓶颈，
根据反馈，查取的数据其实并不错，可能在几千条以内的样子。但是有很多的查询条件过滤。如果有些大表走了索引，但是join的消耗很大，很可能就是表的查询顺序不当导致的。
有些情况下使用全表扫描的代价要比使用索引要低。像这个例子，排查后，logical_date表中虽然有上千条记录，但是实际上使用的只有一条记录。memo这个表是最大的表，由上亿条记录，走了索引。但是join的效率很差，根据排查,memo这个表是这个查询的关键，需要根据时间来得到前一天的数据变化。如果根据时间来过滤，可以过滤到绝大多数的数据。
上一条记录过滤后只剩下 74811 rows selected.如果关联配置表memo_type查询的数据就会一下子减少到1713条左右，这是对于性能极大的提升和关键。--考虑加入并行如果按照时间来查询，这个大表上没有和时间相关的字段，查询走全表扫描会很长，大概在5分钟左右。--without parallel74811 rows selected.Elapsed: 00:03:23.10
这个时候如果只能走全表扫描，但是想使得速度能够提升，可以考虑并行，加入并行后，查询速度控制在了一分钟以内。
--add table mo1_memo_type, with parllel 81713 rows selected.
--加上配置表的过滤条件，查取的数据更少了，速度也有了提升。
Elapsed: 00:00:41.85但是memo表没有时间相关的索引字段，所以会走全表扫描，在目前的产品线中，这个大分区表的索引时严格控制的，所以最后经过测试和比对，还是考虑加并行来提高数据的查取速度。
--去除笛卡尔积连接
如果是以Memo表作为首发，表的执行计划就有了很大的不同，关联时间时，会不停的去和Logical_date表做关联，其实Logical_date表里只需要一条记录，查看执行计划却走了笛卡尔积连接。-去除笛卡尔积连接可以考虑采用with的句式，把数据先缓存起来，作为后续的查询，就避免了反复全表扫描的消耗。可以把这段子查询抽取出来，在后续的查询中直接使用with LO as (select logical_date from (select logical_date from logical_date where EXPIRATION_DATE IS NULL AND LOGICAL_DATE_TYPE = 'B' AND EXPIRATION_DATE IS NULL)where rownum<2)
--简化sql
可以看到sql语句中存在着很多重复的过滤条件，需要考虑在不改变业务的情况下保证语句的简单易读。--减少/去除全表扫描
尝试减少或者去除全表扫描，保证效率。如果通过sql monitor来监控sql语句的性能，可以发现在最后的查取中，对三个表又走了全表扫描。
(SELECT sum(BR.AMOUNT) FROM BL1_RC_RATES BR, CUSTOMER CU, SUBSCRIBER SS WHERE BR.SERVICE_RECEIVER_ID = SS.SUBSCRIBER_NO AND BR.RECEIVER_CUSTOMER = SS.CUSTOMER_ID AND BR.EFFECTIVE_DATE <= BR.EXPIRATION_DATE AND((SS. SUB_STATUS <> 'C' and SS. SUB_STATUS <> 'T' and BR.EXPIRATION_DATE is null) OR (SS. SUB_STATUS = 'C' and BR.EXPIRATION_DATE like SS.EFFECTIVE_DATE)) AND BR.PP_IND = 'Y' AND BR.CYCLE_CODE = CU.BILL_CYCLE) AS PP_RATE, CU.BILL_CYCLE AS CYCLE_CODE, to_char(NVL(SS.L9_TMV_ACT_DATE, SS.INIT_ACT_DATE),'YYYYMMDD') AS ACTIVATED_DATE, to_char(CD.EFFECTIVE_DATE, 'YYYYMMDD') AS SHOP_EFFECTIVE_DATE,
写这个sql的人是考虑在最后的数据集返回时，根据bl1_rc_rates来选择性的返回数据，但是在总查询中已经关联了customer,subscriber，在这个地方又关联就重复了！冗余的全表扫描就是因为这个导致的。
--子查询最大程度过滤结果集
可以考虑使用一些尽可能过滤较多数据的子查询来提高效率。如果一些表的过滤条件会过滤掉大多数的数据，可以考虑子查询。比如表product 根据soc_type来过滤会排除大多数的数据，可以使用如下的方式( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') co来尽可能直接过滤掉最多的数据。--观察执行计划中表的查取顺序。
做了如上的努力之后，发现还是一些全表扫描，效率貌似更差了。根据我的分析，这些表都应该走索引的。
/*+ leading(MO MOT SS CU CHD CPC CA ) */
最后修正后的sql语句如下：
with LO as (select logical_date from (select logical_date from logical_date where EXPIRATION_DATE IS NULL AND LOGICAL_DATE_TYPE = 'B' AND EXPIRATION_DATE IS NULL)where rownum<2)SELECT /*+ leading(MO MOT SS CU CHD CPC CA ) */ DISTINCT CA.L9_CONVERGENCE_CODE AS ATB2, CU.CUST_SUB_TYPE AS ACCOUNT_TYPE, CST.DESCRIPTION AS ACCOUNT_TYPE_DESC, SS.PRIM_RESOURCE_VAL AS MSISDN, CA.BAN AS BAN_KEY, to_char(MO.MEMO_DATE, 'YYYYMMDD') AS MEMO_DATE, CU.L9_IDENTIFICATION AS THAI_ID, SS.SUBSCRIBER_NO AS SUBS_KEY, SS.DEALER_CODE AS SHOP_CODE, CD.DESCRIPTION AS SHOP_NAME, MOT.SHORT_DESC, REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ;]+', 1, 3) STAFF_ID, MO.OPERATOR_ID AS USER_ID, MO.MEMO_SYSTEM_TEXT, CO2.SOC_NAME AS FIRST_SOCNAME, CO3.SOC_NAME AS PREVIOUS_SOCNAME, CO.SOC_NAME AS CURRENT_SOCNAME, REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ; ]+', 1, 1) NAME, CO.SOC_DESCRIPTION AS CURRENT_PP_DESC, CO3.SOC_DESCRIPTION AS PREV_PP_DESC, CO.SOC_CD AS SOC_CD, (SELECT sum(BR.AMOUNT) FROM BL1_RC_RATES BR,-- CUSTOMER CU, SUBSCRIBER SS --去除冗余的全表扫描 WHERE BR.SERVICE_RECEIVER_ID = SS.SUBSCRIBER_NO AND BR.RECEIVER_CUSTOMER = SS.CUSTOMER_ID AND BR.EFFECTIVE_DATE <= BR.EXPIRATION_DATE AND((SS. SUB_STATUS <> 'C' and SS. SUB_STATUS <> 'T' and BR.EXPIRATION_DATE is null) OR (SS. SUB_STATUS = 'C' and BR.EXPIRATION_DATE like SS.EFFECTIVE_DATE)) AND BR.PP_IND = 'Y' AND BR.CYCLE_CODE = CU.BILL_CYCLE) AS PP_RATE, CU.BILL_CYCLE AS CYCLE_CODE, to_char(NVL(SS.L9_TMV_ACT_DATE, SS.INIT_ACT_DATE),'YYYYMMDD') AS ACTIVATED_DATE, to_char(CD.EFFECTIVE_DATE, 'YYYYMMDD') AS SHOP_EFFECTIVE_DATE, CD.EXPIRATION_DATE AS SHOP_EXPIRED_DATE, CA.L9_COMPANY_CODE AS COMPANY_CODE FROM SERVICE_DETAILS S, --大分区表，千万级数据量，存放着交易的明细信息 ( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') CO, --产品配置表，大概几万条左右 CSM_PAY_CHANNEL CPC, --账务相关表，百万级 ACCOUNT CA, --账务相关表，百万级 SUBSCRIBER SS, --用户相关表，百万级 CUSTOMER CU, --用户相关表，百万级 CUSTOMER_SUB_TYPE CST, --用户配置表，几千条数据 CSM_DEALER CD, --产品配置表，大概几千条左右 SERVICE_DETAILS S2, ( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') CO2, --产品配置表，大概几万条左右 SERVICE_DETAILS S3, ( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') CO3, --产品配置表，大概几万条左右 (select /*+ parallel(T 8)*/ MEMO_ID,ENTITY_ID,MEMO_TYPE_ID,ATTR1VALUE,OPERATOR_ID,MEMO_SYSTEM_TEXT,MEMO_DATE from MO1_MEMO T WHERE T.ENTITY_TYPE_ID = 6 AND TRUNC(T.SYS_CREATION_DATE) = (select TRUNC(LO.LOGICAL_DATE - 1) from lo) ) MO , --交易备注表，数据量过亿 MEMO_TYPE MOT, --配置表，数据量几千 -- LOGICAL_DATE LO, --时间配置表，数据量1千多 CHARGE_DETAILS CHD --交易表，数据量千万 WHERE SS.SUBSCRIBER_NO = CHD.AGREEMENT_NO AND CPC.PYM_CHANNEL_NO = CHD.TARGET_PCN AND CHD.CHG_SPLIT_TYPE = 'DR' AND CHD.EXPIRATION_DATE IS NULL AND S.SOC = CO.SOC_CD AND CO.SOC_TYPE = 'P' AND S.AGREEMENT_NO = SS.SUBSCRIBER_NO AND SS.PRIM_RESOURCE_TP = 'C' AND CPC.PAYMENT_CATEGORY = 'POST' AND CA.BAN = CPC.BAN AND (CA.L9_COMPANY_CODE = 'RF' OR CA.L9_COMPANY_CODE = 'RM' OR CA.L9_COMPANY_CODE = 'TM') AND SS.CUSTOMER_ID = CU.CUSTOMER_ID AND CU.CUST_SUB_TYPE = CST.CUST_SUB_TYPE AND CU.CUSTOMER_TYPE = CST.CUSTOMER_TYPE AND SS.DEALER_CODE = CD.DEALER AND S2.EFFECTIVE_DATE= (SELECT MAX(SA1.EFFECTIVE_DATE) FROM SERVICE_DETAILS SA1--, PRODUCT o1 --去除冗余的表连接 WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO AND co.soc_cd = sa1.soc -- and co.soc_type = 'P' ) AND S2.AGREEMENT_NO = S.AGREEMENT_NO AND S2.SOC = CO2.SOC_CD AND CO2.SOC_TYPE = 'P' AND S2.EFFECTIVE_DATE = (SELECT MIN(SA1.EFFECTIVE_DATE) FROM SERVICE_DETAILS SA1--, PRODUCT o1 WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO AND co2.soc_cd = sa1.soc -- and co2.soc_type = 'P' ) AND S3.AGREEMENT_NO = S.AGREEMENT_NO AND S3.SOC = CO3.SOC_CD AND CO3.SOC_TYPE = 'P' AND S3.EFFECTIVE_DATE = (SELECT MAX(SA1.EFFECTIVE_DATE) FROM SERVICE_DETAILS SA1, PRODUCT o1 WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO AND SA1.EFFECTIVE_DATE < (SELECT MAX(SA1.EFFECTIVE_DATE) FROM SERVICE_DETAILS SA1--, PRODUCT o1 WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO and co3.soc_cd = sa1.soc -- and co3.soc_type = 'P' ) and co3.soc_cd = sa1.soc --and co3.soc_type = 'P' ) AND MO.ENTITY_ID = SS.SUBSCRIBER_NO AND MO.ENTITY_TYPE_ID = 6 AND MO.MEMO_TYPE_ID = MOT.MEMO_TYPE_ID -- AND TRUNC(MO.SYS_CREATION_DATE) = (select TRUNC(LO.LOGICAL_DATE - 1) from lo) -- TRUNC(LO.LOGICAL_DATE - 1) -- AND LO.EXPIRATION_DATE IS NULL -- AND LO.LOGICAL_DATE_TYPE = 'B' --AND LO.EXPIRATION_DATE IS NULL AND (MOT.SHORT_DESC = 'BCN' OR MOT.SHORT_DESC = 'BCNM' OR ............ )
经过反复测试，速度都会保持在2分钟左右，相比40分钟和几个小时来说，绝对是性能的提升。
本文转自ICT时空dbasdk的博客，原文链接：，如需转载请自行联系原博主。
你可能感兴趣的文章
美国慈善机构Kars4Kids意外泄露了上万名捐赠者的个人信息
查看>>
php gc
查看>>
Linux 用户打开进程数的调整
查看>>
shell中的比较与测试
查看>>
find命令使用及实例
查看>>
自定义docker nginx镜像无容器日志输出
查看>>
openwrt安装nginx+php+mysql教程
查看>>
iOS开发之UIPopoverController
查看>>
Exchange Server DAG群集状态部分在线
查看>>
saltstack管理二之saltstack的安装
查看>>
WAN killer
查看>>
大型互联网应用如何进行流量削峰，应对瞬间请求？
查看>>
我的友情链接
查看>>
TCP/UDP-路由交换原理6-【HCNA笔记】
查看>>
file文本练习
查看>>
Timer 与 DelayQueue
查看>>
Objective-C 继承新的认识以及作用
查看>>
请认真使用没有后悔药的parted分区工具
查看>>
一些web缓存相关的概念.cache-control expires no-cache no-store maxage
查看>>
NETDOM的几个用法
查看>>