SQL Server 计划：索引扫描/索引查找之间的区别

SQL Server Plans : difference between Index Scan / Index Seek

提问by cindi

In a SQL Server Execution plan what is the difference between an Index Scan and an Index Seek

在 SQL Server 执行计划中，索引扫描和索引查找有什么区别

I'm on SQL Server 2005.

我在 SQL Server 2005 上。

回答by Justin

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.

索引扫描是 SQL Server 读取整个索引以寻找匹配项的地方 - 这花费的时间与索引的大小成正比。

An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records (see http://mattfleming.com/node/192for an idea on how this works) - time taken is only proportional to the number of matching records.

索引查找是 SQL Server 使用索引的 b 树结构直接查找匹配记录的地方（有关如何工作的想法，请参见http://mattfleming.com/node/192） - 所用时间仅与匹配记录的数量。

• In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
• Note however that in certain situations an index scan can be faster than an index seek (sometimes significantlyfaster) - usually when the table is very small, or when a large percentage of the records match the predicate.

• 一般来说，索引搜索比索引扫描更可取（当匹配记录的数量远低于记录总数时），因为执行索引搜索所花费的时间是恒定的，无论您的记录总数是多少桌子。
• 但是请注意，在某些情况下，索引扫描可能比索引查找更快（有时要快得多）——通常是当表非常小，或者当很大比例的记录与谓词匹配时。

回答by John Sansom

The basic rule to follow is Scans are bad, Seeks are good.

要遵循的基本规则是扫描不好，搜索是好的。

Index Scan

索引扫描

When SQL Server does a scan it loads the object which it wants to read from disk into memory, then reads through that object from top to bottom looking for the records that it needs.

当 SQL Server 进行扫描时，它会将它想要从磁盘读取的对象加载到内存中，然后从上到下读取该对象以查找它需要的记录。

Index Seek

索引搜索

When SQL Server does a seek it knows where in the index that the data is going to be, so it loads up the index from disk, goes directly to the part of the index that it needs and reads to where the data that it needs ends. This is obviously a much more efficient operation than a scan, as SQL already knows where the data it is looking for is located.

当 SQL Server 执行查找时，它知道数据将在索引中的哪个位置，因此它从磁盘加载索引，直接转到它需要的索引部分并读取到它需要的数据结束的位置. 这显然是比扫描更有效的操作，因为 SQL 已经知道它正在寻找的数据所在的位置。

---

---

How can I modify an Execution Plan to use a Seek instead of a Scan?

如何修改执行计划以使用 Seek 而不是 Scan？

When SQL Server is looking for your data probably one of the largest things which will make SQL Server switch from a seek to a scan is when some of the columns are you looking for are not included in the index you want it to use. Most often this will have SQL Server fall back to doing a clustered index scan, since the Clustered index contains all the columns in the table. This is one of the biggest reasons (in my opinion at least) that we now have the ability to INCLUDE columns in an index, without adding those columns to the indexed columns of the index. By including the additional columns in the index we increase the size of the index, but we allow SQL Server to read the index, without having togo back to the clustered index, or to the table it self to get these values.

当 SQL Server 正在查找您的数据时，可能使 SQL Server 从查找切换到扫描的最大事情之一是您要查找的某些列未包含在您希望它使用的索引中。大多数情况下，这会让 SQL Server 回退到执行聚集索引扫描，因为聚集索引包含表中的所有列。这是我们现在能够在索引中包含列的最大原因之一（至少在我看来），而无需将这些列添加到索引的索引列中。通过在索引中包含额外的列，我们增加了索引的大小，但我们允许 SQL Server 读取索引，而不必返回聚集索引或它自己的表来获取这些值。

References

参考

For information regarding the specifics of each of these operators within a SQL Server Execution plan see....

有关 SQL Server 执行计划中每个运算符的详细信息，请参阅....

• Clustered Index Scan - Books Online

• Clustered Index Seek - Books
  Online

• 聚集索引扫描 - 联机丛书

• 聚集索引查找 -
  在线书籍

回答by Kundan Dasange

Scans vs. Seeks

扫描与搜索

---

---

Index Scan:

索引扫描：

Since a scan touches every row in the table, whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

由于扫描涉及表中的每一行，无论是否符合条件，成本与表中的总行数成正比。因此，如果表很小或者大多数行符合谓词，则扫描是一种有效的策略。

Index Seek:

索引搜索：

Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

由于查找仅涉及符合条件的行和包含这些符合条件的行的页面，因此成本与符合条件的行和页面的数量成正比，而不是与表中的总行数成正比。

Index Scanis nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then the optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

索引扫描只不过是从第一页到最后一页扫描数据页。如果表上有索引，并且查询涉及大量数据，这意味着查询正在检索超过 50% 或 90% 的数据，然后优化器将只扫描所有数据页以检索数据行。如果没有索引，那么您可能会在执行计划中看到表扫描（Index Scan）。

Index seeksare generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

对于高度选择性的查询，索引查找通常是首选。这意味着查询只是请求较少数量的行，或者只是检索表中的其他 10 行（有些文档说 15%）。

In general query optimizer tries to use an Index Seek which means that the optimizer has found a useful index to retrieve record set. But if it is not able to do so either because there is no index or no useful indexes on the table, then SQL Server has to scan all the records that satisfy the query condition.

一般来说，查询优化器尝试使用索引查找，这意味着优化器已经找到了一个有用的索引来检索记录集。但是如果由于表上没有索引或没有有用的索引而无法这样做，则 SQL Server 必须扫描所有满足查询条件的记录。

Difference between a scan and a seek?

扫描和查找的区别？

A scan returns the entire table or index. A seek efficiently returns rows from one or more ranges of an index based on a predicate. For example, consider the following query:

扫描返回整个表或索引。查找有效地根据谓词从一个或多个索引范围返回行。例如，考虑以下查询：

select OrderDate from Orders where OrderKey = 2

Scan

扫描

With a scan, we read each row in the orders table, evaluate the predicate “where OrderKey = 2” and, if the predicate is true (i.e., if the row qualifies), return the row. In this case, we refer to the predicate as a “residual” predicate. To maximize performance, whenever possible we evaluate the residual predicate in the scan. However, if the predicate is too expensive, we may evaluate it in a separate filter iterator. The residual predicate appears in text showplan with the WHERE keyword or in XML showplan with the tag.

通过扫描，我们读取订单表中的每一行，评估谓词“where OrderKey = 2”，如果谓词为真（即，如果该行符合条件），则返回该行。在这种情况下，我们将谓词称为“剩余”谓词。为了最大限度地提高性能，我们尽可能评估扫描中的残差谓词。但是，如果谓词太昂贵，我们可能会在单独的过滤器迭代器中对其进行评估。残差谓词出现在带有 WHERE 关键字的文本 showplan 中或带有标记的 XML showplan 中。

Here is the text showplan (slightly edited for brevity) for this query using a scan:

这是使用扫描的此查询的文本显示计划（为简洁起见略作编辑）：

|–Table Scan(OBJECT:([ORDERS]), WHERE:([ORDERKEY]=(2)))

|–表扫描(OBJECT:([ORDERS]), WHERE:([ORDERKEY]=(2)))

The following figure illustrates the scan:

下图说明了扫描：

Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate. However, if the table is large and if most of the rows do not qualify, we touch many more pages and rows and perform many more I/Os than is necessary.

由于无论是否符合条件，扫描都会触及表中的每一行，因此成本与表中的总行数成正比。因此，如果表很小或者大多数行符合谓词，则扫描是一种有效的策略。但是，如果表很大并且大多数行不符合条件，我们会接触更多的页面和行并执行比必要更多的 I/O。

Seek

寻找

Going back to the example, if we have an index on OrderKey, a seek may be a better plan. With a seek, we use the index to navigate directly to those rows that satisfy the predicate. In this case, we refer to the predicate as a “seek” predicate. In most cases, we do not need to re-evaluate the seek predicate as a residual predicate; the index ensures that the seek only returns rows that qualify. The seek predicate appears in the text showplan with the SEEK keyword or in XML showplan with the tag.

回到这个例子，如果我们在 OrderKey 上有一个索引，寻找可能是一个更好的计划。通过查找，我们使用索引直接导航到满足谓词的那些行。在这种情况下，我们将谓词称为“寻求”谓词。大多数情况下，我们不需要将seek谓词重新评估为残差谓词；索引确保搜索只返回符合条件的行。搜索谓词出现在带有 SEEK 关键字的文本 showplan 中或带有标记的 XML showplan 中。

Here is the text showplan for the same query using a seek:

这是使用搜索的同一查询的文本显示计划：

|–Index Seek(OBJECT:([ORDERS].[OKEY_IDX]), SEEK:([ORDERKEY]=(2)) ORDERED FORWARD)

|–索引查找(OBJECT:([ORDERS].[OKEY_IDX]), SEEK:([ORDERKEY]=(2)) ORDERED FORWARD)

The following figure illustrates the seek:

下图说明了搜索：

Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table. Thus, a seek is generally a more efficient strategy if we have a highly selective seek predicate; that is, if we have a seek predicate that eliminates a large fraction of the table.

由于查找仅涉及符合条件的行和包含这些符合条件的行的页面，因此成本与符合条件的行和页面的数量成正比，而不是与表中的总行数成正比。因此，如果我们有一个高度选择性的搜索谓词，搜索通常是一种更有效的策略；也就是说，如果我们有一个搜索谓词可以消除表的大部分内容。

A note about showplan

关于展示计划的说明

In showplan, we distinguish between scans and seeks as well as between scans on heaps (an object with no index), clustered indexes, and non-clustered indexes. The following table shows all of the valid combinations:

在 showplan 中，我们区分了扫描和查找以及对堆（没有索引的对象）、聚集索引和非聚集索引的扫描。下表显示了所有有效组合：

https://blogs.msdn.microsoft.com/craigfr/tag/scans-and-seeks/

https://blogs.msdn.microsoft.com/craigfr/tag/scans-and-seeks/

https://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

https://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

回答by Bigeyes

Short answer:

简短的回答：

• Index scan: Touch all rows but certain columns.

• Index seek: Touch certain rows and certain columns.

• 索引扫描：触摸除某些列之外的所有行。

• 索引查找：触摸某些行和某些列。

回答by AdaTheDev

With an Index Scan, all rows in the index are being scanned to find a matching row. This can be efficient for small tables. With an Index Seek, it only needs to touch the rows that actually meet the criteria and so is generally more performant

使用索引扫描，索引中的所有行都会被扫描以找到匹配的行。这对于小表来说是有效的。使用索引查找，它只需要接触实际满足条件的行，因此通常性能更高

回答by kevchadders

An Index Scan happens when the index definition cannot find on a single row to satisfy search predicates. In this case SQL Server has to scan multiple pagesto find a rangeof rows which satisfy the search predicates.

当索引定义无法在单行上找到满足搜索谓词时，就会发生索引扫描。在这种情况下，SQL Server 必须扫描多个页面才能找到满足搜索谓词的行范围。

In the case of a Index Seek, SQL Server finds a single row matching search predicates using indexdefinition.

在索引查找的情况下，SQL Server 使用索引定义查找与搜索谓词匹配的单行。

Index Seeks are better and more effective.

索引搜索更好、更有效。

回答by AutomatedTester

A scan touches every row in the table even if its what you are after or not

扫描会触及表格中的每一行，即使它是您所追求的

A seek looks only at the rows that are what you are looking for.

搜索只查看您要查找的行。

Seeks are always better to have than scans as they are more efficient in the way it looks data up.

搜索总是比扫描更好，因为它们在查找数据的方式上更有效。

A good explanation can be found here

一个很好的解释可以在这里找到