SQLSERVER之9索引和查詢優(yōu)化

1、2022-3-30第1頁第第9章章 索引和查詢優(yōu)化索引和查詢優(yōu)化學(xué)習(xí)目標(biāo)本章重點(diǎn)本章內(nèi)容2022-3-30第2頁學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)l數(shù)據(jù)庫管理系統(tǒng)通常使用索引技術(shù)加快對表中數(shù)數(shù)據(jù)庫管理系統(tǒng)通常使用索引技術(shù)加快對表中數(shù)據(jù)的檢索。索引類似于圖書的目錄。目錄允許用據(jù)的檢索。索引類似于圖書的目錄。目錄允許用戶不必翻閱整本圖書就能根據(jù)頁數(shù)迅速找到所需戶不必翻閱整本圖書就能根據(jù)頁數(shù)迅速找到所需內(nèi)容。在數(shù)據(jù)庫中，索引也允許數(shù)據(jù)庫應(yīng)用程序內(nèi)容。在數(shù)據(jù)庫中，索引也允許數(shù)據(jù)庫應(yīng)用程序迅速找到表中特定的數(shù)據(jù)，而不必掃描整個數(shù)據(jù)迅速找到表中特定的數(shù)據(jù)，而不必掃描整個數(shù)據(jù)庫。庫。l在圖書中，目錄是內(nèi)容和相應(yīng)頁碼的列表清單

2、。在圖書中，目錄是內(nèi)容和相應(yīng)頁碼的列表清單。在數(shù)據(jù)庫中，索引是表中數(shù)據(jù)和相應(yīng)存儲位置的在數(shù)據(jù)庫中，索引是表中數(shù)據(jù)和相應(yīng)存儲位置的列表。列表。l本章將詳細(xì)研究有關(guān)索引和查詢優(yōu)化的內(nèi)容。本章將詳細(xì)研究有關(guān)索引和查詢優(yōu)化的內(nèi)容。2022-3-30第3頁本章重點(diǎn)本章重點(diǎn)l索引的特點(diǎn)索引的特點(diǎn)l索引的類型索引的類型l創(chuàng)建索引創(chuàng)建索引l索引信息索引信息l優(yōu)化索引優(yōu)化索引l優(yōu)化查詢優(yōu)化查詢2022-3-30第4頁本章內(nèi)容本章內(nèi)容9.1 概述概述9.2 索引的類型和特點(diǎn)索引的類型和特點(diǎn)9.3 創(chuàng)建索引創(chuàng)建索引9.4 索引維護(hù)索引維護(hù)9.5 查詢優(yōu)化查詢優(yōu)化9.6 上機(jī)練習(xí)上機(jī)練習(xí)9.7 習(xí)題習(xí)題2022-3-

3、30第5頁本章內(nèi)容本章內(nèi)容9.1 概述概述9.2 索引的類型和特點(diǎn)索引的類型和特點(diǎn)9.3 創(chuàng)建索引創(chuàng)建索引9.4 索引維護(hù)索引維護(hù)9.5 查詢優(yōu)化查詢優(yōu)化9.6 上機(jī)練習(xí)上機(jī)練習(xí)9.7 習(xí)題習(xí)題2022-3-30第6頁9.1 概述概述l在在Microsoft SQL Server系統(tǒng)中，可管理的最小系統(tǒng)中，可管理的最小空間是頁。一個頁是空間是頁。一個頁是8KB字節(jié)的物理空間。插入字節(jié)的物理空間。插入數(shù)據(jù)的時候，數(shù)據(jù)就按照插入的時間順序被放置數(shù)據(jù)的時候，數(shù)據(jù)就按照插入的時間順序被放置在數(shù)據(jù)頁上。一般地，放置數(shù)據(jù)的順序與數(shù)據(jù)本在數(shù)據(jù)頁上。一般地，放置數(shù)據(jù)的順序與數(shù)據(jù)本身的邏輯關(guān)系之間是沒有任何聯(lián)系

