在基于范围的MySQL表分区中确定分区键

Question

我已经对MySQL中的数据库分区进行了一段时间的研究。由于我的DB中有一个不断增长的表，所以我认为使用分区作为优化它的有效工具。我只想保留最近的数据(比如最近的6个月)，表中有一个列名“CREATED_AT”(时间戳，非主)，在我脑海中出现的方法如下

• 使用“CREATED_AT”作为分区键，在表上创建一个基于时间的范围分区。
• 定期运行DB级事件并删除过时的分区。(6个月以上)。

但是，只有将'CREATED_AT‘字段作为主字段才能实现分区。但这不违反主要的关键原则吗？因为相同的字段是非唯一的，并且可以有大量具有相同值的行，那么标记为主字段不是反模式吗？在这个场景中，基于时间的远程分区有什么解决办法吗？

Answer 1

这是一个阻止许多MySQL用户使用分区的问题。

用于分区键的列必须位于表的每个主键或唯一键中。它不一定是这些键中唯一的列(因为键可以是多列的)，但它必须是每个唯一键的一部分。

然而，在许多表中，这将违反表的逻辑设计。所以分区是不实际的。

您可以咬紧牙关，设计一个具有折衷设计的分区的表：

create table mytable (
 id bigint auto_increment not null,
 created_at datetime not null,
 primary key (id, created_at)
) partition by range columns (created_at) (
 partition p20190101 values less than ('2019-01-01'),
 partition p20190201 values less than ('2019-02-01'),
 partition p20190301 values less than ('2019-03-01'),
 partition p20190401 values less than ('2019-04-01'),
 -- etc...
 partition pMAX values less than (MAXVALUE)
);

我测试了这个表，定义它时没有错误。尽管该表在技术上允许具有相同id值的多行(如果它们具有不同的时间戳)，但实际上您可以编写应用程序代码，让id值自动递增，并且永远不会更改id。只要您的代码是唯一一个插入数据的应用程序，您就可以或多或少地确保数据不包含具有相同id的多个行。

您可能认为可以添加一个次要的唯一键约束，以强制执行id本身必须是唯一的。但这违反了分区规则：

mysql> alter table mytable add unique key (id);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

您只需相信应用程序不会插入无效数据。

或者，忘记使用分区，而只是向created_at列添加一个索引，使用增量删除而不是使用DROP分区来删除旧数据。

后一种策略是我所看到的几乎在每一种情况下使用的策略。通常，让关系数据库管理系统对id列执行严格的唯一性是很重要的。允许这种唯一性不被强制执行是不安全的。

关于你的评论：

难道删除整个分区不是比执行增量删除要便宜得多吗？

是也不是。

DELETE可以回滚，因此会产生一些开销，比如在rollback段中临时存储数据。另一方面，它只锁定与索引搜索匹配的行。

删除分区不会进行回滚，因此有一些步骤可以跳过。但是它做了一个ALTER，所以它首先需要在整个表上获得一个元数据锁。任何并发查询，无论是读查询还是写查询，都会阻止该查询并被其阻塞。

演示：

打开两个MySQL客户端窗口。在第一届会议上这样做：

mysql> START TRANSACTION;
mysql> SELECT * FROM mytable;

这在表上持有一个元数据锁，这会阻止类似ALTER之类的事情。

在第二个窗口：

mysql> ALTER TABLE mytable DROP PARTITION p20190101;
<pauses, waiting for the metadata lock held by the first session!>

您甚至可以打开第三个会话并执行以下操作：

mysql> SELECT * FROM mytable;
<also pauses>

第二个选择在ALTER后面等待。它们都排队等待元数据锁。

如果提交第一个选择，则ALTER最终完成：

mysql> ALTER TABLE mytable DROP PARTITION p20190101;
Query OK, 0 rows affected (6 min 25.25 sec)

那6分25秒并不是因为执行DROP分区需要很长时间。这是因为在写这篇文章的时候，我一直没有提交我的交易。

元数据锁等待不会超时，就像InnoDB行锁一样，50秒后就会超时。默认的元数据锁定超时是1年！参见timeout

像ALTER、DROP、RENAME表，甚至CREATE触发器之类的语句都需要获得一个元数据锁。

因此，在某些情况下，取决于是否有长期运行的事务保存元数据锁，使用DELETE以增量方式删除数据可能会更好，即使需要更长的时间。