生产环境大型sql语句调优实战第一篇(二) (r2笔记32天)

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进行了分析。主要瓶颈在于一个很大的业务表,数据量在亿级。如果通过时间条件来过滤,会有5%以内的数据被过滤出来。 但是没有时间相关的索引字段,所以会走全表扫描,在目前的产品线中,这个大分区表的索引时严格控制的,所以最后经过测试和比对,还是考虑加并行来提高数据的查取速度。 --查找性能瓶颈, 根据反馈,查取的数据其实并不错,可能在几千条以内的样子。但是有很多的查询条件过滤。 如果有些大表走了索引,但是join的消耗很大,很可能就是表的查询顺序不当导致的。 有些情况下使用全表扫描的代价要比使用索引要低。 像这个例子,排查后,logical_date表中虽然有上千条记录,但是实际上使用的只有一条记录。 memo这个表是最大的表,由上亿条记录,走了索引。但是join的效率很差,根据排查,memo这个表是这个查询的关键,需要根据时间来得到前一天的数据变化。 如果根据时间来过滤,可以过滤到绝大多数的数据。 上一条记录过滤后只剩下 74811 rows selected. 如果关联配置表memo_type查询的数据就会一下子减少到1713条左右,这是对于性能极大的提升和关键。 --考虑加入并行 如果按照时间来查询,这个大表上没有和时间相关的字段,查询走全表扫描会很长,大概在5分钟左右。 --without parallel 74811 rows selected. Elapsed: 00:03:23.10 这个时候如果只能走全表扫描,但是想使得速度能够提升,可以考虑并行,加入并行后,查询速度控制在了一分钟以内。 --add table mo1_memo_type, with parllel 8 1713 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语句的性能,可以发现在最后的查取中,对三个表又走了全表扫描。 SQL Plan Monitoring Details (Plan Hash Value=1239783398)

如果对于这部分有所疑惑,可以参见最后select中的这段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来尽可能直接过滤掉最多的数据。 --观察执行计划中表的查取顺序。 做了如上的努力之后,发现还是一些全表扫描,效率貌似更差了。根据我的分析,这些表都应该走索引的。

可以使用Hint leading来校正表的访问顺序。
/*+ 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分钟和几个小时来说,绝对是性能的提升。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-07-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

修改字符集的注意那些事儿

最近有开发同学遇到emoji显示问题,表结构是utf8mb4字符集,但是不支持emoji表情字符。我们在解决字符集问题的时候也重新认识了修改字符集操作的...

602
来自专栏数据和云

解锁不可见索引新特性,处理ORA-01555故障

何国亮 云和恩墨交付部技术顾问,获得 Oracle 11g OCM 认证。有超过 6 年超大型数据库专业服务经验,曾为通信运营商、银行、保险、政府、制造业...

1035
来自专栏数据和云

杀手SQL- 一条关于 &#39;Not in&#39; SQL 的优化案例

编辑手记:在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的。SQL 的世界无奇不有,今天我们一起见识一条让你绝对想吐血的杀手...

3296
来自专栏程序猿DD

一个不可思议的MySQL慢查分析与解决

前言 开发需要定期的删除表里一定时间以前的数据,SQL如下 mysql > delete from testtable WHERE biz_date <= '2...

3275
来自专栏杨建荣的学习笔记

一条"简单"的sql语句和小兔子买面包的故事 (r3笔记第25天)

有时候开发人员写sql语句的时候,接触的性能问题越多,可能对sql语句的结构,性能考虑会多一些,这也是一件好事,不过如果考虑不当,本来原本想做的的一些优化却使得...

2515
来自专栏java学习

Oracle基础试题与答案!

表结构: create table tbEmp --职员表 ( eID number(7) primarykey, ...

28612
来自专栏CDA数据分析师

【干货】找不到适合自己的编程书?我自己动手写了一个热门编程书搜索网站(附PDF书单)

原作者 Vlad Wetzel 编译 CDA 编译团队 本文为 CDA 数据分析师原创作品,转载需授权 选择适合自己的编程书绝非易事,美国的程序员小哥根据国外著...

2386
来自专栏大数据

爬虫无烦恼,大牛教你怎么简单爬36kr网,带源码

抓取36kr网站数据 爬虫代码和数据sql脚本在下方链接,抓取过程主要是抓包找到url递归解析的规律,三个主要的函数 以及 表结构脚本如下: #建表语句(写爬虫...

2089
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之事务(r5笔记第4天)

MySQL中的存储引擎很是丰富,常用的有InnoDB,MyISAM等,也查看了不少的资料,基本也有所了解,从一些参考书中看MySQL中的sql部分也是一扫而过,...

4088
来自专栏数据和云

极致之处,精彩无限 - 优化了一半的SQL

编辑手记:RWP(Real World Performance)团队是全球最优秀的性能优化团队,他们的目标在于系统性能千倍的提升。感谢刘永甫专家的授权,他从RW...

3455

扫描关注云+社区