首页 > 数据库 >SQL优化案例4（肇庆某项目）

SQL优化案例4（肇庆某项目）

时间：2022-11-17 10:25:59浏览次数：42  
标签：rows .. 案例 time B01 肇庆 cost SQL loops

泽就找我优化条SQL，反馈执行时间很慢需要 1分钟才能出结果，然后安排！

-- 原SQL

select hiber.dmcod                                                                                  dmcod,
             b01.b0104                                                                                    dmcpt,
             ZB02.ISCANUSE                                                                                ISCANUSE,
             ZB02.DMABR1                                                                                  DMABR1,
             ZB02.DMABR2                                                                                  DMABR2,
             ZB02.ATTRIBUTE                                                                               "ATTRIBUTE",
             ZB02.YESPRV                                                                                  YESPRV,
             ZB02.DMHND                                                                                   DMHND,
             hiber.inpfrq                                                                                 inpfrq,
             decode(hiber.dmparentcod, '00000000-0000-0000-0000-000000000000', 'ROOT', hiber.dmparentcod) DMPARENTCOD
      from b01
               inner join b01_group1_hiber hiber on b01.b00 = hiber.dmcod and hiber.DMPARENTLEV = 1
               left join zb02 on zb02.dmcod = b01.b00
      where b01.b00 in (SELECT DISTINCT B01_NODE_R.B00
                        FROM B01_NODE_R
                                 INNER JOIN B01
                                            ON B01.B00 = B01_NODE_R.B00

                        WHERE B01_NODE_R.NODEID IN (Select nodeid
                                                    From s_treenode
                                                    Start With nodeid = '43D14FB8-59A1-4220-8295-10CF1EB97768'
                                                    Connect By Prior nodeid = parentid)

                        UNION
                        SELECT DISTINCT B01_NODE_R.B00
                        FROM B01_NODE_R
                                 INNER JOIN B01
                                            ON B01.B00 = B01_NODE_R.B00
                        WHERE B01_NODE_R.NODEID IN (Select nodeid
                                                    From S_TREENODE
                                                    Start With nodeid = '43D14FB8-59A1-4220-8295-10CF1EB97768'
                                                    Connect By Prior PARENTID = nodeid))


        and exists(
              select b00
              from b01
              where hiber.DMPARENTLEV = 1
                and (b00 = hiber.dmparentcod or hiber.dmparentcod='00000000-0000-0000-0000-000000000000')
          )
        and hiber.dmcod not in (SELECT dmcod
                                from B01_GROUP1_HIBER
                                where dmcod = '48A15C83-6E8F-463B-B34E-F1E5E5E58DE6'
                                   or dmparentcod = '48A15C83-6E8F-463B-B34E-F1E5E5E58DE6')
        and (zb02.dmcod like '%%' or zb02.dmcpt like '%%' or UPPER(zb02.dmhnd) like UPPER('%%'))
      order by hiber.inpfrq;



-- 执行计划

