分区有外键的mySQL表？ [英] Partitioning mySQL tables that has foreign keys?

问题描述

什么是适当的方式来做到这一点，因为mySQL显然不喜欢这个。
要离开数据库设计的分区或外键对我来说似乎不是一个好主意。我估计有这样一个解决方法吗？
$ b $ p更新03/24：
$ b $ http://opendba.blogspot.com/2008/10/mysql -partitioned-tables-with-trigger.html

谢谢！

解决方案

这取决于分区表中行的大小是需要分区的原因的程度。 p>如果行大小很小，分区的原因是行的数量很大，那么我不确定你应该做什么。

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

让 P 为分区表和 F 是表r在可能的外键中引用。创建一个新表 X ：
$ b

  CREATE TABLE`X`（
`P_id` INT UNSIGNED NOT NULL，
  - 我假设一个INT是足够的，但也许
  - 你实际上需要一个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`.``` 
 ON DELETE CASCADE ON UPDATE RESTRICT，
 CONSTRAINT`Constr_X_F_fk` 
 FOREIGN KEY`F_fk`（`F_id`）REFERENCES`F`.``` 
 ON DELETE RESTRICT ON UPDATE RESTRICT 
）ENGINE = INNODB CHARACTER SET ascii COLLATE ascii_general_ci 
  

关键是创建一个存储过程来添加行到表 P 。您的存储过程应该确定（使用事务），只要将一行添加到表 P ，相应的行就会添加到表 X 。您不得以普通方式将行添加到 P 中！你只能保证如果你继续使用你的存储过程添加行，参照完整性将得到保持。你可以按照正常的方式从 P 自由删除。

这里的想法是你的表 X 具有足够小的行数，即使它有许多行，也希望不需要对其进行分区。我猜这个表上的索引会占用相当大的内存空间。

如果你需要查询 P 在外键上，你当然会查询 X ，因为这就是外键的实际情况。

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?

Update 03/24:

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

How to handle foreign key while partitioning

Thanks!

解决方案

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:

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

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.

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.

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.

这篇关于分区有外键的mySQL表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！