課件第8章 創(chuàng)建數據庫索引

1、LOGO 本章學習目標：本章學習目標：vSQL Server 2016及以前版本中提供及以前版本中提供的與索引相關的功能的與索引相關的功能v如何借助分區(qū)表和索引來使數據庫更加便如何借助分區(qū)表和索引來使數據庫更加便于管理和擴展于管理和擴展v實現分區(qū)表和索引實現分區(qū)表和索引v維護和調優(yōu)索引維護和調優(yōu)索引8.1 SQL Server 2016中新增的索中新增的索引引列存儲索引列存儲索引(columnstore index)是是SQL Server 2012中首次引入的索引類中首次引入的索引類型，是基于列的非聚集索引，用于為涉及型，是基于列的非聚集索引，用于為涉及大量數據的工作負載提高查詢性能，通常大

2、量數據的工作負載提高查詢性能，通常在數據倉庫事實表中使用。在數據倉庫事實表中使用。SQL Server 2016為列存儲索引引入了為列存儲索引引入了兩種新功能：聚集列存儲索引功能和更新兩種新功能：聚集列存儲索引功能和更新現有聚集列存儲索引功能?，F有聚集列存儲索引功能。SQL Server 2012引入列存儲索引時，引入列存儲索引時，只能創(chuàng)建非聚集的列存儲索引，并且在創(chuàng)只能創(chuàng)建非聚集的列存儲索引，并且在創(chuàng)建之后就不能更新。在建之后就不能更新。在SQL Server 2016中，可以創(chuàng)建一個聚集列存儲索引中，可以創(chuàng)建一個聚集列存儲索引(就是表就是表)，并且這個表，并且這個表/列存儲索引之后是列存儲

3、索引之后是可以更新的。可以更新的。SQL Server 2016還針對聯(lián)機索引做了還針對聯(lián)機索引做了重大改進。在重大改進。在SQL Server 2016中，現中，現在可以重構單個分區(qū)，在以前版本的在可以重構單個分區(qū)，在以前版本的SQL Server中是不允許這么做的。對于有非常中是不允許這么做的。對于有非常大的表并且需要把這些表的維護工作分散大的表并且需要把這些表的維護工作分散到幾天中的公司，這是一種非常重要的改到幾天中的公司，這是一種非常重要的改進。進。8.1 SQL Server 2016中新增的索中新增的索引引SQL Server 2016中的另外一個新功能中的另外一個新功能是能夠在是

4、能夠在SHOWPLAN查詢計劃中顯示列查詢計劃中顯示列存儲索引。存儲索引。SHOWPLAN的的EstimatedExecutionMode和和ActualExecutionMode屬性有兩個可屬性有兩個可能的值：能的值：Batch或或Row。其。其Storage屬屬性也有兩個可能的值：性也有兩個可能的值：RowStore和和ColumnStore。8.2 索引和分區(qū)表索引和分區(qū)表創(chuàng)建索引以及如何結合使用索引和分區(qū)表創(chuàng)建索引以及如何結合使用索引和分區(qū)表來管理大型表和進行擴展。來管理大型表和進行擴展。8.2.1 理解索引理解索引為了實現良好的索引設計，首先需要很好為了實現良好的索引設計，首先需要很

5、好地理解索引提供的優(yōu)點。地理解索引提供的優(yōu)點。SQL Server中的索引與書中的目錄具有中的索引與書中的目錄具有相同的功能，讓相同的功能，讓SQL Server能夠盡快定能夠盡快定位和檢索查詢中請求的數據。位和檢索查詢中請求的數據?？紤]一本考慮一本500頁的書，其中有大量章節(jié)，頁的書，其中有大量章節(jié)，但是沒有目錄。要找到書中的某一小節(jié)，但是沒有目錄。要找到書中的某一小節(jié)，讀者需要翻頁并依次瀏覽每一頁，直至找讀者需要翻頁并依次瀏覽每一頁，直至找到感興趣的節(jié)。到感興趣的節(jié)。這個類比也適用于這個類比也適用于SQL Server數據庫表。數據庫表。如果沒有適當的索引，如果沒有適當的索引，SQL Se

