單元31：索引

1、單元單元31：索引及其應(yīng)用：索引及其應(yīng)用問題引入問題引入對(duì)于數(shù)據(jù)庫中的海量數(shù)據(jù)來講，查詢效率是至關(guān)對(duì)于數(shù)據(jù)庫中的海量數(shù)據(jù)來講，查詢效率是至關(guān)重要的，那么：重要的，那么：數(shù)據(jù)在磁盤上是如何存儲(chǔ)的？數(shù)據(jù)在磁盤上是如何存儲(chǔ)的？系統(tǒng)如何找到這些數(shù)據(jù)？系統(tǒng)如何找到這些數(shù)據(jù)？問題解決問題解決使用索引加快查詢速度使用索引加快查詢速度實(shí)際的索引示例：查字典實(shí)際的索引示例：查字典每部字典一般都有拼音、偏旁部首等索引，依靠每部字典一般都有拼音、偏旁部首等索引，依靠它們，我們就不必遍歷整部字典，只需按照索引它們，我們就不必遍歷整部字典，只需按照索引找出所在的頁，直接定位到該頁即可。找出所在的頁，直接定位到該頁即可。

2、主要內(nèi)容主要內(nèi)容1索引概述索引概述2創(chuàng)建索引創(chuàng)建索引3管理和維護(hù)索引管理和維護(hù)索引1. 索引概述索引概述數(shù)據(jù)的存儲(chǔ)與訪問數(shù)據(jù)的存儲(chǔ)與訪問 1索引的作用索引的作用 2索引的類型索引的類型 31.1.1 數(shù)據(jù)的存儲(chǔ)數(shù)據(jù)的存儲(chǔ)在在SQL Server中，數(shù)據(jù)存儲(chǔ)的基本單位是：中，數(shù)據(jù)存儲(chǔ)的基本單位是：一個(gè)頁的大小是：一個(gè)頁的大小是：每頁的開始部分是每頁的開始部分是 字節(jié)的頁首，用于存儲(chǔ)字節(jié)的頁首，用于存儲(chǔ)系統(tǒng)信息，如頁的類型、頁的可用容量、擁有頁的系統(tǒng)信息，如頁的類型、頁的可用容量、擁有頁的對(duì)象對(duì)象ID等等數(shù)據(jù)頁中包含除數(shù)據(jù)頁中包含除text、ntext和和image數(shù)據(jù)外的所有數(shù)據(jù)外的所有數(shù)據(jù)，數(shù)

3、據(jù)緊接著頁首順序存放。頁尾有一個(gè)行偏數(shù)據(jù)，數(shù)據(jù)緊接著頁首順序存放。頁尾有一個(gè)行偏移表移表記錄每行第一字節(jié)與頁首的偏移量記錄每行第一字節(jié)與頁首的偏移量行不能跨頁行不能跨頁因此，一行數(shù)據(jù)最多包含的數(shù)據(jù)量是：因此，一行數(shù)據(jù)最多包含的數(shù)據(jù)量是：1.1.2 數(shù)據(jù)的訪問數(shù)據(jù)的訪問SQL Server 提供了兩提供了兩種數(shù)據(jù)訪問的方法：種數(shù)據(jù)訪問的方法：表掃描法表掃描法從表的第從表的第一行開始逐行查找，直一行開始逐行查找，直到找到符合要求的記錄到找到符合要求的記錄為止。為止。顯然，耗費(fèi)時(shí)間與表的顯然，耗費(fèi)時(shí)間與表的數(shù)據(jù)量稱正比。系統(tǒng)響數(shù)據(jù)量稱正比。系統(tǒng)響應(yīng)時(shí)間太長。應(yīng)時(shí)間太長。1.1.2 數(shù)據(jù)的訪問數(shù)據(jù)的訪

4、問索引法索引法當(dāng)表中創(chuàng)建索引后，當(dāng)表中創(chuàng)建索引后，SQL Server將在數(shù)據(jù)表中為將在數(shù)據(jù)表中為其建立索引頁，每個(gè)索引頁中的行都含有指向數(shù)其建立索引頁，每個(gè)索引頁中的行都含有指向數(shù)據(jù)頁的指針。據(jù)頁的指針。類似我們查字典的過程，不必遍歷整本字典，只類似我們查字典的過程，不必遍歷整本字典，只需找到拼音或偏旁（相當(dāng)于建立索引的列）所指需找到拼音或偏旁（相當(dāng)于建立索引的列）所指向的頁即可向的頁即可1.2 索引的作用索引的作用加快數(shù)據(jù)查詢加快數(shù)據(jù)查詢加快表的連接、排序和分組加快表的連接、排序和分組需要占用系統(tǒng)資源需要占用系統(tǒng)資源減慢數(shù)據(jù)修改的速度減慢數(shù)據(jù)修改的速度1.3 索引的分類索引的分類惟一索引：

