SQL Server中的索引

標籤：des   http   color   io   os   ar   使用   strong   sp   

1 SQL Server中的索引

　　索引是與表或視圖關聯的磁碟上結構，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列產生的鍵。這些鍵儲存在一個結構(B 樹)中，使 SQL Server 可以快速有效地尋找與索引值關聯的行。

　　表或視圖可以包含以下類型的索引：

　　叢集索引

　　叢集索引根據資料行的索引值在表或視圖中排序和儲存這些資料行。索引定義中包含叢集索引列。每個表只能有一個叢集索引，因為資料行本身只能按一個順序排序。

　　只有當表包含叢集索引時，表中的資料行才按排序次序儲存。如果表具有叢集索引，則該表稱為聚集表。如果表沒有叢集索引，則其資料行儲存在一個稱為堆的無序結構中。

　　每個表幾乎都對列定義叢集索引來實現下列功能：

　　1、可用於經常使用的查詢。

　　2、提供高度唯一性。

　　在建立叢集索引之前，應先瞭解資料是如何被訪問的。考慮對具有以下特點的查詢使用叢集索引：

　　使用運算子(如 BETWEEN、>、>=、< 和 <=)返回一系列值。

　　使用叢集索引找到包含第一個值的行後，便可以確保包含後續索引值的行物理相鄰。例如，如果某個查詢在一系列採購訂單號間檢索記 錄，PurchaseOrderNumber 列的叢集索引可快速定位包含起始採購訂單號的行，然後檢索表中所有連續的行，直到檢索到最後的採購訂單號。

　　返回大型結果集。

　　使用 JOIN 子句;一般情況下，使用該子句的是外鍵列。

　　使用 ORDER BY 或 GROUP BY 子句。

　　在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使資料庫引擎 不必對資料進行排序，因為這些行已經排序。這樣可以提高查詢效能。

　　叢集索引不適用於具有下列屬性的列：

　　頻繁更改的列

　　這將導致整行移動，因為資料庫引擎 必須按物理順序保留行中的資料值。這一點要特別注意，因為在大容量交易處理系統中資料通常是可變的。

　　寬鍵

　　寬鍵是若干列或若干大型列的組合。所有非叢集索引將叢集索引中的索引值用作尋找鍵。為同一表定義的任何非叢集索引都將增大許多，這是因為非叢集索引項包含聚集鍵，同時也包含為此非叢集索引定義的鍵列。　非叢集索引

　　非叢集索引具有獨立於資料行的結構。非叢集索引包含非叢集索引索引值，並且每個索引值項都有指向包含該索引值的資料行的指標。

　　從非叢集索引中的索引行指向資料行的指標稱為行定位器。行定位器的結構取決於資料頁是儲存在堆中還是聚集表中。對於堆，行定位器是指向行的指標。對於聚集表，行定位器是叢集索引鍵。

　　在 SQL Server 2005 中，可以向非叢集索引的分葉層級添加非鍵列以跳過現有的索引鍵限制(900 位元組和 16 鍵列)，並執行完整範圍內的索引查詢。

　　非叢集索引與叢集索引具有相同的 B 樹結構，它們之間的顯著差別在於以下兩點：

　　1、基礎資料表的資料行不按非聚集鍵的順序排序和儲存。

　　2、非叢集索引的葉層是由索引頁而不是由資料頁組成。

　　設計非叢集索引時需要注意資料庫的特徵:

　　更新要求較低但包含大量資料的資料庫或表可以從許多非叢集索引中獲益從而改善查詢效能。

　　決策支援系統應用程式和主要包含唯讀資料的資料庫可以從許多非叢集索引中獲益。查詢最佳化工具具有更多可供選擇的索引用來確定最快的存取方法，並且資料庫的低更新特徵意味著索引維護不會降低效能。

　　聯機交易處理應用程式和包含大量更新表的資料庫應避免使用過多的索引。此外，索引應該是窄的，即列越少越好。

