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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

已注册用户请登录

这是一个创建于 2894 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

2018-01-16 10:05:33 +08:00 via Android

卧槽，这陈年代码，我想起以前游戏开发时候用的数据库最开始游戏客户端主程瞎鸡儿建立数据表，后来查询也是这样，一查一动一堆关联表

对于这种如果仅仅做查询的话建议可以跑个定时脚本重新将数据表的大概数据，比如原来 total 字段需要多张表，直接跑脚本写在一张表内部一个 total 字段数据，如果是要查询+修改，那么所需的方法就比较麻烦了

poupoo

2018-01-16 10:10:35 +08:00

@MeteorCat 大神主要是为了修改，而且左连接的子查询其实就是两张同样的表 fruit_table_a 和 fruit_table_b 只是筛选条件不太一样，有没有好的修改方法！

MeteorCat

2018-01-16 10:17:03 +08:00 via Android

@poupoo 可以试下直接用数据库的触发器来将修改逻辑写在数据库那边，具体你也可以了解一下，这种代码只能依托数据库本身特性

crazyneo

2018-01-16 10:30:08 +08:00

我屮给跪了……先考虑建视图，再用游标逐条尽量把分析的东西放业务逻辑，如果不行就用存储过程重写这段逻辑，触发器的话我并不推荐，原因是不知道这张业务表的关联性。

realpg

PRO

2018-01-16 10:34:25 +08:00

改逻辑更多东西在代码实现利用试图和索引变成各种简单查询

有压力的系统是不能这么写 sql 的

poupoo

2018-01-16 10:40:15 +08:00

@crazyneo @realpg 表 fruit_table_a 和 fruit_table_b 数据量差不多，其中表的一个 batch_no 对应多条记录，而 a
与 b 先用主键实现了内连接。

xkeyideal

2018-01-16 11:05:53 +08:00

这种祖传老代码，就别贴出来了，就算贴，也格式化一下

poupoo

2018-01-16 11:21:53 +08:00

@xkeyideal 这里面格式化不太会用，贴的时候是有个格式的

realpg

PRO

2018-01-16 12:51:24 +08:00

@poupoo #6
子查询都上了子查询结果给别的搜索这不生成临时表？生成临时表就全是 filesort，属于 zuo & die 的典型

写这个的是写单机商业软件正常要是写网络服务的趁早开了

wdlth

2018-01-16 12:52:12 +08:00 via iPhone

和我们那老系统有得一拼，里面还有各种 outer join，distinct ……

poupoo

2018-01-16 13:45:47 +08:00

@realpg 并不是网络服务，要统计各项直接合格率，有什么方法没有

poupoo

2018-01-16 14:04:24 +08:00

@wdlth 所有的 left join 都只有一条记录，有什么可行的方法可以代替这种方法，毕竟不是 DBA，真没辙

abusizhishen

2018-01-16 14:33:35 +08:00 via Android

什么鬼，这怎么看

saulshao

2018-01-16 15:50:32 +08:00

我一直有个困惑，在下其实也就会写这样的代码，别的基本都不太会,正好借这个帖子顺便一问:
数据库应用程序基本就是 2 个思路:
1. 写一个特别复杂的查询，把所有的业务逻辑和计算都写在 SQL 里,一次性把结果拿出来显示。好处是只需要查询一次数据库,但是代码可重用性特别差。
2. 将这个查询分解成一系列的 Java/Python 代码,SQL 只负责读取需要的数据,业务逻辑和计算则由应用程序层完成,即所谓的 view 层。好处是代码可重用性比较好,但是可能查询数据库的次数会成(数十)倍增加。
选择这 2 种策略的原则到底是什么？有什么指导性的理论或者是书，又或者是最佳实践的文章不？
楼主这贴其实在我看来更适合后面的方法，因为从数据库返回的记录集很小，查询数据库的次数其实也很有限。即使分开，也没有什么致命的性能问题。

noahzh

2018-01-16 16:27:51 +08:00

@saulshao 那是没有遇到海量数据，数据分库情况下，这样及联查询是没有办法使用的。在数据库里做计算，是一种深坑，一旦数据库没有办法满足你的性能需求时候，就会发现你要改逻辑了，那时候你面临着业务压力与性能压力。性能问题不要看当前，要看业务类型。现在可以满足不代表未来也能满足，但是也不要过度设计。还有数据库返回记录集小是一个没有意义的指标，因为数据库真正瓶颈是查询复杂度。

poupoo

2018-01-16 17:03:52 +08:00

目前来看并没有用到很新的技术，而且也没有转移到 python 或者 Java 的可能，老板就扔了一个包，让你整

lygmqkl

2018-01-16 17:45:30 +08:00

没有架构，没有产品吗？天。。。

wdlth

2018-01-16 21:33:28 +08:00

@poupoo 尽量减少表关联，我看上面有很多的子查询，排名函数、聚合函数，如果条件允许，可以分解成多步简单的查询，再在程序里面进行业务逻辑的实现。

odirus

2018-01-16 21:55:48 +08:00 via Android

搭车求问，大家都是怎么规范别名，临时表命名的？

odirus

2018-01-16 21:56:21 +08:00 via Android

我都 a b c 命名有恐惧症

l00t

2018-01-16 22:22:04 +08:00

说 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

2018-01-17 09:53:14 +08:00

@MeteorCat @l00t @wdlth 确实是 oracle,我考虑拆开每一项用物化视图，你们有什么更好的建议吗。

MeteorCat

2018-01-17 12:32:06 +08:00 via Android

@poupoo 只能这样来处理，我以前那家游戏的数据就是视图+触发器+存储过程，说到底，前人留的坑只能这样处理，重构是不可能重构的，你看你表那堆关联就排除了重构；什么索引优化也别想了，那堆关联 join 就压根没法命中索引，所以只能加机器性能和创建冗余的视图等