5、值不重復(fù)惟一索引：值不重復(fù)非惟一索引：允許重復(fù)非惟一索引：允許重復(fù)聚集索引聚集索引:只有一個(gè)，默認(rèn)只有一個(gè)，默認(rèn)為主鍵，對(duì)表物理排序?yàn)橹麈I，對(duì)表物理排序非聚集索引：不對(duì)表進(jìn)行非聚集索引：不對(duì)表進(jìn)行物理排序。物理排序。2. 創(chuàng)建索引創(chuàng)建索引1索引概述索引概述2創(chuàng)建索引創(chuàng)建索引3管理和維護(hù)索引管理和維護(hù)索引2. 創(chuàng)建索引創(chuàng)建索引索引可以在創(chuàng)建表的約束時(shí)由系統(tǒng)自動(dòng)創(chuàng)建，也索引可以在創(chuàng)建表的約束時(shí)由系統(tǒng)自動(dòng)創(chuàng)建，也可以通過可以通過SQL Server Management Studio或或CREATE INDEX語句來創(chuàng)建。語句來創(chuàng)建。在創(chuàng)建或修改表時(shí)，如果添加了一個(gè)主鍵或惟一在創(chuàng)建或修改表時(shí)，如果添

6、加了一個(gè)主鍵或惟一鍵約束，則系統(tǒng)將自動(dòng)在該表上，以該鍵值作為鍵約束，則系統(tǒng)將自動(dòng)在該表上，以該鍵值作為索引列，創(chuàng)建一個(gè)索引列，創(chuàng)建一個(gè)惟一索引惟一索引。主鍵約束列通常也是主鍵約束列通常也是聚集索引聚集索引 【例】客戶信息表中，需要經(jīng)常按【例】客戶信息表中，需要經(jīng)常按“姓名姓名”進(jìn)行進(jìn)行查找，所以需要在姓名列建立索引。姓名有可能查找，所以需要在姓名列建立索引。姓名有可能重名，因此需要建立非惟一索引，同時(shí)因?yàn)橹麈I重名，因此需要建立非惟一索引，同時(shí)因?yàn)橹麈I已經(jīng)是聚集索引，因此要建立非唯一、非聚集索已經(jīng)是聚集索引，因此要建立非唯一、非聚集索引引創(chuàng)建過程創(chuàng)建過程SQL Server Management

7、 Studio中，展開中，展開marketing數(shù)據(jù)庫節(jié)點(diǎn)數(shù)據(jù)庫節(jié)點(diǎn)打開打開“客戶信息客戶信息”的設(shè)計(jì)視圖的設(shè)計(jì)視圖選擇選擇“表和索引屬性表和索引屬性”按鈕，并選擇按鈕，并選擇“索引索引/鍵鍵”2.1 SQL Server Management Studio中創(chuàng)建索引中創(chuàng)建索引2.1 SQL Server Management Studio中創(chuàng)建索引中創(chuàng)建索引系統(tǒng)自動(dòng)命名選擇創(chuàng)建索引的列2.2 查詢分析器中創(chuàng)建索引查詢分析器中創(chuàng)建索引使用使用CREATE INDEX語句創(chuàng)建索引語句創(chuàng)建索引創(chuàng)建索引命令常用格式如下。創(chuàng)建索引命令常用格式如下。CREATEUNIQUECLUSTERED | NON

8、CLUSTEREDINDEX索引名索引名ON表名表名 (字段名字段名,n)WITH 索引選項(xiàng)索引選項(xiàng) ,n ON 文件組文件組 藍(lán)色表示創(chuàng)建索引使用的關(guān)鍵字藍(lán)色表示創(chuàng)建索引使用的關(guān)鍵字2.2 查詢分析器中創(chuàng)建索引查詢分析器中創(chuàng)建索引【例】在訂單信息表中創(chuàng)建名為【例】在訂單信息表中創(chuàng)建名為“IX_訂單信息訂單信息_客戶貨品客戶貨品”的非聚集、復(fù)合索引，該索引基于的非聚集、復(fù)合索引，該索引基于“客戶編號(hào)客戶編號(hào)”和和“貨品編碼貨品編碼”CREATE NONCLUSTERED INDEX IX_訂單信息訂單信息_客戶貨品客戶貨品 ON 訂單信息訂單信息(客戶編號(hào)客戶編號(hào),貨品編號(hào)貨品編號(hào))GO查看訂