　　一個表如果建有大量索引會影響 INSERT、UPDATE 和 DELETE 語句的效能，因為所有索引都必須隨表中資料的更改進行相應的調整。

　　唯一索引

　　唯一索引確保索引鍵不包含重複的值，因此，表或視圖中的每一行在某種程度上是唯一的。

　　叢集索引和非叢集索引都可以是唯一索引。

　　包含性列索引

　　一種非叢集索引，它擴充後不僅包含鍵列，還包含非鍵列。

　　索引涵蓋

　　指查詢中的SELECT與WHERE子句的所用列同時也屬於非叢集索引的情況。這樣就可以更快檢索資料，因為所有資訊都可以直接來自於索引頁，從而SQL Server可以避免訪問資料頁。加上獨立的索引檔案組，可以用最快速度訪問資料。

　　請看如下表示例：

　　A.建立簡單非叢集索引　以下樣本為 Purchasing.ProductVendor 表的 VendorID 列建立非叢集索引。　

 

USE AdventureWorks;  　　GO  　　CREATE INDEX IX_ProductVendor_VendorID  　　ON Purchasing.ProductVendor (VendorID);  　　GO

　　B. 建立簡單非聚集複合式索引

　　以下樣本為 Sales.SalesPerson 表的 SalesQuota 和 SalesYTD 列建立非聚集複合式索引。　

 

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD  　　ON Sales.SalesPerson (SalesQuota, SalesYTD);  　　GO

　　C. 建立唯一非叢集索引

　　以下樣本為 Production.UnitMeasure 表的 Name 列建立唯一的非叢集索引。該索引將強制插入 Name 列中的資料具有唯一性。　

 

USE AdventureWorks;  　　GO  　　CREATE UNIQUE INDEX AK_UnitMeasure_Name  　　ON Production.UnitMeasure(Name);  　　GO

　　無論何時對基礎資料執行插入、更新或刪除操作，SQL Server 2005 資料庫引擎都會自動維護索引。隨著時間的推移，這些修改可能會導致索引中的資訊分散在資料庫中(含有片段)。當索引包含的頁中的邏輯排序(基於索引值)與數 據檔案中的物理排序不匹配時，就存在片段。片段非常多的索引可能會降低查詢效能，導致應用程式響應緩慢。這個時候，我們需要做得就是重新組織和重建索 引。重建索引將刪除該索引並建立一個新索引。此過程中將刪除片段，通過使用指定的或現有的填滿因數設定壓縮頁來回收磁碟空間，並在連續頁中對索引行重 新排序(根據需要分配新頁)。這樣可以減少擷取所請求資料所需的頁讀取數，從而提高磁碟效能。

　　可以使用下列方法重建叢集索引和非叢集索引：

　　帶 REBUILD 子句的 ALTER INDEX。此語句將替換 DBCC DBREINDEX 語句。

　　帶 DROP_EXISTING 子句的 CREATE INDEX。

　　樣本如下：

　　A. 重建索引

以下樣本將重建單個索引。　　      USE AdventureWorks; 
　　GO 
　　ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee 
　　REBUILD; 
　　GO
　　B.重建表的所有索引並指定選項　　下面的樣本指定了 ALL 關鍵字。這將重建與表相關聯的所有索引。其中指定了三個選項。　      ALTER INDEX ALL ON Production.Product 
　　REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, 
　　STATISTICS_NORECOMPUTE = ON); 
　　GO
      2 Oracle 中的索引　　索引是Oracle使用的加速表中資料檢索的資料庫物件。　　下面的情況，可以考慮使用索引：　　1) 大表　　2) 主鍵(自動索引)　　3) 單鍵列(自動索引)　　4) 外鍵列(自動索引)　　5) 大表上WHERE子句常用的列　　6) ORDER BY 或者GROUP BY子句中使用的列。　　7) 至少返回表中20%行的查詢　　8) 不包含null值的列。　　Oracle中的索引包含有如下幾種類型：　　B*樹索引：這是Oracle中最常用的索引，它的構造類似於二叉樹，能根據鍵提供一行或一個行集的快速存取，通常只需要很少的讀操作就能找到正確的行。B*樹索引由兩列組成，第一列是ROWID, 它是行的位置;第二列是正被索引列的值。

