數(shù)據(jù)庫(kù)原理及應(yīng)用課件：第8章 索引

數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)踐技術(shù)

（ SQLServer2008）第八章第8章索引8.1基本概念8.2索引存儲(chǔ)結(jié)構(gòu)及類型8.3創(chuàng)建索引2/558.1索引基本概念索引與書籍中的目錄類似。索引使對(duì)數(shù)據(jù)的查找不需要對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)?？梢詾楸碇械膯蝹€(gè)列建立索引，也可以為一組列建立索引。索引一般采用B（balance）樹(shù)結(jié)構(gòu)。3/55索引及數(shù)據(jù)間的對(duì)應(yīng)關(guān)系示意圖4/558.1索引基本概念索引項(xiàng)按數(shù)據(jù)頁(yè)(SQLSerer中為8KB)存儲(chǔ)。表中的全部索引連在一起。5/558.1索引基本概念8.2索引存儲(chǔ)結(jié)構(gòu)及類型采用B-樹(shù)結(jié)構(gòu)來(lái)存儲(chǔ)索引索引頁(yè)存放索引項(xiàng)和指向下一層的指針；數(shù)據(jù)頁(yè)用來(lái)存放數(shù)據(jù)。從索引對(duì)物理數(shù)據(jù)的影響來(lái)劃分，索引分為：聚集索引（ClusteredIndex）非聚集索引（Non-clusteredIndex)6/558.2.1B-樹(shù)結(jié)構(gòu)7/558.2.2索引類型聚集索引非聚集索引唯一索引包含列索引篩選索引√√√√√8/551.聚集索引聚集索引的B-樹(shù)按自下而上建立，最下層的葉級(jí)節(jié)點(diǎn)存放數(shù)據(jù)，它同時(shí)也是數(shù)據(jù)頁(yè)。多個(gè)數(shù)據(jù)頁(yè)生成一個(gè)中間層節(jié)點(diǎn)的索引頁(yè)，然后再由數(shù)個(gè)中間層的節(jié)點(diǎn)的索引頁(yè)合成更上層的索引頁(yè)，如此上推，直到生成頂層的根節(jié)點(diǎn)的索引頁(yè)。

9/55聚集索引的存儲(chǔ)結(jié)構(gòu)示意圖10/55數(shù)據(jù)示例eidenamedeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS聚集索引非聚集索引11employee建立聚集索引CREATECLUSTEREDINDEX

索引名字

ON基本表或視圖名(列名[ASC|DESC][,...n])【例1】在employee表的eid列上建立升序聚集索引createclusteredindexcls_em_idonemployee(eid)101聚簇索引樹(shù)構(gòu)建過(guò)程在eid列上建立聚簇索引葉級(jí)索引、數(shù)據(jù)頁(yè)104110111101104110111203202中間級(jí)根原始數(shù)據(jù)13/55帶有聚集索引的查找過(guò)程SELECT*FROMemployeeWHEREeid='E08'14/55聚集索引說(shuō)明在聚集索引的葉節(jié)點(diǎn)中，數(shù)據(jù)按聚集索引項(xiàng)的值進(jìn)行物理排序。一個(gè)索引可以由多個(gè)列（組合索引）組成。一個(gè)表只能包含一個(gè)聚集索引。15/55下列情況考慮創(chuàng)建聚集索引包含大量非重復(fù)值的列。使用下列運(yùn)算符返回一個(gè)范圍值的查詢：BETWEENAND、>、>=、<和<=。被連續(xù)訪問(wèn)的列。返回大型結(jié)果集的查詢。經(jīng)常被用作連接的列。ORDERBY或GROUPBY子句中指定的列。16/55下列情況不適于建立聚集索引頻繁更改的列。字節(jié)長(zhǎng)的列。17/552.非聚集索引數(shù)據(jù)頁(yè)存儲(chǔ)在一個(gè)地方，非聚集索引頁(yè)存儲(chǔ)在另一個(gè)地方。數(shù)據(jù)不按非聚集索引關(guān)鍵字值的順序排序和存儲(chǔ)。葉級(jí)節(jié)點(diǎn)不是存放數(shù)據(jù)的數(shù)據(jù)頁(yè)。葉級(jí)節(jié)點(diǎn)內(nèi)容：非聚集索引關(guān)鍵字值行定位器18/55非聚集索引的存儲(chǔ)示意圖19/55行定位器在SQLServer中，非聚集索引中的行定位器有兩種：如果該表沒(méi)有定義聚集索引，則行定位器就是指向行的指針。如果該表定義有聚集索引，則行定位器就是該行的聚集索引關(guān)鍵字的值。20/55建立非聚集索引CREATENONCLUSTEREDINDEX索引名

ON基本表或視圖名(列名[ASC|DESC][,...n])

with(DROP_EXISTING={ON|OFF})【例2】在employee表的eid列上建立升序非聚集索引createnonclusteredindexnoc_em_idonemployee(eid)with(drop_exiting=on)101104110111在eid列上建立非聚集索引的情形原始數(shù)據(jù)頁(yè)葉級(jí)索引201202220227302303中間級(jí)根41022/55在有聚集索引的表上建立非聚集索引的情形假設(shè)在eid列上建立有聚集索引，在ename列上建立非聚集索引聚簇索引葉級(jí)、數(shù)據(jù)頁(yè)非聚簇索引葉級(jí)非聚簇索引中間級(jí)非聚簇索引根10110212012120220330050150250851023/55數(shù)據(jù)的查找在無(wú)索引數(shù)據(jù)表上的查找在有聚集索引數(shù)據(jù)表上的查找在只有非聚集索引數(shù)據(jù)表上的查找在有聚集索引和非聚集索引的表上查找24/55在無(wú)索引數(shù)據(jù)表上的查找SELECTenameFROMemployeesWHEREeid=‘e08’AD結(jié)果：25在有聚集索引數(shù)據(jù)表上的查找101104110111101104110111SELECTenameFROMemployeesWHEREeid=‘e08’26在只有非聚集索引的表上查找SELECTenameFROMemployeesWHEREeid=‘e08’27在有聚集索引和非聚集索引的表上查找SELECTeid,deptFROMemployeesWHEREename='BC'（1）在非聚集索引上查找E0428在有聚集索引和非聚集索引的表上查找(續(xù))SELECTeid,deptFROMemployeesWHEREeid=‘E04'（2）在聚集索引上查找29下述情況可考慮建立非聚集索引包含大量非重復(fù)值的列。不返回大型結(jié)果集的查詢。經(jīng)常作為查詢條件使用的列。經(jīng)常作為連接和分組條件的列。30/553.唯一索引確保索引列不包含重復(fù)值。當(dāng)在表上創(chuàng)建PRIMARYKEY約束或UNIQUE約束時(shí)，系統(tǒng)會(huì)自動(dòng)在這些列上創(chuàng)建唯一索引。在組合唯一索引的情況下，可以確保索引列中每個(gè)值的組合都是唯一的。聚集索引和非聚集索引都可以是唯一的。CREATEuniqueINDEXindex_nameONtable_or_view_name(column[ASC|DESC][,...n])31/554．包含列索引SELECTdeptfromemployeesWHEREeid='E02'假設(shè)employees只在eid列上建有一個(gè)非聚集索引，沒(méi)有聚集索引。根據(jù)行定位器值到數(shù)據(jù)頁(yè)中查找該職工所在部門。如果擴(kuò)展葉級(jí)索引項(xiàng)內(nèi)容，可以省略最后一步從葉級(jí)索引到數(shù)據(jù)頁(yè)的查找。這種將非索引關(guān)鍵字也放置到非聚集索引葉級(jí)的索引就稱為包含列索引。eiddept行定位器32/55建立包含列索引的好處非鍵列可以是不允許作為索引鍵列的數(shù)據(jù)類型（如text、ntext、varchar(max)等）。在計(jì)算索引鍵列個(gè)數(shù)和索引鍵大小時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)不考慮這些非索引鍵列。33/555．篩選索引篩選索引是一種經(jīng)過(guò)優(yōu)化的非聚集索引，適用于涵蓋從定義完善的數(shù)據(jù)子集中選擇數(shù)據(jù)的查詢。篩選索引使用篩選謂詞對(duì)表中的部分?jǐn)?shù)據(jù)行進(jìn)行索引。與對(duì)全表建立索引相比，設(shè)計(jì)良好的篩選索引可以提高查詢性能、減少索引維護(hù)開(kāi)銷并可降低索引存儲(chǔ)開(kāi)銷。34/55適宜構(gòu)建篩選索引的情況僅包含少量非NULL值的稀疏列。包含多種類別數(shù)據(jù)的異類列。包含多個(gè)范圍值（如金額、時(shí)間和日期）的列。由列值的簡(jiǎn)單比較邏輯定義的分區(qū)表。35/558.3創(chuàng)建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameONtable_or_view_name(column[ASC|DESC][,...n])[INCLUDE(column_name[,...n])][WHERE<filter_predicate>][WITH(<relational_index_option>[,...n])][ON{partition_scheme_name(column_name)|filegroup_name|default

}][;]36/55<relational_index_option>::={PAD_INDEX={ON|OFF}|FILLFACTOR=fillfactor|IGNORE_DUP_KEY={ON|OFF}|DROP_EXISTING={ON|OFF}|ONLINE={ON|OFF}}37/558.3創(chuàng)建索引【例1】在Student表的Sname列上創(chuàng)建一個(gè)非聚集索引。CREATEINDEXIX_Student_SnameONStudent(Sname)38/558.3創(chuàng)建索引【例2】

