| 导读 | 在 DBA 所优化的数据库环境中，绝大多数性能问题其实是由于 SQL 编写不当导致的。SQL 的世界无奇不有，今天我们一起见识一条让你绝对想吐血的杀手SQL。 |
某保险客户，ETL 耗时数个小时，我们做了sql report发现压力主要在其中一个SQL上。
单次执行时间：5788(秒)
单次逻辑读：10亿(块)
单次返回行数:21万(行)
我们首先看SQL语句，因为比较长，此处只节选部分的
查看其执行计划：
我们主要关注一下从7到16行：发现存在两次全表扫描。中间做了一次filter。
多年的经验告诉我，两个全表扫组成的Filter ，问题很严重, 因为涉及数据逐条处理。 而这个执行计划里，被驱动表还是全表扫。
Not In/In 操作有时候的确会产生 Filter操作，在11g之前的版本，要把not in 语句转换成反连接，not in条件的列必须有Not null 属性,或者语句中带入了not null的限制，否则只能采用Filter，逐条过滤.
我们举例说明一下：
查看T_OBJ的属性：
发现有在三列上都没有not null的限制。
我们此时伪装成10G的优化器。
SQL> alter session set optimizer_features_enable=”10.2.0.5″;
执行以下SQL：
SQL> set autotracetrace exp
SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME NOT IN(SELECT OBJECT_NAME FROM T_OBJ);
此时查看执行计划，我们发现走的是filter：
但在11g版本中，优化器可以自动把Not in操作从昂贵的Filter转换成Null-Aware-Anti-Join。
若加个Not null 条件或者栏位属性设为not null
SQL> alter table T_OBJ modify(OBJECT_NAME NOT NULL);
再次执行相同语句：
SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME
NOT IN(SELECT OBJECT_NAME FROM T_OBJ
WHEREOBJECT_NAME IS NOT NULL);
再次查看执行计划：
此时我们发现，在执行计划中，走了hash join anti.
并且，在11g里面，允许not in列没有not null 限制也可以转换Anti-Join.
SQL> alter session set optimizer_features_enable=”11.2.0.4″;
SQL> alter table T_OBJ modify(OBJECT_NAME NULL);
SQ> SELECT * FROM T_TABLE WHERE TABLE_NAME
NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);
查看执行计划：
我们看到，此时在没有非空限制的情况下，也走了hash join anti.
这个特性, 可通过优化器参数控制。
SQL>alter session set “_optimizer_null_aware_antijoin”=FALSE;
再次执行以上语句并查看执行计划：
SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME
NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);
发现仍然走的是hash join anti.
经过验证，不是这个参数设置问题
Not in 的逻辑，就是结果集之间的互斥，其实有多种改写的方式，比如：
— Not exists
— Outer Join + is null
— Minus
not in与以上三种写法的区别是：not in 是会排斥空值。
我们尝试改写。
接下来正当你以为会发生奇迹的时候，语句报错了！
为什么会报错呢？
如果我们把该语句转换为not in的方式：
根据not in的逻辑，此时在fee_code前应该加上’A.’，当然这也是没有问题的，但是，再次看这条语句就会变成：
由于TMP_APP_xxx_PREM A 中并没有FEE_CODE字段, 所以，Not in 无法自动改成Null Aware ANTI JOIN。
所以，至此答案揭晓，竟然是写错了？！我猜中了这开头，却没有猜中这结局。
但在本案例中，由于SQL语句中没有显式写出表明，导致在前期分析过程中一直没有发现这个错误。
你是不是也很无语，其实我更想问的是，你是不是也经常写出杀手SQL呢，但没关系，你有病我有药啊。（无辜脸，不要打我）
我们都知道，在 DBA 所优化的数据库环境中，绝大多数性能问题其实是由于 SQL 编写不当导致的。
对于未上线系统，通过前期的SQL审核管控，将80%的SQL问题消灭在萌生阶段，对于线上运行系统，发现和解决潜在的性能问题，可做到提前预防，防患于未然。
SQL审核，让DBA由系统的急救医生转身成为系统的保健医生
1、DBA参与应用代码开发测试过程：给开发人员提供专业的数据库开发及优化建议
2、优化前置：在应用代码上线前根据业务需求设计高效的SQL、索引
3、控制变更风险：预先评估应用开发中表结构变更、SQL变更对运行中应用的影响，确定合适的变更窗口，变更方案。
以上是优化 SQL 查询，减少'Not in' 运行时间的详细内容。更多信息请关注PHP中文网其他相关文章！
