mysql一对一消息优化表查询

Question

我有这个架构可以使用:

-- store messages between users
CREATE TABLE chat_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sender_id BIGINT UNSIGNED NOT NULL,
    recipient_id BIGINT UNSIGNED NOT NULL,
    message_body TEXT,
    read_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT FK_messages_1 FOREIGN KEY (sender_id) REFERENCES profiles (id),
    CONSTRAINT FK_messages_2 FOREIGN KEY (recipient_id) REFERENCES profiles (id)
) ENGINE = INNODB;

我需要对其运行查询以获取用户的唯一事件聊天列表，我有以下查询:

SELECT * FROM chat_messages WHERE recipient_id = :user_id GROUP BY recipient_id ORDER BY created_at DESC

但是，这会返回分组行:

1. 返回的行包含 message_body，但它不是最新消息(按照需要按先前排序的数据集对其进行分组的思路进行思考)

2. 此外，如果用户向另一个用户发送消息但尚未收到回复，则不会将其包含在列表中(在这种情况下，还应考虑 sender_id)

有什么建议可以通过以性能为中心的查询来实现这一切吗？谢谢:)

Answer 1

您可以使用 notists 查询执行您想要的操作:

SELECT cm.*
FROM chat_messages cm
WHERE recipient_id = :user_id AND
      NOT EXISTS (SELECT 1
                  FROM chat_messages cm2
                  WHERE cm2.recipient_id = cm.recipient_id AND
                        cm2.created_at > cm.created_at
                 )
ORDER BY created_at DESC;

为了提高性能，您应该在 chat_messages(recipient_id,created_at) 上建立索引。