отрим эту таблицу:

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этот план запроса не выглядит слишком страшным ... однако выполнение запроса занимает почти секунду. Это само по себе не конец света, но плохо масштабируемый характер моего дизайна показывает, когда пытаешься выполнить более экзотический запрос, который требуеточень много времени:

-- 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.)