SQL优化案例-运用原理优化执行计划

godba 2024-04-01

132

在计划写这篇文章时候题目斟酌了好久，但感觉也是欠那么点意思。在听的武侠经典歌曲使我脑海中突然浮现一帧画面。全真武士一剑刺向杨过，眼看正中右肩，只见杨过轻轻一个闪身。全真武士一个跟头。自己扑空摔向地面。正如我们的优化，我们知道性能瓶颈处，适当运用原理优化执行计划。

背景：

日常开发咨询SQL优化方案，SQL

 SELECT    b.NAME,
  b.psr_id psrId, b.ast_id astId, b.professional_type professionalType,
  b.equip_type_name equipTypeName, b.voltage_level voltageLevel,
  b.station_line_name stationLineName, b.city,
  b.city_name cityName,   b.maint_org_name maintOrgName,
  b.maint_group_name maintGroupName, A.sun_year_month sunYearMonth, 
  COALESCE ( A.workorder_num, 0 ) workorderNum 
FROM  cost_e***p_pl_month  A 
  left  JOIN cost_e***p_pl_info b  ON A.ast_id = b.ast_id   AND   A.psr_id = b.psr_id  
       and exists (select  1 from  cost_org***ping_info_pl org  where  org.full_path_id LIKE concat('%','ff8080814a616f0f014b38b062e0050e', '%')
                   and org.org_id = b.maint_group   )
WHERE   b.professional_type = '01'
  AND A.sun_year_month <= '202211' 
   AND A.sun_year_month >= '202201' 
ORDER BY
  A.sun_year_month DESC 
LIMIT 20 OFFSET 0

执行计划

Limit  (cost=162070.25..162070.26 rows=1 width=168) (actual time=19660.571..19660.888 rows=20 loops=1)
  Buffers: shared hit=530039 read=196847 written=3
  I/O Timings: read=17143.625 write=0.172
  ->  Sort  (cost=162070.25..162070.26 rows=1 width=168) (actual time=19660.569..19660.885 rows=20 loops=1)
        Sort Key: a.sun_year_month DESC
        Sort Method: top-N heapsort  Memory: 30kB
        Buffers: shared hit=530039 read=196847 written=3
        I/O Timings: read=17143.625 write=0.172
        ->  Gather  (cost=4490.68..162070.24 rows=1 width=168) (actual time=36.620..19550.660 rows=127733 loops=1)
              Workers Planned: 2
              Workers Launched: 0
              Buffers: shared hit=530039 read=196847 written=3
              I/O Timings: read=17143.625 write=0.172
              ->  Nested Loop  (cost=3490.68..161070.14 rows=1 width=168) (actual time=28.689..19522.880 rows=127733 loops=1)
                    Buffers: shared hit=530039 read=196847 written=3
                    I/O Timings: read=17143.625 write=0.172
                    ->  Parallel Hash Join  (cost=3490.13..135230.43 rows=13603 width=85) (actual time=11.101..2695.386 rows=78467 loops=1)
                          Hash Cond: ((b.maint_group)::text = (org.org_id)::text)
                          Buffers: shared hit=261 read=122909
                          I/O Timings: read=2024.567
                          ->  Parallel Seq Scan on cost_e***p_pl_info b  (cost=0.00..130833.94 rows=345232 width=117) (actual time=0.005..2417.542 rows=827989 loops=1)
                                Filter: ((professional_type)::text = '01'::text)
                                Rows Removed by Filter: 833762
                                Buffers: shared hit=8 read=122165
                                I/O Timings: read=2021.871
                          ->  Parallel Hash  (cost=3481.49..3481.49 rows=691 width=30) (actual time=10.627..10.631 rows=1128 loops=1)
                                Buckets: 2048  Batches: 1  Memory Usage: 112kB
                                Buffers: shared hit=253 read=744
                                I/O Timings: read=2.695
                                ->  Parallel Bitmap Heap Scan on cost_org***ping_info_pl org  (cost=425.10..3481.49 rows=691 width=30) (actual time=6.155..10.323 rows=1128 loops=1)
                                      Recheck Cond: ((full_path_id)::text ~~ concat('%', 'ff8080814a616f0f014b38b062e0050e', '%'))
                                      Heap Blocks: exact=551
                                      Buffers: shared hit=253 read=744
                                      I/O Timings: read=2.695
                                      ->  Bitmap Index Scan on index_cost_orgmapping_info_pl_full_path_id  (cost=0.00..424.81 rows=1174 width=0) (actual time=6.050..6.050 rows=1128 loops=1)
                                            Index Cond: ((full_path_id)::text ~~ concat('%', 'ff8080814a616f0f014b38b062e0050e', '%'))
                                            Buffers: shared hit=243 read=203
                                            I/O Timings: read=0.794
                    ->  Index Scan using cost_equip_pl_month_idx_ast_id on cost_e***p_pl_month a  (cost=0.56..1.89 rows=1 width=90) (actual time=0.177..0.213 rows=2 loops=78467)
                          Index Cond: ((ast_id)::text = (b.ast_id)::text)
                          Filter: (((sun_year_month)::text <= '202211'::text) AND ((sun_year_month)::text >= '202201'::text) AND ((b.psr_id)::text = (psr_id)::text))
                          Rows Removed by Filter: 2
                          Buffers: shared hit=529778 read=73938 written=3
                          I/O Timings: read=15119.058 write=0.172
