【Oracle】多表联合查询超时问题排查

文中使用的Oracle版本为10g。

本文内容将涉及大规模SQL联合查询优化内容，本人尽可能讲得容易理解一些，若有看不懂的地方是本人表述不清楚，望各位海涵。此外文章是2016年写的，那时候本人能力有限所以文章中的排查方式或者解决方法都有一定的局限性（现在会更优的做法），因此各位将就着看，仅代表个人看法，谢谢。

排查

本次性能排查的SQL是典型的“业务优先”（根据业务逻辑直接转换为代码，不存在设计模式或者性能优化的思考）脚本，相信大家在开发的过程中多多少少都会遇到过。其实就是每个业务来自一个表，表是同构的，但是查询的时候需要将所有的表都UNION ALL进行数据集整合输出，类似于这种情况吧。

为此，每一种分类统计SQL 基本格式都是：

SELECT SUM(TOTAL) TOTAL, 'YX' TYPE
FROM (SELECT *
      FROM GATHER_STATISTIC_UNIT_YX
      WHERE WRITEDATE >= TO_DATE('1992-11-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
        AND WRITEDATE <= TO_DATE('2016-11-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
 INNER JOIN (SELECT ID
             FROM UMS_ORG
             START WITH ID = 'xxx'
               CONNECT BY PRIOR ID = PID) U ON UNIT = U.ID

之后通过UNION ALL 将所有类型合并起来，这种组合方式是比较清晰和直观的。并且在使用聚合函数SUM、INNER JOIN之前都先通过条件语句缩小扫描范围，在得到数据集后再进行UNION ALL。基本上是合符性能优化的基本要求（尽管还可以将*替换成具体的字段，从而不需要全字段扫描）。

但据了解INNER JOIN内集合查询的条件是会变化的，也就是说INNER JOIN中的语句每个分类都需要根据实际业务写一遍。这时我就会想，如果变化的内容是一样的话就使用WITH … AS的写法也不是不可以的。

但我还是太过天真了。在通读了设计文档后发现，需要UNION ALL的表有39个。这一听联合了39个表来查询肯定是这里出了问题啦。但是经过执行计划执行后发现真正的性能瓶颈应该是在每个分类的查询统计而不是UNION ALL上。下面先看看测试机的执行计划分析如下图：

执行计划内容太多了就不在这里贴出来了，但是可以看到虽然使用的资源有点多，但每个子SQL就性能来说不算太过离谱。这种执行效率不至于生产服务出现查询超时的情况。想到这同样的SQL在生产上执行“路线”（之前我们说过的执行选择器）会不太一样，于是让实施人员在现场连一下生产机，执行一下执行计划来看看。结果如下：

虽然执行计划有点多但还是可以看到一些问题的端倪。

为了方面理解，一段SORT AGGREGATE可以认为是一个分类段落。接着可以看到在测试机上都是用NESTED LOOPS的INNER JOIN操作，在生产机上大部分都使用了 HASH JOIN作为操作依据。这个先按下不表再往下看，看到View里面的执行ID使用的是DICT_DICT_KEY的索引，走的是INDE X RANGE SCAN。通过SQL可以知道，在INNER JOIN 之前UMS_ORG是做一个递归操作，找出了所有组织节点编码，如下：

SELECT ID
FROM UMS_ORG
START WITH ID = 'xxx'
CONNECT BY PRIOR ID = PID

ID字段是表中的唯一字段，而这里面居然做了RANGE SCAN，那么只能说这个View里面的ID字段被设置成了普通的B-TREE索引而已。测试机上设置的是主键，所以测试机上走的是INDEX UNIQUE SCAN。

除此之外，在递归之后还是需要用ID字段作为条件跟业务表中的UNIT字段进行INNER JOIN操作的，由于COST过大，所以在生产上选择使用了STATISTIC_YX_WRITEDATE索引做范围扫描。INNER JOIN 操作数据量大，系统选择了使用HASH JOIN来做连接操作了。

但是问题又来了，即使走了HASH JOIN，在若满足条件的情况下UNIT字段还是应该走STATISTIC_YX_UNIT索引的。

究竟是什么原因会导致不走索引？（开始头脑回溯那些不走索引的原因）其中最有可能的就是字段为空的情况了。于是使用了

SELECT COUNT(1) FROM GATHER_STATISTIC_UNIT_YX WHERE UNIT IS NULL;

得到了不为零的结果，这样就直接说明了不走索引的原因。既然知道问题所在了，要修复就非常简单了这里就不再详细叙述。

结论

1. 多表合并查询性能瓶颈不在于UNION ALL多少个表，而是串行子查询的效率问题；
2. 部分走索引的情况应该首先检查表的异同，想到那些不走索引的情况，结合当前的情况进行验证；
3. 做连接的字段无论是外连接还是内连接都必须建立索引，并且索引能够做成唯一的就必须做成唯一索引，查询效率不是一个等级；
4. MERGE JOIN、NESTED LOOPS都会比HASH JOIN效率来得高，所以一般情况下尽量保证使用前者；
5. 多次出现的SQL脚本可以做成WITH XXX AS (SELECT … FROM ...)这种方式。这次在排查的过程中看到每一个类型都需要INNER JOIN一个查询组织编码数据集在得到ID后来限制业务表中的数据输出，INNER JOIN里面的脚本可以做成：

WITH UORG AS (
  SELECT ID 
      FROM UMS_ORG 
      START WITH ID = 'xxx' 
  CONNECT BY PRIOR ID = PID 
)

放在整个查询语句的开头部分，INNER JOIN 时可以直接使用 INNER JOIN UORG U ON UNIT=U.ID 进行连接。WITH XXX AS 这种做法是将表放到内存里面从而减少磁盘IO的频繁读取或扫描，比较适合枚举表、字典表、组织架构表等数据量比较少而且比较固定的表来使用。