MySQL 分区 VARCHAR(60)

Question

我有一个非常大的 5 亿行表，其中包含以下列:

• id - Bigint - 自动递增主索引。
• date - Datetime - 每个日期大约有 150 万行，超过 1 年的数据将被删除。
• uid - VARCHAR(60) - 用户 ID
• sessionNumber - INT
• start - INT - 开始时间的纪元。
• end - INT - 结束时间的纪元。
• 更多列与此查询不相关。

uid 和sessionNumber 的组合形成一个唯一索引。我还有一个日期索引。

由于规模庞大，我想对表格进行分区。

我的大部分访问都是按日期进行的，因此按日期范围进行分区似乎很直观，但由于日期不是唯一索引的一部分，所以这不是一种选择。

选项 1:RANGE PARTITION 在日期和 BEFORE INSERT TRIGGER

uid 和 sessionNumber 的唯一性被违反时，我并没有真正遇到常规问题。源数据是一致的，但是跨越两天的 session 可以连续两天插入，午夜是第一个的结束时间和第二个的开始时间。

我正在尝试了解是否可以删除唯一键并改为使用触发器

• 检查前一天是否有具有相同标识符的 session ，如果有，
• 更新结束日期。
• 取消实际插入。

但是，我不确定是否可以 1) 在同一张表上触发更新。或 2) 防止实际插入。

选项 2:LINEAR HASH PARTITION on UID

我的第二个选择是在 UID 上使用线性散列分区。但是，我看不到任何使用 VARCHAR 并将其转换为用于 HASH 分区的 INTEGER 的示例。

但是我找不到一个允许的方法来将 VARCHAR 转换为 INTEGER。例如

ALTER TABLE mytable
PARTITION BY HASH (CAST(md5(uid) AS  UNSIGNED integer)) 
PARTITIONS 20

返回分区函数不被允许。

Answer 1

HASH 分区必须使用 32 位整数。但是您不能简单地使用 CAST() 将 MD5 字符串转换为整数。

代替 MD5，CRC32() 可以采用任意字符串并将其转换为 32 位整数。但这也不是一个有效的分区函数。

mysql> alter table v partition by hash(crc32(uid));
ERROR 1564 (HY000): This partition function is not allowed

您可以使用 KEY Partitioning 按字符串进行分区而不是哈希分区。 KEY 分区接受字符串。它通过 MySQL 内置的 PASSWORD() 函数传递任何输入字符串，这基本上与 SHA1 相关。

但是，这会导致您的分区策略出现另一个问题:

mysql> alter table v partition by key(uid);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

您的表的主键 id 不包含您要作为分区依据的列 uid。这是 restriction of MySQL's partitioning :

every unique key on the table must use every column in the table's partitioning expression.

这是我正在测试的表格(最好将其包含在您的问题中):

CREATE TABLE `v` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `uid` varchar(60) NOT NULL,
  `sessionNumber` int(11) NOT NULL,
  `start` int(11) NOT NULL,
  `end` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`,`sessionNumber`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在进一步讨论之前，我想知道您为什么要使用分区？ “绝对大小”不是对表进行分区的理由。

与任何优化一样，分区是为了您要优化的特定查询而完成的。任何优化都会以牺牲其他查询为代价来改进一个查询。优化与表无关。表很高兴坐在那里有 50 亿行，它不在乎。优化是针对查询。

因此您需要知道您要针对哪些查询进行优化。然后决定一个策略。对于您需要优化的查询集，分区可能不是最佳策略!