Planning Time: 14.548 ms
Execution Time: 19661.110 ms

分析：

1.外连接向内连接转换。A表left join B , 却在where 条件中有B表的过滤条件，会使得left join 向inner join 转换。

2.查询结果排序，导致执行成本较高。

性能瓶颈：

全表扫描、全量排序、最终20条数据却是先取出全量数据然后筛选。

优化原理：

充分利用索引的过滤，排序特性。查询到符合条件的记录，立刻返回数据。类似“杨过轻轻一个闪身”，解决性能瓶颈。

解决方案：

结合分析的性能瓶颈可以直接把left join 转换成inner join。经过和业务A表，B表都有professional_type 字段，数据角度,业务特性上也是等价。因此可以改成 A.professional_type = '01' 这那么整个SQL的意义变成利用A表的条件过滤，排序。那么就可以在A表上面建立复合索引。同时满足这两个条件后再到B表去过滤数据。SQL改写成：

SELECT    .....
FROM  cost_e***p_pl_month  A 
Inner  JOIN cost_e***p_pl_info b  ON A.ast_id = b.ast_id   AND   A.psr_id = b.psr_id  
       and exists (select  1 from  cost_org***ping_info_pl org  where  org.full_path_id LIKE concat('%','ff8080814a616f0f014b38b062e0050e', '%')
                   and org.org_id = b.maint_group   )
WHERE   A.professional_type = '01'
  AND A.sun_year_month <= '202211' 
  AND A.sun_year_month >= '202201' 
ORDER BY  A.sun_year_month DESC  LIMIT 20 OFFSET 0

配合建立索引：

Create index  idx_cost_equip_pl_month_professional_type_sun_year_month
 on  cost_e***p_pl_month  (professional_type , sun_year_month );

SQL语意可以理解为同时利用A表数据数据过滤和排序。对比原来的SQL语句利用B表中数据过滤，然后再利用A表中数据排序。SQL的整体执行成本小得多。

优化后的执行计划：

执行成本：执行计划中出现排序。逻辑读8W，物理读1.3W，执行时间470ms

Limit  (cost=69863.16..69863.17 rows=1 width=168) (actual time=470.437..470.719 rows=20 loops=1)
  Buffers: shared hit=80738 read=13055 written=7
  I/O Timings: read=53.363 write=0.169
  ->  Sort  (cost=69863.16..69863.17 rows=1 width=168) (actual time=470.436..470.716 rows=20 loops=1)
        Sort Key: a.sun_year_month DESC
        Sort Method: top-N heapsort  Memory: 28kB
        Buffers: shared hit=80738 read=13055 written=7
        I/O Timings: read=53.363 write=0.169
        ->  Nested Loop  (cost=1307.98..69863.15 rows=1 width=168) (actual time=20.284..469.786 rows=1086 loops=1)
              Buffers: shared hit=80738 read=13055 written=7
              I/O Timings: read=53.363 write=0.169
              ->  Gather  (cost=1307.57..69862.71 rows=1 width=163) (actual time=19.906..240.637 rows=12608 loops=1)
                    Workers Planned: 2
                    Workers Launched: 0
                    Buffers: shared hit=42983 read=13048 written=7
                    I/O Timings: read=53.320 write=0.169
                    ->  Nested Loop  (cost=307.57..68862.61 rows=1 width=163) (actual time=12.108..231.021 rows=12608 loops=1)
                          Buffers: shared hit=42983 read=13048 written=7
                          I/O Timings: read=53.320 write=0.169
                          ->  Parallel Bitmap Heap Scan on cost_e***p_pl_month a  (cost=307.14..34559.83 rows=4533 width=90) (actual time=12.075..48.807 rows=12694 loops=1)
                                Recheck Cond: (((professional_type)::text = '01'::text) AND ((sun_year_month)::text <= '202211'::text) AND ((sun_year_month)::text >= '202201'::text))
                                Heap Blocks: exact=5290
                                Buffers: shared hit=1 read=5340 written=7
                                I/O Timings: read=21.127 write=0.169
                                ->  Bitmap Index Scan on idx_cost_equip_pl_month_professional_type_sun_year_month  (cost=0.00..304.42 rows=10879 width=0) (actual time=11.085..11.086 rows=12694 loops=1)
                                      Index Cond: (((professional_type)::text = '01'::text) AND ((sun_year_month)::text <= '202211'::text) AND ((sun_year_month)::text >= '202201'::text))
                                      Buffers: shared read=51
                                      I/O Timings: read=0.246
                          ->  Index Scan using cost_equip_pl_info_idx_psr_id on cost_e***p_pl_info b  (cost=0.43..7.56 rows=1 width=117) (actual time=0.013..0.013 rows=1 loops=12694)
                                Index Cond: ((psr_id)::text = (a.psr_id)::text)
                                Filter: ((a.ast_id)::text = (ast_id)::text)
                                Buffers: shared hit=42982 read=7708
                                I/O Timings: read=32.193
              ->  Index Only Scan using idx_cost_orgmapping_info_pl_org_id_full_path_id on cost_org***ping_info_pl org  (cost=0.41..0.44 rows=1 width=30) (actual time=0.018..0.018 rows=0 loops=12608)
                    Index Cond: (org_id = (b.maint_group)::text)
                    Filter: ((full_path_id)::text ~~ concat('%', 'ff8080814a616f0f014b38b062e0050e', '%'))
                    Rows Removed by Filter: 1
                    Heap Fetches: 0
                    Buffers: shared hit=37755 read=7
                    I/O Timings: read=0.043
