두 가지 범위 조건을 가진 쿼리에 대한 인덱싱을 수행하려면 어떻게 해야 합니까?

두 가지 범위 조건을 가진 쿼리에 대한 인덱싱을 수행하려면 어떻게 해야 합니까?

다음 시리즈는…

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`,`DeviceId`,`Acknowledged`,`Ended`,`StartedAt`),
  KEY `Key7` (`MarkedForDeletion`,`DeviceId`,`Ended`,`Pinned`,`EndedAt`)
) ENGINE=INNODB;

타임스탬프가 선택되어 많은 행이 일치할 때 이 쿼리는 매우 느립니다.

SELECT (((UNIX_TIMESTAMP(`StartedAt`)) DIV 900) * 900) AS `Period_TS`,
COUNT(`AlarmId`) AS `n`
FROM `Alarms`
WHERE `StartedAt` >= FROM_UNIXTIME(1518990000)  
AND `StartedAt` <  FROM_UNIXTIME(1518998400) 
AND `DeviceId` IN (
    UNHEX('00030000000000000000000000000000'),
    UNHEX('000300000000000000000000000181cd'),
    UNHEX('000300000000000000000000000e7cf6'),
    UNHEX('000300000000000000000000000e7cf7'),
    UNHEX('000300000000000000000000000f423f')
) AND `MarkedForDeletion` = FALSE
GROUP BY `Period_TS` ASC;

저는 이것이 제가 두 개의 필드에서 범위 조건을 혼합하고 있기 때문이라고 믿습니다(DeviceId그리고.StartedAt).

그렇다면 문제를 해결하려면 어떻게 해야 합니까?아마도 인덱스 병합의 사용을 촉발할 무언가가 있습니까?

IN사이에 있는 것입니다.='범위'를 지정합니다.그래서, 저는 질문의 제목과 논쟁합니다.두 가지 범위를 최적화하는 것은 사실상 불가능합니다.IN범위가 최적화될 가능성이 있습니다.

에 기반을 둔

WHERE `StartedAt` >= FROM_UNIXTIME(1518990000)  
AND   `StartedAt` <  FROM_UNIXTIME(1518998400) 
AND `DeviceId` IN (
    UNHEX('00030000000000000000000000000000'),
    UNHEX('000300000000000000000000000181cd'),
    UNHEX('000300000000000000000000000e7cf6'),
    UNHEX('000300000000000000000000000e7cf7'),
    UNHEX('000300000000000000000000000f423f')
) AND `MarkedForDeletion` = FALSE

두 개의 인덱스를 제공하고 Optimizer가 사용할 인덱스를 결정하도록 하겠습니다.

INDEX(MarkedForDeletion, StartedAt, DeviceId)
INDEX(MarkedForDeletion, DeviceId, StartedAt)

MySQL/MariaDB의 최신 버전 중 일부는 건너뛰고 두 번째 인덱스의 세 개 열을 모두 사용할 수 있습니다.모든 버전에서 각 인덱스의 처음 두 열은 해당 인덱스를 후보로 만듭니다.선택은 통계에 의해 결정될 수 있으며 '올바른' 선택일 수도 있고 아닐 수도 있습니다.

부터AlarmId있을 수 없는NULL패턴 사용:COUNT(*).

변경한 후에는 각 인덱스가 "적용"되므로 성능이 더욱 향상됩니다.

MySQL은 "스킵-스캔" 인덱스 작업을 지원하지 않으므로 진단 시 정확할 수 있습니다.다음을 사용하여 논리를 분할할 수 있습니다.union all:

SELECT . . .
FROM ((SELECT a.*
       FROM alarms a
       WHERE MarkedForDeletion = FALSE AND
             DeviceId = UNHEX('00030000000000000000000000000000') AND
             StartedAt >= FROM_UNIXTIME(1518990000) AND
             StartedAt <  FROM_UNIXTIME(1518998400)
      ) UNION ALL
      (SELECT a.*
       FROM alarms a
       WHERE MarkedForDeletion = FALSE AND
             DeviceId = UNHEX('000300000000000000000000000181cd') AND
             StartedAt >= FROM_UNIXTIME(1518990000) AND
             StartedAt <  FROM_UNIXTIME(1518998400)
      ) UNION ALL
      . . .
     ) a
GROUP BY `Period_TS` ASC;

이 쿼리의 경우 처음 세 개의 열이 있는 인덱스를 원합니다.(MarkedForDeletion, DeviceId, StartedAt).

언급URL : https://stackoverflow.com/questions/48892126/how-should-i-go-about-indexing-for-a-query-with-two-range-conditions