SQL Server 索引基礎知識(2)—-叢集索引，非叢集索引)

 

叢集索引　　一種索引，該索引中索引值的邏輯順序決定了表中相應行的物理順序。 　　叢集索引確定表中資料的物理順序。叢集索引類似於電話簿，按姓氏排列資料。由於叢集索引規定資料在表中的實體儲存體順序，因此一個表只能包含一個叢集索引。但該索引可以包含多個列（複合式索引），就像電話簿按姓氏和名字進行組織一樣。 　　　　 　　　　 叢集索引對於那些經常要搜尋範圍值的列特別有效。使用叢集索引找到包含第一個值的行後，便可以確保包含後續索引值的行在物理相鄰。例如，如果應用程式執行的一個查詢經常檢索某一日期範圍內的記錄，則使用叢集索引可以迅速找到包含開始日期的行，然後檢索表中所有相鄰的行，直到到達結束日期。這樣有助於提高此類查詢的效能。同樣，如果對從表中檢索的資料進行排序時經常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都進行排序，從而節省成本。 　　　　 　　　　 當索引值唯一時，使用叢集索引尋找特定的行也很有效率。例如，使用唯一僱員 ID 列 emp_id 尋找特定僱員的最快速的方法，是在 emp_id 列上建立叢集索引或 PRIMARY KEY 約束。　　 適用於叢集索引的情況包括：
　　1、含有大量非重複值的列。
　　2、使用BETWEEN,>,>=,<或<=返回一個範圍值的列
　　3、被連續訪問的列
　　4、返回大型結果集的查詢
　　5、經常被使用串連或GROUP BY子句的查詢訪問的列 叢集索引，非叢集索引在B+樹結構的基礎上，區別和實值型別和參考型別很象 下面轉了一個文章具體學習

 

 

 

 

 

 

由於需要給同事培訓資料庫的索引知識，就收集整理了這個系列的部落格。發表在這裡，也是對索引知識的一個總結回顧吧。通過總結，我發現自己以前很多很模糊的概念都清晰了很多。

不論是 叢集索引，還是非叢集索引，都是用B+樹來實現的。我們在瞭解這兩種索引之前，需要先瞭解B+樹。如果你對B樹不瞭解的話，建議參看以下幾篇文章：

BTree,B-Tree,B+Tree,B*Tree都是什麼
http://blog.csdn.net/manesking/archive/2007/02/09/1505979.aspx

B+ 型樹狀結構的結構圖:

B+ 型樹狀結構的特點:

• 所有關鍵字都出現在葉子結點的鏈表中（稠密索引），且鏈表中的關鍵字恰好是有序的；
• 不可能在非葉子結點命中；
• 非葉子結點相當於是葉子結點的索引（稀疏索引），葉子結點相當於是儲存（關鍵字）資料的資料層；

B+ 型樹狀結構中增加一個資料，或者刪除一個資料，需要分多種情況處理，比較複雜，這裡就不詳述這個內容了。 

叢集索引（Clustered Index）

• 叢集索引的分葉節點就是實際的資料頁
• 在資料頁中資料按照索引順序儲存
• 行的物理位置和行在索引中的位置是相同的
• 每個表只能有一個叢集索引
• 叢集索引的平均大小大約為表大小的5%左右

下面是兩副簡單描述叢集索引的： 

在叢集索引中執行下面語句的的過程：

select * from table where firstName = 'Ota'

 

一個比較抽象點的叢集索引圖示：

 

非叢集索引 （Unclustered Index）  

• 非叢集索引的頁，不是資料，而是指向資料頁的頁。
• 若未指定索引類型，則預設為非叢集索引
• 分葉節點頁的次序和表的實體儲存體次序不同
• 每個表最多可以有249個非叢集索引
• 在非叢集索引建立之前建立叢集索引（否則會引發索引重建）

在非叢集索引中執行下面語句的的過程：

select * from employee where lname = 'Green'

一個比較抽象點的非叢集索引圖示：

 

什麼是 Bookmark Lookup

雖然SQL 2005 中已經不在提  Bookmark Lookup 了(換湯不換藥)，但是我們的很多搜尋都是用的這樣的搜尋過程，如下：
先在非聚集中找，然後再在叢集索引中找。

 

在 http://www.sqlskills.com/ 提供的一個例子中，就給我們示範了 Bookmark Lookup  比 Table Scan 慢的情況，例子的指令碼如下：

