MariaDB 쿼리가 작동하지 않습니다. 옵티마이저 버그?

MariaDB 쿼리가 작동하지 않습니다. 옵티마이저 버그?

MariaDB 버전 10.3.35를 사용하고 있는데 쿼리 중 하나가 특히 느리게 실행되고 있습니다.

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 `AccommodationHasCombinedFacilities`.`accommodationId`
        FROM `AccommodationHasCombinedFacilities`
        WHERE `AccommodationHasCombinedFacilities`.`facilityId` IN (191, 29) -- These IDs can change per query
        GROUP BY `AccommodationHasCombinedFacilities`.`accommodationId`
        HAVING COUNT(DISTINCT `AccommodationHasCombinedFacilities`.`facilityId`) = 2 -- This number equals the amount of IDs above
    )
GROUP BY `facilityId`
ORDER BY `facilityId`

테이블AccommodationHasCombinedFacilities는 ~18.600개의 수용 ID와 ~300개의 시설 ID를 결합한 2개의 열로 구성되어 총 588.000개의 행을 구성합니다.

이 쿼리를 실행하는 데 시간이 엄청나게 오래 걸리는 이유를 알 수 없습니다.입니다.EXPLAIN이 쿼리는 엔진이 하위 쿼리가 종속 하위 쿼리라고 생각함을 나타냅니다.

이드	select_type	테이블	유형	possible_key	열쇠	key_len	심판을 보다	행들	추가의
1	기본적인	시설	심판을 보다	기본, isSearchCriterion	isSearchCriterion	2	컨스	34	위치 사용; 색인 사용
1	기본적인	복합 시설을 갖춘 숙박 시설	심판을 보다	시설이드	시설이드	4	Facility.id	1272	사용 위치
2	종속 하위 쿼리	복합 시설을 갖춘 숙박 시설	색인을 달다	시설이드	숙박 시설이드	1022	NULL	2997	사용 위치

그러나 분석 결과 하위 쿼리는 독립적이며 항상 해당 하위 쿼리에 제공되는 ID에 대한 정적 결과 집합을 제공한다는 결론에 도달했습니다.결국 우리의 해결책은 먼저 하위 쿼리를 실행한 다음 검색된 숙박 ID 목록을 원래 쿼리에 삽입하는 것이었습니다. 그러면 문제가 해결되고 쿼리가 예상했던 대로 빠르게 실행됩니다.

SELECT
    `facilityId`,
    COUNT(`accommodationId`) as accommodationCount
FROM `AccommodationHasCombinedFacilities`
JOIN `Facility`
ON `AccommodationHasCombinedFacilities`.`facilityId` = `Facility`.`id` AND `Facility`.`isSearchCriterion` = 1
WHERE
    `AccommodationHasCombinedFacilities`.`accommodationId` IN
    (1, 2, 3, 4, 5, 6, 7, 8, 9 /* etc. for about 70 IDs */)
GROUP BY `facilityId`
ORDER BY `facilityId`

이것은 MariaDB의 쿼리 최적화 프로그램에 대한 버그입니까, 아니면 우리가 원래 쿼리에 대해 뭔가 잘못한 것입니까?

이 쿼리의 성능을 시험해 볼 수 있습니까?

존재하는 곳이 더 빠름

(테스트되지 않음)

SELECT `facilityId`, COUNT(`accommodationId`) as accommodationCount
FROM `AccommodationHasCombinedFacilities` AS acf
JOIN `Facility` AS f
ON `acf`.`facilityId` = f.`id` AND f.`isSearchCriterion` = 1
WHERE EXISTS
 (SELECT a.`accommodationId` FROM `AccommodationHasCombinedFacilities` AS a 
  WHERE acf.accommodationId = a.accommodationId AND a.`facilityId` = 191)
AND EXISTS
 (SELECT a.`accommodationId` FROM `AccommodationHasCombinedFacilities` AS b 
  WHERE acf.accommodationId = b.accommodationId AND b.`facilityId` = 20)
GROUP BY `facilityId`
ORDER BY `facilityId`;

언급URL : https://stackoverflow.com/questions/76731446/mariadb-query-stuck-optimizer-bug