从 MySQL 5.7 迁移到 MARIADB 10.6 后查询缓慢。执行计划指标问题

分享于2022年07月17日 database mariadb mysql 问答
【问题标题】:从 MySQL 5.7 迁移到 MARIADB 10.6 后查询缓慢。执行计划指标问题(Slow query after migrating from MySQL 5.7 to MARIADB 10.6. Execution plan index problem)
【发布时间】:2022-01-25 01:17:19
【问题描述】:

我们希望将 LAMP 服务器从 MySQL 5.7 迁移到 MariaDB 10.6。我们克隆了服务器,安装了 MariaDB 并迁移了数据库。我期待 SQL 查询的一些速度改进,但令我惊讶的是,我们的应用程序使用 MariaDB 时速度较慢。我正在尝试分析一些查询,我向您展示一个具体案例。

我们有一个用于许多其他查询的 VIEW,如下所示:

ALTER ALGORITHM = MERGE DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `GT_VWCalendariProfessionalInc` AS
   SELECT cpInc.prof, cpInc.start_date, cpInc.end_date
   FROM GT_CalendariProfessional cpInc
   JOIN CTR_Motius ON cpInc.colA = CTR_Motius.colA
   WHERE cpInc.necessitat IS NULL AND ...

cpInc 表中的关键索引:ix_necessitat (column necessitat), ix_professional (column prof), ix_outerjoin_nec_dates (columns necessitat, start_date, end_date)

在 MySQL 中使用来自外部查询的此视图比使用 MariaDB 更快。查询是这样的:

SELECT kal.prof, kal.start_date, kal.end_date
FROM GT_CalendariProfessional kal
LEFT JOIN GT_VWCalendariProfessionalInc AS cpInc ON cpInc.prof = kal.prof AND kal.start_date < cpInc.end_date AND cpInc.start_date < kal.end_date
WHERE kal.prof=3143 AND kal.start_date BETWEEN '2021-01-01 00:00' AND '2022-01-01 00:00'

通过 EXPLAIN,我看到 MariaDB 正在使用低效的索引 (ix_professional)。我不能在视图中强制索引,因为我们在具有不同执行计划的其他查询中使用视图,并且此处的索引效率低下可能在其他查询中是最好的。

使用 MySQL 的执行计划(快速): Slow query after migrating from MySQL 5.7 to MARIADB 10.6. Execution plan index problem

使用 MariaDB 的执行计划(慢): Slow query after migrating from MySQL 5.7 to MARIADB 10.6. Execution plan index problem

一般来说,使用 MariaDB 的许多查询都比使用 MySQL 慢????

  • MariaDB 似乎使用了错误的索引。 I can't FORCE INDEX inside the view because we use the view in other queries with different execution plans 尝试使用带日期的索引,这将限制行搜索和返回
  • 请提供 SHOW CREATE TABLEs ;没有它们就很难阅读解释。
  • 请同时提供SHOW CREATE TABLE CTR_Motius;和 COMPLETE 视图 DEFINER。为您提供帮助所需的重要信息。

【解决方案1】:

首先,让我们添加这些索引;他们 可能 加速这两个版本:

kal:  INDEX(prof, start_date,  end_date)
cpInc:  INDEX(prof, end_date, start_date)
cpInc:  INDEX(prof, start_date, end_date)

并删除 (prof) 上的当前索引