數(shù)據(jù)庫第8章-索引課件

1、第8章 索引8.1 基本概念8.2 索引結(jié)構(gòu)及類型類型8.3 創(chuàng)建索引1/558.1 索引基本概念索引與書籍中的目錄類似。索引使對數(shù)據(jù)的查找不需要對整個表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)?？梢詾楸碇械膯蝹€列建立索引，也可以為一組列（索引項(xiàng)）建立索引。索引一般采用B樹結(jié)構(gòu)。2/55索引及數(shù)據(jù)間的對應(yīng)關(guān)系示意圖 3/55索引的組織方式索引項(xiàng)按數(shù)據(jù)頁(一塊固定大小的連續(xù)存儲空間)存儲。表中的全部索引連在一起。4/558.2 索引存儲結(jié)構(gòu)及類型從索引對物理數(shù)據(jù)的影響來劃分，索引分為：聚集索引（Clustered Index）非聚集索引（Non-clustered Index)SQL Server 2

2、008根據(jù)索引的功能，將索引細(xì)分為：聚集索引、非聚集索引、唯一索引、包含列索引、篩選索引等。聚集索引和非聚集索引都采用B-樹結(jié)構(gòu)來存儲索引項(xiàng)，而且都包含數(shù)據(jù)頁和索引頁，其中索引頁存放索引項(xiàng)和指向下一層的指針，數(shù)據(jù)頁用來存放數(shù)據(jù)。5/558.2.1 B-樹結(jié)構(gòu) 6/558.2.2 索引類型聚集索引非聚集索引唯一索引包含列索引篩選索引視圖索引XML索引全文索引7/551. 聚集索引 聚集索引的B-樹按自下而上建立，最下層的葉級節(jié)點(diǎn)存放數(shù)據(jù)，它同時也是數(shù)據(jù)頁。多個數(shù)據(jù)頁生成一個中間層節(jié)點(diǎn)的索引頁，然后再由數(shù)個中間層的節(jié)點(diǎn)的索引頁合成更上層的索引頁，如此上推，直到生成頂層的根節(jié)點(diǎn)的索引頁。 8/55建

3、有聚集索引的表的存儲結(jié)構(gòu)示意圖9/55數(shù)據(jù)示例eidenamedeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS聚集索引非聚集索引10101聚簇索引樹構(gòu)建過程在eid列上建立聚簇索引葉級索引、數(shù)據(jù)頁104110111101104110111203202中間級根原始數(shù)據(jù)11/55查找過程當(dāng)在建有聚集索引的列上查找數(shù)據(jù)時首先從聚集索引樹的入口（根節(jié)點(diǎn)）開始逐層向下查找，直到達(dá)到B-樹索引的葉級，也就是達(dá)到了要找的數(shù)據(jù)所在的數(shù)據(jù)頁，最后只在這個數(shù)據(jù)頁中查找所需數(shù)據(jù)12/55查

4、找示例SELECT * FROM employee WHERE eid=E0813/55說明在聚集索引的葉節(jié)點(diǎn)中，數(shù)據(jù)按聚集索引項(xiàng)的值進(jìn)行物理排序。因此，聚集索引很類似于電話號碼簿。一個表只能包含一個聚集索引。但一個索引可以由多個列（組合索引）組成。14/55下列情況可考慮創(chuàng)建聚集索 包含大量非重復(fù)值的列。使用下列運(yùn)算符返回一個范圍值的查詢：BETWEEN AND、=、 和 =。被連續(xù)訪問的列。返回大型結(jié)果集的查詢。經(jīng)常被用作連接的列。ORDER BY或GROUP BY子句中指定的列。15/55下列情況不適于建立聚集索引頻繁更改的列。字節(jié)長的列。因?yàn)榫奂饕乃饕?xiàng)的值將被所有非聚集索引作為查

5、找關(guān)鍵字使用，并被存儲在每個非聚集索引的B樹的葉級索引項(xiàng)中。16/55非聚集索引非聚集索引與圖書后邊的術(shù)語表類似。數(shù)據(jù)存儲在一個地方，術(shù)語表存儲在另一個地方。而且數(shù)據(jù)并不按術(shù)語表的順序存放，但術(shù)語表中的每個詞在書中都有確切的位置。非聚集索引就類似于術(shù)語表，而數(shù)據(jù)就類似于一本書的內(nèi)容。17/55非聚集索引的存儲示意圖 18/55非聚集索引與聚集索引的差別數(shù)據(jù)不按非聚集索引關(guān)鍵字值的順序排序和存儲。葉級節(jié)點(diǎn)不是存放數(shù)據(jù)的數(shù)據(jù)頁。非聚集索引B樹的葉級節(jié)點(diǎn)是索引行。每個索引行包含非聚集索引關(guān)鍵字值以及一個或多個行定位器，這些行定位器指向該關(guān)鍵字值對應(yīng)的數(shù)據(jù)行（如果索引不唯一，則可能是多行）19/55行