Sort  (cost=21556.94..21561.27 rows=1730 width=189) (actual time=58852.503..58858.962 rows=6735 loops=1)
  Sort Key: hiber.INPFRQ
  Sort Method: quicksort  Memory: 2114kB
  ->  Nested Loop Semi Join  (cost=4347.38..21463.90 rows=1730 width=189) (actual time=254.165..58825.665 rows=6735 loops=1)
        Join Filter: ((B01_1.B00 = hiber.DMPARENTCOD) OR (hiber.DMPARENTCOD = '00000000-0000-0000-0000-000000000000'::CHARACTER VARYING))
        Rows Removed by Join Filter: 24050830
        ->  Hash Join  (cost=4347.10..5018.28 rows=1730 width=194) (actual time=243.879..283.970 rows=6771 loops=1)
              Hash Cond: (B01.B00 = hiber.DMCOD)
              ->  Seq Scan on B01  (cost=0.00..628.19 rows=6919 width=56) (actual time=0.016..10.741 rows=6919 loops=1)
              ->  Hash  (cost=4325.36..4325.36 rows=1739 width=302) (actual time=243.834..243.834 rows=6771 loops=1)
                    Buckets: 8192 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 1930kB
                    ->  Hash Join  (cost=3696.43..4325.36 rows=1739 width=302) (actual time=202.577..232.219 rows=6771 loops=1)
                          Hash Cond: (ZB02.DMCOD = hiber.DMCOD)
                          ->  Seq Scan on ZB02  (cost=0.00..585.47 rows=6954 width=121) (actual time=0.015..10.009 rows=6954 loops=1)
                                Filter: (((DMCOD)::TEXT ~~ '%%'::TEXT) OR ((DMCPT)::TEXT ~~ '%%'::TEXT) OR (((UPPER((DMHND)::TEXT))::CHARACTER VARYING(8000 byte))::TEXT ~~ '%%'::TEXT))
                          ->  Hash  (cost=3674.38..3674.38 rows=1764 width=181) (actual time=202.542..202.543 rows=6797 loops=1)
                                Buckets: 8192 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 1131kB
                                ->  Hash Join  (cost=3241.92..3674.38 rows=1764 width=181) (actual time=166.538..192.660 rows=6797 loops=1)
                                      Hash Cond: (B01_NODE_R.B00 = hiber.DMCOD)
                                      ->  HashAggregate  (cost=2668.70..2807.08 rows=13838 width=90) (actual time=142.403..148.832 rows=6795 loops=1)
                                            Group Key: B01_NODE_R.B00
                                            ->  Append  (cost=1196.65..2634.10 rows=13838 width=90) (actual time=49.062..133.025 rows=6809 loops=1)
                                                  ->  HashAggregate  (cost=1196.65..1265.84 rows=6919 width=37) (actual time=49.059..49.084 rows=14 loops=1)
                                                        Group Key: B01_NODE_R.B00
                                                        ->  Hash Semi Join  (cost=481.30..1179.35 rows=6919 width=37) (actual time=20.020..49.030 rows=14 loops=1)
                                                              Hash Cond: (B01_NODE_R.NODEID = S_TREENODE.NODEID)
                                                              ->  Hash Join  (cost=353.51..956.44 rows=6919 width=74) (actual time=16.008..41.364 rows=6898 loops=1)
                                                                    Hash Cond: (B01_2.B00 = B01_NODE_R.B00)
                                                                    ->  Index Only Scan using SYS_C0065993 on B01 B01_2  (cost=0.28..508.07 rows=6919 width=37) (actual time=0.009..6.547 rows=6919 loops=1)
                                                                          Heap Fetches: 0
                                                                    ->  Hash  (cost=264.77..264.77 rows=7077 width=74) (actual time=15.955..15.955 rows=7077 loops=1)
                                                                          Buckets: 8192  Batches: 1  Memory Usage: 797kB
                                                                          ->  Seq Scan on B01_NODE_R  (cost=0.00..264.77 rows=7077 width=74) (actual time=0.005..6.737 rows=7077 loops=1)
                                                              ->  Hash  (cost=119.02..119.02 rows=701 width=37) (actual time=0.345..0.346 rows=1 loops=1)
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                    ->  Recursive Union  (cost=0.27..112.01 rows=701 width=37) (actual time=0.338..0.341 rows=1 loops=1)
                                                                          ->  Index Scan using SYS_C0072413 on S_TREENODE  (cost=0.27..8.29 rows=1 width=74) (actual time=0.015..0.017 rows=1 loops=1)
                                                                                Index Cond: (NODEID = '43D14FB8-59A1-4220-8295-10CF1EB97768'::CHARACTER VARYING)
                                                                          ->  Hash Join  (cost=0.03..9.67 rows=70 width=74) (actual time=0.310..0.310 rows=0 loops=1)
                                                                                Hash Cond: (S_TREENODE.PARENTID = (PRIOR S_TREENODE.NODEID))
                                                                                ->  Seq Scan on S_TREENODE  (cost=0.00..8.41 rows=141 width=74) (actual time=0.010..0.135 rows=141 loops=1)
                                                                                ->  Hash  (cost=0.02..0.02 rows=1 width=40) (actual time=0.009..0.009 rows=1 loops=1)
                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                                      ->  WorkTable Scan on "connect"  (cost=0.00..0.02 rows=1 width=40) (actual time=0.002..0.004 rows=1 loops=1)
                                                  ->  HashAggregate  (cost=1160.69..1229.88 rows=6919 width=37) (actual time=66.348..72.722 rows=6795 loops=1)
                                                        Group Key: B01_NODE_R_1.B00
                                                        ->  Hash Semi Join  (cost=445.33..1143.39 rows=6919 width=37) (actual time=16.122..57.147 rows=6795 loops=1)
                                                              Hash Cond: (B01_NODE_R_1.NODEID = S_TREENODE_1.NODEID)
                                                              ->  Hash Join  (cost=353.51..956.44 rows=6919 width=74) (actual time=15.988..41.372 rows=6898 loops=1)
                                                                    Hash Cond: (B01_3.B00 = B01_NODE_R_1.B00)
                                                                    ->  Index Only Scan using SYS_C0065993 on B01 B01_3  (cost=0.28..508.07 rows=6919 width=37) (actual time=0.008..6.553 rows=6919 loops=1)
                                                                          Heap Fetches: 0
                                                                    ->  Hash  (cost=264.77..264.77 rows=7077 width=74) (actual time=15.932..15.932 rows=7077 loops=1)
                                                                          Buckets: 8192  Batches: 1  Memory Usage: 797kB
                                                                          ->  Seq Scan on B01_NODE_R B01_NODE_R_1  (cost=0.00..264.77 rows=7077 width=74) (actual time=0.009..6.653 rows=7077 loops=1)
                                                              ->  Hash  (cost=91.68..91.68 rows=11 width=37) (actual time=0.077..0.078 rows=2 loops=1)
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                    ->  Recursive Union  (cost=0.27..91.57 rows=11 width=37) (actual time=0.049..0.070 rows=2 loops=1)
                                                                          ->  Index Scan using SYS_C0072413 on S_TREENODE S_TREENODE_1  (cost=0.27..8.29 rows=1 width=74) (actual time=0.011..0.013 rows=1 loops=1)
                                                                                Index Cond: (NODEID = '43D14FB8-59A1-4220-8295-10CF1EB97768'::CHARACTER VARYING)
                                                                          ->  Nested Loop  (cost=0.27..8.32 rows=1 width=74) (actual time=0.014..0.016 rows=1 loops=2)
                                                                                ->  WorkTable Scan on connect_1  (cost=0.00..0.02 rows=1 width=40) (actual time=0.002..0.003 rows=1 loops=2)
                                                                                ->  Index Scan using SYS_C0072413 on S_TREENODE S_TREENODE_1  (cost=0.27..8.29 rows=1 width=74) (actual time=0.007..0.008 rows=1 loops=2)
                                                                                      Index Cond: (NODEID = (PRIOR S_TREENODE_1.PARENTID))
                                      ->  Hash  (cost=529.11..529.11 rows=3529 width=91) (actual time=24.106..24.107 rows=7058 loops=1)
                                            Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 917kB
                                            ->  Seq Scan on B01_GROUP1_HIBER hiber  (cost=264.56..529.11 rows=3529 width=91) (actual time=6.792..14.479 rows=7058 loops=1)
                                                  Filter: ((NOT (hashed SubPlan 1)) AND (DMPARENTLEV = '1'::NUMERIC))
                                                  Rows Removed by Filter: 45
                                                  SubPlan 1
                                                    ->  Seq Scan on B01_GROUP1_HIBER  (cost=0.00..264.55 rows=7 width=37) (actual time=6.775..6.776 rows=0 loops=1)
                                                          Filter: ((DMCOD = '48A15C83-6E8F-463B-B34E-F1E5E5E58DE6'::CHARACTER VARYING) OR (DMPARENTCOD = '48A15C83-6E8F-463B-B34E-F1E5E5E58DE6'::CHARACTER VARYING))
                                                          Rows Removed by Filter: 7103
        ->  Materialize  (cost=0.28..542.66 rows=6919 width=37) (actual time=0.001..3.011 rows=3553 loops=6771)
              ->  Index Only Scan using SYS_C0065993 on B01 B01_1  (cost=0.28..508.07 rows=6919 width=37) (actual time=0.015..6.860 rows=6919 loops=1)
                    Heap Fetches: 0
