SQL优化 -- 一例 Union All 引发的性能问题

在帮助用户优化应用过程中，发现用户有个SQL不恰当地使用union all， 导致SQL解析非常缓慢的问题。这个语句优化很简单，但考虑到这个问题很有代表意义，我觉得很有必要对于问题进行总结。

一、用户例子

整个语句用了将近1500个Union All，使得语句阅读性非常差。且由于语句中含有大量的Select ，整个解析耗时非常大。

WITH company_user_temp AS (SELECT '00629999000100260000' AS company_code
 FROM dual
 UNION ALL
 SELECT '00250033000000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00630005000300000000' AS company_code FROM dual
 UNION ALL
 SELECT '00460207000000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00420089000000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00630008000100000000' AS company_code FROM dual
 UNION ALL
 SELECT '00630013001000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00620035001900000000' AS company_code FROM dual
......

用户大量使用值的union all，导致90%的时间耗在SQL解析上。

         ->  Seq Scan on dual dual_1458  (cost=0.00..1.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
         ->  Seq Scan on dual dual_1459  (cost=0.00..1.01 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
         ->  Seq Scan on dual dual_1460  (cost=0.00..1.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
         ->  Seq Scan on dual dual_1461  (cost=0.00..1.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
 Planning Time: 5081.423 ms
 Execution Time: 43.726 ms
(1466 rows)

Time: 5230.600 ms (00:05.231)

 可以看到，由于整个SQL非常长，涉及dual 表频次非常多，导致SQL解析非常耗时。

PS：对于union all 的每个部分，优化器都要进行 parse , analyze , plan 操作，虽然速度上很快，但由于数量过多，总耗时很大。

二、问题分析

由于SQL有大量的union all，针对union all 的每个部分，SQL 都要进行解析。 由于整个SQL涉及1500多张表 （dual），整个性能非常差。考虑以下修改方式：

with company_user_temp as (select  * from (values('00629999000100260000'), ('00250033000000000000')) as company_code )
select count(*) from company_user_temp;

通过这种方式，可以减少整条SQL涉及表的数量，提升SQL解析的性能。

三、验证分析

由于SQL过长，无法实际修改验证，构建以下动态SQL进行验证。

1、union 方式

test=# declare
test-#   v_sql text;
test-# begin
test-#   v_sql:= 'WITH company_user_temp AS (';
test-#   for i in 1..2000 loop
test-#     v_sql:=v_sql||'select '||i||' as company_code from dual union all ';
test-#   end loop;
test-#   v_sql:=substr(v_sql,0,length(v_sql) - 10) ||') select count(*) from company_user_temp';
test-#   execute immediate v_sql;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 6735.076 ms (00:06.735)

2、values 方式

test=# declare
test-#   v_sql text;
test-# begin
test-#   v_sql := 'with company_code as (select * from (values(';
test-#   for i in 1..2000 loop
test-#     v_sql:=v_sql||i||'),(';
test-#   end loop;
test-#   v_sql:=substr(v_sql,0,length(v_sql) - 2)||') as company_code ) select count(*) from company_code';
test-#   execute immediate v_sql;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 10.325 ms

结论：可以看到，这样修改后，SQL 性能得到了大幅提升。

四、总结

除了用values 这种写法外，还可以尝试用数组的方式。