理解SQL Server中索引的概念，原理理解SQL Server中索引的概念，原理

理解SQL Server中索引的概念，原理 摘自:http://51even.iteye.com/blog/1490412 簡介

 

    在SQL Server中，索引是一種增強式的存在，這意味著，即使沒有索引，SQL Server仍然可以實現應有的功能。但索引可以在大多數情況下大大提升查詢效能，在OLAP中尤其明顯.要完全理解索引的概念，需要瞭解大量原理性的知識，包括B樹，堆，資料庫頁，區，填滿因數，片段,檔案組等等一系列相關知識，這些知識寫一本小書也不為過。所以本文並不會深入討論這些主題。

 

索引是什麼

    索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速存取資料庫表中的特定資訊。

    精簡來說，索引是一種結構.在SQL Server中，索引和表（這裡指的是加了叢集索引的表）的儲存結構是一樣的,都是B樹，B樹是一種用於尋找的平衡多叉樹.理解B樹的概念如:

    

    理解為什麼使用B樹作為索引和表（有叢集索引）的結構，首先需要理解SQL Server儲存資料的原理.

    在SQL SERVER中，儲存的單位最小是頁(PAGE),頁是不可再分的。就像細胞是生物學中不可再分的，或是原子是化學中不可再分的最小單位一樣.這意味著,SQL SERVER對於頁的讀取，要麼整個讀取，要麼完全不讀取，沒有折中.

    在資料庫檢索來說，對於磁碟IO掃描是最消耗時間的.因為磁碟掃描涉及很多物理特性，這些是相當消耗時間的。所以B樹設計的初衷是為了減少對於磁碟的掃描次數。如果一個表或索引沒有使用B樹（對於沒有叢集索引的表是使用堆heap儲存),那麼尋找一個資料，需要在整個表自主資料庫頁中全盤掃描。這無疑會大大加重IO負擔.而在SQL SERVER中使用B樹進行儲存，則僅僅需要將B樹的根節點存入記憶體，經過幾次尋找後就可以找到存放所需資料的被葉子節點包含的頁！進而避免的全盤掃描從而提高了效能.

    下面，通過一個例子來證明：

     在SQL SERVER中，表上如果沒有建立叢集索引，則是按照堆（HEAP）存放的，假設我有這樣一張表:

     

     現在這張表上沒有任何索引，也就是以堆存放，我通過在其上加上叢集索引（以B樹存放）來展現對IO的減少:

     

 

 

 

理解聚集和叢集索引

    在SQL SERVER中，最主要的兩類索引是叢集索引和非叢集索引。可以看到，這兩個分類是圍繞聚集這個關鍵字進行的.那麼首先要理解什麼是聚集.

    聚集在索引中的定義:

    為了提高某個屬性(或屬性群組)的查詢速度，把這個或這些屬性(稱為聚集碼)上具有相同值的元組集中存放在連續的物理塊稱為聚集。

    簡單來說，叢集索引就是:

    

    在SQL SERVER中，聚集的作用就是將某一列（或是多列）的物理順序改變為和邏輯順序相一致,比如，我從adventureworks資料庫的employee中抽取5條資料:

    

    當我在ContactID上建立叢集索引時，重新查詢:

    

    在SQL SERVER中，叢集索引的儲存是以B樹儲存，B樹的葉子直接儲存叢集索引的資料:

    

    因為叢集索引改變的是其所在表的實體儲存體順序，所以每個表只能有一個叢集索引.

 

非叢集索引

     因為每個表只能有一個叢集索引，如果我們對一個表的查詢不僅僅限於在叢集索引上的欄位。我們又對叢集索引列之外還有索引的要求，那麼就需要非叢集索引了.

     非叢集索引，本質上來說也是叢集索引的一種.非叢集索引並不改變其所在表的物理結構，而是額外產生一個叢集索引的B樹結構，但葉子節點是對於其所在表的引用,這個引用分為兩種，如果其所在表上沒有叢集索引，則引用行號。如果其所在表上已經有了叢集索引，則引用叢集索引的頁.

     一個簡單的非叢集索引概念如下:

     

     可以看到，非叢集索引需要額外的空間進行儲存，按照被索引列進行叢集索引，並在B樹的葉子節點包含指向非叢集索引所在表的指標.

     MSDN中，對於非叢集索引描述圖是:

     

     可以看到，非叢集索引也是一個B樹結構，與叢集索引不同的是，B樹的葉子節點存的是指向堆或叢集索引的指標.

     通過非叢集索引的原理可以看出，如果其所在表的物理結構改變後，比如加上或是刪除叢集索引，那麼所有非叢集索引都需要被重建，這個對於效能的損耗是相當大的。所以最好要先建立叢集索引，再建立對應的非叢集索引.

 

