SQL Server 计划:索引扫描/索引查找之间的区别 [英] SQL Server Plans : difference between Index Scan / Index Seek

问题描述

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

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

我使用的是 SQL Server 2005.

I'm on SQL Server 2005.

推荐答案

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

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 服务器使用索引的 b 树结构直接查找匹配记录的地方(参见 http://mattfleming.com/node/192 了解其工作原理) - 所用时间仅与数量成正比匹配记录.

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.

• 一般来说，索引查找比索引扫描更可取(当匹配记录的数量远低于记录总数时)，因为执行索引查找所用的时间是恒定的，无论总数有多少表中的记录.
• 但是请注意，在某些情况下，索引扫描可能比索引查找更快(有时明显更快) - 通常当表非常小，或者当大部分记录与索引匹配时谓词.

• 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 significantly faster) - usually when the table is very small, or when a large percentage of the records match the predicate.

这篇关于SQL Server 计划:索引扫描/索引查找之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！