6、rver就必就必須掃描包含表中數據的所有數據頁。對于須掃描包含表中數據的所有數據頁。對于包含大量數據的表，這會是非常耗費時間包含大量數據的表，這會是非常耗費時間的、資源密集型的操作。這就是索引如此的、資源密集型的操作。這就是索引如此重要的原因所在。重要的原因所在。根據索引存儲數據的方式以及索引的內部根據索引存儲數據的方式以及索引的內部結構、作用和定義方式，可以采用多種方結構、作用和定義方式，可以采用多種方式分類索引。下面將簡要描述這些索引類式分類索引。下面將簡要描述這些索引類型。型。8.2.1 理解索引理解索引1. 基于行的索引基于行的索引基于行的基于行的(或行存儲或行存儲)索引是傳統(tǒng)的索引，

7、索引是傳統(tǒng)的索引，將數據存儲為數據頁中的行。這些索引包將數據存儲為數據頁中的行。這些索引包括聚集索引和非聚集索引。括聚集索引和非聚集索引。1) 聚集索引聚集索引聚集索引基于鍵列存儲和排序表的葉級數聚集索引基于鍵列存儲和排序表的葉級數據。實際的存儲頁鏈接在一起，所以可以據。實際的存儲頁鏈接在一起，所以可以按照聚集鍵的順序依次讀取表，導致的按照聚集鍵的順序依次讀取表，導致的I/O開銷極小。每個表只可以有一個聚集開銷極小。每個表只可以有一個聚集索引，因為只可以按照一種順序排序數據，索引，因為只可以按照一種順序排序數據，而且聚集索引代表了實際的表數據。而且聚集索引代表了實際的表數據。讓實際數據聚集起來

8、，有助于提高順序讀讓實際數據聚集起來，有助于提高順序讀取的性能。一個數據頁可能包含已經排序取的性能。一個數據頁可能包含已經排序好的幾行到許多行實際數據。好的幾行到許多行實際數據。所有非聚集索引中都會包含聚集鍵字段，所有非聚集索引中都會包含聚集鍵字段，以便引用回聚集索引的葉級行。如果選擇以便引用回聚集索引的葉級行。如果選擇了一個大聚集索引鍵，這會影響非聚集索了一個大聚集索引鍵，這會影響非聚集索引的大小。引的大小。當表定義包括主鍵約束時，就會默認創(chuàng)建當表定義包括主鍵約束時，就會默認創(chuàng)建聚集索引。好的聚集索引與好的主鍵有一聚集索引。好的聚集索引與好的主鍵有一些相同的屬性：字段不會改變，并且總是些相同

9、的屬性：字段不會改變，并且總是遞增。添加新記錄時，這種類型的聚集索遞增。添加新記錄時，這種類型的聚集索引鍵有助于減少頁拆分。引鍵有助于減少頁拆分。8.2.1 理解索引理解索引2) 非聚集索引非聚集索引非聚集索引包含索引鍵值和行定位器，行非聚集索引包含索引鍵值和行定位器，行定位器指向實際的數據行。如果沒有聚集定位器指向實際的數據行。如果沒有聚集索引，行定位器就是實際數據行的索引，行定位器就是實際數據行的RowID指針。如果存在聚集索引，行定位器就是指針。如果存在聚集索引，行定位器就是該行的聚集索引鍵。該行的聚集索引鍵。可以優(yōu)化非聚集索引以滿足更多的查詢，可以優(yōu)化非聚集索引以滿足更多的查詢，降低查

10、詢響應時間，減少索引大小。下面降低查詢響應時間，減少索引大小。下面將描述最重要的兩種經過優(yōu)化的非聚集索將描述最重要的兩種經過優(yōu)化的非聚集索引。引。3) 覆蓋索引覆蓋索引覆蓋索引是滿足覆蓋索引是滿足(覆蓋覆蓋)特定查詢的所有字特定查詢的所有字段需求的索引。通過在段需求的索引。通過在CREATE INDEX語句中使用語句中使用INCLUDE短語，非聚集索引短語，非聚集索引在葉級可以包含非鍵列，以幫助覆蓋查詢。在葉級可以包含非鍵列，以幫助覆蓋查詢。這些索引類型可以改進查詢性能，并減少這些索引類型可以改進查詢性能，并減少I/O操作，因為滿足查詢所需要的列作為操作，因為滿足查詢所需要的列作為鍵列或非鍵列

11、包括在索引自身中，不需要鍵列或非鍵列包括在索引自身中，不需要再讀取實際的數據行。再讀取實際的數據行。INCLUDE短語使非聚集索引更加靈活，短語使非聚集索引更加靈活，因為鍵中包含的字段可以具有鍵中原本不因為鍵中包含的字段可以具有鍵中原本不允許的數據類型，并且在計算索引大小或允許的數據類型，并且在計算索引大小或鍵列的數量時，也不會考慮它們。鍵列的數量時，也不會考慮它們。8.2.1 理解索引理解索引4) 過濾索引過濾索引過濾索引使用過濾索引使用WHERE子句指示將要索引子句指示將要索引哪些行。因為只是索引表中的部分行，所哪些行。因為只是索引表中的部分行，所以可以創(chuàng)建較小的數據集存儲到索引中。以可以

