php - 将私有(private)消息 'body' 字段存储在单独的表中以提高速度

Question

我为我的用户创建了一个私有(private)消息传递系统，该系统是在带有 mysql 后端的 php 中创建的。系统会删除旧消息，但通常会保留超过 500,000 条消息。目前所有数据都包含在一张表中:

message_table
    message_id (int 11)
    message_from_id (int 11)
    message_to_id (int 11)
    message_timestamp (int 11)
    message_subject (varchar 50)
    message_text (text)

大部分消息都很短，所以我正在考虑将系统更改为:

message_table
    message_id (int 11)
    message_from_id (int 11)
    message_to_id (int 11)
    message_timestamp (int 11)
    message_subject (varchar 50)
    message_short_body (varchar 50)
    message_text_id (int 11)

text_table
    text_id (int 11)
    text_body (text)

然后，如果输入了一条短消息，它将在'message_short_body' 下输入，如果更长，将被添加到'text_table' 并将'text_id' 存储为'message_text_id'。当消息被访问时，我会有类似的东西:

SELECT * FROM message_table LEFT JOIN text_table ON text_table.text_id = message_table.message_text_id IF message_table.message_text_id != 0 WHERE message_table.message_to_id = $user_id

我添加了“IF message_table.message_text_id != 0”，但不知道这样的事情是否可行。

作为一般规则，是否可以判断这是否会减小数据库的大小/加快查询速度？

Answer 1

I added "IF message_table.message_text_id != 0" and don't know if something like that is possible.

除非您的 text_table 中确实有包含 text_id = 0 的行，否则无需执行此操作。只需省略 IF 并使用以下查询:

SELECT IFNULL(text_table.text_body, message_table.message_short_body) AS body,
       …
FROM message_table
LEFT JOIN text_table ON text_table.text_id = message_table.message_text_id
WHERE message_table.message_to_id = $user_id

就性能而言，可能如果您将条件添加到连接条件中，引擎可以更有效地优化事情:

SELECT IFNULL(text_table.text_body, message_table.message_short_body) AS body,
       …
FROM message_table
LEFT JOIN text_table ON text_table.text_id = message_table.message_text_id
                    AND message_table.message_text_id != 0
WHERE message_table.message_to_id = $user_id

您也可以尝试使用子查询的方法:

SELECT IF(message_text_id = 0, message_short_body, (
  SELECT text_table.message_short_body
  FROM text_table
  WHERE text_table.text_id = message_table.message_text_id)) AS body,
       …
FROM message_table
WHERE message_table.message_to_id = $user_id

如果不需要，这样做的好处是不在 text_table 中执行搜索，但缺点是对带有长消息的每个案例执行单独的查询。我希望上述查询更好，但我不确定。

As a general rule is it possible to tell if this would reduce the size of the database / speed up queries ?

您必须进行基准测试，因为这取决于用例。如果您的大部分查询从文本以外的字段中检索数据，那么较小的表将使这些查询更快，从而提高性能。另一方面，如果您通常需要正文和消息的其余部分，那么您最终可能会获得更差的性能。

您还应该使用基准来区分上述不同的备选方案。

就数据库大小而言，您可能会看到增加:文本数据的存储要求大致相同，但额外表的索引将花费您。

我想如果这是我的架构，我会删除 message_text_id 并让 text_table 的主键与 message_table 的主键匹配>。 IE。每个键要么只出现在消息表中，要么同时出现在两个表中，并且具有相同键的行属于一起。在这些情况下，可以通过将 message_table.message_short_body 设置为 NULL 来对消息是否在另一个表中进行编码。