USE CREDITgo-- These samples use the Credit database. You can download and restore the-- credit database from here:-- http://www.sqlskills.com/resources/conferences/CreditBackup80.zip-- NOTE: This is a SQL Server 2000 backup and MANY examples will work on -- SQL Server 2000 in addition to SQL Server 2005.--------------------------------------------------------------------------------- (1) Create two tables which are copies of charge:--------------------------------------------------------------------------------- Create the HEAPSELECT * INTO ChargeHeap FROM Chargego-- Create the CL TableSELECT * INTO ChargeCL FROM ChargegoCREATE CLUSTERED INDEX ChargeCL_CLInd ON ChargeCL (member_no, charge_no)go--------------------------------------------------------------------------------- (2) Add the same non-clustered indexes to BOTH of these tables:--------------------------------------------------------------------------------- Create the NC index on the HEAPCREATE INDEX ChargeHeap_NCInd ON ChargeHeap (Charge_no)go-- Create the NC index on the CL TableCREATE INDEX ChargeCL_NCInd ON ChargeCL (Charge_no)go--------------------------------------------------------------------------------- (3) Begin to query these tables and see what kind of access and I/O returns--------------------------------------------------------------------------------- Get ready for a bit of analysis:SET STATISTICS IO ON-- Turn Graphical Showplan ON (Ctrl+K)-- First, a point query (also, see how a bookmark lookup looks in 2005)SELECT * FROM ChargeHeap WHERE Charge_no = 12345goSELECT * FROM ChargeCL WHERE Charge_no = 12345go-- What if our query is less selective?-- 1000 is .0625% of our data... (1,600,000 million rows)SELECT * FROM ChargeHeap WHERE Charge_no < 1000goSELECT * FROM ChargeCL WHERE Charge_no < 1000go-- What if our query is less selective?-- 16000 is 1% of our data... (1,600,000 million rows)SELECT * FROM ChargeHeap WHERE Charge_no < 16000goSELECT * FROM ChargeCL WHERE Charge_no < 16000go--------------------------------------------------------------------------------- (4) What's the EXACT percentage where the bookmark lookup isn't worth it?--------------------------------------------------------------------------------- What happens here: Table Scan or Bookmark lookup?SELECT * FROM ChargeHeap WHERE Charge_no < 4000goSELECT * FROM ChargeCL WHERE Charge_no < 4000go-- What happens here: Table Scan or Bookmark lookup?SELECT * FROM ChargeHeap WHERE Charge_no < 3000goSELECT * FROM ChargeCL WHERE Charge_no < 3000go-- And - you can narrow it down by trying the middle ground:-- What happens here: Table Scan or Bookmark lookup?SELECT * FROM ChargeHeap WHERE Charge_no < 3500goSELECT * FROM ChargeCL WHERE Charge_no < 3500go-- And again:SELECT * FROM ChargeHeap WHERE Charge_no < 3250goSELECT * FROM ChargeCL WHERE Charge_no < 3250go-- And again:SELECT * FROM ChargeHeap WHERE Charge_no < 3375goSELECT * FROM ChargeCL WHERE Charge_no < 3375go-- Don't worry, I won't make you go through it all :)-- For the Heap Table (in THIS case), the cutoff is: 0.21%SELECT * FROM ChargeHeap  WHERE Charge_no < 3383goSELECT * FROM ChargeHeap WHERE Charge_no < 3384go-- For the Clustered Table (in THIS case), the cut-off is: 0.21%SELECT * FROM ChargeCL WHERE Charge_no < 3438SELECT * FROM ChargeCL WHERE Charge_no < 3439go

這個例子也就是 吳家震 在Teched 2007 上的那個示範例子。

小結：

這篇部落格只是簡單的用幾個圖表來介紹索引的實現方法：B+數， 叢集索引，非叢集索引，Bookmark Lookup 的資訊而已。

參考資料：

表組織和索引組織
http://technet.microsoft.com/zh-cn/library/ms189051.aspx
http://technet.microsoft.com/en-us/library/ms189051.aspx

How Indexes Work
http://manuals.sybase.com/onlinebooks/group-asarc/asg1200e/aseperf/@Generic__BookTextView/3358

Bookmark Lookup
http://blogs.msdn.com/craigfr/archive/2006/06/30/652639.aspx 

Logical and Physical Operators Reference
http://msdn2.microsoft.com/en-us/library/ms191158.aspx