12、創(chuàng)建較小的數據集存儲到索引中。過濾索引總是非聚集索引，因為它們選擇過濾索引總是非聚集索引，因為它們選擇總記錄集的一個子集，而總記錄集用表上總記錄集的一個子集，而總記錄集用表上的聚集索引表示。如果查詢的的聚集索引表示。如果查詢的WHERE子子句可用過濾索引的句可用過濾索引的WHERE子句中的行滿子句中的行滿足，那么就會在查詢計劃中選擇過濾索引。足，那么就會在查詢計劃中選擇過濾索引。8.2.1 理解索引理解索引2. 基于列的索引基于列的索引基于列的索引是在單獨列上創(chuàng)建的索引。基于列的索引是在單獨列上創(chuàng)建的索引?；诹械乃饕袃煞N主要類型：列存儲索基于列的索引有兩種主要類型：列存儲索引引(SQL S

13、erver 2012中首次引入中首次引入)和和XML索引索引(提供了提供了XML列中的值的索引列中的值的索引)。在在SQL Server 2012中，這些索引是不中，這些索引是不可更新的可更新的要在索引中添加值，就需要要在索引中添加值，就需要重新構建索引。重新構建索引。非聚集列存儲索引具有以下限制：非聚集列存儲索引具有以下限制：可以索引表中列的子集可以索引表中列的子集(聚集表或堆表聚集表或堆表)。只能通過重新構建索引來更新。只能通過重新構建索引來更新?？梢耘c表上的其他索引合并?？梢耘c表上的其他索引合并。需要額外的空間，以便在索引中獨立于行需要額外的空間，以便在索引中獨立于行值存儲列的副本。值存

14、儲列的副本。8.2.1 理解索引理解索引在在SQL Server 2016中，聚集列存儲索中，聚集列存儲索引是可以更新的，但是具有如下限制：引是可以更新的，但是具有如下限制：聚集列存儲索引不能有任何非聚集索引，聚集列存儲索引不能有任何非聚集索引，它是表上唯一的索引。它是表上唯一的索引。存儲為聚集列存儲索引的表不能用在復制存儲為聚集列存儲索引的表不能用在復制中。中。存儲為聚集列存儲索引的表不能使用變更存儲為聚集列存儲索引的表不能使用變更數據捕捉，這是數據捕捉，這是SQL Server功能。功能。存儲為聚集列存儲索引的表不能關聯(lián)任何存儲為聚集列存儲索引的表不能關聯(lián)任何FILESTREAM列。列。聚

15、集列存儲索引功能只能在聚集列存儲索引功能只能在SQL Server 2016的企業(yè)版、開發(fā)版和評估版中使用。的企業(yè)版、開發(fā)版和評估版中使用。在聚集列存儲索引上不能創(chuàng)建主鍵，也不在聚集列存儲索引上不能創(chuàng)建主鍵，也不能創(chuàng)建引用完整性約束。能創(chuàng)建引用完整性約束。8.2.1 理解索引理解索引3. 內存優(yōu)化索引內存優(yōu)化索引SQL Server 2016創(chuàng)建了新的索引來支創(chuàng)建了新的索引來支持內存優(yōu)化表。散列索引保存在內存中，持內存優(yōu)化表。散列索引保存在內存中，用于訪問內存優(yōu)化用于訪問內存優(yōu)化(Hekaton)表中的數據。表中的數據。所需要的內存量與散列索引使用的桶計數所需要的內存量與散列索引使用的桶計數有

16、關。有關。內存優(yōu)化的非聚集索引將對從內存優(yōu)化表內存優(yōu)化的非聚集索引將對從內存優(yōu)化表中訪問的數據進行排序。這些索引只能使中訪問的數據進行排序。這些索引只能使用用CREATE TABLE和和CREATE INDEX語句創(chuàng)建，并且是為范圍排序掃描語句創(chuàng)建，并且是為范圍排序掃描(按照排按照排序順序讀取大量數據序順序讀取大量數據)創(chuàng)建的。當內存表加創(chuàng)建的。當內存表加載到內存中時會創(chuàng)建這些索引，它們不會載到內存中時會創(chuàng)建這些索引，它們不會被持久化到物理表。被持久化到物理表。8.2.1 理解索引理解索引4. 其他索引類型其他索引類型SQL Server中還有其他一些類型的索引，中還有其他一些類型的索引，用于

