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

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

您用于分区键的列必须在表的每个 PRIMARY KEY 或 UNIQUE KEY 中。它不必是这些键中的唯一列（因为键可以是多列），但它必须是每个唯一键的一部分。

不过，在许多表中，它会违反表的逻辑设计。所以分区是不切实际的。

您可以咬紧牙关，设计一张带有折衷设计的隔板的桌子：

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列添加索引，并使用增量 DELETE 而不是使用 DROP PARTITION 来修剪旧数据。

后一种策略是我在几乎所有情况下看到的。通常，让 RDBMS 对id列强制执行严格的唯一性很重要。不强制执行这种唯一性是不安全的。

回复您的评论：

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

是和不是。

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

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

演示：

打开两个 MySQL 客户端窗口。在第一个会话中这样做：

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

这在表上持有一个元数据锁，它会阻止像 ALTER TABLE 这样的东西。

在第二个窗口中：

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

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

mysql> SELECT * FROM mytable;
<also pauses>

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

如果我提交第一个 SELECT，则 ALTER TABLE 最终完成：

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

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

元数据锁等待不会像 InnoDB 行锁那样超时，它会在 50 秒后超时。默认元数据锁定超时为1 年！请参阅https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lock_wait_timeout

像 ALTER TABLE、DROP TABLE、RENAME TABLE 这样的语句，甚至像 CREATE TRIGGER 这样的东西都需要获取元数据锁。

因此，在某些情况下，根据您是否有持有元数据锁的长时间运行的事务，使用 DELETE 增量删除数据可能会更好地提高并发吞吐量，即使需要更长的时间。

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

问题描述

解决方案

推荐阅读