mysql - 如何对 MySql 表进行分区以使用 90 天旋转分区？

Question

我想创建一个分区表，其中将填充数亿条记录。使用分区，我如何才能将某一天的记录放入一个分区，然后将第二天的记录放入另一个分区，等等。然后在九十多天之后，我可以从最旧的分区中删除旧数据。

我试过这个声明(散列函数使用分区数量的模数来计算哪个分区获取数据)。这确保每天使用 92 个分区中的不同分区；除了它不起作用。

CREATE TABLE records(
    id INT NOT NULL AUTO_INCREMENT,
    dt DATETIME,
    PRIMARY KEY (id)
)
PARTITION BY HASH((MOD(DAYOFYEAR(dt), 92) + 92))
PARTITIONS 92;

上述代码段的问题在于哈希表达式中使用的列必须是表中的唯一键。

我该如何解决这个问题，以便根据每天的记录有九十个左右的旋转分区？

如果我只是将 dt 列添加到主键，如果选择一个日期范围，它似乎会命中所有分区，这不是我想要的。

有什么想法吗？

Answer 1

原因是要在日期字段上分区并按范围查询，您必须在分区表达式中使用 YEAR() 或 TO_DAYS()。

像这样的分区按预期工作:

CREATE TABLE `alert` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `eventId` int(10) unsigned NOT NULL,
  `occurred` datetime NOT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (TO_DAYS(occurred))
(PARTITION 28_06 VALUES LESS THAN (735413) ENGINE = InnoDB,
 PARTITION 29_06 VALUES LESS THAN (735414) ENGINE = InnoDB,
 PARTITION 30_06 VALUES LESS THAN (735415) ENGINE = InnoDB,
 PARTITION 01_07 VALUES LESS THAN (735416) ENGINE = InnoDB,
 PARTITION 02_07 VALUES LESS THAN (735417) ENGINE = InnoDB,
 PARTITION 03_07 VALUES LESS THAN (735418) ENGINE = InnoDB,
 PARTITION 04_07 VALUES LESS THAN (735419) ENGINE = InnoDB,
 PARTITION 05_07 VALUES LESS THAN (735420) ENGINE = InnoDB,
 PARTITION 06_07 VALUES LESS THAN (735421) ENGINE = InnoDB,
 PARTITION 07_07 VALUES LESS THAN (735422) ENGINE = InnoDB) */

mysql> explain partitions SELECT * FROM alert WHERE occurred >= '2013-07-02' and occurred <= '2013-07-04';
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | alert | 02_07,03_07,04_07 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+

然后您需要自己管理分区的删除和创建。