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

索引和查詢優(yōu)化4/23/20231第1頁，共20頁，2023年，2月20日，星期六教學目標理解索引的優(yōu)點和缺點理解堆的結(jié)構(gòu)特點理解聚集索引和非聚集索引的特點理解索引的類型使用CREATEINDEX語句創(chuàng)建索引的方式理解索引統(tǒng)計信息的特點和獲得方式理解查詢優(yōu)化的方式4/23/20232第2頁，共20頁，2023年，2月20日，星期六教學過程8.1概述8.2索引的類型和特點8.3創(chuàng)建索引8.4索引維護8.5查詢優(yōu)化4/23/20233第3頁，共20頁，2023年，2月20日，星期六8.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ù)的這種堆放方式稱為堆。當一個數(shù)據(jù)頁上的數(shù)據(jù)堆放滿之后，數(shù)據(jù)就得堆放在另外一個數(shù)據(jù)頁上，這時就稱為頁分解。索引是一種與表或視圖關(guān)聯(lián)的物理結(jié)構(gòu)，可以用來加快從表或視圖中檢索數(shù)據(jù)行的速度。4/23/20234第4頁，共20頁，2023年，2月20日，星期六8.2索引的類型和特點在MicrosoftSQLServer2005系統(tǒng)中，有兩種基本的索引類型：聚集索引和非聚集索引。除此之外，還有惟一性索引、包含性列索引、索引視圖、全文索引、XML索引等。在這些索引類型中，聚集索引和非聚集索引是數(shù)據(jù)庫引擎中索引的基本類型，是理解惟一性索引、包含性列索引、索引視圖的基礎(chǔ)，本節(jié)主要研究者兩種索引類型。另外，為了更好地理解索引結(jié)構(gòu)，有必要對堆結(jié)構(gòu)有所了解。最后，簡單介紹一下系統(tǒng)訪問數(shù)據(jù)的方式。4/23/20235第5頁，共20頁，2023年，2月20日，星期六堆堆是不含聚集索引的表，表中的數(shù)據(jù)沒有任何的順序。堆的信息記錄在sys.partitions目錄視圖中。每一個堆都可能有多個不同的分區(qū)，每一個分區(qū)都有一個堆結(jié)構(gòu)，每一個分區(qū)在sys.partitions目錄視圖中都有一行，且index_id=0。也就是說，每一個堆都可能有多個堆結(jié)構(gòu)。4/23/20236第6頁，共20頁，2023年，2月20日，星期六聚集索引聚集索引是一種數(shù)據(jù)表的物理順序與索引順序相同的索引，非聚集索引則是一種數(shù)據(jù)表的物理順序與索引順序不相同的索引。聚集索引的葉級和非葉級構(gòu)成了一個特殊類型的B樹結(jié)構(gòu)。B樹結(jié)構(gòu)中的每一頁稱為一個索引節(jié)點。索引的最低級節(jié)點是葉級節(jié)點。在一個聚集索引中，某個表的數(shù)據(jù)頁是葉級，在葉級之上的索引頁是非葉級。在聚集索引中，頁的順序是有序的。4/23/20237第7頁，共20頁，2023年，2月20日，星期六非聚集索引非聚集索引與聚集索引具有相同的B樹結(jié)構(gòu)，但是，在非聚集索引中，基礎(chǔ)表的數(shù)據(jù)行不是按照非聚集鍵的順序排序和存儲，且非聚集索引的葉級是由索引頁而不是由數(shù)據(jù)頁組成。非聚集索引既可以定義在表或視圖的聚集索引上，也可以定義在表或視圖的堆上。非聚集索引中的每一個索引行都是由非聚集鍵值和行定位符組成，該行定位符指向聚集索引或堆中包含該鍵值的數(shù)據(jù)行。如果表或視圖中沒有聚集索引(堆)，則行定位符是指向行的指針RID，RID由文件標識符ID、頁碼和頁上的行數(shù)生成。4/23/20238第8頁，共20頁，2023年，2月20日，星期六其他類型的索引除了聚集索引和非聚集索引之外，MicrosoftSQLServer2005系統(tǒng)還提供了一些其他類型的索引或索引表現(xiàn)形式，這些內(nèi)容包括惟一性索引包含性列索引索引視圖全文索引XML索引4/23/20239第9頁，共20頁，2023年，2月20日，星期六訪問數(shù)據(jù)的方式第一種方法是表掃描，就是指系統(tǒng)將指針放在該表的表頭數(shù)據(jù)所在的數(shù)據(jù)頁上，然后按照數(shù)據(jù)頁的排列順序，一頁一頁地從前向后掃描該表數(shù)據(jù)所占有的全部數(shù)據(jù)頁，直至掃描完表中的全部記錄。第二種方法是使用索引查找。4/23/202310第10頁，共20頁，2023年，2月20日，星期六8.3創(chuàng)建索引在MicrosoftSQLServer2005系統(tǒng)中，既可以直接創(chuàng)建索引，也可以間接創(chuàng)建索引。當直接創(chuàng)建索引時，既可以使用CREATEINDEX語句，也可以使用圖形工具。4/23/202311第11頁，共20頁，2023年，2月20日，星期六直接方法和間接方法可以把創(chuàng)建索引的方式分為直接方法和間接方法。直接創(chuàng)建索引的方法就是使用命令和工具直接創(chuàng)建索引。間接創(chuàng)建索引就是通過創(chuàng)建其他對象而附加創(chuàng)建了索引，例如在表中定義主鍵約束或惟一性約束時，同時也創(chuàng)建了索引。雖然，這兩種方法都可以創(chuàng)建索引，但是，它們創(chuàng)建索引的具體內(nèi)容是有區(qū)別的。4/23/202312第12頁，共20頁，2023年，2月20日，星期六使用CREATEINDEX語句在MicrosoftSQLServer2005系統(tǒng)中，使用CREATEINDEX語句可以在關(guān)系表上創(chuàng)建索引4/23/202313第13頁，共20頁，2023年，2月20日，星期六數(shù)據(jù)庫引擎優(yōu)化顧問使用MicrosoftSQLServer2005的數(shù)據(jù)庫引擎優(yōu)化顧問，用戶可以方便地選擇和創(chuàng)建索引、索引視圖和分區(qū)的最佳集合。數(shù)據(jù)庫引擎優(yōu)化顧問分析一個或多個數(shù)據(jù)庫的工作負荷和實現(xiàn)，其中工作負荷是對要優(yōu)化的一個或多個數(shù)據(jù)庫執(zhí)行的一組Transact-SQL語句。數(shù)據(jù)庫引擎優(yōu)化顧問的輸入是由SQLServerProfiler生成的跟蹤文件、指定的跟蹤表或工作負荷。數(shù)據(jù)庫引擎優(yōu)化顧問的輸出是修改數(shù)據(jù)庫的物理設(shè)計結(jié)構(gòu)的建議，其中物理設(shè)計結(jié)構(gòu)包括聚集索引、非聚集索引、索引視圖、分區(qū)等。4/23/202314第14頁，共20頁，2023年，2月20日，星期六查看索引信息在MicrosoftSQLServer2005系統(tǒng)中，可以使用一些目錄視圖和系統(tǒng)函數(shù)查看有關(guān)索引的信息。這些目錄視圖和系統(tǒng)函數(shù)如表8-1所示。4/23/202315第15頁，共20頁，2023年，2月20日，星期六8.4索引維護索引在創(chuàng)建之后，由于數(shù)據(jù)的增加、刪除、更新等操作使得索引頁發(fā)生碎塊，因此為了提高系統(tǒng)的性能，必須對索引進行維護。這些維護包括查看碎塊信息、維護統(tǒng)計信息、分析索引性能、刪除重建索引等。4/23/202316第16頁，共20頁，2023年，2月20日，星期六查看索引統(tǒng)計信息索引統(tǒng)計信息是查詢優(yōu)化器用來分析和評估查詢、確定最優(yōu)查詢計劃的基礎(chǔ)數(shù)據(jù)。一般地，用戶可以通過常用的方式訪問指定索引的統(tǒng)計信息。一種方式是使用DBCCSHOW_STATISTICS命令另一種是使用圖形化工具4/23/202317第17頁，共20頁，2023年，2月20日，星期六查看索引碎片信息可以使用兩種方式查看有關(guān)索引的碎片信息，使用sys.dm_db_index_physical_stats系統(tǒng)函數(shù)和使用圖形化工具。注意，sys.dm_db_index_physical_stats系統(tǒng)函數(shù)替代了以前版本中的DBCCSHOWCONTIG命令。4/23/202318第18頁，共20頁，2023年，2月20日，星期六維護索引統(tǒng)計信息統(tǒng)計信息是存儲在MicrosoftSQLServer中的列數(shù)據(jù)的樣本。這些數(shù)據(jù)一般地用于索引列，但是還可以為非索引列創(chuàng)建統(tǒng)計。MicrosoftSQLServer維護某一個索引關(guān)鍵值的分布統(tǒng)計信息，并且使用這些統(tǒng)計信息來確定在查詢進程中哪一個索引是有用的。查詢的優(yōu)化依賴于這些統(tǒng)計信息的分布準確度。查詢優(yōu)化器使用這些數(shù)據(jù)樣本來決定是使用表掃描還是使用索引。當表中數(shù)據(jù)發(fā)生變化時，MicrosoftSQLServer周期性地自動修改統(tǒng)計信息。索引統(tǒng)計被自動地修改，索引中的關(guān)鍵值顯著變化。4/23/202319第19頁，共20頁，2023年，2月20日，星期六8.5查詢優(yōu)化在很多情況下，為了達到同樣的結(jié)果，可以寫出多個不同的查詢形式。但是，不同的查詢形式往往消耗的時間不相同，因此有不同的性能。如何提高查詢語句的性能呢？下面，介紹MicrosoftSQLServer查詢優(yōu)化器和優(yōu)化隱藏的特點。在查詢語句中，MicrosoftSQ