4、的。身的邏輯關(guān)系之間是沒有任何聯(lián)系的。l因此，從數(shù)據(jù)之間的邏輯關(guān)系方面來講，數(shù)據(jù)是因此，從數(shù)據(jù)之間的邏輯關(guān)系方面來講，數(shù)據(jù)是亂七八糟堆放在一起的。數(shù)據(jù)的這種堆放方式稱亂七八糟堆放在一起的。數(shù)據(jù)的這種堆放方式稱為堆。為堆。l當(dāng)一個數(shù)據(jù)頁上的數(shù)據(jù)堆放滿之后，數(shù)據(jù)就得堆當(dāng)一個數(shù)據(jù)頁上的數(shù)據(jù)堆放滿之后，數(shù)據(jù)就得堆放在另外一個數(shù)據(jù)頁上，這時就稱為頁分解。放在另外一個數(shù)據(jù)頁上，這時就稱為頁分解。2022-3-30第7頁為什么要創(chuàng)建索引呢？為什么要創(chuàng)建索引呢？l索引是一種與表或視圖關(guān)聯(lián)的物理結(jié)構(gòu)，可以用來加快從索引是一種與表或視圖關(guān)聯(lián)的物理結(jié)構(gòu)，可以用來加快從表或視圖中檢索數(shù)據(jù)行的速度。表或視圖中檢索數(shù)據(jù)行

5、的速度。l為什么要創(chuàng)建索引呢？這是因為創(chuàng)建索引可以大大提高系為什么要創(chuàng)建索引呢？這是因為創(chuàng)建索引可以大大提高系統(tǒng)的性能。第一，通過創(chuàng)建唯一性索引，可以保證每一行統(tǒng)的性能。第一，通過創(chuàng)建唯一性索引，可以保證每一行數(shù)據(jù)的唯一性。第二，可以大大加快數(shù)據(jù)的檢索速度，這數(shù)據(jù)的唯一性。第二，可以大大加快數(shù)據(jù)的檢索速度，這也是索引的最主要的原因。第三，可以加速表和表之間的也是索引的最主要的原因。第三，可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。第四，在使用第四，在使用ORDER BY和和GROUP BY子句進(jìn)行數(shù)據(jù)檢子句進(jìn)行數(shù)據(jù)檢索

6、時，同樣可以顯著減少查詢中分組和排序的時間。第五，索時，同樣可以顯著減少查詢中分組和排序的時間。第五，通過使用索引，可以在查詢的過程中使用優(yōu)化隱藏器，提通過使用索引，可以在查詢的過程中使用優(yōu)化隱藏器，提高系統(tǒng)的性能。高系統(tǒng)的性能。l正是因為上述這些原因，所以應(yīng)該對表增加索引。正是因為上述這些原因，所以應(yīng)該對表增加索引。2022-3-30第8頁為什么不對表中的每一個列創(chuàng)建一為什么不對表中的每一個列創(chuàng)建一個索引呢？個索引呢？l也許會有人要問增加索引有如此多的優(yōu)點(diǎn)，為什么不對表也許會有人要問增加索引有如此多的優(yōu)點(diǎn)，為什么不對表中的每一個列創(chuàng)建一個索引呢？中的每一個列創(chuàng)建一個索引呢？l雖然索引有許多優(yōu)

7、點(diǎn)，但是為表中的每一個列都增加索引雖然索引有許多優(yōu)點(diǎn)，但是為表中的每一個列都增加索引是非常不明智的做法。這是因為增加索引也有其不利的一是非常不明智的做法。這是因為增加索引也有其不利的一面。面。l第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間。第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間。l第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間。如果要建立聚集索引，每一個索引還要占一定的物理空間。如果要建立聚集索引，那么需要的空間就會更大。那么需要的空間就會更大。l第三，當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候，索第三，當(dāng)對表中的數(shù)據(jù)進(jìn)行

8、增加、刪除和修改的時候，索引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。2022-3-30第9頁索引是建立在列的上面索引是建立在列的上面l在經(jīng)常需要搜索的列上創(chuàng)建索引；在經(jīng)常需要搜索的列上創(chuàng)建索引；l在主鍵上創(chuàng)建索引；在主鍵上創(chuàng)建索引；l在經(jīng)常用于連接的列上創(chuàng)建索引，也就是在外鍵在經(jīng)常用于連接的列上創(chuàng)建索引，也就是在外鍵上創(chuàng)建索引；上創(chuàng)建索引；l在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引(因因為索引已經(jīng)排序，其指定的范圍是連續(xù)的為索引已經(jīng)排序，其指定的范圍是連續(xù)的)；l在經(jīng)常需要排序的列上創(chuàng)建索引在經(jīng)常需要排序