　　圖:典型的B*樹索引布局

　　這個樹底層的塊稱為葉子節點(leaf node) 或(leaf block),其中分別包含各個索引鍵以及一個rowid(它是指向所索引的行)。葉子節點之上的內部塊稱為分支塊(branch block)，這些節點用於實現導航。例如，如果想在索引中找到值20，要從樹頂開始，找到左分支，我們檢查這個塊，並發現需要找到範圍"20..25" 的塊，這個塊將是葉子塊，其中會指示包含數20的行。索引的葉子節點實際上構成了一個雙向鏈表。一旦發現要從葉子節點中的那裡開始，執行值的有序掃描 (index range scan)就會很容易，我們就不必再在索引結構中導航：而只需根據葉子節點向前或向後掃描就可以了。

　　B*樹的特點之一是：所有葉子塊都應該在樹的同一層上，這一層稱之為索引的高度, 它說明所有從索引的根塊到葉子塊的遍曆都會訪問同樣數目的塊。也就是說，對於形如"SELECT INDEX_column FROM TABLE WHERE INXDEX_column =:X"的索引，要達到葉子塊來擷取第一行，不論使用的:X值是什麼，都會執行同樣數目的I/O,由此可見B*樹的B代表的是balanced，所謂 的"Height balanced"。大多數B*樹索引的高度都是2或3，即使索引中有數百萬行記錄也是如此，這說明，一般而言，在索引中找到一個鍵只需要2到3次I/O , 這確實不錯。

　　B*樹是一個極佳的通用索引機制，無論是大表還是小表都很適用，隨著底層表大小增長，擷取資料的效能僅會稍有惡化。

　　比如，我們為customers表建立一個常見的B*樹索引:　

 

CREATE INDEX IDX_Cus_City on customers(city)

　　B*樹索引有以下子類型：

　　複合索引

　　複合索引也是一種B*樹索引,它由多列組成。當我們擁有使用兩列或超過兩列的頻繁查詢時，就使用B*樹複合索引，而其所使用的兩列或多列在 where子句中and邏輯操作符串連。因為複合索引中列的順序很重要，所以確信以最有效索引能排列他們，可以參考用作列排序的下面的兩個準則 ：

　　1) 前置列應該是查詢中使用最頻繁的列。

　　2) 前置列應該是選擇最多的列，這意味著它比後面的列具有更高的基數。

　　複合索引在下列情況中具有優勢：

　　1)假定在WHERE子句中頻繁使用下面的條件：order_status_id = 1 和order_date = ‘dd-mon-yyyy’。如果為每一列建立一個索引，那麼為了搜尋列的值，兩個索引都要被讀取，但是如果為兩列都建立一個複合索引，那麼只有一個索引 被讀取，這樣無疑比兩個索引要求更少的I/O。

　　2) 使用前面例子中同樣的條件，如果建立一個複合索引，將更快地檢索行，因為你正在排除了所有order_status_id 不是1的行，從而減少了搜尋order_date的行數。

　　反向鍵索引

　　B*樹索引的另一個特點是能夠將索引鍵“反轉”。首先，你可以問問自己“為什麼想這麼做?” B*樹索引是為特定的環境、特定的問題而設計的。實現B*樹索引的目的是為了減少“右側”索引中對索引葉子塊的競爭，比如在一個Oracle RAC 環境中，某些列用一個序列值或時間戳記填充，這些列上建立的索引就屬於“右側”(right-hand-side)索引。

