SQL Server索引設計基礎知識詳解使用

2023-04-04 06:01:38

一、前言

索引設計不佳和缺少索引是提高資料庫和應用程式效能的主要障礙。設計高效的索引對於獲得良好的資料庫和應用程式效能極為重要。本索引設計指南包含關於索引體系結構的資訊，以及有助於設計有效索引以滿足應用程式需求的最佳做法。

二、索引設計背景知識

就像一本書，書本末尾有一個索引，可幫助快速查詢書籍內的資訊。索引是按順序排列的關鍵字列表，每個關鍵字旁邊是一組頁碼，這些頁碼指向可在其中找到每個關鍵字的頁面。

行儲存索引也一樣：它是按順序排列的值列表，每個值都有指向這些值所在的資料頁面的指標。索引本身儲存在頁上，稱為索引頁。

索引是與表或檢視關聯的磁碟上或記憶體中結構，可以加快從表或檢視中檢索行的速度。行儲存索引包含由表或檢視中的一列或多列生成的鍵。對於行儲存索引，這些鍵以樹結構（B+ 樹）儲存，使資料庫引擎可以快速高效地找到與鍵值關聯的一行或多行。

行儲存索引將邏輯組織的資料儲存為包含行和列的表，物理上以行資料格式（稱為行儲存1）儲存，或以名為列儲存的列資料格式儲存。

為資料庫及其工作負荷選擇正確的索引是一項需要在查詢速度與更新所需開銷之間取得平衡的複雜任務。如果基於磁碟的行儲存索引較窄，或者說索引關鍵字中只有很少的幾列，則需要的磁碟空間和維護開銷都較少。而另一方面，寬索引可覆蓋更多的查詢。您可能需要試驗若干不同的設計，才能找到最有效的索引。可以新增、修改和刪除索引而不影響資料庫架構或應用程式設計。因此，應試驗多個不同的索引而無需猶豫。

資料庫引擎的查詢優化器可在大多數情況下可靠地選擇最高效的索引。總體索引設計策略應為查詢優化器提供可供選擇的多個索引，並依賴查詢優化器做出正確的決定。這在多種情況下可減少分析時間並獲得良好的效能。

不要總是將索引的使用等同於良好的效能，或者將良好的效能等同於索引的高效使用。如果只要使用索引就能獲得最佳效能，那查詢優化器的工作就簡單了。但事實上，不正確的索引選擇並不能獲得最佳效能。因此，查詢優化器的任務是隻在索引或索引組合能提高效能時才選擇它，而在索引檢索有礙效能時則避免使用它。

行儲存是儲存關係表資料的傳統方法。 “行儲存”是指基礎資料儲存格式為堆、B+ 樹（聚集索引）或記憶體優化表的表。 “基於磁碟的行儲存”排除了記憶體優化表。

2.1、索引設計策略包括的任務

瞭解資料庫本身的特徵。例如，記憶體優化表和索引提供無閂鎖設計，尤其適用於資料庫是否是頻繁修改資料的聯機事務處理 (OLTP) 資料庫的應用場景。或者，列儲存索引尤其適用於典型的資料倉儲資料集。列儲存索引可以通過為常見資料倉儲查詢（如篩選、聚合、分組和星型聯接查詢）提供更快的效能，以轉變使用者的資料倉儲體驗。
瞭解最常用的查詢的特徵。例如，瞭解到最常用的查詢聯接兩個或多個表將有助於決定要使用的最佳索引型別。
瞭解查詢中使用的列的特徵。例如，某個索引對於含有整數資料型別同時還是唯一的或非空的列是理想索引。
確定哪些索引選項可在建立或維護索引時提高效能。例如，對某個現有大型表建立聚集索引將會受益於 ONLINE 索引選項。 ONLINE 選項允許在建立索引或重新生成索引時繼續對基礎資料執行並行活動。
確定索引的最佳儲存位置。非聚集索引可以與基礎表儲存在同一個檔案組中，也可以儲存在不同的檔案組中。索引的儲存位置可通過提高磁碟 I/O 效能來提高查詢效能。
使用動態管理檢視 (DMV)（例如 sys.dm_db_missing_index_details 和 sys.dm_db_missing_index_columns）識別缺失索引時，可能會在同一個表和列上獲得類似的索引變體。檢查表上的現有索引以及缺失索引建議，以防止建立重複索引。

三、常規索引設計

瞭解資料庫、查詢和資料列的特徵可以幫助設計出最佳索引。

3.1、資料庫注意事項

設計索引時，應考慮以下資料庫準則：

對錶編制大量索引會影響 INSERT、UPDATE、DELETE 和 MERGE 語句的效能，因為當表中的資料更改時，所有索引都須適當調整。避免對經常更新的表進行過多的索引，並且索引應保持較窄，就是說，列要儘可能少；使用多個索引可以提高更新少而資料量大的查詢的效能。大量索引可以提高不修改資料的查詢（例如 SELECT 語句）的效能，因為查詢優化器有更多的索引可供選擇，從而可以確定最快的存取方法。
對小表進行索引可能不會產生優化效果，因為查詢優化器在遍歷用於搜尋資料的索引時，花費的時間可能比執行簡單的表掃描還長。因此，小表的索引可能從來不用，但仍必須在表中的資料更改時進行維護。
檢視包含聚合、表聯接或聚合和聯接的組合時，檢視的索引可以顯著地提升效能。若要使查詢優化器使用檢視，並不一定非要在查詢中顯式參照該檢視。
可以選擇啟用自動索引優化。
查詢儲存有助於識別效能不佳的查詢，並提供查詢執行計劃的歷史記錄，其中記錄由優化器選擇的索引。