9、單信息表的索引信息查看訂單信息表的索引信息EXEC SP_HELPINDEX 訂單信息訂單信息注意事項(xiàng)：注意事項(xiàng)：執(zhí)行執(zhí)行INSERT和和UPDATE語句時(shí)，如果表中有聚集語句時(shí)，如果表中有聚集或惟一索引，則自動(dòng)檢驗(yàn)重復(fù)值或惟一索引，則自動(dòng)檢驗(yàn)重復(fù)值組合索引列相同但順序不同，則索引不同組合索引列相同但順序不同，則索引不同惟一索引將檢驗(yàn)現(xiàn)存數(shù)據(jù)惟一索引將檢驗(yàn)現(xiàn)存數(shù)據(jù)2.2 查詢分析器中創(chuàng)建索引查詢分析器中創(chuàng)建索引（1）用）用SELECT INTO語句生成表語句生成表“高檔貨品高檔貨品”，將貨品信息表中售價(jià)大于平均價(jià)的貨品插入該表將貨品信息表中售價(jià)大于平均價(jià)的貨品插入該表（2）然后為）然后為“高檔

10、貨品高檔貨品”創(chuàng)建基于創(chuàng)建基于“編號(hào)編號(hào)”列的列的惟一索引惟一索引“IX_高檔貨品高檔貨品”（3）驗(yàn)證惟一索引的功能）驗(yàn)證惟一索引的功能（1）SELECT * INTO 高檔貨品高檔貨品 FROM 貨品信息貨品信息WHERE 售價(jià)售價(jià)(SELECT AVG(售價(jià)售價(jià)) FROM 貨品信息貨品信息)（2）CREATE UNIQUE INDEX IX_高檔貨品高檔貨品 ON 高檔貨品高檔貨品(編號(hào)編號(hào)) 2.2 查詢分析器中創(chuàng)建索引查詢分析器中創(chuàng)建索引(3)執(zhí)行如下命令執(zhí)行如下命令I(lǐng)NSERT 高檔貨品高檔貨品SELECT * FROM 貨品信息貨品信息 WHERE 售價(jià)售價(jià)=(SELECT MA

11、X(售價(jià)售價(jià)) FROM 貨品信息貨品信息 )3.管理和維護(hù)索引管理和維護(hù)索引1索引概述索引概述2創(chuàng)建索引創(chuàng)建索引3管理和維護(hù)索引管理和維護(hù)索引3. 管理和維護(hù)索引管理和維護(hù)索引查看和修改索引信息查看和修改索引信息SQL Server Management StudioSP_HELPINDEX刪除索引刪除索引SQL Server Management StudioDROP INDEXDROP INDEX語句不能刪除由主鍵約束和惟一約語句不能刪除由主鍵約束和惟一約束創(chuàng)建的索引，這些索引必須通過刪除約束才能束創(chuàng)建的索引，這些索引必須通過刪除約束才能自動(dòng)刪除。自動(dòng)刪除。刪除聚集索引時(shí)，所有非聚集索引

12、都將重建刪除聚集索引時(shí)，所有非聚集索引都將重建3. 管理和維護(hù)索引管理和維護(hù)索引索引的維護(hù)索引的維護(hù)如何進(jìn)行數(shù)據(jù)查詢?nèi)绾芜M(jìn)行數(shù)據(jù)查詢，即查詢中是否使，即查詢中是否使用索引，使用了哪用索引，使用了哪些索引，都是由查些索引，都是由查詢優(yōu)化器決定，查詢優(yōu)化器決定，查詢優(yōu)化器為每個(gè)查詢優(yōu)化器為每個(gè)查詢生成一個(gè)最佳的詢生成一個(gè)最佳的執(zhí)行計(jì)劃執(zhí)行計(jì)劃用戶對(duì)數(shù)據(jù)庫的插用戶對(duì)數(shù)據(jù)庫的插入、修改和刪除等入、修改和刪除等操作，會(huì)使數(shù)據(jù)變操作，會(huì)使數(shù)據(jù)變得支離破碎，造成得支離破碎，造成索引性能的下降索引性能的下降索引的分析索引的分析l select a.* from dbo.訂單信息訂單信息 a,dbo.客戶信客戶

