V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
poupoo
V2EX  ›  问与答

求 DBA 带飞, sql 优化问题,左链接字句太多,加上分析排序,效率很慢

  •  
  •   poupoo · 2018-01-16 09:48:58 +08:00 · 2489 次点击
    这是一个创建于 2263 天前的主题,其中的信息可能已经有所发展或是发生改变。
    select u.final_ng_cnt,
    v.color_cnt,
    v.color_total,
    y.weight_total,
    y.weight_pass_cnt,
    z.residual_total,
    z.residual_total - z.last_ng_cnt as residual_pass_cnt,
    p.package_total,
    p.package_pass_cnt
    from (select 'aaa' as batch_no,
    sum(case
    when t.result = 0 then
    1
    else
    0
    end) final_ng_cnt,
    count(distinct t.code) as final_total
    from (select a.item,
    a.code,
    a.result,
    a.datetime,
    a. recordid,
    dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000') desc) as final_flag
    from fruit_table_a a
    inner join fruit_table_b b on a. recordid =
    b.productid
    where instr(a.code, '000000000000') = 0
    and instr(a.code, '9999999') = 0
    and instr(nvl(b.batch_no, '@@@'), '@@@') = 0
    and b.batch_no = 'aaa') t
    where t.final_flag = 1) u
    left join (select 'aaa' as batch_no,
    sum(case
    when c.first_rs = 1 then
    1
    else
    0
    end) color_cnt,
    count(c.code) as color_total

    from (select b.batch_no,
    a.item,
    a.code,
    a.result as first_rs,
    a.datetime,
    dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '00000000')) as first_flag
    from fruit_table_a a
    inner join fruit_table_b b on a. recordid =
    b.productid


    where instr(a.code, '000000000000') = 0
    and instr(a.code, '9999999') = 0
    and instr(a.item, '0019') > 0
    and b.batch_no = 'aaa') c
    where c.first_flag = 1
    ) v on u.batch_no = v.batch_no
    left join (select 'aaa' as batch_no,
    sum(case
    when instr(g.result, '0') = 0 then
    1
    else
    0
    end) weight_pass_cnt,
    count(g.code) as weight_total
    from (select k.code,
    wm_concat(k.item) rank,
    wm_concat(k.result) as result
    from (select distinct c. recordid,
    c.batch_no,
    c.line_num,
    c.station_id,
    c.thread_id,
    c.item,
    c.code,
    c.result,
    c.datetime,
    c.first_flag
    from (select a. recordid,
    b.batch_no,
    a.line_num,
    a.station_id,
    a.thread_id,
    a.item,
    a.code,
    a.result,
    a.datetime,
    dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag
    from fruit_table_a a
    inner join fruit_table_b b on a. recordid =
    b.productid

    where instr(a.code, '000000000000') = 0
    and instr(a.code, '9999999') = 0
    and instr(a.item, '0823') > 0
    and b.batch_no = 'aaa'
    union all
    select a. recordid,
    b.batch_no,
    a.line_num,
    a.station_id,
    a.thread_id,
    a.item,
    a.code,
    a.result,
    a.datetime,
    dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag
    from fruit_table_a a
    inner join fruit_table_b b on a. recordid =
    b.productid

    where instr(a.code, '000000000000') = 0
    and instr(a.code, '9999999') = 0
    and instr(a.item, '0923') > 0
    and b.batch_no = 'aaa'
    union all
    select a. recordid,
    b.batch_no,
    a.line_num,
    a.station_id,
    a.thread_id,
    a.item,
    a.code,
    a.result,
    a.datetime,
    dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag
    from fruit_table_a a
    inner join fruit_table_b b on a. recordid =
    b.productid

    where instr(a.code, '000000000000') = 0
    and instr(a.code, '9999999') = 0
    and instr(a.item, '1023') > 0
    and b.batch_no = 'aaa') c) k
    group by k.code) g
    where instr(g.rank, '0823') > 0
    and instr(g.rank, '0923') > 0
    and instr(g.rank, '1023') > 0) y on u.batch_no =
    y.batch_no
    left join (select 'aaa' as batch_no,
    sum(count(c.code)) as residual_total,
    sum(sum(case
    when c.final_flag = 1 and c.result = 0 then
    1
    else
    0
    end)) last_ng_cnt
    from (select b.batch_no,
    a.line_num,
    a.station_id,
    a.thread_id,
    a.item,
    a.code,
    a.result,
    a.datetime,
    dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000') desc) as final_flag
    from fruit_table_a a
    inner join fruit_table_b b on a. recordid =
    b.productid


    where instr(a.code, '000000000000') = 0
    and instr(a.code, '9999999') = 0
    and instr(a.item, '0823') = 0
    and instr(a.item, '0923') = 0
    and instr(a.item, '1023') = 0
    and instr(a.item, '23') > 0
    and b.batch_no = 'aaa') c
    group by c.code) z on u.batch_no = z.batch_no
    left join (select 'aaa' as batch_no,
    sum(case
    when g.first_rs = 1 and g.first_flag = 1 then
    1
    else
    0
    end) package_pass_cnt,
    count(distinct g.code) as package_total

    from (select 'aaa' as batch_no,
    a.item,
    a.code,
    a.result as first_rs,
    a.datetime,
    dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '00000000')) as first_flag
    from fruit_table_a a
    inner join fruit_table_b b on a. recordid =
    b.productid

    where instr(a.code, '000000000000') = 0
    and instr(a.code, '9999999') = 0
    and b.batch_no = 'aaa'
    and instr(a.item, '0020') > 0) g) p on u.batch_no =
    p.batch_no
    23 条回复    2018-01-17 12:32:06 +08:00
    MeteorCat
        1
    MeteorCat  
       2018-01-16 10:05:33 +08:00 via Android   ❤️ 1
    卧槽,这陈年代码,我想起以前游戏开发时候用的数据库最开始游戏客户端主程瞎鸡儿建立数据表,后来查询也是这样,一查一动一堆关联表



    对于这种如果仅仅做查询的话建议可以跑个定时脚本重新将数据表的大概数据,比如原来 total 字段需要多张表,直接跑脚本写在一张表内部一个 total 字段数据,如果是要查询+修改,那么所需的方法就比较麻烦了
    poupoo
        2
    poupoo  
    OP
       2018-01-16 10:10:35 +08:00
    @MeteorCat 大神主要是为了修改,而且左连接的子查询其实就是两张同样的表 fruit_table_a 和 fruit_table_b 只是筛选条件不太一样,有没有好的修改方法!
    MeteorCat
        3
    MeteorCat  
       2018-01-16 10:17:03 +08:00 via Android
    @poupoo 可以试下直接用数据库的触发器来将修改逻辑写在数据库那边,具体你也可以了解一下,这种代码只能依托数据库本身特性
    crazyneo
        4
    crazyneo  
       2018-01-16 10:30:08 +08:00   ❤️ 1
    我屮艸芔茻给跪了……先考虑建视图,再用游标逐条尽量把分析的东西放业务逻辑,如果不行就用存储过程重写这段逻辑,触发器的话我并不推荐,原因是不知道这张业务表的关联性。
    realpg
        5
    realpg  
       2018-01-16 10:34:25 +08:00   ❤️ 1
    改逻辑 更多东西在代码实现 利用试图和索引 变成各种简单查询

    有压力的系统是不能这么写 sql 的
    poupoo
        6
    poupoo  
    OP
       2018-01-16 10:40:15 +08:00
    @crazyneo @realpg 表 fruit_table_a 和 fruit_table_b 数据量差不多,其中表的一个 batch_no 对应多条记录,而 a
    与 b 先用主键实现了内连接。
    xkeyideal
        7
    xkeyideal  
       2018-01-16 11:05:53 +08:00
    这种祖传老代码,就别贴出来了,就算贴,也格式化一下
    poupoo
        8
    poupoo  
    OP
       2018-01-16 11:21:53 +08:00
    @xkeyideal 这里面格式化不太会用,贴的时候是有个格式的
    realpg
        9
    realpg  
       2018-01-16 12:51:24 +08:00   ❤️ 1
    @poupoo #6
    子查询都上了 子查询结果给别的搜索 这不生成临时表?生成临时表就全是 filesort,属于 zuo & die 的典型

    写这个的是写单机商业软件正常 要是写网络服务的 趁早开了
    wdlth
        10
    wdlth  
       2018-01-16 12:52:12 +08:00 via iPhone   ❤️ 1
    和我们那老系统有得一拼,里面还有各种 outer join,distinct ……
    poupoo
        11
    poupoo  
    OP
       2018-01-16 13:45:47 +08:00
    @realpg 并不是网络服务,要统计各项直接合格率,有什么方法没有
    poupoo
        12
    poupoo  
    OP
       2018-01-16 14:04:24 +08:00
    @wdlth 所有的 left join 都只有一条记录,有什么可行的方法可以代替这种方法,毕竟不是 DBA,真没辙
    abusizhishen
        13
    abusizhishen  
       2018-01-16 14:33:35 +08:00 via Android
    什么鬼,这怎么看
    saulshao
        14
    saulshao  
       2018-01-16 15:50:32 +08:00   ❤️ 1
    我一直有个困惑,在下其实也就会写这样的代码,别的基本都不太会,正好借这个帖子顺便一问:
    数据库应用程序基本就是 2 个思路:
    1. 写一个特别复杂的查询,把所有的业务逻辑和计算都写在 SQL 里,一次性把结果拿出来显示。好处是只需要查询一次数据库,但是代码可重用性特别差。
    2. 将这个查询分解成一系列的 Java/Python 代码,SQL 只负责读取需要的数据,业务逻辑和计算则由应用程序层完成,即所谓的 view 层。好处是代码可重用性比较好,但是可能查询数据库的次数会成(数十)倍增加。
    选择这 2 种策略的原则到底是什么?有什么指导性的理论或者是书,又或者是最佳实践的文章不?
    楼主这贴其实在我看来更适合后面的方法,因为从数据库返回的记录集很小,查询数据库的次数其实也很有限。即使分开,也没有什么致命的性能问题。
    noahzh
        15
    noahzh  
       2018-01-16 16:27:51 +08:00   ❤️ 2
    @saulshao 那是没有遇到海量数据,数据分库情况下,这样及联查询是没有办法使用的。在数据库里做计算,是一种深坑,一旦数据库没有办法满足你的性能需求时候,就会发现你要改逻辑了,那时候你面临着业务压力与性能压力。性能问题不要看当前,要看业务类型。现在可以满足不代表未来也能满足,但是也不要过度设计。还有数据库返回记录集小是一个没有意义的指标,因为数据库真正瓶颈是查询复杂度。
    poupoo
        16
    poupoo  
    OP
       2018-01-16 17:03:52 +08:00
    目前来看并没有用到很新的技术,而且也没有转移到 python 或者 Java 的可能,老板就扔了一个包,让你整
    lygmqkl
        17
    lygmqkl  
       2018-01-16 17:45:30 +08:00   ❤️ 1
    没有架构,没有产品吗? 天。。。
    wdlth
        18
    wdlth  
       2018-01-16 21:33:28 +08:00   ❤️ 1
    @poupoo 尽量减少表关联,我看上面有很多的子查询,排名函数、聚合函数,如果条件允许,可以分解成多步简单的查询,再在程序里面进行业务逻辑的实现。
    odirus
        19
    odirus  
       2018-01-16 21:55:48 +08:00 via Android
    搭车求问,大家都是怎么规范别名,临时表 命名的?
    odirus
        20
    odirus  
       2018-01-16 21:56:21 +08:00 via Android   ❤️ 1
    我都 a b c 命名有恐惧症
    l00t
        21
    l00t  
       2018-01-16 22:22:04 +08:00   ❤️ 1
    说 SQL 优化呢,你得先说数据库是什么,然后贴个目前的执行计划,这个是最基本的了吧…… 不然从何说起呢……

    从语法看,你这个多半是 Oracle 吧。我建议你把每个子查询单独拉出来执行下,看看哪个特别慢,找到性能瓶颈。

    没有执行计划,说不了太多。单从语句看,y 那段问题比较大。没有必要写那么复杂的,你可以精简出更有效率的语句来实现同样的效果。y 里面的那个 dense_rank 更是完全看不出作用。

    你这个语句里所有的 inner join 里面的 where 貌似都可以改成 and,或者直接改成不需要 inner join 的模式。我看你的 inner join 貌似无非只是确认下存在而已,对于 b 表的字段一个都没用上。

    left join 对你这个语句的影响应该等于 0。你的 u,v,y,z,p 全都只有一行记录,这种 left join 能有啥关系?
    poupoo
        22
    poupoo  
    OP
       2018-01-17 09:53:14 +08:00
    @MeteorCat @l00t @wdlth 确实是 oracle,我考虑拆开每一项用物化视图,你们有什么更好的建议吗。
    MeteorCat
        23
    MeteorCat  
       2018-01-17 12:32:06 +08:00 via Android
    @poupoo 只能这样来处理,我以前那家游戏的数据就是视图+触发器+存储过程,说到底,前人留的坑只能这样处理,重构是不可能重构的,你看你表那堆关联就排除了重构;什么索引优化也别想了,那堆关联 join 就压根没法命中索引,所以只能加机器性能和创建冗余的视图等
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3346 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 00:42 · PVG 08:42 · LAX 17:42 · JFK 20:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.