对具有外键的 mySQL 表进行分区?

2022-01-20 00:00:00 partitioning foreign-keys mysql

什么是合适的方法来做到这一点，因为 mySQL 显然不喜欢这个.从数据库设计中排除分区或外键对我来说似乎不是一个好主意.我猜想有一个解决方法吗?

What would be an appropriate way to do this, since mySQL obviously doesnt enjoy this. To leave either partitioning or the foreign keys out from the database design would not seem like a good idea to me. I'll guess that there is a workaround for this?

03/24 更新:

http://opendba.blogspot.com/2008/10/mysql-partitioned-tables-with-trigger.html

如何在分区时处理外键

谢谢！

推荐答案

这取决于分区表中的行大小在多大程度上是需要分区的原因.

It depends on the extent to which the size of rows in the partitioned table is the reason for partitions being necessary.

如果行大小很小并且分区的原因是行数，那么我不确定你应该怎么做.

If the row size is small and the reason for partitioning is the sheer number of rows, then I'm not sure what you should do.

如果行大小很大，那么您是否考虑过以下几点:

If the row size is quite big, then have you considered the following:

令 P 为分区表，F 为在可能的外键中引用的表.新建表X:

Let P be the partitioned table and F be the table referenced in the would-be foreign key. Create a new table 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 中自由删除.

and crucially, create a stored procedure for adding rows to table P. Your stored procedure should make certain (use transactions) that whenever a row is added to table P, a corresponding row is added to table X. You must not allow rows to be added to P in the "normal" way! You can only guarantee that referential integrity will be maintained if you keep to using your stored procedure for adding rows. You can freely delete from P in the normal way, though.

这里的想法是您的表 X 有足够小的行，即使它有很多很多行，您也希望不需要对其进行分区.不过，我猜表上的索引会占用相当大的内存.

The idea here is that your table X has sufficiently small rows that you should hopefully not need to partition it, even though it has many many rows. The index on the table will nevertheless take up quite a large chunk of memory, I guess.

如果您需要在外键上查询 P，您当然会查询 X，因为那是外键实际所在的位置.

Should you need to query P on the foreign key, you will of course query X instead, as that is where the foreign key actually is.

相关文章