軟件開發魔典從入門到項目實踐項目實踐基礎知識核心應用高級應用行業應用項目實踐軟件開發魔典SQLServer從入門到項目實踐清華大學出版社（超值版）核心技術本課時學習重點了解索引的含義和特點熟悉索引的分類掌握創建索引的方法掌握管理和維護索引的方法聚慕課教育研發中心學習、授課與教學PPT課件認識索引索引的概述索引的優缺點聚慕課教育研發中心學習、授課與教學PPT課件索引的概述索引設計不合理或者缺少索引都會對數據庫和應用程序的性能造成障礙。高效的索引對于獲得良好的性能非常重要。設計索引時，應該考慮以下準則：（1）索引并非越多越好，一個表中如果有大量的索引，不僅占用大量的磁盤空間，而且會影響INSERT、DELETE、UPDATE等語句的性能。因為當表中數據更改的同時，索引也會進行調整和更新。（2）避免對經常更新的表進行過多的索引，并且索引中的列盡可能少。而對經常用于查詢的字段應該創建索引，但要避免添加不必要的字段。（3）數據量小的表最好不要使用索引，由于數據較少，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。（4）在條件表達式中經常用到的、不同值較多的列上建立索引，在不同值少的列上不要建立索引。比如在學生表的【性別】字段上只有【男】與【女】兩個不同值，因此就無須建立索引。如果建立索引，不但不會提高查詢效率，反而會嚴重降低更新速度。（5）當唯一性是某種數據本身的特征時，指定唯一索引。使用唯一索引能夠確保定義的列的數據完整性，提高查詢速度。（6）在頻繁進行排序或分組（即進行GROUPBY或ORDERBY操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。聚慕課教育研發中心學習、授課與教學PPT課件索引的優缺點在數據庫中合理地使用索引可以提高查詢數據的速度，下面介紹索引的優缺點：索引的優點主要有以下幾條：（1）通過創建唯一索引，可以保證數據庫表中每一行數據的唯一性。（2）可以大大加快數據的查詢速度，這也是創建索引的最主要的原因。（3）實現數據的參照完整性，可以加速表和表之間的連接。（4）在使用分組和排序子句進行數據查詢時，也可以顯著減少查詢中分組和排序的時間。索引的缺點主要有以下幾條：（1）創建索引和維護索引要耗費時間，并且隨著數據量的增加所耗費的時間也會增加。（2）索引需要占磁盤空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數據文件更快達到最大文件尺寸。（3）當對表中的數據進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了數據的維護速度。聚慕課教育研發中心學習、授課與教學PPT課件索引的分類1.聚集索引2.非聚集索引3.其他索引聚慕課教育研發中心學習、授課與教學PPT課件聚集索引聚集索引基于數據行的鍵值，在表內排序和存儲這些數據行。每個表只能有一個聚集索引，因為數據行本身只能按一個順序存儲。創建聚集索引時應該考慮以下幾個因素：（1）每個表只能有一個聚集索引。（2）表中的物理順序和索引中行的物理順序是相同的，創建任何非聚集索引之前要首先創建聚集索引，這是因為非聚集索引改變了表中行的物理順序。（3）關鍵值的唯一性使用UNIQUE關鍵字或者由內部的唯一標識符明確維護。（4）在索引的創建過程中，SQLServer臨時使用當前數據庫的磁盤空間，所以要保證有足夠的空間創建聚集索引。聚慕課教育研發中心學習、授課與教學PPT課件非聚集索引非聚集索引具有完全獨立于數據行的結構，使用非聚集索引不用將物理數據頁中的數據按列排序。非聚集索引包含索引鍵值和指向表數據存儲位置的行定位器。可以對表或索引視圖創建多個非聚集索引。通常，設計非聚集索引是為了改善經常使用的、沒有建立聚集索引的查詢的性能。查詢優化器在搜索數據值時，先搜索非聚集索引以找到數據值在表中的位置，然后直接從該位置檢索數據。這使得非聚集索引成為完全匹配查詢的最佳選擇，因為索引中包含所搜索的數據值在表中的精確位置的項。具有以下特點的查詢可以考慮使用非聚集索引：（1）使用JOIN或GROUPBY子句。應為連接和分組操作中所涉及的列創建多個非聚集索引，為任何外鍵列創建一個聚集索引。（2）包含大量唯一值的字段。（3）不返回大型結果集的查詢。創建篩選索引以覆蓋從大型表中返回定義完善的行子集的查詢。（4）經常包含在查詢的搜索條件（如返回完全匹配的WHERE子句）中的列。聚慕課教育研發中心學習、授課與教學PPT課件其他索引除了聚集索引和非聚集索引之外，SQLServer中還提供了其他的索引類型，如表14-1所示。聚慕課教育研發中心學習、授課與教學PPT課件創建索引聚慕課教育研發中心學習、授課與教學PPT課件創建索引的語法格式使用SQL創建聚合索引使用SQL創建非聚合索引使用SQL創建復合索引在SSMS中創建索引創建索引的語法格式使用CREATEINDEX語句可以創建索引，在創建索引的語法中包括了創建聚合索引和非聚合索引兩種方式，用戶可以根據實際需要進行選擇，語法格式如下：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]聚慕課教育研究中心學習、授課與教學PPT課件使用SQL創建聚合索引為了演示創建索引的方法，下面創建一個作者信息數據表authorsinfo，SQL語句如下：USEmydbaseCREATETABLEauthorsinfo( idintIDENTITY(1,1)NOTNULL, namevarchar(20)NOTNULL, gendertinyintNOTNULL, ageintNOTNULL, phonevarchar(15)NULL, remarkvarchar(100)NULL);聚慕課教育研究中心學習、授課與教學PPT課件使用SQL創建聚合索引單擊【執行】按鈕，即可完成數據表的創建，執行結果如圖14-1所示。

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

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

