SQL语句-如何通过索引提高速度

Question

我有一个脚本，该脚本必须查看超过250万条记录，以查找是否有未读电子邮件的成员。我想知道可以采取什么措施来提高其速度。目前，运行脚本最多可能需要8秒钟的时间：

SELECT TOP(1) MemberMailID
FROM MemberMail
WHERE ToReadFlag = 0
AND ToMemberID = 102
AND ToDeletedFlag = 0
AND FromDeletedFlag = 0
AND OnHold = 0
AND ToArchivedFlag = 0

如何使用索引使其更快？

Answer 1

该索引可能会有所帮助，但是请记住，这里没有免费的午餐（必须维护索引，因此这会影响您的插入/更新/删除工作量）：

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 not
WHERE ToMemberID = 102
AND ToReadFlag = 0
AND ToDeletedFlag = 0
AND FromDeletedFlag = 0
AND OnHold = 0
AND ToArchivedFlag = 0
ORDER 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;

这些差异可能对您的数据和使用模式无关紧要，但是您将能够比我们所能猜测的更容易地测试差异。