P2P消息系统设计

Question

这里的数据库新手，只是好奇，看看我的设计是否可以受益于任何改进。

我的目标是保持尽可能简单:用户可以创建一个帐户，两个帐户可以相互消息。

就这样。

为此，我使用了以下模式：

CREATE TABLE account (
    id         INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    email      VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name  VARCHAR(255) NOT NULL,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id),
    UNIQUE (email)
);

CREATE TABLE message (
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    to_id   INT(10) UNSIGNED NOT NULL,
    from_id INT(10) UNSIGNED NOT NULL,
    body    VARCHAR(500) NOT NULL,
    seen    BOOLEAN NOT NULL DEFAULT 0,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id)
);

使用此模式，我希望能够运行两个查询：

1. 一张快照。我想要消息概述，类似于Facebook是如何做到的；您在每个线程中的最新消息都列出了。
2. 一根线。我想在一个列表中显示两个用户之间的消息线程。

我使用以下查询实现了#1的分页列表：

SELECT message.*, `from`.*, `to`.*
FROM message
LEFT JOIN account AS `to`
    ON `to`.id = message.to_id
LEFT JOIN account AS `from`
    ON `from`.id = message.from_id
WHERE message.id IN(
    SELECT MAX(m.id) 
    FROM message m
    WHERE m.id < 999999999
        AND m.to_id = 1
        AND m.deleted_at IS NULL
    GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id)
)
ORDER BY message.seen, message.id DESC
LIMIT 100

上面对ID为1的用户的查询将显示从每个用户收到的最新消息的快照。

我使用以下查询实现了#2的分页列表：

SELECT message.*, `from`.*, `to`.*
FROM message
LEFT JOIN account AS `to`
    ON `to`.id = message.to_id
LEFT JOIN account AS `from`
    ON `from`.id = message.from_id
WHERE message.id < 999999999
    AND (message.to_id = 1 AND message.from_id = 2) OR (message.to_id = 2 AND message.from_id = 1)
    AND message.deleted_at IS NULL
LIMIT 100

上面的查询将提供ID为1的用户和ID为2的用户之间的所有消息。

有什么想法吗？我希望听到一些明显的改进领域，以及一些可能的解释，所以我可以从中吸取教训。

干杯!

Answer 1

• deleted和seen阻碍了优化
• GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id)看起来很奇怪。难道这还不够吗：GROUP BY m.to_id, m.from_id？
• 因为用户要么是from_id，要么是to_id，一半的信息已经被知道了？
• m.id < 999999999是干什么用的？
• IN ( SELECT ... )的优化效果往往很差。
• 你说的是“分页”，但我没有看到效率低下的OFFSET --而不是更好的“记住我停下来的地方”。
• 使用现实的限制，而不是盲目的VARCHAR(255)。
• 每个表都有3个时间戳。你永远不会用它们中的一些。
• 不要使用SELECT *；相反，请详细说明您真正需要的列。
• 您将需要更多的索引，但让我们拭目以待，看看更多的SELECTs是什么样的。
• LEFT JOIN account假定可能缺少所需的account。如果是，则存在数据完整性问题。因此，切换到JOIN；这可能有助于优化。
• LIMIT 100 --难道在UI页面上放的不太多吗？
• AND (message.to_id = 1 AND message.from_id = 2) OR (message.to_id = 2 AND message.from_id = 1)很难优化。使用两个UNION的SELECTs而不是OR，并添加(暂时) INDEX(from_id, to_id)。原因是优化器几乎没有处理OR的技术；它只是忽略索引并返回到表扫描中。

这样做可能会奏效：

AND (message.to_id, message.from_id) IN ((1, 2), (2, 1))

下面是Changelog条目，它提示这样的内容：

- 2013-12-03 5.7.3里程碑13 -增加或更改功能

优化器现在能够将范围扫描访问方法应用于此表单的查询：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前，要使用范围扫描，必须将查询编写为：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
                      OR ( col_1 = 'c' AND col_2 = 'd' );

为了使优化器使用范围扫描，查询必须满足以下条件：

只能使用IN谓词，不能使用IN。

在in谓词的左侧行构造函数中可能只有列引用。

IN谓词的右侧必须有多个行构造函数。

IN谓词右侧的行构造函数必须只包含运行时常量，这些常量要么是文字，要么是本地列引用，在执行过程中绑定到常量。

用于适用查询的解释输出将从全表或索引扫描更改为范围扫描。通过检查处理程序_朗读_第一、处理程序_朗读_钥匙和处理程序_朗读_下一首状态变量的值，也可以看到更改。