17、支持具體的開發(fā)主題。用于支持具體的開發(fā)主題。1) XML索引索引XML索引是一種特殊的索引類型，用于索索引是一種特殊的索引類型，用于索引存儲在引存儲在XML列中的值。這些索引拆分列中的值。這些索引拆分XML列并存儲詳細信息，供在列并存儲詳細信息，供在SQL查詢中查詢中快速檢索?？焖贆z索。XML列可能很大，在運行時將列可能很大，在運行時將XML數據拆分成可讀的數據元素會減緩大數據拆分成可讀的數據元素會減緩大型型XML查詢。通過使用查詢。通過使用XML索引，這種拆索引，這種拆分是提前完成的，在運行時讀取很快。分是提前完成的，在運行時讀取很快。2) 全文索引全文索引創(chuàng)建全文索引是為了支持創(chuàng)建全文索引

18、是為了支持SQL Server中中的全文搜索功能。全文索引讓用戶和應用的全文搜索功能。全文索引讓用戶和應用程序能夠在程序能夠在SQL Server表中查詢基于字表中查詢基于字符的數據。必須先在表上創(chuàng)建全文索引，符的數據。必須先在表上創(chuàng)建全文索引，然后才能在全文搜索中包含它。然后才能在全文搜索中包含它。全文搜索是全文搜索是SQL Server中的一項可選功中的一項可選功能，在使用前必須先打開。能，在使用前必須先打開。8.2.1 理解索引理解索引3) 空間索引空間索引空間索引對空間數據列進行索引。空間數空間索引對空間數據列進行索引?？臻g數據列包含據列包含GEOMETRY或或GEOGRAPHY類型的

19、值?？臻g索引支持處理空間數據的類型的值?？臻g索引支持處理空間數據的操作，如內置的地理方法操作，如內置的地理方法(STContains()、STDistance()、STEquals()、STIntersects()等等)。為了讓優(yōu)化器能夠。為了讓優(yōu)化器能夠選擇查詢，必須在查詢的選擇查詢，必須在查詢的JOIN或或WHERE子句中使用這些方法。子句中使用這些方法。8.2.1 理解索引理解索引5. SQL Server使用索引的方式使用索引的方式為了實現優(yōu)秀的索引設計，很重要的一點為了實現優(yōu)秀的索引設計，很重要的一點是深入了解是深入了解SQL Server使用索引的方式。使用索引的方式。在在SQL

20、Server中，查詢優(yōu)化器組件確定中，查詢優(yōu)化器組件確定用于執(zhí)行查詢的最符合成本效益的選項。用于執(zhí)行查詢的最符合成本效益的選項。查詢優(yōu)化器評估大量查詢執(zhí)行計劃并選擇查詢優(yōu)化器評估大量查詢執(zhí)行計劃并選擇具有最低成本的執(zhí)行計劃。具有最低成本的執(zhí)行計劃。8.2.2 創(chuàng)建索引創(chuàng)建索引使用使用T-SQL命令手動創(chuàng)建索引：命令手動創(chuàng)建索引：(1) 打開打開SQL Server Management Studio并連接到并連接到SQL Server實例。實例。(2) 確保安裝了確保安裝了http:/ 打開新的查詢窗口并遵循如下列表中打開新的查詢窗口并遵循如下列表中提供的一種示例查詢語法：提供的一種示例查詢語

21、法：首先創(chuàng)建一個表的副本。在本例中，使用首先創(chuàng)建一個表的副本。在本例中，使用下面的腳本創(chuàng)建下面的腳本創(chuàng)建HumanResources.Employee表的一表的一個副本，此腳本將刪除這些例子中不需要個副本，此腳本將刪除這些例子中不需要的字段：的字段：8.2.2 創(chuàng)建索引創(chuàng)建索引首先創(chuàng)建一個表的副本。在本例中，使用首先創(chuàng)建一個表的副本。在本例中，使用下面的腳本創(chuàng)建下面的腳本創(chuàng)建HumanResources.Employee表的一表的一個副本，此腳本將刪除這些例子中不需要個副本，此腳本將刪除這些例子中不需要的字段：的字段：8.2.2 創(chuàng)建索引創(chuàng)建索引要在剛才創(chuàng)建的表上創(chuàng)建聚集索引，可使要在剛才創(chuàng)建

