SQL 语句 - 如何通过索引提高速度 [英] SQL Statement - How can Improve speed with indexing

问题描述

我有一个脚本，它必须查看超过 250 万条记录才能查找成员是否有未读电子邮件.我想知道可以做些什么来提高它的速度.目前运行脚本最多可能需要 8 秒:

I have a script that has to look throught over 2.5 million records to find if a member that has an unread email. I want to know what can be done to improve its speed. Currently it can take up to 8 seconds to run the script:

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

如何使用索引使其更快?

How could I make it faster using indexes?

推荐答案

这个索引可能会有所帮助，但请记住没有免费的午餐(索引必须维护，因此这会影响您的插入/更新/删除工作负载):

This index will probably be helpful, but keep in mind that there is no free lunch (indexes have to be maintained, so this will affect your insert/update/delete workload):

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:

If you change the values of some of these flags depending on the query, you may experiment with adding those columns to the key of the index instead of the filter, e.g. let's say sometimes you check for OnHold = 0 and sometimes 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.例如:

You may also want to experiment with having MemberMailID in the key instead of the INCLUDE. e.g.:

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;

这些差异可能与您的数据和使用模式无关，但您可以比我们猜测的更容易测试差异.

These differences may not matter for your data and usage patterns, but you'll be able to test differences easier than we'll be able to guess.

这篇关于SQL 语句 - 如何通过索引提高速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！