認識SQLServer索引以及單列索引和多列索引的不同

標籤：des   style   blog   http   io   ar   color   os   使用   

 一、索引的概念

　　索引的用途：我們對資料查詢及處理速度已成為衡量應用系統成敗的標準，而採用索引來加快資料處理速度通常是最普遍採用的最佳化方法。

　　索引是什麼：資料庫中的索引類似於一本書的目錄，在一本書中使用目錄可以快速找到你想要的資訊，而不需要讀完全書。在資料庫中，資料庫程式使用索引可以重啊到表中的資料，而不必掃描整個表。書中的目錄是一個字詞以及各字詞所在的頁碼列表，資料庫中的索引是表中的值以及各值儲存位置的列表。

　　索引的利弊：查詢執行的大部分開銷是I/O，使用索引提高效能的一個主要目標是避免全表掃描，因為全表掃描需要從磁碟上讀取表的每一個資料頁，如果有索引指向資料值，則查詢只需要讀少數次的磁碟就行啦。所以合理的使用索引能加速資料的查詢。但是索引並不總是提高系統的效能，帶索引的表需要在資料庫中佔用更多的儲存空間，同樣用來增刪資料的命令已耗用時間以及維護索引所需的處理時間會更長。所以我們要合理使用索引，及時更新去除次優索引。

 

 二、資料表的基本結構

　　一個新表被建立之時，系統將在磁碟中分配一段以8K為單位的連續空間，當欄位的值從記憶體寫入磁碟時，就在這一既定空間隨機儲存，當一個 8K用完的時候，資料庫指標會自動分配一個8K的空間。這裡，每個8K空間被稱為一個資料頁（Page），又名頁面或資料頁面，並分配從0-7的頁號, 每個檔案的第0頁記錄引導資訊，叫檔案頭（File header）；每8個資料頁（64Ｋ）的組合形成擴充區（Extent），稱為擴充。全部資料頁的組合形成堆（Heap）。

　　SQLS規定行不能跨越資料頁，所以，每行記錄的最大資料量只能為8K。這就是char和varchar這兩種字串類型容量要限制在8K以內的 原因，儲存超過8K的資料應使用text類型，實際上，text類型的欄位值不能直接錄入和儲存，它只是儲存一個指標，指向由若干8K的文本資料頁所組成 的擴充區，真正的資料正是放在這些資料頁中。 

頁面有空間頁面和資料頁面之分。  

　　當一個擴充區的8個資料頁中既包含了空間頁面又包括了資料或索引頁面時，稱為混合擴充（Mixed Extent）,每張表都以混合擴充開始；反之，稱為一致擴充（Uniform Extent），專門儲存資料及索引資訊。 

　　表被建立之時，SQLS在混合擴充中為其分配至少一個資料頁面，隨著資料量的增長，SQLS可即時在混合擴充中分配出7個頁面，當資料超過8個頁面時，則從一致擴充中分配資料頁面。  

　　空間頁面專門負責資料空間的分配和管理，包括：PFS頁面（Page free space）：記錄一個頁面是否已指派、位於混合擴充還是一致擴充以及頁面上還有多少可用空間等資訊；GAM頁面（Global allocation map）和SGAM頁面(Secodary global allocation map)：用來記錄閒置擴充或含有空閑頁面的混合擴充的位置。SQLS綜合利用這三種類型的分頁檔在必要時為資料表建立新空間；  

資料頁或索引頁則專門儲存資料及索引資訊，SQLS使用4種類型的資料頁面來管理表或索引：它們是IAM頁、資料頁、文本/映像頁和索引頁。 

　　在WINDOWS中，我們對檔案執行的每一步操作，在磁碟上的物理位置只有系統（system）才知道；SQL SERVER沿襲了這種工作方式，在插入資料的過程中，不但每個欄位值在資料頁面中的儲存位置是隨機的，而且每個資料頁面在“堆”中的排列位置也只有系統 （system）才知道。  

　　這是為什麼呢？眾所周知，OS之所以能管理DISK，是因為在系統啟動時首先載入了檔案配置表：FAT（File Allocation Table），正是由它管理檔案系統並記錄對檔案的一切操作，系統才得以正常運行；同理，作為管理系統級的SQL SERVER,也有這樣一張類似FAT的表存在，它就是索引分布映像頁：IAM（Index Allocation Map）。  

IAM的存在，使SQLS對資料表的物理管理有了可能。  

　　IAM頁從混合擴充中分配，記錄了8個初始頁面的位置和該擴充區的位置，每個IAM頁面能管理512,000個資料頁面，如果資料量太 大，SQLS也可以增加更多的IAM頁，可以位於檔案的任何位置。第一個IAM頁被稱為FirstIAM,其中記錄了以後的IAM頁的位置。  

　　資料頁和文本/映像頁互反，前者儲存非文本/映像類型的資料，因為它們都不超過8K的容量，後者則只儲存超過8K容量的文本或映像類型資料。而索 引頁顧名思義，儲存的是與索引結構相關的資料資訊。瞭解頁面的問題有助我們下一步準確理解SQLS維護索引的方式，如頁面分割、填滿因數等。    三、頁分裂