9、的列上創(chuàng)建索引(因為索引已經(jīng)排因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查序，這樣查詢可以利用索引的排序，加快排序查詢時間詢時間)；l在經(jīng)常用在在經(jīng)常用在WHERE子句中的列上創(chuàng)建索引。子句中的列上創(chuàng)建索引。2022-3-30第10頁本章內(nèi)容本章內(nèi)容9.1 概述概述9.2 索引的類型和特點(diǎn)索引的類型和特點(diǎn)9.3 創(chuàng)建索引創(chuàng)建索引9.4 索引維護(hù)索引維護(hù)9.5 查詢優(yōu)化查詢優(yōu)化9.6 上機(jī)練習(xí)上機(jī)練習(xí)9.7 習(xí)題習(xí)題2022-3-30第11頁9.2 索引的類型和特點(diǎn)索引的類型和特點(diǎn)l在在Microsoft SQL Server 2005系統(tǒng)中有兩種基系統(tǒng)中有兩種基本的索引類型：聚集索

10、引和非聚集索引。本的索引類型：聚集索引和非聚集索引。l除此之外，還有唯一性索引、包含性列索引、索除此之外，還有唯一性索引、包含性列索引、索引視圖、全文索引及引視圖、全文索引及XML索引等。在這些索引類索引等。在這些索引類型中，聚集索引和非聚集索引是數(shù)據(jù)庫引擎中索型中，聚集索引和非聚集索引是數(shù)據(jù)庫引擎中索引的基本類型，是理解唯一性索引、包含性列索引的基本類型，是理解唯一性索引、包含性列索引、索引視圖的基礎(chǔ)，引、索引視圖的基礎(chǔ)，l本節(jié)主要研究這兩種索引類型。本節(jié)主要研究這兩種索引類型。l另外，為了更好地理解索引結(jié)構(gòu)，有必要對堆結(jié)另外，為了更好地理解索引結(jié)構(gòu)，有必要對堆結(jié)構(gòu)有所了解。構(gòu)有所了解。l最

11、后，簡單介紹一下系統(tǒng)訪問數(shù)據(jù)的方式。最后，簡單介紹一下系統(tǒng)訪問數(shù)據(jù)的方式。2022-3-30第12頁堆堆l堆是不含聚集索引的表，表中的數(shù)據(jù)沒有堆是不含聚集索引的表，表中的數(shù)據(jù)沒有任何的順序。任何的順序。l堆的信息記錄在堆的信息記錄在sys.partitions目錄視圖中。目錄視圖中。每一個堆都可能有多個不同的分區(qū)，每一每一個堆都可能有多個不同的分區(qū)，每一個分區(qū)都有一個堆結(jié)構(gòu)，每一個分區(qū)在個分區(qū)都有一個堆結(jié)構(gòu)，每一個分區(qū)在sys.partitions目錄視圖中都有一行，且目錄視圖中都有一行，且index_id=0。也就是說，每一個堆都可能。也就是說，每一個堆都可能有多個堆結(jié)構(gòu)。有多個堆結(jié)構(gòu)。20

12、22-3-30第13頁堆結(jié)構(gòu)示意圖堆結(jié)構(gòu)示意圖 2022-3-30第14頁聚集索引聚集索引l聚集索引是一種數(shù)據(jù)表的物理順序與索引順序相同的索引，聚集索引是一種數(shù)據(jù)表的物理順序與索引順序相同的索引，非聚集索引則是一種數(shù)據(jù)表的物理順序與索引順序不相同非聚集索引則是一種數(shù)據(jù)表的物理順序與索引順序不相同的索引。的索引。l聚集索引的葉級和非葉級構(gòu)成了一個特殊類型的聚集索引的葉級和非葉級構(gòu)成了一個特殊類型的B樹結(jié)構(gòu)。樹結(jié)構(gòu)。B樹結(jié)構(gòu)中的每一頁稱為一個索引節(jié)點(diǎn)。索引的最低級節(jié)樹結(jié)構(gòu)中的每一頁稱為一個索引節(jié)點(diǎn)。索引的最低級節(jié)點(diǎn)是葉級節(jié)點(diǎn)。點(diǎn)是葉級節(jié)點(diǎn)。l在一個聚集索引中，某個表的數(shù)據(jù)頁是葉級，在葉級之上在一個

