考虑这个表：
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`)
) ENGINE=INNODB;
并且，对于此测试，请像这样填充它：
-- Populate some dummy data; 500 alarms for each
-- of 1000 one-second periods
SET @testDevice = UNHEX('00030000000000000000000000000000');
DROP PROCEDURE IF EXISTS `injectAlarms`;
DELIMITER ;;
CREATE PROCEDURE injectAlarms()
BEGIN
SET @fromdate = '2018-02-18 00:00:00';
SET @numdates = 1000;
SET @todate = DATE_ADD(@fromdate, INTERVAL @numdates SECOND);
-- Create table of alarm codes to join on
DROP TABLE IF EXISTS `__codes`;
CREATE TEMPORARY TABLE `__codes` (
`Code` BIGINT NOT NULL PRIMARY KEY
);
SET @startcode = 0;
SET @endcode = 499;
REPEAT
INSERT INTO `__codes` VALUES(@startcode);
SET @startcode = @startcode + 1;
UNTIL @startcode > @endcode END REPEAT;
-- Add an alarm for each code, for each second in range
REPEAT
INSERT INTO `Alarms`
(`DeviceId`, `Code`, `Ended`, `NaturalEnd`, `Pinned`, `Acknowledged`, `StartedAt`, `EndedAt`)
SELECT
@testDevice,
`Code`,
TRUE, FALSE, FALSE, FALSE,
@fromdate, @fromdate
FROM `__codes`;
SET @fromdate = DATE_ADD(@fromdate, INTERVAL 1 SECOND);
UNTIL @fromdate > @todate END REPEAT;
END;;
DELIMITER ;
CALL injectAlarms();
现在，对于某些数据集，以下查询效果很好：
SELECT * FROM `Alarms`
WHERE
((`Alarms`.`Ended` = FALSE AND `Alarms`.`Acknowledged` = FALSE) OR `Alarms`.`Pinned` = TRUE) AND
`MarkedForDeletion` = FALSE AND
`DeviceId` = @testDevice
;
这是因为 MariaDB 足够聪明，可以使用索引合并，例如：
id select_type table type possible_keys
1 SIMPLE Alarms index_merge Key1,Key2,Key3,Key4,Key5,Key6
key key_len ref rows Extra
Key1,Key2,Key3 2,1,17 (NULL) 2 Using union(Key1,intersect(Key2,Key3)); Using where
但是，如果我使用上述过程填充的数据集，并稍微翻转查询（这是我需要的另一个视图，但在这种情况下将返回更多行）：
SELECT * FROM `Alarms`
WHERE
((`Alarms`.`Ended` = TRUE OR `Alarms`.`Acknowledged` = TRUE) AND `Alarms`.`Pinned` = FALSE) AND
`MarkedForDeletion` = FALSE AND
`DeviceId` = @testDevice
;
……它没有：
id select_type table type possible_keys
1 SIMPLE Alarms ref Key1,Key2,Key3,Key4,Key5,Key6
key key_len ref rows Extra
Key2 1 const 144706 Using where
我宁愿希望索引合并更频繁地发生。事实上，鉴于ref=const，这个查询计划看起来并不太可怕……但是，查询需要几乎一秒钟的时间来运行。这本身并不是世界末日，但在尝试更奇特的查询时，我的设计的可扩展性较差，这需要一个very很久：
-- Create a temporary table that we'll join against in a mo
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`)
);
-- Populate it (in reality this is performed by my application layer)
SET @endtime = 1518992216;
SET @starttime = @endtime - 86400;
SET @inter = 900;
DROP PROCEDURE IF EXISTS `populateRanges`;
DELIMITER ;;
CREATE PROCEDURE populateRanges()
BEGIN
REPEAT
INSERT IGNORE INTO `_ranges` VALUES(FROM_UNIXTIME(@starttime),FROM_UNIXTIME(@starttime + @inter));
SET @starttime = @starttime + @inter;
UNTIL @starttime > @endtime END REPEAT;
END;;
DELIMITER ;
CALL populateRanges();
-- Actual query
SELECT UNIX_TIMESTAMP(`_ranges`.`Start`) AS `Start_TS`,
COUNT(`Alarms`.`AlarmId`) AS `n`
FROM `_ranges`
LEFT JOIN `Alarms`
ON `Alarms`.`StartedAt` < `_ranges`.`End`
AND (`Alarms`.`EndedAt` IS NULL OR `Alarms`.`EndedAt` >= `_ranges`.`Start`)
AND ((`Alarms`.`EndedAt` IS NULL AND `Alarms`.`Acknowledged` = FALSE) OR `Alarms`.`Pinned` = TRUE)
-- Again, the above condition is sometimes replaced by:
-- AND ((`Alarms`.`EndedAt` IS NOT NULL OR `Alarms`.`Acknowledged` = TRUE) AND `Alarms`.`Pinned` = FALSE)
AND `DeviceId` = @testDevice
AND `MarkedForDeletion` = FALSE
GROUP BY `_ranges`.`Start`
（此查询应该收集每个时间片的计数列表，每个计数指示有多少个警报的 [StartedAt,EndedAt] 范围与该时间片相交。结果填充线图。）
同样，当我设计这些表时，它们中的行并不多，索引合并似乎使一切都顺利进行。但现在不是这样：使用中给出的数据集injectAlarms()，这需要40秒去完成！
添加时我注意到了这一点MarkedForDeletion专栏并执行我的一些第一个大型数据集规模测试。这就是为什么我选择的索引并没有因为存在而产生什么大问题MarkedForDeletion，尽管如果我删除上面描述的结果是相同的AND MarkedForDeletion = FALSE根据我的询问；然而，我保留了这个条件，因为最终我需要它存在。
我尝试过一些USE INDEX/FORCE INDEX组合，但它似乎从未使用索引合并作为结果。
我可以定义哪些索引以使该表在给定情况下快速运行？或者我如何重组我的查询以实现相同的目标？
(上述查询计划在 MariaDB 5.5.56/CentOS 7 上获得，但解决方案也必须适用于 MySQL 5.1.73/CentOS 6。）
