包含的列在Seek运算符中使用 [英] Included columns are used in Seek operator

问题描述

考虑一个具有两个非聚集索引的表，并查询:

Consider a table with two non clustered index, and query:

1 INDEX_1 on table (column1, column2, column3)
2 INDEX_2 on table (column1) INCLUDED (column2, column3)

    SELECT column3
    FROM   table
    WHERE  column1 = 100 columnn2 =  100

由于某些原因，SQL Server使用INDEX_2. 两个索引的执行计划都相同(除了Seek运算符中的对象) 另外，两者的逻辑读取时间相同. 如果索引未按column2

For some reason, SQL server uses INDEX_2. Execution plan for boths indexes are the same(besides object in Seek operator) Also, the logical reads times are the same for boths. How is it possible to perform seek operator with such conditions on INDEX_2, if index is not sorted by column2

推荐答案

如果索引未按column2排序，如何在INDEX_2上以这种条件执行搜索运算符

How is it possible to perform seek operator with such conditions on INDEX_2, if index is not sorted by column2

索引搜索"会搜索索引中的某个位置，但随后可能会沿着叶级页面从该点开始进行扫描，这些页面将按照索引排序顺序存储在一个双向链接的列表中.

An Index Seek seeks to a location in an index, but then may scan from that point along the leaf-level pages, which are stored in a doubly-linked list following the index sort order.

因此，它实际上更像是搜索+扫描".从聚簇索引中进行选择时，您会看到相同的计划，因为包含列的索引属于微型聚簇索引.

So it's really more of a Seek+Scan. You'll see the same plan when selecting from a clustered index, because an index with included columns is kind of a miniature clustered index.

例如在Adventureworks中

eg in Adventureworks

SELECT SalesOrderID, *
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43892
  AND ProductID = 758

将为您提供一个可以读取多行的索引查找:

will give you an Index Seek that reads multiple rows:

寻找包含该SalesOrderID行的第一页，然后进行扫描，直到找到具有该ProductID的所有行.当操作员找到具有不同SalesOrderID的页面时，就完成了.

Seeking to the first page that contains a row for that SalesOrderID, and then scanning until it locates all the rows with that ProductID. When the operator finds a page with a different SalesOrderID, it's done.

但是，如果(column1，column2，column3)是聚簇索引键，则查询将是单行Seek.所有非唯一非聚集索引都将所有聚集索引列添加为索引键列，并在物理上存储为唯一索引.

But If (column1, column2, column3) is the clustered index key, the query would be a single-row Seek. All non-unique non-clustered indexes will have all the clustered index columns added as index key columns, and will physically be stored as a unique index.

这篇关于包含的列在Seek运算符中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！