mysql - 查询需要将近两秒钟，但只匹配两行——为什么索引没有帮助？

Question

表:

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;

它目前有大约三百万行。

查询:

SELECT
COUNT(`AlarmId`) AS `n`
FROM `Alarms`
WHERE `StartedAt` < FROM_UNIXTIME(1519101900)
AND (`EndedAt` IS NULL OR `EndedAt` > FROM_UNIXTIME(1519101900))
AND `DeviceId` = UNHEX('00030000000000000000000000000000')
AND `MarkedForDeletion` = FALSE
AND (
     (`Alarms`.`EndedAt` IS NULL AND `Alarms`.`Acknowledged` = FALSE)
  OR ( `Alarms`.`EndedAt` IS NOT NULL AND `Alarms`.`Pinned` = TRUE)
)

查询计划:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  Alarms  range   Key2,Key3,Key4,Key5,Key6,KeyB   KeyB    21              1574778 Using where; Using index

耗时:1,763,222μs

在这种特殊情况下，查询(正确地)甚至没有匹配很多行(结果是 n = 2)。

利用我从使用索引合并中学到的知识(虽然我仍然没有做对)，我尝试稍微重新组织条件(原始是由一些 C++ 生成的，基于输入条件，因此奇怪的运算符分布):

SELECT COUNT(`AlarmId`) AS `n`
FROM `Alarms`
WHERE 
(
    `EndedAt` IS NULL
    AND `Acknowledged` = FALSE

    AND `StartedAt` < FROM_UNIXTIME(1519101900)
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
) OR (
    `EndedAt` > FROM_UNIXTIME(1519101900)
    AND `Pinned` = TRUE

    AND `StartedAt` < FROM_UNIXTIME(1519101900)
    AND `MarkedForDeletion` = FALSE
    AND `DeviceId` = UNHEX('00030000000000000000000000000000')
);

...但结果是一样的。

那么为什么要花这么长时间？我如何修改它/索引以使其立即运行？

Answer 1

• OR 是出了名的难以优化。
• MySQL 几乎从不在单个查询中使用两个索引。

要避免这两种情况，请将 OR 转换为 UNION。每个 SELECT 都可以使用其不同的索引。因此，为每个构建一个最优的 INDEX。

实际上，由于您只执行 COUNT，因此您也可以评估两个单独的计数并将它们相加。

SELECT ( SELECT  COUNT(*)
            FROM  `Alarms`
            WHERE  `EndedAt` IS NULL
              AND  `Acknowledged` = FALSE
              AND  `StartedAt` < FROM_UNIXTIME(1519101900)
              AND  `MarkedForDeletion` = FALSE
              AND  `DeviceId` = UNHEX('00030000000000000000000000000000' )
       ) + 
       ( SELECT  COUNT(*)
            FROM  `Alarms`
            WHERE  `EndedAt` > FROM_UNIXTIME(1519101900)
              AND  `Pinned` = TRUE
              AND  `StartedAt` < FROM_UNIXTIME(1519101900)
              AND  `MarkedForDeletion` = FALSE
              AND  `DeviceId` = UNHEX('00030000000000000000000000000000')
       ) AS `n`;

INDEX(DeviceId, Acknowledged, MarkedForDeletion, EndedAt, StartedAt) -- for first
INDEX(DeviceId, Pinned, MarkedForDeletion, EndedAt, StartedAt) -- for second
INDEX(DeviceId, Pinned, MarkedForDeletion, StartedAt, EndedAt) -- for second

好吧，如果有重叠，那将不起作用。那么，让我们回到 UNION 模式:

SELECT  COUNT(*) AS `n`
    FROM
    (
        ( SELECT  AlarmId
            FROM  `Alarms`
            WHERE  `EndedAt` IS NULL
              AND  `Acknowledged` = FALSE
              AND  `StartedAt` < FROM_UNIXTIME(1519101900)
              AND  `MarkedForDeletion` = FALSE
              AND  `DeviceId` = UNHEX('00030000000000000000000000000000')  
        )
        UNION DISTINCT
        ( SELECT  AlarmId
            FROM  `Alarms`
            WHERE  `EndedAt` > FROM_UNIXTIME(1519101900)
              AND  `Pinned` = TRUE
              AND  `StartedAt` < FROM_UNIXTIME(1519101900)
              AND  `MarkedForDeletion` = FALSE
              AND  `DeviceId` = UNHEX('00030000000000000000000000000000')
         )
    );

再次添加这些索引。

每个 INDEX 中的前几列可以按任何顺序排列，因为它们是用 =(或 IS NULL)测试的。最后一两个是“范围”测试。只有第一个范围将用于过滤，但我包含了另一列以便索引“覆盖”。

我的公式可能比“索引合并”更好。