22、的表上創(chuàng)建聚集索引，可使用如下所示的用如下所示的CREATE CLUSTERED INDEX T-SQL命令：命令：要創(chuàng)建非聚集索引，可使用要創(chuàng)建非聚集索引，可使用T-SQL命令命令CREATE NONCLUSTERED INDEX。NONCLUSTERED是默認索引類型，可是默認索引類型，可以省略：以省略：8.2.2 創(chuàng)建索引創(chuàng)建索引要創(chuàng)建覆蓋索引，可以使用要創(chuàng)建覆蓋索引，可以使用T-SQL命令命令CREATE NONCLUSTERED INDEX以以及及INCLUDE關鍵字，如下所示：關鍵字，如下所示：要創(chuàng)建過濾索引，可以使用要創(chuàng)建過濾索引，可以使用T-SQL命令命令CREATE NONC

23、LUSTERED INDEX以以及及WHERE關鍵字，如下所示：關鍵字，如下所示：8.2.2 創(chuàng)建索引創(chuàng)建索引要創(chuàng)建聚集列存儲索引，首先應該刪除表要創(chuàng)建聚集列存儲索引，首先應該刪除表上的其他所有索引。然后，像下面這樣使上的其他所有索引。然后，像下面這樣使用用T-SQL命令命令CREATE COLUMNSTORE INDEX：通過刪除聚集索引，將該聚集列存儲索引通過刪除聚集索引，將該聚集列存儲索引轉換回一個行存儲表：轉換回一個行存儲表：要創(chuàng)建非聚集列存儲索引，需要使用要創(chuàng)建非聚集列存儲索引，需要使用T-SQL命令命令CREATE COLUMNSTORE INDEX，如下所示：，如下所示：8.2.

24、3 使用分區(qū)表和索引使用分區(qū)表和索引分區(qū)表可以幫助優(yōu)化系統(tǒng)。分區(qū)表是將單分區(qū)表可以幫助優(yōu)化系統(tǒng)。分區(qū)表是將單張表分布到多個單元上的一種方式，其中張表分布到多個單元上的一種方式，其中每個單元都可以建立在獨立的文件組中。每個單元都可以建立在獨立的文件組中。恰當使用的話，分區(qū)和索引可以幫助管理恰當使用的話，分區(qū)和索引可以幫助管理大量數據，并以更快的速度將信息返回給大量數據，并以更快的速度將信息返回給查詢。查詢。創(chuàng)建分區(qū)是為了幫助把表分解為更小的單創(chuàng)建分區(qū)是為了幫助把表分解為更小的單元，并給元，并給SQL查詢引擎提供更好的技術來查詢引擎提供更好的技術來優(yōu)化查詢，包括并行和分區(qū)清除。索引和優(yōu)化查詢，包括

25、并行和分區(qū)清除。索引和分區(qū)進一步幫助了查詢引擎，通過添加一分區(qū)進一步幫助了查詢引擎，通過添加一層數據訪問來幫助標識和定位滿足查詢所層數據訪問來幫助標識和定位滿足查詢所需要的行。需要的行。每個分區(qū)不只包含聚集索引的鍵字段，還每個分區(qū)不只包含聚集索引的鍵字段，還在每行中包含分區(qū)鍵。分區(qū)內的行根據分在每行中包含分區(qū)鍵。分區(qū)內的行根據分區(qū)鍵物理存儲在一起。在分區(qū)表上構建的區(qū)鍵物理存儲在一起。在分區(qū)表上構建的索引可以使用與分區(qū)表相同的分區(qū)函數索引可以使用與分區(qū)表相同的分區(qū)函數/模模式進行分區(qū)，也可以使用自己的分區(qū)函數式進行分區(qū)，也可以使用自己的分區(qū)函數和模式，還可以不分區(qū)。當非聚集索引使和模式，還可以不

26、分區(qū)。當非聚集索引使用與基礎表相同的分區(qū)鍵用與基礎表相同的分區(qū)鍵(聚集索引鍵聚集索引鍵)分分區(qū)時，就稱為分區(qū)對齊索引。區(qū)時，就稱為分區(qū)對齊索引。添加其他非分區(qū)對齊的索引可以提高性能。添加其他非分區(qū)對齊的索引可以提高性能。如果許多查詢中都沒有分區(qū)鍵，查詢性能如果許多查詢中都沒有分區(qū)鍵，查詢性能可能會降低。此時，創(chuàng)建非聚集、非分區(qū)可能會降低。此時，創(chuàng)建非聚集、非分區(qū)對齊的索引應該可以提高性能。對齊的索引應該可以提高性能。8.3 索引維護索引維護每次在每次在SQL Server表中插入、更新或刪表中插入、更新或刪除數據時，都會相應地更新索引。更新索除數據時，都會相應地更新索引。更新索引時，葉級頁的數

