logo标签列表

如何对具有外键的mySQL表进行分区？

mysqlforeign-keyspartitioning
6
2个回答
3
我强烈建议使用日期作为关键字对数据进行分片，以便将其归档到归档表中。如果需要从多个归档表报告，请使用视图或在应用程序中构建逻辑。
然而，在数据库结构正确的情况下，在分区或分片之前，您应该能够处理数千万行的表。
嗨，Gary。分片绝对是一种有趣的方法。我有点担心它会导致应用逻辑中需要大量的连接操作？ - Industrial
我会在你的设计中保留外键。为什么你觉得需要分区呢？我们有超过一亿行的表，性能表现非常好。在我看来，阴影效果比分区更好。MySQL当前的分区实现太过局限。 - Gary
在另一个问题中，我看到你提到你的表有40万条记录。如果它被正确索引，对于这样小的表进行分区不会带来任何性能上的好处。 - Gary
嗨，这个表格肯定会有超过400,000行。虽然我不记得问过关于一个400K表格的问题 :) - Industrial
你在那个信息中提到了 400K。也许我误解了。 - Gary
嗨，Gary，那是针对一个完全不同的应用程序，存储需求要小得多。我完全没有记得当时发布过那个 :) 非常感谢您的建议和研究 :) - Industrial
2

这取决于分区表中行大小是否是需要进行分区的原因。

如果行大小很小，而分区的原因是行的数量之多，则我不确定您应该怎么做。

如果行大小相当大，那么您是否考虑过以下内容：

设P为分区表，F为可能的外键引用表。创建一个新表X：

CREATE TABLE `X` (
    `P_id` INT UNSIGNED NOT NULL,
        -- I'm assuming an INT is adequate, but perhaps
        -- you will actually require a BIGINT
    `F_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`P_id`, `F_id`),
    CONSTRAINT `Constr_X_P_fk`
        FOREIGN KEY `P_fk` (`P_id`) REFERENCES `P`.`id`
        ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `Constr_X_F_fk`
        FOREIGN KEY `F_fk` (`F_id`) REFERENCES `F`.`id`
        ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

关键是，为表格P创建一个存储过程。您的存储过程应该确保（使用事务），每当向表格P添加一行时，都会向表格X添加相应的行。您不能以“正常”的方式向P添加行！只有使用您的存储过程添加行，才能保证引用完整性得到维护。您可以自由地按照正常方式从P中删除行。
这里的想法是，您的表格X具有足够小的行，因此即使它有许多行，您也希望不需要对其进行分区。尽管如此，表格上的索引仍将占用相当大的内存块。
如果您需要查询外键P，当然要查询X，因为那里才是实际的外键。
3
我刚刚查看了 MySQL 手册页面上的分区限制，并发现不仅你不能在分区表上创建外键，你还不能有一个指向分区表的外键。这意味着上面的方法并不完全可行。你可以通过删除 Constr_X_P_fk 约束并添加第二个存储过程来解决这个问题，其目的是允许在 P 和 X 中删除行。显然，你应该确保任何删除操作都使用存储过程来执行，而不是使用普通的 DELETE 语句。 - Hammerite
但是在MySQL 8中，使用外键分区表是可能的吗？ - Kundan
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接