一条SQL引发的“血案”：与SQL优化相关的4个案例.docx

《一条SQL引发的“血案”：与SQL优化相关的4个案例.docx》由会员分享，可在线阅读，更多相关《一条SQL引发的“血案”：与SQL优化相关的4个案例.docx（20页珍藏版）》请在第一文库网上搜索。

1、一条SQL引发的“血案：与SQL优化相关的4个案导读：本文通过几个案例探讨一下SQL优化的相关问题。案例1 一条SQL引发的血案1 .案例说明某大型电商公司数据仓库系统，正常情况下每天0 9点会执行大量作业，生成前一天的业务报表，供管理层分析使用。但某天早晨6点开始，监控人员就频繁收到业务报警,大批业务报表突然出现大面积延迟。原本8点前就应跑出的报表，一直持续到10点仍然没有结果。公司领导非常重视，严令在11点前必须解决问题。DBA紧急介入处理，通过TOP命令查看到某个进程占用了大量资源,杀掉后不久还会再次出现。经与开发人员沟通，这是由于调度机制所致，非正常结束的作业会反复执行。暂时设置该作业

2、无效，并从脚本中排查可疑SQLO同时对比从线上收集的ASH/AWR报告，最终定位到某条SQL比较可疑。经与开发人员确认系一新增功能，因上线紧急，只做了简单的功能测试。正是因为这一条SQL ,导致整个系统运行缓慢，大量作业受到影响，修改SQL后系统恢复正常。具体分析SELECT /*+ INDEX (Al xxxxx) */ SUM(A2.CRKSL), SUM(A2.CRKSL*A2D3) .一这是一个很典型的两表关联语句，两张表的数据量都较大。下面来看看执行计划，如图1所示。执行计划触目惊心，优化器评估返回的腕量为3505T条记录，计划返回量127P字节，总成本9890G ,返回时间999:

3、59:59。OperationNameRowsBytesCost (%CPU)TimePstart5 saECT STATEMENT9890G(100)，rpSORT AGGREGATErRi_IMERGE JCHN CARTESIAN3505T1127P9890G (1)199as9:59rPARTITION RANGE ITERATOR(25M|wiom(170KO):00:34:12l53|243P - TABLE ACCESS FULL125ML 一 |1010M|V0K(1)：00:34:121153243rBUFFERSORT|l35M|9890G(1)999:59:59| ICE

4、X FULL SCAN1135M|382K(1)01:16:34图1执行计划分析结论从执行计划中可见，两表关联使用了笛卡儿积的关联方式。我们知道笛卡儿连接是指两表没有任何条件限制的连接查询。一般情况下应尽量避免笛卡儿积，除三原些特殊场合,否则再强大的数据库也无法处理。这是一个典型的多表关联缺乏连接条件，导致笛卡儿积，引发性能问题的案例。2 .给我们的启示从案例本身来讲并没有什么特别之处，不过是开发人员疏忽导致了一条质量很差的SQLO但从更深层次来讲，这个案例可以给我们带来如下启示。开发人员的一个疏忽造成了严重的后果，原来数据库竟是如此的脆弱。需要对数据库保持敬畏”之心。电脑不是人脑，它不知道你

5、的需求是什么，只能根据写好的逻辑进行处理。不要去责怪开发人员，谁都会犯错误，关键是如何从制度上保证不再发生类似的问题。3 .解决之道1 ) SQL开发规范加强对数据库开发人员的培训工作,提高其对降库的理解能力和SQL开发水平。将部分SQL运行检查的职责前置，在开发阶段就能规避很多问题。要向开发人员灌输SQL优化的思想，在工作中逐步积累，这样才能提高公司整体开发质量，也可以避免很多低级错误。2 ) SQL Review 制度对于SQL Review ,怎么强调都不过分。从业内来看,很多公司也都在自己的开发流程中纳入了这个环节，甚至列入考评范围，对其重视程度可见一斑。其常见典型做法是利用SQL分析

6、引擎(商用或自研)进行分析或采取半人工的方式进行审核。审核后的结果可作为持续改进的依据。SQL Review的中间结果可以保留，作为系统上线后的对比分析依据，进而可将SQL的审核、优化、管理等功能集成起来，完成对SQL整个生命周期的管理。3 ）限流/资源控制有些数据库提供了丰富的资源限制功能，可以从多个维度限制会话对资源（CPU、MEMORY. 10）的使用,可避免发生单个会话影响整个数据库的运行状态。对于一些开源数据库，部分技术实力较强的公司还通过对内核的修改实现了限流功能,控制资源消耗较多的SQL运行数量，从而避免拖慢数据库的整体运行。案例2糟糕的结构设计带来的问题I .案例说明这是某公司

