在覆盖WHERE子句的过滤索引上使用哪些键列

Question

我正在创建一个过滤索引，以便WHERE过滤器包含完整的查询条件。对于这样的索引，键列似乎是不必要的，尽管SQL要求我添加一个。例如，考虑这个表:

CREATE TABLE Invoice
(
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Data VARCHAR(MAX) NOT NULL,
    IsProcessed BIT NOT NULL DEFAULT 0,
    IsInvalidated BIT NOT NULL DEFAULT 0
)

查询表查找要处理的新发票，即:

SELECT *
FROM Invoice
WHERE IsProcessed = 0 AND IsInvalidated = 0

所以，我可以用一个过滤索引来调优这些查询:

CREATE INDEX IX_Invoice_IsProcessed_IsInvalidated
ON Invoice (IsProcessed)
WHERE (IsProcessed = 0 AND IsInvalidated = 0)
GO

我的问题:IX_Invoice_IsProcessed_IsInvalidated的关键列应该是什么?假设键列没有被使用。我的直觉让我选择一个较小的列，使索引结构相对平坦。我应该选择表的主键(Id)吗?一个过滤列，还是两个都是?

Answer 1

因为你在那个表上有一个聚集索引所以你在索引的关键列中放什么并不重要;意味着Id是免费存在的。您唯一能做的就是对索引包含部分中的所有内容进行include处理，以便在索引的叶级实际拥有数据，以排除对表的键查找。或者，如果队列非常大，那么，也许在键部分中使用其他列会很有用。

现在，如果该表没有主键，那么您必须使用include或指定用于连接或其他目的的所有列作为键列。否则，将发生堆上的RID查找，因为在索引的叶级上，您将有对数据页的引用。

Answer 2

这个经过过滤的索引覆盖了表的多少百分比?如果它很小，您可能希望覆盖整个表，以便在不触及表的情况下处理来自索引的"SELECT *"。如果它是表的很大一部分，虽然这不是最优的。然后我建议使用聚集索引或主键。我需要做更多的研究，因为我忘记了哪个是最优的，但如果它们是相同的，你应该设置。

Answer 3

我建议你这样声明

CREATE INDEX IX_Invoice_IsProcessed_IsInvalidated
ON Invoice (Id)
INCLUDE (Data)
WHERE (IsProcessed = 0 AND IsInvalidated = 0)

INCLUDE子句将意味着Data列的值将作为索引的一部分存储。

如果没有INCLUDE子句，那么

的查询计划

SELECT Id, Data
FROM Invoice
WHERE IsProcessed = 0 AND IsInvalidated = 0

将涉及两步过程

• 使用索引查找匹配的主键值列表标准
• 从表中获取与这些主键匹配的数据

另一方面，如果索引包含[Data]列，那么它将适当地覆盖查询，因为不需要使用主键

查找数据

你不会不劳而获

这样做的缺点是，您将为这些记录存储两次varchar(MAX)数据，因此需要将更多的数据写入数据库，并且将使用更多的存储空间，尽管如果您只谈论数据的一小部分，这不是一个大问题。

像往常一样，你花越多的时间和精力小心地把东西放好，就越容易越快地把它们找回来。