13、聚集索引中，某個表的數(shù)據(jù)頁是葉級，在葉級之上的索引頁是非葉級。在聚集索引中，頁的順序是有序的。的索引頁是非葉級。在聚集索引中，頁的順序是有序的。應(yīng)該在表中經(jīng)常搜索的列或按照順序訪問的列上創(chuàng)建聚集應(yīng)該在表中經(jīng)常搜索的列或按照順序訪問的列上創(chuàng)建聚集索引。其中，用于指定聚集索引第一頁地址信息的索引。其中，用于指定聚集索引第一頁地址信息的root_page來自來自sys.system_internal_allocation_units系統(tǒng)視圖中。聚集索引的結(jié)構(gòu)示意圖如圖系統(tǒng)視圖中。聚集索引的結(jié)構(gòu)示意圖如圖9-2所示。所示。2022-3-30第15頁聚集索引的結(jié)構(gòu)示意圖聚集索引的結(jié)構(gòu)示意圖 2022-3

14、-30第16頁非聚集索引非聚集索引l非聚集索引與聚集索引具有相同的非聚集索引與聚集索引具有相同的B樹結(jié)構(gòu)，但是在非聚樹結(jié)構(gòu)，但是在非聚集索引中，基礎(chǔ)表的數(shù)據(jù)行不是按照非聚集鍵的順序排序集索引中，基礎(chǔ)表的數(shù)據(jù)行不是按照非聚集鍵的順序排序和存儲，且非聚集索引的葉級是由索引頁而不是由數(shù)據(jù)頁和存儲，且非聚集索引的葉級是由索引頁而不是由數(shù)據(jù)頁組成。組成。l非聚集索引既可以定義在表或視圖的聚集索引上，也可以非聚集索引既可以定義在表或視圖的聚集索引上，也可以定義在表或視圖的堆上。非聚集索引中的每一個索引行都定義在表或視圖的堆上。非聚集索引中的每一個索引行都是由非聚集鍵值和行定位符組成，該行定位符指向聚集索是

15、由非聚集鍵值和行定位符組成，該行定位符指向聚集索引或堆中包含該鍵值的數(shù)據(jù)行。如果表或視圖中沒有聚集引或堆中包含該鍵值的數(shù)據(jù)行。如果表或視圖中沒有聚集索引索引(堆堆)，則行定位符是指向行的指針，則行定位符是指向行的指針RID，而，而RID由文件由文件標(biāo)識符標(biāo)識符ID、頁碼和頁上的行數(shù)生成。如果表或視圖上有聚、頁碼和頁上的行數(shù)生成。如果表或視圖上有聚集索引，則行定位符則是行的聚集索引鍵。非聚集索引的集索引，則行定位符則是行的聚集索引鍵。非聚集索引的結(jié)構(gòu)示意圖如圖結(jié)構(gòu)示意圖如圖9-3所示。所示。2022-3-30第17頁非聚集索引的結(jié)構(gòu)示意圖非聚集索引的結(jié)構(gòu)示意圖 2022-3-30第18頁其他類型

16、的索引其他類型的索引l除了聚集索引和非聚集索引之外，除了聚集索引和非聚集索引之外，Microsoft SQL Server 2005系統(tǒng)還提供系統(tǒng)還提供了一些其他類型的索引或索引表現(xiàn)形式，這些內(nèi)容包括唯一性索引、包含性了一些其他類型的索引或索引表現(xiàn)形式，這些內(nèi)容包括唯一性索引、包含性列索引、索引視圖、全文索引和列索引、索引視圖、全文索引和XML索引。索引。l在創(chuàng)建聚集索引或非聚集索引時，索引鍵可以都不相同，也可以包含重復(fù)值。在創(chuàng)建聚集索引或非聚集索引時，索引鍵可以都不相同，也可以包含重復(fù)值。如果希望索引鍵都各不相同，那么必須創(chuàng)建唯一性索引。當(dāng)然，在創(chuàng)建聚集如果希望索引鍵都各不相同，那么必須創(chuàng)建

