为什么在创建索引时使用INCLUDE子句?

小开

最佳答案

如果列不在WHERE/JOIN/GROUP BY/ORDER BY中，而只在SELECT子句中的列列表中，则使用INCLUDE。

INCLUDE子句将数据添加到最低的/leaf级别，而不是添加到索引树中。这使得索引更小，因为它不是树

的一部分

INCLUDE columns不是索引中的键列，所以它们没有排序。这意味着它对于我上面提到的谓词、排序等并不是很有用。但是，如果从键列

开始的几行中有剩余查找，五月是有用的

另一篇MSDN文章，带有工作示例

小开

基本索引列被排序，但包含的列不被排序。这节省了维护索引的资源，同时仍然可以在所包含的列中提供数据来覆盖查询。因此，如果您想涵盖查询，您可以将搜索条件放在索引的已排序列中定位行，但随后“包括”其他具有非搜索数据的未排序列。它确实有助于减少索引维护中的排序和碎片量。

小开

您可以使用INCLUDE将一个或多个列添加到非聚集索引的叶级，如果这样做可以“覆盖”您的查询。

假设您需要查询员工的ID、部门ID和姓。

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5

如果碰巧在(EmployeeID, DepartmentID)上有一个非聚集索引，一旦找到给定部门的员工，现在必须执行“书签查找”以获得实际的完整员工记录，只是为了获得姓氏列。如果你有很多员工的话，这在绩效方面是非常昂贵的。

如果你在你的索引中包含了这个姓氏:

CREATE NONCLUSTERED INDEX NC_EmpDep
ON Employee(EmployeeID, DepartmentID)
INCLUDE (Lastname)

然后，您需要的所有信息都可以在非聚集索引的叶级中获得。只需在非聚类索引中查找给定部门的员工，就可以获得所有必要的信息，并且不再需要在索引中查找每个员工的书签—>可以节省大量时间。

显然，您不能在每个非聚集索引中包含每一列——但是如果您确实有一些查询只需要“覆盖”一到两列(并且经常使用)，那么将这些查询包含到合适的非聚集索引中会非常有帮助。

小开

其中已经很好地解释了原因(包括索引的叶级中的数据)。的原因,你给两个震动,当您运行您的查询,如果没有额外的列包括(SQL 2005新功能)的SQL服务器去聚集索引得到额外的列,这需要更多的时间,并添加更多的负载到SQL Server服务,磁盘,和内存(缓冲区缓存具体)作为新数据页面加载到内存,可能会推动其他经常需要数据缓冲区缓存。

小开

索引定义内嵌的所有列的总大小是有限制的。尽管如此，我从来没有创建过这么宽的索引。对我来说，更大的优势是，您可以用一个包含列的索引覆盖更多查询，因为它们不必以任何特定的顺序定义。把它看作是索引中的索引。一个例子是StoreID(其中StoreID是低选择性的，这意味着每个商店都与许多客户相关联)，然后是客户人口统计数据(LastName, FirstName, DOB): 如果你只是按照这个顺序内联这些列(StoreID, LastName, FirstName, DOB)，你只能有效地搜索你知道StoreID和LastName的客户

另一方面，在StoreID上定义索引并包括LastName、FirstName、DOB列将允许您在本质上进行两次查找——在StoreID上进行索引谓词，然后在任何包含的列上进行查找谓词。这将让你覆盖所有可能的搜索排列，只要它开始与StoreID。

小开

我在已经给出的答案中没有看到的另一个考虑因素是，包含的列可能是不允许作为索引键列的数据类型，例如varchar(max)。

这允许您在覆盖索引中包含这样的列。我最近不得不这样做，以提供一个nHibernate生成的查询，它在SELECT中有很多列，有一个有用的索引。

小开

这个讨论忽略了重要的一点:问题不是“非键列”作为指数-columns还是包括-columns更好。

