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

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

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

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

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

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

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

7、索引是傳統(tǒng)的索引，將數(shù)據(jù)存儲(chǔ)為數(shù)據(jù)頁中的行。這些索引包將數(shù)據(jù)存儲(chǔ)為數(shù)據(jù)頁中的行。這些索引包括聚集索引和非聚集索引。括聚集索引和非聚集索引。1) 聚集索引聚集索引聚集索引基于鍵列存儲(chǔ)和排序表的葉級(jí)數(shù)聚集索引基于鍵列存儲(chǔ)和排序表的葉級(jí)數(shù)據(jù)。實(shí)際的存儲(chǔ)頁鏈接在一起，所以可以據(jù)。實(shí)際的存儲(chǔ)頁鏈接在一起，所以可以按照聚集鍵的順序依次讀取表，導(dǎo)致的按照聚集鍵的順序依次讀取表，導(dǎo)致的I/O開銷極小。每個(gè)表只可以有一個(gè)聚集開銷極小。每個(gè)表只可以有一個(gè)聚集索引，因?yàn)橹豢梢园凑找环N順序排序數(shù)據(jù)，索引，因?yàn)橹豢梢园凑找环N順序排序數(shù)據(jù)，而且聚集索引代表了實(shí)際的表數(shù)據(jù)。而且聚集索引代表了實(shí)際的表數(shù)據(jù)。讓實(shí)際數(shù)據(jù)聚集起來

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

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

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

11、包括在索引自身中，不需要鍵列或非鍵列包括在索引自身中，不需要再讀取實(shí)際的數(shù)據(jù)行。再讀取實(shí)際的數(shù)據(jù)行。INCLUDE短語使非聚集索引更加靈活，短語使非聚集索引更加靈活，因?yàn)殒I中包含的字段可以具有鍵中原本不因?yàn)殒I中包含的字段可以具有鍵中原本不允許的數(shù)據(jù)類型，并且在計(jì)算索引大小或允許的數(shù)據(jù)類型，并且在計(jì)算索引大小或鍵列的數(shù)量時(shí)，也不會(huì)考慮它們。鍵列的數(shù)量時(shí)，也不會(huì)考慮它們。8.2.1 理解索引理解索引4) 過濾索引過濾索引過濾索引使用過濾索引使用WHERE子句指示將要索引子句指示將要索引哪些行。因?yàn)橹皇撬饕碇械牟糠中?，所哪些行。因?yàn)橹皇撬饕碇械牟糠中?，所以可以?chuàng)建較小的數(shù)據(jù)集存儲(chǔ)到索引中。以可以

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

27、據(jù)將被移動(dòng)，以支持索引時(shí)，葉級(jí)頁的數(shù)據(jù)將被移動(dòng)，以支持索引的排序順序，這可能導(dǎo)致索引碎片。引的排序順序，這可能導(dǎo)致索引碎片。在行存儲(chǔ)索引中，被刪除或修改的行可以在行存儲(chǔ)索引中，被刪除或修改的行可以重用空的空間，但是頁分割可能導(dǎo)致出現(xiàn)重用空的空間，但是頁分割可能導(dǎo)致出現(xiàn)碎片。在列存儲(chǔ)索引中，應(yīng)該定期重新構(gòu)碎片。在列存儲(chǔ)索引中，應(yīng)該定期重新構(gòu)建索引以回收刪除或更新操作導(dǎo)致的空的建索引以回收刪除或更新操作導(dǎo)致的空的空間，以及更新所有非聚集列存儲(chǔ)索引，空間，以及更新所有非聚集列存儲(chǔ)索引，這是非常重要的。這是非常重要的。隨著時(shí)間的推移，數(shù)據(jù)頁中的數(shù)據(jù)分布可隨著時(shí)間的推移，數(shù)據(jù)頁中的數(shù)據(jù)分布可能會(huì)變得不再

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

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

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

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

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

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

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

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

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

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

38、下所示：圖圖8-8-5顯示了顯示了sys.dm_db_missing_index_details DMVsys.dm_db_missing_index_details DMV的的結(jié)果。結(jié)果。使用使用sys.dm_db_missing_ index_details DMV可以快速標(biāo)識(shí)是否可以快速標(biāo)識(shí)是否需要索引。數(shù)據(jù)庫引擎優(yōu)化顧問需要索引。數(shù)據(jù)庫引擎優(yōu)化顧問(Database Engine Tuning Advisor，DTA)是標(biāo)識(shí)遺漏索引的另一種方式，并且是標(biāo)識(shí)遺漏索引的另一種方式，并且有一個(gè)向?qū)韼椭瓿蓸?biāo)識(shí)遺漏索引的過有一個(gè)向?qū)韼椭瓿蓸?biāo)識(shí)遺漏索引的過程。從程。從SQL Server

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

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

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