3.2、查詢注意事項

設計索引時，應考慮以下查詢準則：

為經常用於查詢中的謂詞和聯接條件的列建立非聚集索引。但是，應避免新增不必要的列。新增太多索引列可能對磁碟空間和索引維護效能產生負面影響。
涵蓋索引可以提高查詢效能，因為符合查詢要求的全部資料都存在於索引本身中。也就是說，只需要索引頁，而不需要表的資料頁或聚集索引來檢索所需資料，因此，減少了總體磁碟 I/O。
將插入或修改儘可能多的行的查詢寫入單個語句內，而不要使用多個查詢更新相同的行。僅使用一個語句，就可以利用優化的索引維護。
評估查詢型別以及如何在查詢中使用列。例如，在完全匹配查詢型別中使用的列就適合用於非聚集索引或聚集索引。

3.3、列注意事項

設計索引時，應考慮以下列準則：

對於聚集索引，請保持較短的索引鍵長度。另外，對唯一列或非空列建立聚集索引可以使聚集索引獲益。
無法指定 ntext、 text、 image、 varchar(max) 、 nvarchar(max) 和 varbinary(max) 資料型別的列為索引鍵列。不過， varchar(max) 、 nvarchar(max) 、 varbinary(max) 和 xml 資料型別的列可以作為非鍵索引列參與非聚集索引。
xml 資料型別的列只能在 XML 索引中用作鍵列。
檢查列的唯一性。在同一個列組合的唯一索引而不是非唯一索引提供了有關使索引更有用的查詢優化器的附加資訊。
在列中檢查資料分佈。通常情況下，為包含很少唯一值的列建立索引或在這樣的列上執行聯接將導致長時間執行的查詢。
考慮對具有定義完善的子集的列（例如，稀疏列、大部分值為 NULL 的列、含各類值的列以及含不同範圍的值的列）使用篩選索引。設計良好的篩選索引可以提高查詢效能，降低索引維護成本和儲存成本。
如果索引包含多個列，則應考慮列的順序。 WHERE 子句中使用的列應位於等於 (=) 、大於 (>) 、小於 (<) 或 BETWEEN 搜尋條件或參與聯接的列。其他列應該基於其非重複級別進行排序，就是說，從最不重複的列到最重複的列。
考慮對計算列進行索引。

3.4、索引的特徵

在確定某一索引適合某一查詢之後，可以選擇最適合具體情況的索引型別。索引包含以下特性：

聚集還是非聚集
唯一還是非唯一
單列還是多列
索引中的列是升序排序還是降序排序
非聚集索引是全表還是經過篩選
列儲存與行儲存
記憶體優化表的雜湊索引與非聚集索引

也可以通過SQL Server的設定選項自定義索引的初始儲存特徵以優化其效能或維護。而且，通過使用檔案組或分割區方案可以確定索引儲存位置來優化效能。

3.5、索引排序順序設計指南

定義索引時，請考慮索引鍵列的資料是按升序還是按降序儲存。CREATE INDEX、CREATE TABLE 和 ALTER TABLE 語句的語法在索引和約束中的各列上支援關鍵字 ASC（升序）和 DESC（降序）：

當參照表的查詢包含用以指定索引中鍵列的不同方向的 ORDER BY 子句時，指定鍵值儲存在該索引中的順序很有用。在這些情況下，索引就無需在查詢計劃中使用 SORT 運運算元。因此，使得查詢更有效。

檢索資料以滿足此條件需要將 Purchasing.PurchaseOrderDetail 表中的 RejectedQty 列按降序（由大到小）排序，並且將 ProductID 列按升序（由小到大）排序，比如：

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;
GO

此查詢的下列執行計劃顯示了查詢優化器使用 SORT 運運算元按 ORDER BY 子句指定的順序返回結果集。

如果使用與查詢的 ORDER BY 子句中的鍵列匹配的鍵列建立基於磁碟的行儲存索引，則無需在查詢計劃中使用 SORT 運運算元，從而使查詢計劃更有效。

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);
GO

再次執行查詢後，下列執行計劃顯示未使用 SORT 運運算元，而使用了新建立的非聚集索引。

總結

覆蓋索引是針對非聚集索引的指定，它直接解析一個或幾個類似的查詢結果，而不存取其基表，並且不會引發查詢。這意味著，由 SELECT 子句以及所有 WHERE 和 JOIN 引數返回的列都被索引所覆蓋。當與表本身的行和列相比，如果索引足夠窄，那麼執行查詢的 I/O 可能會少得多，這意味著它是總列的一個真正子集。如果選擇大型表的一小部分，請考慮覆蓋索引，其中的小部分是由一個固定謂詞定義，比如一個稀疏列，例如它只包含幾個非 NULL 值。

到此這篇關於SQL Server索引設計基礎知識詳解使用的文章就介紹到這了,更多相關SQL Server索引設計內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！