全書(shū)授課與教學(xué)課件第14章

軟件開(kāi)發(fā)魔典從入門(mén)到項(xiàng)目實(shí)踐項(xiàng)目實(shí)踐基礎(chǔ)知識(shí)核心應(yīng)用高級(jí)應(yīng)用行業(yè)應(yīng)用項(xiàng)目實(shí)踐軟件開(kāi)發(fā)魔典SQLServer從入門(mén)到項(xiàng)目實(shí)踐清華大學(xué)出版社（超值版）核心技術(shù)本課時(shí)學(xué)習(xí)重點(diǎn)了解索引的含義和特點(diǎn)熟悉索引的分類(lèi)掌握創(chuàng)建索引的方法掌握管理和維護(hù)索引的方法聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件認(rèn)識(shí)索引索引的概述索引的優(yōu)缺點(diǎn)聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件索引的概述索引設(shè)計(jì)不合理或者缺少索引都會(huì)對(duì)數(shù)據(jù)庫(kù)和應(yīng)用程序的性能造成障礙。高效的索引對(duì)于獲得良好的性能非常重要。設(shè)計(jì)索引時(shí)，應(yīng)該考慮以下準(zhǔn)則：（1）索引并非越多越好，一個(gè)表中如果有大量的索引，不僅占用大量的磁盤(pán)空間，而且會(huì)影響INSERT、DELETE、UPDATE等語(yǔ)句的性能。因?yàn)楫?dāng)表中數(shù)據(jù)更改的同時(shí)，索引也會(huì)進(jìn)行調(diào)整和更新。（2）避免對(duì)經(jīng)常更新的表進(jìn)行過(guò)多的索引，并且索引中的列盡可能少。而對(duì)經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引，但要避免添加不必要的字段。（3）數(shù)據(jù)量小的表最好不要使用索引，由于數(shù)據(jù)較少，查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果。（4）在條件表達(dá)式中經(jīng)常用到的、不同值較多的列上建立索引，在不同值少的列上不要建立索引。比如在學(xué)生表的【性別】字段上只有【男】與【女】?jī)蓚€(gè)不同值，因此就無(wú)須建立索引。如果建立索引，不但不會(huì)提高查詢效率，反而會(huì)嚴(yán)重降低更新速度。（5）當(dāng)唯一性是某種數(shù)據(jù)本身的特征時(shí)，指定唯一索引。使用唯一索引能夠確保定義的列的數(shù)據(jù)完整性，提高查詢速度。（6）在頻繁進(jìn)行排序或分組（即進(jìn)行GROUPBY或ORDERBY操作）的列上建立索引，如果待排序的列有多個(gè)，可以在這些列上建立組合索引。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件索引的優(yōu)缺點(diǎn)在數(shù)據(jù)庫(kù)中合理地使用索引可以提高查詢數(shù)據(jù)的速度，下面介紹索引的優(yōu)缺點(diǎn)：索引的優(yōu)點(diǎn)主要有以下幾條：（1）通過(guò)創(chuàng)建唯一索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。（2）可以大大加快數(shù)據(jù)的查詢速度，這也是創(chuàng)建索引的最主要的原因。（3）實(shí)現(xiàn)數(shù)據(jù)的參照完整性，可以加速表和表之間的連接。（4）在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時(shí)，也可以顯著減少查詢中分組和排序的時(shí)間。索引的缺點(diǎn)主要有以下幾條：（1）創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會(huì)增加。（2）索引需要占磁盤(pán)空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達(dá)到最大文件尺寸。（3）當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)地維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件索引的分類(lèi)1.聚集索引2.非聚集索引3.其他索引聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件聚集索引聚集索引基于數(shù)據(jù)行的鍵值，在表內(nèi)排序和存儲(chǔ)這些數(shù)據(jù)行。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序存儲(chǔ)。創(chuàng)建聚集索引時(shí)應(yīng)該考慮以下幾個(gè)因素：（1）每個(gè)表只能有一個(gè)聚集索引。（2）表中的物理順序和索引中行的物理順序是相同的，創(chuàng)建任何非聚集索引之前要首先創(chuàng)建聚集索引，這是因?yàn)榉蔷奂饕淖兞吮碇行械奈锢眄樞?。?）關(guān)鍵值的唯一性使用UNIQUE關(guān)鍵字或者由內(nèi)部的唯一標(biāo)識(shí)符明確維護(hù)。（4）在索引的創(chuàng)建過(guò)程中，SQLServer臨時(shí)使用當(dāng)前數(shù)據(jù)庫(kù)的磁盤(pán)空間，所以要保證有足夠的空間創(chuàng)建聚集索引。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件非聚集索引非聚集索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，使用非聚集索引不用將物理數(shù)據(jù)頁(yè)中的數(shù)據(jù)按列排序。非聚集索引包含索引鍵值和指向表數(shù)據(jù)存儲(chǔ)位置的行定位器?？梢詫?duì)表或索引視圖創(chuàng)建多個(gè)非聚集索引。通常，設(shè)計(jì)非聚集索引是為了改善經(jīng)常使用的、沒(méi)有建立聚集索引的查詢的性能。查詢優(yōu)化器在搜索數(shù)據(jù)值時(shí)，先搜索非聚集索引以找到數(shù)據(jù)值在表中的位置，然后直接從該位置檢索數(shù)據(jù)。這使得非聚集索引成為完全匹配查詢的最佳選擇，因?yàn)樗饕邪阉鞯臄?shù)據(jù)值在表中的精確位置的項(xiàng)。具有以下特點(diǎn)的查詢可以考慮使用非聚集索引：（1）使用JOIN或GROUPBY子句。應(yīng)為連接和分組操作中所涉及的列創(chuàng)建多個(gè)非聚集索引，為任何外鍵列創(chuàng)建一個(gè)聚集索引。（2）包含大量唯一值的字段。（3）不返回大型結(jié)果集的查詢。創(chuàng)建篩選索引以覆蓋從大型表中返回定義完善的行子集的查詢。（4）經(jīng)常包含在查詢的搜索條件（如返回完全匹配的WHERE子句）中的列。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件其他索引除了聚集索引和非聚集索引之外，SQLServer中還提供了其他的索引類(lèi)型，如表14-1所示。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件創(chuàng)建索引聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件創(chuàng)建索引的語(yǔ)法格式使用SQL創(chuàng)建聚合索引使用SQL創(chuàng)建非聚合索引使用SQL創(chuàng)建復(fù)合索引在SSMS中創(chuàng)建索引創(chuàng)建索引的語(yǔ)法格式使用CREATEINDEX語(yǔ)句可以創(chuàng)建索引，在創(chuàng)建索引的語(yǔ)法中包括了創(chuàng)建聚合索引和非聚合索引兩種方式，用戶可以根據(jù)實(shí)際需要進(jìn)行選擇，語(yǔ)法格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,..n])[INCLUDE(column_name[,...n])][with(PAD_INDEX={ON|OFF}|FILLFACTOR=fillfactor|SORT_IN_TEMPDB={ON|OFF}|IGNORE_DUP_KEY={ON|OFF}|STATISTICS_NORECOMPUTE={ON|OFF}|DROP_EXISTING={ON|OFF}|ONLINE={ON|OFF}|ALLOW_ROW_LOCKS={ON|OFF}|ALLOW_PAGE_LOCKS={ON|OFF}|MAXDOP=max_degree_of_parallelism)[….n]聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件使用SQL創(chuàng)建聚合索引為了演示創(chuàng)建索引的方法，下面創(chuàng)建一個(gè)作者信息數(shù)據(jù)表authorsinfo，SQL語(yǔ)句如下：USEmydbaseCREATETABLEauthorsinfo( idintIDENTITY(1,1)NOTNULL, namevarchar(20)NOTNULL, gendertinyintNOTNULL, ageintNOTNULL, phonevarchar(15)NULL, remarkvarchar(100)NULL);聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件使用SQL創(chuàng)建聚合索引單擊【執(zhí)行】按鈕，即可完成數(shù)據(jù)表的創(chuàng)建，執(zhí)行結(jié)果如圖14-1所示。

圖14-1創(chuàng)建數(shù)據(jù)表數(shù)據(jù)表創(chuàng)建完成后，下面使用SQL語(yǔ)句創(chuàng)建集合索引，使用CREATEUNIQUECLUSTEREDINDEX語(yǔ)句可以創(chuàng)建唯一性聚集索引。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件使用SQL創(chuàng)建非聚合索引非聚集索引在一張數(shù)據(jù)表中可以存在多個(gè)，并且創(chuàng)建非聚集索引時(shí)，可以不將其列設(shè)置成唯一索引，創(chuàng)建非聚集索引的SQL語(yǔ)句如下：CREATEUNIQUENONCLUSTEREDINDEX?！纠?4.2】在authorsinfo表中的name列上，創(chuàng)建一個(gè)名稱為Idx_name的唯一非聚集索引，升序排列，填充因子為10%，SQL語(yǔ)句如下。CREATEUNIQUENONCLUSTEREDINDEXIdx_nameONauthorsinfo(name)WITHFILLFACTOR=10;聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件使用SQL創(chuàng)建非聚合索引單擊【執(zhí)行】按鈕，即可完成非聚集索引的創(chuàng)建，執(zhí)行結(jié)果如圖14-3所示。

圖14-3創(chuàng)建非聚集索引聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件使用SQL創(chuàng)建復(fù)合索引所謂復(fù)合索引就是指在一張表中創(chuàng)建索引時(shí)，索引列可以由多列組成，有時(shí)也被稱為組合索引?！纠?4.3】在authorsinfo表中的name和gender列上，創(chuàng)建一個(gè)名稱為Idx_nameAndgender的唯一非聚集組合索引，升序排列，填充因子為20%，SQL語(yǔ)句如下。CREATEUNIQUENONCLUSTEREDINDEXIdx_nameAndgenderONauthorsinfo(name,gender)WITHFILLFACTOR=20;單擊【執(zhí)行】按鈕，即可完成非聚集組合索引的創(chuàng)建，執(zhí)行結(jié)果如圖14-4所示。

圖14-4創(chuàng)建非聚集組合索引聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件在SSMS中創(chuàng)建索引創(chuàng)建索引的語(yǔ)法中有些關(guān)鍵字是比較難記的，這時(shí)就可以在SSMS中以界面方式來(lái)創(chuàng)建索引了，具體操作步驟如下：步驟1：?jiǎn)?dòng)SSMS并連接到數(shù)據(jù)庫(kù)中，在【對(duì)象資源管理器】窗口中，打開(kāi)【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)下面要?jiǎng)?chuàng)建索引的數(shù)據(jù)表節(jié)點(diǎn)，例如這里選擇fruits表，打開(kāi)該節(jié)點(diǎn)下面的子節(jié)點(diǎn)，右擊【索引】節(jié)點(diǎn)，在彈出的快捷菜單中選擇【新建索引】?【非聚焦索引】菜單命令，如圖14-5所示。步驟2：打開(kāi)【新建索引】窗口，在【常規(guī)】選項(xiàng)卡中，可以配置索引的名稱和是否是唯一索引等，如圖14-6所示。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件在SSMS中創(chuàng)建索引

