SQL Server 计划：索引扫描/索引查找之间的区别
在 SQL Server 执行计划中，索引扫描和索引查找之间有什么区别
我使用的是 SQL Server 2005。
In a SQL Server Execution plan what is the difference between an Index Scan and an Index Seek
I'm on SQL Server 2005.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
索引扫描是 SQL Server 读取整个索引以查找匹配项 - 所需的时间与索引的大小成正比。
索引查找是指 SQL Server 使用索引的 B 树结构直接查找匹配的记录（请参阅 http://mattfleming.com/node/192 了解其工作原理） - 所花费的时间仅与匹配记录的数量成正比。
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.
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/192 for an idea on how this works) - time taken is only proportional to the number of matching records.
要遵循的基本规则是扫描不好，搜索好。
索引扫描
当 SQL Server 进行扫描时，它会将要从磁盘读取的对象加载到内存中，然后从上到下读取该对象以查找所需的记录。
索引查找
当 SQL Server 进行查找时，它知道数据将位于索引中的哪个位置，因此它从磁盘加载索引，直接转到所需的索引部分，然后读取到所需数据结束的位置。 这显然是比扫描更有效的操作，因为 SQL 已经知道它正在查找的数据位于何处。
如何修改执行计划以使用查找而不是扫描？
当 SQL Server 查找数据时，可能使 SQL Server 从查找切换到扫描的最重要的事情之一是当您要查找的某些列未包含在您希望其使用的索引中时。 大多数情况下，这会让 SQL Server 回退到执行聚集索引扫描，因为聚集索引包含表中的所有列。 这是我们现在能够在索引中包含列的最大原因之一（至少在我看来），而无需将这些列添加到索引的索引列中。 通过在索引中包含附加列，我们增加了索引的大小，但我们允许 SQL Server 读取索引，而不必返回到聚集索引或表本身来获取这些值。
参考
有关 SQL Server 执行计划中每个运算符的具体信息，请参阅...
聚集索引扫描 - 书籍
在线
聚集索引查找 - 书籍
在线
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.
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.
How can I modify an Execution Plan to use a Seek instead of a 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.
References
For information regarding the specifics of each of these operators within a SQL Server Execution plan see....
Clustered Index Scan - Books
Online
Clustered Index Seek - Books
Online
简短回答：
索引扫描：触摸除某些列之外的所有行。
索引查找：触摸某些行和某些列。
Short answer:
Index scan: Touch all rows but certain columns.
Index seek: Touch certain rows and certain columns.
通过索引扫描，将扫描索引中的所有行以查找匹配的行。 这对于小表来说非常有效。
使用索引查找，它只需要触及实际满足条件的行，因此通常性能更高
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
当索引定义无法在单行上找到满足搜索谓词时，就会发生索引扫描。 在这种情况下，SQL Server 必须扫描多个页面来查找满足搜索谓词的范围行。
在索引查找的情况下，SQL Server 使用索引定义查找与搜索谓词匹配的单行。
索引查找更好、更有效。
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 pages to find a range of rows which satisfy the search predicates.
In the case of a Index Seek, SQL Server finds a single row matching search predicates using index definition.
Index Seeks are better and more effective.
扫描会触及表中的每一行，无论它是否是
您想要的内容。查找仅查看您正在查找的行。
查找总是比扫描更好，因为它们查找数据的方式更有效。
可以找到一个很好的解释 这里
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