mysql - 优化包含超过20个内联表的mysql查询

Question

我正在使用 MySQL 数据库。请求您建议一些查询构建的良好实践。我已经为 Join on 子句列应用了索引。

以下查询的执行时间为 13 秒。是否有任何其他方法可以将执行时间减少到 2 或 3 秒？

谢谢。

SELECT  *
    FROM  
      ( SELECT  pd.id AS pid,pd.request_number,pd.requester, pd.desc_of_auto,
               pd.benefits,pd.frequency_task, pd.freq_days,pd.time_spent_cur_hr,
               pd.time_mgr_hr, pd.time_belowmgr_hr,pd.remarks,pd.expected_save_dollar_yr,
                pd.expected_save_dollar_desc, ROUND(pd.total_time_save_hr) AS total_time_save_hr,
                pd.total_cost_save, DATE_FORMAT(pd.exp_start_date, '%d-%b-%Y') AS exp_start_date,
                pd.reasons_challenges_to_start,pd.approx_effort_hr,pd.approx_cost_dollar,
                DATE_FORMAT(pd.req_date, '%d-%b-%Y') AS req_date, pd.gtmt_hr,
                DATE_FORMAT(pd.expected_end_date, '%d-%b-%Y') AS expected_end_date,
                pd.time_mgr_hr_mst,pd.time_belowmgr_hr_mst,pd.gtmt_hr_mst,
                pd.conversation_rate,pd.higherValues, 
              ( SELECT  GROUP_CONCAT(DISTINCT dm.dept_name)
                    FROM  dept_mst dm
                    LEFT JOIN  dept_payback_mst dpm  ON dpm.DeptId = dm.id
                      AND  dpm.Status = 'Active'
                    WHERE  pid = dpm.PaybackId
              ) AS dept, 
              ( SELECT  GROUP_CONCAT(DISTINCT dm.id)
                    FROM  dept_mst dm
                    LEFT JOIN  dept_payback_mst dpm  ON dpm.DeptId = dm.id
                      AND  dpm.Status = 'Active'
                    WHERE  pid = dpm.PaybackId
              ) AS deptid, 
              ( SELECT  GROUP_CONCAT(DISTINCT sm.Sbu_name)
                    FROM  sbu_mst sm
                    LEFT JOIN  sbu_payback_mst spm  ON spm.SbuId = sm.Sbu_id
                      AND  spm.Status = 'Active'
                    WHERE  pid = spm.PaybackId
                      AND  sm.Sbu_id IN (1000)
              ) AS sbu, 
              ( SELECT  GROUP_CONCAT(DISTINCT sm.Sbu_id)
                    FROM  sbu_mst sm
                    LEFT JOIN  sbu_payback_mst spm  ON spm.SbuId = sm.Sbu_id
                      AND  spm.Status = 'Active'
                    WHERE  pid = spm.PaybackId
                      AND  sm.Sbu_id IN (1000)
              ) AS sbuid, 
              ( SELECT  GROUP_CONCAT(DISTINCT cm.Cmp_name)
                    FROM  cmp_mst cm
                    LEFT JOIN  comp_payback_mst cpm  ON cpm.CompId = cm.cmp_id
                      AND  cpm.Status = 'Active'
                    WHERE  pid = cpm.PaybackId
              ) AS comp, 
              ( SELECT  GROUP_CONCAT(DISTINCT cm.cmp_id)
                    FROM  cmp_mst cm
                    LEFT JOIN  comp_payback_mst cpm  ON cpm.CompId = cm.cmp_id
                      AND  cpm.Status = 'Active'
                    WHERE  pid = cpm.PaybackId 
              ) AS compid, 
              ( SELECT  GROUP_CONCAT(DISTINCT rm.Resource_Name)
                    FROM  resource_mst rm
                    LEFT JOIN  resource_payback_mapping rpm  ON rpm.resourceId = rm.id
                    WHERE  pid = rpm.PaybackId 
              ) AS resreq, 
              ( SELECT  um.UserName
                    FROM  user_mst um
                    WHERE  um.id = pd.requester 
              ) AS reqName, 
              ( SELECT  prm.priority
                    FROM  priority_master prm
                    WHERE  prm.id = pd.req_priority 
              ) AS reqPriority, 
              ( SELECT  stm.req_status
                    FROM  status_master stm
                    WHERE  stm.id = pd.req_status
                      AND  stm.req_status = '4- Completed' 
              ) AS reqStatus, 
              ( SELECT  GROUP_CONCAT(DISTINCT um1.UserName)
                    FROM  user_mst um1
                    LEFT JOIN  proj_champ_paymst pcp  ON pcp.champ_id = um1.id
                      AND  pcp.status = 'Active'
                    WHERE  pid = pcp.PaybackId 
              ) AS proj_champ, 
              ( SELECT  GROUP_CONCAT(DISTINCT um2.UserName)
                    FROM  user_mst um2
                    LEFT JOIN  proj_team_member_paymst ptm  ON ptm.team_member_id = um2.id
                      AND  ptm.status = 'Active'
                    WHERE  pid = ptm.PaybackId 
              ) AS proj_team, 
              ( SELECT  tym.type
                    FROM  type_master tym
                    WHERE  tym.id = pd.dataUpload 
              ) AS type, 
              ( SELECT  fmm.freqName
                    FROM  freq_mst fmm
                    WHERE  fmm.id = pd.frequency_task 
              ) AS frqName
            FROM  project_detail pd
            WHERE  (pd.total_time_save_hr > 0
                      OR  pd.expected_save_dollar_yr > 0
                   )
              AND  IFNULL(YEAR(expected_end_date), YEAR(NOW())) IN (2015 ,
                        2016
                         )
            GROUP BY  pd.request_number , pd.id
            ORDER BY  DATE(expected_end_date)
      ) u
    WHERE  reqStatus = '4- Completed'
      AND  sbuid IN (1000)
      AND  compid IN (1000 , 1003,1100,1101,1200,1600, 2001, 2002,
                2003,2004,2005,2006,4000,4100,4200,4300 
                     ) 

Answer 1

您应该重新表述查询。

sbu_mst JOIN sbu_payback_mst WHERE ... 显示两次。如果您使用 JOIN 而不是这样的子查询，则不需要对其进行两次计算。

cmp_mst 也是如此。

无需更多的重新排列(和JOINing)，您可能可以摆脱外部查询。

当 WHERE 处理这种情况时，不要使用 LEFT。示例:LEFT JOIN sbu_payback_mst spm ... pid = spm.PaybackId

可能需要的一些索引:

dept_payback_mst: (PaybackId, Status, DeptId)
       -- unless it already has PRIMARY KEY(PaybackId)
proj_team_member_paymst: (PaybackId)