Mysql分区：包含超出日期范围的分区

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

经过查询，我发现它使用“old”分区，该分区不应该包含任何相关数据。

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

Answer 2

您已经在 TO_DAYS(date) 上进行了分区，这意味着分区修剪大多数情况下只会发生在简单的情况下，除非您对约束应用 TO_DAYS(date) 。

你必须这样做，例如 select * from ptest where day between TO_DAYS('2010-03-11') 和 TO_DAYS('2010-03-12') - 尽管在这种情况下这可能是 mysql 之间的缺点。

mysql中对日期进行分区是很困难的，而且分区实现上有很多缺点，至少如果你想覆盖很多不同的查询约束，我们通常在表中放置一个指向日历的整数id而不是DATE类型，因为我们发现 mysql 处理简单整数上的分区与涉及函数（例如 TO_DAYS）的列上的分区相比相当可靠，我们有

create table datatbl (
  time_id int NOT NULL,
  ....
);

time_id 引用一个预先填充了未来 10 年日期的日历，看起来像

create table calendar (
  time_id int primary key
  year int NOT NULL,
  month int NOT NULL,
  day int NOT NULL,
  dayofyear int NOT NULL,
  quarter int NOT NULL,
  is_weekend char(1) NOT NULL,
  db_date DATE not NULL,
  unique index(year,month,day),
  unique index(dbdate)
);

查询已连接到此表，因此获取一个月的所有数据只需要 where cal.year = 2010 and cal.month = 1 。或者也可以这样做，因为 cal.db_date 在 '2010-01-01' 和 '2010-01-31' 之间

datatbl 在 time_id 上进行分区，上面的查询将让mysql做分区修剪。 time_id 也是年/月/日的组合，因此 2010-03-03 的 time_id 将是整数 20100303 ，不应该用于查询，这只是自动创建 new/drop 的脚本的一个方便旧分区。