一、前言

索引設(shè)計不佳和缺少索引是提高數(shù)據(jù)庫和應(yīng)用程序性能的主要障礙。 設(shè)計高效的索引對于獲得良好的數(shù)據(jù)庫和應(yīng)用程序性能極為重要。 本索引設(shè)計指南包含關(guān)于索引體系結(jié)構(gòu)的信息，以及有助于設(shè)計有效索引以滿足應(yīng)用程序需求的最佳做法。

二、索引設(shè)計背景知識

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

行存儲索引也一樣：它是按順序排列的值列表，每個值都有指向這些值所在的數(shù)據(jù)頁面的指針。 索引本身存儲在頁上，稱為索引頁。

索引是與表或視圖關(guān)聯(lián)的磁盤上或內(nèi)存中結(jié)構(gòu)，可以加快從表或視圖中檢索行的速度。 行存儲索引包含由表或視圖中的一列或多列生成的鍵。 對于行存儲索引，這些鍵以樹結(jié)構(gòu)（B+ 樹）存儲，使數(shù)據(jù)庫引擎可以快速高效地找到與鍵值關(guān)聯(lián)的一行或多行。

行存儲索引將邏輯組織的數(shù)據(jù)存儲為包含行和列的表，物理上以行數(shù)據(jù)格式（稱為 行存儲1）存儲，或以名為列 存儲的列數(shù)據(jù)格式存儲。

為數(shù)據(jù)庫及其工作負荷選擇正確的索引是一項需要在查詢速度與更新所需開銷之間取得平衡的復(fù)雜任務(wù)。 如果基于磁盤的行存儲索引較窄，或者說索引關(guān)鍵字中只有很少的幾列，則需要的磁盤空間和維護開銷都較少。 而另一方面，寬索引可覆蓋更多的查詢。 您可能需要試驗若干不同的設(shè)計，才能找到最有效的索引。 可以添加、修改和刪除索引而不影響數(shù)據(jù)庫架構(gòu)或應(yīng)用程序設(shè)計。 因此，應(yīng)試驗多個不同的索引而無需猶豫。

數(shù)據(jù)庫引擎的查詢優(yōu)化器可在大多數(shù)情況下可靠地選擇最高效的索引。 總體索引設(shè)計策略應(yīng)為查詢優(yōu)化器提供可供選擇的多個索引，并依賴查詢優(yōu)化器做出正確的決定。 這在多種情況下可減少分析時間并獲得良好的性能。

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

行存儲是存儲關(guān)系表數(shù)據(jù)的傳統(tǒng)方法。 “行存儲”是指基礎(chǔ)數(shù)據(jù)存儲格式為堆、B+ 樹（聚集索引）或內(nèi)存優(yōu)化表的表。 “基于磁盤的行存儲”排除了內(nèi)存優(yōu)化表。

2.1、索引設(shè)計策略包括的任務(wù)

• 了解數(shù)據(jù)庫本身的特征。例如，內(nèi)存優(yōu)化表和索引提供無閂鎖設(shè)計，尤其適用于數(shù)據(jù)庫是否是頻繁修改數(shù)據(jù)的聯(lián)機事務(wù)處理 (OLTP) 數(shù)據(jù)庫的應(yīng)用場景。 或者， 列存儲索引尤其適用于典型的數(shù)據(jù)倉庫數(shù)據(jù)集。 列存儲索引可以通過為常見數(shù)據(jù)倉庫查詢（如篩選、聚合、分組和星型聯(lián)接查詢）提供更快的性能，以轉(zhuǎn)變用戶的數(shù)據(jù)倉庫體驗。
• 了解最常用的查詢的特征。 例如，了解到最常用的查詢聯(lián)接兩個或多個表將有助于決定要使用的最佳索引類型。
• 了解查詢中使用的列的特征。 例如，某個索引對于含有整數(shù)數(shù)據(jù)類型同時還是唯一的或非空的列是理想索引。
• 確定哪些索引選項可在創(chuàng)建或維護索引時提高性能。 例如，對某個現(xiàn)有大型表創(chuàng)建聚集索引將會受益于 ONLINE 索引選項。 ONLINE 選項允許在創(chuàng)建索引或重新生成索引時繼續(xù)對基礎(chǔ)數(shù)據(jù)執(zhí)行并發(fā)活動。
• 確定索引的最佳存儲位置。非聚集索引可以與基礎(chǔ)表存儲在同一個文件組中，也可以存儲在不同的文件組中。 索引的存儲位置可通過提高磁盤 I/O 性能來提高查詢性能。
• 使用動態(tài)管理視圖 (DMV)（例如 sys.dm_db_missing_index_details 和 sys.dm_db_missing_index_columns）識別缺失索引時，可能會在同一個表和列上獲得類似的索引變體。 檢查表上的現(xiàn)有索引以及缺失索引建議，以防止創(chuàng)建重復(fù)索引。

三、常規(guī)索引設(shè)計

了解數(shù)據(jù)庫、查詢和數(shù)據(jù)列的特征可以幫助設(shè)計出最佳索引。

3.1、數(shù)據(jù)庫注意事項

設(shè)計索引時，應(yīng)考慮以下數(shù)據(jù)庫準則：