13、信息息 where 編號(hào)編號(hào)=客戶編號(hào)客戶編號(hào)3.1 索引的分析索引的分析（1）顯示查詢計(jì)劃）顯示查詢計(jì)劃SQL Server提供了兩種顯示查詢中的數(shù)據(jù)處理步提供了兩種顯示查詢中的數(shù)據(jù)處理步驟以及如何訪問數(shù)據(jù)的方式。驟以及如何訪問數(shù)據(jù)的方式。 以圖形方式顯示執(zhí)行計(jì)劃以圖形方式顯示執(zhí)行計(jì)劃客戶訂單查詢的執(zhí)行計(jì)劃客戶訂單查詢的執(zhí)行計(jì)劃 掃描訂單信息表聚集索引的說明掃描訂單信息表聚集索引的說明 3.1 索引的分析索引的分析 以表格方式顯示執(zhí)行計(jì)劃以表格方式顯示執(zhí)行計(jì)劃通過在查詢語句中設(shè)置通過在查詢語句中設(shè)置SHOWPLAN選項(xiàng)，我們選項(xiàng)，我們可以選擇是否讓可以選擇是否讓SQL Server顯示查詢計(jì)

14、劃。顯示查詢計(jì)劃。設(shè)置是否顯示查詢計(jì)劃的命令為：設(shè)置是否顯示查詢計(jì)劃的命令為： SET SHOWPLAN_ALL ON|OFF或或 SET SHOWPLAN_TEXT ON|OFF3.1 索引的分析索引的分析SET SHOWPLAN_ALL ONGOSELECT * FROM 客戶信息客戶信息 A INNER JOIN 訂單訂單信息信息 B ON A.編號(hào)編號(hào)=B.客戶編號(hào)客戶編號(hào)GO 3.1 索引的分析索引的分析（2）數(shù)據(jù)）數(shù)據(jù)I/O統(tǒng)計(jì)統(tǒng)計(jì)通過設(shè)置通過設(shè)置STATISTICS IO,可以顯示磁盤可以顯示磁盤I/O信息信息SET STATISTICS IO ONGOSELECT * FROM

15、 客戶信息客戶信息 A INNER JOIN 訂單訂單信息信息 B ON A.編號(hào)編號(hào)=B.客戶編號(hào)客戶編號(hào)GO 3.2 索引的維護(hù)索引的維護(hù)（1）統(tǒng)計(jì)信息更新）統(tǒng)計(jì)信息更新在在SQL Server Management Studio中通過設(shè)置中通過設(shè)置數(shù)據(jù)庫的屬性決定是否實(shí)現(xiàn)統(tǒng)計(jì)的自動(dòng)更新。數(shù)據(jù)庫的屬性決定是否實(shí)現(xiàn)統(tǒng)計(jì)的自動(dòng)更新。使用使用UPDATE STATISTICS命令更新命令更新“客戶信息客戶信息”表主鍵索引的統(tǒng)計(jì)信息。表主鍵索引的統(tǒng)計(jì)信息。 UPDATE STATISTICS 客戶信息客戶信息 PK_客戶信息客戶信息GO設(shè)置數(shù)據(jù)庫的屬性決定是否實(shí)現(xiàn)統(tǒng)計(jì)的自動(dòng)更新 3.2 索引的維護(hù)

16、索引的維護(hù)（2）使用）使用DBCC SHOWCONTIG語句掃描表語句掃描表對(duì)表進(jìn)行數(shù)據(jù)操作可能會(huì)導(dǎo)致表碎片，而表碎對(duì)表進(jìn)行數(shù)據(jù)操作可能會(huì)導(dǎo)致表碎片，而表碎片會(huì)導(dǎo)致額外的頁讀取，從而造成數(shù)據(jù)庫查詢片會(huì)導(dǎo)致額外的頁讀取，從而造成數(shù)據(jù)庫查詢性能的降低。此時(shí)用戶可以通過使用性能的降低。此時(shí)用戶可以通過使用DBCC SHOWCONTIG語句來掃描表，并通過其返回語句來掃描表，并通過其返回值確定該表的索引頁是否已經(jīng)嚴(yán)重不連續(xù)。值確定該表的索引頁是否已經(jīng)嚴(yán)重不連續(xù)。DBCC SHOWCONFIG(客戶信息客戶信息, PK_客戶信客戶信息息)3.2 索引的維護(hù)索引的維護(hù)3.2 索引的維護(hù)索引的維護(hù)如果頁密度百分比比較低，則需要清理表中碎片如果頁密度百分比比較低，則需要清理表中碎片3.2 索引的維護(hù)索引的維護(hù)（3）使用）使用DBCC INDEXDEFRAG語句進(jìn)行碎片語句進(jìn)行碎片整理整理DBCC INDEXDEFRAG(marketing,客戶信息客戶信息, PK_