　　一半的資料將保留在老頁面，而另一半將放入新頁面，並且新頁面可能被分配到任何可用的頁。所以，頻繁頁分裂，後果很嚴重，將使物理表產生大量資料片段，導致直接造成I/O效率的急劇下降，最後，停止SQLS的運行並重建索引將是我們的唯一選擇!

 

 四、填滿因數

　　索引的一個特性，定義該索引每頁上的可用空間量。FILLFACTOR（填滿因數）適應以後表資料的擴充並減小了頁面分割的可能性。填滿因數是從0到100的百分比數值，設為100時表示將資料頁填滿。只有當不會對資料變更時(例如 唯讀表中)才用此設定。值越小則資料頁上的空閑空間越大，這樣可以減少在索引增長過程中進行頁分裂的需要，但這一操作需要佔用更多的硬碟空間。填滿因數指定不當，會降低資料庫的讀取效能，其降低量與填滿因數設定值成反比。

 

 五、索引的分類

SQL SERVER中有多種索引類型。

按儲存結構區分：“叢集索引（又稱聚類索引，簇集索引）”，“分叢集索引（非聚類索引，非簇集索引）”

按資料唯一性區分：“唯一索引”，“非唯一索引”

按鍵列個數區分：“單列索引”，“多列索引”。

 

 六、叢集索引

　　叢集索引是一種對磁碟上實際資料重新組織以按指定的一列或多列值排序。像我們用到的漢語字典，就是一個叢集索引，比如要查“張”，我們自然而然就翻到字典的後面百十頁。然後根據字母順序跟尋找出來。這裡用到微軟的平衡二叉樹演算法，即首先把書翻到大概二分之一的位置，如果要找的頁碼比該頁的頁碼小，就把書向前翻到四分之一處，否則，就把書向後翻到四分之三的地方，依此類推，把書頁續分成更小的部分，直至正確的頁碼。

　　由於叢集索引是給資料排序，不可能有多種排法，所以一個表只能建立一個叢集索引。科學統計建立這樣的索引需要至少相當與該表120%的附加空間，用來存放該表的副本和索引中間頁，但是他的效能幾乎總是比其它索引要快。

　　由於在叢集索引下，資料在物理上是按序排列在資料頁上的，重複值也排在一起，因而包含範圍檢查（bentween,<,><=,>=）或使用group by 或order by的查詢時，一旦找到第一個索引值的行，後面都將是連在一起，不必在進一步的搜尋，避免啦大範圍的掃描，可以大大提高查詢速度。

 

 七、非叢集索引

　　sqlserver預設情況下建立的索引是非叢集索引，他不重新組織表中的資料，而是對每一行儲存索引列值並用一個指標指向資料所在的頁面。他像漢語字典中的根據‘偏旁部首’尋找要找的字，即便對資料不排序，然而他擁有的目錄更像是目錄，對查取資料的效率也是具有的提升空間，而不需要全表掃描。

一個表可以擁有多個非叢集索引，每個非叢集索引根據索引列的不同提供不同的排序次序。

 

 八、索引的使用

1、索引的建立 