17、唯一性索引。當(dāng)然，在創(chuàng)建聚集索引或非聚集索引時，都可以指定該索引具有唯一性的特點(diǎn)。這種唯一性與索引或非聚集索引時，都可以指定該索引具有唯一性的特點(diǎn)。這種唯一性與前面講過的主鍵約束是關(guān)聯(lián)的，某種程度上可以說，主鍵約束等于唯一性的前面講過的主鍵約束是關(guān)聯(lián)的，某種程度上可以說，主鍵約束等于唯一性的聚集索引。聚集索引。l如果多個列的字節(jié)總數(shù)大于如果多個列的字節(jié)總數(shù)大于900字節(jié)且又希望將這些列都包含在索引中，那字節(jié)且又希望將這些列都包含在索引中，那么可以使用包含性列索引。么可以使用包含性列索引。 l如果希望提高視圖的查詢效率，可以將視圖的索引物理化，也就是說將結(jié)果如果希望提高視圖的查詢效率，可以將視圖

18、的索引物理化，也就是說將結(jié)果集永久存儲在索引中。集永久存儲在索引中。 l全文索引是一種特殊類型的基于標(biāo)記的索引，是通過全文索引是一種特殊類型的基于標(biāo)記的索引，是通過Microsoft SQL Server的全文引擎服務(wù)創(chuàng)建、使用和維護(hù)，其目的是為用戶提供在字符串?dāng)?shù)據(jù)中高的全文引擎服務(wù)創(chuàng)建、使用和維護(hù)，其目的是為用戶提供在字符串?dāng)?shù)據(jù)中高效率地搜索復(fù)雜的詞語。這種索引的結(jié)構(gòu)與數(shù)據(jù)庫引擎使用的聚集索引或非效率地搜索復(fù)雜的詞語。這種索引的結(jié)構(gòu)與數(shù)據(jù)庫引擎使用的聚集索引或非聚集索引的聚集索引的B樹結(jié)構(gòu)是不同的。樹結(jié)構(gòu)是不同的。lXML索引是與索引是與XML數(shù)據(jù)關(guān)聯(lián)的索引形式，是數(shù)據(jù)關(guān)聯(lián)的索引形式，是XM

19、L二進(jìn)制二進(jìn)制BLOB的已拆分持久的已拆分持久表示形式。表示形式。XML索引又可以分為主索引和輔助索引。索引又可以分為主索引和輔助索引。2022-3-30第19頁訪問數(shù)據(jù)的方式訪問數(shù)據(jù)的方式l訪問數(shù)據(jù)庫中數(shù)據(jù)時，可以采用兩種方法即表掃描和索引查找。訪問數(shù)據(jù)庫中數(shù)據(jù)時，可以采用兩種方法即表掃描和索引查找。l第一種方法是表掃描，就是指系統(tǒng)將指針放在該表的表頭數(shù)據(jù)所在的第一種方法是表掃描，就是指系統(tǒng)將指針放在該表的表頭數(shù)據(jù)所在的數(shù)據(jù)頁上，然后按照數(shù)據(jù)頁的排列順序，逐頁地從前向后掃描該表數(shù)數(shù)據(jù)頁上，然后按照數(shù)據(jù)頁的排列順序，逐頁地從前向后掃描該表數(shù)據(jù)所占有的全部數(shù)據(jù)頁，直至掃描完表中的全部記錄。在掃描

20、時，如據(jù)所占有的全部數(shù)據(jù)頁，直至掃描完表中的全部記錄。在掃描時，如果找到符合查詢條件的記錄，那么就將這條記錄挑選出來。最后，將果找到符合查詢條件的記錄，那么就將這條記錄挑選出來。最后，將全部挑選出來符合查詢語句條件的記錄顯示出來。全部挑選出來符合查詢語句條件的記錄顯示出來。l第二種方法是使用索引查找。索引是一種樹狀結(jié)構(gòu)，其中存儲了關(guān)鍵第二種方法是使用索引查找。索引是一種樹狀結(jié)構(gòu)，其中存儲了關(guān)鍵字和指向包含關(guān)鍵字所在記錄的數(shù)據(jù)頁的指針。當(dāng)使用索引查找時系字和指向包含關(guān)鍵字所在記錄的數(shù)據(jù)頁的指針。當(dāng)使用索引查找時系統(tǒng)將沿著索引的樹狀結(jié)構(gòu)，根據(jù)索引中關(guān)鍵字和指針找到符合查詢條統(tǒng)將沿著索引的樹狀結(jié)構(gòu)，

