聊天应用数据库索引的选择

Question

我用数据库开发了一个小型聊天应用程序。我创建了图像中显示的数据库。

​

我刚开始使用索引，我希望为查询选择适当的索引。

可以在Messages表中使用聚集索引吗？如果可以，哪一列(或哪些列)应该具有聚集索引？还是应该使用非聚集索引？

Update：我使用它获取消息的查询是：

 Select TextContent From Messages where (SenderId='1' and ReciverID = '2') or (SenderId='2' and ReciverID = '1') order by date

SenderID和ReciverID的值只是为了澄清。

Answer 1

您可能应该向messages表中添加一个代理项主键，并创建一个索引：

ALTER TABLE messages ADD COLUMN id BIGINT NOT NULL IDENTITY

ALTER TABLE messages ADD CONSTRAINT pk_messages_id PRIMARY KEY (id)

CREATE INDEX ix_messages_sender_receiver_date (senderId, receiverId, date) ON messages

如果您想检索会话链中的前10条消息，那么重写一下查询可能会有所帮助：

SELECT  m.*
FROM    (
        SELECT  TOP 10
                *
        FROM    (
                SELECT  date, id
                FROM    messages
                WHERE   senderId = 1 AND receiverId = 2
                UNION
                SELECT  date, id
                FROM    messages
                WHERE   senderId = 2 AND receiverId = 1
                ) q
        ORDER BY
                date DESC, id DESC
        ) q
JOIN    messages m
ON      m.id = q.id

这样，Server更有可能合并到会话的两个方向，而不是单独地对它们进行排序。

或者，使用user1、user2和direction代替发送方和接收方，以便user1 < user2 (总是)和direction定义文本是从user1到user2还是相反。

这样，您就可以在user1 = 1 AND user2 = 2上进行简单的过滤，而不必担心OR或联合。

您可以在计算列中这样做，您也可以索引这些列：

ALTER TABLE messages ADD COLUMN user1 AS CASE WHEN senderId < receiverId THEN senderId ELSE receiverId END

ALTER TABLE messages ADD COLUMN user2 AS CASE WHEN senderId > receiverId THEN senderId ELSE receiverId END

ALTER TABLE messages ADD COLUMN direction AS CASE WHEN senderId < receiverId THEN 0 ELSE 1 END

CREATE INDEX ix_messages_user1_user2_date ON messages (user1, user2, date)

然后选择：

SELECT  *
FROM    messages
WHERE   user1 = 1
        AND user2 = 2 -- make sure lower number goes to user1, higher number goes to user2
ORDER BY
        date