Planning Time: 14.951 ms
Execution Time: 470.797 ms

看到此执行计划，果然符合预期。执行时间大幅度缩短到470ms。但是处于DBA的直觉此执行计划还可以继续优化。当然是需要一点优化功力的。相信各位看官看到这里估计已经累了。笔者就不继续挖掘了。直接说优化手段。

SQL再次调整：

 SELECT  /*+   nestloop(a b org)  indexscan(a)   indexscan(b)      leading(a b org)  */ .....
FROM power_sch.cost_e***p_pl_month a  
inner  JOIN power_sch.cost_e***p_pl_info b  ON A.ast_id = b.ast_id  and  A.psr_id = b.psr_id 
and exists (select  1 from  power_sch.cost_org***ping_info_pl org  where  org.full_path_id LIKE concat('%','ff8080814a616f0f014b38b062e0050e', '%') and org.org_id = b.maint_group) 
WHERE   A.professional_type = '01'   
  AND A.sun_year_month <= '202211' 
  AND A.sun_year_month >= '202201' 
ORDER BY   A.sun_year_month DESC 
  LIMIT 20  OFFSET 0;

最优执行计划：

Limit  (cost=1.27..116793.00 rows=1 width=168) (actual time=3.121..32.038 rows=20 loops=1)
  Buffers: shared hit=2248 read=1133
  I/O Timings: read=5.645
  ->  Nested Loop  (cost=1.27..116793.00 rows=1 width=168) (actual time=3.120..32.029 rows=20 loops=1)
        Buffers: shared hit=2248 read=1133
        I/O Timings: read=5.645
        ->  Nested Loop  (cost=0.86..116792.56 rows=1 width=163) (actual time=0.355..17.660 rows=418 loops=1)
              Buffers: shared hit=1070 read=1068
              I/O Timings: read=5.312
              ->  Index Scan Backward using idx_cost_equip_pl_month_professional_type_sun_year_month on cost_e***p_pl_month a  (cost=0.43..34467.41 rows=10879 width=90) (actual time=0.269..3.747 rows=449 loops=1)
                    Index Cond: (((professional_type)::text = '01'::text) AND ((sun_year_month)::text <= '202211'::text) AND ((sun_year_month)::text >= '202201'::text))
                    Buffers: shared hit=3 read=370
                    I/O Timings: read=1.903
              ->  Index Scan using cost_equip_pl_info_idx_psr_id on cost_e***p_pl_info b  (cost=0.43..7.56 rows=1 width=117) (actual time=0.029..0.029 rows=1 loops=449)
                    Index Cond: ((psr_id)::text = (a.psr_id)::text)
                    Filter: ((a.ast_id)::text = (ast_id)::text)
                    Buffers: shared hit=1067 read=698
                    I/O Timings: read=3.410
        ->  Index Only Scan using idx_cost_orgmapping_info_pl_org_id_full_path_id on cost_org***ping_info_pl org  (cost=0.41..0.44 rows=1 width=30) (actual time=0.033..0.033 rows=0 loops=418)
              Index Cond: (org_id = (b.maint_group)::text)
              Filter: ((full_path_id)::text ~~ concat('%', 'ff8080814a616f0f014b38b062e0050e', '%'))
              Rows Removed by Filter: 1
              Heap Fetches: 0
              Buffers: shared hit=1178 read=65
              I/O Timings: read=0.332
Planning Time: 12.672 ms
Execution Time: 32.124 ms

符合预期没有全表扫描，没有排序。执行成本逻辑读2千，物理读1千执行时间32ms

总结：

	瓶颈	成本
原SQL	全表扫描，全量排序	19S/次
第一次优化	无全表扫描，有全量排序	470ms/次
第二次优化	无全表扫描,无全量排序	32ms/次

结束语：

这篇文章创作实在不易，案例分析，原理运用，思路递进，尤其在符合预期中再次扣出可以精进的地方，实属难得，更难得的是又再次超出预期，写一个结束语纪念一下吧。用论语中一句话结尾吧。论语：知之为知之，不知为不知。我是不是可以这样“探索”，知之，为，知之！，不知为，不知。可以这样解读：掌握一门知识，并且赋予实践。然后在实践中再总结知识是明智的。不了解或者不掌握一门知识，直接干，是不明智的！

文章转载自godba，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL优化案例-运用原理优化执行计划

优化原理：

评论