Planning time: 3.423 ms
Execution time: 58921.213 ms



  ->  Nested Loop Semi Join  (cost=4347.38..21463.90 rows=1730 width=189) (actual time=254.165..58825.665 rows=6735 loops=1)
        Join Filter: ((B01_1.B00 = hiber.DMPARENTCOD) OR (hiber.DMPARENTCOD = '00000000-0000-0000-0000-000000000000'::CHARACTER VARYING))

可以看到，主要是这块（Nested Loop Semi Join）的节点导致影响到整条SQL缓慢，真实返回的时间是在 58825.665 毫秒 SQL如下：

exists(
              select b00
              from b01
              where hiber.DMPARENTLEV = 1
                and (b00 = hiber.dmparentcod or hiber.dmparentcod='00000000-0000-0000-0000-000000000000')
          )


这里 b01 表和外部 b01_group1_hiber hiber 表关联查询走了两个条件，是 or 的关系，由于 or 是不走索引的，所以执行计划只能走嵌套循环 + 全表扫描。

尝试将 or hiber.dmparentcod='00000000-0000-0000-0000-000000000000' 这个关联条件去掉，整条语句使用 380ms 就能出结果。


我们可以将SQL进行等价改写来进行优化：

with x as (select hiber.dmcod               dmcod,
                  b01.b0104                 dmcpt,
                  ZB02.ISCANUSE             ISCANUSE,
                  ZB02.DMABR1               DMABR1,
                  ZB02.DMABR2               DMABR2,
                  ZB02.ATTRIBUTE            "ATTRIBUTE",
                  ZB02.YESPRV               YESPRV,
                  ZB02.DMHND                DMHND,
                  hiber.inpfrq              inpfrq,
                  decode(hiber.dmparentcod, '00000000-0000-0000-0000-000000000000', 'ROOT',
                         hiber.dmparentcod) DMPARENTCOD
           from b01
                    inner join b01_group1_hiber hiber on b01.b00 = hiber.dmcod and hiber.DMPARENTLEV = 1
                    left join zb02 on zb02.dmcod = b01.b00
           where b01.b00 in (SELECT DISTINCT B01_NODE_R.B00
                             FROM B01_NODE_R
                                      INNER JOIN B01
                                                 ON B01.B00 = B01_NODE_R.B00

                             WHERE B01_NODE_R.NODEID IN (Select nodeid
                                                         From s_treenode
                                                         Start With nodeid = '43D14FB8-59A1-4220-8295-10CF1EB97768'
                                                         Connect By Prior nodeid = parentid)
                             UNION
                             SELECT DISTINCT B01_NODE_R.B00
                             FROM B01_NODE_R
                                      INNER JOIN B01
                                                 ON B01.B00 = B01_NODE_R.B00
                             WHERE B01_NODE_R.NODEID IN (Select nodeid
                                                         From S_TREENODE
                                                         Start With nodeid = '43D14FB8-59A1-4220-8295-10CF1EB97768'
                                                         Connect By Prior PARENTID = nodeid))
             and hiber.dmcod not in (SELECT dmcod
                                     from B01_GROUP1_HIBER
                                     where dmcod = '48A15C83-6E8F-463B-B34E-F1E5E5E58DE6'
                                        or dmparentcod = '48A15C83-6E8F-463B-B34E-F1E5E5E58DE6')
             and (zb02.dmcod like '%%' or zb02.dmcpt like '%%' or UPPER(zb02.dmhnd) like UPPER('%%')))

