Index Seek和Index Scan的區別

標籤：

Index Seek是Sql Server執行查詢語句時利用建立的索引進行尋找，索引是B樹結構，Sql Server先尋找索引樹的根節點，一級一級向下尋找，在尋找到相應葉子節點後，取出葉子節點的資料。對於叢集索引，葉子節點是整個表的資料，能夠擷取到所有列的資料，而對於非叢集索引，葉子節點儲存的是索引列的資料，如果索引有包含列，那麼葉子節點中儲存有包含列的資料，擷取的資料是索引列和包含列，如果還需要其他列的資料，那麼必須進行key lookup，根據索引葉子節點包含的“行地址”資訊到源表中去擷取資料，這部屬於Index Seek，而是Key Looup（書籤尋找）的範疇。Index Seek非常適用於從大資料量的表中返回少量記錄的查詢。

Index Scan是直接遍曆索引樹的所有葉子節點，類似於Table Scan。

 

參考文章

http://www.cnblogs.com/wuxiaoqian726/articles/2015519.html

 

SQL有一個查詢最佳化分析器 Query Optimizer，其在執行查詢之前首先會進行分析，當查詢中有可以利用的索引時，其會優先分析使用Index Seek進行查詢的效率，在使用Index Seek查詢效率並不好的情況下，其會使用Index Scan進行查詢。那究竟是在什麼情況下會造成Index Seek效率比Index Scan還低呢？

         1.在要查詢的表中資料不是很多的話，使用Index Seek效率不一定高，因此使用Index seek還要先從索引樹開始，然後再利用葉子節點去尋找相應的行。在行樹比較少的情況下，還沒有直接進行Index scan快。

          2.在返回的資料量大的情況下，在返回的資料量佔總資料量的50%或者超過50%則使用Index Seek效率不一定好，在返回的資料量佔10%-15%時，利用Index Seek能獲得最佳的效能。

          3.在建立索引的列的取值很多是一致的情況下，建立索引不一定能獲得很好的效率。其實理由很簡單，當建立索引的列取值的變化少的情況下，建立的索引二叉樹是矮胖型的，樹層次不高，很多行的資訊都包含在葉子上，這樣的查詢顯然是不能很好的利用到索引。

 

 

參考文章

http://blog.sqlauthority.com/2009/08/24/sql-server-index-seek-vs-index-scan-diffefence-and-usage-a-simple-note/

 

SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note

In this article we shall examine the two modes of data search and retrieval using indexes- index seeks and index scans, 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.

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.

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的區別