一条SQL引发的“血案”：与SQL优化相关的4个案例-CDA数据分析师官网

热线电话：13121318867

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

2020-08-14

作者：马立和高振娇韩锋

来源：大数据DT（ID：hzdashuju）

内容摘编自《数据库高效优化：架构、规范与SQL技巧》

案例01 一条SQL引发的“血案”
1. 案例说明
某大型电商公司数据仓库系统，正常情况下每天0～9点会执行大量作业，生成前一天的业务报表，供管理层分析使用。但某天早晨6点开始，监控人员就频繁收到业务报警，大批业务报表突然出现大面积延迟。原本8点前就应跑出的报表，一直持续到10点仍然没有结果。公司领导非常重视，严令在11点前必须解决问题。
DBA紧急介入处理，通过TOP命令查看到某个进程占用了大量资源，杀掉后不久还会再次出现。经与开发人员沟通，这是由于调度机制所致，非正常结束的作业会反复执行。
暂时设置该作业无效，并从脚本中排查可疑SQL。同时对比从线上收集的ASH/AWR报告，最终定位到某条SQL比较可疑。
经与开发人员确认系一新增功能，因上线紧急，只做了简单的功能测试。正是因为这一条SQL，导致整个系统运行缓慢，大量作业受到影响，修改SQL后系统恢复正常。

具体分析

这是一个很典型的两表关联语句，两张表的数据量都较大。下面来看看执行计划，如图1-1所示。
执行计划触目惊心，优化器评估返回的数据量为3505T条记录，计划返回量127P字节，总成本9890G，返回时间999:59:59。

分析结论

从执行计划中可见，两表关联使用了笛卡儿积的关联方式。我们知道笛卡儿连接是指两表没有任何条件限制的连接查询。一般情况下应尽量避免笛卡儿积，除非某些特殊场合，否则再强大的数据库也无法处理。
这是一个典型的多表关联缺乏连接条件，导致笛卡儿积，引发性能问题的案例。
2. 给我们的启示
从案例本身来讲并没有什么特别之处，不过是开发人员疏忽导致了一条质量很差的SQL。但从更深层次来讲，这个案例可以给我们带来如下启示。

开发人员的一个疏忽造成了严重的后果，原来数据库竟是如此的脆弱。需要对数据库保持“敬畏”之心。
电脑不是人脑，它不知道你的需求是什么，只能根据写好的逻辑进行处理。
不要去责怪开发人员，谁都会犯错误，关键是如何从制度上保证不再发生类似的问题。

3. 解决之道
1）SQL开发规范
加强对数据库开发人员的培训工作，提高其对数据库的理解能力和SQL开发水平。将部分SQL运行检查的职责前置，在开发阶段就能规避很多问题。要向开发人员灌输SQL优化的思想，在工作中逐步积累，这样才能提高公司整体开发质量，也可以避免很多低级错误。
2）SQL Review制度
对于SQL Review，怎么强调都不过分。从业内来看，很多公司也都在自己的开发流程中纳入了这个环节，甚至列入考评范围，对其重视程度可见一斑。其常见典型做法是利用SQL分析引擎（商用或自研）进行分析或采取半人工的方式进行审核。审核后的结果可作为持续改进的依据。
SQL Review的中间结果可以保留，作为系统上线后的对比分析依据，进而可将SQL的审核、优化、管理等功能集成起来，完成对SQL整个生命周期的管理。
3）限流/资源控制
有些数据库提供了丰富的资源限制功能，可以从多个维度限制会话对资源（CPU、MEMORY、IO）的使用，可避免发生单个会话影响整个数据库的运行状态。
对于一些开源数据库，部分技术实力较强的公司还通过对内核的修改实现了限流功能，控制资源消耗较多的SQL运行数量，从而避免拖慢数据库的整体运行。

