问题描述
我正在使用数据库的小型聊天应用程序。我创建图像中显示的数据库。我新建索引,我想为查询选择适当的索引。
我可以在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
这篇关于如何选择聊天应用程序数据库的索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!