ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 国内数据库 > 循序渐进丨MogDB 数据库查询重写规则lazyagg详解

循序渐进丨MogDB 数据库查询重写规则lazyagg详解

原创 国内数据库 作者：云和恩墨 时间：2024-08-12 14:32:17 0 删除 编辑

问题概述

在 MogDB 和 openGauss 中，参数rewrite_rule用于控制查询重写，本文介绍查询重写规则lazyagg。
在未设置rewrite_rule=lazyagg的情况下，子查询中有GROUP BY会先进行GROUP BY；
lazyagg表示延迟聚合运算，目的是消除子查询中的聚合运算，先关联再GROUP BY；
当子查询中有GROUP BY，子查询中的表很大，子查询与外面的表(比较小/过滤完之后数据量少)进行关联之后还有GROUP BY，这个时候就可以开启lazyagg特性，加快SQL性能。

参考示例

现在有如下例子：
    orcl=> explain analyze select *+ set(rewrite_rule none) */ t1.object_id, sum(total)

    
orcl->   from test02 t1,

    
orcl->        (select object_id, sum(data_object_id) as total from test01 group by object_id) t2

    
orcl->  where t1.object_id = t2.object_id and t1.owner='SCOTT'

    
orcl->  group by t1.object_id;

    
                                                                    QUERY PLAN                                                                    

    
--------------------------------------------------------------------------------------------------------------------------------------------------

    
 HashAggregate  (cost=1452817.48..1452817.80 rows=32 width=70) (actual time=19813.801..19813.804 rows=36 loops=1)

    
   Group By Key: t1.object_id

    
   ->  Hash Join  (cost=1450644.14..1452748.21 rows=13854 width=38) (actual time=19786.470..19813.740 rows=36 loops=1)

    
         Hash Cond: (test01.object_id = t1.object_id)

    
         ->  HashAggregate  (cost=1450525.16..1451391.03 rows=86587 width=44) (actual time=19785.539..19802.382 rows=86987 loops=1)

    
               Group By Key: test01.object_id

    
               ->  Seq Scan on test01  (cost=0.00..1227838.44 rows=44537344 width=12) (actual time=0.006..5913.694 rows=44537344 loops=1)

    
         ->  Hash  (cost=118.58..118.58 rows=32 width=6) (actual time=0.140..0.140 rows=36 loops=1)

    
                Buckets: 32768  Batches: 1  Memory Usage: 258kB

    
               ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.036..0.128 rows=36 loops=1)

    
                     Index Cond: ((owner)::text = 'SCOTT'::text)

    
 Total runtime: 19814.139 ms

    
(12 rows)
    /*+ set(rewrite_rule none) */表示禁止所有查询重写规则。从执行计划中看到，子查询先进行了GROUP BY，再与test02进行关联，整个SQL GROUP BY了2次。
    现在设置rewrite_rule=lazyagg，我们再来看一下执行计划：
      orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.object_id, sum(total)

      
orcl->   from test02 t1,

      
orcl->        (select object_id, sum(data_object_id) as total from test01 group by object_id) t2

      
orcl->  where t1.object_id = t2.object_id and t1.owner='SCOTT'

      
orcl->  group by t1.object_id;

      
                                                                   QUERY PLAN                                                                   

      
------------------------------------------------------------------------------------------------------------------------------------------------

      
 HashAggregate  (cost=64868.16..64868.48 rows=32 width=44) (actual time=45.018..45.023 rows=36 loops=1)

      
   Group By Key: t1.object_id

      
   ->  Nested Loop  (cost=12.23..64785.86 rows=16460 width=12) (actual time=0.150..38.695 rows=18432 loops=1)

      
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.017..0.060 rows=36 loops=1)

      
               Index Cond: ((owner)::text = 'SCOTT'::text)

      
         ->  Bitmap Heap Scan on test01  (cost=12.23..2015.71 rows=514 width=12) (actual time=3.912..36.585 rows=18432 loops=36)

      
               Recheck Cond: (object_id = t1.object_id)

      
               Heap Blocks: exact=18432

      
               ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.11 rows=514 width=0) (actual time=2.304..2.304 rows=18432 loops=36)

      
                     Index Cond: (object_id = t1.object_id)

      
 Total runtime: 45.229 ms

      