案例02 糟糕的结构设计带来的问题
1. 案例说明
这是某公司后台的ERP系统，系统已经上线运行了10多年。随着时间的推移，累积的数据量越来越大。随着公司业务量的不断增加，数据库系统运行缓慢的问题日益凸显。
为提高运行效率，公司计划有针对性地对部分大表进行数据清理。在DBA对某个大表进行清理时出现了问题。这个表本身有数百吉字节，按照指定的清理规则只需要根据主键字段范围（运算符为>=）选择出一定比例（不超过10%）的数据进行清理即可。
但在实际使用中发现，该SQL是全表扫描，执行时间大大超出预期。DBA尝试使用强制指定索引方式清理数据，依然无效，整个SQL语句的执行效率达不到要求。为了避免影响正常业务运行，不得不将此次清理工作放在半夜进行，还需要协调库房等诸多单位进行配合，严重影响正常业务运行。
为了尽量减少对业务的影响，DBA求助笔者帮助协同分析。这套ERP系统是由第三方公司开发的，历史很久远，相关的数据字典等信息都已经找不到了，只能从纯数据库的角度进行分析。这是一个普通表（非分区表），按照主键字段的范围查询一批记录并进行清理。
按照正常理解，执行索引范围扫描应该是效率较高的一种处理方式，但实际情况都是全表扫描。进一步分析发现，该表的主键是没有业务含义的，仅仅是自增长的数据，其来源是一个序列。
但奇怪的是，这个主键字段的类型是变长文本类型，而不是通常的数字类型。当初定义该字段类型的依据，现在已经无从考证，但实验表明正是这个字段的类型“异常”，导致了错误的执行路径。
下面通过一个实验重现这个问题。
1）数据准备
两个表的数据类型相似（只是ID字段类型不同），各插入了320万数据，ID字段范围为1~3200000。

2）模拟场景
相关代码如下：

对于普通的采用数值类型的字段，范围查询就是正常的索引范围扫描，执行效率很高。

对于文本类型字段的表，范围查询就是对应的全表扫描，效率较低是显而易见的。
3）分析结论

字符类型在索引中是“乱序”的，这是因为字符类型的排序方式与我们的预期不同。从“select * from t2 where id>= '3199990'”执行返回755 565条记录可见，不是直观上的10条记录。这也是当初在做表设计时，开发人员没有注意的问题。
字符类型还导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说，就是按照数字类型插入（1..3200000），按字符类型（'1'...'32000000'）t排序。

select table_name,index_name,leaf_blocks,num_rows,clustering_factor from user_indexes where table_name in ('T1','T2');
TABLE_NAME         INDEX_NAME      LEAF_BLOCKS   NUM_ROWS    CLUSTERING_FACTOR -------------- -------------- ---------------- ---------- --------------------- T1               SYS_C0025294             6275    3200000                 31520
T2               SYS_C0025295            13271    3200000                632615

在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。

4）解决方法
具体的解决方法如下：

select * from t2 where id between '3199990' and '3200000'; -------------------------------------------------------------------------------- | Id  | Operation                 | Name         |Rows|Bytes |Cost(%CPU)| Time   | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT          |             |   6|  390 |   5 (0)|00:00:01|
|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |   6|  390 |   5 (0)|00:00:01|
|*  2 |   INDEX RANGE SCAN        | SYS_C0025295 |   6|      |   3 (0)|00:00:01| -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1  recursive calls 0  db block gets 13  consistent gets 0  physical reads

将SQL语句由开放区间扫描（>=），修改为封闭区间（between xxx and max_value）。使得数据在索引局部顺序是“对的”。如果采用这种方式仍然走全表扫描，还可以进一步细化分段或者采用“逐条提取+批绑定”的方法。
2. 给我们的启示
这是一个典型的由不好的数据类型带来的执行计划异常的例子。它给我们带来如下启示：

糟糕的数据结构设计往往是致命的，后期的优化只是补救措施。只有从源头上加以杜绝，才是优化的根本。
在设计初期能引入数据库审核，可以起到很好的作用。

案例03 规范SQL写法好处多

1. 案例说明
某大型电商公司数据仓库系统，开发人员反映作业运行缓慢。经检查是一个新增业务中某条SQL语句导致。经分析是非标准的SQL引起优化器判断异常，将其修改成标准写法后，SQL恢复正常。
1）具体分析
看下面的代码：

