使用分区修剪的查询优化



我有一个表,每天至少增长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';

它正确地只使用了一个分区。看这把小提琴。

顺便说一句,这适用于所有查询,而不仅仅是分区表上的查询。您不应该对列值应用函数,因为它每次都会进行一次全表扫描。

相关内容

  • 没有找到相关文章

最新更新