调优大小大于2GB的表的SQL查询

Question

我有一个记录数以百万计的表，表的大小目前是2GB，预计还会继续增长。

表结构

CREATE TABLE `test` (
`column_1` int(11) NOT NULL AUTO_INCREMENT,
`column_2` int(11) NOT NULL,
`column_3` int(11) NOT NULL,
`column_4` int(11) NOT NULL,
`column_5` datetime NOT NULL,
`column_6` time NOT NULL,
 PRIMARY KEY (`column_1`),
UNIQUE KEY `index_1` (`column_2`,`column_3`),
UNIQUE KEY `index_2` (`column_2`,`column_4`),
KEY `index_3` (`column_3`),
KEY `index_4` (`column_4`),
KEY `index_5` (`column_2`),
KEY `index_6` (`column_5`,`column_2`),
CONSTRAINT `fk_1` FOREIGN KEY (`column_3`) REFERENCES `test2`(`id`),
CONSTRAINT `fk_2` FOREIGN KEY (`column_4`) REFERENCES `test2` (`id`),
CONSTRAINT `fl_3` FOREIGN KEY (`column_2`) REFERENCES `link` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14164023 DEFAULT CHARSET=utf8;

当我运行以下查询时，对于不同的column_2值，大约需要5-8秒的时间。有人能帮助更好地执行这个查询吗？

SELECT count(*)  FROM test WHERE test.column_2= 26 and
test.column_5 between '2015-06-01 00:00:00' AND
'2015-06-30 00:00:00'  ;

注意:上面提到的时间是通过在mysql工作台上执行查询来捕获的。

Answer 1

您的index_6当前有column_5，然后是column_2，因此MySQL首先尝试基于BETWEEN子句进行筛选。然而，MySQL的局限性是在范围模式下使用索引之后，它不能使用索引的第二部分(在这个博客帖子中有更多的信息)。

优化这类查询的正确方法是将等式列作为索引的第一部分，以范围列作为第二部分。然后MySQL将选择具有column_2值为26的行，然后使用索引的第二部分根据column_5日期范围对其进行进一步筛选。

因此，解决方案是有一个索引：

KEY `ind_c2_c5` (`column_2`,`column_5`)

顺便说一句，最好给出索引的描述性名称，这样您就可以一见钟情地知道它们是用来做什么的。