问题

最近在写一个项目SQL的时候遇到了问题，SQL的执行时间过长：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
'A' as type,
count(adl.is_attend) as count,
round(sum(case when adl.is_attend = 1 then 1 else 0 end)/ count(adl.is_attend),2) * 100 as rate
from
attendance adl
inner join (select deptId from department start with deptId = '...'
            connect by up_daptId = prior deptId) as nbd on adl.deptId = nbd.deptId
where 
to_date(adl.date,'yyyy-MM-dd') >= to_date( #{begin_date}
    ,'yyyy-MM-dd')
and to_date(adl.date,'yyyy-MM-dd') <= to_date( #{end_date}
    ,'yyyy-MM-dd')
    
union all

......

上面的代码是一个配合Union组合使用的SQL代码，其他部分的代码和这一部分的代码很相似，仅仅是修改了select中的type和inner join 的部门id。代码的总运行时间达到了110s左右，单段SQL代码运行时间在18S左右，可以说是非常长的运行时间了。

另外， #{begin_date}，#{end_date}分别是后端将会传入的参数，做动态查询使用。

优化方案

首先分析最直接的问题——为什么运行时间这么长？

其实这里的问题并没有很复杂，attendance表的数据有一千三百多万条，庞大的数据量光是最基础的简单的查询都需要花费20S左右。

另外，需要关联的部门表是一个递归join，这里会查询出给出的deptId下面的所有小部门，这里的查询也很耗时间。

那么，怎么解决问题呢？

方案一：首先数据量的问题是无解的，我们不可能说某一段数据直接舍弃了，而且注意，我们的where 条件是限制了时间的，因此对数据量实际上是有一定限制的。因此首先优化的思路就是在Join上：

递归Join是很耗时的一个操作，因此我们可以直接想到在deptId上建立一个索引——事实上这是个正确的思路，通过建立索引运行时间直接从18S优化到了2S。

方案二：这里还可以优化where的查询条件：

在日期限制的时候使用了to_date函数，事实上仅仅做年月日对比的时候，可以直接不使用这个函数做字符串比较。这是因为Oracle SQL的数据优先级，会将低优先级的字符串隐式转换转为日期格式。这里通过隐式转换，运行时间可以继续优化，从2S提升至1S。

但是注意，这是有风险的——首先，隐式数据类型转换可能会对性能产生负面影响（当然这里是正面的）；而且，隐式转换可能会产生奇怪的结果。具体取决于NLS_DATE_FORMAT参数的值。

当通过隐式转换或不指定格式模型的显式转换将日期时间值转换为文本时，格式模型由全球化会话参数之一定义，即NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT或者NLS_TIMESTAMP_TZ_FORMAT。

举个例子，NLS_DATE_FORMAT设置的默认值是'DD-MON-RR'两位数年份，因此当日期转换为字符串时，世纪时间可能会丢失，并在根据十年的值转换回日期时被 1900 或 2000 替换掉；并且假设原始时间有时分秒，这些数据也会丢失掉。

总之，不同的参数可能有不同的结果。

所以，考虑到方案2的优化并不是必要的，我还是保留了日期函数。