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

《SQL_SERVER_2005之9索引和查詢優(yōu)化》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、第9章索引和查詢優(yōu)化學(xué)習(xí)目標(biāo)本章重點(diǎn)本章內(nèi)容9/1/20211學(xué)習(xí)目標(biāo)數(shù)據(jù)庫管理系統(tǒng)通常使用索引技術(shù)加快對表中數(shù)據(jù)的檢索。索引類似于圖書的目錄。目錄允許用戶不必翻閱整本圖書就能根據(jù)頁數(shù)迅速找到所需內(nèi)容。在數(shù)據(jù)庫中，索引也允許數(shù)據(jù)庫應(yīng)用程序迅速找到表中特定的數(shù)據(jù)，而不必掃描整個數(shù)據(jù)庫。在圖書中，目錄是內(nèi)容和相應(yīng)頁碼的列表清單。在數(shù)據(jù)庫中，索引是表中數(shù)據(jù)和相應(yīng)存儲位置的列表。本章將詳細(xì)研究有關(guān)索引和查詢優(yōu)化的內(nèi)容。9/1/20212本章重點(diǎn)索引的特點(diǎn)索引的類型創(chuàng)建索引索引信息優(yōu)化索引優(yōu)化查詢9/1/20213本章內(nèi)容9.1概述9.2索引的類

2、型和特點(diǎn)9.3創(chuàng)建索引9.4索引維護(hù)9.5查詢優(yōu)化9.6上機(jī)練習(xí)9.7習(xí)題9/1/20214本章內(nèi)容9.1概述9.2索引的類型和特點(diǎn)9.3創(chuàng)建索引9.4索引維護(hù)9.5查詢優(yōu)化9.6上機(jī)練習(xí)9.7習(xí)題9/1/202159.1概述在MicrosoftSQLServer系統(tǒng)中，可管理的最小空間是頁。一個頁是8KB字節(jié)的物理空間。插入數(shù)據(jù)的時候，數(shù)據(jù)就按照插入的時間順序被放置在數(shù)據(jù)頁上。一般地，放置數(shù)據(jù)的順序與數(shù)據(jù)本身的邏輯關(guān)系之間是沒有任何聯(lián)系的。因此，從數(shù)據(jù)之間的邏輯關(guān)系方面來講，數(shù)據(jù)是亂七八糟堆放在一起的。數(shù)據(jù)的這種堆放方式稱為堆。當(dāng)一個

3、數(shù)據(jù)頁上的數(shù)據(jù)堆放滿之后，數(shù)據(jù)就得堆放在另外一個數(shù)據(jù)頁上，這時就稱為頁分解。9/1/20216為什么要創(chuàng)建索引呢？索引是一種與表或視圖關(guān)聯(lián)的物理結(jié)構(gòu)，可以用來加快從表或視圖中檢索數(shù)據(jù)行的速度。為什么要創(chuàng)建索引呢？這是因?yàn)閯?chuàng)建索引可以大大提高系統(tǒng)的性能。第一，通過創(chuàng)建唯一性索引，可以保證每一行數(shù)據(jù)的唯一性。第二，可以大大加快數(shù)據(jù)的檢索速度，這也是索引的最主要的原因。第三，可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。第四，在使用ORDERBY和GROUPBY子句進(jìn)行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間

4、。第五，通過使用索引，可以在查詢的過程中使用優(yōu)化隱藏器，提高系統(tǒng)的性能。正是因?yàn)樯鲜鲞@些原因，所以應(yīng)該對表增加索引。9/1/20217為什么不對表中的每一個列創(chuàng)建一個索引呢？也許會有人要問增加索引有如此多的優(yōu)點(diǎn)，為什么不對表中的每一個列創(chuàng)建一個索引呢？雖然索引有許多優(yōu)點(diǎn)，但是為表中的每一個列都增加索引是非常不明智的做法。這是因?yàn)樵黾铀饕灿衅洳焕囊幻妗５谝?，?chuàng)建索引和維護(hù)索引要耗費(fèi)時間。第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間。如果要建立聚集索引，那么需要的空間就會更大。第三，當(dāng)對表中的數(shù)據(jù)進(jìn)

5、行增加、刪除和修改的時候，索引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。9/1/20218索引是建立在列的上面在經(jīng)常需要搜索的列上創(chuàng)建索引；在主鍵上創(chuàng)建索引；在經(jīng)常用于連接的列上創(chuàng)建索引，也就是在外鍵上創(chuàng)建索引；在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引(因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的)；在經(jīng)常需要排序的列上創(chuàng)建索引(因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間)；在經(jīng)常用在WHERE子句中的列上創(chuàng)建索引。9/1/20219本章內(nèi)容9.1概述9.2索引的類型和特點(diǎn)9.3創(chuàng)建索引9.4索引維護(hù)9.5查詢優(yōu)化9.6

6、上機(jī)練習(xí)9.7習(xí)題9/1/2021109.2索引的類型和特點(diǎn)在MicrosoftSQLServer2005系統(tǒng)中有兩種基本的索引類型：聚集索引和非聚集索引。除此之外，還有唯一性索引、包含性列索引、索引視圖、全文索引及XML索引等。在這些索引類型中，聚集索引和非聚集索引是數(shù)據(jù)庫引擎中索引的基本類型，是理解唯一性索引、包含性列索引、索引視圖的基礎(chǔ)，本節(jié)主要研究這兩種索引類型。另外，為了更好地理解索引結(jié)構(gòu)，有必要對堆結(jié)構(gòu)有所了解。最后，簡單介紹一下系統(tǒng)訪問數(shù)據(jù)的方式。9/1/202111堆堆是不含聚集索引的表，表中的數(shù)據(jù)沒有任何的順序。堆的信

7、息記錄在sys.partitions目錄視圖中。每一個堆都可能有多個不同的分區(qū)，每一個分區(qū)都有一個堆結(jié)構(gòu)，每一個分區(qū)在sys.partitions目錄視圖中都有一行，且index_id=0。也就是說，每一個堆都可能有多個堆結(jié)構(gòu)。9/1/202112堆結(jié)構(gòu)示意圖9/1/202113聚集索引聚集索引是一種數(shù)據(jù)表的物理順序與索引順序相同的索引，非聚集索引則是一種數(shù)據(jù)表的物理順序與索引順序不相同的索引。聚集索引的葉級和非葉級構(gòu)成了一個特殊類型的B樹結(jié)構(gòu)。B樹結(jié)構(gòu)中的每一頁稱為一個索引節(jié)點(diǎn)。索引的最低級節(jié)點(diǎn)是葉級節(jié)點(diǎn)。在一個聚集索引中，某個表的數(shù)

8、據(jù)頁是葉級，在葉級之上的索引頁是非葉級。在聚集索引中，頁的順序是有序的。應(yīng)該在表中經(jīng)常搜索的列或按照順序訪問的列上創(chuàng)建聚集索引。其中，用于指定聚集索引第一頁地址信息的root_page來自sys.syste