叢集索引 VS 非叢集索引

      前面通過對於叢集索引和非叢集索引的原理解釋.我們不難發現，大多數情況下，叢集索引的速度比非叢集索引要略快一些.因為叢集索引的B樹葉子節點直接儲存資料，而非叢集索引還需要額外通過葉子節點的指標找到資料.

      還有，對於大量連續資料尋找，非叢集索引十分乏力，因為非叢集索引需要在非叢集索引的B樹中找到每一行的指標，再去其所在表上找資料，效能因此會大打折扣.有時甚至不如不加非叢集索引.

      因此，大多數情況下叢集索引都要快於非叢集索引。但叢集索引只能有一個，因此選對叢集索引所施加的列對於查詢效能提升至關緊要.

 

索引的使用

     索引的使用並不需要顯式使用，建立索引後查詢分析器會自動找出最短路徑使用索引.

     但是有這種情況.當隨著資料量的增長，產生了索引片段後，很多儲存的資料進行了不適當的跨頁，會造成片段(關於跨頁和片段以及填滿因數的介紹，我會在後續文章中說到)我們需要重建立立索引以加快效能:

     比如前面的test_tb2上建立的一個叢集索引和非叢集索引，可以通過DMV語句查詢其索引的情況:

SELECT index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,record_count,avg_page_space_used_in_percentFROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),OBJECT_ID('test_tb2'),NULL,NULL,'Sampled')

 

     

    我們可以通過重建索引來提高速度:

ALTER INDEX idx_text_tb2_EmployeeID ON test_tb2 REBUILD

 

 

    還有一種情況是，當隨著表資料量的增大，有時候需要更新表上的統計資訊，讓查詢分析器根據這些資訊選擇路徑，使用:

UPDATE STATISTICS 表名

   那麼什麼時候知道需要更新這些統計資訊呢，就是當執行計畫中估計行數和實際表的行數有出入時:

   

 

使用索引的代價

    我最喜歡的一句話是”everything has price”。我們通過索引獲得的任何效能提升並不是不需要付出代價。這個代價來自幾方面.

    1.通過叢集索引的原理我們知道，當表建立索引後，就以B樹來儲存資料.所以當對其進行更新插入刪除時，就需要頁在物理上的移動以調整B樹.因此當更新插入刪除資料時，會帶來效能的下降。而對於叢集索引，當更新表後，非叢集索引也需要進行更新，相當於多更新了N（N=非叢集索引數量）個表。因此也下降了效能.

    2.通過上面對非叢集索引原理的介紹，可以看到，非叢集索引需要額外的磁碟空間。

    3.前文提過，不恰當的非叢集索引反而會降低效能.

    所以使用索引需要根據實際情況進行權衡.通常我都會將非叢集索引全部放到另外一個獨立硬碟上，這樣可以分散IO，從而使查詢並行.

摘自:http://51even.iteye.com/blog/1490412 簡介

 

    在SQL Server中，索引是一種增強式的存在，這意味著，即使沒有索引，SQL Server仍然可以實現應有的功能。但索引可以在大多數情況下大大提升查詢效能，在OLAP中尤其明顯.要完全理解索引的概念，需要瞭解大量原理性的知識，包括B樹，堆，資料庫頁，區，填滿因數，片段,檔案組等等一系列相關知識，這些知識寫一本小書也不為過。所以本文並不會深入討論這些主題。

 

索引是什麼

    索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速存取資料庫表中的特定資訊。

    精簡來說，索引是一種結構.在SQL Server中，索引和表（這裡指的是加了叢集索引的表）的儲存結構是一樣的,都是B樹，B樹是一種用於尋找的平衡多叉樹.理解B樹的概念如:

    

    理解為什麼使用B樹作為索引和表（有叢集索引）的結構，首先需要理解SQL Server儲存資料的原理.

    在SQL SERVER中，儲存的單位最小是頁(PAGE),頁是不可再分的。就像細胞是生物學中不可再分的，或是原子是化學中不可再分的最小單位一樣.這意味著,SQL SERVER對於頁的讀取，要麼整個讀取，要麼完全不讀取，沒有折中.

    在資料庫檢索來說，對於磁碟IO掃描是最消耗時間的.因為磁碟掃描涉及很多物理特性，這些是相當消耗時間的。所以B樹設計的初衷是為了減少對於磁碟的掃描次數。如果一個表或索引沒有使用B樹（對於沒有叢集索引的表是使用堆heap儲存),那麼尋找一個資料，需要在整個表自主資料庫頁中全盤掃描。這無疑會大大加重IO負擔.而在SQL SERVER中使用B樹進行儲存，則僅僅需要將B樹的根節點存入記憶體，經過幾次尋找後就可以找到存放所需資料的被葉子節點包含的頁！進而避免的全盤掃描從而提高了效能.

    下面，通過一個例子來證明：

     在SQL SERVER中，表上如果沒有建立叢集索引，則是按照堆（HEAP）存放的，假設我有這樣一張表:

     

     現在這張表上沒有任何索引，也就是以堆存放，我通過在其上加上叢集索引（以B樹存放）來展現對IO的減少:

     

 

 

 