圖14-5【新建索引】菜單命令

圖14-6【新建索引】窗口聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件

在SSMS中創(chuàng)建索引步驟3：?jiǎn)螕簟咎砑印堪粹o，打開(kāi)選擇添加索引的列窗口，從中選擇要添加索引的表中的列，這里選擇在數(shù)據(jù)類(lèi)型為varchar的name列上添加索引，如圖14-7所示。步驟4：選擇完之后，單擊【確定】按鈕，返回【新建索引】窗口，\，如圖14-8所示。

圖14-7選擇索引列

圖14-8【新建索引】窗口

聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件

在SSMS中創(chuàng)建索引步驟5：?jiǎn)螕粼摯翱谥械摹敬_認(rèn)】按鈕，返回【對(duì)象資源管理器】窗口之后，可以在索引節(jié)點(diǎn)下面看到名稱為Index_name的新索引，說(shuō)明該索引創(chuàng)建成功，如圖14-9所示。

圖14-9創(chuàng)建非聚集索引成功聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件修改索引修改索引的語(yǔ)法格式禁用不需要的索引重新生成新的索引重命名索引的名稱聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件修改索引的語(yǔ)法格式修改索引的語(yǔ)法格式與創(chuàng)建索引的語(yǔ)法格式有很大的差異，修改索引的語(yǔ)法格式如下：ALTERINDEXindex_nameON{[database_name].table_or_view_name}{[REBUILD][with(<rebuild_index_option>[,...n])][DISABLE][REORGANIZE][PARTITION=partition_number]}主要參數(shù)介紹如下：index_name：要修改索引的名稱。database_name：索引所在數(shù)據(jù)庫(kù)的名稱。table_or_view_name：表或視圖的名稱。REBUILD：使用相同的規(guī)則生成索引。DISABLE：將禁用索引。PARTITION：執(zhí)行將重新組織的索引。從修改索引的語(yǔ)法規(guī)則可以看出，修改索引只是對(duì)原有索引進(jìn)行禁用，重新生成等操作，并不是直接修改原有索引的表或列。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件禁用不需要的索引索引可以幫助用戶提高查詢數(shù)據(jù)的速度，但有時(shí)一張數(shù)據(jù)表中創(chuàng)建了多個(gè)索引，會(huì)造成對(duì)空間的浪費(fèi)，因此，有時(shí)需要將一些暫時(shí)不用的索引禁用掉，當(dāng)再次需要時(shí)再啟用該索引。【例14.4】禁用authorsinfo表中名稱為Idx_nameAndgender的唯一非聚集組合索引，SQL語(yǔ)句如下。USEmydbase;ALTERINDEXIdx_nameAndgenderONauthorsinfoDISABLE;單擊【執(zhí)行】按鈕，即可禁用authorsinfo表中名稱為Idx_nameAndgender的索引，執(zhí)行結(jié)果如圖14-10所示。