使用DROP_EXISTING刪除和重新創(chuàng)建索引。在Student表的Sname列上刪除并重新創(chuàng)建索引，同時(shí)將葉級(jí)索引頁(yè)和中間級(jí)索引頁(yè)的充滿度設(shè)置為80%。CREATENONCLUSTEREDINDEXIX_Student_SnameONStudent(Sname)WITH(FILLFACTOR=80,PAD_INDEX=ON,DROP_EXISTING=ON)39/558.3創(chuàng)建索引示例【例3】

使用包含列以避免索引大小限制。通過(guò)在非聚集索引中包含非索引鍵列，來(lái)避免超過(guò)索引大小限制（最大鍵列數(shù)16，最大索引鍵大小900字節(jié)）Titlenvarchar(50)Revisionnchar(5)FileNamenvarchar(400)CREATEINDEXIX_Document_TitleONProduction.Document(Title,Revision)INCLUDE(FileName)40/55示例【例4】

為下列查詢?cè)O(shè)計(jì)索引建議。SELECTAddressLine1,AddressLine2,City,

StateProvinceID,PostalCodeFROMPerson.AddressWHEREPostalCodeBETWEEN'98000'and'99999'設(shè)計(jì)覆蓋查詢的索引：

CREATEINDEXIX_Address_PostalCodeONPerson.Address(PostalCode)INCLUDE(AddressLine1,AddressLine2,City,StateProvinceID);41/55刪除索引DROPINDEXindex_nameONtable_or_view_name【例5】刪除Student表中的Sname_ind索引。DROPINDEXIdx_SnameONStudent42/55設(shè)計(jì)索引注意事項(xiàng)對(duì)于聚集索引，應(yīng)保持較短的索引鍵長(zhǎng)度。不能將ntext、text、image、varchar(max)、nvarchar(max)和varbinary(max)數(shù)據(jù)類型的列指定為索引鍵列。對(duì)具有定義完善的子集的列（如，稀疏列、大部分值為NULL的列、含各類值的列以及含不同范圍的值的列）可以考慮使用篩