该索引可能会有所帮助，但是请记住，这里没有免费的午餐（必须维护索引，因此这会影响您的插入/更新/删除工作量）：
CREATE NonCLUSTERED INDEX unread_emails ON dbo.MemberMail(ToMemberID) INCLUDE (MemberMailID) WHERe ToReadFlag = 0 AND ToDeletedFlag = 0 AND FromDeletedFlag = 0 AND onHold = 0 AND ToArchivedFlag = 0;
现在，您的查询可以说：
SELECT TOP (1) MemberMailID FROM dbo.MemberMail -- dbo prefix WITH (INDEX (unread_emails)) -- in case you need to force, though you should notWHERe ToMemberID = 102AND ToReadFlag = 0AND ToDeletedFlag = 0AND FromDeletedFlag = 0AND onHold = 0AND ToArchivedFlag = 0ORDER BY ToMemberID; -- ORDER BY is important!
如果您根据查询更改其中一些标志的值，则可以尝试将这些列添加到索引键而不是过滤器中，例如，假设有时您要检查
onHold = 0，有时
onHold =1：
CREATE NonCLUSTERED INDEX unread_emails ON dbo.MemberMail(ToMemberID, OnHold) INCLUDE (MemberMailID) WHERe ToReadFlag = 0 AND ToDeletedFlag = 0 AND FromDeletedFlag = 0 AND ToArchivedFlag = 0;
您可能还想尝试使用
MemberMailID键而不是
INCLUDE。例如：
CREATE NonCLUSTERED INDEX unread_emails ON dbo.MemberMail(ToMemberID, MemberMailID) WHERe ToReadFlag = 0 AND ToDeletedFlag = 0 AND FromDeletedFlag = 0 AND onHold = 0 AND ToArchivedFlag = 0;
这些差异可能对您的数据和使用模式无关紧要，但是您将能够比我们所能猜测的更容易地测试差异。