问题是使用包含机制来包含索引中并不需要?(通常不是where-子句的一部分，但通常包含在select中)。所以你的困境总是:

在id1上使用索引，id2…idN 独自一人或
在id1上使用索引，id2…idN 加上包括 col1, col2…colN

< p >地点: Id1, id2…idN是常用于限制的列，col1, col2…colN是经常选择的列，但通常不用于限制

(将所有这些列作为index-key的一部分的选项总是愚蠢的(除非它们也用于限制)——因为它总是维护更昂贵，因为即使“键”没有改变，索引也必须更新和排序)。

所以选择1还是2?

答:如果你的表很少更新-主要是插入/删除-那么使用include机制来包含一些“热列”(通常用于选择-但不通常用于限制)是相对便宜的，因为插入/删除需要索引更新/排序，因此很少有额外的开销与存储一些额外的列相关，同时已经更新了索引。开销是用于在索引上存储冗余信息的额外内存和CPU。

如果你考虑添加的列作为包含-columns经常更新(没有索引- __abc0 -columns被更新)- 或 -如果它们太多以至于索引变得接近你的表的副本-我建议使用选项1 !此外，如果添加某些include-column(s)结果没有产生性能差异-你可能想要跳过添加它们的想法:)验证它们是有用的!

键中每个相同值的平均行数(id1, id2…)idN)也有一定的重要性。

请注意，如果在限制: 只要索引这样就可以使用中使用了作为索引的包括-column添加的列(基于对索引- __abc3 -columns的限制)，那么SQL Server将根据索引(leaf-node-values)匹配列限制，而不是在表本身周围进行昂贵的方法。

小开

更喜欢INCLUDE而不是键列如果你不需要键的那一列的一个原因是文档。这使得在未来发展索引更加容易。

考虑到你的例子:

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)

如果你的查询是这样的，那么这个索引是最好的:

SELECT col2, col3
FROM MyTable
WHERE col1 = ...

当然你不应该把列放在INCLUDE中，如果你可以从把它们放在关键部分中获得额外的好处。下面这两个查询实际上更倾向于索引键中的col2列。

SELECT col2, col3
FROM MyTable
WHERE col1 = ...
AND col2 = ...

SELECT TOP 1 col2, col3
FROM MyTable
WHERE col1 = ...
ORDER BY col2

让我们假设这是不的情况，并且我们在INCLUDE子句中有col2，因为将它放在索引的树部分没有任何好处。

几年过去了。

你需要调优这个查询:

SELECT TOP 1 col2
FROM MyTable
WHERE col1 = ...
ORDER BY another_col

要优化该查询，可以使用以下索引:

CREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2)

如果你检查你在这个表上已经有哪些索引，你之前的索引可能还在那里:

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)

现在你知道Col2和Col3不是索引树的一部分，因此不能用来缩小读索引范围，也不能用来对行进行排序。将another_column添加到索引的键部分的末尾(在col1之后)是相当安全的。打破任何东西的风险很小:

DROP INDEX idx1 ON MyTable;
CREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2, Col3);

该指数将变得更大，这仍然有一些风险，但与引入新指数相比，扩展现有指数通常更好。

如果你有一个没有INCLUDE的索引，你不知道在Col1之后添加another_col会中断什么查询。

CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)

如果在Col1和Col2之间添加another_col会发生什么?其他查询是否会受到影响?

与键列如果你添加这些列只是为了避免从表中获取它们相比，INCLUDE还有其他“优点”。然而，我认为文档方面是最重要的。

回答你的问题:

在决定是否使用INCLUDE子句创建覆盖索引时，您有什么建议?

如果向索引中添加一列的唯一目的是在不访问表的情况下使该列在索引中可用，则将其放入INCLUDE子句中。

如果将列添加到索引键带来额外的好处(例如对于order by或因为它可以缩小读索引范围)，则将其添加到键中。

你可以在这里阅读更长的讨论:

https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes