MySQL查询需要很长时间

Question

对于一个需要很长时间的MySQL查询，我存在性能问题。我想找出原因。

MySQL版本为5.6，运行在云上

可以使用但需要很长时间的查询是：

SELECT `ha_manage_activity`.*, `firstName`, `lastName`, `avatar`, `ha_manage_activity_types`.`template`, `ha_manage_projects`.`projectName`, `ha_manage_tasks`.`taskName`, 
`ha_manage_subtasks`.`subtaskName`, `ha_manage_projects`.`comments` as project_comment, `ha_manage_tasks`.`comments` as task_comment, `ha_manage_subtasks`.`comments` as subtask_comment,
`ha_manage_projects`.`closed` as project_closed, `ha_manage_tasks`.`completed` as task_completed, `ha_manage_subtasks`.`completed` as subtask_completed, 
ROUND(ha_manage_timesheets.seconds/3600, 2) as hours 
FROM (`ha_manage_activity`)  
JOIN `ha_users` ON `ha_users`.`userID` = `ha_manage_activity`.`userID`  
JOIN `ha_manage_projects` ON `ha_manage_projects`.`projectID` = `ha_manage_activity`.`projectID` AND ha_manage_projects.deleted = 0  
LEFT JOIN `ha_manage_tasks` ON `ha_manage_tasks`.`taskID` = `ha_manage_activity`.`taskID` AND ha_manage_tasks.deleted = 0  
LEFT JOIN `ha_manage_subtasks` ON `ha_manage_subtasks`.`subtaskID` = `ha_manage_activity`.`subtaskID` AND ha_manage_subtasks.deleted = 0  LEFT JOIN `ha_manage_activity_types` ON `ha_manage_activity_types`.`activityTypeID` = `ha_manage_activity`.`activityTypeID`  
LEFT JOIN `ha_manage_timesheets` ON `ha_manage_timesheets`.`entryID` = `ha_manage_activity`.`timesheetID`  
LEFT JOIN `ha_manage_team_users` ON `ha_manage_projects`.`projectID` = `ha_manage_projects`.`projectID` AND ha_manage_team_users.userID = "40586"  
JOIN `ha_manage_shared_projects` ON `ha_manage_shared_projects`.`projectID` = `ha_manage_projects`.`projectID` AND (ha_manage_shared_projects.userID = "40586" OR ha_manage_shared_projects.teamID = ha_manage_team_users.teamID)  
LEFT JOIN `ha_manage_shared_tasks` ON `ha_manage_shared_tasks`.`taskID` = `ha_manage_tasks`.`taskID` AND (ha_manage_shared_tasks.userID = "40586" OR ha_manage_shared_tasks.teamID = ha_manage_team_users.teamID) 
LEFT JOIN `ha_manage_shared_subtasks` ON `ha_manage_shared_subtasks`.`subtaskID` = `ha_manage_subtasks`.`subtaskID` AND (ha_manage_shared_subtasks.userID = "40586" OR ha_manage_shared_subtasks.teamID = ha_manage_team_users.teamID)  
WHERE `ha_manage_activity`.`companyID` =  '31852'  
AND (   
(ha_manage_activity.projectID > 0 AND ha_manage_activity.taskID = 0 AND ha_manage_activity.subtaskID = 0 AND projectName IS NOT NULL) OR   
(ha_manage_activity.projectID > 0 AND ha_manage_activity.taskID > 0 AND ha_manage_activity.subtaskID = 0 AND taskName IS NOT NULL) OR   
(ha_manage_activity.projectID > 0 AND ha_manage_activity.taskID > 0 AND ha_manage_activity.subtaskID > 0 AND subtaskName IS NOT NULL)   )  
GROUP BY `ha_manage_activity`.`activityID`  
ORDER BY `ha_manage_activity`.`dateCreated` 
desc LIMIT 50

这个查询平均需要1分30秒，并返回50行：

50 rows in set (1 min 30.17 sec)

I have ran EXPLAIN to check what MySQL is doing:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_projects
         type: ref
possible_keys: PRIMARY,deleted
          key: deleted
      key_len: 1
          ref: const
         rows: 206834
        Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_shared_projects
         type: ref
possible_keys: userID,teamID,projectID
          key: projectID
      key_len: 5
          ref: projectbubble.ha_manage_projects.projectID
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_activity
         type: ref
possible_keys: PRIMARY,idx_ids
          key: idx_ids
      key_len: 4
          ref: projectbubble.ha_manage_projects.projectID
         rows: 6
        Extra: Using index condition; Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_users
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: projectbubble.ha_manage_activity.userID
         rows: 1
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_tasks
         type: eq_ref
possible_keys: PRIMARY,deleted,deleted_2
          key: PRIMARY
      key_len: 4
          ref: projectbubble.ha_manage_activity.taskID
         rows: 1
        Extra: Using where
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_subtasks
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: projectbubble.ha_manage_activity.subtaskID
         rows: 1
        Extra: Using where
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_activity_types
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: projectbubble.ha_manage_activity.activityTypeID
         rows: 1
        Extra: Using where
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_timesheets
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: projectbubble.ha_manage_activity.timesheetID
         rows: 1
        Extra: NULL
*************************** 9. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_team_users
         type: ref
possible_keys: userID
          key: userID
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
*************************** 10. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_shared_tasks
         type: ref
possible_keys: userID,teamID,taskID
          key: taskID
      key_len: 5
          ref: projectbubble.ha_manage_tasks.taskID
         rows: 1
        Extra: Using where
*************************** 11. row ***************************
           id: 1
  select_type: SIMPLE
        table: ha_manage_shared_subtasks
         type: ref
possible_keys: userID,taskID
          key: taskID
      key_len: 5
          ref: projectbubble.ha_manage_subtasks.subtaskID
         rows: 1
        Extra: Using where


11 rows in set (0.02 sec)

有什么东西我可以优化以获得性能吗？任何帮助都是非常感谢的。

Answer 1

下面的指数可以提供一些改进。

ALTER TABLE ha_manage_activity
ADD INDEX dba246341wr (companyID, activityID, dateCreated)

不幸的是，使用此索引仍然需要双重查找，因为筛选查询和连接到其他表仍然需要ha_manage_activity的许多其他列，而且SELECT子句也需要所有这些列。

还可以尝试将此表中出现在其他联接中的所有其他列添加到此索引的末尾。然后，为SELECT子句添加之后的所有其余列。这将消除双重查找问题，但基本上会给您留下一个表的副本。

即使这样，您的性能可能也不会有很大的提高，因为在OR子句和各种联接中都有大量的WHERE语句。在大多数情况下，这些都会破坏性能。

在您的问题中包含每个表的SHOW CREATE TABLE tablename语句，可能会有更多的帮助。

Answer 2

ha_manage_activity: INDEX(companyID, projectID)
ha_manage_activity: INDEX(companyID, activityID)

(目前还不清楚优化者会选择哪一种。)

此外，还可以使用ha_manage_activity.projectID > 0，因为这对于3个OR是常见的：

AND ha_manage_activity.projectID > 0 AND (   
(ha_manage_activity.taskID = 0 AND ha_manage_activity.subtaskID = 0 AND projectName IS NOT NULL) OR   
(ha_manage_activity.taskID > 0 AND ha_manage_activity.subtaskID = 0 AND taskName IS NOT NULL) OR   
(ha_manage_activity.taskID > 0 AND ha_manage_activity.subtaskID > 0 AND subtaskName IS NOT NULL)
                                         )  

这使优化器有更好的机会从索引中使用projectID。