一次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,被反复连接,且每次连接都使用了表扫描:

一次SQL优化经历

先看看这张表的情况,实际上连接使用的都是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秒!

一次SQL优化经历

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

一次SQL优化经历

  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的连接的计划中含有如下提示:
    一次SQL优化经历

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

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

  2. T1;
  3. T2;
    Analyze T3;
    Analyze T4;

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

上一篇:如何从线下数据库迁移到RDS


下一篇:PG锁问题排查