27、據將被移動，以支持索引時，葉級頁的數據將被移動，以支持索引的排序順序，這可能導致索引碎片。引的排序順序，這可能導致索引碎片。在行存儲索引中，被刪除或修改的行可以在行存儲索引中，被刪除或修改的行可以重用空的空間，但是頁分割可能導致出現重用空的空間，但是頁分割可能導致出現碎片。在列存儲索引中，應該定期重新構碎片。在列存儲索引中，應該定期重新構建索引以回收刪除或更新操作導致的空的建索引以回收刪除或更新操作導致的空的空間，以及更新所有非聚集列存儲索引，空間，以及更新所有非聚集列存儲索引，這是非常重要的。這是非常重要的。隨著時間的推移，數據頁中的數據分布可隨著時間的推移，數據頁中的數據分布可能會變得不再

28、平衡。一些數據頁中數據的能會變得不再平衡。一些數據頁中數據的填充可能非常稀疏，而其他數據頁則被填填充可能非常稀疏，而其他數據頁則被填滿。過多稀疏填充的數據頁會帶來性能問滿。過多稀疏填充的數據頁會帶來性能問題，因為需要讀取更多的數據頁來檢索請題，因為需要讀取更多的數據頁來檢索請求的數據。求的數據。另一方面，接近填滿的頁可能會在插入或另一方面，接近填滿的頁可能會在插入或更新數據時產生頁分割。當發(fā)生頁分割時，更新數據時產生頁分割。當發(fā)生頁分割時，會將大約一半的數據移動到新創(chuàng)建的數據會將大約一半的數據移動到新創(chuàng)建的數據頁中。這種經常執(zhí)行的重新組織操作會消頁中。這種經常執(zhí)行的重新組織操作會消耗資源并創(chuàng)建

29、數據頁碎片。耗資源并創(chuàng)建數據頁碎片。我們的目標是將盡可能多的數據存儲到最我們的目標是將盡可能多的數據存儲到最少量的數據頁中，同時為數據增長留出一少量的數據頁中，同時為數據增長留出一定的空間，從而防止過多的頁分割?？梢远ǖ目臻g，從而防止過多的頁分割。可以通過微調索引填充因子來實現這種微妙的通過微調索引填充因子來實現這種微妙的平衡。平衡。8.3.1 監(jiān)控索引碎片監(jiān)控索引碎片在在SQL Server 2016中可以使用提供的中可以使用提供的Data Management Views(數據管理數據管理視圖，視圖，DMV)來監(jiān)控索引碎片來監(jiān)控索引碎片(包括列存儲包括列存儲索引索引)。最有用的。最有用的D

30、MV之一是之一是sys.dm_db_index_physical_stats，它提供每個索引的平均碎片信息。它提供每個索引的平均碎片信息。例如，可以按照如下所示查詢例如，可以按照如下所示查詢sys.dm_db_index_physical_stats DMV：8.3.1 監(jiān)控索引碎片監(jiān)控索引碎片圖圖8-1顯示了此查詢的結果。顯示了此查詢的結果。8.3.1 監(jiān)控索引碎片監(jiān)控索引碎片在此在此DMV的執(zhí)行結果中可以觀察到具有較的執(zhí)行結果中可以觀察到具有較多碎片的索引。具有較高碎片百分比的索多碎片的索引。具有較高碎片百分比的索引必須進行碎片整理以避免產生性能問題。引必須進行碎片整理以避免產生性能問題。

31、根據碎片的類型根據碎片的類型(內部或外部內部或外部)，SQL Server會以無效率的方式存儲和訪問碎片會以無效率的方式存儲和訪問碎片較多的索引。外部碎片意味著沒有以邏輯較多的索引。外部碎片意味著沒有以邏輯順序存儲數據頁。內部碎片意味著頁存儲順序存儲數據頁。內部碎片意味著頁存儲的數據量少于可以容納的數據量。這兩種的數據量少于可以容納的數據量。這兩種碎片都會導致延長查詢時間。進一步的碎片都會導致延長查詢時間。進一步的DMV查詢可以標識需要整理碎片的具體索查詢可以標識需要整理碎片的具體索引。引。SQL Server 2016中的一個新功能允許中的一個新功能允許清理分區(qū)索引內的單獨分區(qū)，并對其進行清