7、后台的ERP系统，系统已经上线运行了 10多年。随着时间的推移,累积的数据量越来越大。随着公司业务量的不断增加，数据库系统运行缓慢的问题日益凸显。为提高运行效率，公司计划有针对性地对部分大表进行数据清理。在DBA对某个大表进行清理时出现了问题。这个表本身有数百吉字节，按照指定的清理规则只需要根据主键字段范围（运算符为=）选择出一定比例（不超过10% ）的数据进行清理即可。但在实际使用中发现，该SQL是全表扫描，执行时间大大超出预期。DBA尝试使用强制指定索引方式清理数据，依然无效，整个SQL语句的执行效率达不到要求。为了避免影响正常业务运行，不得不将此次清理工作放在半夜进行，还需要协调库房等诸

8、多单位进行配合,严重影响正常业务运行。为了尽量减少对业务的影响,DBA求助笔者帮助协同分析。这套ERP系统是由第三方公司开发的，历史很久远，相关的数据字典等信息都已经找不到了，只能从纯数据库的角度进行分析。这是一个普通表（非分区表），按照主键字段的范围查询一批记录并进行清理。按照正常理解，执行索引范围扫描应该是效率较高的一种处理方式，但实际情况都是全表扫描。进一步分析发现，该表的主键是没有业务含义的，仅仅是自增长的数据，其来源是一个序列。但奇怪的是，这个主键字段的类型是变长文本类型，而不是通常的数字类型。当初定义该字段类型的依据，现在已经无从考证，但实验表明正是这个字段的类型异常，导致了错误的

9、执行路径。下面通过一个实验重现这个问题。1）数据准备两个表的数据类型相似（只是ID字段类型不同），各插入了 320万数据，ID字段范围为 13200000。tl as sele * Fror dba_objects lere 1=0;|II primcreate tabi t2 as se* rom dba_objects -heralter table t2 add id varchar2(10) primaryselect test?testJ test,rownum,rownum,test,sysdatesysdatetestIselect test?testJ test,rownum,

10、rownum,test,sysdatesysdatetestIconnect by|execdbms_statsgather_table_stats(ownname = fhf1tabname = tl1cascaexecdbms_stats.gather_table_stats(ownname = 1hf1tabname = ft2f casca2）模拟场景相关代码如下：* tl= 3199990；Id | Operation| Name|Rows |Bytes|Cost (%CPU)|0 SELECT STATEMENT| 11| 693 |4 (0) | 0| 1 | TABLE ACC

11、ESS BY INDEX ROWID| Tl| 11 | 693 |4 (0) |一II* 2 | INDEX RANGE SCAN|SYS_C0025294| 11|3 (0) | 0calls6 consistent getshvsRaTeadsl对于普通的采用数值类型的字段，范围查询就是正常的索引范围扫描，执行效率很高。t2131999901；Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time0 SELECT STATEMENT2417K149M8927(2) 00:01:4uaI* 1ETABLE ACCE： S FULL T

12、22417K|149M8927(2) 00:01:4callsj0 db82568 consistent gets对于文本类型字段的表，范围查询就是对应的全表扫描，效率较低是显而易见的。3)分析结论字符类型在索引中是乱序的，这是因为字符类型的排序方式与我们的预期不同。从select * from t2 where id= 3199990”执行返回 755 565 条记录可见,不是直观上的10条记录。这也是当初在做表设计时，开发人员没有注意的问题。字符类型还导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说,就是按照数字类型插入(1.3200000 ),按字符类型(,1.13200000

13、0, ) t排序。table_nameJ index_name, leaflocks num_rows clustering_f actorj|TABLE_NAME工NDEX_NAMELEAF_BLOCKS NUM_ROWS CLUSTER工NG|T1SYS_C002529462753200000IT2SYSJZ0025295132713200000在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。4）解决方法具体的解决方法如下:select * from t2 where id between 31999901 and

14、 32000001 TABLE ACCESS BY INDEX ROWID T26390 |5 (000：00:01* 2 | INDEX RANGE SCAN| SYS_C0025295 |6|3 (0)|Icalls0 db block13gets将SQL语句由开放区间扫描（ =）修改为封闭区间（between xxx and max_value ）。使得数据在索引局部N页序是对的。如果采用这种方式仍然走全表扫描，还可以进一步细化分段或者采用逐条提取十批绑定的方法。2.给我们的启示这是一个典型的由不好的数据类型带来的执行计划异常的例子。它给我们带来如下启示:糟糕的数据结构设计往往是致命的，后期的优化只是补救措施。只有从源头上加以杜绝，才是优化的根本。在设计初期能引入数据库审核，可以起到很好的作用。案例3规范SQL写法好处多1 .案例说明某大型电商公司数据仓库系统，开发人员反映作业运行缓慢。经检查是一个新增业务中某条SQL语句导致。经分析是非标准的SQL引起优化器判断异常，将其修改成标准写法后，SQL恢复正常。1）具体分析看下面的代码：select fromorder