sql - 向用户存储和显示用户收件箱的最有效方式

Question

我正在尝试为我目前正在使用的应用程序实现一个消息传递系统。消息存储在表中:

CREATE TABLE `message` (
  `MessageID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `Thread` BIGINT(20) NOT NULL,
  `From` BIGINT(20) NOT NULL,
  `To` BIGINT(20) NOT NULL,
  `DateTime` DATETIME NOT NULL DEFAULT '2012-01-01 00:00:00',
  `Content` TEXT NOT NULL,
  PRIMARY KEY (`MessageID`),
  CONSTRAINT `FK_messageTo` FOREIGN KEY (`To`) REFERENCES `team` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_messageFrom` FOREIGN KEY (`From`) REFERENCES `team` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_thread` FOREIGN KEY (`Thread`) REFERENCES `thread` (`ThreadID`) ON DELETE CASCADE ON UPDATE CASCADE
);

Thread 是另一个存储 session 线程的表，基本上是成对的用户。

当用户查看他们与某人的对话时，他们应该只显示最近的消息，比如 10 条消息。更多消息按需显示(点击按钮，无论如何)。我不确定在这种情况下汇集数据库的最有效方法是什么。

• 每次都把整条线都拿出来剪掉，显然达不到目的。

• 按照以下方式进行查询:

  SELECT *
    FROM message
   WHERE (to = {party1} AND `from` = {party2})
      OR (to = {party2} AND `from` = {party1})
   ORDER BY messageID DESC
   LIMIT {start}, {length}
  

其中像 {name} 这样的表达式是变量。我不确定 LIMIT 在这里是如何工作的，以及是否会比我的第一种方法更快。

• 第三个选项是添加另一个在线程内递增的字段(索引)(因为每当在系统中的任何位置添加新消息时，消息表中的主键都会递增)。

Answer 1

看到您的要求后，我想到的一个建议是:比方说，如果您必须一次显示 10 个对话，并且如果用户想要查看更多内容，则会为他提供一个按钮以查看接下来的 10 个对话。

所以你可以做的是，首先选择前 10 个对话并将最后一个对话的 ID 号存储在前端应用程序的某个变量中。

SELECT *
  FROM message
 WHERE (to = {party1} AND from = {party2})
    OR (to = {party2} AND from = {party1})
   AND messageid BETWEEN 1 AND 10
 ORDER BY messageID 

当用户点击一个按钮查看接下来的 10 个对话时，然后再次向 sql 引擎发送一个带有 rang lastid+10 的查询。即:

SELECT *
  FROM message
 WHERE (to = {party1} AND from = {party2})
    OR (to = {party2} AND from = {party1})
   AND messageid BETWEEN 11 AND 20
 ORDER BY messageID 

还要确保表上有正确的索引。