1. 文章
  2. 基础运维

SQL优化-子查询结果集过多

coredumped · 2018-10-22 11:05:46发表 · 4214 次点击
   1    2   

SQL优化-子查询结果集过多

这个优化案例是一个实际的业务场景，朋友公司从商业数据库迁移到PG中，测试中发现性能存在问题，同等数据量在商业数据库中不到秒级就出结果，但是在PG中缺需要几分钟甚至更久。

经过优化对SQL的改写效果还是不错，这里与大家分享下。

现象

最大的一张主表T98_SYT_ACCT_STAT_D_1有5.8亿，其余的都是百万级
原始SQL语句如下: 

  1. select
  2.         org.Internal_Org_Id    as  Stat_Org_Id     
  3.        ,(case when org.Internal_Org_Id = '999999888'
  4.               then '合计'
  5.               else org.Org_Name
  6.               end )            as  Stat_Org_Name   
  7.        ,(case when org.Internal_Org_Id = '999999888'
  8.               then 1
  9.               else 2
  10.               end)             as ORDERID
  11.        ,sum(data.jon_acct_num)         as   jon_acct_num     
  12.        ,sum(data.jon_cross_num)        as   jon_cross_num   
  13.        ,sum(data.jon_pay_num)          as   jon_pay_num      
  14.        ,sum(data.total_jon_num)        as   total_jon_num    
  15.        ,sum(data.des_acct_num)         as   des_acct_num     
  16.        ,sum(data.des_cross_num)        as   des_cross_num    
  17.        ,sum(data.des_pay_num)          as   des_pay_num      
  18.        ,sum(data.total_des_num)        as   total_des_num    
  19.        ,sum(data.accjon_acct_num)      as   accjon_acct_num  
  20.        ,sum(data.accjon_cross_num)     as   accjon_cross_num 
  21.        ,sum(data.accjon_pay_num)       as   accjon_pay_num   
  22.        ,sum(data.total_accjon_num)     as   total_accjon_num 
  23.        ,sum(data.accjon_acct_amt)      as   accjon_acct_amt  
  24.        ,sum(data.accjon_cross_amt)     as   accjon_cross_amt 
  25.        ,sum(data.accjon_acct1_amt)     as   accjon_acct1_amt 
  26.        ,sum(data.accjon_acct0_amt)     as   accjon_acct0_amt 
  27.        ,sum(data.accjon_pay_amt)       as   accjon_pay_amt   
  28.        ,sum(data.total_accjon_amt)     as   total_accjon_amt 
  29.   from cimfbview.t98_int_org_app_rela_h org
  30.   left join
  31.   (
  32.        select
  33.         syt.Stat_Org_Id             as   Stat_Org_Id        
  34.        ,sum(syt.jon_acct_num)       as   jon_acct_num       
  35.        ,sum(syt.jon_cross_num)      as   jon_cross_num      
  36.        ,sum(syt.jon_pay_num)        as   jon_pay_num        
  37.        ,sum(syt.total_jon_num)      as   total_jon_num     
  38.        ,sum(syt.des_acct_num)       as   des_acct_num       
  39.        ,sum(syt.des_cross_num)      as   des_cross_num      
  40.        ,sum(syt.des_pay_num)        as   des_pay_num        
  41.        ,sum(syt.total_des_num)      as   total_des_num      
  42.        ,sum(syt.accjon_acct_num)    as   accjon_acct_num    
  43.        ,sum(syt.accjon_cross_num)   as   accjon_cross_num   
  44.        ,sum(syt.accjon_pay_num)     as   accjon_pay_num     
  45.        ,sum(syt.total_accjon_num)   as   total_accjon_num   
  46.        ,sum(syt.accjon_acct_amt)    as   accjon_acct_amt    
  47.        ,sum(syt.accjon_cross_amt)   as   accjon_cross_amt   
  48.        ,sum(syt.accjon_acct1_amt)   as   accjon_acct1_amt   
  49.        ,sum(syt.accjon_acct0_amt)   as   accjon_acct0_amt   
  50.        ,sum(syt.accjon_pay_amt)     as   accjon_pay_amt     
  51.        ,sum(syt.total_accjon_amt)   as   total_accjon_amt   
  52.   from T98_SYT_ACCT_STAT_D_1 syt
  53.   where syt.Stat_Period_Cd = '1'   
  54.         and syt.Summ_Date  =  to_date('20161001','yyyymmdd')
  55.         and syt.Stat_Org_Attr_Cd = '9'
  56.         group by 1
  57.   )     data
  58.      on org.Internal_Org_Id=data.Stat_Org_Id
  59.   where  (org.Internal_Org_Id = '999999888'
  60.         OR ORG.Parent_Int_Org_Id = '999999888'
  61.             or '999999888' =
  62.             (
  63.           case
  64.             when '1' = '1' and '2' in ('2','3')  then nation_org_id
  65.             when '1' = '2' and '2' in ('3','4','5') then prov_org_id
  66.             when '1' = '3' and '2' in ('4','5') then city_org_id
  67.             when '1' = '4' and '2' = '5' then county_org_id
  68.           else Internal_Org_Id
  69.           end
  70.             )
  71.           and org.Int_Org_level_cd in ('1','2')  
  72.        )
  73.           and org.Int_Org_Stru_Type_Cd='1'
  74.           and org.Int_Org_Type_Cd in ('01','07','10','05','99')
  75.           and org.Start_Dt <= to_date('20161001','yyyymmdd')
  76.           and org.End_Dt > to_date('20161001','yyyymmdd')
  77.   group by 1,2,3;