6、定位器非聚集索引可以建立在有聚集索引的表上，也可以建立在無任何索引的表上。在SQL Server中，非聚集索引中的行定位器有兩種形式：如果該表沒有定義聚集索引，則行定位器就是指向行的指針(用文件標(biāo)識符（ID）、頁碼和頁上的行序號生成)。如果該表定義有聚集索引，則行定位器就是該行的聚集索引關(guān)鍵字的值。20/55101104110111在eid列上建有非聚集索引的情形原始數(shù)據(jù)頁葉級索引201202220227302303中間級根41021/55在有聚集索引的表上建立非聚集索引的情形在eid列上建立有聚集索引，在ename列上建立有非聚集索引聚簇索引葉級、數(shù)據(jù)頁非聚簇索引葉級非聚簇索引中間級非聚簇索

7、引根10110212012120220330050150250851022/55數(shù)據(jù)的查找在無索引數(shù)據(jù)表上的查找在有聚簇索引數(shù)據(jù)表上的查找在只有非聚簇索引數(shù)據(jù)表上的查找在有聚集索引和非聚集索引的表上查找23/55在無索引數(shù)據(jù)表上的查找SELECT ename FROM employees WHERE eid = e08AD結(jié)果：24在有聚簇索引數(shù)據(jù)表上的查找101104110111101104110111SELECT ename FROM employees WHERE eid = e0825在只有非聚集索引的表上查找SELECT ename FROM employees WHERE eid

8、= e0826在有聚集索引和非聚集索引的表上查找SELECT eid, dept FROM employees WHERE ename = BC（1）在非聚集索引上查找E0427在有聚集索引和非聚集索引的表上查找(續(xù))SELECT eid, dept FROM employees WHERE eid = E04（2）在聚集索引上查找28下述情況可考慮建立非聚集索引包含大量非重復(fù)值的列。不返回大型結(jié)果集的查詢。經(jīng)常作為查詢條件使用的列。經(jīng)常作為連接和分組條件的列。29/55唯一索引確保索引列不包含重復(fù)值。在組合唯一索引的情況下，可以確保索引列中每個值的組合都是唯一的。例如，如果在last_nam

9、e、first_name和middle_initial列的組合上創(chuàng)建了唯一索引full_name，則該表中任何兩個人都不可以具有完全相同的名字。聚集索引和非聚集索引都可以是唯一的。 30/55說明如果必須要實(shí)施唯一性來確保數(shù)據(jù)的完整性，則應(yīng)在列上創(chuàng)建UNIQUE約束或PRIMARY KEY約束，而不要創(chuàng)建唯一索引。例如，如果限制身份證號碼（sid）列的取值不重復(fù)，則可在sid列上創(chuàng)建UNIQUE約束。實(shí)際上，當(dāng)在表上創(chuàng)建PRIMARY KEY約束或UNIQUE約束時，系統(tǒng)會自動在這些列上創(chuàng)建唯一索引。 31/554包含列索引SELECT dept from employees WHERE ei

10、d=E02如果employees只在eid列上建有一個非聚集索引，沒有聚集索引。當(dāng)查找到葉級索引210頁時匹配 “eid = E02”的匹配數(shù)據(jù)，下一步要做的事情，是取出E02索引關(guān)鍵字對應(yīng)的行定位器值。根據(jù)行定位器值到數(shù)據(jù)頁中查找該職工所在部門。如果擴(kuò)展葉級索引項(xiàng)內(nèi)容，可以省略最后一步從葉級索引到數(shù)據(jù)頁的查找。eiddept行定位器32/55建立包含列索引的好處如果查詢涉及的所有列都包含在索引中，則可以顯著提高查詢性能。因?yàn)镈BMS可直接在索引B-樹中找到所有的列值，而不需要再訪問表或聚集索引數(shù)據(jù)，從而減少磁盤 I/O 操作。因此可考慮擴(kuò)展非聚集索引的葉級，使其除了包含索引關(guān)鍵字外，還包含查

