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

[poupoo]

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

[MeteorCat]

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



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

[poupoo]

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

[MeteorCat]

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

[crazyneo]

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

[realpg]

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

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

[poupoo]

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

[xkeyideal]

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

[poupoo]

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

[realpg]

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

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

[wdlth]

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

[poupoo]

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

[poupoo]

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

[abusizhishen]

什么鬼，这怎么看

[saulshao]

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

[noahzh]

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

[poupoo]

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

[lygmqkl]

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

[wdlth]

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

[odirus]

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

[odirus]

我都 a b c 命名有恐惧症