一、前言

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.2、查詢注意事項(xiàng)

設(shè)計(jì)索引時(shí)，應(yīng)考慮以下查詢準(zhǔn)則：

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

3.3、列注意事項(xiàng)

設(shè)計(jì)索引時(shí)，應(yīng)考慮以下列準(zhǔn)則：

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

3.4、索引的特征

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

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

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

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

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

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

檢索數(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í)行計(jì)劃顯示了查詢優(yōu)化器使用 SORT 運(yùn)算符按 ORDER BY 子句指定的順序返回結(jié)果集。

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

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

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

總結(jié)

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

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

最新評論