21、根據(jù)索引中關(guān)鍵字和指針找到符合查詢條件的記錄。最后將全部查找到的符合查詢語句條件的記錄顯示出來。件的記錄。最后將全部查找到的符合查詢語句條件的記錄顯示出來。當(dāng)系統(tǒng)沿著索引值查找時，使用搜索值與索引值進(jìn)行比較判斷。這種當(dāng)系統(tǒng)沿著索引值查找時，使用搜索值與索引值進(jìn)行比較判斷。這種比較判斷一直進(jìn)行下去，直到滿足下面兩個條件為止：比較判斷一直進(jìn)行下去，直到滿足下面兩個條件為止： 搜索值不大于或等于索引值。搜索值不大于或等于索引值。 搜索值大于或等于索引頁上的最后一個值。搜索值大于或等于索引頁上的最后一個值。2022-3-30第20頁本章內(nèi)容本章內(nèi)容9.1 概述概述9.2 索引的類型和特點(diǎn)索引的類型和特

22、點(diǎn)9.3 創(chuàng)建索引創(chuàng)建索引9.4 索引維護(hù)索引維護(hù)9.5 查詢優(yōu)化查詢優(yōu)化9.6 上機(jī)練習(xí)上機(jī)練習(xí)9.7 習(xí)題習(xí)題2022-3-30第21頁9.3 創(chuàng)建索引創(chuàng)建索引l在在Microsoft SQL Server 2005系統(tǒng)中，既系統(tǒng)中，既可以直接創(chuàng)建索引，也可以間接創(chuàng)建索引?？梢灾苯觿?chuàng)建索引，也可以間接創(chuàng)建索引。l當(dāng)直接創(chuàng)建索引時，可以使用當(dāng)直接創(chuàng)建索引時，可以使用CREATE INDEX語句，也可以使用圖形工具。語句，也可以使用圖形工具。2022-3-30第22頁直接方法和間接方法直接方法和間接方法l可以把創(chuàng)建索引的方式分為直接方法和間接方法?？梢园褎?chuàng)建索引的方式分為直接方法和間接方法。l

23、直接創(chuàng)建索引的方法就是使用命令和工具直接創(chuàng)建索引。直接創(chuàng)建索引的方法就是使用命令和工具直接創(chuàng)建索引。l間接創(chuàng)建索引就是通過創(chuàng)建其他對象而附加創(chuàng)建了索引，間接創(chuàng)建索引就是通過創(chuàng)建其他對象而附加創(chuàng)建了索引，例如在表中定義主鍵約束或唯一性約束的同時也創(chuàng)建了索例如在表中定義主鍵約束或唯一性約束的同時也創(chuàng)建了索引。雖然，這兩種方法都可以創(chuàng)建索引，但是它們創(chuàng)建索引。雖然，這兩種方法都可以創(chuàng)建索引，但是它們創(chuàng)建索引的具體內(nèi)容是有區(qū)別的。引的具體內(nèi)容是有區(qū)別的。l使用使用CREATE INDEX語句或使用創(chuàng)建索引向?qū)韯?chuàng)建索語句或使用創(chuàng)建索引向?qū)韯?chuàng)建索引，這是最基本的索引創(chuàng)建方式，并且這種方法最具有柔引，這