select x1.dmcod,
       x1.dmcpt,
       x1.ISCANUSE,
       x1.DMABR1,
       x1.DMABR2,
       x1.ATTRIBUTE,
       x1.YESPRV,
       x1.DMHND,
       x1.inpfrq,
       x1.DMPARENTCOD
from (select *
      from x
               left join (select b00 from b01 where hiber.DMPARENTLEV = 1) b on (b.b00 = x.dmparentcod)
      UNION
      /* or 的关系大部分情况下 只能通过 union 来进行 改写，某些情况下 使用 union all 也是等价， 但是一定要注意返回的数据 几比几的关系，避免出现交集导致数据重复  */
      select *
      from x
               left join (select b00 from b01 where hiber.DMPARENTLEV = 1) b
                         on (x.dmparentcod = '00000000-0000-0000-0000-000000000000')) x1
where x1.b00 is not null
group by /* 变回 1 的关系*/
    x1.dmcod,
    x1.dmcpt,
    x1.ISCANUSE,
    x1.DMABR1,
    x1.DMABR2,
    x1.ATTRIBUTE,
    x1.YESPRV,
    x1.DMHND,
    x1.inpfrq,
    x1.DMPARENTCOD
order by x1.inpfrq;

遗憾的是本案例除了SQL改写并无其他优化方案，加索引也是不好使的，客户应用系统已经上线无法进行SQL改写，还是希望kingbase 数据库未来能出一个类似于ORACLE sql_profile 的功能（SQL不变的情况下修改执行计划），能通过更多方案来调优生产环境的慢SQL。