圖14-10禁用不要的索引聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件禁用不需要的索引當(dāng)用戶希望使用該索引是，使用啟用的語(yǔ)句啟用該索引即可，啟用的方法是將語(yǔ)句中DISABLE修改為ENABLE即可。那么如何才能知道一個(gè)數(shù)據(jù)表中哪些索引被禁用，哪些索引被啟用呢？這時(shí)可以通過(guò)系統(tǒng)視圖sys.indexes來(lái)查詢就可以了，為了讓讀者能夠明了地查看結(jié)果，可以只查詢其中的索引名稱列（name）和索引是否禁用列（is_disabled），SQL語(yǔ)句如下：USEmydbase;SELECTname,is_disabledFROMsys.indexes;單擊【執(zhí)行】按鈕，即可完成索引是否禁用的查詢操作，執(zhí)行結(jié)果如圖14-11所示，可以看到有些索引列的值為1，有些索引列的值為0，而1代表該索引被禁用，0代表該索引被啟用。

圖14-11查看索引是否被禁用聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件重新生成新的索引重新生成新的索引實(shí)際上就是將原來(lái)的索引刪除掉，再創(chuàng)建一個(gè)新的索引。重新生成新索引的好處是可以減少獲取所請(qǐng)求數(shù)據(jù)所需的頁(yè)讀取數(shù)，以便提高磁盤(pán)性能。重新生成新索引使用的是修改索引語(yǔ)法中的REBUILD關(guān)鍵字來(lái)實(shí)現(xiàn)的?！纠?4.5】在authorsinfo表中重新生成名稱為Idx_nameAndgender的索引。SQL語(yǔ)句如下：USEmydbase;ALTERINDEXIdx_nameAndgenderONauthorsinfoREBUILD;單擊【執(zhí)行】按鈕，即可完成重新生成新索引的操作，執(zhí)行結(jié)果如圖14-12所示。