在SQL Server中建立索引的文法如下：

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )[with[PAD_INDEX][[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY][[,]DROP_EXISTING][[,]STATISTICS_NORECOMPUTE][[,]SORT_IN_TEMPDB]][ ON filegroup ] 

CREATE INDEX命令建立索引各參數說明如下：

UNIQUE：用於指定為表或視圖建立唯一索引，即不允許存在索引值相同的兩行。

CLUSTERED：用於指定建立的索引為叢集索引。

NONCLUSTERED：用於指定建立的索引為非叢集索引。

index_name：用於指定所建立的索引的名稱。

table：用於指定建立索引的表的名稱。

view：用於指定建立索引的視圖的名稱。

ASC|DESC：用於指定具體某個索引列的升序或降序排序方向。

Column：用於指定被索引的列。

PAD_INDEX：用於指定索引中間級中每個頁（節點）上保持開放的空間。

FILLFACTOR = fillfactor：用於指定在建立索引時，每個索引頁的資料占索引頁大小的百分比，fillfactor的值為1到100。

IGNORE_DUP_KEY：用於控制當往包含於一個唯一叢集索引中的列中插入重複資料時SQL Server所作的反應。

DROP_EXISTING：用於指定應刪除並重新建立已命名的先前存在的叢集索引或者非叢集索引。

STATISTICS_NORECOMPUTE：用於指定到期的索引統計不會自動重新計算。

SORT_IN_TEMPDB：用於指定建立索引時的中間排序結果將儲存在 tempdb 資料庫中。

ON filegroup：用於指定存放索引的檔案組。

 

2、索引的管理

Exec sp_helpindex BigData   --查看索引定義Exec sp_rename ‘BigData.idx_mobiel‘,‘idx_big_mobiel‘  --將索引名由‘idx_mobiel‘ 改為‘idx_big_mobiel‘drop index BigData.idx_big_mobiel  --刪除bigdata表中的idx_big_mobiel索引dbcc showcontig(bigdata,idx_mobiel) --檢查bigdata表中索引idx_mobiel的片段資訊dbcc indexdefrag(Test,bigdata,idx_mobiel)  --整理test資料庫中bigdata表的索引idx_mobiel上的片段update statistics bigdata  --更新bigdata表中的全部索引的統計資訊

 

3、索引的設計原則

對於一張表來說索引的有無和建立什麼樣的索引，要取決與where字句和Join運算式中。

一般來說建立索引的原則包括以下內容：

• 系統一般會給逐漸欄位自動建立叢集索引。
• 有大量重複值且經常有範圍查詢和排序、分組的列，或者經常頻繁訪問的列，考慮建立叢集索引。
• 在一個經常做插入操作的表中建立索引，應使用fillfactor(填滿因數)來減少頁分裂，同時提高並發度降低死結的發生。如果在表為唯讀表，填滿因數可設為100.
• 在選擇索引鍵時，儘可能採用小資料類型的列作為鍵以使每個索引頁能容納儘可能多的索引鍵和指標，通過這種方式，可使一個查詢必需遍曆的索引頁面降低到最小，此外，儘可能的使用整數做為索引值，因為整數的訪問速度最快。

 

 九、單列索引和多列索引的比較

 　　在SQL Server中，索引分為兩種，一種是單列索引，一種是多列索引。多列索引建立方法類似單列索引，只是擁有多個列，

我們建立了IX_StuID_StuName多列索引，其中包含表中的兩列StuId和StuName.

假設有這樣一個people表： 

CREATE TABLE People(    PeopleId INT NOT NULL,    FirstName NVARCHAR(50) NOT NULL,    LastName NVARCHAR(50) NOT NULL,    Age INT NOT NULL,    PRIMARY KEY (PeopleId)  );

下面是我們插入到這個people表的資料：
　　表中有四個名字為“Mikes”的人(其中兩個姓Sullivans，兩個姓McConnells)，有兩個年齡為17歲的人，還有一個名字與眾不同的Joe Smith。 這個表的主要用途是根據指定的使用者姓、名以及年齡返回相應的peopleid。

例如，我們可能需要尋找姓名為MikeSullivan、年齡17歲使用者的peopleid，SQL語句為

 SELECT peopleid FROM people WHERE firstname=‘Mike‘ AND lastname=‘Sullivan‘ AND age=17;

　　首先，我們可以考慮在單個列上建立索引，比如firstname、lastname或者age列。如果我們建立firstname列的索引，資料庫將通過這個索引迅速把搜尋範圍限制到那些firstname=‘Mike‘的記錄，然後再在這個“中間結果集”上進行其他條件的搜尋：它首先排除那些lastname不等於“Sullivan”的記錄，然後排除那些age不等於17的記錄。當記錄滿足所有搜尋條件之後，資料庫就返回最終的搜尋結果。
　　由於建立了firstname列的索引，與執行表的完全掃描相比，MySQL的效率提高了很多，但我們要求MySQL掃描的記錄數量仍舊遠遠超過了實際所需要的。雖然我們可以刪除firstname列上的索引，再建立lastname或者age列的索引，但總地看來，不論在哪個列上建立索引搜尋效率仍舊相似。
　　為了提高搜尋效率，我們需要考慮運用多列索引。如果為firstname、lastname和age這三個列建立一個多列索引，資料庫只需一次檢索就能夠找出正確的結果！
　　那麼，如果在firstname、lastname、age這三個列上分別建立單列索引，效果是否和建立一個firstname、lastname、age的多列索引一樣呢？

　　答案是否定的，兩者完全不同。當我們執行查詢的時候，MySQL只能使用一個索引。如果你有三個單列的索引，MySQL會試圖選擇一個限制最嚴格的索引。但是，即使是限制最嚴格的單列索引，它的限制能力也肯定遠遠低於firstname、lastname、age這三個列上的多列索引。

　　多列索引還有另外一個優點，它通過稱為最左首碼（Leftmost Prefixing）的概念體現出來。繼續考慮前面的例子，現在我們有一個firstname、lastname、age列上的多列索引，我們稱這個索引為fname_lname_age。當搜尋條件是以下各種列的組合時，資料庫將使用fname_lname_age索引：
firstname，lastname，age
firstname，lastname
firstname
從另一方面理解，它相當於我們建立了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的索引。下面這些查詢都能夠使用這個fname_lname_age索引：

SELECT peopleid FROM people WHERE firstname=‘Mike‘ AND lastname=‘Sullivan‘ AND age=‘17‘; SELECT peopleid FROM people WHERE firstname=‘Mike‘ AND lastname=‘Sullivan‘; SELECT peopleid FROM people WHERE firstname=‘Mike‘; 

下面這些查詢不能夠使用這個fname_lname_age索引：

SELECT peopleid FROM people Where lastname=‘Sullivan‘; SELECT peopleid FROM people Where age=‘17‘; SELECT peopleid FROM people Where lastname=‘Sullivan‘ AND age=‘17‘;

 

免責聲明： 本文大部分內容來自http://www.cnblogs.com/knowledgesea/p/3672099.html，單列和多列索引介紹來自網路。

 

認識SQLServer索引以及單列索引和多列索引的不同