查询比较慢我们首先查看执行计划:

  1. GroupAggregate  (cost=14409317.97..14409320.04 rows=18 width=622) (actual time=472510.542..472510.766 rows=37 loops=1)
  2.    Group Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COALESCE
  3. (org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  4.    ->  Sort  (cost=14409317.97..14409318.02 rows=18 width=622) (actual time=472510.519..472510.542 rows=37 loops=1)
  5.          Sort Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COA
  6. LESCE(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  7.          Sort Method: quicksort  Memory: 34kB
  8.          ->  Merge Left Join  (cost=14404111.77..14409317.60 rows=18 width=622) (actual time=472016.735..472510.437 rows=37 loops=
  9. 1)
  10.                Merge Cond: (a.internal_org_id = syt.stat_org_id)
  11.                ->  Sort  (cost=68934.45..68934.49 rows=18 width=76) (actual time=452.103..452.133 rows=37 loops=1)
  12.                      Sort Key: a.internal_org_id
  13.                      Sort Method: quicksort  Memory: 29kB
  14.                      ->  Gather  (cost=1000.42..68934.07 rows=18 width=76) (actual time=131.356..451.966 rows=37 loops=1)
  15.                            Workers Planned: 2
  16.                            Workers Launched: 2
  17.                            ->  Nested Loop Left Join  (cost=0.42..67932.27 rows=8 width=76) (actual time=58.910..398.641 rows=12 l
  18. oops=3)
  19.                                  ->  Parallel Seq Scan on t98_int_org_app_rela_h a  (cost=0.00..67864.63 rows=8 width=51) (actual
  20. time=58.773..398.006 rows=12 loops=3)
  21.                                        Filter: ((int_org_stru_type_cd = '1'::bpchar) AND (start_dt <= to_date('20161001'::text, 'y
  22. yyymmdd'::text)) AND (end_dt > to_date('20161001'::text, 'yyyymmdd'::text)) AND (int_org_type_cd = ANY ('{01,07,10,05,99}'::bpchar
  23. [])) AND ((internal_org_id = '999999888'::bpchar) OR (parent_int_org_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHE
  24. N ('2'::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE internal_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])
  25. ))))
  26.                                        Rows Removed by Filter: 549182
  27.                                  ->  Index Scan using idx_3 on t04_sys_organization org3  (cost=0.42..8.44 rows=1 width=45) (actua
  28. l time=0.041..0.042 rows=1 loops=37)
  29.                                        Index Cond: ((a.new_org_id = internal_org_id) AND (sys_id = 'S20'::character(3)))
  30.                                        Filter: (province_cd = 71)
  31.                ->  GroupAggregate  (cost=14335177.32..14339939.28 rows=35483 width=586) (actual time=471564.595..472002.661 rows=5
  32. 6831 loops=1)
  33.                      Group Key: syt.stat_org_id
  34.                      ->  Sort  (cost=14335177.32..14335317.84 rows=56208 width=100) (actual time=471564.558..471606.793 rows=56832
  35.  loops=1)
  36.                            Sort Key: syt.stat_org_id
  37.                            Sort Method: quicksort  Memory: 9529kB
  38.                            ->  Gather  (cost=1000.00..14330742.93 rows=56208 width=100) (actual time=70985.797..471057.045 rows=56
  39. 833 loops=1)
  40.                                  Workers Planned: 2
  41.                                  Workers Launched: 2
  42.                                  ->  Parallel Seq Scan on t98_syt_acct_stat_d_1 syt  (cost=0.00..14324122.13 rows=23420 width=100)
  43.  (actual time=71007.859..471086.192 rows=18944 loops=3)
  44.                                        Filter: ((stat_period_cd = '1'::bpchar) AND (stat_org_attr_cd = '9'::bpchar) AND (summ_date
  45.  = to_date('20161001'::text, 'yyyymmdd'::text)))
  46.                                        Rows Removed by Filter: 186677461
  47.  Planning time: 2.688 ms
  48.  Execution time: 472512.890 ms

