MySQL迁移至MariaDB后查询性能下降的优化咨询

将数据库从MySQL 8.0.23迁移至MariaDB 11.3.2后，发现相同表结构下的某查询在MariaDB中耗时3.5秒，远高于MySQL的0.8秒。已执行EXPLAIN和ANALYZE命令，确认两者执行流程存在差异，但评估行数相近，现需优化MariaDB端的查询性能。

相关查询语句 #

select  `q`.*, EXISTS (
    SELECT `qe`.* FROM `questions` `qe`     
    CROSS JOIN `companies_has_users` `cu` ON cu.company_id = 160 
    LEFT JOIN `answer_groups` `ag` ON ag.question_id = qe.id AND ag.archived = 0 
    LEFT JOIN `answer_fields` `af` ON af.answer_group_id = ag.id AND af.archived = 0 
    LEFT JOIN `answers` `a` ON a.answer_field_id = af.id AND a.is_answered = 1 AND a.user_id = cu.user_id AND a.questionnaire_id = 228 
    LEFT JOIN `answers` `a_2` ON a_2.answer_field_id = af.id AND a_2.is_default = 1 
    LEFT JOIN `answer_fields` `af_2` ON af_2.id = a.answer_field_id AND af_2.archived = 0 
    LEFT JOIN `answer_fields` `af_3` ON af_3.id = a_2.answer_field_id AND af_3.archived = 0 
    LEFT JOIN `questions_has_user_files` `quf` ON quf.question_id = qe.id AND quf.company_id = 160 
    LEFT JOIN `questionnaires_has_questions` `qhq` ON qhq.question_id = qe.id
    WHERE ((`qhq`.`questionnaire_id` = 228) OR (`qhq`.`questionnaire_id` = 149)) AND (`qe`.`archived`=FALSE) AND (qe.id = q.id) AND (`a`.`id` IS NOT NULL)) AS `count_answers`, 
    EXISTS (SELECT `qe`.* FROM `questions` `qe` 
    CROSS JOIN `companies_has_users` `cu` ON cu.company_id = 160 
    LEFT JOIN `answer_groups` `ag` ON ag.question_id = qe.id AND ag.archived = 0 
    LEFT JOIN `answer_fields` `af` ON af.answer_group_id = ag.id AND af.archived = 0 
    LEFT JOIN `answers` `a` ON a.answer_field_id = af.id AND a.is_answered = 1 AND a.user_id = cu.user_id AND a.questionnaire_id = 228 
    LEFT JOIN `answers` `a_2` ON a_2.answer_field_id = af.id AND a_2.is_default = 1 
    LEFT JOIN `answer_fields` `af_2` ON af_2.id = a.answer_field_id AND af_2.archived = 0 
    LEFT JOIN `answer_fields` `af_3` ON af_3.id = a_2.answer_field_id AND af_3.archived = 0 
    LEFT JOIN `questions_has_user_files` `quf` ON quf.question_id = qe.id AND quf.company_id = 160 
    LEFT JOIN `questionnaires_has_questions` `qhq` ON qhq.question_id = qe.id 
    WHERE ((`qhq`.`questionnaire_id` = 228) OR (`qhq`.`questionnaire_id` = 149)) AND (`qe`.`archived`=FALSE) AND (qe.id = q.id) AND (`a_2`.`id` IS NOT NULL)) AS `count_default_answers`, 
    EXISTS (SELECT `qe`.* FROM `questions` `qe` 
    CROSS JOIN `companies_has_users` `cu` ON cu.company_id = 160 
    LEFT JOIN `answer_groups` `ag` ON ag.question_id = qe.id AND ag.archived = 0 
    LEFT JOIN `answer_fields` `af` ON af.answer_group_id = ag.id AND af.archived = 0 
    LEFT JOIN `answers` `a` ON a.answer_field_id = af.id AND a.is_answered = 1 AND a.user_id = cu.user_id AND a.questionnaire_id = 228 
    LEFT JOIN `answers` `a_2` ON a_2.answer_field_id = af.id AND a_2.is_default = 1 
    LEFT JOIN `answer_fields` `af_2` ON af_2.id = a.answer_field_id AND af_2.archived = 0 
    LEFT JOIN `answer_fields` `af_3` ON af_3.id = a_2.answer_field_id AND af_3.archived = 0 
    LEFT JOIN `questions_has_user_files` `quf` ON quf.question_id = qe.id AND quf.company_id = 160 
    LEFT JOIN `questionnaires_has_questions` `qhq` ON qhq.question_id = qe.id 
    WHERE ((`qhq`.`questionnaire_id` = 228) OR (`qhq`.`questionnaire_id` = 149)) AND (`qe`.`archived`=FALSE) AND (qe.id = q.id) AND ((`af_2`.`user_upload_enabled`=2) OR (`af_3`.`user_upload_enabled`=2))) AS `user_upload_enabled`, 
    EXISTS (SELECT `qe`.* FROM `questions` `qe` 
    CROSS JOIN `companies_has_users` `cu` ON cu.company_id = 160 
    LEFT JOIN `answer_groups` `ag` ON ag.question_id = qe.id AND ag.archived = 0 
    LEFT JOIN `answer_fields` `af` ON af.answer_group_id = ag.id AND af.archived = 0 
    LEFT JOIN `answers` `a` ON a.answer_field_id = af.id AND a.is_answered = 1 AND a.user_id = cu.user_id AND a.questionnaire_id = 228 
    LEFT JOIN `answers` `a_2` ON a_2.answer_field_id = af.id AND a_2.is_default = 1 
    LEFT JOIN `answer_fields` `af_2` ON af_2.id = a.answer_field_id AND af_2.archived = 0 
    LEFT JOIN `answer_fields` `af_3` ON af_3.id = a_2.answer_field_id AND af_3.archived = 0 
    LEFT JOIN `questions_has_user_files` `quf` ON quf.question_id = qe.id AND quf.company_id = 160 
    LEFT JOIN `questionnaires_has_questions` `qhq` ON qhq.question_id = qe.id 
    WHERE ((`qhq`.`questionnaire_id` = 228) OR (`qhq`.`questionnaire_id` = 149)) AND (`qe`.`archived`=FALSE) AND (qe.id = q.id) AND ((`quf`.`file_id` IS NOT NULL) AND (`quf`.`questionnaire_id`=228))) AS `has_user_file_id` FROM `questions` `q` 
    LEFT JOIN `questionnaires_has_questions` `qhq` ON qhq.question_id = q.id 
    WHERE ((`qhq`.`questionnaire_id` = 228) OR (`qhq`.`questionnaire_id` = 149)) AND (`q`.`archived`=FALSE) 
    GROUP BY `q`.`id` ORDER BY `count_answers` DESC, `count_default_answers` DESC