标签：rows,..,案例,time,B01,肇庆,cost,SQL,loops
From： https://www.cnblogs.com/yuzhijian/p/16898512.html

相关文章

  • MySQL主从原理
    MySQL主从又叫Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据是实时同步的MySQL主从是基于binlog的，主上需开启binlo......
  • SQL改写案例1
    一开发哥们找我改写SQL，他写的逻辑始终不对，安排！--他写的SQL：#order_id是主键withaas(selectstr_to_date(regist_time,'%Y-%m-%d')asregist_timefrominno_busi_info......
  • Linux内存泄露案例分析和内存管理分享
    作者：李遵举一、问题近期我们运维同事接到线上LB（负载均衡）服务内存报警，运维同事反馈说LB集群有部分机器的内存使用率超过80%，有的甚至超过90%，而且内存使用率还再不停的增长......
  • SQL优化案例2（白云区短信项目）
    京华开发一哥们找我优化条报表SQL，反馈执行时间很慢需要18s才能出结果，安排。#原SQL    SELECT    2ASTYPE,    to_char(a."create_time",'yyy......
  • SQL Server 高可用（always on）配置指南之数据库侦听器及高可用
    1.简介1、参考SQLServer高可用（alwayson）配置指南之域(AD)环境搭建  ​​https://blog.51cto.com/waringid/5851856​​完成域控服务器（DomainControl，以下简称DC）2、......
  • mysql的锁机制-MyISAM表锁
    1、MySQL锁的基本介绍锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资......
  • Babelfish for PostgreSQL
      BabelfishforPostgreSQL开源已快一月，不过全网还没有实践者总结。今天我们就测试看看，Babelfish到底是如何部署与使用的！ BabelfishforPostgreSQL介绍我们先回......
  • MySQL8.0.26-Linux版安装
    MySQL8.0.26-Linux版安装1.准备一台Linux服务器云服务器或者虚拟机都可以;Linux的版本为CentOS7;2.下载Linux版MySQL安装包https://downloads.mysql.com/archives/......
  • 大数据Hadoop之——EFAK和Confluent KSQL简单使用（kafka listeners 和 advertised.list
    目录一、EFAK概述和安装二、listeners和advertised.listeners配置详解三、KSQL使用1）KSQL架构2）Confluent安装（ZK/KAFKA/KSQL）1、下载confluent2、配置环境变量3、创建log和dat......
  • MySQL 创建存储过程，使用 while 批量插入数据
    1、创建带字段的表student(id,name,age)#id整型主建不能为空自动增长#name字符型长度50不能为空#age整型不能为空默认值18mysql>createtablestudent(->i......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99