圖14-4創建非聚集組合索引聚慕課教育研究中心學習、授課與教學PPT課件在SSMS中創建索引創建索引的語法中有些關鍵字是比較難記的，這時就可以在SSMS中以界面方式來創建索引了，具體操作步驟如下：步驟1：啟動SSMS并連接到數據庫中，在【對象資源管理器】窗口中，打開【數據庫】節點下面要創建索引的數據表節點，例如這里選擇fruits表，打開該節點下面的子節點，右擊【索引】節點，在彈出的快捷菜單中選擇【新建索引】?【非聚焦索引】菜單命令，如圖14-5所示。步驟2：打開【新建索引】窗口，在【常規】選項卡中，可以配置索引的名稱和是否是唯一索引等，如圖14-6所示。聚慕課教育研究中心學習、授課與教學PPT課件在SSMS中創建索引

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

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

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

圖14-7選擇索引列

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

聚慕課教育研究中心學習、授課與教學PPT課件

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

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

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

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

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

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

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

圖14-23【索引屬性】窗口聚慕課教育研究中心學習、授課與教學PPT課件查看索引的統計信息

索引信息還包括統計信息，這些信息可以用來分析索引性能，更好地維護索引。索引統計信息是查詢優化器用來分析和評估查詢、制定最優查詢方式的基礎數據，用戶可以在SSMS中查看索引統計信息，也可以使用DBCCSHOW_STATISTICS命令來查看指定索引的信息。聚慕課教育研究中心學習、授課與教學PPT課件刪除索引刪除索引的語法一次刪除一個索引一次刪除多個索引在SSMS中刪除索引聚慕課教育研究中心學習、授課與教學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]}主要參數介紹如下：index_name項：索引的名稱。database_name項：數據庫的名稱。schema_name項：該表或視圖所屬架構的名稱。table_or_view_name項：與該索引關聯的表或視圖的名稱。聚慕課教育研究中心學習、授課與教學PPT課件

一次刪除一個索引

從刪除索引的語法格式可以看出，在刪除索引時可以一次刪除一個索引，也可以同時刪除多個索引，下面介紹一次刪除一個索引的方法。【例14.9】刪除數據表authorsinfo中的Idx_name索引，輸入語句如下。USEmydbaseDROPINDEXIdx_nameO