圖14-12重新生成新的索引聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件重命名索引的名稱使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename可以修改索引的名稱，其語(yǔ)法格式如下。sp_rename'object_name','new_name','object_type'主要參數(shù)介紹如下：object_name：用戶對(duì)象或數(shù)據(jù)類(lèi)型的當(dāng)前限定或非限定名稱。此對(duì)象可以是表、索引、列、別名數(shù)據(jù)類(lèi)型或用戶定義類(lèi)型。new_name：指定對(duì)象的新名稱。object_type：指定修改的對(duì)象類(lèi)型.聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件查詢索引使用系統(tǒng)存儲(chǔ)過(guò)程查詢索引在SSMS中查看索引查看索引的統(tǒng)計(jì)信息聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件使用系統(tǒng)存儲(chǔ)過(guò)程查詢索引使用系統(tǒng)存儲(chǔ)過(guò)程sp_helpindex可以查看數(shù)據(jù)表或視圖中的索引信息，語(yǔ)法格式如下。sp_helpindex[@objname=]'name'其中[@objname=]'name'：用戶定義的表或視圖的限定或非限定名稱。僅當(dāng)指定限定的表或視圖名稱時(shí)，才需要使用引號(hào)。如果提供了完全限定的名稱，包括數(shù)據(jù)庫(kù)名稱，則該數(shù)據(jù)庫(kù)名稱必須是當(dāng)前數(shù)據(jù)庫(kù)的名稱。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件在SSMS中查看索引

