mysql - 性能MySQL

Question

我使用大量 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

Answer 1

是的，很多。

您可以删除相当多的重复连接。只需加入一次，然后再进行分组。对于选择中的计数，您可以使用 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 个子查询，它们都请求相同的数据。

也可以对请求的其他表执行此操作