mysql - 使用分区修剪优化查询

Question

我有一个每天至少增长 200 万条记录的表，我必须每天在上面运行统计信息。由于我的统计查询可能需要三个多小时才能运行 :O 我正在尝试对表进行一些优化。我以为我会利用分区，以便查询优化器可以利用分区修剪，但是当我运行我的查询时，所有分区仍在被查看。

我已经创建了一个测试表，也可以在mysql fiddle上使用

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

有什么智慧的话？？？

Answer 1

问题在于您如何进行查询、分区工作。

当你做的时候

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';

它正确地只使用了一个分区。看这个fiddle .

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