使用时间戳和主键进行表分区

Question

我正在将结构化日志数据导入MySQL。我对使用术语"大数据"犹豫不决，但大小并不小——表中大约有 50 列，需要每秒连续导入 1,000 条记录。还要求将所有这些数据导入到同一数据库中的同一表中。实际上，对于任何性能(和理智)而言，我看到的唯一选择是对表结构进行分区。因为它是日志数据，所以有一个时间戳列，我可以安全地假设报告将包含在其查询中，并且是索引和分区定义中使用的主要候选者。

在墨菲定律出现之前，我运气很好，看起来像这样：

CREATE TABLE `poorly_designed_log_table` (
...
`timestamp` INTEGER UNSIGNED NOT NULL,
...
INDEX (`timestamp`)
)
ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (`timestamp`) (
PARTITION p0001 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01')),
PARTITION p0002 VALUES LESS THAN (UNIX_TIMESTAMP('2017-02-01')),
PARTITION p0003 VALUES LESS THAN (UNIX_TIMESTAMP('2017-03-01')),
...

我一次批量接收大约 100-200 行的传入数据，我使用事务和多行 INSERT 语句将其推送到数据库。但是，由于数据提供给我的方式，批次之间可能存在重复记录。例如，给定从 10：30：00 到 10：35：59 的一个批次和从 10：35：00 到 10：40：59 的下一批，则 10：35：00 到 10：35：59 之间的记录出现在两个批次中。换句话说，时间戳列是可索引的，但不是主键。

幸运的是，我可以使用的数据中有一个主键。我的目的是执行插入忽略并让MySQL自行拒绝重复项。我知道我需要更改我的定义以便强制执行主键，并且我也知道该键需要包含在分区定义中。主键是 VARCHAR(36)，采用以下形式：XXXX--。例如，时间戳1499942130的三个唯一记录的主键将是 XXXX-1499942130-1、XXXX-1499942130-2 和 XXXX-1499942130-3。

我的问题是如何使用时间戳和主键定义分区，以便数据存储在物理"每月"表中以进行快速检索？

Answer 1

我让它工作：

CREATE TABLE `poorly_designed_log_table` (
`timestamp` int(10) unsigned NOT NULL,
`pk` varchar(36) NOT NULL,
PRIMARY KEY (`timestamp`,`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(`timestamp`, pk)
(PARTITION p0001 VALUES LESS THAN (1483257600, MAXVALUE),
PARTITION p0002 VALUES LESS THAN (1485936000, MAXVALUE),
PARTITION p0003 VALUES LESS THAN (1488355200, MAXVALUE)
);

我不得不将时间戳添加到 PRIMARY KEY 约束中，否则会出现此错误：

错误 1503 (HY000)：主键必须包含表分区函数中的所有列

MySQL手册关于分区键，主键和唯一键说：

分区表的分区表达式中使用的所有列都必须是该表可能具有的每个唯一键的一部分。

所以让PK同时拥有两列似乎是多余的，但这是必要的。