임시 테이블에 결합하면 쿼리가 느려지는 이유는 무엇입니까?

임시 테이블에 결합하면 쿼리가 느려지는 이유는 무엇입니까?

^{(여러분은 제가 열어둔 다른 질문을 보셨을지도 모릅니다.제가 직접 조사하면서 이 문제에 대한 답을 얻는 데 도움이 되기를 바라면서 관련성이 있지만 좀 더 구체적인 질문을 던집니다.)}

---

배경

다음 표를 참고하십시오.

CREATE TABLE `Alarms` (
  `AlarmId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `DeviceId` BINARY(16) NOT NULL,
  `Code` BIGINT(20) UNSIGNED NOT NULL,
  `Ended` TINYINT(1) NOT NULL DEFAULT '0',
  `NaturalEnd` TINYINT(1) NOT NULL DEFAULT '0',
  `Pinned` TINYINT(1) NOT NULL DEFAULT '0',
  `Acknowledged` TINYINT(1) NOT NULL DEFAULT '0',
  `StartedAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `EndedAt` TIMESTAMP NULL DEFAULT NULL,
  `MarkedForDeletion` TINYINT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`AlarmId`),
  KEY `Key1` (`Ended`,`Acknowledged`),
  KEY `Key2` (`Pinned`),
  KEY `Key3` (`DeviceId`,`Pinned`),
  KEY `Key4` (`DeviceId`,`StartedAt`,`EndedAt`),
  KEY `Key5` (`DeviceId`,`Ended`,`EndedAt`),
  KEY `Key6` (`MarkedForDeletion`),

  KEY `KeyB` (`MarkedForDeletion`,`DeviceId`,`StartedAt`,`EndedAt`,`Acknowledged`,`Pinned`)
) ENGINE=INNODB;

• Key1-Key6인덱스 머지에 의존할 수 있다고 가정한 원래의 디자인에서 가져온 것입니다(다른 질문에 따르면, 이것은 사실이 아닌 것 같습니다).

• KeyB제가 현재 수사할 때 가지고 있는 것들입니다

데이터베이스에는 수십만 개의 행이 있습니다(모든 행과 일치합니다).DeviceId,StartedAt그리고.MarkedForDeletion아래 나열된 기준, 단,NOT NULL EndedAt그리고.Pinned=FALSE따라서 실제로 일치하는 행이 없습니다.) 다음 쿼리는 사용하라고 지시하는 한 매우 빠르게 실행됩니다(~100ms).KeyB:

SET @a = '2018-02-18 00:00:00';
SET @b = '2018-02-18 01:00:00';

SELECT * FROM `Alarms` USE INDEX (`KeyB`) WHERE
(
    `Alarms`.`EndedAt` IS NULL
    AND `Alarms`.`Acknowledged` = FALSE

    AND `Alarms`.`StartedAt` < @b
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
) OR (
    `Alarms`.`EndedAt` IS NOT NULL
    AND `Alarms`.`EndedAt` >= @a
    AND `Alarms`.`Pinned` = TRUE

    AND `Alarms`.`StartedAt` < @b
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
);

쿼리 계획:

나는 이 모든 것이 지금까지 가능한 한 빨리 만들어졌다는 것을 의미한다고 생각한다.하지만, 잠시 후에 알게 되겠지만, 그것은 사실이 아닌 것 같다.

---

문제

다음으로 시간 범위의 임시 테이블을 만들고 이전과 동일한 범위를 추가한 다음 이 테이블에 결합함으로써 문제를 더 복잡하게 만들겠습니다.

DROP TABLE IF EXISTS `_ranges`;
CREATE TEMPORARY TABLE `_ranges` (
    `Start` TIMESTAMP NOT NULL DEFAULT 0,
    `End`   TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY(`Start`, `End`)
);

INSERT INTO `_ranges` VALUES(@a, @b));

지금:

SELECT
    UNIX_TIMESTAMP(`_ranges`.`Start`) AS `Start_TS`,
    COUNT(`Alarms`.`AlarmId`) AS `n`
