Mysql分区: Partitions outside of date range is included

Question

我刚刚尝试根据日期配置分区，但似乎mysql仍然包含一个没有相关数据的分区。它将使用相关的分区，但由于某种原因还包括最旧的分区。我做错了吗？

版本是5.1.44 (MyISAM)

我先根据“day”添加了几个分区，是“date”类型

ALTER TABLE ptest
PARTITION BY RANGE(TO_DAYS(day))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2009-08-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2009-11-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-02-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-05-01'))
);

查询后发现，它使用的是“旧”分区，应该没有任何相关数据。

mysql> explain partitions select * from ptest where day between '2010-03-11' and '2010-03-12';
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | ptest      | p1,p4      | range | day           | day  | 3       | NULL | 79   | Using where |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+

当我选择一天时，它按预期工作:

mysql> explain partitions select * from ptest where day = '2010-03-11';
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
| 1  | SIMPLE      | ptest      | p4         | ref  | day           | day  | 3       | const | 39   |       |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+

Answer 1

这实际上是预期的结果，因为最旧的分区将始终保存未评估为有效日期(空)的值。一个解决方法是创建一个额外的分区，该分区将不包含任何数据并且存在于最早日期之前的所有值。此分区将始终被扫描，但对性能几乎没有影响，因为它是空的。

http://bugs.mysql.com/bug.php?id=49754