24、是最基本的索引創(chuàng)建方式，并且這種方法最具有柔性，可以定制創(chuàng)建出符合自己需要的索引。性，可以定制創(chuàng)建出符合自己需要的索引。 l通過定義主鍵約束或唯一性約束，也可以間接創(chuàng)建索引。通過定義主鍵約束或唯一性約束，也可以間接創(chuàng)建索引。 2022-3-30第23頁CREATE INDEX語句語句lCREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name lON table_or_view_name ( column ASC | DESC ,.n ) lINCLUDE (column_name, n)l WITH l( PAD_INDEX = ON |

25、OFFl | FILLFACTOR = fillfactorl | SORT_IN_TEMPDB = ON | OFFl | IGNORE_DUP_KEY = ON | OFFl | STATISTICS_NORECOMPUTE = ON | OFFl | DROP_EXISTING = ON | OFFl | ONLINE = ON | OFFl | ALLOW_ROW_LOCKS = ON | OFFl | ALLOW_PAGE_LOCKS = ON | OFFl | MAXDOP = max_degree_of_parallelism), nlON partition_schema_na

26、me (column_name) | | default2022-3-30第24頁【例【例9-1】使用】使用CREATE INDEX語語句創(chuàng)建唯一性的聚集索引句創(chuàng)建唯一性的聚集索引 2022-3-30第25頁【例【例9-2】使用】使用CREATE INDEX語語句創(chuàng)建唯一性的非聚集索引句創(chuàng)建唯一性的非聚集索引 2022-3-30第26頁【例【例9-3】使用】使用CREATE INDEX語語句創(chuàng)建包含性列索引句創(chuàng)建包含性列索引 2022-3-30第27頁【例【例9-4】使用】使用DROP INDEX語句刪語句刪除指定的索引除指定的索引 2022-3-30第28頁數(shù)據(jù)庫引擎優(yōu)化顧問數(shù)據(jù)庫引擎優(yōu)化顧

27、問l使用使用Microsoft SQL Server 2005的數(shù)據(jù)庫引擎的數(shù)據(jù)庫引擎優(yōu)化顧問，用戶可以方便地選擇和創(chuàng)建索引、索優(yōu)化顧問，用戶可以方便地選擇和創(chuàng)建索引、索引視圖和分區(qū)的最佳集合。引視圖和分區(qū)的最佳集合。l數(shù)據(jù)庫引擎優(yōu)化顧問分析一個或多個數(shù)據(jù)庫的工數(shù)據(jù)庫引擎優(yōu)化顧問分析一個或多個數(shù)據(jù)庫的工作負(fù)荷和實現(xiàn)，其中工作負(fù)荷是對要優(yōu)化的一個作負(fù)荷和實現(xiàn)，其中工作負(fù)荷是對要優(yōu)化的一個或多個數(shù)據(jù)庫執(zhí)行的一組或多個數(shù)據(jù)庫執(zhí)行的一組Transact-SQL語句。語句。l數(shù)據(jù)庫引擎優(yōu)化顧問的輸入是由數(shù)據(jù)庫引擎優(yōu)化顧問的輸入是由SQL Server Profiler生成的跟蹤文件、指定的跟蹤表或工作負(fù)

28、生成的跟蹤文件、指定的跟蹤表或工作負(fù)荷。數(shù)據(jù)庫引擎優(yōu)化顧問的輸出是修改數(shù)據(jù)庫的荷。數(shù)據(jù)庫引擎優(yōu)化顧問的輸出是修改數(shù)據(jù)庫的物理設(shè)計結(jié)構(gòu)的建議，其中物理設(shè)計結(jié)構(gòu)包括聚物理設(shè)計結(jié)構(gòu)的建議，其中物理設(shè)計結(jié)構(gòu)包括聚集索引、非聚集索引、索引視圖、分區(qū)等。集索引、非聚集索引、索引視圖、分區(qū)等。2022-3-30第29頁【例【例9-5】使用數(shù)據(jù)庫引擎優(yōu)化顧問】使用數(shù)據(jù)庫引擎優(yōu)化顧問 2022-3-30第30頁【例【例9-5】2022-3-30第31頁查看索引信息查看索引信息l在在Microsoft SQL Server 2005系統(tǒng)中，可系統(tǒng)中，可以使用一些目錄視圖和系統(tǒng)函數(shù)查看有關(guān)以使用一些目錄視圖和系統(tǒng)函

