mysql - 提高 MySQL SELECT WHERE 日期时间查询性能

Question

我在查询结构如下的表时遇到性能问题:

CREATE TABLE `SerieDetailSet`(
`Id` int NOT NULL AUTO_INCREMENT UNIQUE, 
`TimeStamp` datetime NOT NULL, 
`DoubleValue` double, 
`StringValue` longtext, 
`ValidityStartDate` datetime NOT NULL, 
`ValidityEndDate` datetime NOT NULL, 
`ValidityStartOffset` int NOT NULL, 
`ValidityEndOffset` int NOT NULL, 
`Serie_Id` int NOT NULL, 
`SerieDetailGroup_Id` int NOT NULL);

ALTER TABLE `SerieDetailSet` ADD PRIMARY KEY (Id);

Serie_Id 和 SerieDetailGroup_Id 是外键，它们被索引

我还在 ValidityStartDate 和 ValidityEndDate 列中添加了索引

ALTER TABLE `seriedetailset` ADD INDEX `ValidityStartDate_ValidityEndDate` (`ValidityStartDate`, `ValidityEndDate`);

该表非常大(约 2000 万行)，我的典型 SELECT 查询是

SELECT * FROM `seriedetailset`
WHERE `Serie_Id` IN ( 109,110,111,112,113 ) AND  `ValidityStartDate` >= '2013-12-25 00:00:00' AND `ValidityStartDate` < '2013-12-25 13:15:00'

我必须做什么才能提高查询性能？现在不行了

编辑

解释一个典型的查询

EXPLAIN
SELECT *
FROM `SerieDetailSet`
WHERE ((`ValidityStartDate` >= '01/01/2013 00:45:00') AND (`ValidityStartDate` < '[01/01/2013 01:00:00]')) AND (`Serie_Id` IN ( 109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,155 ))

+------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+
| id   | select_type | table          | type | possible_keys          | key  | key_len | ref  | rows     | Extra       |
+------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+
|    1 | SIMPLE      | SerieDetailSet | ALL  | IX_FK_SerieSerieDetail | NULL | NULL    | NULL | 16440103 | Using where |
+------+-------------+----------------+------+------------------------+------+---------+------+----------+-------------+

Answer 1

如果这是您的查询:

SELECT *
FROM `seriedetailset`
WHERE `Serie_Id` IN ( 109,110,111,112,113 ) AND 
      `ValidityStartDate` >= '2013-12-25 00:00:00' AND `ValidityStartDate` < '2013-12-25 13:15:00'

那么相应的索引就是一个复合索引:

create index idx_seriedetailset_id_sd on seriedetailset(serie_id, validitystartdate);