(11 rows)
      从执行计划中看到，子查询中的聚合运算被消除了，子查询中的表test01与test02先做了关联，关联之后再进行GROUP BY，整个SQL只做了1次GROUP BY。

      注意事项

      想要lazyagg查询改写规则生效，必须满足两个条件：

      1. 子查询中有GROUP BY

      2. 子查询与外面的表关联之后还有GROUP BY

      如果子查询与外面的表关联之后没有GROUP BY，lazyagg查询改写规则不会生效，这个时候请使用谓词推入。
      比如下面SQL lazyagg就不会生效，因为子查询与外面的表关联之后没有GROUP BY：
        orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.object_id, total

        
  from test02 t1,

        
       (select object_id, sum(data_object_id) as total from test01 group by object_id) t2

        
 where t1.object_id = t2.object_id and t1.owner='SCOTT';

        
                                                                 QUERY PLAN                                                                 

        
--------------------------------------------------------------------------------------------------------------------------------------------

        
 Hash Join  (cost=1450642.43..1452765.96 rows=13982 width=38) (actual time=19008.136..19038.606 rows=36 loops=1)

        
   Hash Cond: (test01.object_id = t1.object_id)

        
   ->  HashAggregate  (cost=1450523.45..1451397.33 rows=87388 width=44) (actual time=19007.086..19026.905 rows=86987 loops=1)

        
         Group By Key: test01.object_id

        
         ->  Seq Scan on test01  (cost=0.00..1227837.30 rows=44537230 width=12) (actual time=0.004..5699.204 rows=44537344 loops=1)

        
   ->  Hash  (cost=118.58..118.58 rows=32 width=6) (actual time=0.123..0.123 rows=36 loops=1)

        
          Buckets: 32768  Batches: 1  Memory Usage: 258kB

        
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.048..0.110 rows=36 loops=1)

        
               Index Cond: ((owner)::text = 'SCOTT'::text)

        
 Total runtime: 19038.900 ms

        
(10 rows)
        设置rewrite_rule=predpushnormal：
          orcl=> explain analyze select /*+ set(rewrite_rule predpushnormal) */ t1.object_id, total

          
  from test02 t1,

          
       (select object_id, sum(data_object_id) as total from test01 group by object_id) t2

          
 where t1.object_id = t2.object_id and t1.owner='SCOTT';

          
                                                                   QUERY PLAN                                                                   

          
------------------------------------------------------------------------------------------------------------------------------------------------

          
 Nested Loop  (cost=12.20..64826.62 rows=2 width=38) (actual time=1.542..36.819 rows=36 loops=1)

          
   ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=6) (actual time=0.028..0.094 rows=36 loops=1)

          
         Index Cond: ((owner)::text = 'SCOTT'::text)

          
   ->  GroupAggregate  (cost=12.20..2022.09 rows=2 width=44) (actual time=36.683..36.683 rows=36 loops=36)

          
         Group By Key: test01.object_id

          
         ->  Bitmap Heap Scan on test01  (cost=12.20..2019.52 rows=510 width=12) (actual time=4.016..31.464 rows=18432 loops=36)

          
               Recheck Cond: (t1.object_id = object_id)

          
               Heap Blocks: exact=18432

          
               ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=510 width=0) (actual time=2.369..2.369 rows=18432 loops=36)

          
                     Index Cond: (t1.object_id = object_id)

          
 Total runtime: 37.015 ms

          
(11 rows)
          子查询中有union all可以生效：
            orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.owner, sum(total)

            