29、數(shù)查看有關(guān)索引的信息。索引的信息。l這些目錄視圖和系統(tǒng)函數(shù)的描述說明如表這些目錄視圖和系統(tǒng)函數(shù)的描述說明如表9-1所示。所示。2022-3-30第32頁【例【例9-6】查看索引信息】查看索引信息 2022-3-30第33頁本章內(nèi)容本章內(nèi)容9.1 概述概述9.2 索引的類型和特點(diǎn)索引的類型和特點(diǎn)9.3 創(chuàng)建索引創(chuàng)建索引9.4 索引維護(hù)索引維護(hù)9.5 查詢優(yōu)化查詢優(yōu)化9.6 上機(jī)練習(xí)上機(jī)練習(xí)9.7 習(xí)題習(xí)題2022-3-30第34頁9.4 索引維護(hù)索引維護(hù)l索引在創(chuàng)建之后，由于數(shù)據(jù)的增加、刪除、索引在創(chuàng)建之后，由于數(shù)據(jù)的增加、刪除、更新等操作使得索引頁發(fā)生碎塊，為了提更新等操作使得索引頁發(fā)生碎塊，

30、為了提高系統(tǒng)的性能，必須對索引進(jìn)行維護(hù)。高系統(tǒng)的性能，必須對索引進(jìn)行維護(hù)。l這些維護(hù)包括查看碎塊信息、維護(hù)統(tǒng)計信這些維護(hù)包括查看碎塊信息、維護(hù)統(tǒng)計信息、分析索引性能及刪除重建索引等。息、分析索引性能及刪除重建索引等。2022-3-30第35頁查看索引統(tǒng)計信息查看索引統(tǒng)計信息l索引統(tǒng)計信息是查詢優(yōu)化器用來分析和評索引統(tǒng)計信息是查詢優(yōu)化器用來分析和評估查詢、確定最優(yōu)查詢計劃的基礎(chǔ)數(shù)據(jù)。估查詢、確定最優(yōu)查詢計劃的基礎(chǔ)數(shù)據(jù)。一般地，用戶可以通過常用的方式訪問指一般地，用戶可以通過常用的方式訪問指定索引的統(tǒng)計信息。一種方式是使用定索引的統(tǒng)計信息。一種方式是使用DBCC SHOW_STATISTICS命令

31、，另一種命令，另一種是使用圖形化工具。是使用圖形化工具。lDBCC SHOW_STATISTICS命令可以用來命令可以用來返回指定表或視圖的特定對象的統(tǒng)計信息，返回指定表或視圖的特定對象的統(tǒng)計信息，這些特定對象可以是索引、列等。這些特定對象可以是索引、列等。2022-3-30第36頁【例【例9-7】查看索引統(tǒng)計信息】查看索引統(tǒng)計信息 2022-3-30第37頁查看索引的碎片信息查看索引的碎片信息l可以使用兩種方式查看有關(guān)索引的碎片信可以使用兩種方式查看有關(guān)索引的碎片信息，使用息，使用sys.dm_db_index_physical_stats系統(tǒng)函系統(tǒng)函數(shù)和使用圖形化工具。數(shù)和使用圖形化工具。

32、l注意，注意，sys.dm_db_index_physical_stats系統(tǒng)函數(shù)替代了以前版本中的系統(tǒng)函數(shù)替代了以前版本中的DBCC SHOWCONTIG命令。命令。2022-3-30第38頁【例【例9-8】查看索引碎片信息】查看索引碎片信息 2022-3-30第39頁維護(hù)索引統(tǒng)計信息維護(hù)索引統(tǒng)計信息l統(tǒng)計信息是存儲在統(tǒng)計信息是存儲在Microsoft SQL Server中的列中的列數(shù)據(jù)的樣本。數(shù)據(jù)的樣本。l這些數(shù)據(jù)一般地用于索引列，但是還可以為非索這些數(shù)據(jù)一般地用于索引列，但是還可以為非索引列創(chuàng)建統(tǒng)計。引列創(chuàng)建統(tǒng)計。Microsoft SQL Server維護(hù)某一維護(hù)某一個索引關(guān)鍵值的分布統(tǒng)計信息，并且使用這些統(tǒng)個索引關(guān)鍵值的分布統(tǒng)計信息，并且使用這些統(tǒng)計信息來確定在查詢進(jìn)程中哪一個索引