冗余数据JOIN导致的慢SQL优化一例

CASE

一个这样的查询，每个表都只有几千条数据，但是查询非常慢，几十秒不出结果。

select  
distinct abc.pro_col1, abc.col3  
from  
t0 p  
INNER JOIN t1 abc 
  on p.id=abc.par_col2
inner join t2 s 
  on  s.col3=abc.col3  
inner join t3 po 
  on  po.id=s.col4 
where p.state=2 and po.state=3 
order by abc.pro_col1, abc.col3; 

优化方法

从语义来看，这条SQL是在经过几个JOIN后取其中一个表的两个字段的唯一值。

但是每一次关联，都可能产生冗余的值，所以导致了结果集越来越庞大。

修改建议，每一次JOIN都输出唯一值，减少冗余。

select 
distinct pro_col1, col3 from
(
select 
distinct t1.pro_col1, t1.col3, s.col4 from 
(
select 
distinct abc.pro_col1, abc.col3 from 
t1 abc INNER JOIN t0 p    
on (p.id = abc.par_col2 and p.state=2)
) t1
inner join t2 s 
on (s.col3 = t1.col3)
) t2
inner join t3 po   
on (po.id = t2.col4 and po.state=3)
order by t2.pro_col1, t2.col3  ;

修改后几十毫秒可以输出结果。

重现

postgres=# create table rt1(id int, info text);
CREATE TABLE
postgres=# create table rt2(id int, info text);
CREATE TABLE
postgres=# create table rt3(id int, info text);
CREATE TABLE
postgres=# create table rt4(id int, info text);
CREATE TABLE

postgres=# insert into rt1 select generate_series(1,1000),'test';
INSERT 0 1000
postgres=# insert into rt2 select 1,'test' from generate_series(1,1000);
INSERT 0 1000
postgres=# insert into rt3 select 1,'test' from generate_series(1,1000);
INSERT 0 1000
postgres=# insert into rt4 select 1,'test' from generate_series(1,1000);
INSERT 0 1000

以下查询，每次JOIN都产生大量的冗余数据，越到后面的JOIN，冗余越多，导致的查询非常漫长。

postgres=# explain select distinct rt1.id from rt1 join rt2 on rt1.id=rt2.id join rt3 on rt2.id=rt3.id join rt4 on rt3.id=rt4.id;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 HashAggregate  (cost=145.25..155.25 rows=1000 width=4)
   Group Key: rt1.id
   ->  Hash Join  (cost=113.00..142.75 rows=1000 width=4)
         Hash Cond: (rt4.id = rt1.id)
         ->  Seq Scan on rt4  (cost=0.00..16.00 rows=1000 width=4)
         ->  Hash  (cost=100.50..100.50 rows=1000 width=12)
               ->  Hash Join  (cost=70.75..100.50 rows=1000 width=12)
                     Hash Cond: (rt3.id = rt1.id)
                     ->  Seq Scan on rt3  (cost=0.00..16.00 rows=1000 width=4)
                     ->  Hash  (cost=58.25..58.25 rows=1000 width=8)
                           ->  Hash Join  (cost=28.50..58.25 rows=1000 width=8)
                                 Hash Cond: (rt2.id = rt1.id)
                                 ->  Seq Scan on rt2  (cost=0.00..16.00 rows=1000 width=4)
                                 ->  Hash  (cost=16.00..16.00 rows=1000 width=4)
                                       ->  Seq Scan on rt1  (cost=0.00..16.00 rows=1000 width=4)
(15 rows)

修改如下，可以很快的得到结果

postgres=# select distinct t2.id from 
(
select distinct t1.id from 
(select distinct rt1.id from rt1 join rt2 on rt1.id=rt2.id) t1
join 
rt3 on t1.id=rt3.id
) t2
join rt4 on t2.id=rt4.id
;
 id 
----
  1
(1 row)
Time: 2.052 ms

postgres=# explain select distinct t2.id from 
postgres-# (
postgres(# select distinct t1.id from 
postgres(# (select distinct rt1.id from rt1 join rt2 on rt1.id=rt2.id) t1
postgres(# join 
postgres(# rt3 on t1.id=rt3.id
postgres(# ) t2
postgres-# join rt4 on t2.id=rt4.id
postgres-# ;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=190.25..200.25 rows=1000 width=4)
   Group Key: rt1.id
   ->  Hash Join  (cost=158.00..187.75 rows=1000 width=4)
         Hash Cond: (rt4.id = rt1.id)
         ->  Seq Scan on rt4  (cost=0.00..16.00 rows=1000 width=4)
         ->  Hash  (cost=145.50..145.50 rows=1000 width=4)
               ->  HashAggregate  (cost=125.50..135.50 rows=1000 width=4)
                     Group Key: rt1.id
                     ->  Hash Join  (cost=93.25..123.00 rows=1000 width=4)
                           Hash Cond: (rt3.id = rt1.id)
                           ->  Seq Scan on rt3  (cost=0.00..16.00 rows=1000 width=4)
                           ->  Hash  (cost=80.75..80.75 rows=1000 width=4)
                                 ->  HashAggregate  (cost=60.75..70.75 rows=1000 width=4)
                                       Group Key: rt1.id
                                       ->  Hash Join  (cost=28.50..58.25 rows=1000 width=4)
                                             Hash Cond: (rt2.id = rt1.id)
                                             ->  Seq Scan on rt2  (cost=0.00..16.00 rows=1000 width=4)
                                             ->  Hash  (cost=16.00..16.00 rows=1000 width=4)
                                                   ->  Seq Scan on rt1  (cost=0.00..16.00 rows=1000 width=4)
(19 rows)

Time: 0.750 ms

小结

这种SQL，如果要改内核的话，可以对统计信息进行分析（每个字段都有n_distinct），并对其进行query rewrite，得到同样的结果。

postgres=# \d pg_stats
          View "pg_catalog.pg_stats"
         Column         |   Type   | Modifiers 
------------------------+----------+-----------
 schemaname             | name     | 
 tablename              | name     | 
 attname                | name     | 
 inherited              | boolean  | 
 null_frac              | real     | 
 avg_width              | integer  | 
 n_distinct             | real     | 
 most_common_vals       | anyarray | 
 most_common_freqs      | real[]   | 
 histogram_bounds       | anyarray | 
 correlation            | real     | 
 most_common_elems      | anyarray | 
 most_common_elem_freqs | real[]   | 
 elem_count_histogram   | real[]   | 

祝大家玩得开心，欢迎随时来 阿里云促膝长谈业务需求 ，恭候光临。

阿里云的小伙伴们加油，努力 做好内核与服务，打造最贴地气的云数据库 。