MySQL通过外键分区

Question

我在 MySQL 中有一个聊天数据库。

“用户”表

用户 ID(PK)、用户名

“聊天”表

chat_id(PK)、user1_id(FK)、user2_id(FK)

“消息”表

message_id(PK)、chat_id(FK)、user_from(FK)、message_text、message_date

由于我预计消息表中有数百万条记录，因此我考虑对其进行分区。这是一个好方法吗？这里必须使用什么类型的分区？我想如果我按 chat_id 分区，那么对于两个用户之间的每次聊天我都会得到一个分区。在实践中，结果是，每次都会检索分区中的所有记录，因为它们都属于同一个聊天。但是，这意味着如果我有 100 万条聊天记录，我就有 100 万个分区。但是，由于chat_id是外键，MySQL不允许通过chat_id进行分区。

Answer 1

关于 PARTITIONing 需要理解的主要一点是，它本身并不提供任何性能优势。

有一些异常(exception)。唯一可能适用的是:

如果您打算删除“旧”聊天记录，例如 30 天后，则可以使用DROP PARTITION提高DELETE的效率。

更多讨论:http://mysql.rjweb.org/doc.php/partitionmaint

回到您的具体问题:

“两个用户之间的每次聊天我都会得到一个分区”——不!分区不能很好地扩展。一般来说，数据库引擎被设计为在执行 DML 操作时高效:选择/插入/删除/更新，但以牺牲 DDL 操作为代价:创建/更改/删除。

“它们都属于同一个聊天”——这听起来像是试图帮助“缓存”。其中大部分可以通过仔细选择索引来实现。给定聊天的消息可以通过此技术在messages表中“聚集”在一起:

CREATE TABLE Messages (
    message_id BIGINT NOT NULL AUTO_INCREMENT,
    chat_id INT UNSIGNED NOT NULL,
    ...
    PRIMARY KEY(chat_id, message_id),  -- to cluster by chat
    INDEX(message_id)   -- to keep auto_increment happy
) ENGINE=InnoDB;

在几乎所有情况下，分区的“目的”都可以通过合适的索引方案来模拟。 (推论:切换到分区或从分区切换时必须重新设计索引。)

“100万个分区”——8K是极限。并且，每个分区至少有一个磁盘文件；操作系统不喜欢拥有一百万个文件，尤其是在单个目录中。即使是 8K 也正在拉伸(stretch)事物。