通过添加外键约束来优化SQL性能一例

我们知道，一般情况下，外键约束不会对执行计划产生什么影响，以前看过这样一篇文章，其实是一种特殊情况下，外键与非空约束对执行计划的共同作用。
Oracle约束Constraint对于CBO优化器的作用：http://blog.itpub.net/17203031/viewspace-1063998/
这种SQL一般不会在实际的应用中出现，而近日，我们却遇到了一个生产环境下通过添加外键来解决性能问题的案例。

具体的SQL比较复杂，我们抽取其中对性能产生影响的一段子查询,SQL如下：
Select a.Id, a.病人病区id
From 输液配药记录 A
Where 部门id = :V034 And 执行时间 Between To_Date(:V035, 'yyyy-mm-dd') And To_Date(:V036, 'yyyy-mm-dd') And
      Nvl(操作状态, 0) Not In (10, 11) And Not Exists
 (Select 1 From 输液配药内容 D, 药品收发记录 E Where d.收发id = e.Id And d.记录id = a.Id ))

在用户处通过SQL Trace跟踪出来的执行计划，如下：
     24    HASH GROUP BY (cr=2828241 pr=0 pw=0 time=23 us cost=4010 size=1260 card=36)
     24     VIEW  (cr=2828241 pr=0 pw=0 time=92 us cost=4009 size=10500 card=300)
     24      HASH GROUP BY (cr=2828241 pr=0 pw=0 time=46 us cost=4009 size=11400 card=300)
    200       HASH JOIN  (cr=2828241 pr=0 pw=0 time=22885 us cost=4008 size=11400 card=300)
    171        TABLE ACCESS FULL 部门表 (cr=7 pr=0 pw=0 time=170 us cost=2 size=3570 card=170)
    200        VIEW  (cr=2828234 pr=0 pw=0 time=22188 us cost=4005 size=5100 card=300)
    200         UNION-ALL  (cr=2828234 pr=0 pw=0 time=21790 us)
    175          FILTER  (cr=1092 pr=0 pw=0 time=18357 us)
    175           TABLE ACCESS BY INDEX ROWID 输液配药记录 (cr=1092 pr=0 pw=0 time=18096 us cost=496 size=5675 card=227)
   3985            INDEX RANGE SCAN 输液配药记录_IX_执行时间 (cr=22 pr=0 pw=0 time=4108 us cost=15 size=0 card=13611)(object id 118389)
     25          FILTER  (cr=2827142 pr=0 pw=0 time=120 us)
     25           HASH JOIN ANTI (cr=2827142 pr=0 pw=0 time=72 us cost=3509 size=2774 card=73)
   3810            TABLE ACCESS BY INDEX ROWID 输液配药记录 (cr=1092 pr=0 pw=0 time=9649 us cost=496 size=183375 card=7335)
   3985             INDEX RANGE SCAN 输液配药记录_IX_执行时间 (cr=22 pr=0 pw=0 time=4606 us cost=15 size=0 card=13611)(object id 118389)
6080912            VIEW  VW_SQ_7 (cr=2826050 pr=0 pw=0 time=56189932 us cost=2927 size=75377627 card=5798279)
6080912             NESTED LOOPS  (cr=2826050 pr=0 pw=0 time=46522656 us cost=2927 size=104369022 card=5798279)
6137702              TABLE ACCESS FULL 输液配药内容 (cr=18277 pr=0 pw=0 time=5494503 us cost=2749 size=69579348 card=5798279)
6080912              INDEX UNIQUE SCAN 药品收发记录_PK (cr=2807773 pr=0 pw=0 time=0 us cost=1 size=6 card=1)(object id 118625)

可以看到，由于执行计划采用了HASH JOIN ANTI连接，对其中一张大表"输液配药内容"采取了全表扫描，性能问题比较严重。
在该表上添加一个外键:
输液配药内容_FK_收发id Foreign Key (收发id) References 药品收发记录(ID)

从而避免了HASH JOIN ANTI连接，执行计划中的全表扫描也消失了，变成了访问该外键字段上的高效索引"输液配药记录_IX_收发ID"。

在测试环境，不加外键，执行计划中并没有出现全表扫描，而是走的索引快速全扫描，如下：
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     3 |   120 |   128   (6)| 00:00:02 |
|*  1 |  FILTER                       |                |       |       |            |          |
|*  2 |   HASH JOIN ANTI              |                |     3 |   120 |   128   (6)| 00:00:02 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| 输液配药记录   |   257 |  6939 |    26   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | 输液配药记录_IX|   477 |       |     1   (0)| 00:00:01 |
|   5 |    VIEW                       | VW_SQ_1        |   192K|  2444K|   100   (5)| 00:00:02 |
|   6 |     NESTED LOOPS              |                |   192K|  3384K|   100   (5)| 00:00:02 |
|   7 |      INDEX FAST FULL SCAN     | 输液配药内容_PK|   192K|  2250K|    96   (3)| 00:00:02 |
|*  8 |      INDEX UNIQUE SCAN        | 药品收发记录_PK|     1 |     6 |     1   (0)| 00:00:01 |


加了外键之后，变成了：
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    27 |     9   (0)| 00:00:01 |
|*  1 |  FILTER                       |                |       |       |            |          |
|   2 |   NESTED LOOPS ANTI           |                |     1 |    27 |     9   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| 输液配药记录   |     3 |    69 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | 输液配药记录_IX|     6 |       |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | 输液配药内容_PK|  1678 |  6712 |     1   (0)| 00:00:01 |


与用户生产环境的差异就是数据量的区别，测试环境的表"输液配药内容"只有1678行，用户生产环境有数百万行。
另外又找了一个数据量较小的用户数据环境测试（该表有20万行记录），加不加外键，都一样，都走的索引快速全扫描。

分析认为，是由于Not Exists的子查询中的条件不足，导致优化器对成本的评估出了差错，误用了HASH JOIN ANTI连接。
验证表明，在Not Exists的子查询中加上条件 And e.单据 In ('9', '10'),可以避免反连接，从而用到预期的索引。

最后，根据业务分析，这个外键应该加上，之前没有加，是由于主表数据删除后，子表保留了数据，现在从业务层面分析，子表不必再保留数据。
既然有的用户数据环境，加不加外键，都会导致执行计划问题，那么，还得修改SQL。
分析Not Exists中子查询，发现加了外键后，其实后面一张表就没有必要再连接了，所以，最终改成这样：
Select a.Id, a.病人病区id
From 输液配药记录 A
Where 部门id = :V034 And 执行时间 Between To_Date(:V035, 'yyyy-mm-dd') And To_Date(:V036, 'yyyy-mm-dd') And
      Nvl(操作状态, 0) Not In (10, 11) And Not Exists (Select 1 From 输液配药内容 D Where d.记录id = a.Id )

另外写了一个类似的SQL：
Select a.Id
From 病人路径执行 A
Where 登记时间 Between :1 And :2 And Not Exists
 (Select 1 From 病人路径医嘱 B, 病人医嘱记录 C Where a.Id = b.路径执行id And b.病人医嘱id = c.Id)

证实了这样一点：
在Not Exists的子查询条件中，如果存在两张表连接的情况，即使有索引，执行计划会错误的选择包含索引全扫描的哈希反连接，
而不是更高效的采用索引范围扫描的嵌套反连接。

小结：
通过这个案例，我们看到，外键是否存在，在某些数据环境下(一定的数据量)，特定的SQL语句中（Not Exists），是会影响执行计划的选择的。