报表数据查询条件过多，导致数据库cpu占用率到99%

SELECT a.ztName,a.machID,a.machName,a.code,a.oeeData,a.gatherYear,a.runtime,a.DiffData,b.outs,IFNULL(b.qualifiedWeight,0) qualifiedWeight,IFNULL(b.qualifiedaMount,0) qualifiedaMount
FROM
(
SELECT z.ztName,oee.machID,m.machName,m.code,oee.oeeData,oee.gatherYear,oee.runtime,IFNULL(sum(e.DiffData),0.00) as DiffData,IFNULL(SUM(o.qualifiedaMount),0.00),IFNULL(SUM(o.qualifiedWeight),0.00)
FROM i_plc_zutai z LEFT JOIN i_plc_machine m ON m.operationFlag=9 and z.id=m.ztID
LEFT JOIN i_plc_statistics_oee oee ON oee.operationFlag=9 and m.id=oee.machID and oee.gatherYear!=''
LEFT JOIN i_plc_equipment1summary e ON e.operationFlag=9 AND e.operationValue='电量'
and oee.gatherYear=SUBSTR(e.gatherDate FROM 1 FOR 4) and oee.machID=e.machID and m.id=e.machID
LEFT JOIN i_plc_report_order o on o.machinecode = m.code
WHERE z.operationFlag=9
and m.code in ('002-11B-01','002-11B-02','002-11B-03','002-11B-04','002-14B-01','002-14B-02','002-24B-03','002-14B-03','002-19B-02','002-24B-01','002-24B-02','002-19B-01','002-19B-03',
'002-19B-04','002-14B-04','002-14B-05','002-14B-06','002-14B-07','004-M10-20','004-M10-22','004-M10-23','004-M10-24','004-M10-25','004-M10-27','004-M10-28','004-M12-07','004-M12-08','004-M12-09',
'004-M12-10','004-M12-11','004-M12-12','004-M12-13','004-M12-14','004-M16-01','004-M16-02','004-M16-03','004-M16-04','004-M16-05','004-M16-06','004-M20-01','004-M20-02','004-M20-03','004-M22-01JJ',
'004-M22-02JJ','004-M22-03JJ','004-M27-01','004-M27-02','004-M30-06JJ','004-M30-07JJ','004-M48-04JJ','004-M48-05JJ','001-63S-01C','001-103S-01C','001-103S-02C','001-103S-03C','001-103S-04C',
'001-105S-01C','001-104S-01C','001-104S-02C','001-104S-03C','001-104S-05D','包装','001-84S-01C','001-64S-01D','001-83S-01C','001-84S-02C','001-84S-03C','001-83S-02C','001-83S-03C','001-84S-05D',
'001-84S-06D','001-84S-07D','001-47-16-01DL','001-47-16-02DL','001-47-16-03DL','001-47-20-01DL','001-47-20-02DL','001-12-01D','001-12-02D','001-12-03D','001-12-04D','001-12-05D','001-12-06D',
'003-12B-03','003-12B-04','003-12B-05','003-6B-01','003-6B-02','003-GCS8B-01','003-GCS8B-02','003-GCS8B-03','003-GCS8B-04','003-GCS8B-05','003-GCS8B-06','003-GCS8B-07','003-GCS-8B-08','003-GCS8B-09',
'003-GCS-8B-10','003-GCS8B-11','003-GCS8B-12','003-GCS8B-13','003-GCS8B-14','015-45-1','015-45-2','RD-J23/63','LSRCL-WDL-SX-01','004-M24-01','LMGY-M6','LSGS-54-20-1','LSGS-54-20-2','LSCX-12L-2S-01',
'LSCX-20-3S-2-01','003-M16-01','003-M16-02','003-M20-01','LSCS-M16-3','001-20L-01','005-6130-01','005-6130-02','005-6130-03','005-6130-04','005-6130-05','005-6130-06','005-0640-001','005-0640-002',
'005-0640-003','005-0640-004','005-0640-005','005-0640-006','005-0640-007','005-0640-008','005-0640-009','005-0640-010','005-0640-011','005-0640-012','005-0640-013','005-0640-014','005-0640-015',
'005-0640-016','005-0640-017','005-0640-018','005-0640-019','005-0640-020','005-0640-021','005-0640-022','005-0640-023','005-0640-024','005-0640-025','005-0640-026','005-350-01','005-350-02',
'005-6150-01','005-6150-02','机加工委外','拉丝外协','041-LH-01','024-YH-01','024-YH-02','020-GP-3','YX-YXJ','020-GP-1','020-GP-2','汽标包装','WG01','06-JB21-160-01','06-JB21-160-02','06-JB21-160-03',
'06-JB21-160-04','06-JB21-160-05','06-JS31-160-01','06-TCP200-01','06-TCP200-02','06-J121-125-01','06-J121-125-02','06-J21-250-01','06-25-01','06-25-02','06-25-03','06-25-04','06-25-05','06-25-06',
'06-35-01','06-63-01','06-63-02','06-35-02','004-M10-19','005-6130-11','表面处理HDG-04','019-GU-01','019-GU-02','019-GU-03','044-DK-01','044-DK-02','022-PW-01','022-PW-02','0430-2500-04','043-2300-03',
'007-806-01','007-806-02','007-806-03','001-254S-01D','002-33B-5S-01YX','002-33B-6S-02ZY','002-41B-6S-01ZY','002-41B-6S-02YX','001-64S-02D','001-64S-03D','001-104L-01C','001-134L-01C','001-134L-02C',
'001-135L-01C','001-165-01C','001-204L-01C','001-204L-02C','001-204L-03D','013-1040A-01JZ','013-1040A-02JZ','013-1050A-01YJ','013-1050A-02YJ','013-1040A-03JZ','013-1040A-04JZ','013-1040A-05JZ','013-1040B-06NY',
'013-1040B-07NY','013-1040B-08NY','013-1050A-03YJ','TX-PSG/2300/C4','001-Z12-30','ZK-Z5132A','017-4025-1','017-4025-2','017-4230-02','017-4232-01','DL-GSYJ/75','DL-J21/100-1','DL-J21/100-2','DL-YG/60','001-Z12-20',
'001-Z23-30-1','001-Z23-30-2','002-46B-01','009-20-1','009-20-2','009-20-3','009-15-1','009-15-2','009-15-3','009-15-5','009-20-4','015-075-1','015-075-2','009-15-6','009-15-7','009-15-8','009-15-9','015-32-1','009-15-10',
'015-32-2','009-15-11','009-15-12','009-15-13','009-15-14','LSCX-30L-2S-01','LSCX-12-3S-2-01','025-800A7','表面处理HDG-05','浙江科腾','浙江帼瑞实业公司','海盐常绿标准件有限公司','浙江超博尔五金有限公司','009-15-15',
'013-1040B-02NY','013-1040B-03NY','013-1040B-04NY','013-1040B-05NY','022-PW-03','013-1040A-01YJ','013-1040B-01NY','005-6130-12','005-6130-13','005-6130-14','005-6136-01','007-CHL-01ZT','007-CHL-02ZT','007-CHL-03ZT',
'007-HUL-01YK','007-HUL-02ZT','007-QXL-01ZT','003-075-01','003-16-03WX','003-16S-01C','003-16S-02C','003-M20C-01WX','003-M20C-02C','003-M20C-03C','003-M20C-04WX','005-6130-09','005-6130-10','004-M5-29','004-M5-31',
'001-103L-01C','001-84S-04D','001-103S-02H','001-84S-08C','001-103S-01H','024-YH-03','028-FR-01','029-B-01','029-D-01','029-F-01','029-K-01','029-T-01','005-40-01','005-4085-01','005-4085-02','005-5060-01','005-5060-02',
'005-6130-07','005-6130-08','003-GCS08-06','003-GCS08-07','003-GCS08-08','003-GCS10-01','001-104S-04D','041-QH-02','004-M5-32','004-M6-30','004-M6-34','004-M6-35','004-M8-15','004-M8-16','004-M8-17','004-M8-18',
'004-M8-21','004-M8-26','004-M8-33','003-GCS8-03','003-GCS10-02','003-GCS6B-03','003-GCS6B-04','003-GCS-8-01','003-GCS8-02','813','003-12B-07','003-12B-08','003-12B-09','003-12B-10','003-12B-11','003-12B-12',
'003-12B-13','003-GCS08-04','003-GCS08-05','009-15-4','801','001-254S-02D','811','808','809','810','807','802','041-QH-01','041-QH-03','041-QH-04','041-QH-05','041-QH-06','003-12B-06','803','804','001-164-01D',
'806','812','001-84S-09C','001-305L-01C','001-164-02C','001-164-03C','805','040-SI-01','040-SJ-02','040-SJ-03','040-SJ-04','040-SJ-05','040-SJ-06','040-SJ-07','040-SJ-08','001-52L-01','LSCX-20L-2S-01','中岳热处理')
and z.ztName in ('螺帽车间','机加工车间','鼎盛成型2#车间','热处理车间','鸿亿电镀线','自动烘打线','汽标热处理车间','包装车间','汽标成型车间','达克罗车间','汽标磷化车间','热成型车间','螺杆电镀车间','螺杆调直车间',
'鼎盛成型3-1#车间','鼎盛成型3-2#车间','球化炉车间','多用炉车间','挂镀车间','工厂管家','鼎盛成型车间','热墩车间','包装生产线','其他车间','处理','表面处理车间','拉丝车间','汽标包装车间','滚丝车间','立体库','自动包装线',
'鼎盛热处理车间','螺杆车间','委外','双击车间')
GROUP BY z.ztName,oee.machID,m.machName,m.code,oee.oeeData,oee.gatherYear
) as a,
(
SELECT z.ztName,oee.machID,m.machName,m.code,oee.oeeData,oee.gatherYear,IFNULL(sum(e.DiffData),0.00) as outs,ROUND(SUM(o.qualifiedaMount),3) qualifiedaMount,ROUND(SUM(o.qualifiedWeight),3) qualifiedWeight
FROM i_plc_zutai z LEFT JOIN i_plc_machine m ON m.operationFlag=9 and z.id=m.ztID
LEFT JOIN i_plc_statistics_oee oee ON oee.operationFlag=9 and m.id=oee.machID and oee.gatherYear!=''
LEFT JOIN i_plc_equipment1summary e ON e.operationFlag=9 AND
e.operationValue='产量'
and oee.gatherYear=SUBSTR(e.gatherDate FROM 1 FOR 4) and oee.machID=e.machID and m.id=e.machID
LEFT JOIN i_plc_report_order o on o.machinecode = m.code
WHERE z.operationFlag=9
and z.ztName in ('螺帽车间','机加工车间','鼎盛成型2#车间','热处理车间','鸿亿电镀线','自动烘打线','汽标热处理车间','包装车间','汽标成型车间','达克罗车间','汽标磷化车间','热成型车间',
'螺杆电镀车间','螺杆调直车间','鼎盛成型3-1#车间','鼎盛成型3-2#车间','球化炉车间','多用炉车间','挂镀车间','工厂管家','鼎盛成型车间','热墩车间','包装生产线','其他车间','处理','表面处理车间',
'拉丝车间','汽标包装车间','滚丝车间','立体库','自动包装线','鼎盛热处理车间','螺杆车间','委外','双击车间')
and m.code in ('002-11B-01','002-11B-02','002-11B-03','002-11B-04','002-14B-01','002-14B-02','002-24B-03','002-14B-03','002-19B-02','002-24B-01','002-24B-02','002-19B-01','002-19B-03',
'002-19B-04','002-14B-04','002-14B-05','002-14B-06','002-14B-07','004-M10-20','004-M10-22','004-M10-23','004-M10-24','004-M10-25','004-M10-27','004-M10-28','004-M12-07','004-M12-08',
'004-M12-09','004-M12-10','004-M12-11','004-M12-12','004-M12-13','004-M12-14','004-M16-01','004-M16-02','004-M16-03','004-M16-04','004-M16-05','004-M16-06','004-M20-01','004-M20-02',
'004-M20-03','004-M22-01JJ','004-M22-02JJ','004-M22-03JJ','004-M27-01','004-M27-02','004-M30-06JJ','004-M30-07JJ','004-M48-04JJ','004-M48-05JJ','001-63S-01C','001-103S-01C','001-103S-02C',
'001-103S-03C','001-103S-04C','001-105S-01C','001-104S-01C','001-104S-02C','001-104S-03C','001-104S-05D','包装','001-84S-01C','001-64S-01D','001-83S-01C','001-84S-02C','001-84S-03C',
'001-83S-02C','001-83S-03C','001-84S-05D','001-84S-06D','001-84S-07D','001-47-16-01DL','001-47-16-02DL','001-47-16-03DL','001-47-20-01DL','001-47-20-02DL','001-12-01D','001-12-02D',
'001-12-03D','001-12-04D','001-12-05D','001-12-06D','003-12B-03','003-12B-04','003-12B-05','003-6B-01','003-6B-02','003-GCS8B-01','003-GCS8B-02','003-GCS8B-03','003-GCS8B-04','003-GCS8B-05',
'003-GCS8B-06','003-GCS8B-07','003-GCS-8B-08','003-GCS8B-09','003-GCS-8B-10','003-GCS8B-11','003-GCS8B-12','003-GCS8B-13','003-GCS8B-14','015-45-1','015-45-2','RD-J23/63','LSRCL-WDL-SX-01',
'004-M24-01','LMGY-M6','LSGS-54-20-1','LSGS-54-20-2','LSCX-12L-2S-01','LSCX-20-3S-2-01','003-M16-01','003-M16-02','003-M20-01','LSCS-M16-3','001-20L-01','005-6130-01','005-6130-02','005-6130-03',
'005-6130-04','005-6130-05','005-6130-06','005-0640-001','005-0640-002','005-0640-003','005-0640-004','005-0640-005','005-0640-006','005-0640-007','005-0640-008','005-0640-009','005-0640-010',
'005-0640-011','005-0640-012','005-0640-013','005-0640-014','005-0640-015','005-0640-016','005-0640-017','005-0640-018','005-0640-019','005-0640-020','005-0640-021','005-0640-022','005-0640-023',
'005-0640-024','005-0640-025','005-0640-026','005-350-01','005-350-02','005-6150-01','005-6150-02','机加工委外','拉丝外协','041-LH-01','024-YH-01','024-YH-02','020-GP-3','YX-YXJ','020-GP-1',
'020-GP-2','汽标包装','WG01','06-JB21-160-01','06-JB21-160-02','06-JB21-160-03','06-JB21-160-04','06-JB21-160-05','06-JS31-160-01','06-TCP200-01','06-TCP200-02','06-J121-125-01','06-J121-125-02',
'06-J21-250-01','06-25-01','06-25-02','06-25-03','06-25-04','06-25-05','06-25-06','06-35-01','06-63-01','06-63-02','06-35-02','004-M10-19','005-6130-11','表面处理HDG-04','019-GU-01','019-GU-02',
'019-GU-03','044-DK-01','044-DK-02','022-PW-01','022-PW-02','0430-2500-04','043-2300-03','007-806-01','007-806-02','007-806-03','001-254S-01D','002-33B-5S-01YX','002-33B-6S-02ZY','002-41B-6S-01ZY',
'002-41B-6S-02YX','001-64S-02D','001-64S-03D','001-104L-01C','001-134L-01C','001-134L-02C','001-135L-01C','001-165-01C','001-204L-01C','001-204L-02C','001-204L-03D','013-1040A-01JZ','013-1040A-02JZ',
'013-1050A-01YJ','013-1050A-02YJ','013-1040A-03JZ','013-1040A-04JZ','013-1040A-05JZ','013-1040B-06NY','013-1040B-07NY','013-1040B-08NY','013-1050A-03YJ','TX-PSG/2300/C4','001-Z12-30','ZK-Z5132A',
'017-4025-1','017-4025-2','017-4230-02','017-4232-01','DL-GSYJ/75','DL-J21/100-1','DL-J21/100-2','DL-YG/60','001-Z12-20','001-Z23-30-1','001-Z23-30-2','002-46B-01','009-20-1','009-20-2','009-20-3',
'009-15-1','009-15-2','009-15-3','009-15-5','009-20-4','015-075-1','015-075-2','009-15-6','009-15-7','009-15-8','009-15-9','015-32-1','009-15-10','015-32-2','009-15-11','009-15-12','009-15-13','009-15-14',
'LSCX-30L-2S-01','LSCX-12-3S-2-01','025-800A7','表面处理HDG-05','浙江科腾','浙江帼瑞实业公司','海盐常绿标准件有限公司','浙江超博尔五金有限公司','009-15-15','013-1040B-02NY','013-1040B-03NY','013-1040B-04NY',
'013-1040B-05NY','022-PW-03','013-1040A-01YJ','013-1040B-01NY','005-6130-12','005-6130-13','005-6130-14','005-6136-01','007-CHL-01ZT','007-CHL-02ZT','007-CHL-03ZT','007-HUL-01YK','007-HUL-02ZT','007-QXL-01ZT',
'003-075-01','003-16-03WX','003-16S-01C','003-16S-02C','003-M20C-01WX','003-M20C-02C','003-M20C-03C','003-M20C-04WX','005-6130-09','005-6130-10','004-M5-29','004-M5-31','001-103L-01C','001-84S-04D','001-103S-02H',
'001-84S-08C','001-103S-01H','024-YH-03','028-FR-01','029-B-01','029-D-01','029-F-01','029-K-01','029-T-01','005-40-01','005-4085-01','005-4085-02','005-5060-01','005-5060-02','005-6130-07','005-6130-08','003-GCS08-06',
'003-GCS08-07','003-GCS08-08','003-GCS10-01','001-104S-04D','041-QH-02','004-M5-32','004-M6-30','004-M6-34','004-M6-35','004-M8-15','004-M8-16','004-M8-17','004-M8-18','004-M8-21','004-M8-26','004-M8-33','003-GCS8-03',
'003-GCS10-02','003-GCS6B-03','003-GCS6B-04','003-GCS-8-01','003-GCS8-02','813','003-12B-07','003-12B-08','003-12B-09','003-12B-10','003-12B-11','003-12B-12','003-12B-13','003-GCS08-04','003-GCS08-05','009-15-4','801',
'001-254S-02D','811','808','809','810','807','802','041-QH-01','041-QH-03','041-QH-04','041-QH-05','041-QH-06','003-12B-06','803','804','001-164-01D','806','812','001-84S-09C','001-305L-01C','001-164-02C','001-164-03C',
'805','040-SI-01','040-SJ-02','040-SJ-03','040-SJ-04','040-SJ-05','040-SJ-06','040-SJ-07','040-SJ-08','001-52L-01','LSCX-20L-2S-01','中岳热处理')
GROUP BY z.ztName,oee.machID,m.machName,m.code,oee.oeeData,oee.gatherYear
) as b
WHERE a.machID=b.machID
and a.gatherYear>= ('2017')
and a.gatherYear>= ('2017')
and a.gatherYear=b.gatherYear
GROUP BY a.ztName,a.machID,a.machName,a.code,a.oeeData,a.gatherYear
ORDER  BY a.gatherYear;


