我有一个查询使用大量的COUNT和SUM。它拥有大量记录，因此查询速度非常慢（大约1秒/ 300日志），并且经常内存不足。在我的查询中，一个表可以使用不同的WHERE条件进行多次查询。有没有优化的方法？
SELECT
Waiting.w_waiting, Active.w_accept, Cancel.w_cancel,
Notwork.w_notwork,Inwork.w_inwork,Precheck.w_precheck,
Task.id,Task.case_id,Task.customer_id,Task.created,Task.interpreter_id,Task.high_light,Task.is_test,
Task.redo,Task.deliveryProduction,Task.accept_assign,Task.qc_checking_id,
Task.interpreter_id,Task.check_interpreter,Task.jobTitle,Task.amount,Task.isExpress,
Task.is_ready,Task.status,Task.vip_job,Task.is_final_assigned,Task.sub_status,
Task.jobInfo,Task.jobInfoProduction,Task.jobInfo_trans,Task.jobInfoProduction_trans,Task.customer_id,
Qc_waitcheck.waitcheck,Qc_done.qc_done,
Qc_ready.qc_ready,Qc_redo.qc_redo,Qc_redo_done.qc_redo_done,
W_inwork.inwork,Task.customer_workflow_id,Task.workflow_activated,Task.superqc,Task.is_temp_stop,
COALESCE(Upload_New, 0) AS Upload_New, COALESCE(Upload_Int, 0) AS Upload_Int, COALESCE(Upload_Ext, 0) AS Upload_Ext,
COALESCE(Upload_All, 0) AS Upload_All, COALESCE(Accepted_Files, 0) AS Accepted_Files, COALESCE(Check_Int, 0) AS Check_Int,
COALESCE(Check_Ext, 0) AS Check_Ext, COALESCE(All_Files, 0) AS All_Files
FROM tasks AS Task
LEFT JOIN staff_jobs AS SJ ON SJ.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_waiting, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.is_waiting = 1
GROUP BY SJ.task_id
) AS Waiting ON Waiting.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_accept, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.is_waiting = 0 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Active ON Active.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_cancel, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.is_waiting = 0 AND SJ.actived = 0
GROUP BY SJ.task_id
) AS Cancel ON Cancel.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_notwork, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.status = 0 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Notwork ON Notwork.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_inwork, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.status = 1 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Inwork ON Inwork.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_precheck, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.status = 3 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Precheck ON Precheck.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(StaffJob.id) AS inwork, StaffJob.task_id AS task_id
FROM staff_jobs AS StaffJob
WHERE StaffJob.status >= 3
GROUP BY StaffJob.task_id
) AS W_inwork ON W_inwork.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(StaffPic.id) AS waitcheck, StaffPic.task_id AS task_id
FROM staff_pics AS StaffPic
WHERE StaffPic.status = 3
GROUP BY StaffPic.task_id
) AS Qc_waitcheck ON Qc_waitcheck.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(BRDONE.id) AS qc_done, BRDONE.task_id AS task_id
FROM br24dones AS BRDONE
WHERE BRDONE.rating IS NOT NULL AND BRDONE.rating > 0
GROUP BY BRDONE.task_id
) AS Qc_done ON Qc_done.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(BRDONE.id) AS qc_ready, BRDONE.task_id AS task_id
FROM br24dones AS BRDONE
WHERE BRDONE.is_ready = 1
GROUP BY BRDONE.task_id
) AS Qc_ready ON Qc_ready.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(StaffPic.id) AS qc_redo, StaffPic.task_id AS task_id
FROM staff_pics AS StaffPic
WHERE StaffPic.redo > 0
GROUP BY StaffPic.task_id
) AS Qc_redo ON Qc_redo.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(StaffPic.id) AS qc_redo_done, StaffPic.task_id AS task_id
FROM staff_pics AS StaffPic
WHERE StaffPic.redo > 0 AND StaffPic.status = 3
GROUP BY StaffPic.task_id
) AS Qc_redo_done ON Qc_redo_done.task_id = Task.id
LEFT JOIN
(SELECT SUM(Upload_New) AS Upload_New, StaffJob.task_id AS task_id FROM staff_jobs AS StaffJob
LEFT JOIN
(SELECT COUNT(OP.id) AS Upload_New, OP.job_id AS task_id FROM outputs AS OP
GROUP BY OP.staff_job_id, OP.job_id) AS Upload_New
ON Upload_New.task_id = StaffJob.task_id
WHERE StaffJob.type = 0
GROUP BY StaffJob.task_id) AS Upload_New
ON Upload_New.task_id = Task.id
LEFT JOIN
(SELECT SUM(Upload_Int) AS Upload_Int, StaffJob.task_id AS task_id FROM staff_jobs AS StaffJob
LEFT JOIN
(SELECT COUNT(OP.id) AS Upload_Int, OP.job_id AS task_id FROM outputs AS OP
GROUP BY OP.staff_job_id, OP.job_id) AS Upload_Int
ON Upload_Int.task_id = StaffJob.task_id
WHERE StaffJob.type = 1
GROUP BY StaffJob.task_id) AS Upload_Int
ON Upload_Int.task_id = Task.id
LEFT JOIN
(SELECT SUM(Upload_Ext) AS Upload_Ext, StaffJob.task_id AS task_id FROM staff_jobs AS StaffJob
LEFT JOIN
(SELECT COUNT(OP.id) AS Upload_Ext, OP.job_id AS task_id FROM outputs AS OP
GROUP BY OP.staff_job_id, OP.job_id) AS Upload_Ext
ON Upload_Ext.task_id = StaffJob.task_id
WHERE StaffJob.type = 2
GROUP BY StaffJob.task_id) AS Upload_Ext
ON Upload_Ext.task_id = Task.id
LEFT JOIN
(SELECT Upload_All AS Upload_All, StaffJob.task_id AS task_id FROM staff_jobs AS StaffJob
LEFT JOIN
(SELECT COUNT(OP.id) AS Upload_All, OP.job_id AS task_id FROM outputs AS OP
GROUP BY OP.job_id) AS Upload_All
ON Upload_All.task_id = StaffJob.task_id
) AS Upload_All
ON Upload_All.task_id = Task.id
LEFT JOIN
(SELECT COUNT(BR_CHECK.id) AS Accepted_Files, BR_CHECK.task_id AS task_id FROM br24dones AS BR_CHECK
WHERE BR_CHECK.intern_redo_id IS NULL AND BR_CHECK.extern_redo_id IS NULL GROUP BY BR_CHECK.task_id) AS CHECKING
ON CHECKING.task_id = Task.id
LEFT JOIN
(SELECT COUNT(BR_INT.id) AS Check_Int, BR_INT.task_id AS task_id FROM br24dones AS BR_INT
WHERE BR_INT.intern_redo_id IS NOT NULL GROUP BY BR_INT.task_id) AS INTERNAL
ON INTERNAL.task_id = Task.id
LEFT JOIN
(SELECT COUNT(BR_EXT.id) AS Check_Ext, BR_EXT.task_id AS task_id FROM br24dones AS BR_EXT
WHERE BR_EXT.extern_redo_id IS NOT NULL GROUP BY BR_EXT.task_id) AS EXTERNAL
ON EXTERNAL.task_id = Task.id
LEFT JOIN
(SELECT COUNT(BR_ALL.id) AS All_Files, BR_ALL.task_id AS task_id FROM br24dones AS BR_ALL
GROUP BY BR_ALL.task_id) AS DONE_ALL
ON DONE_ALL.task_id = Task.id GROUP BY Task.id
最佳答案
是的很多。
您可以删除很多重复的联接。只需加入一次，然后再进行小组讨论。对于select中的计数，您可以使用if语句仅对状态正确的计数。
我没有信息，也没有时间向您全面介绍，但是您必须减少子查询。请记住，对于每一行，所有这些子查询都将被执行（如果子查询中有另一个子查询，则这些数字加起来会很大。）
参加前几个加入
SELECT
Waiting.w_waiting, Active.w_accept, Cancel.w_cancel,
Notwork.w_notwork,Inwork.w_inwork,Precheck.w_precheck,
Task.id,Task.case_id,Task.customer_id,Task.created,Task.interpreter_id,Task.high_light,Task.is_test,
Task.redo,Task.deliveryProduction,Task.accept_assign,Task.qc_checking_id,
Task.interpreter_id,Task.check_interpreter,Task.jobTitle,Task.amount,Task.isExpress,
Task.is_ready,Task.status,Task.vip_job,Task.is_final_assigned,Task.sub_status,
Task.jobInfo,Task.jobInfoProduction,Task.jobInfo_trans,Task.jobInfoProduction_trans,Task.customer_id,
Qc_waitcheck.waitcheck,Qc_done.qc_done,
Qc_ready.qc_ready,Qc_redo.qc_redo,Qc_redo_done.qc_redo_done,
W_inwork.inwork,Task.customer_workflow_id,Task.workflow_activated,Task.superqc,Task.is_temp_stop,
COALESCE(Upload_New, 0) AS Upload_New, COALESCE(Upload_Int, 0) AS Upload_Int, COALESCE(Upload_Ext, 0) AS Upload_Ext,
COALESCE(Upload_All, 0) AS Upload_All, COALESCE(Accepted_Files, 0) AS Accepted_Files, COALESCE(Check_Int, 0) AS Check_Int,
COALESCE(Check_Ext, 0) AS Check_Ext, COALESCE(All_Files, 0) AS All_Files
FROM tasks AS Task
LEFT JOIN staff_jobs AS SJ ON SJ.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_waiting, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.is_waiting = 1
GROUP BY SJ.task_id
) AS Waiting ON Waiting.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_accept, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.is_waiting = 0 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Active ON Active.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_cancel, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.is_waiting = 0 AND SJ.actived = 0
GROUP BY SJ.task_id
) AS Cancel ON Cancel.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_notwork, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.status = 0 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Notwork ON Notwork.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_inwork, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.status = 1 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Inwork ON Inwork.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(SJ.id) AS w_precheck, SJ.task_id AS task_id
FROM staff_jobs AS SJ
WHERE SJ.status = 3 AND SJ.actived = 1
GROUP BY SJ.task_id
) AS Precheck ON Precheck.task_id = Task.id
LEFT JOIN
(
SELECT COUNT(StaffJob.id) AS inwork, StaffJob.task_id AS task_id
FROM staff_jobs AS StaffJob
WHERE StaffJob.status >= 3
GROUP BY StaffJob.task_id
) AS W_inwork ON W_inwork.task_id = Task.id
可以简化为：
SELECT
*,
COALESCE(Upload_New, 0) AS Upload_New, COALESCE(Upload_Int, 0) AS Upload_Int, COALESCE(Upload_Ext, 0) AS Upload_Ext,
COALESCE(Upload_All, 0) AS Upload_All, COALESCE(Accepted_Files, 0) AS Accepted_Files, COALESCE(Check_Int, 0) AS Check_Int,
COALESCE(Check_Ext, 0) AS Check_Ext, COALESCE(All_Files, 0) AS All_Files,
SUM(IF(SJ.is_waiting = 1)) AS w_waiting,
SUM(IF(SJ.is_waiting = 0 AND SJ.actived = 1)) AS w_accept,
SUM(IF(SJ.is_waiting = 0 AND SJ.actived = 0)) AS w_cancel,
SUM(IF(SJ.status = 0 AND SJ.actived = 1)) AS w_notwork,
SUM(IF(SJ.status = 1 AND SJ.actived = 1)) AS w_inwork,
SUM(IF(SJ.status = 3 AND SJ.actived = 1)) AS w_precheck,
SUM(IF(SJ.status >= 1)) AS inwork
FROM tasks AS Task
LEFT JOIN staff_jobs AS SJ ON SJ.task_id = Task.id
GROUP BY SJ.task_id
这将删除6个都请求相同数据的子查询。
也可以为其他请求的表完成此操作
关于mysql - 性能MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39078172/