执行计划对比 #

已获取MariaDB和MySQL的执行计划，两者执行流程存在明显差异，但评估行数相近。

优化思路与排查建议 #

1. 重构查询语句，消除重复逻辑 #

• 四个EXISTS子查询逻辑高度冗余，可整合为一次关联查询，通过条件聚合计算四个标记字段，避免重复扫描多张表。示例重构语句：

  SELECT 
    q.*,
    MAX(CASE WHEN a.id IS NOT NULL THEN 1 ELSE 0 END) AS count_answers,
    MAX(CASE WHEN a_2.id IS NOT NULL THEN 1 ELSE 0 END) AS count_default_answers,
    MAX(CASE WHEN af_2.user_upload_enabled=2 OR af_3.user_upload_enabled=2 THEN 1 ELSE 0 END) AS user_upload_enabled,
    MAX(CASE WHEN quf.file_id IS NOT NULL AND quf.questionnaire_id=228 THEN 1 ELSE 0 END) AS has_user_file_id
  FROM questions q
  LEFT JOIN questionnaires_has_questions qhq ON qhq.question_id = q.id
  LEFT JOIN questions qe ON qe.id = q.id AND qe.archived=FALSE
  CROSS JOIN companies_has_users cu ON cu.company_id = 160
  LEFT JOIN answer_groups ag ON ag.question_id = qe.id AND ag.archived = 0
  LEFT JOIN answer_fields af ON af.answer_group_id = ag.id AND af.archived = 0
  LEFT JOIN answers a ON a.answer_field_id = af.id AND a.is_answered = 1 AND a.user_id = cu.user_id AND a.questionnaire_id = 228
  LEFT JOIN answers a_2 ON a_2.answer_field_id = af.id AND a_2.is_default = 1
  LEFT JOIN answer_fields af_2 ON af_2.id = a.answer_field_id AND af_2.archived = 0
  LEFT JOIN answer_fields af_3 ON af_3.id = a_2.answer_field_id AND af_3.archived = 0
  LEFT JOIN questions_has_user_files quf ON quf.question_id = qe.id AND quf.company_id = 160
  WHERE qhq.questionnaire_id IN (228,149) AND q.archived=FALSE
  GROUP BY q.id
  ORDER BY count_answers DESC, count_default_answers DESC
  

• 将原WHERE中的OR条件替换为IN（qhq.questionnaire_id IN (228,149)），优化器更易生成高效执行计划。
• 若q.id是主键，主查询的GROUP BY q.id可直接移除，减少不必要的聚合操作。

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

根据查询中的过滤、连接和排序条件，创建以下复合索引：

• questionnaires_has_questions：(questionnaire_id, question_id)，覆盖WHERE和JOIN条件
• questions：(archived, id)，匹配过滤和关联条件
• answer_groups：(question_id, archived)，匹配JOIN和过滤条件
• answer_fields：(answer_group_id, archived, user_upload_enabled)，覆盖JOIN、过滤和判断条件
• answers：(answer_field_id, is_answered, user_id, questionnaire_id)、(answer_field_id, is_default)，分别匹配两个LEFT JOIN的条件
• questions_has_user_files：(question_id, company_id, questionnaire_id, file_id)，覆盖JOIN和WHERE条件
• 执行ANALYZE TABLE更新所有涉及表的统计信息，确保MariaDB优化器能精准评估数据分布。

3. 调整MariaDB优化器配置 #

• 对比MySQL和MariaDB的optimizer_switch参数，确保MariaDB启用了derived_merge、join_cache_level等关键优化开关。
• 增大join_buffer_size、sort_buffer_size参数，为查询的连接和排序操作分配足够内存，避免磁盘临时表或文件排序。
• 检查innodb_buffer_pool_size，确保其设置为服务器内存的50%-70%，让常用数据能缓存到内存中。

4. 强制指定执行计划验证 #

• 若MariaDB选择的驱动表或连接顺序不合理，可通过STRAIGHT_JOIN强制指定连接顺序（参考MySQL执行计划中的顺序），测试性能变化。
• 查看执行计划的type列，确保关键表使用ref或range访问类型，避免ALL全表扫描；若出现Using temporary或Using filesort，需通过索引覆盖排序字段优化。

内容的提问来源于stack exchange，提问作者paul mart