11、詢涉及的列。這種將非索引關(guān)鍵字也放置到非聚集索引葉級的索引就稱為包含列索引。33/55建立包含列索引的好處（續(xù)）非鍵列可以是不允許作為索引鍵列的數(shù)據(jù)類型（有些數(shù)據(jù)類型是不允許建立索引的，比如text等）。 在計算索引鍵列個數(shù)和索引鍵大小時，數(shù)據(jù)庫管理系統(tǒng)不考慮這些非索引鍵列。34/555篩選索引篩選索引是一種經(jīng)過優(yōu)化的非聚集索引，適用于涵蓋從定義完善的數(shù)據(jù)子集中選擇數(shù)據(jù)的查詢。篩選索引使用篩選謂詞對表中的部分?jǐn)?shù)據(jù)行進(jìn)行索引。與對全表建立索引相比，設(shè)計良好的篩選索引可以提高查詢性能、減少索引維護(hù)開銷并可降低索引存儲開銷。35/55設(shè)計篩選索引注意事項(xiàng)為了設(shè)計有效的篩選索引，必須了解應(yīng)用程序使用哪

12、些查詢以及這些查詢與數(shù)據(jù)子集的關(guān)聯(lián)。例如：所含值中大部分為 NULL 的列含異類類別的值的列含不同范圍的值的列都屬于具有定義完善子集的數(shù)據(jù)。36/55篩選索引優(yōu)點(diǎn)提高了查詢性能和計劃質(zhì)量；減少了索引維護(hù)開銷；減少了索引存儲開銷。 37/55適合建立篩選索引的情況當(dāng)只需要查詢列中少量的相關(guān)值時，可以針對值的子集創(chuàng)建篩選索引。異類數(shù)據(jù)的篩選索引。表中含有異類數(shù)據(jù)行時，可以為一種或多種類別的數(shù)據(jù)創(chuàng)建篩選索引。38/55適宜構(gòu)建篩選索引的情況僅包含少量非NULL值的稀疏列。包含多種類別數(shù)據(jù)的異類列。包含多個范圍值（如金額、時間和日期）的列。由列值的簡單比較邏輯定義的分區(qū)表。39/558.3 創(chuàng)建索引C

13、REATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table_or_view_name ( column ASC | DESC ,.n ) INCLUDE ( column_name ,.n ) WHERE WITH ( ,.n) ON partition_scheme_name ( column_name ) | filegroup_name | default ; 40/55創(chuàng)建索引（續(xù)） := PAD_INDEX = ON | OFF | FILLFACTOR = fillfactor | IGNORE_DUP_KEY =

14、 ON | OFF | DROP_EXISTING = ON | OFF | ONLINE = ON | OFF 41/55創(chuàng)建索引（續(xù)）:= AND := | := column_name IN (constant , ) := column_name constant := IS | IS NOT | = | | != | | = | ! | | = | ! := TO 42/55示例例1.在Student表的Sname列上創(chuàng)建一個非聚集索引。 CREATE INDEX Idx_Sname ON Student (Sname)43/55示例例2.為Sales.SalesPerson表的Sa

15、lesQuota和 SalesYTD 列創(chuàng)建一個非聚集組合索引。CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD)44/55示例例3.為Production.UnitMeasure表的Name列創(chuàng)建一個唯一非聚集索引。CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name)45/55示例例4使用IGNORE_DUP_KEY選項(xiàng)創(chuàng)建索引。比較： CREAT

16、E UNIQUE INDEX UK_Index ON #Student (Sname) WITH (IGNORE_DUP_KEY = ON) CREATE UNIQUE INDEX UK_Index ON #Student (Sname) WITH (IGNORE_DUP_KEY = OFF)46/55示例例5. 使用DROP_EXISTING刪除和重新創(chuàng)建索引。在Student表的Sname列上刪除并重新創(chuàng)建索引，同時將葉級索引頁和中間級索引頁的充滿度設(shè)置為80%。 CREATE NONCLUSTERED INDEX Idx_Sname ON Student(Sname) WITH (FIL

17、LFACTOR = 80, PAD_INDEX = ON, DROP_EXISTING = ON)47/55示例例6. 創(chuàng)建包含列索引。CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)48/55示例例7. 創(chuàng)建篩選索引。篩選條件是EndDate為非NULL的數(shù)據(jù)CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS