我有一个表,每天至少增长200万条记录,我必须每天运行统计数据。由于我的统计查询可能需要三个小时以上的时间来运行:0,所以我正在尝试对表进行一些优化。我认为我应该利用分区,以便查询优化器可以利用分区修剪,但是当我运行查询时,仍然会查看所有分区。
我已经创建了一个测试表,也可以在mysql提琴
CREATE TABLE `log_tests` (
`_id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(25) DEFAULT NULL,
PRIMARY KEY (`_id`,`timestamp`),
KEY `log_tests__timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (unix_timestamp(`timestamp`))
(PARTITION p201401 VALUES LESS THAN (unix_timestamp('2014-02-01 00:00:00')) ENGINE = MyISAM,
PARTITION pNew VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
;
INSERT INTO `log_tests` (`timestamp`, `name`) VALUES ('2014-01-10 01:01:01', '1');
INSERT INTO `log_tests` (`timestamp`, `name`) VALUES ('2014-01-11 01:01:01', '2');
INSERT INTO `log_tests` (`name`) VALUES ('3');
INSERT INTO `log_tests` (`name`) VALUES ('4');
INSERT INTO `log_tests` (`name`) VALUES ('5');
现在…当我为1月30日之前的时间轴运行带有where的select语句时,将查看两个分区,而不仅仅是p201401分区。例如:
explain partitions select * from log_tests
where unix_timestamp(`timestamp`) < unix_timestamp('2014-01-31 00:00:00')
的回报:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra
---------------------------------------------------------------------------------------------------------------
1 | SIMPLE | log_tests | p201401,pNew | ALL | NULL | NULL | NULL | NULL | 5 | Using where
有什么至理名言吗?问题在于如何执行查询,分区工作。
当你这样做
explain partitions select * from log_tests
where unix_timestamp(`timestamp`) < unix_timestamp('2014-01-31 00:00:00')
将函数应用于列值。当对列应用函数时,MySQL总是强制执行全表扫描,因为所有行都需要应用该函数才能计算表达式。如果考虑函数rand()
,可能会更容易理解,那么很明显,每一行都必须求值。
如果您将查询更改为
explain partitions select * from log_tests
where timestamp < '2014-01-31 00:00:00';
它正确地只使用了一个分区。看这把小提琴。
顺便说一句,这适用于所有查询,而不仅仅是分区表上的查询。您不应该对列值应用函数,因为它每次都会进行一次全表扫描。