升级MySQL 5.7至8.0.32后Redmine MyPage查询过慢求助

问题背景 #

将Redmine的MySQL数据库从5.7升级至8.0.32后，MyPage核心查询耗时从几秒剧增至约1分钟。Redmine版本涵盖4.1.1.stable及最新的5.0.5，且已添加所有必要索引。通过执行计划对比发现：MySQL 8.0的派生查询优化机制导致执行逻辑与5.7产生差异——5.7版本从issues表出发，利用assigned_to_id索引快速过滤数据；而8.0.32版本优先扫描issue_statuses表并采用哈希连接，导致效率大幅下降。

解决办法 #

1. 关闭派生表合并优化 #

MySQL 8.0默认开启derived_merge优化，部分场景下会导致执行计划恶化。可通过会话级或全局参数关闭该优化：

• 会话级测试：执行以下命令后重新运行查询，验证是否恢复高效执行计划

SET SESSION optimizer_switch='derived_merge=off';

• 全局配置：若测试有效，在my.cnf（或my.ini）中添加以下配置，重启MySQL生效：

optimizer_switch='derived_merge=off'

2. 重写查询语句，引导优化器选择高效路径 #

将原查询中的子查询替换为显式JOIN，避免优化器生成低效执行计划。修改后的查询示例如下：

SELECT
  issues.id AS t0_r0,
  issues.tracker_id AS t0_r1,
  issues.project_id AS t0_r2,
  issues.subject AS t0_r3,
  issues.description AS t0_r4,
  issues.due_date AS t0_r5,
  issues.category_id AS t0_r6,
  issues.status_id AS t0_r7,
  issues.assigned_to_id AS t0_r8,
  issues.priority_id AS t0_r9,
  issues.fixed_version_id AS t0_r10,
  issues.author_id AS t0_r11,
  issues.lock_version AS t0_r12,
  issues.created_on AS t0_r13,
  issues.updated_on AS t0_r14,
  issues.start_date AS t0_r15,
  issues.done_ratio AS t0_r16,
  issues.estimated_hours AS t0_r17,
  issues.parent_id AS t0_r18,
  issues.root_id AS t0_r19,
  issues.lft AS t0_r20,
  issues.rgt AS t0_r21,
  issues.is_private AS t0_r22,
  issues.position AS t0_r23,
  issues.remaining_hours AS t0_r24,
  issues.story_points AS t0_r25,
  issues.closed_on AS t0_r26,
  issue_statuses.id AS t1_r0,
  issue_statuses.name AS t1_r1,
  issue_statuses.is_closed AS t1_r2,
  issue_statuses.position AS t1_r3,
  issue_statuses.default_done_ratio AS t1_r4,
  projects.id AS t2_r0,
  projects.name AS t2_r1,
  projects.description AS t2_r2,
  projects.homepage AS t2_r3,
  projects.is_public AS t2_r4,
  projects.parent_id AS t2_r5,
  projects.created_on AS t2_r6,
  projects.updated_on AS t2_r7,
  projects.identifier AS t2_r8,
  projects.status AS t2_r9,
  projects.lft AS t2_r10,
  projects.rgt AS t2_r11,
  projects.inherit_members AS t2_r12,
  projects.default_version_id AS t2_r13,
  projects.default_assigned_to_id AS t2_r14
FROM
  issues
  INNER JOIN projects ON projects.id = issues.project_id
  INNER JOIN issue_statuses ON issue_statuses.id = issues.status_id
  LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id
  -- 将IN子查询替换为显式JOIN
  INNER JOIN issue_statuses open_statuses 
    ON open_statuses.id = issues.status_id AND open_statuses.is_closed = FALSE
  -- 将EXISTS子查询替换为显式JOIN
  INNER JOIN enabled_modules em 
    ON em.project_id = projects.id AND em.name='issue_tracking'
WHERE
  projects.status <> 9
  AND issues.assigned_to_id IN ('1051', '2643')
  AND projects.status IN ('1')
ORDER BY
  enumerations.position DESC,
  issues.updated_on DESC,
  issues.id DESC
LIMIT 10;

3. 创建针对性复合索引 #

添加复合索引引导优化器选择最优执行路径：

• 为issues表创建复合索引，覆盖过滤条件和关联字段：

CREATE INDEX index_issues_on_assigned_to_status_project 
ON issues (assigned_to_id, status_id, project_id);

• 为enabled_modules表创建复合索引，加速项目模块验证：

CREATE INDEX index_enabled_modules_on_project_name 
ON enabled_modules (project_id, name);

4. 升级MySQL至更高版本 #

MySQL 8.0早期版本的优化器可能存在场景化bug，升级到8.0.36及以上的稳定版本，可能修复该派生查询优化的逻辑问题，使执行计划自动回归高效路径。

内容的提问来源于stack exchange，提问作者Kushal Singh