通过执行计划看到t98_syt_acct_stat_d_1表并没有走到索引(其实表上是有索引)，通过调整random_page_cost,seq_page_cost让优化器更偏向于走索引，得到的执行计划如下:

  1. GroupAggregate  (cost=9913849.82..9913851.89 rows=18 width=622) (actual time=166908.251..166908.659 rows=37 loops=1)
  2.    Group Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COALESCE
  3. (org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  4.    ->  Sort  (cost=9913849.82..9913849.86 rows=18 width=622) (actual time=166908.218..166908.259 rows=37 loops=1)
  5.          Sort Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COA
  6. LESCE(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  7.          Sort Method: quicksort  Memory: 34kB
  8.          ->  Nested Loop Left Join  (cost=1.43..9913849.44 rows=18 width=622) (actual time=138.763..166907.954 rows=37 loops=1)
  9.                ->  Merge Left Join  (cost=1.00..9913805.16 rows=18 width=627) (actual time=138.717..166905.751 rows=37 loops=1)
  10.                      Merge Cond: (a.internal_org_id = syt.stat_org_id)
  11.                      ->  Index Scan using idx_2 on t98_int_org_app_rela_h a  (cost=0.43..74380.19 rows=18 width=51) (actual time=1
  12. 4.101..2392.532 rows=37 loops=1)
  13.                            Index Cond: (int_org_stru_type_cd = '1'::bpchar)
  14.                            Filter: ((start_dt <= to_date('20161001'::text, 'yyyymmdd'::text)) AND (end_dt > to_date('20161001'::te
  15. xt, 'yyyymmdd'::text)) AND (int_org_type_cd = ANY ('{01,07,10,05,99}'::bpchar[])) AND ((internal_org_id = '999999888'::bpchar) OR
  16. (parent_int_org_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'::text = ANY ('{2,3}'::text[])) THEN nation_org
  17. _id ELSE internal_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])))))
  18.                            Rows Removed by Filter: 415239
  19.                      ->  GroupAggregate  (cost=0.58..9838981.24 rows=35483 width=586) (actual time=69.743..164300.902 rows=56831 l
  20. oops=1)
  21.                            Group Key: syt.stat_org_id
  22.                            ->  Index Scan using idx_1 on t98_syt_acct_stat_d_1 syt  (cost=0.58..9834359.79 rows=56208 width=100) (
  23. actual time=45.638..163209.973 rows=56832 loops=1)
  24.                                  Index Cond: ((summ_date = to_date('20161001'::text, 'yyyymmdd'::text)) AND (stat_period_cd = '1':
  25. :bpchar) AND (stat_org_attr_cd = '9'::bpchar))
  26.                ->  Index Scan using idx_3 on t04_sys_organization org3  (cost=0.42..2.45 rows=1 width=45) (actual time=0.041..0.04
  27. 3 rows=1 loops=37)
  28.                      Index Cond: ((a.new_org_id = internal_org_id) AND (sys_id = 'S20'::character(3)))
  29.                      Filter: (province_cd = 71)
  30.  Planning time: 2.604 ms
  31.  Execution time: 166909.087 ms

