日期:2014-05-17  浏览次数:20635 次

求解 一张用户消息表 如何才能建立良好的索引
本帖最后由 xdq025 于 2013-02-27 10:49:48 编辑
消息表结构
MessageId uniqueidentifier pk  (主键  已有聚集索引)
UserID    int              fk  (用户标识)
Cat       int                  (消息分类)
TaskId    bigint               (消息源标识)
Status    int    
HandleStatus int              (处理结果)
Created   datetime

该表 在未来会有多名用户,且每位用户每天产生零条或多条消息。

现在要对 每条消息 进行处理 并使用 HandleStatus 字段来标记处理结果。
Cat(分类) 及 TaskId(消息源标识) 共同决定 是否同一则消息(该则消息 可以在消息表中 存在多个子消息)
Cat(分类) 与 Status 共同决定 该消息接收时的状态
Created 是 该消息 到达的时间

现在 有一个计划任务 每隔一段时间(几分钟)便查询 消息表中:
 一段时间内,指定用户、消息分类、状态,并排除指定处理处理状态的记录

那么该如何 建立索引 才能高效的查询?

查询 大致如下:

declare
@UserId int,
@Category int,
@IncludeHandleStatus int,
@ExceptHandleStatus int,
@IncludeStatus int,
@ExceptStatus int,
@StartDate datetime,
@EndDate datetime
select nm2.MessageId,nm2.Category,nm2.ReferenceId,nm2.UserId from dbo.NotifyMessage nm2,
(
select nm1.MessageId
from dbo.NotifyMessage nm1
where --先行 过滤 时间、用户、分类、状态
(@StartDate is null or @StartDate<=nm1.CreateDate) And
(@EndDate is null or @EndDate>=nm1.CreateDate) And
(@Category is null or nm1.Category&@Category=@Category) And
(@UserId is null or @UserId=nm1.UserId) And
nm1.HandleStatus&@IncludeHandleStatus=@IncludeHandleStatus And
nm1.[Status]&@IncludeStatus=@IncludeStatus
) tmp1
where nm2.MessageId=tmp1.MessageId and
not exists
(
select 1 from dbo.NotifyMessage nm1
where
nm1.UserId=nm2.UserId and
nm1.Category=nm2.Category and nm1.ReferenceId=nm2.ReferenceId   --以 分类及关联ID 分组
and 
(
(@ExceptHandleStatus<>0 And nm1.HandleStatus&@ExceptHandleStatus<>0) or
(@ExceptStatus<>0 And nm1.[Status]&@ExceptStatus<>0)
)
)

SQL索引

------解决方案--------------------

CREATE NONCLUSTERED INDEX [NotifyMessage_Indexer] ON [dbo].[StoreToolsCommon_NotifyMessage] 
(
    [CreateDate] ASC,
    [UserID] ASC,
    [Category] ASC,
    [ReferenceId] ASC,
    [Status] ASC,
    [HandleStatus] ASC
)

------解决方案--------------------
先要了解你的数据,然后知道sql的写法,才考虑索引,不然创建的索引常常会无效、多余这些