V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
raymanr
V2EX  ›  程序员

一次查询优化...

  •  
  •   raymanr · 2020-10-15 15:03:38 +08:00 · 2177 次点击
    这是一个创建于 1260 天前的主题,其中的信息可能已经有所发展或是发生改变。

    部门同事来找我说现在用的几个存储过程(离职同事写的)查询太慢了, 要几个小时才能出结果, 于是我打开后看到了如此的内容...

    我也不是说有什么优越感, 但是这槽点也太大了...

    以下是部分片段, 该存储过程总计 900 行, 类似的计算部分大概有四百行多, 剩余部分全是字符串拼接

    该同事还留下了另外几个存储过程等待修改, 看到五百行往上的满篇 left join, with 套 with

    十几个表 left join, 还是 on a.col1 >= b.col2 and a.col2 < b.col2 这种连接

    现在只想摸鱼

    
    -- GMV
    SET
        @start_gmv = (
            SELECT
                SUM(`客户付款总金额(¥)`)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = start_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    SET
        @end_gmv = (
            SELECT
                SUM(`客户付款总金额(¥)`)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = end_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    -- 订单量
    SET
        @start_order = (
            SELECT
                COUNT(*)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = start_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    SET
        @end_order = (
            SELECT
                COUNT(*)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = end_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    -- 产品销量
    SET
        @start_sale = (
            SELECT
                SUM(`产品销量`)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = start_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    SET
        @end_sale = (
            SELECT
                SUM(`产品销量`)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = end_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    -- 客单价
    SET
        @start_atv = (
            SELECT
                SUM(`客户付款总金额(¥)`) / COUNT(*)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = start_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    SET
        @end_atv = (
            SELECT
                SUM(`客户付款总金额(¥)`) / COUNT(*)
            FROM
                `历史订单明细`
            WHERE
                `测算日期范围` = end_estimate_date
                AND `部门` LIKE CONCAT('%', @department, '%')
                AND `经营团队` LIKE CONCAT('%', @management_team, '%')
                AND `组别` LIKE CONCAT('%', @groups, '%')
        );
    
    -- 产品成本占比
    ...
    
    第 1 条附言  ·  2020-10-16 17:55:23 +08:00
    干!

    把这个存储过程理到最后一步, 眼看就收工了

    结果存储过程里面套了一个函数, 打开函数一看

    又是这种 like 了一堆条件然后 sum 接着赋值给一大堆变量的东西

    又是 TM 的一千行的 sql 函数
    16 条回复    2020-10-16 17:58:24 +08:00
    sxfscool
        1
    sxfscool  
       2020-10-15 15:17:20 +08:00
    中文列表是不是槽点[捂脸]
    sxfscool
        2
    sxfscool  
       2020-10-15 15:17:40 +08:00
    列名,打错了
    weizhen199
        3
    weizhen199  
       2020-10-15 15:21:48 +08:00
    我也不想看。。加索引加 hint 完事。
    或者让他们改 java 做
    ahmcsxcc
        4
    ahmcsxcc  
       2020-10-15 15:22:21 +08:00
    什么数据库说下啊
    raymanr
        5
    raymanr  
    OP
       2020-10-15 15:23:59 +08:00
    @sxfscool
    不是, 中文名其实我倒觉得还好, 比英文名还容易看懂意思点

    WHERE 部分的 LIKE 运算符会进行全表扫描, 速度本来就很慢,

    于是每查询一个变量就需要进行一次全表扫描,

    可每次查询的都是同一张表同一个条件
    raymanr
        6
    raymanr  
    OP
       2020-10-15 15:28:17 +08:00
    @weizhen199
    人都跑了, 现在这球提到我这来改了, 唉, 这么复杂的存储过程, 我花一两个个月来改不算过分吧
    Mithril
        7
    Mithril  
       2020-10-15 15:32:26 +08:00   ❤️ 1
    @raymanr 可以的,不过你也别改了,马上 2077 就要 release 了。你要一个月的时间,花一礼拜重写了它,剩下的时间玩玩 2077 不香吗?
    xuanbg
        8
    xuanbg  
       2020-10-15 16:14:31 +08:00
    唯一的槽点是 like,你可以改成= ‘xxid’,让程序先用可以模糊查询的下拉列表 like 查到并选中 id,再查询数据,这样就可以走索引了。
    xuanbg
        9
    xuanbg  
       2020-10-15 16:18:16 +08:00
    @xuanbg 譬如部门参数,就可以通过输入「业务」查询到业务 1 部、业务 2 部……,然后选中业务 1 部得到业务 1 部的 id,再用这个 id 来查询。
    Rimifon
        10
    Rimifon  
       2020-10-15 16:19:33 +08:00
    这个是不是应该先 select 到一个临时表,然后从临时表去做统计。like 语句改成 charindex 会不会好一些。
    raymanr
        11
    raymanr  
    OP
       2020-10-15 16:22:09 +08:00
    @xuanbg
    算了, 本来就是非正规的业务, 数据部门的破事, 一个大表没主键没维度表, 主要也是这个 like 循环套循环的全表扫描一百多遍槽点太大了, 实际上查询需要用到的数据也就二三十万行, 跑了几个小时说出去真的是太丢人了
    zoharSoul
        12
    zoharSoul  
       2020-10-15 16:48:59 +08:00
    @sxfscool 专有领域中文列名挺好的.
    很多东西不好翻译.
    lasuar
        13
    lasuar  
       2020-10-15 17:28:19 +08:00   ❤️ 1
    为什么那么多人喜欢写存储过程
    zouzou0208
        14
    zouzou0208  
       2020-10-16 08:21:49 +08:00
    建议重写。
    no1xsyzy
        15
    no1xsyzy  
       2020-10-16 11:54:19 +08:00
    @zoharSoul 然而不是中文专有领域,这些中文单词都是英文强行翻译过来的,找到原本的英文术语就行
    @xuanbg 但是我挺喜欢我用的系统就这么 concat 两个 % 上去当作模糊搜索的,有必要的话我自己也可以手写几个 % 进去更模糊地搜索(
    raymanr
        16
    raymanr  
    OP
       2020-10-16 17:58:24 +08:00
    @lasuar
    我恨存储过程, 改这个破东西时候心里已经骂了提出用存储过程来解决和写这个垃圾存储过程的人五千万遍了

    尤其是今天快改完时候发现最后几行的一个没见过的函数, 打开该函数发现又是一千行的时候
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3109 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 12:55 · PVG 20:55 · LAX 05:55 · JFK 08:55
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.