如何选择聊天应用程序数据库的索引 [英] How to choose indexes for chat application database

问题描述

我正在使用数据库的小型聊天应用程序。我创建图像中显示的数据库。

我新建索引，我想为查询选择适当的索引。

我可以在Messages表中使用聚簇索引吗？如果可以，哪个列（或列）应该具有聚簇索引？或者我应该使用非聚集索引？

更新：我用户要获取消息的查询是：

 从（SenderId ='1'和ReciverID ='2'）或（SenderId ='2'和ReciverID ='1'）的消息中选择TextContent按日期
  

SenderID和ReciverID的值仅用于澄清。

解决方案

您可能应该在消息表中添加一个代理主键，并创建一个索引：

  ALTER TABLE消息ADD COLUMN id BIGINT NOT NULL IDENTITY 
 
 ALTER TABLE消息ADD CONSTRAINT pk_messages_id PRIMARY KEY（id）
 
 CREATE INDEX ix_messages_sender_receiver_date（senderId ，receiverId，date）ON消息
  

如果要检索，只要前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 
日期DESC，id DESC 
）q 
 JOIN消息m 
 ON m.id = q.id 
  

SQL Server更有可能合并加入对话的两个方向，而不是单独排序。

或者，而不是发件人和接收方，使用 user1 ， user2 和 direction ，这样 user1< ; user2 （always）和 direction 定义文本是否从 user1 到 user2 或者反之亦然。

这样，你可以简单地在 user1 = 1 AND user2 = 2 ，而不用担心 OR 或工会。

你可以做在您可以索引的计算列中：

  ALTER TABLE消息ADD COLUMN user1 AS CASE WHEN senderId< receiverId THEN senderId ELSE receiverId END 
 
 ALTER TABLE消息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消息（user1，user2，date）
  

然后选择：

  SELECT * 
 FROM messages 
 WHERE user1 = 1 
 AND user2 = 2  - 确保较低的数字到user1，更高的数字到user2 
 ORDER BY 
日期
  

I work on a small chat application with database. I create the database shown in the image.

I new to indexes and I want to choose the appropriate indexes for the query.

Can I use clustered index in the Messages table ? and if I can, Which column (or columns) should have clustered index ? or should I use non-clustered index ?

Update: The query that I user to get messages is :

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

The values for SenderID and ReciverID is just for clarification.

解决方案

You probably should add a surrogate primary key to your messages table and create an index:

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

If you want to retrieve, say, just top 10 last messages from the conversation chain, it might help to rewrite your query a little:

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

This way, SQL Server is more likely to merge join the two directions of the conversation rather than sorting them separately.

Alternatively, instead of sender and receiver, use user1, user2 and direction, so that user1 < user2 (always) and direction defines whether the text goes from user1 to user2 or vise versa.

This way, you can always filter simply on user1 = 1 AND user2 = 2, without having to worry about OR or unions.

You can do that in computed columns which you can also index:

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)

then select:

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

这篇关于如何选择聊天应用程序数据库的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！