mysql5.6 主从不同步 存储过程大事务导致
总结（重建从库同步后，周期性出现延迟，过段时间后恢复，分析原因，由大事务存储过程导致）
1 遇到的问题经营计划主从不同步，从库sql和io进程双YES，但是Seconds_Behind_Master不为0
2 重建从库，进行主从同步，观察周六日是正常，在周一上午使用业务后，再次导致主从不同步
3 经分析relay-log，mysql_binlog
4 发现是大事务导致，大量的插入，更新，删除，这是导致主从不同步的根本原因，也有可能某些表没有主键导致，慢查询
5 大事务是研发业务问题，数据库系统一共有89个存储过程，研发需要调整业务，对于研发来说应该规定不要使用存储过程了
6 从运维角度来说优化数据库的参数已没有意义了，问题出在存储过程和业务，慢查询等
7 对于运维来说可以考虑更换到mysql 5.7版本，mysql5.7对于并行复制有很大优化，但是因为存储过程太多89个，太长，更换版本不一定能保证主从同步一致
8 此阶段测试期间，周五周六周日没出现同不延迟问题，周一早上大事务过程中还是出现同步延迟情况，优化数据参数后，周二和三早上同步自动恢复，都同步正常，判断此为周期性的延迟，等类似的周一上午的大事务出现还是会出现延迟情况的
9 目前，因为大事务存储过程的增删改导致主从不同步，影响无法做正常的读写分离，优化最好从研发角度，减少存储过程和优化业务，运维角度更换5.7版本，还有优化慢查询语句。
10 观察慢语句记录，整理可以优化的慢查询记录（备份原有慢查询，清空后研究新出现的慢查询，研究目前周一早上会出现大事务，考虑查询问题，除增删改）
mysqldumpslow -s r -t 20 mysql_slow.log > slow_report.log 以下为原来的慢查询记录（已隐藏IP）
Count: 4 Time=93.44s (373s) Lock=0.01s (0s) Rows=990309.8 (3961239), myq[myq]@[172XX]
SELECT * FROM `bp_plan_project_tjx_detail`
Count: 2 Time=101.80s (203s) Lock=0.00s (0s) Rows=1073778.0 (2147556), myq[myq]@[172XX]
SELECT * FROM `bp_plan_project_tjx_detail_2016_1`
Count: 1 Time=278.12s (278s) Lock=0.00s (0s) Rows=995923.0 (995923), myq[myq]@[172XX]
select * from bp_projectreportdetail where v_jldw is not null
Count: 2 Time=81.46s (162s) Lock=0.00s (0s) Rows=386903.0 (773806), myq[myq]@[172XX]
SELECT * FROM `bp_projectreport`
Count: 9 Time=23.04s (207s) Lock=0.00s (0s) Rows=64041.6 (576374), myq[myq]@[隐藏业务175] -------------------------此为业务大事务查询
select deptid,deptid as orgid,deptname,N as pro_type,v_tjxcode,n_monthvalue,n_yearvalue,
n_totalvalue,n_monthplan,n_yearplan,n_totalplan,type from (
-- 部门
select e.deptid,e.deptname,r.v_tjxcode,r.n_monthvalue ,r.n_yearvalue,
r.n_totalvalue,r.n_monthplan,r.n_yearplan,r.n_totalplan ,N as type from
(select deptid ,deptname ,depttype from eadept ea where
ea.deptid like concat('S','S' ) and
case when 'S' in (N) then ea.depttype in(N)
when 'S' in (N) then ea.depttype in(N,N)
when 'S' in (N) then ea.depttype in(N) end
and ea.ENABLED = 'S' ) e
left join
(
select report.v_unitid,report.v_filldate ,report.v_depttype ,
detail.v_tjxcode,if(report.v_filldate = CONCAT('S', 'S','S'),detail.n_monthvalue,null) as n_monthvalue,
if(substr(report.v_filldate,N,N) = 'S' ,detail.n_yearvalue,null) as n_yearvalue,
detail.n_totalvalue,
if(report.v_filldate = CONCAT('S', 'S','S'),detail.n_monthplan,null) as n_monthplan,
if(substr(report.v_filldate,N,N) = 'S',detail.n_yearplan,null) as n_yearplan,
detail.n_totalplan
from (
select report1.* from
(select bp.* from bp_projectreport bp
where case when 'S' in (N) then bp.v_depttype in(N)
when 'S' in (N) then bp.v_depttype in(N,N)
when 'S' in (N) then bp.v_depttype in(N) end
and bp.v_unitid like concat('S','S')
and bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
order by bp.v_unitid, bp.v_filldate desc ) report1 group by report1.v_unitid) report
JOIN bp_projectreportdetail detail ON detail.v_reportid = report.id
JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S' ) r
on e.deptid = r.v_unitid and r.v_depttype = e.depttype
union all
-- 局指指挥部
select m.deptid,m.deptname,n.v_tjxcode,sum(n.n_monthvalue) as n_monthvalue,
sum(n.n_yearvalue) as n_yearvalue,sum(n.n_totalvalue) as n_totalvalue,
sum(n.n_monthplan) as n_monthplan,sum(n.n_yearplan) as n_yearplan,
sum(n.n_totalplan) as n_totalplan,N as type from (
select e.*,v.FPROJECTID from (select deptid ,deptname ,depttype from eadept ea where
ea.deptid like concat('S','S' ) and
case when 'S' in (N,N) then ea.depttype in ('S')
else N= N end
and ea.ENABLED = 'S' ) e
left join project_view v on v.FUNITID = e.deptid and e.depttype = N and v.projectcat = 'S'
) m
left join (
select u.PARENTPROJECTID,u.v_filldate,u.parentname ,u.v_tjxcode ,
sum(if(v_filldate = CONCAT('S', 'S','S'),u.n_monthvalue,null)) as n_monthvalue,
sum(if(substr(v_filldate,N,N) ='S',u.n_yearvalue,null)) as n_yearvalue,
sum(u.n_totalvalue) as n_totalvalue,
sum(if(v_filldate = CONCAT('S', 'S','S'),u.n_monthplan,null)) as n_monthplan,
sum(if(substr(v_filldate,N,N) ='S',u.n_yearplan,null)) as n_yearplan,
sum(u.n_totalplan) as n_totalplan
from (
select pro.*,report.v_unitid,report.v_projectid,report.v_depttype,report.v_filldate,detail.v_tjxcode,detail.v_tjxmc,detail.v_jldw,
detail.n_monthvalue ,detail.n_yearvalue,detail.n_totalvalue,detail.n_monthplan,detail.n_yearplan,detail.n_totalplan from (
select id,v_unitid,v_projectid,v_depttype,v_filldate from (
SELECT
id, v_unitid, v_projectid,v_depttype, v_filldate
FROM
bp_projectreport bp
WHERE
bp.v_depttype = 'S' and
bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
ORDER BY bp.v_projectid , bp.v_filldate DESC )
p group by v_unitid ,v_projectid , v_depttype
) report
join (
select v.FPROJECTID as parentprojectid,v.FPROJECTNAME as parentname,pi.FPROJECTID,pi.FPROJECTNAME,pi.funitid,pi.funit,pi.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
join project_info pi on v.FPROJECTID=pi.PARENTPROJECTID
where v.`status`='S' and i.PRO_TYPE='S' and (
v.FUNITID like concat('S','S' ) or v.forgid = 'S') and v.projectcat='S' and pi.pro_type ='S'
union ALL
select v.FPROJECTID as parentprojectid ,v.FPROJECTNAME,v.FPROJECTID,v.FPROJECTNAME,v.funitid,v.funit,v.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
where v.`status`='S' and i.PRO_TYPE in ('S') and ( v.FUNITID like concat('S','S') or v.forgid = 'S')and v.projectcat='S'
) pro on report.v_depttype ='S' and report.v_projectid = pro.FPROJECTID
JOIN bp_projectreportdetail detail ON detail.v_reportid = report.id
JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S' ) u group by u.parentprojectid , u.v_tjxcode
) n on n.PARENTPROJECTID = m.fprojectid
group by m.deptid ,n.v_tjxcode ) k
union all
-- 项目
select deptid ,orgid,fprojectname,pro_type,v_tjxcode,n_monthvalue,n_yearvalue,n_totalvalue,
n_monthplan,n_yearplan,n_totalplan,type from (
select o.deptid,o.fprojectid as orgid,g.fprojectname,pro_type,g.v_tjxcode,g.n_monthvalue,
g.n_yearvalue,g.n_totalvalue,g.n_monthplan,g.n_yearplan,g.n_totalplan ,N as type from (
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select i1.fprojectid ,ea.deptid from eadept ea
join project_view v on ea.deptid = v.funitid
join project_info i on i.fprojectid = v.fprojectid and i.pro_type = 'S'
join project_info i1 on i1.PARENTPROJECTID = i.fprojectid and i1.pro_type = 'S'
where ea.deptid like concat('S','S') and 'S' in (N,N) and ea.depttype = N and ea.enabled = 'S'
) o left join (
-- 局指项目 (汇总自己跟工区的数据)
select t1.parentprojectid as fprojectid,pro_view.FPROJECTNAME,pro_view.funitid ,N as pro_type,t1.v_tjxcode , t1.n_monthvalue ,t1.n_yearvalue,
t1.n_totalvalue,t1.n_monthplan,t1.n_yearplan,t1.n_totalplan from (
select parentprojectid ,v_tjxcode,
sum(if(v_filldate = CONCAT('S', 'S','S'),n_monthvalue,null)) as n_monthvalue,
sum(if(substr(v_filldate ,N,N)= 'S',n_yearvalue,null)) as n_yearvalue,
sum(n_totalvalue) as n_totalvalue,
sum(if(v_filldate = CONCAT('S', 'S','S'),n_monthplan,null)) as n_monthplan,
sum(if(substr(v_filldate ,N,N)= 'S',n_yearplan,null)) as n_yearplan,
sum(n_totalplan) as n_totalplan,
N as type from (
select pro.parentprojectid ,pro.parentname,
pro.fprojectid,pro.fprojectname,pro.funit,pro.funitid,pro.fprojecttypecode,
report.id,report.v_filldate,
detail.v_tjxcode,detail.v_tjxmc,detail.n_monthvalue,detail.n_yearvalue,detail.n_totalvalue,
detail.n_monthplan,detail.n_yearplan,detail.n_totalplan
from
( select v.FPROJECTID as parentprojectid,v.FPROJECTNAME as parentname,pi.FPROJECTID,pi.FPROJECTNAME,pi.funitid,pi.funit,pi.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
join project_info pi on v.FPROJECTID=pi.PARENTPROJECTID
where v.`status`='S' and i.PRO_TYPE='S' and (
v.FUNITID like concat('S','S' ) or v.forgid = 'S') and v.projectcat='S' and pi.pro_type ='S'
union ALL
select v.FPROJECTID as parentprojectid ,v.FPROJECTNAME,v.FPROJECTID,v.FPROJECTNAME,v.funitid,v.funit,v.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
where v.`status`='S' and i.PRO_TYPE in ('S') and ( v.FUNITID like concat('S','S') or v.forgid = 'S')and v.projectcat='S'
) pro
left join (
select report1.* from
(select * from bp_projectreport bp
where bp.v_depttype = 'S'
and bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
order by bp.v_projectid, bp.v_filldate desc ) report1 group by report1.v_projectid
) report on pro.fprojectid = report.v_projectid
left join bp_projectreportdetail detail on detail.v_reportid = report.id
left JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S' ) p group by PARENTPROJECTID,v_tjxcode
) t1
join project_view pro_view on pro_view.fprojectid = t1.parentprojectid
union all
-- 工区 跟 标准项目数据
select
pro.fprojectid,pro.fprojectname,pro.funitid,pro.pro_type,
detail.v_tjxcode,
if(report.v_filldate = concat('S','S','S') ,detail.n_monthvalue,null ) as n_monthvalue,
if(substr(report.v_filldate,N,N) = 'S',detail.n_yearvalue,null) as n_yearvalue,
detail.n_totalvalue,
if(report.v_filldate = concat('S','S','S') ,detail.n_monthplan,null ) as n_monthplan,
if(substr(report.v_filldate,N,N) = 'S',detail.n_yearplan,null) as n_yearplan,
detail.n_totalplan
from
( select
v.FPROJECTID as parentprojectid,v.FPROJECTNAME as parentname,pi.FPROJECTID,pi.FPROJECTNAME,pi.funitid,pi.funit,pi.pro_type
from project_view v join project_info i on v.fprojectid=i.fprojectid
join project_info pi on v.FPROJECTID=pi.PARENTPROJECTID
where v.`status`='S' and i.PRO_TYPE='S' and (
case when 'S' in('S') then pi.FUNITID else v.FUNITID end like concat('S','S' ) or v.forgid = 'S') and v.projectcat='S' and pi.pro_type ='S'
union all
select v.FPROJECTID as parentprojectid ,v.FPROJECTNAME as parentname,v.FPROJECTID,v.FPROJECTNAME,v.funitid,v.funit,i.pro_type
from project_view v join project_info i on v.fprojectid=i.fprojectid
where v.`status`='S' and i.PRO_TYPE in ('S') and ( v.FUNITID like concat('S','S') or v.forgid = 'S')and v.projectcat='S'
) pro
left join (
select report1.* from
(select * from bp_projectreport bp
where bp.v_depttype = 'S'
and bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
order by bp.v_projectid, bp.v_filldate desc ) report1 group by report1.v_projectid
) report on pro.fprojectid = report.v_projectid
left join bp_projectreportdetail detail on detail.v_reportid = report.id
left JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S'
) g on g.fprojectid = o.fprojectid ) k where N= N
order by deptid,type,pro_type,orgid
Count: 1 Time=11.97s (11s) Lock=0.00s (0s) Rows=370378.0 (370378), myq[myq]@[172.16.0.1]
select r.id,r.v_projectid,r.v_filldate from bp_projectreport r where r.v_depttype='S'
order by r.v_projectid,r.v_filldate DESC
Count: 1 Time=70.42s (70s) Lock=0.00s (0s) Rows=368976.0 (368976), myq[myq]@[172.16.0.1]
select bp.* from bp_projectreport bp
where case when 'S' in (N) then bp.v_depttype in(N)
when 'S' in (N) then bp.v_depttype in(N)
when 'S' in (N) then bp.v_depttype in(N) end
and bp.v_unitid like concat('S','S')
and bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
order by bp.v_projectid, bp.v_filldate desc
Count: 1 Time=46.79s (46s) Lock=0.00s (0s) Rows=364236.0 (364236), myq[myq]@[172XX]
select p.id,p.v_projectid,p.v_filldate,p.v_reporttype
from bp_projectreport p
where p.v_status='S' and p.v_depttype='S' and p.v_filldate<=concat('S','S','S')
order by p.v_projectid,p.v_filldate desc
Count: 1 Time=19.03s (19s) Lock=0.00s (0s) Rows=362918.0 (362918), myq[myq]@[172XX]
select yb.id,p.v_filldate,p.v_projectid from bp_projectreportdetail yb join bp_projectreport p on yb.v_reportid=p.id
where p.v_depttype=N and v_tjxcode = 'S' and yb.v_filldate<='S'
Count: 2 Time=27.98s (55s) Lock=0.00s (0s) Rows=135532.0 (271064), myq[myq]@[隐藏175业务]
select deptid,deptid as orgid,deptname,N as pro_type,v_tjxcode,n_monthvalue,n_yearvalue,
n_totalvalue,n_monthplan,n_yearplan,n_totalplan,type from (
-- 部门
select e.deptid,e.deptname,r.v_tjxcode,r.n_monthvalue ,r.n_yearvalue,
r.n_totalvalue,r.n_monthplan,r.n_yearplan,r.n_totalplan ,N as type from
(select deptid ,deptname ,depttype from eadept ea where
ea.deptid like concat('S','S' ) and
case when 'S' in (N) then ea.depttype in(N)
when 'S' in (N) then ea.depttype in(N,N)
when 'S' in (N) then ea.depttype in(N) end
and ea.ENABLED = 'S' ) e
left join
(
select report.v_unitid,report.v_filldate ,report.v_depttype ,
detail.v_tjxcode,if(report.v_filldate = CONCAT('S', 'S','S'),detail.n_monthvalue,null) as n_monthvalue,
if(substr(report.v_filldate,N,N) = 'S' ,detail.n_yearvalue,null) as n_yearvalue,
detail.n_totalvalue,
if(report.v_filldate = CONCAT('S', 'S','S'),detail.n_monthplan,null) as n_monthplan,
if(substr(report.v_filldate,N,N) = 'S',detail.n_yearplan,null) as n_yearplan,
detail.n_totalplan
from (
select report1.* from
(select bp.* from bp_projectreport bp
where case when 'S' in (N) then bp.v_depttype in(N)
when 'S' in (N) then bp.v_depttype in(N,N)
when 'S' in (N) then bp.v_depttype in(N) end
and bp.v_unitid like concat('S','S')
-- and bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
order by bp.v_unitid, bp.v_filldate desc ) report1 group by report1.v_unitid) report
JOIN bp_projectreportdetail detail ON detail.v_reportid = report.id
JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S' ) r
on e.deptid = r.v_unitid and r.v_depttype = e.depttype
union all
-- 局指指挥部
select m.deptid,m.deptname,n.v_tjxcode,sum(n.n_monthvalue) as n_monthvalue,
sum(n.n_yearvalue) as n_yearvalue,sum(n.n_totalvalue) as n_totalvalue,
sum(n.n_monthplan) as n_monthplan,sum(n.n_yearplan) as n_yearplan,
sum(n.n_totalplan) as n_totalplan,N as type from (
select e.*,v.FPROJECTID from (select deptid ,deptname ,depttype from eadept ea where
ea.deptid like concat('S','S' ) and
case when 'S' in (N,N) then ea.depttype in ('S')
else N= N end
and ea.ENABLED = 'S' ) e
left join project_view v on v.FUNITID = e.deptid and e.depttype = N and v.projectcat = 'S'
) m
left join (
select u.PARENTPROJECTID,u.v_filldate,u.parentname ,u.v_tjxcode ,
sum(if(v_filldate = CONCAT('S', 'S','S'),u.n_monthvalue,null)) as n_monthvalue,
sum(if(substr(v_filldate,N,N) ='S',u.n_yearvalue,null)) as n_yearvalue,
sum(u.n_totalvalue) as n_totalvalue,
sum(if(v_filldate = CONCAT('S', 'S','S'),u.n_monthplan,null)) as n_monthplan,
sum(if(substr(v_filldate,N,N) ='S',u.n_yearplan,null)) as n_yearplan,
sum(u.n_totalplan) as n_totalplan
from (
select pro.*,report.v_unitid,report.v_projectid,report.v_depttype,report.v_filldate,detail.v_tjxcode,detail.v_tjxmc,detail.v_jldw,
detail.n_monthvalue ,detail.n_yearvalue,detail.n_totalvalue,detail.n_monthplan,detail.n_yearplan,detail.n_totalplan from (
select id,v_unitid,v_projectid,v_depttype,v_filldate from (
SELECT
id, v_unitid, v_projectid,v_depttype, v_filldate
FROM
bp_projectreport bp
WHERE
bp.v_depttype = 'S' and
bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
ORDER BY bp.v_projectid , bp.v_filldate DESC )
p group by v_unitid ,v_projectid , v_depttype
) report
join (
select v.FPROJECTID as parentprojectid,v.FPROJECTNAME as parentname,pi.FPROJECTID,pi.FPROJECTNAME,pi.funitid,pi.funit,pi.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
join project_info pi on v.FPROJECTID=pi.PARENTPROJECTID
where v.`status`='S' and i.PRO_TYPE='S' and (
v.FUNITID like concat('S','S' ) or v.forgid = 'S') and v.projectcat='S' and pi.pro_type ='S'
union ALL
select v.FPROJECTID as parentprojectid ,v.FPROJECTNAME,v.FPROJECTID,v.FPROJECTNAME,v.funitid,v.funit,v.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
where v.`status`='S' and i.PRO_TYPE in ('S') and ( v.FUNITID like concat('S','S') or v.forgid = 'S')and v.projectcat='S'
) pro on report.v_depttype ='S' and report.v_projectid = pro.FPROJECTID
JOIN bp_projectreportdetail detail ON detail.v_reportid = report.id
JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S' ) u group by u.parentprojectid , u.v_tjxcode
) n on n.PARENTPROJECTID = m.fprojectid
group by m.deptid ,n.v_tjxcode ) k
union all
-- 项目
select deptid ,orgid,fprojectname,pro_type,v_tjxcode,n_monthvalue,n_yearvalue,n_totalvalue,
n_monthplan,n_yearplan,n_totalplan,type from (
select o.deptid,o.fprojectid as orgid,g.fprojectname,pro_type,g.v_tjxcode,g.n_monthvalue,
g.n_yearvalue,g.n_totalvalue,g.n_monthplan,g.n_yearplan,g.n_totalplan ,N as type from (
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select v.fprojectid ,ea.deptid from project_view v join project_info i on v.fprojectid = i.fprojectid
join eadept ea on ea.depttype = N
and v.funitid like concat(ea.deptid,'S')
where i.pro_type = N and 'S' in (N,N) and ea.deptid like concat('S','S') and ea.enabled = 'S'
union all
select i1.fprojectid ,ea.deptid from eadept ea
join project_view v on ea.deptid = v.funitid
join project_info i on i.fprojectid = v.fprojectid and i.pro_type = 'S'
join project_info i1 on i1.PARENTPROJECTID = i.fprojectid and i1.pro_type = 'S'
where ea.deptid like concat('S','S') and 'S' in (N,N) and ea.depttype = N and ea.enabled = 'S'
) o left join (
-- 局指项目 (汇总自己跟工区的数据)
select t1.parentprojectid as fprojectid,pro_view.FPROJECTNAME,pro_view.funitid ,N as pro_type,t1.v_tjxcode , t1.n_monthvalue ,t1.n_yearvalue,
t1.n_totalvalue,t1.n_monthplan,t1.n_yearplan,t1.n_totalplan from (
select parentprojectid ,v_tjxcode,
sum(if(v_filldate = CONCAT('S', 'S','S'),n_monthvalue,null)) as n_monthvalue,
sum(if(substr(v_filldate ,N,N)= 'S',n_yearvalue,null)) as n_yearvalue,
sum(n_totalvalue) as n_totalvalue,
sum(if(v_filldate = CONCAT('S', 'S','S'),n_monthplan,null)) as n_monthplan,
sum(if(substr(v_filldate ,N,N)= 'S',n_yearplan,null)) as n_yearplan,
sum(n_totalplan) as n_totalplan,
N as type from (
select pro.parentprojectid ,pro.parentname,
pro.fprojectid,pro.fprojectname,pro.funit,pro.funitid,pro.fprojecttypecode,
report.id,report.v_filldate,
detail.v_tjxcode,detail.v_tjxmc,detail.n_monthvalue,detail.n_yearvalue,detail.n_totalvalue,
detail.n_monthplan,detail.n_yearplan,detail.n_totalplan
from
( select v.FPROJECTID as parentprojectid,v.FPROJECTNAME as parentname,pi.FPROJECTID,pi.FPROJECTNAME,pi.funitid,pi.funit,pi.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
join project_info pi on v.FPROJECTID=pi.PARENTPROJECTID
where v.`status`='S' and i.PRO_TYPE='S' and (
v.FUNITID like concat('S','S' ) or v.forgid = 'S') and v.projectcat='S' and pi.pro_type ='S'
union ALL
select v.FPROJECTID as parentprojectid ,v.FPROJECTNAME,v.FPROJECTID,v.FPROJECTNAME,v.funitid,v.funit,v.fprojecttypecode
from project_view v join project_info i on v.fprojectid=i.fprojectid
where v.`status`='S' and i.PRO_TYPE in ('S') and ( v.FUNITID like concat('S','S') or v.forgid = 'S')and v.projectcat='S'
) pro
left join (
select report1.* from
(select * from bp_projectreport bp
where bp.v_depttype = 'S'
and bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
order by bp.v_projectid, bp.v_filldate desc ) report1 group by report1.v_projectid
) report on pro.fprojectid = report.v_projectid
left join bp_projectreportdetail detail on detail.v_reportid = report.id
left JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S' ) p group by PARENTPROJECTID,v_tjxcode
) t1
join project_view pro_view on pro_view.fprojectid = t1.parentprojectid
union all
-- 工区 跟 标准项目数据
select
pro.fprojectid,pro.fprojectname,pro.funitid,pro.pro_type,
detail.v_tjxcode,
if(report.v_filldate = concat('S','S','S') ,detail.n_monthvalue,null ) as n_monthvalue,
if(substr(report.v_filldate,N,N) = 'S',detail.n_yearvalue,null) as n_yearvalue,
detail.n_totalvalue,
if(report.v_filldate = concat('S','S','S') ,detail.n_monthplan,null ) as n_monthplan,
if(substr(report.v_filldate,N,N) = 'S',detail.n_yearplan,null) as n_yearplan,
detail.n_totalplan
from
( select
v.FPROJECTID as parentprojectid,v.FPROJECTNAME as parentname,pi.FPROJECTID,pi.FPROJECTNAME,pi.funitid,pi.funit,pi.pro_type
from project_view v join project_info i on v.fprojectid=i.fprojectid
join project_info pi on v.FPROJECTID=pi.PARENTPROJECTID
where v.`status`='S' and i.PRO_TYPE='S' and (
case when 'S' in('S') then pi.FUNITID else v.FUNITID end like concat('S','S' ) or v.forgid = 'S') and v.projectcat='S' and pi.pro_type ='S'
union all
select v.FPROJECTID as parentprojectid ,v.FPROJECTNAME as parentname,v.FPROJECTID,v.FPROJECTNAME,v.funitid,v.funit,i.pro_type
from project_view v join project_info i on v.fprojectid=i.fprojectid
where v.`status`='S' and i.PRO_TYPE in ('S') and ( v.FUNITID like concat('S','S') or v.forgid = 'S')and v.projectcat='S'
) pro
left join (
select report1.* from
(select * from bp_projectreport bp
where bp.v_depttype = 'S'
and bp.v_status = 'S'
and bp.v_filldate <= concat('S','S','S')
order by bp.v_projectid, bp.v_filldate desc ) report1 group by report1.v_projectid
) report on pro.fprojectid = report.v_projectid
left join bp_projectreportdetail detail on detail.v_reportid = report.id
left JOIN bp_report_tjx ret ON INSTR(CONCAT('S', ret.v_tjxcode, 'S'), CONCAT('S', detail.v_tjxcode, 'S')) > N
AND ret.v_userid ='S'
AND ret.v_report_code = 'S'
) g on g.fprojectid = o.fprojectid ) k where N= N
order by deptid,type,pro_type,orgid
Count: 1 Time=7.92s (7s) Lock=0.00s (0s) Rows=195551.0 (195551), myq[myq]@[172.16.0.1]
select d.n_monthvalue,main.v_reporttype,main.v_filldate,d.v_tjxcode from (
select r.id,r.v_reporttype,r.v_filldate from bp_projectreport r
where r.v_filldate<='S' and r.v_depttype='S' and r.v_unitid like 'S'
and r.v_status='S') main
join bp_projectreportdetail d on main.id=d.v_reportid -- group by main.v_reporttype,d.v_tjxcode
Count: 1 Time=4.13s (4s) Lock=0.00s (0s) Rows=195551.0 (195551), myq[myq]@[172.16.0.1]
select if(main.v_filldate='S',d.n_monthvalue,null),main.v_reporttype,main.v_filldate from (
select r.id,r.v_reporttype,r.v_filldate from bp_projectreport r
where r.v_filldate<='S' and r.v_depttype='S' and r.v_unitid like 'S'
and r.v_status='S') main
join bp_projectreportdetail d on main.id=d.v_reportid -- group by main.v_reporttype,d.v_tjxcode
Count: 1 Time=3.88s (3s) Lock=0.00s (0s) Rows=195551.0 (195551), myq[myq]@[172XX]
select d.n_monthvalue,main.v_reporttype,main.v_filldate from (
select r.id,r.v_reporttype,r.v_filldate from bp_projectreport r
where r.v_filldate<='S' and r.v_depttype='S' and r.v_unitid like 'S'
and r.v_status='S') main
join bp_projectreportdetail d on main.id=d.v_reportid -- group by main.v_reporttype,d.v_tjxcode
Count: 4 Time=9.47s (37s) Lock=0.00s (0s) Rows=40517.0 (162068), myq[myq]@[172XX]
SELECT * FROM `bp_chartdata`
Count: 1 Time=11.38s (11s) Lock=0.00s (0s) Rows=82505.0 (82505), myq[myq]@[172XX]
SELECT * FROM `bp_projectreportdetail`
Count: 1 Time=233.77s (233s) Lock=0.00s (0s) Rows=79618.0 (79618), myq[myq]@[172XX]
select *
from bp_projectreport r
order by r.v_filldate
Count: 1 Time=20.99s (20s) Lock=0.00s (0s) Rows=71584.0 (71584), myq[myq]@[172XX]
select r.id,r.v_unitid,r.v_filldate,r.v_depttype,r.v_projectid
from bp_projectreport r
where r.v_status='S' and r.v_filldate<= concat('S','S','S')
and r.v_unitid like CONCAT('S','S')
and r.v_DEPTTYPE='S'
order by r.v_unitid,r.v_filldate desc
Count: 1 Time=10.51s (10s) Lock=0.00s (0s) Rows=69214.0 (69214), myq[myq]@[172XX]
select * from bp_projectreportdetail d
where d.n_monthvalue*N - floor(d.n_monthvalue)*N = N and d.v_tjxcode='S'
Count: 1 Time=58.11s (58s) Lock=0.00s (0s) Rows=62586.0 (62586), myq[myq]@[172XX]
SELECT * FROM `bp_stat_design_detail`
Count: 1 Time=13.81s (13s) Lock=0.00s (0s) Rows=40099.0 (40099), myq[myq]@[172XX]
select *
from bp_projectreport r
where r.v_depttype='S' and r.bistype is not null
