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

軟件開發(fā)魔典從入門到項目實踐項目實踐基礎(chǔ)知識核心應(yīng)用高級應(yīng)用行業(yè)應(yīng)用項目實踐軟件開發(fā)魔典SQLServer從入門到項目實踐清華大學(xué)出版社（超值版）核心技術(shù)本課時學(xué)習(xí)重點了解索引的含義和特點熟悉索引的分類掌握創(chuàng)建索引的方法掌握管理和維護(hù)索引的方法聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件認(rèn)識索引索引的概述索引的優(yōu)缺點聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件索引的概述索引設(shè)計不合理或者缺少索引都會對數(shù)據(jù)庫和應(yīng)用程序的性能造成障礙。高效的索引對于獲得良好的性能非常重要。設(shè)計索引時，應(yīng)該考慮以下準(zhǔn)則：（1）索引并非越多越好，一個表中如果有大量的索引，不僅占用大量的磁盤空間，而且會影響INSERT、DELETE、UPDATE等語句的性能。因為當(dāng)表中數(shù)據(jù)更改的同時，索引也會進(jìn)行調(diào)整和更新。（2）避免對經(jīng)常更新的表進(jìn)行過多的索引，并且索引中的列盡可能少。而對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引，但要避免添加不必要的字段。（3）數(shù)據(jù)量小的表最好不要使用索引，由于數(shù)據(jù)較少，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產(chǎn)生優(yōu)化效果。（4）在條件表達(dá)式中經(jīng)常用到的、不同值較多的列上建立索引，在不同值少的列上不要建立索引。比如在學(xué)生表的【性別】字段上只有【男】與【女】兩個不同值，因此就無須建立索引。如果建立索引，不但不會提高查詢效率，反而會嚴(yán)重降低更新速度。（5）當(dāng)唯一性是某種數(shù)據(jù)本身的特征時，指定唯一索引。使用唯一索引能夠確保定義的列的數(shù)據(jù)完整性，提高查詢速度。（6）在頻繁進(jìn)行排序或分組（即進(jìn)行GROUPBY或ORDERBY操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件索引的優(yōu)缺點在數(shù)據(jù)庫中合理地使用索引可以提高查詢數(shù)據(jù)的速度，下面介紹索引的優(yōu)缺點：索引的優(yōu)點主要有以下幾條：（1）通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。（2）可以大大加快數(shù)據(jù)的查詢速度，這也是創(chuàng)建索引的最主要的原因。（3）實現(xiàn)數(shù)據(jù)的參照完整性，可以加速表和表之間的連接。（4）在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時，也可以顯著減少查詢中分組和排序的時間。索引的缺點主要有以下幾條：（1）創(chuàng)建索引和維護(hù)索引要耗費時間，并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加。（2）索引需要占磁盤空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達(dá)到最大文件尺寸。（3）當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候，索引也要動態(tài)地維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件索引的分類1.聚集索引2.非聚集索引3.其他索引聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件聚集索引聚集索引基于數(shù)據(jù)行的鍵值，在表內(nèi)排序和存儲這些數(shù)據(jù)行。每個表只能有一個聚集索引，因為數(shù)據(jù)行本身只能按一個順序存儲。創(chuàng)建聚集索引時應(yīng)該考慮以下幾個因素：（1）每個表只能有一個聚集索引。（2）表中的物理順序和索引中行的物理順序是相同的，創(chuàng)建任何非聚集索引之前要首先創(chuàng)建聚集索引，這是因為非聚集索引改變了表中行的物理順序。（3）關(guān)鍵值的唯一性使用UNIQUE關(guān)鍵字或者由內(nèi)部的唯一標(biāo)識符明確維護(hù)。（4）在索引的創(chuàng)建過程中，SQLServer臨時使用當(dāng)前數(shù)據(jù)庫的磁盤空間，所以要保證有足夠的空間創(chuàng)建聚集索引。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件非聚集索引非聚集索引具有完全獨立于數(shù)據(jù)行的結(jié)構(gòu)，使用非聚集索引不用將物理數(shù)據(jù)頁中的數(shù)據(jù)按列排序。非聚集索引包含索引鍵值和指向表數(shù)據(jù)存儲位置的行定位器?？梢詫Ρ砘蛩饕晥D創(chuàng)建多個非聚集索引。通常，設(shè)計非聚集索引是為了改善經(jīng)常使用的、沒有建立聚集索引的查詢的性能。查詢優(yōu)化器在搜索數(shù)據(jù)值時，先搜索非聚集索引以找到數(shù)據(jù)值在表中的位置，然后直接從該位置檢索數(shù)據(jù)。這使得非聚集索引成為完全匹配查詢的最佳選擇，因為索引中包含所搜索的數(shù)據(jù)值在表中的精確位置的項。具有以下特點的查詢可以考慮使用非聚集索引：（1）使用JOIN或GROUPBY子句。應(yīng)為連接和分組操作中所涉及的列創(chuàng)建多個非聚集索引，為任何外鍵列創(chuàng)建一個聚集索引。（2）包含大量唯一值的字段。（3）不返回大型結(jié)果集的查詢。創(chuàng)建篩選索引以覆蓋從大型表中返回定義完善的行子集的查詢。（4）經(jīng)常包含在查詢的搜索條件（如返回完全匹配的WHERE子句）中的列。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件其他索引除了聚集索引和非聚集索引之外，SQLServer中還提供了其他的索引類型，如表14-1所示。聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件創(chuàng)建索引聚慕課教育研發(fā)中心學(xué)習(xí)、授課與教學(xué)PPT課件創(chuàng)建索引的語法格式使用SQL創(chuàng)建聚合索引使用SQL創(chuàng)建非聚合索引使用SQL創(chuàng)建復(fù)合索引在SSMS中創(chuàng)建索引創(chuàng)建索引的語法格式使用CREATEINDEX語句可以創(chuàng)建索引，在創(chuàng)建索引的語法中包括了創(chuàng)建聚合索引和非聚合索引兩種方式，用戶可以根據(jù)實際需要進(jìn)行選擇，語法格式如下：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)建一個作者信息數(shù)據(jù)表authorsinfo，SQL語句如下：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語句創(chuàng)建集合索引，使用CREATEUNIQUECLUSTEREDINDEX語句可以創(chuàng)建唯一性聚集索引。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件使用SQL創(chuàng)建非聚合索引非聚集索引在一張數(shù)據(jù)表中可以存在多個，并且創(chuàng)建非聚集索引時，可以不將其列設(shè)置成唯一索引，創(chuàng)建非聚集索引的SQL語句如下：CREATEUNIQUENONCLUSTEREDINDEX。【例14.2】在authorsinfo表中的name列上，創(chuàng)建一個名稱為Idx_name的唯一非聚集索引，升序排列，填充因子為10%，SQL語句如下。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)建索引時，索引列可以由多列組成，有時也被稱為組合索引?！纠?4.3】在authorsinfo表中的name和gender列上，創(chuàng)建一個名稱為Idx_nameAndgender的唯一非聚集組合索引，升序排列，填充因子為20%，SQL語句如下。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)建索引的語法中有些關(guān)鍵字是比較難記的，這時就可以在SSMS中以界面方式來創(chuàng)建索引了，具體操作步驟如下：步驟1：啟動SSMS并連接到數(shù)據(jù)庫中，在【對象資源管理器】窗口中，打開【數(shù)據(jù)庫】節(jié)點下面要創(chuàng)建索引的數(shù)據(jù)表節(jié)點，例如這里選擇fruits表，打開該節(jié)點下面的子節(jié)點，右擊【索引】節(jié)點，在彈出的快捷菜單中選擇【新建索引】?【非聚焦索引】菜單命令，如圖14-5所示。步驟2：打開【新建索引】窗口，在【常規(guī)】選項卡中，可以配置索引的名稱和是否是唯一索引等，如圖14-6所示。聚慕課教育研究中心學(xué)習(xí)、授課與教學(xué)PPT課件在SSMS中創(chuàng)建索引

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

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

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

圖14-7選擇索引列

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

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

在SSMS中創(chuàng)建索引步驟5：單擊該窗口中的【確認(rèn)】按鈕，返回【對象資源管理器】窗口之后，可以在索引節(jié)點下面看到名稱為Index_name的新索引，說明該索引創(chuàng)建成功，如圖14-9所示。

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

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

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

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

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

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

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

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

一次刪除一個索引

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