select ... from ... where (
    ( 
　　　  order_creation_date>= to_date(20120208,'yyyy-mm-dd') and 
    order_creation_date<to_date(20120209,'yyyy-mm-dd')
    )  or     ( 
　　　  send_date>= to_date(20120208,'yyyy-mm-dd') and send_date<to_date(20120209,　'yyyy-mm-dd')
    )
)
andnvl(a.bd_id,0) = 1 -------------------------------------------------------------------------------- |  Id | Operation              | Name   |Cost (%CPU)| Time   |Pstart | Pstop | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |        | 2470K(100)|        |       |       |
|   1 |  SORT GROUP BY         |        |           |        |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID                                   |  XXXX  |     5 (0) | 00:00:01 | ROW L | ROW L |
|   3 |    NESTED LOOPS         |        | 2470K (1) | 08:14:11 |       |       |
|   4 |     VIEW               |VW_NSO_1| 2470K (1) | 08:14:10 |       |       |
|   5 |      FILTER            |        |           |          |       |       |
|   6 |       HASH GROUP BY    |        |  2470K (1)| 08:14:10 |       |       |
|   7 |        TABLE ACCESS BY GLOBAL INDEX ROWID 
                               |  XXXX  |      5 (0)| 00:00:01 | ROW L | ROW L |
|   8 |         NESTED LOOPS    |        |  2470K (1)| 08:14:10 |       |       |
|   9 |          SORT UNIQUE    |        |  2340K (2)| 07:48:11 |       |       |
|  10 |           PARTITION RANGE ALL  
                                |        |  2340K (2)| 07:48:11 |    1  |    92 |
|  11 |            TABLE ACCESS FULL                                 |  XXXX  |  2340K (2)| 07:48:11 |    1  |    92 |
|  12 |          INDEX RANGE SCAN 
                                |  XXXX  |      3 (0)| 00:00:01 |       |       |
|  13 |     INDEX RANGE SCAN    |  XXXX  |      3 (0)| 00:00:01 |       |       | --------------------------------------------------------------------------------

这个SQL中涉及的主要表是一个分区表，从执行计划（Pstart、Pstop）中可见，扫描了所有分区，分区裁剪特性没有起效。
2）解决方法
见下面的代码：

select ... from ... where 
    order_creation_date >= to_date(20120208,'yyyy-mm-dd') and 
    order_creation_date<to_date(20120209,'yyyy-mm-dd') union all select ... from ... where send_date>= to_date(20120208,'yyyy-mm-dd') and 
    send_date<to_date(20120209,'yyyy-mm-dd') and 
nvl(a.bd_id,0) = 5

尝试通过引入union all来分解查询，以便于优化器做出更准确的判断。采用这个方法后，确实起效了，当然不可避免会扫描两遍表。

select ... from ... where (
    ( 
        order_creation_date>= to_date(20120208,'yyyymmdd') and 
        order_creation_date<to_date(20120209,'yyyymmdd')
    )  or     ( 
        send_date>= to_date(20120208,'yyyymmdd') and 
        send_date<to_date(20120209,'yyyymmdd')
    )
); -------------------------------------------------------------------------------- |  Id   | Operation           | Name | Cost(%CPU)|Time      | Pstart  | Pstop   | -------------------------------------------------------------------------------- |     0 | SELECT STATEMENT    |      |  42358 (1)| 00:08:29 |         |         |
|     1 |  SORT AGGREGATE     |      |           |          |         |         |
|     2 |   CONCATENATION     |      |           |          |         |         |
|     3 |    PARTITION RANGE SINGLE
                              |      |  17393 (1)| 00:03:29 |      57 |     57 |
|*    4 |     TABLE ACCESS FULL                               | XXXX |  17393 (1)| 00:03:29 |      57 |     57 |
|*    5 |    TABLE ACCESS BY GLOBAL INDEX ROWID 
                              | XXXX |  24966 (1)| 00:05:00 |   ROWID |  ROWID |
|*    6 |     INDEX RANGE SCAN  
                              | XXXX |    658 (1)| 00:00:08 |         |         | ---------------------------------------------------------------------------------

通过调整日期FORMAT格式，优化器很精准地判断了分区（Pstart=57、Pstop=57），整体SQL性能得到了很大的提高，作业运行时间从8个多小时缩减到8分钟。
3）分析结论
对于非标准的日期格式，Oracle在复杂逻辑判断的情况下分区裁剪特性无法识别，不起作用。这种情况下，会走全表扫描，结果是正确的，但是执行效率会很低。通过使用union all，简化了条件判断。使得Oracle在非保准日期格式下也能使用分区裁剪特性，但最佳修改方式还是规范SQL的写法。
2. 给我们的启示