理解聚集和叢集索引

    在SQL SERVER中，最主要的兩類索引是叢集索引和非叢集索引。可以看到，這兩個分類是圍繞聚集這個關鍵字進行的.那麼首先要理解什麼是聚集.

    聚集在索引中的定義:

    為了提高某個屬性(或屬性群組)的查詢速度，把這個或這些屬性(稱為聚集碼)上具有相同值的元組集中存放在連續的物理塊稱為聚集。

    簡單來說，叢集索引就是:

    

    在SQL SERVER中，聚集的作用就是將某一列（或是多列）的物理順序改變為和邏輯順序相一致,比如，我從adventureworks資料庫的employee中抽取5條資料:

    

    當我在ContactID上建立叢集索引時，重新查詢:

    

    在SQL SERVER中，叢集索引的儲存是以B樹儲存，B樹的葉子直接儲存叢集索引的資料:

    

    因為叢集索引改變的是其所在表的實體儲存體順序，所以每個表只能有一個叢集索引.

 

非叢集索引

     因為每個表只能有一個叢集索引，如果我們對一個表的查詢不僅僅限於在叢集索引上的欄位。我們又對叢集索引列之外還有索引的要求，那麼就需要非叢集索引了.

     非叢集索引，本質上來說也是叢集索引的一種.非叢集索引並不改變其所在表的物理結構，而是額外產生一個叢集索引的B樹結構，但葉子節點是對於其所在表的引用,這個引用分為兩種，如果其所在表上沒有叢集索引，則引用行號。如果其所在表上已經有了叢集索引，則引用叢集索引的頁.

     一個簡單的非叢集索引概念如下:

     

     可以看到，非叢集索引需要額外的空間進行儲存，按照被索引列進行叢集索引，並在B樹的葉子節點包含指向非叢集索引所在表的指標.

     MSDN中，對於非叢集索引描述圖是:

     

     可以看到，非叢集索引也是一個B樹結構，與叢集索引不同的是，B樹的葉子節點存的是指向堆或叢集索引的指標.

     通過非叢集索引的原理可以看出，如果其所在表的物理結構改變後，比如加上或是刪除叢集索引，那麼所有非叢集索引都需要被重建，這個對於效能的損耗是相當大的。所以最好要先建立叢集索引，再建立對應的非叢集索引.

 

叢集索引 VS 非叢集索引

      前面通過對於叢集索引和非叢集索引的原理解釋.我們不難發現，大多數情況下，叢集索引的速度比非叢集索引要略快一些.因為叢集索引的B樹葉子節點直接儲存資料，而非叢集索引還需要額外通過葉子節點的指標找到資料.

      還有，對於大量連續資料尋找，非叢集索引十分乏力，因為非叢集索引需要在非叢集索引的B樹中找到每一行的指標，再去其所在表上找資料，效能因此會大打折扣.有時甚至不如不加非叢集索引.

      因此，大多數情況下叢集索引都要快於非叢集索引。但叢集索引只能有一個，因此選對叢集索引所施加的列對於查詢效能提升至關緊要.

 

索引的使用

     索引的使用並不需要顯式使用，建立索引後查詢分析器會自動找出最短路徑使用索引.

     但是有這種情況.當隨著資料量的增長，產生了索引片段後，很多儲存的資料進行了不適當的跨頁，會造成片段(關於跨頁和片段以及填滿因數的介紹，我會在後續文章中說到)我們需要重建立立索引以加快效能:

     比如前面的test_tb2上建立的一個叢集索引和非叢集索引，可以通過DMV語句查詢其索引的情況:

SELECT index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,record_count,avg_page_space_used_in_percentFROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),OBJECT_ID('test_tb2'),NULL,NULL,'Sampled')

 

     

    我們可以通過重建索引來提高速度:

ALTER INDEX idx_text_tb2_EmployeeID ON test_tb2 REBUILD

 

 

    還有一種情況是，當隨著表資料量的增大，有時候需要更新表上的統計資訊，讓查詢分析器根據這些資訊選擇路徑，使用:

UPDATE STATISTICS 表名

   那麼什麼時候知道需要更新這些統計資訊呢，就是當執行計畫中估計行數和實際表的行數有出入時:

   

 

使用索引的代價

    我最喜歡的一句話是”everything has price”。我們通過索引獲得的任何效能提升並不是不需要付出代價。這個代價來自幾方面.

    1.通過叢集索引的原理我們知道，當表建立索引後，就以B樹來儲存資料.所以當對其進行更新插入刪除時，就需要頁在物理上的移動以調整B樹.因此當更新插入刪除資料時，會帶來效能的下降。而對於叢集索引，當更新表後，非叢集索引也需要進行更新，相當於多更新了N（N=非叢集索引數量）個表。因此也下降了效能.

    2.通過上面對非叢集索引原理的介紹，可以看到，非叢集索引需要額外的磁碟空間。

    3.前文提過，不恰當的非叢集索引反而會降低效能.

    所以使用索引需要根據實際情況進行權衡.通常我都會將非叢集索引全部放到另外一個獨立硬碟上，這樣可以分散IO，從而使查詢並行.