除使用系統(tǒng)存儲(chǔ)過(guò)程查詢索引信息外，用戶還可以在SSMS中查詢索引信息，具體的方法為：在【對(duì)象資源管理器】窗口中，打開(kāi)指定數(shù)據(jù)庫(kù)節(jié)點(diǎn)，這里選擇mydbase，然后選擇該數(shù)據(jù)庫(kù)中的數(shù)據(jù)表fruits，并展開(kāi)該表中的索引節(jié)點(diǎn)，選中表中的索引項(xiàng)，這里選擇Index-name_01索引，右擊鼠標(biāo)，在彈出的快捷菜單中選擇【屬性】命令，或雙擊要查看信息的索引，如圖14-23所示。

圖14-22【屬性】菜單命令

圖14-23【索引屬性】窗口聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件查看索引的統(tǒng)計(jì)信息

索引信息還包括統(tǒng)計(jì)信息，這些信息可以用來(lái)分析索引性能，更好地維護(hù)索引。索引統(tǒng)計(jì)信息是查詢優(yōu)化器用來(lái)分析和評(píng)估查詢、制定最優(yōu)查詢方式的基礎(chǔ)數(shù)據(jù)，用戶可以在SSMS中查看索引統(tǒng)計(jì)信息，也可以使用DBCCSHOW_STATISTICS命令來(lái)查看指定索引的信息。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件刪除索引刪除索引的語(yǔ)法一次刪除一個(gè)索引一次刪除多個(gè)索引在SSMS中刪除索引聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件刪除索引的語(yǔ)法使用DROP語(yǔ)句可以刪除索引，刪除索引的語(yǔ)法格式如下：DROPINDEX{index_nameON{[database_name.[schema_name].[schema_name]table_or_view_name}[,…n]|[owner_name.]table_or_view_name.index_name[,…n]}主要參數(shù)介紹如下：index_name項(xiàng)：索引的名稱。database_name項(xiàng)：數(shù)據(jù)庫(kù)的名稱。schema_name項(xiàng)：該表或視圖所屬架構(gòu)的名稱。table_or_view_name項(xiàng)：與該索引關(guān)聯(lián)的表或視圖的名稱。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件

一次刪除一個(gè)索引

從刪除索引的語(yǔ)法格式可以看出，在刪除索引時(shí)可以一次刪除一個(gè)索引，也可以同時(shí)刪除多個(gè)索引，下面介紹一次刪除一個(gè)索引的方法?！纠?4.9】刪除數(shù)據(jù)表authorsinfo中的Idx_name索引，輸入語(yǔ)句如下。USEmydbaseDROPINDEXIdx_nameO