可以看到通过走索引后时间减少了一半多，仔细观察执行计划在此处COST值消耗较大并且后续还有一个Merge Left Join,子查询数据返回太多导致后面做Merge时效率也很低。t98_syt_acct_stat_d_1这个表数据量最大5.8亿

  1. Index Scan using idx_1 on t98_syt_acct_stat_d_1 syt  (cost=0.58..9834359.79 rows=56208 width=100) (
  2. actual time=45.638..163209.973 rows=56832 loops=1)
  3.                                  Index Cond: ((summ_date = to_date('20161001'::text, 'yyyymmdd'::text)) AND (stat_period_cd = '1':
  4. :bpchar) AND (stat_org_attr_cd = '9'::bpchar))

解决方案

我们需要较少子查询返回的结果集，观察SQL语句中的这部分

  1. on org.Internal_Org_Id=data.Stat_Org_Id
  2.   where  (org.Internal_Org_Id = '999999888'
  3.         OR ORG.Parent_Int_Org_Id = '999999888'
  4.             or '999999888' =
  5.             (
  6.           case
  7.             when '1' = '1' and '2' in ('2','3')  then nation_org_id
  8.             when '1' = '2' and '2' in ('3','4','5') then prov_org_id
  9.             when '1' = '3' and '2' in ('4','5') then city_org_id
  10.             when '1' = '4' and '2' = '5' then county_org_id
  11.           else Internal_Org_Id
  12.           end
  13.             )
  14.           and org.Int_Org_level_cd in ('1','2')  
  15.        )
  16.           and org.Int_Org_Stru_Type_Cd='1'
  17.           and org.Int_Org_Type_Cd in ('01','07','10','05','99')
  18.           and org.Start_Dt <= to_date('20161001','yyyymmdd')
  19.           and org.End_Dt > to_date('20161001','yyyymmdd')

