一次查询优化…
資深大佬 : raymanr 7
部门同事来找我说现在用的几个存储过程(离职同事写的)查询太慢了, 要几个小时才能出结果, 于是我打开后看到了如此的内容…
我也不是说有什么优越感, 但是这槽点也太大了…
以下是部分片段, 该存储过程总计 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, '%') ); -- 产品成本占比 ... 大佬有話說 (16)