一次SQL优化经历

最近遇到一个SQL执行很慢。这个SQL比较长，但基本的形态比较简单：

SELECT T1.*, T2.C1 .... FROM T1 LEFT JOIN T2 ON T1.C1 = T2.C1 LEFT JOIN T3 ON T1.C2 = T3.C1 LEFT JOIN T3 ON T1.C3 = T3.C1 ....

1. 执行explain ，直接查看执行计划。发现很多的SeqScan（表扫描）。其中有一个表T3，被反复连接，且每次连接都使用了表扫描：

先看看这张表的情况，实际上连接使用的都是C1字段，而这个字段上有索引，为什么没有使用？利用select count(*) from T3，看到此张表的数据量不大（13393），这可能是优化器为选择索引的原因（数据量过小），但也可能是 统计信息不准确造成，执行了ANALYZE T3未见变化。（后来发现是这样，当加入LIMIT子句，且limit小于10000时，使用的是索引扫描，否则使用的是表扫描，这是由优化器根据代价评估决定的，并没有问题。）

这里，我们感觉执行计划基本没有问题。

1. 用explain analyze 分析SQL执行过程中，哪个环节耗费了最多时间。直接执行发现长时间得不到结果，所以加入了LIMIT子句，即 explain analyze limit 1000，执行耗时8秒多。查询计划中处理了对T4等表的表扫描，但这些表数据量都不大。仔细查看查询计划中的actual time的变化情况，发现在第二行出现了一个不正常的现象：actual time=11.614..8913.433 。这个数据中，11.614代表输出第一行时所用的时间，8913.433 代码输出所有行的时间，也就是说输出第一行用了11ms，输出1000行用了8.9秒！

查看最后一行，发现了原因，这里有个SubPlan，就是每次输出一行前都要计算一下这个SubPlan，而这个Subplan含有对T1的一次表扫描，就是说每次都要对hr_users全部扫描一次，返回1000条结果要扫描1000次T1。hr_users有2万多条记录，即总计扫描2000万左右的记录！

1. 查看SQL，发现这个Subplan对应一个SELECT里面的子查询，里面有个条件是WHERE TO_CHAR(T1.C10) = TO_CHAR(TEMP.C20)) ，而我们知道这种TO_CHAR转换容易造成索引无法使用（USER_ID是数字类型）。将这个条件改为：T1.C10 = TO_number(TEMP.C20)) （还需要业务评估是否可以这样改！），上述语句的运行时间由8秒降低至100多ms！！
2. 试着执行explain analyze limit 30000，发现与T2的连接的计划中含有如下提示：
   

即与T2的连接过滤了很多记录，怀疑是否这个连接是性能瓶颈。而T2是个View，于是试着把这个View的结果插入到一个表中，并建立索引，使用这个表替换T2，发现作用不大。

1. 为了防止统计信息不准确，运行了如下的语句收集查询中涉及的表的统计信息：

2. T1;

3. T2;
   Analyze T3;
   Analyze T4;

上述修改后，大幅降低了原SQL的查询延迟。直接执行这个SQL结果超过数十万行，因此进一步的优化，需要查看这个SQL是如何使用的，根据场景进行优化。