org.Internal_Org_Id=data.Stat_Org_Id ， 后面的where条件是org表的过滤条件，我们将此过滤条件放到子查询中减少子查询返回的结果集，这样可以提高效率
改写后的SQL及执行计划: 

  1.  select
  2.         org.Internal_Org_Id    as  Stat_Org_Id     --机构编号
  3.        ,(case when org.Internal_Org_Id = '999999888'
  4.               then '合计'
  5.               else org.Org_Name
  6.               end )            as  Stat_Org_Name   --机构代码
  7.        ,(case when org.Internal_Org_Id = '999999888'
  8.               then 1
  9.               else 2
  10.               end)             as ORDERID
  11.        ,sum(data.jon_acct_num)         as   jon_acct_num     
  12.        ,sum(data.jon_cross_num)        as   jon_cross_num    
  13.        ,sum(data.jon_pay_num)          as   jon_pay_num      
  14.        ,sum(data.total_jon_num)        as   total_jon_num    
  15.        ,sum(data.des_acct_num)         as   des_acct_num     
  16.        ,sum(data.des_cross_num)        as   des_cross_num    
  17.        ,sum(data.des_pay_num)          as   des_pay_num      
  18.        ,sum(data.total_des_num)        as   total_des_num    
  19.        ,sum(data.accjon_acct_num)      as   accjon_acct_num  
  20.        ,sum(data.accjon_cross_num)     as   accjon_cross_num 
  21.        ,sum(data.accjon_pay_num)       as   accjon_pay_num   
  22.        ,sum(data.total_accjon_num)     as   total_accjon_num 
  23.        ,sum(data.accjon_acct_amt)      as   accjon_acct_amt  
  24.        ,sum(data.accjon_cross_amt)     as   accjon_cross_amt 
  25.        ,sum(data.accjon_acct1_amt)     as   accjon_acct1_amt 
  26.        ,sum(data.accjon_acct0_amt)     as   accjon_acct0_amt 
  27.        ,sum(data.accjon_pay_amt)       as   accjon_pay_amt   
  28.        ,sum(data.total_accjon_amt)     as   total_accjon_amt 
  29.   from cimfbview.t98_int_org_app_rela_h org
  30.   left join
  31.   (
  32.        select
  33.         syt.Stat_Org_Id             as   Stat_Org_Id        
  34.        ,sum(syt.jon_acct_num)       as   jon_acct_num       
  35.        ,sum(syt.jon_cross_num)      as   jon_cross_num      
  36.        ,sum(syt.jon_pay_num)        as   jon_pay_num        
  37.        ,sum(syt.total_jon_num)      as   total_jon_num      
  38.        ,sum(syt.des_acct_num)       as   des_acct_num       
  39.        ,sum(syt.des_cross_num)      as   des_cross_num      
  40.        ,sum(syt.des_pay_num)        as   des_pay_num        
  41.        ,sum(syt.total_des_num)      as   total_des_num      
  42.        ,sum(syt.accjon_acct_num)    as   accjon_acct_num    
  43.        ,sum(syt.accjon_cross_num)   as   accjon_cross_num   
  44.        ,sum(syt.accjon_pay_num)     as   accjon_pay_num    
  45.        ,sum(syt.total_accjon_num)   as   total_accjon_num   
  46.        ,sum(syt.accjon_acct_amt)    as   accjon_acct_amt    
  47.        ,sum(syt.accjon_cross_amt)   as   accjon_cross_amt   
  48.        ,sum(syt.accjon_acct1_amt)   as   accjon_acct1_amt   
  49.        ,sum(syt.accjon_acct0_amt)   as   accjon_acct0_amt   
  50.        ,sum(syt.accjon_pay_amt)     as   accjon_pay_amt     
  51.        ,sum(syt.total_accjon_amt)   as   total_accjon_amt   
  52.   from T98_SYT_ACCT_STAT_D_1 syt
  53.   where syt.Stat_Period_Cd = '1'   
  54.         and syt.Summ_Date  =  to_date('20161001','yyyymmdd')
  55.         and syt.Stat_Org_Attr_Cd = '9'
  56.         and syt.Stat_Org_Id in (
  57.         select distinct(Internal_Org_Id) from  t98_int_org_app_rela_h org   where org.Internal_Org_Id = '999999888'
  58.         OR ORG.Parent_Int_Org_Id = '999999888'
  59.             or '999999888' =
  60.             (
  61.           case
  62.             when '1' = '1' and '2' in ('2','3')  then nation_org_id
  63.             when '1' = '2' and '2' in ('3','4','5') then prov_org_id
  64.             when '1' = '3' and '2' in ('4','5') then city_org_id
  65.             when '1' = '4' and '2' = '5' then county_org_id
  66.           else Internal_Org_Id
  67.           end
  68.             )
  69.           and org.Int_Org_level_cd in ('1','2')  
  70.           and org.Int_Org_Stru_Type_Cd='1'
  71.           and org.Int_Org_Type_Cd in ('01','07','10','05','99')
  72.           and org.Start_Dt <= to_date('20161001','yyyymmdd')
  73.           and org.End_Dt > to_date('20161001','yyyymmdd')
  75.         )
  76.         group by 1
  77.   )     data
  78.      on org.Internal_Org_Id=data.Stat_Org_Id
  79.   where  (org.Internal_Org_Id = '999999888'
  80.         OR ORG.Parent_Int_Org_Id = '999999888'
  81.             or '999999888' =
  82.             (
  83.           case
  84.             when '1' = '1' and '2' in ('2','3')  then nation_org_id
  85.             when '1' = '2' and '2' in ('3','4','5') then prov_org_id
  86.             when '1' = '3' and '2' in ('4','5') then city_org_id
  87.             when '1' = '4' and '2' = '5' then county_org_id
  88.           else Internal_Org_Id
  89.           end
  90.             )
  91.           and org.Int_Org_level_cd in ('1','2')  
  92.        )
  93.           and org.Int_Org_Stru_Type_Cd='1'
  94.           and org.Int_Org_Type_Cd in ('01','07','10','05','99')
  95.           and org.Start_Dt <= to_date('20161001','yyyymmdd')
  96.           and org.End_Dt > to_date('20161001','yyyymmdd')
  97.   group by 1,2,3;
  99.  GroupAggregate  (cost=140463.35..140465.42 rows=18 width=622) (actual time=548.831..549.113 rows=37 loops=1)
  100.    Group Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COALESCE
  101. (org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  102.    ->  Sort  (cost=140463.35..140463.40 rows=18 width=622) (actual time=548.789..548.817 rows=37 loops=1)
  103.          Sort Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COA
  104. LESCE(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  105.          Sort Method: quicksort  Memory: 34kB
  106.          ->  Nested Loop Left Join  (cost=69906.79..140462.98 rows=18 width=622) (actual time=520.434..548.664 rows=37 loops=1)
  107.                Join Filter: (a.internal_org_id = data.stat_org_id)
  108.                Rows Removed by Join Filter: 669
  109.                ->  Gather  (cost=1000.42..68934.07 rows=18 width=76) (actual time=63.704..89.506 rows=37 loops=1)
  110.                      Workers Planned: 2
  111.                      Workers Launched: 2
  112.                      ->  Nested Loop Left Join  (cost=0.42..67932.27 rows=8 width=76) (actual time=61.970..389.321 rows=12 loops=3
  113. )
  114.                            ->  Parallel Seq Scan on t98_int_org_app_rela_h a  (cost=0.00..67864.63 rows=8 width=51) (actual time=6
  115. 1.885..388.864 rows=12 loops=3)
  116.                                  Filter: ((int_org_stru_type_cd = '1'::bpchar) AND (start_dt <= to_date('20161001'::text, 'yyyymmd
  117. d'::text)) AND (end_dt > to_date('20161001'::text, 'yyyymmdd'::text)) AND (int_org_type_cd = ANY ('{01,07,10,05,99}'::bpchar[])) A
  118. ND ((internal_org_id = '999999888'::bpchar) OR (parent_int_org_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'
  119. ::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE internal_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])))))
  120.                                  Rows Removed by Filter: 549182
  121.                            ->  Index Scan using idx_3 on t04_sys_organization org3  (cost=0.42..8.44 rows=1 width=45) (actual time
  122. =0.027..0.029 rows=1 loops=37)
  123.                                  Index Cond: ((a.new_org_id = internal_org_id) AND (sys_id = 'S20'::character(3)))
  124.                                  Filter: (province_cd = 71)
  125.                ->  Materialize  (cost=68906.37..71450.71 rows=292 width=586) (actual time=12.309..12.389 rows=19 loops=37)
  126.                      ->  Subquery Scan on data  (cost=68906.37..71449.25 rows=292 width=586) (actual time=455.410..457.807 rows=40
  127.  loops=1)
  128.                            ->  GroupAggregate  (cost=68906.37..71446.33 rows=292 width=586) (actual time=455.407..457.739 rows=40
  129. loops=1)
  130.                                  Group Key: syt.stat_org_id
  131.                                  ->  Nested Loop  (cost=68906.37..71416.40 rows=292 width=100) (actual time=455.322..457.390 rows=
  132. 41 loops=1)
  133.                                        ->  Unique  (cost=68905.79..68907.25 rows=291 width=10) (actual time=455.259..456.424 rows=
  134. 44 loops=1)
  135.                                              ->  Sort  (cost=68905.79..68906.52 rows=292 width=10) (actual time=455.255..455.810 r
  136. ows=710 loops=1)
  137.                                                    Sort Key: org.internal_org_id
  138.                                                    Sort Method: quicksort  Memory: 58kB
  139.                                                    ->  Gather  (cost=1000.00..68893.83 rows=292 width=10) (actual time=0.683..453.
  140. 986 rows=725 loops=1)
  141.                                                          Workers Planned: 2
  142.                                                          Workers Launched: 2
  143.                                                          ->  Parallel Seq Scan on t98_int_org_app_rela_h org  (cost=0.00..67864.63
  144.  rows=122 width=10) (actual time=2.719..448.507 rows=242 loops=3)
  145.                                                                Filter: ((internal_org_id = '999999888'::bpchar) OR (parent_int_org
  146. _id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE intern
  147. al_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])) AND (int_org_stru_type_cd = '1'::bpchar) AND (int_org_type_cd = AN
  148. Y ('{01,07,10,05,99}'::bpchar[])) AND (start_dt <= to_date('20161001'::text, 'yyyymmdd'::text)) AND (end_dt > to_date('20161001'::
  149. text, 'yyyymmdd'::text))))
  150.                                                                Rows Removed by Filter: 548953
  151.                                        ->  Index Scan using idx_1 on t98_syt_acct_stat_d_1 syt  (cost=0.58..8.60 rows=1 width=100)
  152.  (actual time=0.016..0.017 rows=1 loops=44)
  153.                                              Index Cond: ((stat_org_id = org.internal_org_id) AND (summ_date = to_date('20161001':
  154. :text, 'yyyymmdd'::text)) AND (stat_period_cd = '1'::bpchar) AND (stat_org_attr_cd = '9'::bpchar))
  155.  Planning time: 2.997 ms
  156.  Execution time: 565.724 ms

