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

孫發(fā)勤

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

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

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

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