mysql范围查询如何建索引,我应该如何为具有两个范围条件的查询建立索引？

Next in the series…

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;

This query is quite slow when the timestamps are chosen such that many rows are matched:

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;

I believe that this because I am mixing range conditions on two fields (DeviceId and StartedAt).

If that's true, what can I do to resolve the problem? Perhaps something to trigger use of an index merge?

解决方案

IN is sort of between = and a 'range'. So, I quibble with the title on the Question. Two ranges is virtually impossible to optimize; an IN plus a range has some chance of optimization.

Based on

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

I would provide 2 indexes and let the Optimizer decide which to use:

INDEX(MarkedForDeletion, StartedAt, DeviceId)

INDEX(MarkedForDeletion, DeviceId, StartedAt)

Some newer versions of MySQL/MariaDB can leapfrog and make use of all 3 columns in the second index. In all versions the first 2 columns of either index makes it a candidate. The choice may be driven by statistics, and may (or may not) be the 'right' choice.

Since AlarmId cannot be NULL, use the pattern: COUNT(*).

After making that change, each of my indexes is "covering", thereby giving an extra boost in performance.