Index Seek vs Index Scan
In this article we shall examine(检查调查检测考试) the two modes of data search and retrieval using indexes- index seek and index scan, and the differences between the two.
Firstly, let us revisit indexes briefly. An index in a SQL Server database is analogous to the index at the start of a book. That is, its function is to allow you to quickly find the data you are searching for inside the book; in the case of a database, the “book” is a table.
An index scan means that SQL Server reads all rows in a table, and then returns only those rows that satisfy the search criteria(标准,条件).When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially(本质上,本来) means that all of the rows of the index are examined instead of the table directly. This is sometimes contrasted(比较,对比) to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.
索引扫描，遍历索引所有节点中的数据行。
表扫描，直接遍历表数据。
索引扫描和表扫描基本没什么区别。（索引扫描可以有时可避免回表，和排序）
You may wonder why the Query Optimizer may choose to do an index or table scan. Surely it is much faster to first look up data using an index than to go through(参加；经受；仔细检查；通过) all the rows in a table? In fact, for small tables data retrieval via an index or table scan is faster than using the index itself for selection. This is because the added overhead (额外开销)of first reading the index, then reading the pages containing the rows returned by the index, does not offer any performance improvement for a table with only a few rows.
Other reasons to use an index scan would be when an index is not selective enough, and when a query will return a large percentage (greater than 50%) of rows from the table. In such cases the additional overhead of first using the index may result in a small degradation of performance.
使用索引真的比遍历表快吗？
小表使用index scan 或者table scan 比 index seek 更快。这是因为先读取索引带来额外的开销，再通过索引读取数据航所在的页，对于小表的读取不会由性能提升。
An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition(条件). An index seek will be most beneficial in cases where a small percentage (less than 10 or 15%) of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; this is highly beneficial, in performance terms, when a table has a very large number of rows.(注意：index seek表数据量大是前提条件)
当返回数据量占比小于10或15的时候index seek 收益最大。
It is also worth noting that it is usually not worthwhile to create indexes on low-cardinality(基数) columns as they would rarely be used by the Query Optimizer. A low-cardinality column is one that contains a very small range of distinct values, for example a ‘Gender’ column would have only two distinct values- Male or Female. An example of a high-cardinality column is of course the primary key column, in which each value is distinct.
In summary, the Query Optimizer generally tries to perform an index seek. If this is not possible or beneficial (for example when the total number of rows is very small) then an index scan is used instead.
对于类似性别这类字段数据的范围很小的索引，查询优化器很少命中。
总之插叙优化器通常尽力使用index seek。如果没有或者收益不大就会使用index scan（例如表数据量很小）
