如何提高复杂的 MySQL 选择查询的性能？

debugcn 发表于 Dev

约格什·乔汉

我正在开发项目管理工具，在通过管理端生成报告时，从数据库中加载数据的时间超过 5 分钟。我知道有几点可以帮助我提高性能，但现在我需要在 SELECT 查询中获得帮助

SELECT timesheet_client.organisation as client_name, timesheet_project.title as project_name,
  timesheet_task.name as task, CONCAT(timesheet_user.first_name, ' ', timesheet_user.last_name) as resource_name,
  timesheet_user.bill_factor, timesheet_client.client_type, sum(spent) as spent, sum(delivered_hours) as delivered_hours, 
  sum(billable_hours) as billable_hours, comments, color, lock_color, updated_by, updated_by_date, timesheet_user_psdb.grp_id, 
  timesheet_user_psdb.client_id, timesheet_user_psdb.proj_id, timesheet_user_psdb.task_id, timesheet_user_psdb.uid, 
  timesheet_user_grp.grp_name
FROM timesheet_user_psdb,timesheet_user, timesheet_client, 
  timesheet_project,timesheet_task,timesheet_user_grp
WHERE timesheet_user.username=timesheet_user_psdb.uid and
  timesheet_client.client_id=timesheet_user_psdb.client_id and timesheet_project.proj_id=timesheet_user_psdb.proj_id and
  timesheet_task.task_id = timesheet_user_psdb.task_id and timesheet_user_grp.grp_id=timesheet_user_psdb.grp_id and month =3
  AND year = 2017 and month!='' and timesheet_user_psdb.client_id=326  
GROUP BY timesheet_user_psdb.task_id,timesheet_user_psdb.uid
ORDER BY timesheet_client.client_type desc,timesheet_client.organisation,timesheet_user_psdb.proj_id,
  timesheet_user_psdb.task_id,timesheet_user.uid,timesheet_user_psdb.task_id;

我已经在所有主键上使用了索引。

解释输出：

对此的帮助将是非常可观的。

约翰迈耶

试试这个：

SELECT 
    TC.ORGANISATION AS CLIENT_NAME, 
    TP.TITLE AS PROJECT_NAME,
    TT.NAME AS TASK, 
    CONCAT(TU.FIRST_NAME, ' ', TU.LAST_NAME) AS RESOURCE_NAME,
    TU.BILL_FACTOR, 
    TC.CLIENT_TYPE, SUM(SPENT) AS SPENT, -- You should specify which table this comes from
    SUM(DELIVERED_HOURS) AS DELIVERED_HOURS, -- You should specify which table this comes from
    SUM(BILLABLE_HOURS) AS BILLABLE_HOURS, -- You should specify which table this comes from
    COMMENTS, -- You should specify which table this comes from
    COLOR, -- You should specify which table this comes from
    LOCK_COLOR, -- You should specify which table this comes from
    UPDATED_BY, -- You should specify which table this comes from
    UPDATED_BY_DATE, -- You should specify which table this comes from
    TUP.GRP_ID, 
    TUP.CLIENT_ID, 
    TUP.PROJ_ID, 
    TUP.TASK_ID, 
    TUP.UID, 
    TUG.GRP_NAME
FROM    
    TIMESHEET_USER AS TU
        LEFT OUTER JOIN 
    TIMESHEET_USER_PSDB AS TUP
        ON TU.USERNAME = TUP.UID
        LEFT OUTER JOIN
    TIMESHEET_USER_GRP AS TUG
        ON TUP.GRP_ID = TUG.GRP_ID
        LEFT OUTER JOIN     
    TIMESHEET_CLIENT AS TC
        ON TUP.CLIENT_ID = TC.CLIENT_ID
        LEFT OUTER JOIN
    TIMESHEET_PROJECT AS TP
        ON TUP.PROJ_ID = TP.PROJ_ID
        LEFT OUTER JOIN
    TIMESHEET_TASK TT
        ON TUP.TASK_ID = TT.TASK_ID 
WHERE 
    MONTH = 3 AND -- You should specify which table this comes from
    YEAR = 2017 AND -- You should specify which table this comes from
    MONTH != '' AND -- You should specify which table this comes from
    TUP.CLIENT_ID = 326  
GROUP BY 
    TUP.TASK_ID,
    TUP.UID
ORDER BY 
    TC.CLIENT_TYPE DESC,
    TC.ORGANISATION,
    TUP.PROJ_ID,
    TUP.TASK_ID,
    TU.UID,
    TUP.TASK_ID;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。