从MySQL 5.7迁移到MariaDB10.6的地方(SUBQUERY)后的缓慢查询

Question

我们希望将LAMP服务器从MySQL 5.7迁移到MariaDB 10.6。我们克隆了服务器，安装了MariaDB并迁移了数据库。我原以为SQL查询会有一些速度上的改进，但令我惊讶的是，我们的应用程序在使用MariaDB时速度更慢。我试图分析一些查询，我向您展示了一个特定的案例。

我们有这样一个查询：(a) SELECT .. FROM .. WHERE EXISTS (SELECT .. FROM .. WHERE outer_col=inner_col AND inner_where)

用MySQL 5.7: 0.1s。与MariaDB 10.6: 10

我试图按照建议的这里：(b) .. WHERE outer_col IN (SELECT inner_col FROM .. WHERE inner_where)更改查询

用MySQL 5.7: 0.1s。与MariaDB 10.6: 10

最后，我试图禁用物化optimizer_switch：SET SESSION optimizer_switch='materialization=off';的默认设置。

它起作用了!对于MariaDB，这两个查询现在都持续0.1s。

但是，为什么？这个优化器不应该是一个改进吗？你建议永久关闭它吗？我还有别的选择吗？

我也尝试过优化器exists_to_in。使用exists_to_in=off和materialization=on查询'b‘10s，查询'a’0.1s。

Thx

用MySQL 5.7 (FAST)分析计划

​

用MariaDB 10.6、materialization=on和exists_to_in=on (慢)分析计划

​

用MariaDB 10.6，materialization=off (FAST!)分析计划

​

用MariaDB 10.6，exists_to_in=off (FAST!)分析计划

​

创建具有相关行的sumar化表：

CREATE TABLE `GT_CalendariProfessional` (
    `CalendariProfessionalID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `ProfessionalId` INT(11) NOT NULL,
    `DataInici` DATETIME(6) NOT NULL,
    `DataFi` DATETIME(6) NOT NULL,
    `NecessitatId` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`CalendariProfessionalID`) USING BTREE,
    INDEX `ix_professional` (`ProfessionalId`) USING BTREE,
    INDEX `ix_necessitat` (`NecessitatId`) USING BTREE,
    INDEX `ix_outerjoin_nec_dates` (`NecessitatId`, `DataInici`, `DataFi`) USING BTREE,
    INDEX `ix_outerjoin_prof_dates` (`ProfessionalId`, `DataInici`, `DataFi`) USING BTREE,
    INDEX `ix_IdIncidencia` (`IdIncidencia`) USING BTREE,
    CONSTRAINT `FK_GT_CalendariProfessional_GNR_Professionals` FOREIGN KEY (`ProfessionalId`) REFERENCES `GIRH_PROD`.`GNR_Professionals` (`ProfessionalID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `FK_GT_CalendariProfessional_GT_Necessitat` FOREIGN KEY (`NecessitatId`) REFERENCES `GIRH_PROD`.`GT_Necessitat` (`NecessitatID`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6423951
;

SELECT COUNT(1) FROM GT_CalendariProfessional => 5413440行

Answer 1

当您同时拥有INDEX(a)和INDEX(a,b)时，放弃前者；它会分散优化器使用后者的注意力。有时，这会导致查询计划缓慢。(我看到有两宗这样的案件。)

我需要查看查询，以判断我的建议在您的情况下是否重要。(即使如此，我可能也不太确定。)不管怎么说，

INDEX `ix_professional` (`ProfessionalId`) USING BTREE,

会把优化者的注意力转移到

INDEX `ix_outerjoin_prof_dates` (`ProfessionalId`, `DataInici`, `DataFi`) USING BTREE,

这将是“一样好，也许更好”，但“可能稍慢”。