32、理分區(qū)索引內的單獨分區(qū)，并對其進行碎片整理，這在幫助碎片整理，這在幫助DBA的同時，只對性的同時，只對性能產生了最小的影響，并且降低了維護活能產生了最小的影響，并且降低了維護活動的停機時間。動的停機時間。8.3.2 清理索引清理索引索引清理應該始終是所有數據庫維護操作索引清理應該始終是所有數據庫維護操作的一部分。根據索引由于數據改變而產生的一部分。根據索引由于數據改變而產生的碎片，需要定期執(zhí)行這些索引清理任務。的碎片，需要定期執(zhí)行這些索引清理任務。如果索引包含過多的碎片，就可以通過重如果索引包含過多的碎片，就可以通過重新組織或重新構建索引來對索引進行碎片新組織或重新構建索引來對索引進行碎片清理

33、。清理。重新組織索引：重新組織索引：重新排序和壓縮葉級頁重新排序和壓縮葉級頁聯(lián)機執(zhí)行索引重新排序聯(lián)機執(zhí)行索引重新排序(不使用任何長期不使用任何長期鎖鎖)適合于具有較低碎片百分比的索引適合于具有較低碎片百分比的索引重新構建索引：重新構建索引：重新創(chuàng)建新索引，然后刪除原索引重新創(chuàng)建新索引，然后刪除原索引回收磁盤空間回收磁盤空間重新排序和壓縮鄰近頁中的行重新排序和壓縮鄰近頁中的行使用企業(yè)版中提供的聯(lián)機索引重新構建使用企業(yè)版中提供的聯(lián)機索引重新構建選項選項更加適合于具有較多碎片的索引更加適合于具有較多碎片的索引8.3.2 清理索引清理索引表表8-2列出了列出了DimCustomer表的索引操表的索引操

34、作的一般性語法。作的一般性語法。8.4 使用索引改進查詢性能使用索引改進查詢性能SQL Server 2016包含一些包含一些DMV，可以，可以用于微調查詢。用于微調查詢。DMV可用于顯示特定查詢可用于顯示特定查詢的表面執(zhí)行統(tǒng)計數據，如查詢執(zhí)行的次數、的表面執(zhí)行統(tǒng)計數據，如查詢執(zhí)行的次數、執(zhí)行的讀寫次數、消耗的執(zhí)行的讀寫次數、消耗的CPU時間量、索時間量、索引查詢使用情況統(tǒng)計數據等。引查詢使用情況統(tǒng)計數據等。可以使用通過可以使用通過DMV獲得的執(zhí)行統(tǒng)計數據來獲得的執(zhí)行統(tǒng)計數據來微調查詢，例如，可以重構微調查詢，例如，可以重構T-SQL代碼來代碼來利用并行性和現有的索引。還可以使用這利用并行性和

35、現有的索引。還可以使用這些些DMV來標識遺漏的索引、未利用的索引，來標識遺漏的索引、未利用的索引，以及標識需要執(zhí)行碎片整理的索引。以及標識需要執(zhí)行碎片整理的索引。例如，研究例如，研究AdventureWorksDW數據數據庫的庫的FactInternetSales表中的現有索表中的現有索引。如圖引。如圖8-2所示，所示，FactInternetSales表已經有了良好構建的索引。表已經有了良好構建的索引。圖8-2 AdventureWorksDW數據庫的FactInternetSales表中的索引8.4 使用索引改進查詢性能使用索引改進查詢性能為了說明查詢調優(yōu)過程，依次運行一系列為了說明查詢調

36、優(yōu)過程，依次運行一系列步驟以生成可以通過步驟以生成可以通過DMV顯示的執(zhí)行統(tǒng)計顯示的執(zhí)行統(tǒng)計數據：數據：(1) 刪除刪除FactInternetSales表中的現表中的現有索引有索引ProductKey和和OrderDateKey，如下所示：如下所示：8.4 使用索引改進查詢性能使用索引改進查詢性能(2) 執(zhí)行如下腳本執(zhí)行如下腳本3次：次：8.4 使用索引改進查詢性能使用索引改進查詢性能圖圖8-3顯示了執(zhí)行的顯示了執(zhí)行的T-SQL腳本和結果。腳本和結果。根據計算機上的可用資源不同，執(zhí)行結果根據計算機上的可用資源不同，執(zhí)行結果可能有所不同。可能有所不同。圖8-3 執(zhí)行的T-SQL腳本和結果圖8.