红色的查询sql里面   in的值太多，加上要取两种值，电量，产量，，就as了两张表，导致mysql查询很慢，一个没查完，其他的全在等待，服务器mysql cpu占用率99%
in的值是报表下拉复选框，全选后的所有值
下拉复选框全选这种用过滤做本质上是不是也是用in的？

请教各位大神有遇到这种问题的没有，有没有什么好的解决方法
FineReporthgyin 发布于 2017-11-21 12:13
回答问题
悬赏：2 F币 + 添加悬赏
举报
提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知
共12回答
最佳回答
0
星痕发布于2017-11-21 12:14（编辑于 2023-9-6 09:34）
555
最佳回答
0
yiyemeiying发布于2017-11-21 12:16（编辑于 2023-9-6 09:34）
555
最佳回答
0
hgyin发布于2017-11-21 12:19（编辑于 2023-9-6 09:34）
555
最佳回答
0
Poseidon发布于2017-11-21 13:11（编辑于 2023-9-6 09:34）
555
举报 收起评论(4)
  • 星痕 星痕 200多万行的sql？你在逗我吗？是从上辈子开始写的吗？？？
    回复
    2017-11-21 13:46 
  • Poseidon Poseidon 回复 星痕 :你好像啥，看不到是用FOR循环拼接出来的么
    回复
    2017-11-21 13:47 
  • 星痕 星痕 回复 Poseidon :for循环拼接的200W行sql....大佬我服，，，带带我
    回复
    2017-11-21 13:49 
  • Poseidon Poseidon 回复 星痕 :滴，你的智商不足请及时缴费
    回复
    2017-11-21 13:58 
最佳回答
0
hgyin发布于2017-11-21 13:15（编辑于 2023-9-6 09:34）
555
举报 收起评论(2)
  • yiyemeiying yiyemeiying 而且你的a和b用的是全连接？这样好吗。。。从细节开始吧~~~
    回复
    2017-11-21 13:26 
  • hgyin hgyin(提问者) 回复 yiyemeiying :是a，b每个里面的条件太多了吗？还是说我把a，b里面重复的条件放到最外面，
    回复
    2017-11-21 13:44 
最佳回答
0
hgyin发布于2017-11-21 13:16（编辑于 2023-9-6 09:34）
555
  • 0关注人数
  • 459浏览人数
    • 最后回答于：2017-11-21 13:58
    活动推荐 更多
    热门课程 更多
    隐私政策
    帆软用户服务协议
    联系管理员
    联系帆软
    Copyright© 帆软软件有限公司 版权所有
    苏ICP备18065767号
    返回顶部