FROM `_ranges`
LEFT JOIN `Alarms` USE INDEX (`KeyB`) ON
(
    `Alarms`.`EndedAt` IS NULL
    AND `Alarms`.`Acknowledged` = FALSE

    AND `Alarms`.`StartedAt` < `_ranges`.`End`
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
) OR (
    `Alarms`.`EndedAt` IS NOT NULL
    AND `Alarms`.`EndedAt` >= `_ranges`.`Start`
    AND `Alarms`.`Pinned` = TRUE

    AND `Alarms`.`StartedAt` < `_ranges`.`End`
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
)
GROUP BY `_ranges`.`Start`

150밀리초까지는 괜찮은 편입니다.제 경험상으로는 이것은 적절한 질문 플랜입니다.

이제 몇 가지 시간 범위를 추가해 보겠습니다.

TRUNCATE TABLE `_ranges`;
INSERT INTO `_ranges` VALUES
    (DATE_ADD(@a, INTERVAL 3600*0  SECOND), DATE_ADD(@a, INTERVAL 3600*1  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*1  SECOND), DATE_ADD(@a, INTERVAL 3600*2  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*2  SECOND), DATE_ADD(@a, INTERVAL 3600*3  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*3  SECOND), DATE_ADD(@a, INTERVAL 3600*4  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*4  SECOND), DATE_ADD(@a, INTERVAL 3600*5  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*5  SECOND), DATE_ADD(@a, INTERVAL 3600*6  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*6  SECOND), DATE_ADD(@a, INTERVAL 3600*7  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*7  SECOND), DATE_ADD(@a, INTERVAL 3600*8  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*8  SECOND), DATE_ADD(@a, INTERVAL 3600*9  SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*9  SECOND), DATE_ADD(@a, INTERVAL 3600*10 SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*10 SECOND), DATE_ADD(@a, INTERVAL 3600*11 SECOND)),
    (DATE_ADD(@a, INTERVAL 3600*10 SECOND), DATE_ADD(@a, INTERVAL 3600*12 SECOND))
;

이제 동일한 쿼리가 1초 이상 걸립니다.계획 다시 쿼리:

더 많은 시간 범위를 추가하면 속도가 점점 느려지고 각각의 기간이 별로 중요하지 않은 것 같습니다. 그러나 이 특정 사례에 대한 실제 결과는 전혀 중요하지 않습니다.Alarms일치:

실제 사용 사례에서는 필요에 따라 시간 범위의 선택이 달라집니다. 예를 들어, 12시간 동안 15분 분량의 시간 조각이 필요한데, 현재 이 작업을 생성하는 데 약 5초가 걸립니다.추가하기 전에KeyB30초 넘게 걸렸어요

쿼리 조건을 조금 바꾸면(이것도 필요합니다)

SELECT
    UNIX_TIMESTAMP(`_ranges`.`Start`) AS `Start_TS`,
    COUNT(`Alarms`.`AlarmId`) AS `n`
FROM `_ranges`
LEFT JOIN `Alarms` USE INDEX (`KeyB`) ON
(
    `Alarms`.`EndedAt` IS NULL
    AND `Alarms`.`Acknowledged` = TRUE

    AND `Alarms`.`StartedAt` < `_ranges`.`End`
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
) OR (
    `Alarms`.`EndedAt` IS NOT NULL
    AND `Alarms`.`EndedAt` >= `_ranges`.`Start`
    AND `Alarms`.`Pinned` = FALSE

    AND `Alarms`.`StartedAt` < `_ranges`.`End`
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
)
GROUP BY `_ranges`.`Start`;

…이제 델은 몇 가지 요구에 부응하고 있습니다.Alarms행:

…하지만 성능은 여전히 비슷합니다.

따라서 병목현상은 실제 결합에 방해가 된다고 생각합니다._ranges구성되었습니다.

왜요? 어떻게 고칠 수 있죠?

(MariaDB 5.5.56/CentOS 7에서 취득한 상기 쿼리 플랜은 MySQL 5.1.73/CentOS 6에서도 솔루션이 동작해야 합니다.)

언급URL : https://stackoverflow.com/questions/48873266/why-does-this-join-to-a-temporary-table-slow-down-my-query-so-much