《數(shù)據(jù)庫(kù)原理與應(yīng)用》09.索引的創(chuàng)建和使用

孫發(fā)勤

揚(yáng)州大學(xué)教育科學(xué)與技術(shù)學(xué)院YZU.SUN@163.COM《數(shù)據(jù)庫(kù)原理與應(yīng)用》

第九講索引的創(chuàng)建和使用本章的學(xué)習(xí)要點(diǎn):一.索引的概念和優(yōu)點(diǎn)二.索引的分類三.創(chuàng)建索引的方法四.對(duì)索引進(jìn)行管理索引概述一.索引的概念:

索引是由表中的一列或若干列字段值以及相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁的邏輯指針構(gòu)成的.索引概述一.索引的優(yōu)點(diǎn):

使用索引對(duì)數(shù)據(jù)進(jìn)行各種操作可以極大的提高系統(tǒng)性能,尤其是在數(shù)據(jù)查詢方面.1.利用索引的數(shù)據(jù)查詢索引概述2.不利用索引的數(shù)據(jù)查詢（直接查找）系統(tǒng)直接在數(shù)據(jù)表中查詢，從表的第一條記錄開始，依次掃描比較數(shù)據(jù)表中的所有記錄，直到找到所需的記錄或掃描完數(shù)據(jù)表的所有記錄。索引概述二.索引的缺點(diǎn):1.創(chuàng)建的索引文件將要占用一定容量的存儲(chǔ)空間.2.當(dāng)每次對(duì)數(shù)據(jù)表進(jìn)行更新時(shí),數(shù)據(jù)庫(kù)還要執(zhí)行額外的操作來維護(hù)索引.降低對(duì)數(shù)據(jù)表的更新速度.總結(jié):利用索引可以提高對(duì)表的查詢速度,但是會(huì)降低對(duì)表的更新速度,所以過多的索引不一定能提高系統(tǒng)的性能.我們必須科學(xué)的設(shè)計(jì)索引,才能帶來數(shù)據(jù)庫(kù)性能的提高.索引概述三.建立索引需參照的原則(一)建議創(chuàng)建索引的列1.在經(jīng)常用來檢索的列上創(chuàng)建索引(如經(jīng)常在WHERE、GROUPBY、ORDERBY子句中出現(xiàn)的列).2.在表的主鍵、外鍵上創(chuàng)建索引.3.在聚集索引中，避免包括不必要的列，盡可能使用較小的數(shù)據(jù)類型，例如用VARCHAR代替CHAR。索引概述三.建立索引需參照的原則(一)建議創(chuàng)建索引的列4.在經(jīng)常用于表間連接的字段上建立索引.5.在條件表達(dá)式中經(jīng)常用到的不同值較多的字段上建立索引，在不同值較少的字段上不要建立索引.6.如果經(jīng)常同時(shí)使用多個(gè)字段排序，可以在這些字段上建立復(fù)合索引。7.具有高選擇性的列是索引的好候選列，具有高密度的列是索引最糟糕的候選列。索引概述(二)一般不考慮用于創(chuàng)建索引的列.1.在查詢中幾乎不涉及的列2.包含太多重復(fù)值的列(如性別)3.當(dāng)數(shù)據(jù)表的讀寫性能比查詢更重要時(shí),應(yīng)少建或不建索引.4.數(shù)據(jù)類型為TEXT、NTEXT和IMAGE的列（不能用于創(chuàng)建索引）。索引分類一.從數(shù)據(jù)表存儲(chǔ)結(jié)構(gòu)的角度分類:1.聚集索引2.非聚集索引二.從索引字段是否允許存在重復(fù)值來分類:1.唯一索引2.普通索引三.從創(chuàng)建索引依據(jù)的字段個(gè)數(shù)來分類:1.單字段索引2.復(fù)合索引(多字段索引，最多可包含16個(gè)字段，但列的數(shù)據(jù)類型的長(zhǎng)度之和不能超過900個(gè)字節(jié))聚集索引一.概念:聚集索引將表中的記錄在物理數(shù)據(jù)頁中的位置按索引字段值重新排列.二.創(chuàng)建聚集索引的注意事項(xiàng):1.每個(gè)表只能有一個(gè)聚集索引2.因?yàn)榫奂饕淖兞吮碇行械奈锢眄樞?所以在創(chuàng)建任何非聚集索引之前要首先創(chuàng)建聚集索引.3.在聚集索引的創(chuàng)建過程中,SQLSERVER會(huì)臨時(shí)使用當(dāng)前數(shù)據(jù)庫(kù)的磁盤空間(大約為原表的1.2倍),所以要保證有足夠的空間創(chuàng)建聚集索引.非聚集索引一.概念:非聚集索引與表中數(shù)據(jù)行的實(shí)際存儲(chǔ)結(jié)構(gòu)無關(guān),非聚集索引存儲(chǔ)的是索引關(guān)鍵字的值與記錄的存儲(chǔ)位置的對(duì)應(yīng)關(guān)系.二.創(chuàng)建非聚集索引的注意事項(xiàng):1.使用非聚集索引的查詢速度要比使用聚集索引的查詢速度慢.2.一個(gè)表可以創(chuàng)建多個(gè)非聚集索引(最多可達(dá)249個(gè)).3.當(dāng)刪除、新建或重建聚集索引時(shí)，系統(tǒng)會(huì)自動(dòng)更新所有的非聚集索引。創(chuàng)建索引一.由系統(tǒng)自動(dòng)創(chuàng)建例如:當(dāng)創(chuàng)建主鍵約束或唯一性約束時(shí)系統(tǒng)會(huì)自動(dòng)的創(chuàng)建唯一性索引.二.用企業(yè)管理器創(chuàng)建索引打開企業(yè)管理器——右擊需創(chuàng)建索引的表—所有任務(wù)——管理索引——……創(chuàng)建索引二.用企業(yè)管理器創(chuàng)建索引注意事項(xiàng):在索引頁已填滿時(shí),SQLSERVER就必須花時(shí)間拆分該索引頁,以便為新行騰出空間.選擇合適的填充引子值可以在葉級(jí)索引頁上分配出一定百分比的可用空間,以減少頁拆分.提高數(shù)據(jù)庫(kù)的性能.1.填充索引:用于指定索引中間級(jí)即非葉節(jié)點(diǎn)中每個(gè)頁(節(jié)點(diǎn))上保持開放的空間.其值使用填充因子所指定的大小.2.填充因子:在創(chuàng)建索引時(shí)用于指定每個(gè)索引頁的數(shù)據(jù)占索引頁大小的百分比.創(chuàng)建索引二.用企業(yè)管理器創(chuàng)建索引填充因子大小的設(shè)置原則:1.對(duì)于那些頻繁進(jìn)行大量數(shù)據(jù)插入或刪除的表,填充因子可大約設(shè)為10.2.對(duì)數(shù)據(jù)不更改、只讀的表,填充因子可以設(shè)到95以上.創(chuàng)建索引三.利用TRANSACT-SQL語句創(chuàng)建索引語法格式:CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXINDEX_NAMEON{TABLE|VIEW}(COLUMNNAME[ASC|DESC][……])[ONFILEGROUPNAME]創(chuàng)建索引三.利用TRANSACT-SQL語句創(chuàng)建索引例1：為AAA數(shù)據(jù)庫(kù)中的學(xué)生表創(chuàng)建一個(gè)唯一聚集索引CC，依據(jù)學(xué)號(hào)字段進(jìn)行降序排列。USEAAACREATEUNIQUECLUSTEREDINDEXCCON學(xué)生表（學(xué)號(hào)DESC）創(chuàng)建索引三.利用TRANSACT-SQL語句創(chuàng)建索引例2：CREATEINDEXDDDON成績(jī)表（語文，數(shù)學(xué)DESC，英語）創(chuàng)建索引三.利用TRANSACT-SQL語句創(chuàng)建索引例3:先接入學(xué)成績(jī)降序再按出生日期升序?qū)閷W(xué)生表創(chuàng)建聚集索引,索引名為Q.刪除索引語法：DROPINDEX表名.索引名|視圖名.索引名[,...n]例:CREATEUNIQUEINDEXAON學(xué)生表(姓名)DROPINDEX學(xué)生表.A刪除索引關(guān)于刪除索引的注意事項(xiàng):1.執(zhí)行DROPINDEX語句后，SQLServer收回被索引占用的磁盤空間.2.不能用DROPINDEX語句刪除PRIMARYKEY約束或UNIQUE約束創(chuàng)建的索引。必須先刪除約束，使索引自動(dòng)刪除.3.刪除表的時(shí)候，表上的所有索引亦同時(shí)被刪除4.刪除聚集索引的時(shí)候，所有表上的非聚集索引都會(huì)自動(dòng)被重建.5.DROPINDEX語句不能在系統(tǒng)表上使用如何獲取某表現(xiàn)有索引的信息1、在企業(yè)管理器中直接查看2、EXECsp_helpindex表名該系統(tǒng)存儲(chǔ)過程返回指定表的所有索引信息3、EXECsp_help表名該系統(tǒng)存儲(chǔ)過程返回指定表的所有相關(guān)信息索引優(yōu)化向?qū)б?概念：索引優(yōu)化向?qū)荢QLSERVER提供的創(chuàng)建索引的工具,它使用查詢優(yōu)化器分析工作負(fù)荷中的查詢?nèi)蝿?wù),向有大量工作負(fù)荷的數(shù)據(jù)庫(kù)推薦最佳的索引混合方式,以加快