• 對表編制大量索引會影響 INSERT、UPDATE、DELETE 和 MERGE 語句的性能，因為當(dāng)表中的數(shù)據(jù)更改時，所有索引都須適當(dāng)調(diào)整。避免對經(jīng)常更新的表進行過多的索引，并且索引應(yīng)保持較窄，就是說，列要盡可能少；使用多個索引可以提高更新少而數(shù)據(jù)量大的查詢的性能。 大量索引可以提高不修改數(shù)據(jù)的查詢（例如 SELECT 語句）的性能，因為查詢優(yōu)化器有更多的索引可供選擇，從而可以確定最快的訪問方法。
• 對小表進行索引可能不會產(chǎn)生優(yōu)化效果，因為查詢優(yōu)化器在遍歷用于搜索數(shù)據(jù)的索引時，花費的時間可能比執(zhí)行簡單的表掃描還長。 因此，小表的索引可能從來不用，但仍必須在表中的數(shù)據(jù)更改時進行維護。
• 視圖包含聚合、表聯(lián)接或聚合和聯(lián)接的組合時，視圖的索引可以顯著地提升性能。 若要使查詢優(yōu)化器使用視圖，并不一定非要在查詢中顯式引用該視圖。
• 可以選擇啟用自動索引優(yōu)化。
• 查詢存儲有助于識別性能不佳的查詢，并提供查詢執(zhí)行計劃的歷史記錄，其中記錄由優(yōu)化器選擇的索引。

3.2、查詢注意事項

設(shè)計索引時，應(yīng)考慮以下查詢準則：

• 為經(jīng)常用于查詢中的謂詞和聯(lián)接條件的列創(chuàng)建非聚集索引。 但是，應(yīng)避免添加不必要的列。 添加太多索引列可能對磁盤空間和索引維護性能產(chǎn)生負面影響。
• 涵蓋索引可以提高查詢性能，因為符合查詢要求的全部數(shù)據(jù)都存在于索引本身中。 也就是說，只需要索引頁，而不需要表的數(shù)據(jù)頁或聚集索引來檢索所需數(shù)據(jù)，因此，減少了總體磁盤 I/O。
• 將插入或修改盡可能多的行的查詢寫入單個語句內(nèi)，而不要使用多個查詢更新相同的行。 僅使用一個語句，就可以利用優(yōu)化的索引維護。
• 評估查詢類型以及如何在查詢中使用列。 例如，在完全匹配查詢類型中使用的列就適合用于非聚集索引或聚集索引。

3.3、列注意事項

設(shè)計索引時，應(yīng)考慮以下列準則：

• 對于聚集索引，請保持較短的索引鍵長度。 另外，對唯一列或非空列創(chuàng)建聚集索引可以使聚集索引獲益。
• 無法指定 ntext、 text、 image、 varchar(max) 、 nvarchar(max) 和 varbinary(max) 數(shù)據(jù)類型的列為索引鍵列。 不過， varchar(max) 、 nvarchar(max) 、 varbinary(max) 和 xml 數(shù)據(jù)類型的列可以作為非鍵索引列參與非聚集索引。
• xml 數(shù)據(jù)類型的列只能在 XML 索引中用作鍵列。
• 檢查列的唯一性。 在同一個列組合的唯一索引而不是非唯一索引提供了有關(guān)使索引更有用的查詢優(yōu)化器的附加信息。
• 在列中檢查數(shù)據(jù)分布。 通常情況下，為包含很少唯一值的列創(chuàng)建索引或在這樣的列上執(zhí)行聯(lián)接將導(dǎo)致長時間運行的查詢。
• 考慮對具有定義完善的子集的列（例如，稀疏列、大部分值為 NULL 的列、含各類值的列以及含不同范圍的值的列）使用篩選索引。 設(shè)計良好的篩選索引可以提高查詢性能，降低索引維護成本和存儲成本。
• 如果索引包含多個列，則應(yīng)考慮列的順序。 WHERE 子句中使用的列應(yīng)位于等于 (=) 、大于 (>) 、小于 (<) 或 BETWEEN 搜索條件或參與聯(lián)接的列。 其他列應(yīng)該基于其非重復(fù)級別進行排序，就是說，從最不重復(fù)的列到最重復(fù)的列。
• 考慮對計算列進行索引。

3.4、索引的特征

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

• 聚集還是非聚集
• 唯一還是非唯一
• 單列還是多列
• 索引中的列是升序排序還是降序排序
• 非聚集索引是全表還是經(jīng)過篩選
• 列存儲與行存儲
• 內(nèi)存優(yōu)化表的哈希索引與非聚集索引

也可以通過SQL Server的設(shè)置選項自定義索引的初始存儲特征以優(yōu)化其性能或維護。 而且，通過使用文件組或分區(qū)方案可以確定索引存儲位置來優(yōu)化性能。

3.5、索引排序順序設(shè)計指南

定義索引時，請考慮索引鍵列的數(shù)據(jù)是按升序還是按降序存儲。CREATE INDEX、CREATE TABLE 和 ALTER TABLE 語句的語法在索引和約束中的各列上支持關(guān)鍵字 ASC（升序）和 DESC（降序）：

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

檢索數(shù)據(jù)以滿足此條件需要將 Purchasing.PurchaseOrderDetail 表中的 RejectedQty 列按降序（由大到小）排序，并且將 ProductID 列按升序（由小到大）排序，比如：

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

此查詢的下列執(zhí)行計劃顯示了查詢優(yōu)化器使用 SORT 運算符按 ORDER BY 子句指定的順序返回結(jié)果集。

如果使用與查詢的 ORDER BY 子句中的鍵列匹配的鍵列創(chuàng)建基于磁盤的行存儲索引，則無需在查詢計劃中使用 SORT 運算符，從而使查詢計劃更有效。

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

再次執(zhí)行查詢后，下列執(zhí)行計劃顯示未使用 SORT 運算符，而使用了新創(chuàng)建的非聚集索引。

總結(jié)

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

到此這篇關(guān)于SQL Server索引設(shè)計基礎(chǔ)知識詳解使用的文章就介紹到這了,更多相關(guān)SQL Server索引設(shè)計內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論