orcl->  from test02 t1,

            
orcl->       (select object_id, sum(data_object_id) as total from test01 group by object_id

            
orcl(>        union all

            
orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_id

            
orcl(>       ) t2

            
orcl-> where t1.object_id = t2.object_id and t1.owner='SCOTT'

            
orcl-> group by t1.owner; 

            
                                                                      QUERY PLAN                                                                      

            
------------------------------------------------------------------------------------------------------------------------------------------------------

            
 GroupAggregate  (cost=12.20..100248.99 rows=1 width=43) (actual time=41.682..41.682 rows=1 loops=1)

            
   Group By Key: t1.owner

            
   ->  Nested Loop  (cost=12.20..64549.61 rows=7139875 width=11) (actual time=0.156..37.395 rows=18468 loops=1)

            
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=11) (actual time=0.019..0.062 rows=36 loops=1)

            
               Index Cond: ((owner)::text = 'SCOTT'::text)

            
         ->  Append  (cost=12.20..2008.36 rows=511 width=12) (actual time=4.092..35.783 rows=18468 loops=36)

            
               ->  Bitmap Heap Scan on test01  (cost=12.20..2000.09 rows=510 width=12) (actual time=4.022..34.234 rows=18432 loops=36)

            
                     Recheck Cond: (object_id = t1.object_id)

            
                     Heap Blocks: exact=18432

            
                     ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=510 width=0) (actual time=2.411..2.411 rows=18432 loops=36)

            
                           Index Cond: (object_id = t1.object_id)

            
               ->  Index Scan using idx_test03_objectid on test03  (cost=0.00..8.27 rows=1 width=12) (actual time=0.162..0.169 rows=36 loops=36)

            
                     Index Cond: (object_id = t1.object_id)

            
 Total runtime: 41.905 ms

            
(14 rows)
            子查询中有union无法生效：
              orcl=> explain analyze select /*+ set(rewrite_rule lazyagg) */ t1.owner, sum(total)

              
