主页 / dba / 问题 / 276091
Accepted
Matěj Štágl
Asked: 2020-09-26 07:39:54 +0800 CST

查询优化 - 对话所有频道中的未读消息

  • 772

我有一个缓慢的查询，我正在尝试调整。DB（mssql）中的表：

  • Users, ChatGroups, ChatMessages, ChatGroupChannels,ChatMessageSeenFast

查询：

WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @userId)
SELECT (
    select count(*)
    from ChatMessages m
    left join ChatGroupChannels ch on ch.groupId = s.recipientId and ch.id = m.channelId
    left join ChatMessageSeenFast f on f.userId = @userId and f.groupId = s.recipientId and f.channelId = ISNULL(ch.Id, 0)
    where m.recipientId = s.recipientId and m.channelId = ISNULL(ch.Id, 0) and m.id > ISNULL(f.lastSeenMsgId, 0)

) as unseenCount, task.flag as taskFlag, task.note as taskNote, s.id as id, s.seen as seenByReader, MAX(s.date) as seenByReaderDate, s.textFlags as messageTextFlags, MAX(s.date) as messageSentDate, s.text as messageText, s.userId as messageSenderId, s.type as messageType, s.recipientId as messageRecipientId, s.recipientType as messageRecipientType, g.name as groupName, g.imageMip64x64 as groupImageMip64,  g.id as groupId, g.groupType as groupType, g.groupFlag as groupFlag, u.profilePictureMip64 as mbyOtherUserProfilePicMip64, u.name as mbyOtherUserName, uu.name as senderName
FROM messages_ranked s 
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
join Users uu on uu.id = s.userId
left join ChatGroupTasks task on task.groupId = g.Id and task.userId = @userId
WHERE seqnum = 1 and (u.id <> @userId or g.groupType > 0)
group by task.flag, task.note, s.id, s.seen, s.textFlags, s.text, s.userId, s.type, s.recipientId, s.recipientType, g.name, g.imageMip64x64, g.id, g.groupType, g.groupFlag, u.profilePictureMip64, u.name, uu.name
ORDER BY MAX(s.date) DESC 
OFFSET @skip ROWS 
FETCH NEXT @take ROWS ONLY

打破它：

我的第一步是获得对ChatGroup - LastMessageInThatGroup。我正在使用 CTE 来做到这一点：

WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @userId)
select g.id as ChatGroupId, s.id as LastMessageInThisGroupId
FROM messages_ranked s 
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
WHERE seqnum = 1 and (u.id <> @userId or g.groupType > 0)
group by s.id, g.id
ORDER BY MAX(s.date) DESC

在此处输入图像描述

这意味着给定用户是成员的任何组中的最后一条消息的 ID 为 86823，相应组的 ID 为 6901。

解释一下(u.id <> @userId or g.groupType > 0)- 聊天组有以下 groupTypes：

  • 1 对 1 对话 = 0。在这种情况下group.coreMemberId1是我们的用户或其他成员。group.coreMemberId2是他们中的另一个。
  • 真正的群组对话 = 1+ - 这里两者group.coreMemberId1都group.coreMemberId2等于创建此群组的用户的 ID。

我的第二步是加入其他表以获取一些补充信息：

join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
join Users uu on uu.id = s.userId
left join ChatGroupTasks task on task.groupId = g.Id and task.userId = @userId

到目前为止，一切都很好。我的问题在于最后一步 - 获取我们的用户在所有对话渠道中的未读消息计数：

SELECT (
    select count(*)
    from ChatMessages m
    left join ChatGroupChannels ch on ch.groupId = s.recipientId and ch.id = m.channelId
    left join ChatMessageSeenFast f on f.userId = @userId and f.groupId = s.recipientId and f.channelId = ISNULL(ch.Id, 0)
    where m.recipientId = s.recipientId and m.channelId = ISNULL(ch.Id, 0) and m.id > ISNULL(f.lastSeenMsgId, 0)

) as unseenCount

一个组可以有任意数量的通道。为了节省空间，在上面的查询中为每个聊天组“存在”一个“虚拟”频道（称为 ID = 0）。由于大多数用户不使用频道，因此默认情况下为每个组创建一个频道对我来说是一种资源浪费。因此，默认情况下，所有消息都写入此特殊通道。那应该解释ISNULL(ch.Id, 0)

ChatMessageSeenFast持有以下信息：

groupId - userId - channelId (can be null) - lastMessageSeenId - lastUpdateDatetime

这一步背后的逻辑是：

为了计算所有未见消息的数量，我将计算每个频道的所有未见消息的数量，然后进行总结。

当我s.recipientId从子查询中使用它时，它会大大降低整个事情的速度。将突出显示的分支添加到执行计划中： 在此处输入图像描述

在频道存在之前，我可以通过简单地加入ChatMessageSeenFast分配给对话中的用户来获得未见消息的数量，但现在这将只为一个（选定的）频道产生未见消息。

我已经创建了建议我的 mssms 的索引，它加快了执行速度，但我仍然对处理查询所需的内存和时间不满意。我怎样才能加快速度？

为了将整个问题放在一个上下文中，我使用这些数据在我的网络应用程序中创建对话列表：

在此处输入图像描述

sql-server query-performance

1 个回答

  1. Best Answer

    让我们试试这个：

    WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @myId)
SELECT task.flag as taskFlag, task.note as taskNote, s.id as id, s.seen as seenByReader, MAX(s.date) as seenByReaderDate, s.textFlags as messageTextFlags, MAX(s.date) as messageSentDate, s.text as messageText, s.userId as messageSenderId, s.type as messageType, s.recipientId as messageRecipientId, s.recipientType as messageRecipientType, g.name as groupName, g.imageMip64x64 as groupImageMip64,  g.id as groupId, g.groupType as groupType, g.groupFlag as groupFlag, u.profilePictureMip64 as mbyOtherUserProfilePicMip64, u.name as mbyOtherUserName, uu.name as senderName
INTO #t
FROM messages_ranked s 
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
join Users uu on uu.id = s.userId
left join ChatGroupTasks task on task.groupId = g.Id and task.userId = @myId
WHERE seqnum = 1 and (@myId <> u.id or g.groupType > 0) 
group by task.flag, task.note, s.id, s.seen, s.textFlags, s.text, s.userId, s.type, s.recipientId, s.recipientType, g.name, g.imageMip64x64, g.id, g.groupType, g.groupFlag, u.profilePictureMip64, u.name, uu.name
ORDER BY MAX(s.date) DESC 
OFFSET @skip ROWS 
FETCH NEXT @take ROWS ONLY

SELECT 
(

select sum(m.unseenCount)
from (
 select  (
 select count(*)
 from ChatMessages m
 left join ChatGroupChannels ch on ch.groupId = s.recipientId and ch.id = m.channelId
 left join ChatMessageSeenFast f on f.userId = @readerId and f.groupId = s.recipientId and f.channelId = ISNULL(ch.Id, 0)
 where m.recipientId = s.recipientId and m.channelId = ISNULL(ch.Id, 0) and m.id > ISNULL(f.lastSeenMsgId, 0)
 ) as unseenCount  
) m

) as unseenCount
,s.*
from #t s
    • 4

相关问题