索引是與表或視圖關聯的磁盤上結構，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲在一個結構(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 列創建非聚集索引。
| USEAdventureWorks; GO CREATEINDEXIX_ProductVendor_VendorID ONPurchasing.ProductVendor(VendorID); GO |
B. 創建簡單非聚集組合索引
以下示例為 Sales.SalesPerson 表的 SalesQuota 和 SalesYTD 列創建非聚集組合索引。
| CREATENONCLUSTEREDINDEXIX_SalesPerson_SalesQuota_SalesYTD ONSales.SalesPerson(SalesQuota,SalesYTD); GO |
C. 創建唯一非聚集索引
以下示例為 Production.UnitMeasure 表的 Name 列創建唯一的非聚集索引。該索引將強制插入 Name 列中的數據具有唯一性。
| USEAdventureWorks; GO CREATEUNIQUEINDEXAK_UnitMeasure_Name ONProduction.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*樹索引:
| CREATEINDEXIDX_Cus_Cityoncustomers(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)來回傳遞這 個塊來實現共享，互連是兩個(或多個)機器之間的一條專用網絡連接。如果某個利用一個序列填充，這個列上有一個主鍵索引 ，那么每個人插入新值時，都會視圖修改目前索引結構右側的左塊(見本文圖一，其中顯示出索引中較高的值都放在右側，而較低的值放在左側)。如果對用序列填 充的列上的索引進行修改，就會聚集在很少的一組葉子塊上。倘若將索引的鍵反轉，對索引進行插入時，就能在索引中的所有葉子鍵上分布開(不過這往往會使索引 不能得到充分地填充)。
反向鍵索引創建語句語法如下：
| CREATEINDEXindex_nameontable_name(column_name)REVERSE; |
降序索引
降序索引(descending index)是oracle 8i引入的，用以擴展B*樹索引的功能，它允許在索引中以降序(從大到小的順序)存儲一列。在oracle8i及以上版本中，DESC關鍵字確實會改變創建和使用索引的的方式。
我們可以這樣創建降序索引
| CREATEINDEXIDX_jobs_titleonhr.jobs(job_titleDESC); SETautotracetraceonlyEXPLAIN; SELECT*FROMhr.jobs WHEREjob_titleBetween'a'AND'ZZZZZZZZZZZ'; ExecutionPlan ------------------------------------------ 學習交流
熱門圖片
猜你喜歡的新聞
新聞熱點  2024-06-26 22:28:41
 2024-06-26 22:26:16
 2024-06-26 22:23:01
 2024-06-25 19:29:23
 2024-06-25 19:22:14
 2024-06-25 19:19:15
疑難解答 |