规范的SQL写法，不但利于提高代码可读性，还有利于优化器生成更优的执行计划。
分区功能是Oracle应对大数据的利器，但在使用中要注意是否真正会用到分区特性；否则，可能适得其反，使用分区会导致效率更差。

案例04 “月底难过”
1. 案例说明
某大型电商公司数据仓库系统经常出现在月底运行缓慢的情况，但在平时系统运行却非常正常。这是因为月底往往有月报等大批量作业运行，而就在这个时间点上，常常会出现缓慢情况，所以业务人员一到月底就非常紧张。这也成了一个老大难问题，困扰了很长时间。
DBA介入处理，发现一个很奇怪的现象：某条主要SQL是造成执行缓慢的主因，其执行计划是不确定的，也就是说因为执行计划的改变，导致其运行效率不同。而往往较差的执行计划发生在月底几天，且由于月底大批作业的影响，整体性能比较饱和，更突显了这个问题。
针对某个出现问题的时间段做了进一步分析，结果表明是由于统计信息的缺失导致了优化器产生了较差的执行计划，并据此指定了人工策略，彻底解决了这个问题。
1）具体分析
先来看下面的代码：

select... from xxx a join xxx b on a.order_id = b.lyywzdid left join xxx c on b.gysid = c.gysid
whereb.cdate>= to_date('2012-03-31', 'yyyy-mm-dd') – 3 and ...
a.send_date>= to_date('2012-03-31', 'yyyy-mm-dd') - 1 and 
　　　　a.send_date<to_date('2012-03-31', 'yyyy-mm-dd'); -------------------------------------------------------------------------------- |Id  | Operation          |Name  |  Rows  |  Bytes  | Cost (%CPU) |Pstart|Pstop| -------------------------------------------------------------------------------- |  0 | SELECT STATEMENT   |      |      1 |     104 |      9743(1)|      |     |
|  1 |  HASH JOIN OUTER   |      |      1 |     104 |      9743(1)|      |     |
|  2 |   TABLE ACCESS BY LOCAL INDEX ROWID                           | XXXX |      1 |      22 |         0(0)| 1189 | 1189|
|  3 |    NESTED LOOPS    |      |      1 |      94 |      9739(1)|      |     |
|  4 |     PARTITION RANGE ITERATOR    
                          |      |   1032 |   74304 |      9739(1)|  123 | 518 |
|  5 |      TABLE ACCESS FULL 
                          | XXXX |   1032 |   74304 |      9739(1)|  123 | 518 |
|  6 |     PARTITION RANGE SINGLE
                          |      |      1 |         |         0(0)| 1189 | 1189 |
|  7 |      INDEX RANGE SCAN 
                          | XXXX |      1 |         |         0(0)| 1189 | 1189 |
|  8 |   TABLE ACCESS FULL                           | XXXX |    183 |    1830 |         3(0)|      |     | --------------------------------------------------------------------------------

执行计划中，多表关联使用了嵌套循环，这点对于OLAP系统来说是比较少见的。一般优化器更倾向于使用SM和HJ。进一步检查发现其成本竟然是0，怪不得优化器使用了嵌套循环。
2）深入分析
检查发现索引数据统计信息异常，这是分区索引，仅两天的分区统计信息都是0。导致优化器认为嵌套循环的执行效率更高，而不是使用哈希连接。结合业务发现，月底是业务高峰期，对于系统统计信息的作业收集，在指定的时间窗口内无法完成。最后导致统计信息不完整，优化器采用了错误的执行计划。
3）解决方法
解决的代码如下：

exec dbms_stats.gather_index_stats(
  ownname=>'xxx', 
  indname=>'xxx',
  partname=>'PART_xxx', 
  estimate_percent => 10);

分析完对象的统计信息即恢复正常。
2. 给我们的启示

统计信息是优化器优化的重要参考依据，一个完整、准确的统计信息是必要条件。往往在优化过程中，第一步就是查看相关对象的统计信息。
分区机制是Oracle针对大数据的重要解决手段，但也很容易造成所谓“放大效应”。即对于普通表而言，统计信息更新不及时可能不会导致执行计划偏差过大；但对于分区表、索引来说，很容易出现因更新不及时出现0的情况，进而导致执行计划产生严重偏差。

CDA数据分析师考试相关入口一览（建议收藏）：

▷ 想报名CDA认证考试，点击>>> “CDA报名”