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')
  74. )
  75. group by 1
  76. ) data
  77. on org.Internal_Org_Id=data.Stat_Org_Id
  78. where (org.Internal_Org_Id = '999999888'
  79. OR ORG.Parent_Int_Org_Id = '999999888'
  80. or '999999888' =
  81. (
  82. case
  83. when '1' = '1' and '2' in ('2','3') then nation_org_id
  84. when '1' = '2' and '2' in ('3','4','5') then prov_org_id
  85. when '1' = '3' and '2' in ('4','5') then city_org_id
  86. when '1' = '4' and '2' = '5' then county_org_id
  87. else Internal_Org_Id
  88. end
  89. )
  90. and org.Int_Org_level_cd in ('1','2')
  91. )
  92. and org.Int_Org_Stru_Type_Cd='1'
  93. and org.Int_Org_Type_Cd in ('01','07','10','05','99')
  94. and org.Start_Dt <= to_date('20161001','yyyymmdd')
  95. and org.End_Dt > to_date('20161001','yyyymmdd')
  96. group by 1,2,3;
  97. GroupAggregate (cost=140463.35..140465.42 rows=18 width=622) (actual time=548.831..549.113 rows=37 loops=1)
  98. Group Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COALESCE
  99. (org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  100. -> Sort (cost=140463.35..140463.40 rows=18 width=622) (actual time=548.789..548.817 rows=37 loops=1)
  101. Sort Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COA
  102. LESCE(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  103. Sort Method: quicksort Memory: 34kB
  104. -> Nested Loop Left Join (cost=69906.79..140462.98 rows=18 width=622) (actual time=520.434..548.664 rows=37 loops=1)
  105. Join Filter: (a.internal_org_id = data.stat_org_id)
  106. Rows Removed by Join Filter: 669
  107. -> Gather (cost=1000.42..68934.07 rows=18 width=76) (actual time=63.704..89.506 rows=37 loops=1)
  108. Workers Planned: 2
  109. Workers Launched: 2
  110. -> Nested Loop Left Join (cost=0.42..67932.27 rows=8 width=76) (actual time=61.970..389.321 rows=12 loops=3
  111. )
  112. -> Parallel Seq Scan on t98_int_org_app_rela_h a (cost=0.00..67864.63 rows=8 width=51) (actual time=6
  113. 1.885..388.864 rows=12 loops=3)
  114. Filter: ((int_org_stru_type_cd = '1'::bpchar) AND (start_dt <= to_date('20161001'::text, 'yyyymmd
  115. d'::text)) AND (end_dt > to_date('20161001'::text, 'yyyymmdd'::text)) AND (int_org_type_cd = ANY ('{01,07,10,05,99}'::bpchar[])) A
  116. ND ((internal_org_id = '999999888'::bpchar) OR (parent_int_org_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'
  117. ::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE internal_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])))))
  118. Rows Removed by Filter: 549182
  119. -> Index Scan using idx_3 on t04_sys_organization org3 (cost=0.42..8.44 rows=1 width=45) (actual time
  120. =0.027..0.029 rows=1 loops=37)
  121. Index Cond: ((a.new_org_id = internal_org_id) AND (sys_id = 'S20'::character(3)))
  122. Filter: (province_cd = 71)
  123. -> Materialize (cost=68906.37..71450.71 rows=292 width=586) (actual time=12.309..12.389 rows=19 loops=37)
  124. -> Subquery Scan on data (cost=68906.37..71449.25 rows=292 width=586) (actual time=455.410..457.807 rows=40
  125. loops=1)
  126. -> GroupAggregate (cost=68906.37..71446.33 rows=292 width=586) (actual time=455.407..457.739 rows=40
  127. loops=1)
  128. Group Key: syt.stat_org_id
  129. -> Nested Loop (cost=68906.37..71416.40 rows=292 width=100) (actual time=455.322..457.390 rows=
  130. 41 loops=1)
  131. -> Unique (cost=68905.79..68907.25 rows=291 width=10) (actual time=455.259..456.424 rows=
  132. 44 loops=1)
  133. -> Sort (cost=68905.79..68906.52 rows=292 width=10) (actual time=455.255..455.810 r
  134. ows=710 loops=1)
  135. Sort Key: org.internal_org_id
  136. Sort Method: quicksort Memory: 58kB
  137. -> Gather (cost=1000.00..68893.83 rows=292 width=10) (actual time=0.683..453.
  138. 986 rows=725 loops=1)
  139. Workers Planned: 2
  140. Workers Launched: 2
  141. -> Parallel Seq Scan on t98_int_org_app_rela_h org (cost=0.00..67864.63
  142. rows=122 width=10) (actual time=2.719..448.507 rows=242 loops=3)
  143. Filter: ((internal_org_id = '999999888'::bpchar) OR (parent_int_org
  144. _id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE intern
  145. 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
  146. Y ('{01,07,10,05,99}'::bpchar[])) AND (start_dt <= to_date('20161001'::text, 'yyyymmdd'::text)) AND (end_dt > to_date('20161001'::
  147. text, 'yyyymmdd'::text))))
  148. Rows Removed by Filter: 548953
  149. -> Index Scan using idx_1 on t98_syt_acct_stat_d_1 syt (cost=0.58..8.60 rows=1 width=100)
  150. (actual time=0.016..0.017 rows=1 loops=44)
  151. Index Cond: ((stat_org_id = org.internal_org_id) AND (summ_date = to_date('20161001':
  152. :text, 'yyyymmdd'::text)) AND (stat_period_cd = '1'::bpchar) AND (stat_org_attr_cd = '9'::bpchar))
  153. Planning time: 2.997 ms
  154. 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;
  55. GroupAggregate (cost=69003.44..69005.51 rows=18 width=622) (actual time=401.488..401.925 rows=37 loops=1)
  56. Group Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COALESCE
  57. (org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  58. -> Sort (cost=69003.44..69003.48 rows=18 width=136) (actual time=401.431..401.487 rows=37 loops=1)
  59. Sort Key: a.internal_org_id, (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN '合计'::character varying ELSE COA
  60. LESCE(org3.org_name, a.org_name) END), (CASE WHEN (a.internal_org_id = '999999888'::bpchar) THEN 1 ELSE 2 END)
  61. Sort Method: quicksort Memory: 34kB
  62. -> Gather (cost=1001.00..69003.06 rows=18 width=136) (actual time=61.302..401.261 rows=37 loops=1)
  63. Workers Planned: 2
  64. Workers Launched: 2
  65. -> Nested Loop Left Join (cost=1.00..68001.21 rows=8 width=136) (actual time=56.932..395.159 rows=12 loops=3)
  66. -> Nested Loop Left Join (cost=0.42..67932.27 rows=8 width=76) (actual time=56.841..394.613 rows=12 loops=3
  67. )
  68. -> Parallel Seq Scan on t98_int_org_app_rela_h a (cost=0.00..67864.63 rows=8 width=51) (actual time=5
  69. 6.745..394.173 rows=12 loops=3)
  70. Filter: ((int_org_stru_type_cd = '1'::bpchar) AND (start_dt <= to_date('20161001'::text, 'yyyymmd
  71. d'::text)) AND (end_dt > to_date('20161001'::text, 'yyyymmdd'::text)) AND (int_org_type_cd = ANY ('{01,07,10,05,99}'::bpchar[])) A
  72. ND ((internal_org_id = '999999888'::bpchar) OR (parent_int_org_id = '999999888'::bpchar) OR (('999999888'::bpchar = CASE WHEN ('2'
  73. ::text = ANY ('{2,3}'::text[])) THEN nation_org_id ELSE internal_org_id END) AND (int_org_level_cd = ANY ('{1,2}'::bpchar[])))))
  74. Rows Removed by Filter: 549182
  75. -> Index Scan using idx_3 on t04_sys_organization org3 (cost=0.42..8.44 rows=1 width=45) (actual time
  76. =0.026..0.027 rows=1 loops=37)
  77. Index Cond: ((a.new_org_id = internal_org_id) AND (sys_id = 'S20'::character(3)))
  78. Filter: (province_cd = 71)
  79. -> 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
  80. 34..0.036 rows=1 loops=37)
  81. Index Cond: ((a.internal_org_id = stat_org_id) AND (summ_date = to_date('20161001'::text, 'yyyymmdd'::t
  82. ext)) AND (stat_period_cd = '1'::bpchar) AND (stat_org_attr_cd = '9'::bpchar))
  83. Planning time: 4.092 ms
  84. Execution time: 412.283 ms

总结

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

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

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

0 评论  
添加一条新评论