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

Question

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

之前Murphy’s Law震惊了，我运气不错，看起来像这样:

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出现在两个批处理中。换句话说，时间戳列是可索引的，但不是主键。

幸运的是，我可以使用数据中的主键。我的意图是执行 INSERT IGNORE 并让 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 约束中，否则会出现此错误:

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Partitioning Keys, Primary Keys, and Unique Keys 上的 MySQL 手册说:

All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

所以让PK同时拥有两列看似多余，但却是必须的。