mysql 语句优化

omc_block表三百万条数据+
om_flow 表二十万条数据+
其他表一百条数据左右

优化后（6.6s）

 select a.bsn_app_code,a.org_code,a.peer_count
,ifnull(b.joinCerts,0) as joinCerts
,ifnull(c.flows,0) as flows
,ifnull(d.romUsed,0) as romUsed 
,ifnull(e.blocks,0) as blocks 
,ifnull(f.tps,0) as tps 
 from (
 select
a.bsn_app_code -- 应用编码
,org.org_code  -- 组织编码
,oa.peer_count  -- 节点数量
,a.channel_name
from om_org_app oa
inner join om_app a on a.app_id = oa.app_id
inner join om_org org on org.org_id = oa.org_id
where oa.state !=3) as a
--  参数证书数   <==参数人数 与之相等
left join(
select euc.org_code,eua.bsn_app_code,(euc.user_cret_id) as joinCerts  from om_epmt_user_app_cert euac 
inner join om_epmt_user_cert euc on euc.user_cret_id = euac.user_cret_id
inner join om_epmt_user_app eua on  eua.user_app_id = euac.user_app_id
group by euc.org_code,eua.bsn_app_code 
) as b on b.bsn_app_code = a.bsn_app_code and b.org_code = a.org_code

left join (
 -- 流量（网关和数据）
select fd.bsn_app_code, ROUND(sum(fd.total_flow)) as flows  from  om_flow_day fd  
group by fd.bsn_app_code 
) as c on c.bsn_app_code = a.bsn_app_code

left join (
-- 已用（硬盘）容量
select oc.channel_name,org.org_code, sum( (oc.used_disk_capacity + oc.peer_used_disk_capacity)) as romUsed from omc_channel oc
inner join om_org_server os on os.org_server_id = oc.org_server_id
inner join om_org org on org.org_id = os.org_id
group by oc.channel_name,org.org_code 
)as d on d.channel_name = a.channel_name and d.org_code = a.org_code

left join(
-- 单记账节点总区块数
select b.channel_name ,count(block_num) as blocks  from  omc_block b 
 group by b.channel_name 
) as e on e.channel_name = a.channel_name

left join(
-- tps
 SELECT  f.bsn_app_code,f.org_code,sum(f.request_count) as tps FROM om_flow f 
group by f.bsn_app_code,f.org_code
) as f on f.bsn_app_code = a.bsn_app_code and f.org_code = a.org_code

image.png

优化前（88.6s）

select
a.bsn_app_code -- 应用编码
,org.org_code  -- 组织编码
,oa.peer_count  -- 节点数量
, (
select count(euc.user_cret_id) from om_epmt_user_app_cert euac 
inner join om_epmt_user_cert euc on euc.user_cret_id = euac.user_cret_id
where euac.user_app_id in(
    select eua.user_app_id  from om_epmt_user_app eua where   eua.bsn_app_code =a.bsn_app_code
) and euc.org_code = org.org_code
) as joinCerts -- 参数证书数   <==参数人数 与之相等
, (select ifnull(ROUND(sum(fd.total_flow)),0) from  om_flow_day fd where fd.bsn_app_code = a.bsn_app_code)  as flows  -- 流量（网关和数据）
,(
select  ifnull(sum( (oc.used_disk_capacity + oc.peer_used_disk_capacity)),0) from omc_channel oc
inner join om_org_server os on os.org_server_id = oc.org_server_id
 where oc.channel_name = a.channel_name
 and os.org_id = oa.org_id
)  as romUsed -- 已用（硬盘）容量
,   
(
 select ifnull(count(block_num),0) from  omc_block b where b.channel_name = a.channel_name
) as blocks  -- 单记账节点总区块数
,(
SELECT ifnull(sum(f.request_count),0)  FROM om_flow f where f.bsn_app_code = a.bsn_app_code and f.org_code = org.org_code
)
as tps
from om_org_app oa
inner join om_app a on a.app_id = oa.app_id
inner join om_org org on org.org_id = oa.org_id
;

image.png