SQL Server 查詢效能最佳化——建立索引原則（一）

     索引是什嗎？索引是提高查詢效能的一個重要工具，索引就是把查詢語句所需要的少量資料添加到索引分頁中，這樣訪問資料時只要訪問少數索引的分頁就可以。但是索引對於提高查詢效能也不是萬能的，也不是建立越多的索引就越好。索引建少了，用WHERE子句找資料效率低，不利於尋找資料。索引建多了，不利於新增、修改和刪除等操作，因為做這些操作時，SQL SERVER除了要更新資料表本身，還要連帶地立即更新所有的相關索引，而且過多的索引也會浪費硬碟空間。因此要建得恰到好處，這就需要經驗了。

 

一：索引的基本目的

     索引的基本目的是在大量資料中找尋少量資料。你可以想像一下，若一本書有700頁，就像資料表有700個資料頁，而索引卻有600個索引頁，你會想用索引來查詢書籍的內容嗎？

     索引欄位的值重複性越低越好，假設書籍中如“的”“了”這些在文章中重複性極高的字，每頁都有一大堆，你會先翻索引頁某個位置有“的”，翻回該頁讀取了“的”之後，再索引看下一個“的”，結果是在先前同一頁的不同位置，又翻回書籍原頁查看下一個“的”。

     那麼怎麼理解索引是從大量資料中尋找少量資料呢？下面我們舉個例子來說明。

     如果一個資料表的記錄平均長度為400位元組，則100萬條記錄需要5萬個資料頁，其計算公式如下：

　　1000000/（8060/400）=50000

　　如果該資料表建立叢集索引，索引值為4個位元組長度，而ID的資料長度為13個位元組，因此索引結構每條記錄為20個位元組。

　　4（叢集索引索引值）+13（ID索引值）+3（管理資訊）=20

　　以ID欄位所建立的索引，100%填充率，則總分頁數約為2482頁，其計算方式如下：

　　1000000/(8060/20)

　　即使是使用80%的填充率來計算也只有3106頁。其計算方式如下：

　　1000000/((8060*0.8)/20)

　　從上面可以看出如果是第一種情況，則索引頁只佔到總資料頁的5%：

　　2482/50000=0.04964 

　　即使考慮取每頁只填充80%的索引資料，第二種情況，索引頁也只是佔總資料頁的6%：

　　3106/50000=0.06212 

　　再說如果查詢條件中的欄位建立索引，則由於索引索引值資料都是以B-Tree有順序的擺放，所以可採用二分尋找找資料。也就是2的N次方大於記錄數，就可以找到該條資料。而2的20次方大於100萬，因此最多找尋20次就可以找到該條記錄。由於比較次數少，資料結構也小，節省訪問硬碟與內在的資源，索引將大幅提升找尋資料的效率。SQL SERVER為提高訪問與尋找對比的效率，用來作索引的資料域索引值愈小愈好，也就是要讓分頁盡量存更多的索引值記錄。

 

 註：

　　如果未使用 UNIQUE 屬性建立叢集索引，資料庫引擎將向表自動添加一個 4 位元組的 uniqueifier 列。必要時，資料庫引擎將向行自動添加一個 uniqueifier 值以使每個鍵唯一。此列和列值供內部使用，使用者不能查看或訪問。

 

 

二：什麼是索引

　　在 SQL Server 中，索引是按 B 樹結構進行組織的。如。

          　　

 

　　您也可以把索引理解為一種特殊的目錄。微軟的SQL SERVER提供了兩種索引：叢集索引（clustered index，也稱聚類索引、簇集索引）和非叢集索引（nonclustered index，也稱非聚類索引、非簇集索引）。下面，舉例來說明一下叢集索引和非叢集索引的區別：

　　其實，新華字典的本文本身就是一個叢集索引。比如，我們要查“按”字，就會很自然地翻開字典的前幾頁，因為“按”的拼音是“an”，而按照拼音排序的新華字典是以英文字母“a”開頭並以“z”結尾的，那麼“按”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那麼就說明新華字典中沒有這個字；同樣的，如果查“招”字，那也會將新華字典翻到最後部分，因為“招”的拼音是“zhao”。也就是說，新華字典的本文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內容。我們把這種本文內容本身就是一種按照一定規則排列的目錄稱為“叢集索引”。

　　如果您碰到一個不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據“偏旁部首”查到您要找的字，然後根據這個字後的頁碼直接翻到某頁來找到您要找的字。但您結合“部首目錄”和“檢字表”而查到的字的排序並不是真正的本文的排序方法，比如您查“張”字，我們可以看到在查部首之後的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63 頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字並不是真正的分別位於“張”字的上下方，現在您看到的連續的“馳、張、弩”三字實際上就是他們在非叢集索引中的排序，是字典本文中的字在非叢集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結果，然後 再翻到您所需要的頁碼。我們把這種目錄純粹是目錄，本文純粹是本文的排序方式稱為“非叢集索引”。

 

通過以上例子，我們可以理解到什麼是“叢集索引”和“非叢集索引”。進一步引申一下。

 

叢集索引

　　叢集索引指的是資料表本身就是索引的一部分，就是指資料表本身就是叢集索引的子葉層，整個資料表的擺放順序是按照你選定的索引值由小到大排序，SQL SERVER  2000 之後的版本可指定資料由大到小排序。