　　RAC 是一種Oracle 配置，其中多個執行個體可以裝載和開啟同一個資料庫。如果兩個執行個體需要同時修改同一個資料區塊，它們會通過一個硬體互連(interconnect)來回傳遞這 個塊來實現共用，互連是兩個(或多個)機器之間的一條專用網路連接。如果某個利用一個序列填充，這個列上有一個主鍵索引 ，那麼每個人插入新值時，都會視圖修改目前索引結構右側的左塊(見本文圖一，其中顯示出索引中較高的值都放在右側，而較低的值放在左側)。如果對用序列填 充的列上的索引進行修改，就會聚集在很少的一組葉子塊上。倘若將索引的鍵反轉，對索引進行插入時，就能在索引中的所有葉子鍵上分布開(不過這往往會使索引 不能得到充分地填充)。

　　反向鍵索引建立語句文法如下：　

 

CREATE INDEX index_name on table_name(column_name) REVERSE ;

　　降序索引

　　降序索引(descending index)是oracle 8i引入的，用以擴充B*樹索引的功能，它允許在索引中以降序(從大到小的順序)儲存一列。在oracle8i及以上版本中，DESC關鍵字確實會改變建立和使用索引的的方式。

　　我們可以這樣建立降序索引

 

CREATE INDEX IDX_jobs_title on hr.jobs (job_title DESC);  　　SET autotrace traceonly EXPLAIN;  　　SELECT * FROM hr.jobs  　　WHERE job_title Between ‘a‘ AND ‘ZZZZZZZZZZZ ‘; 　Execution Plan  　　----------------------------------------------------------  　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=33)  　　1 0 FILTER  　　2 1 TABLE ACCESS (BY INDEX ROWID) OF ‘JOBS‘ (Cost=1 Card=1 B  　　ytes=33)  　　3 2 INDEX (RANGE SCAN) OF ‘IDX_JOBS_TITLE‘ (NON-UNIQUE) (C  　　ost=2 Card=1)                 　　SQL> SELECT * from hr.jobs  　　2 WHERE job_title between ‘a‘ and ‘ZZZZZZZZZZZ ‘;  　　Execution Plan  　　----------------------------------------------------------  　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=33)  　　1 0 FILTER  　　2 1 TABLE ACCESS (FULL) OF ‘JOBS‘ (Cost=2 Card=1 Bytes=33)  　　SQL> DROP INDEX IDX_jobs_title ;  　　SQL> CREATE INDEX IDX_jobs_title on hr.jobs (job_title );  　　SQL> Select * FROM hr.jobs  　　2 Where job_title between ‘a‘ and ‘ZZZZZZZZZZZ ‘;  　　Execution Plan  　　----------------------------------------------------------  　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=33)  　　1 0 FILTER  　　2 1 TABLE ACCESS (FULL) OF ‘JOBS‘ (Cost=2 Card=1 Bytes=33)

　　位元影像索引

　　位元影像索引(bitmap index)是從Oracle7.3 版本開始引入的。目前Oracle企業版和個人版都支援位元影像索引，但標準版不支援。位元影像索引是為資料倉儲/線上分析查詢環境設計的，在此所有查詢要求的數 據在系統實現時根本不知道。位元影像索引特別不適用於OLTP 系統，如果系統中的資料會由多個並發會話頻繁地更新，這種系統也不適用位元影像索引。

　　位元影像索引是這樣一種結構，其中用一個索引鍵條目儲存指向多行的指標;這與B*樹結構不同，在B*樹結構中，索引鍵和表中的行存在著對應關係。在位元影像索引中，可能只有很少的索引條目，每個索引條目指向多行。而在傳統的B*樹中，一個索引條目就指向一行。

　　B*樹索引一般來講應當是選擇性的。與之相反，位元影像索引不應是選擇性的，一般來講它們應該“沒有選擇性“。如果有大量線上分析查詢，特別是查詢 以一種即席方式引用了多列或者會產生諸如COUNT 之類的彙總，在這樣的環境中，位元影像索引就特別有用 。位元影像索引使用 CREATE BITMAP INDEX index_name ON table_name(column_name1,column_name2) TABLESPACE tablespace_name命令文法建立。

SQL Server中的索引