MariaDB 10.3.35查询执行缓慢疑似优化器Bug咨询

首先得给你的分析点个赞——你完全是对的，那个子查询确实是独立的，它的结果只依赖你传入的固定设施ID列表，和外层查询的任何字段都没有关联。MariaDB把它标记成DEPENDENT SUBQUERY确实是优化器的判断失误，而非你的写法错误。

为什么会出现这个误判？ #

问题出在你子查询和外层查询用了同一个表名AccommodationHasCombinedFacilities。虽然逻辑上子查询完全独立，但MariaDB 10.3的查询优化器在处理这种同表名的子查询时，容易误判为“子查询需要引用外层表的字段”，进而选择了最糟糕的执行计划：对外层查询的每一行都执行一次子查询。这就导致原本只需要跑一次的子查询被重复执行了上千次，直接拖垮了性能。

怎么解决这个问题？ #

除了你已经用到的“预查询子结果再代入”的方法，还有两个更优雅的方案：

1. 给子查询的表加别名，明确区分表实例

给子查询里的表加个别名，让优化器清楚它和外层的表是两个独立的实例，这样优化器就会正确识别这是个独立子查询，只执行一次并缓存结果：

SELECT
    `facilityId`,
    COUNT(`accommodationId`) as accommodationCount
FROM `AccommodationHasCombinedFacilities`
JOIN `Facility`
    ON `AccommodationHasCombinedFacilities`.`facilityId` = `Facility`.`id` 
    AND `Facility`.`isSearchCriterion` = 1
WHERE
    `AccommodationHasCombinedFacilities`.`accommodationId` IN (
        SELECT DISTINCT `sub`.`accommodationId`
        FROM `AccommodationHasCombinedFacilities` AS sub
        WHERE `sub`.`facilityId` IN (191, 29)
        GROUP BY `sub`.`accommodationId`
        HAVING COUNT(DISTINCT `sub`.`facilityId`) = 2
    )
GROUP BY `facilityId`
ORDER BY `facilityId`

2. 把IN子查询改成JOIN关联

用JOIN代替IN子查询，把独立子查询转换成临时表关联的形式，这种写法对优化器更友好，更容易触发最优执行计划：

SELECT
    ahcf.`facilityId`,
    COUNT(ahcf.`accommodationId`) as accommodationCount
FROM `AccommodationHasCombinedFacilities` ahcf
JOIN `Facility` f
    ON ahcf.`facilityId` = f.`id` 
    AND f.`isSearchCriterion` = 1
JOIN (
    SELECT `accommodationId`
    FROM `AccommodationHasCombinedFacilities`
    WHERE `facilityId` IN (191, 29)
    GROUP BY `accommodationId`
    HAVING COUNT(DISTINCT `facilityId`) = 2
) sub_ids ON ahcf.`accommodationId` = sub_ids.`accommodationId`
GROUP BY ahcf.`facilityId`
ORDER BY ahcf.`facilityId`

这算优化器Bug吗？ #

严格来说，这属于MariaDB 10.3版本优化器的局限性。后续的版本（比如10.4及以上）对这种同表名子查询的判断逻辑做了优化，基本不会再出现这种误判。所以如果你有升级版本的条件，升级后这个问题也会自然解决。

总的来说，你的查询逻辑没问题，是特定版本优化器的判断失误导致的性能问题，用上面的方法就能轻松解决。

备注：内容来源于stack exchange，提问作者Emosewaj