　　整個資料表按照索引值欄位由小到大排序，再搭配由索引值欄位加上指標的上層索引結構，也就是根節點和非子葉層級，形成整個叢集索引。因為資料表內實際擺放資料的方式只能遵循一種順序，所以一個資料表只能有一個叢集索引。在指定叢集索引時，資料域本身並不需要唯一，或指定為唯一的叢集索引，SQL SERVER內部會自動為重複的索引值建立4個位元組的唯一標識。

　　如果你的資料表有一列常常用來排序，另一列常常用來 範圍查詢，還有一列重複性非常高，則該用哪一列來做叢集索引。正確答案是依據哪個查詢最重要，最常被使用者執行。例如：你的老闆一小時內多次執行某個查詢當然比一個月執行一兩次的查詢來得重要。 

　　表（堆）建立叢集索引或刪除和重新建立現有叢集索引時，要求資料庫具有額外的可用工作區來容納資料排序結果和原始表或現有叢集索引資料的臨時副本。 

　　當堆或聚集表具有多個分區時，每個分區都有一個堆或 B 樹結構，其中包含該指定分區的行組。例如，如果一個聚集表有 4 個分區，那麼將有 4 個 B 樹，每個分區一個。

 

　　叢集索引（ Clustered Index）

　　·        叢集索引的分葉節點就是實際的資料頁

　　·        在資料頁中資料按照索引順序儲存

　　·        行的物理位置和行在索引中的位置是相同的

　　·        每個表只能有一個叢集索引

　　·        叢集索引的平均大小大約為表大小的 5%左右

 

　　要使用索引來更有效地排序查詢資料，最直接的方式就是在你要排序的欄位上建立叢集索引。在建立叢集索引之後，SQL SERVER會重新組織資料頁，讓其中的資料行按照叢集索引中索引值的順序儲存。SQL SERVER不需要在硬碟上的資料一定要實際按照叢集索引排序，但在建立叢集索引時，會嘗試在邏輯上排序資料的同時，也會在物理上讓資料儘可能地排序。在索引子葉層級中的每個資料頁都有一個指標指向索引分頁的前一頁與後一頁，形成雙向連結串列，在內部的系統資料表包含了各索引子葉層第一個分頁的地址，為了保證資料在邏輯上是依照叢集索引的順序存放的，SQL SERVER 只需要由第一個分頁開始，並依照其串連串列一個接著一個依序尋找資料即可。如。

 

         

 

註：聚集表是有叢集索引的表。

 

 

非叢集索引

 　　非叢集索引是完全獨立於資料表之外的結構，所以不會影響資料行的順序，其子葉層包含索引行。每個索引行包含非聚集索引值、行定位器和任意包含列或非鍵列。行定位器中存入的資料有兩種類型：書籤（BOOKMARK）或叢集索引的索引值。如果資料表上建立了叢集索引，則行定位器中存入的資料就是叢集索引的索引值。如果資料表沒有建立叢集索引，則行定位器中存入的資料就是書籤，即指向資料表中記錄具體位置的ROWID，也就是文檔編號、分頁編號與頁內記錄編號（稱之為SOLT編號）所組合成的值。通過該ROWID 在資料表內擷取資料就稱為書籤尋找 BOOKMARK LOOKUP。所以，一般通過非叢集索引尋找到符合的索引值後，還會搭配書籤尋找。

　　當非叢集索引從結構中找到符合的記錄時，雖然在子葉層該索引值是由小到大排序，因此可能在一個分頁上就有全部符合查詢條件的索引值，但因為資料表中資料行的擺放是沒有按順序的（或是說沒有按照該非叢集索引的索引值順序擺放），所以真正符合記錄的資料是散布在文檔各處的，而SQL SERVER每次讀取資料都是以資料頁為單位，因此，找到一條記錄所在位置後，要先將存放該條記錄的分頁讀到記憶體中，再從該頁讀出記錄。

　　因為BOOKMARK LOOKUP是進行隨機的I/O操作，當符合查詢的記錄很多時，通過非叢集索引訪問將導致資料頁讀取非常頻繁，就算兩條記錄在同一個分頁，該分頁也會被重複讀兩次，因此或符合的記錄有N條，就需要讀取資料表內的分頁N頁，雖然大部分的讀取操作都是針對記憶體中的快取，但記錄數過多時一樣沒有效率，還不如資料表掃描，全部掃描一遍，把符合條件資料找出來。

　　雖然 SQL 2005 以後的版本中已經不在提 BOOKMARK LOOKUP了(但實際上卻是換湯不換藥)，我們的很多搜尋都是使用如下的搜尋過程：先在非聚集中找，然後再在叢集索引中找。如。

         

 

 

　　非叢集索引 （ Unclustered Index）  

　　·        非叢集索引的頁，不是資料，而是指向資料頁的頁。

　　·        若未指定索引類型，則預設為非叢集索引

　　·        分葉節點頁的次序和表的實體儲存體次序不同

　　·        每個表最多可以有 249個非叢集索引（一般認為每個表不應該超過10個索引）

　　·        在非叢集索引建立之前建立叢集索引（否則會引發索引重建）

 

　　叢集索引與非叢集索引使用的情況：

動作描述	使用叢集索引	使用非叢集索引
外鍵列	應	應
主鍵列	應	應
列經常被分組排序(order by)	應	應
返回某範圍內的資料	應	不應
小數目的不同值	應	不應
大數目的不同值	不應	應
頻繁更新的列	不應	應
頻繁修改索引列	不應	應
一個或極少不同值	不應	不應

 

　　今天就普及一下索引的一些基本知識，明天來說明怎麼選擇要建立索引的列，條件是什麼，方法是什麼。