执行时间缩小到不到1秒，通过执行计划看到SQL执行顺序发生了变化，t98_syt_acct_stat_d_1不是最先被执行的，而是先过滤完internal_org_id,再根据internal_org_id与t98_syt_acct_stat_d_1上的stat_org_id做 Nested Loop
还有一种方式可以将子查询提出来改成连接,这种方式SQL语句看起来比较清爽， 效率也不错 

  1. select
  2.       org.Internal_Org_Id    as  Stat_Org_Id     --机构编号
  3.      ,(case when org.Internal_Org_Id = '999999888'
  4.             then '合计'
  5.             else org.Org_Name
  6.             end )            as  Stat_Org_Name   --机构代码
  7.      ,(case when org.Internal_Org_Id = '999999888'
  8.             then 1
  9.             else 2
  10.             end)             as ORDERID
  11.      ,sum(data.jon_acct_num     )   as   jon_acct_num     
  12.      ,sum(data.jon_cross_num    )   as   jon_cross_num    
  13.      ,sum(data.jon_pay_num      )   as   jon_pay_num      
  14.      ,sum(data.total_jon_num    )   as   total_jon_num    
  15.      ,sum(data.des_acct_num     )   as   des_acct_num     
  16.      ,sum(data.des_cross_num    )   as   des_cross_num    
  17.      ,sum(data.des_pay_num      )   as   des_pay_num      
  18.      ,sum(data.total_des_num    )   as   total_des_num    
  19.      ,sum(data.accjon_acct_num  )   as   accjon_acct_num  
  20.      ,sum(data.accjon_cross_num )   as   accjon_cross_num 
  21.      ,sum(data.accjon_pay_num   )   as   accjon_pay_num   
  22.      ,sum(data.total_accjon_num )   as   total_accjon_num 
  23.      ,sum(data.accjon_acct_amt  )   as   accjon_acct_amt  
  24.      ,sum(data.accjon_cross_amt )   as   accjon_cross_amt 
  25.      ,sum(data.accjon_acct1_amt )   as   accjon_acct1_amt 
  26.      ,sum(data.accjon_acct0_amt )   as   accjon_acct0_amt 
  27.      ,sum(data.accjon_pay_amt   )   as   accjon_pay_amt   
  28.      ,sum(data.total_accjon_amt )   as   total_accjon_amt 
  29. from cimfbview.t98_int_org_app_rela_h org
  30. left join T98_SYT_ACCT_STAT_D_1 data
  31.        on org.Internal_Org_Id=data.Stat_Org_Id
  32.       and data.Stat_Period_Cd = '1'
  33.       and data.Summ_Date  =  to_date('20161001','yyyymmdd')
  34.       and data.Stat_Org_Attr_Cd = '9'
  35. where  (org.Internal_Org_Id = '999999888'
  36.       OR ORG.Parent_Int_Org_Id = '999999888'
  37.           or '999999888' =
  38.           (
  39.         case
  40.           when '1' = '1' and '2' in ('2','3')  then nation_org_id
  41.           when '1' = '2' and '2' in ('3','4','5') then prov_org_id
  42.           when '1' = '3' and '2' in ('4','5') then city_org_id
  43.           when '1' = '4' and '2' = '5' then county_org_id
  44.         else Internal_Org_Id
  45.         end
  46.           )
  47.         and org.Int_Org_level_cd in ('1','2')  
  48.      )
  49.         and org.Int_Org_Stru_Type_Cd='1'
  50.         and org.Int_Org_Type_Cd in ('01','07','10','05','99')
  51.         and org.Start_Dt <= to_date('20161001','yyyymmdd')
  52.         and org.End_Dt > to_date('20161001','yyyymmdd')
  53. group by 1,2,3
  54. order by    1,2,3;
  57. GroupAggregate  (cost=69003.44..69005.51 rows=18 width=622) (actual time=401.488..401.925 rows=37 loops=1)
  58.    Group Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COALESCE
  59. (org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  60.    ->  Sort  (cost=69003.44..69003.48 rows=18 width=136) (actual time=401.431..401.487 rows=37 loops=1)
  61.          Sort Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COA
  62. LESCE(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  63.          Sort Method: quicksort  Memory: 34kB
  64.          ->  Gather  (cost=1001.00..69003.06 rows=18 width=136) (actual time=61.302..401.261 rows=37 loops=1)
  65.                Workers Planned: 2
  66.                Workers Launched: 2
  67.                ->  Nested Loop Left Join  (cost=1.00..68001.21 rows=8 width=136) (actual time=56.932..395.159 rows=12 loops=3)
  68.                      ->  Nested Loop Left Join  (cost=0.42..67932.27 rows=8 width=76) (actual time=56.841..394.613 rows=12 loops=3
  69. )
  70.                            ->  Parallel Seq Scan on t98_int_org_app_rela_h a  (cost=0.00..67864.63 rows=8 width=51) (actual time=5
  71. 6.745..394.173 rows=12 loops=3)
  72.                                  Filter: ((int_org_stru_type_cd = '1'::bpchar) AND (start_dt <= to_date('20161001'::text, 'yyyymmd
  73. d'::text)) AND (end_dt > to_date('20161001'::text, 'yyyymmdd'::text)) AND (int_org_type_cd = ANY ('{01,07,10,05,99}'::bpchar[])) A
  74. ND ((internal_org_id = '999999888'::bpchar) OR (parent_int_org_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'
  75. ::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE internal_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])))))
  76.                                  Rows Removed by Filter: 549182
  77.                            ->  Index Scan using idx_3 on t04_sys_organization org3  (cost=0.42..8.44 rows=1 width=45) (actual time
  78. =0.026..0.027 rows=1 loops=37)
  79.                                  Index Cond: ((a.new_org_id = internal_org_id) AND (sys_id = 'S20'::character(3)))
  80.                                  Filter: (province_cd = 71)
  81.                      ->  Index Scan using idx_1 on t98_syt_acct_stat_d_1 data  (cost=0.58..8.60 rows=1 width=100) (actual time=0.0
  82. 34..0.036 rows=1 loops=37)
  83.                            Index Cond: ((a.internal_org_id = stat_org_id) AND (summ_date = to_date('20161001'::text, 'yyyymmdd'::t
  84. ext)) AND (stat_period_cd = '1'::bpchar) AND (stat_org_attr_cd = '9'::bpchar))
  85.  Planning time: 4.092 ms
  86.  Execution time: 412.283 ms

总结

优化就是减少对数据扫描次数，子查询或连接查询时应尽量将结果集在底层减少数据结果

观察执行计划要看按执行顺序查看，这样才会发现哪一步执行计划出现了问题、返回的数据量最大、COST值消耗最大

数据库都是相通的这里数据库是PG但MySQL优化思路也一样。

本站文章，未经作者同意，请勿转载，如需转载，请邮件customer@csudata.com.
0 评论  
添加一条新评论

用户登录

个人首页
立即登录