如何在此表上使用 MySQL 表分区？
我有一个基本上如下所示的表：
CREATE TABLE myTable (
id INT auto_increment,
field1 TINYINT,
field2 CHAR(2),
field3 INT,
theDate DATE,
otherStuff VARCHAR(20)
PRIMARY KEY (id)
UNIQUE KEY (field1, field2, field3)
)
我想根据theDate的月份和年份对表进行分区，但是手册告诉我不允许：
分区表的分区表达式中使用的所有列都必须是该表可能具有的每个唯一键的一部分。换句话说，表上的每个唯一键都必须使用表分区表达式中的每一列
我有哪些选择？我还可以对表进行分区吗？
I have a table that essentially looks like this:
CREATE TABLE myTable (
id INT auto_increment,
field1 TINYINT,
field2 CHAR(2),
field3 INT,
theDate DATE,
otherStuff VARCHAR(20)
PRIMARY KEY (id)
UNIQUE KEY (field1, field2, field3)
)
I'd like to partition the table based on the month and year of theDate, however the manual is telling me I'm not allowed:
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. In other words, every unique key on the table must use every column in the table's partitioning expression
What are my options here? Can I still partition the table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我在这里写了一篇关于这个问题的博客文章 使用 MySQL 表分区扩展 Rails （ Rails 使用整数 PK）。同样的技术应该适用于您的情况，但不幸的是您必须删除 [field1, field2, field3] 唯一键。
删除唯一的密钥也是我处理过的一个问题（尽管帖子中没有提到）。我通过在创建记录之前进行存在性检查来解决这个问题，意识到由于竞争条件，我们仍然会偶尔受到欺骗。实际上，这并不是一个问题。然而，增加的查询吞吐量需要扩大 innodb 缓冲区大小，因为 I/O 非常残酷。
I wrote a blog post about this issue here Scaling Rails with MySQL table partitioning (rails uses integer PKs). The same technique should work in your case, except, unfortunately you have to drop the [field1, field2, field3] unique key.
Dropping a unique key was a problem I dealt with too (though not mentioned in the post). I worked around it by implemented an existence check before creating the record, realizing that we'd still get occasional dupes due to the race condition. In practice, that didn't turn out to be a problem. However, the increased query throughput required scaling up the innodb buffer size 'cause the i/o's we're brutal.
不，不是目前的形式。
您可以删除主键，而只是将“id”设为普通索引。辅助唯一索引仍然是一个问题，您需要删除它或更改分区方案以涉及日期列。
No, not in the current form.
You could remove the primary key and instead just make "id" a normal index. The secondary unique index would still be a problem and you would need to remove this or change the partitioning scheme to involve theDate column.
如果您根据日期时间列的年和月进行分区，您是否考虑过为每个年月组合创建一个表，然后使用 合并表？我知道这并不能直接回答你的问题，但认为它可能会有所帮助......
If you are partitioning based on the year and month of a datetime column, have you thought about having a table for each year-month combo and then using a merge table? I know this doesn't directly answer your question, but thought it may help...