orcl->  from test02 t1,

              
orcl->       (select object_id, sum(data_object_id) as total from test01 group by object_id

              
orcl(>        union 

              
orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_id

              
orcl(>       ) t2

              
orcl-> where t1.object_id = t2.object_id and t1.owner='SCOTT'

              
orcl-> group by t1.owner; 

              
                                                                      QUERY PLAN                                                                      

              
------------------------------------------------------------------------------------------------------------------------------------------------------

              
 GroupAggregate  (cost=1457835.61..1462212.44 rows=1 width=69) (actual time=19242.592..19242.592 rows=1 loops=1)

              
   Group By Key: t1.owner

              
   ->  Hash Join  (cost=1457835.61..1462072.93 rows=27900 width=37) (actual time=19219.921..19242.559 rows=67 loops=1)

              
         Hash Cond: (test01.object_id = t1.object_id)

              
         ->  HashAggregate  (cost=1457716.63..1459460.38 rows=174375 width=44) (actual time=19218.659..19231.297 rows=96155 loops=1)

              
               Group By Key: test01.object_id, (sum(test01.data_object_id))

              
               ->  Append  (cost=1450523.45..1456844.75 rows=174375 width=44) (actual time=19077.681..19160.701 rows=173974 loops=1)

              
                     ->  HashAggregate  (cost=1450523.45..1451397.33 rows=87388 width=44) (actual time=19077.681..19095.367 rows=86987 loops=1)

              
                           Group By Key: test01.object_id

              
                           ->  Seq Scan on test01  (cost=0.00..1227837.30 rows=44537230 width=12) (actual time=0.006..5869.211 rows=44537344 loops=1)

              
                     ->  HashAggregate  (cost=2833.81..3703.68 rows=86987 width=44) (actual time=35.819..52.104 rows=86987 loops=1)

              
                           Group By Key: test03.object_id

              
                           ->  Seq Scan on test03  (cost=0.00..2398.87 rows=86987 width=12) (actual time=0.011..11.127 rows=86987 loops=1)

              
         ->  Hash  (cost=118.58..118.58 rows=32 width=11) (actual time=0.075..0.075 rows=36 loops=1)

              
                Buckets: 32768  Batches: 1  Memory Usage: 258kB

              
               ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=11) (actual time=0.019..0.066 rows=36 loops=1)

              
                     Index Cond: ((owner)::text = 'SCOTT'::text)

              
 Total runtime: 19242.893 ms

              
(18 rows)
              这个时候还是用谓词推入：
                orcl=> explain analyze select /*+ set(rewrite_rule predpushnormal) */ t1.owner, sum(total)

                
orcl->  from test02 t1,

                
orcl->       (select object_id, sum(data_object_id) as total from test01 group by object_id

                
orcl(>        union 

                
orcl(>        select object_id, sum(data_object_id) as total from test03 group by object_id

                
orcl(>       ) t2

                
orcl-> where t1.object_id = t2.object_id and t1.owner='SCOTT'

                
orcl-> group by t1.owner; 

                
                                                                            QUERY PLAN                                                                            

                
------------------------------------------------------------------------------------------------------------------------------------------------------------------

                
 GroupAggregate  (cost=2030.41..65094.73 rows=1 width=69) (actual time=38.381..38.381 rows=1 loops=1)

                
   Group By Key: t1.owner

                
   ->  Nested Loop  (cost=2030.41..65094.71 rows=3 width=37) (actual time=1.710..38.356 rows=67 loops=1)

                
         ->  Index Scan using idx_test02_owner on test02 t1  (cost=0.00..118.58 rows=32 width=11) (actual time=0.021..0.065 rows=36 loops=1)

                
               Index Cond: ((owner)::text = 'SCOTT'::text)

                
         ->  HashAggregate  (cost=2030.41..2030.44 rows=3 width=44) (actual time=38.200..38.205 rows=67 loops=36)

                
               Group By Key: test01.object_id, (sum(test01.data_object_id))

                
               ->  Append  (cost=12.20..2030.40 rows=3 width=44) (actual time=37.877..38.108 rows=72 loops=36)

                
                     ->  GroupAggregate  (cost=12.20..2022.09 rows=2 width=44) (actual time=37.856..37.857 rows=36 loops=36)

                
                           Group By Key: test01.object_id

                
                           ->  Bitmap Heap Scan on test01  (cost=12.20..2019.52 rows=510 width=12) (actual time=3.786..32.509 rows=18432 loops=36)

                
                                 Recheck Cond: (t1.object_id = object_id)

                
                                 Heap Blocks: exact=18432

                
                                 ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=510 width=0) (actual time=2.208..2.208 rows=18432 loops=36)

                
                                       Index Cond: (t1.object_id = object_id)

                
                     ->  GroupAggregate  (cost=0.00..8.28 rows=1 width=44) (actual time=0.203..0.203 rows=36 loops=36)

                
                           Group By Key: test03.object_id

                
                           ->  Index Scan using idx_test03_objectid on test03  (cost=0.00..8.27 rows=1 width=12) (actual time=0.159..0.166 rows=36 loops=36)

                
                                 Index Cond: (t1.object_id = object_id)

                
 Total runtime: 38.676 ms

                
(20 rows)


                关于作者

                罗炳森，云和恩墨数据库架构师，15年SQL优化&数据库性能优化经验，擅长超大型，超复杂SQL&存储过程优化，国内首位专注于SQL优化实战案例博客作者；5年+ 数据库/ETL/BI/大数据培训经验，累计为中国培养了近1000多名DBA，2000多名ETL/BI/大数据人才；2015年出版《Oracle查询优化改写技巧与案例》，累计销量达6万余册，2018年出版《SQL优化核心思想》，被翻译为繁体字在台湾出版。


                来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29715045/viewspace-3024778/ ，如需转载，请注明出处，否则将追究法律责任。

                0
                0
                上一篇： 循序渐进丨MogDB 数据库带级联从库的集群切换后如何保持原有架构？
                下一篇： 循序渐进丨Oracle 通过透明网关以dblink形式连接 MogDB 的方法
                请登录后发表评论 登录
                全部评论
                云和恩墨

                注册时间：2014-06-19

                • 博文量
                  183
                • 访问量
                  346702

                最新文章

                支持我们 作者招募 用户协议 FAQ Contact Us

                北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

                广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

                违法和不良信息举报电话:18101376593