37、4 使用索引改進查詢性能使用索引改進查詢性能(3) 運行如下腳本以分析上述查詢的執(zhí)行運行如下腳本以分析上述查詢的執(zhí)行統(tǒng)計數據：統(tǒng)計數據：8.4 使用索引改進查詢性能使用索引改進查詢性能圖圖8-4顯示了主要由顯示了主要由sys.dm_exec_query_stats DMV報報告的執(zhí)行統(tǒng)計數據。告的執(zhí)行統(tǒng)計數據。圖8-4 由sys.dm_exec_query_stats DMV報告的執(zhí)行統(tǒng)計數據8.4 使用索引改進查詢性能使用索引改進查詢性能(4) 查詢查詢sys.dm_db_missing_index_details DMV，以檢查是否報告了遺漏的索引，以檢查是否報告了遺漏的索引，如下所示：如

38、下所示：圖圖8-8-5顯示了顯示了sys.dm_db_missing_index_details DMVsys.dm_db_missing_index_details DMV的的結果。結果。使用使用sys.dm_db_missing_ index_details DMV可以快速標識是否可以快速標識是否需要索引。數據庫引擎優(yōu)化顧問需要索引。數據庫引擎優(yōu)化顧問(Database Engine Tuning Advisor，DTA)是標識遺漏索引的另一種方式，并且是標識遺漏索引的另一種方式，并且有一個向導來幫助完成標識遺漏索引的過有一個向導來幫助完成標識遺漏索引的過程。從程。從SQL Server

39、Management Studio的的“工具工具”菜單中可以執(zhí)行菜單中可以執(zhí)行DTA。圖8-5 sys.dm_db_missing_index_details DMV的結果8.4 使用索引改進查詢性能使用索引改進查詢性能(5) 繼續(xù)進行查詢調優(yōu)，在繼續(xù)進行查詢調優(yōu)，在FactInternetSales表上創(chuàng)建表上創(chuàng)建ProductKey和和OrderDateKey索引，索引，如下所示：如下所示：8.4 使用索引改進查詢性能使用索引改進查詢性能(6) 再次執(zhí)行第再次執(zhí)行第(2)步中定義的步中定義的Internet_ResellerProductSales查查詢詢3次。圖次。圖8-6顯示這個查詢的

40、讀取次數顯顯示這個查詢的讀取次數顯著改善，這也將改善這個查詢的整體執(zhí)行著改善，這也將改善這個查詢的整體執(zhí)行時間。時間。圖8-6 查詢的讀取次數8.5 數據庫引擎優(yōu)化顧問數據庫引擎優(yōu)化顧問自自SQL Server 2005以來，提供給數據以來，提供給數據庫管理員的一款比較有用的工具是數據庫庫管理員的一款比較有用的工具是數據庫引擎優(yōu)化顧問引擎優(yōu)化顧問(DTA)。在本章中已經看到，。在本章中已經看到，使用使用DTA可以分析數據庫以查找遺漏的索可以分析數據庫以查找遺漏的索引并給出其他性能調優(yōu)建議，如分區(qū)和索引并給出其他性能調優(yōu)建議，如分區(qū)和索引視圖。引視圖。DTA接受如下類型的工作負載：接受如下類型的

41、工作負載：SQL腳本文件腳本文件(*.sql)跟蹤文件跟蹤文件(*.trc)XML文件文件(*.xml)跟蹤表跟蹤表計劃緩存計劃緩存 8.5 數據庫引擎優(yōu)化顧問數據庫引擎優(yōu)化顧問圖圖8-7顯示了顯示了DTA的工作負載選擇界面，的工作負載選擇界面，包括新的包括新的Plan Cache選項。選項。DTA帶給帶給DBA和和SQL Server開發(fā)人員的開發(fā)人員的顯著優(yōu)點是能夠快速生成數據庫性能改進顯著優(yōu)點是能夠快速生成數據庫性能改進建議，而不需要知道底層的數據庫架構、建議，而不需要知道底層的數據庫架構、數據結構、使用模式甚至是數據結構、使用模式甚至是SQL Server查詢優(yōu)化器的內部工作原理。查詢優(yōu)化器的內部工作原理。圖8-7 DTA的工作負載選擇界面8.6 索引太多的成本索引太多的成本太多的索引會產生與大量額外數據頁關聯(lián)太多的索引會產生與大量額外數據頁關聯(lián)