數(shù)據(jù)庫教務(wù)系統(tǒng)的索引設(shè)計(jì)

任務(wù)6教務(wù)數(shù)據(jù)庫的索引設(shè)計(jì)

任務(wù)目標(biāo)索引是SQLSERVER中的一種數(shù)據(jù)庫對(duì)象，在數(shù)據(jù)庫大量數(shù)據(jù)中，為迅速查找你需要的數(shù)據(jù)，或排序等需要，在原數(shù)據(jù)表基礎(chǔ)上可建立索引這種輔助文件。通過它可對(duì)數(shù)據(jù)表進(jìn)行排序，迅速查找。合理地設(shè)計(jì)索引可以提高數(shù)據(jù)庫的性能。本章任務(wù)目標(biāo)是學(xué)習(xí)SQLSERVER2005中索引設(shè)計(jì)的相關(guān)知識(shí)，并進(jìn)行教務(wù)數(shù)據(jù)庫索引的建立、索引的查看、索引的修改及索引的刪除等操作。使學(xué)生能認(rèn)識(shí)索引及其優(yōu)缺點(diǎn)，根據(jù)需要合理地進(jìn)行表的索引設(shè)計(jì)，能利用SQL語句或SQLServerManagementStudio進(jìn)行索引的各種操作。返回目錄1學(xué)習(xí)引領(lǐng)為了提高查閱速度，我們并不是從書的第一頁開始順序查找，而是首先查看書的目錄索引，找到需要的這一章節(jié)在目錄中所列的頁碼，然后根據(jù)這一頁碼直接找到需要的章節(jié)。在數(shù)據(jù)庫中，為了從大量的數(shù)據(jù)中迅速找到需要的內(nèi)容，也采用類似于書目錄這樣的索引技術(shù)，使得在執(zhí)行數(shù)據(jù)查詢時(shí)不必掃描整個(gè)數(shù)據(jù)庫，就能迅速查到所需要的內(nèi)容。在了解表的概念、表結(jié)構(gòu)的基礎(chǔ)上，首先先要認(rèn)識(shí)什么是索引，為什么要建立索引，索引的分類、優(yōu)缺點(diǎn)，如何考慮建立索引。通過教務(wù)數(shù)據(jù)庫中的索引操作掌握索引創(chuàng)建、管理的語法。并完成書中的課堂實(shí)踐及習(xí)題來消化理解數(shù)據(jù)庫的索引知識(shí)。返回目錄2任務(wù)組成描述返回目錄3任務(wù)6-1教務(wù)數(shù)據(jù)庫索引創(chuàng)建子任務(wù)1認(rèn)識(shí)索引子任務(wù)內(nèi)容索引的概念和分類。唯一索引的概念。創(chuàng)建索引的優(yōu)、缺點(diǎn)。索引的創(chuàng)建語法。子任務(wù)要求了解索引的概念、存儲(chǔ)方式、查詢的原理。能夠描述創(chuàng)建索引的優(yōu)點(diǎn)和缺點(diǎn)。能學(xué)會(huì)如何考慮一個(gè)列是否建索引。掌握索引SQL創(chuàng)建語法。41.索引的概念

索引是數(shù)據(jù)庫隨機(jī)檢索的常用手段，索引是為了加速對(duì)表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散的存儲(chǔ)結(jié)構(gòu)。SQLServer的索引記錄了表中的關(guān)鍵字，提供了指向表中行的指針。它是一種物理結(jié)構(gòu)，能夠提供一種以一列或多列的值為基礎(chǔ)迅速查找表中行的能力。索引是針對(duì)一個(gè)表而建立的，它是由除存放表的數(shù)據(jù)頁面以外的索引頁面組成的。每個(gè)索引頁面中的行都含有邏輯指針（指向表中的行），在數(shù)據(jù)查詢時(shí)，系統(tǒng)先在索引中尋找匹配的索引值，再按照索引值的對(duì)應(yīng)關(guān)系定位表中行的位置，從而加速檢索物理數(shù)據(jù)。索引可以創(chuàng)建在一列或多列的組合上，就像圖書館的書目可以有多種查詢方式（比如按作者、按出版社等）一樣，也可以在數(shù)據(jù)庫表的多個(gè)列上建立不同的索引。52.索引類型索引是數(shù)據(jù)庫對(duì)象中的一種。根據(jù)索引的順序與數(shù)據(jù)表的物理順序是否相同，可以把索引分成兩種類型：聚集索引與非聚集索引。3.唯一索引的概念要區(qū)別表中的兩行，確定行的唯一性，可采用唯一索引。唯一索引既可以采用聚集索引的結(jié)構(gòu)，又可以采用非聚集索引的結(jié)構(gòu)。唯一索引的特征：①

兩行不能具有相同的索引值。②

能夠進(jìn)行實(shí)體完整性的實(shí)施。③

創(chuàng)建主鍵約束和唯一約束時(shí)系統(tǒng)會(huì)自動(dòng)創(chuàng)建唯一索引。在實(shí)際程序開發(fā)編程中經(jīng)常會(huì)使用唯一索引。如學(xué)生表、教師表等中，會(huì)有很多列的列值需要保證其唯一性，如：有學(xué)生學(xué)號(hào)、教師工號(hào)、課程編號(hào)、系代號(hào)等，可在這些列上創(chuàng)建唯一索引。但如果在某列創(chuàng)建時(shí)，在該列數(shù)據(jù)存在重復(fù)值，系統(tǒng)將會(huì)返回錯(cuò)誤信息。本章首頁64.創(chuàng)建索引的優(yōu)、缺點(diǎn)（1）創(chuàng)建索引的優(yōu)點(diǎn)使用索引可以大大提高系統(tǒng)的性能，其具體表現(xiàn)在：創(chuàng)建唯一索引，保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性?？梢源蟠蠹涌鞌?shù)據(jù)檢索速度，提高系統(tǒng)的性能?？梢约铀俦砼c表之間的連接，這一點(diǎn)在實(shí)現(xiàn)數(shù)據(jù)的參照完整性方面有特別的意義。在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。（2）創(chuàng)建索引的缺點(diǎn)增加索引也有許多不利的方面：創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚集索引，那么需要的空間就會(huì)更大。當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。75.如何考慮一個(gè)列是否建索引（1）考慮建索引的列考慮建索引的列有如下選擇：①在經(jīng)常需要搜索的列上，可以加快搜索的速度。②主鍵在作為主鍵的列上，強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)。③連接中頻繁使用的列在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度。④在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的。⑤在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間。在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。85.如何考慮一個(gè)列是否建索引（2）不考慮建索引的列沒有必要對(duì)表中的所有列都建立索引。建立索引需要時(shí)間和存儲(chǔ)開銷，在進(jìn)行數(shù)據(jù)操作后，維護(hù)索引也要花費(fèi)時(shí)間和空間。所以，能不能創(chuàng)建索引、在哪些列上創(chuàng)建索引，要看建立索引和維護(hù)索引的代價(jià)與因建立索引所節(jié)省的時(shí)間相比哪個(gè)更合算。比如不考慮在以下列上建立索引：①對(duì)于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。這是因?yàn)椋热贿@些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。②對(duì)于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引。這是因?yàn)?，由于這些列的取值很少，例如學(xué)生表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。③對(duì)于那些定義為text,image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因?yàn)椋@些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。④當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。這是因?yàn)?，修改性能和檢索性能是互相矛盾的。當(dāng)增加索引時(shí)，會(huì)提高檢索性能，但是會(huì)降低修改性能。當(dāng)減少索引時(shí)，會(huì)提高修改性能，降低檢索性能。因此，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。⑤數(shù)據(jù)行數(shù)很少的小表一般也沒有必要?jiǎng)?chuàng)建索引。96.索引創(chuàng)建語法利用T-SQL語句中的CREATEINDEX命令創(chuàng)建索引，也可以使用CREATETABLE或ALTERTABLE在創(chuàng)建或修改表時(shí)創(chuàng)建索引。

CREATEINDEX命令既可以創(chuàng)建一個(gè)可改變表的物理順序的聚集索引，也可以創(chuàng)建提高查詢性能的非聚集索引，其語法形式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON{表名|視圖名}（列名[ASC|DESC][,...n]）

[WITH[PAD_INDEX][[,]FILLFACTOR=填充因子百分比][[,]IGNORE_DUP_KEY]

[[,]DROP_EXISTING][[,]STATISTICS_NORECOMPUTE][[,]SORT_IN_TEMPDB]

][ON文件組]10在以上語法形式中： UNIQUE：指定創(chuàng)建的索引是唯一索引。如果不使用這個(gè)關(guān)鍵字，創(chuàng)建的索引就不是唯一索引。 CLUSTERED|NONCLUSTERED：指定被創(chuàng)建索引的類型。使用CLUSTERED創(chuàng)建的是聚集索引；使用NONCLUSTERED創(chuàng)建的是非聚集索引。這兩個(gè)關(guān)鍵字中只能選其中的一個(gè)。 索引名：為新創(chuàng)建的索引指定的名字，必須符合標(biāo)識(shí)符規(guī)則。 表名：創(chuàng)建索引的基表的名字。視圖名：創(chuàng)建索引的視圖的名字。兩者選一。 列名：索引中包含的列的名字。 ASC|DESC：確定某個(gè)具體的索引列是升序還是降序排序。默認(rèn)設(shè)置為ASC升序。 FILLFACTOR：稱為填充因子，它指定創(chuàng)建索引時(shí)，每個(gè)索引頁的數(shù)據(jù)占索引頁大小的百分比，它的值為1到100。對(duì)于那些頻繁進(jìn)行大量數(shù)據(jù)插入或刪除的表在建索引時(shí)應(yīng)該為將來生成的索引數(shù)據(jù)預(yù)留較大的空間，即將它的值設(shè)得較小，否則，索引頁會(huì)因數(shù)據(jù)的插入而很快填滿，并產(chǎn)生分頁，而分頁會(huì)大大增加系統(tǒng)的開銷。但如果設(shè)得過小，又會(huì)浪費(fèi)大量的磁盤空間，降低查詢性能。 11

PAD_INDEX指定填充索引的內(nèi)部節(jié)點(diǎn)的行數(shù)，至少應(yīng)大于等于兩行。PAD_INDEX選項(xiàng)只有在FILLFACTOR選項(xiàng)指定后才起作用。因?yàn)镻AD_INDEX使用與FILLFACTOR相同的百分比。缺省時(shí)，SQLServer確保每個(gè)索引頁至少有能容納一條最大索引行數(shù)據(jù)的空閑空間。如果FILLFACTOR指定的百分比不夠容納一行數(shù)據(jù)SQLServer會(huì)自動(dòng)內(nèi)部更改百分比。 IGNORE_DUP_KEY：此選項(xiàng)控制了當(dāng)往包含于一個(gè)惟一約束中的列中插入重復(fù)數(shù)據(jù)時(shí)SQLServer所作的反應(yīng)。當(dāng)選擇此選項(xiàng)時(shí)，SQLServer返回一個(gè)錯(cuò)誤信息，跳過此行數(shù)據(jù)的插入，繼續(xù)執(zhí)行下面的插入數(shù)據(jù)的操作：當(dāng)沒選擇此選項(xiàng)時(shí)，SQLServer不僅會(huì)返回一個(gè)錯(cuò)誤信息，還會(huì)回滾（RollsBack）整個(gè)INSERT語句。 DROP_EXISTING：刪除先前存在的、與創(chuàng)建索引同名的聚集索引或非聚集索引。 STATISTICS_NORECOMPUTE：指定分布統(tǒng)計(jì)不自動(dòng)更新。需要手動(dòng)執(zhí)行不帶NORECOMPUTE子句的UPDATESTATISTICS命令。 SORT_IN_TEMPDB：指定用于創(chuàng)建索引的分類排序結(jié)果將被存儲(chǔ)到Tempdb

數(shù)據(jù)庫中。如果Tempdb

數(shù)據(jù)庫和用戶數(shù)據(jù)庫位于不同的磁盤設(shè)備上，那么使用這一選項(xiàng)可以減少創(chuàng)建索引的時(shí)間，但它會(huì)增加創(chuàng)建索引所需的磁盤空間。 ON文件組：指定存放索引的文件組。12任務(wù)6-1教務(wù)數(shù)據(jù)庫索引創(chuàng)建子任務(wù)2創(chuàng)建學(xué)生表的唯一性聚集索引子任務(wù)內(nèi)容聚集索引的概念。創(chuàng)建學(xué)生表的唯一性聚集索引。子任務(wù)要求了解聚集索引的概念，知道聚集索引的存儲(chǔ)與特點(diǎn)。能夠利用SQL語句創(chuàng)建聚集索引，注意一些參數(shù)的選擇。了解創(chuàng)建聚集索引的注意點(diǎn)。能夠分析錯(cuò)誤并修改。131.聚集索引概念

數(shù)據(jù)表的物理順序和索引表的順序相同，它根據(jù)表中的一列或多列值的組合排列記錄。聚集索引確定表中數(shù)據(jù)的物理順序。聚集索引類似于電話簿。由于聚集索引規(guī)定數(shù)據(jù)在表中的物理存儲(chǔ)順序，因此一個(gè)表只能包含一個(gè)聚集索引。但該索引可以包含多個(gè)列（組合索引），就像電話簿按姓氏和名字進(jìn)行組織一樣。聚集索引對(duì)于那些經(jīng)常要搜索范圍值的列特別有效。由于聚集索引的順序與數(shù)據(jù)行存放的物理順序相同，使用聚集索引找到包含第一個(gè)值的行后，便可以確保包含后續(xù)索引值的行在物理相鄰。避免每次查詢?cè)摿袝r(shí)都進(jìn)行排序，從而節(jié)省成本。創(chuàng)建聚集索引的幾個(gè)注意事項(xiàng)：每張表只能有一個(gè)聚集索引聚集索引是表的物理順序，所以應(yīng)先建聚集索引，后建非聚集索引定義聚集索引鍵時(shí)使用的列越少越好，主鍵是聚集索引的良好候選者頻繁更改的列不適合創(chuàng)建聚集索引142.創(chuàng)建學(xué)生表index_studentID索引在教務(wù)數(shù)據(jù)庫的Student表上創(chuàng)建一個(gè)名為index_studentID的唯一性聚集索引，索引關(guān)鍵字為studentid，升序，填充因子50%。（1）運(yùn)行MicrosoftSQLServerManagementStudio（2）在對(duì)象資源管理器展開“數(shù)據(jù)庫”選擇“教務(wù)管理”數(shù)據(jù)庫，單擊快捷工具欄中的“新建查詢”按鈕，出現(xiàn)查詢窗口，如圖6-1所示。（3）在查詢窗口如圖6-2所示輸入以下SQL語句：USE教務(wù)管理GOCREATEUNIQUECLUSTEREDINDEXindex_studentIDONStudent(studentidASC)WITHFILLFACTOR=50（4）可先單擊執(zhí)行旁邊的分析按鈕進(jìn)行語法分析。然后單擊“執(zhí)行”按鈕，創(chuàng)建成功則如圖6-3所示。如失敗則可能出現(xiàn)如圖6-4所示，分析可知是該表已存在聚集索引，可刪除后再重新執(zhí)行即可。（5）在對(duì)象資源管理器依次展開“教務(wù)管理”數(shù)據(jù)庫節(jié)點(diǎn)、“Student”表節(jié)點(diǎn)、“索引”節(jié)點(diǎn)就可以看見節(jié)點(diǎn)下已有index_studentID聚集索引，如圖6-5所示。15任務(wù)6-1教務(wù)數(shù)據(jù)庫索引創(chuàng)建子任務(wù)3創(chuàng)建教師表的唯一性非聚集索引子任務(wù)內(nèi)容非聚集索引的概念。創(chuàng)建教師表的唯一性非聚集索引。子任務(wù)要求了解非聚集索引的概念。利用SQL語句創(chuàng)建任務(wù)。了解創(chuàng)建非聚集索引的注意點(diǎn)。能夠分析錯(cuò)誤并修改。161.非聚集索引（NonclusteredIndex）數(shù)據(jù)表的物理順序和索引表的順序不相同，數(shù)據(jù)存儲(chǔ)在一個(gè)地方，索引表存儲(chǔ)在另一個(gè)地方，索引帶有指針指向數(shù)據(jù)的存儲(chǔ)位置。索引是有序的，索引中的項(xiàng)目按索引鍵值的順序存儲(chǔ)。而表中的數(shù)據(jù)并不和索引相同，表中的信息按另一種順序存儲(chǔ)（可以由聚集索引規(guī)定）。對(duì)于非聚集索引而言，表的物理順序與索引順序不同，表的數(shù)據(jù)并不是按照索引列排序的。一個(gè)表可以既有聚集索引又有非聚集索引。在平常有些書籍包含多個(gè)目錄，對(duì)于非聚集索引也是如此。可以為在表中查找數(shù)據(jù)時(shí)常用的每個(gè)列都創(chuàng)建一個(gè)非聚集索引。創(chuàng)建非聚集索引的幾個(gè)注意事項(xiàng)：非聚集索引實(shí)際上是一個(gè)表的邏輯順序。創(chuàng)建索引時(shí)，缺省為非聚集索引。返回精確匹配的查詢的搜索條件（WHERE子句）中經(jīng)常使用的列可考慮建非聚集索引。應(yīng)在聯(lián)接和分組操作中使用的列上創(chuàng)建非聚集索引。172.創(chuàng)建教師表的index_teacher_phone索引在教務(wù)數(shù)據(jù)庫的teacher表上創(chuàng)建一個(gè)名為index_teacher_phone的唯一性非聚集索引，索引關(guān)鍵字為phone，降序，填充因子50%。執(zhí)行步驟基本同上，SQL語句如下：USE教務(wù)管理GOCREATEUNIQUENONCLUSTEREDINDEXindex_teacher_phoneONteacher(phoneDESC)WITHFILLFACTOR=5018任務(wù)6-1教務(wù)數(shù)據(jù)庫索引創(chuàng)建子任務(wù)4創(chuàng)建教師班級(jí)課程表中的復(fù)合索引子任務(wù)內(nèi)容認(rèn)識(shí)復(fù)合索引。創(chuàng)建教師班級(jí)課程表的復(fù)合索引。子任務(wù)要求了解復(fù)合索引的實(shí)際需要。掌握創(chuàng)建復(fù)合索引的語法，能夠使用SQL語句創(chuàng)建復(fù)合索引。191.認(rèn)識(shí)復(fù)合索引有些索引列需要由兩列或更多的列組成，我們把由兩列或更多的列組成的索引稱作“復(fù)合索引”。（1）復(fù)合索引的特點(diǎn)①

索引列為兩列或兩列以上。②

在檢索時(shí)會(huì)將多列作為一個(gè)整體進(jìn)行。③

創(chuàng)建復(fù)合索引中多列的順序可以和表定義的列順序不同。（2）創(chuàng)建復(fù)合索引要注意以下幾個(gè)方面：①

在定義多列時(shí)，排列順序很重要，（列1，列2）上的索引不同于（列2，列1）上的索引。②

使用復(fù)合索引可以減少表上創(chuàng)建索引的數(shù)量。③

在條件查詢中為了優(yōu)化使用復(fù)合索引，一般需要引用復(fù)合索引的第一列。④

在查詢的表中如果有幾列是需要頻繁訪問，可以考慮建復(fù)合索引來提高查詢性能。⑤

在復(fù)合索引中索引的列最多可使用16列組合。202.創(chuàng)建教師班級(jí)課程表的index_class_course_teacher索引在教務(wù)數(shù)據(jù)庫的教師班級(jí)課程表表上創(chuàng)建一個(gè)名為index_class_course_teacher的非聚集復(fù)合索引，索引關(guān)鍵字為classid、courseid、teacherid，升序，填充因子50%。執(zhí)行步驟同上，SQL語句如下：USE教務(wù)管理GOCREATENONCLUSTEREDINDEXindex_class_course_teacherONtechercourseschedule(classidASC,courseidASC,teacheridASC)WITHFILLFACTOR=5021任務(wù)6-1教務(wù)數(shù)據(jù)庫索引創(chuàng)建子任務(wù)5使用SQLServerManagementStudio創(chuàng)建索引子任務(wù)內(nèi)容使用SQLServerManagementStudio來創(chuàng)建索引。創(chuàng)建學(xué)生表的唯一性聚集索引。子任務(wù)要求掌握用SQLServerManagementStudio創(chuàng)建索引的方法和操作。能夠進(jìn)行錯(cuò)誤判斷并修改。22利用SQLServerManagementStudio創(chuàng)建index_studentID索引。（1）運(yùn)行MicrosoftSQLServerManagementStudio。（2）在對(duì)象資源管理器依次展開“數(shù)據(jù)庫”、“教務(wù)管理”數(shù)據(jù)庫節(jié)點(diǎn)、“Student”表節(jié)點(diǎn)。（3）在“索引”節(jié)點(diǎn)上單擊右鍵，選擇“新建索引”項(xiàng)。如圖6-6所示。（4）打開“新建索引”對(duì)話框，輸入索引的名稱：index_studentID，指定索引類型為聚集索引，可能會(huì)出現(xiàn)如圖6-7所示“重新索引”提示對(duì)話框，這時(shí)可單擊“是”刪除現(xiàn)有的聚集索引創(chuàng)建新的聚集索引，也可單擊“否”保留原有索引。（5）如繼續(xù)創(chuàng)建，選擇“唯一”復(fù)選框，如圖6-8所示。（6）單擊“添加”按鈕，打開“選擇列”對(duì)話框，選擇studentid列為需要?jiǎng)?chuàng)建索引的列，如圖6-9所示。（7）單擊“選擇列”對(duì)話框上的“確定”按鈕回到“新建索引”對(duì)話框，在左上方的“選擇頁”內(nèi)容中選擇“選項(xiàng)”，進(jìn)行選項(xiàng)設(shè)置，將“填充因子”設(shè)為50%，所圖6-10所示。（8）也可設(shè)置“存儲(chǔ)”等項(xiàng)目，最后單擊“確定”按鈕創(chuàng)建索引成功，同樣可以如圖6-5中相似看到創(chuàng)建的索引。提示：在確定要建索引，可選擇最適合的索引類型，如：聚集索引還是非聚集索引。唯一索引還是非唯一索引。針對(duì)單列還是針對(duì)多列。索引中的列排序是升序還是降序。23任務(wù)6-1教務(wù)數(shù)據(jù)庫索引創(chuàng)建子任務(wù)6創(chuàng)建教師表的全文索引子任務(wù)內(nèi)容認(rèn)識(shí)全文索引的概念。創(chuàng)建教師表的全文索引。子任務(wù)要求掌握用SQLServerManagementStudio創(chuàng)建全文索引的操作方法。能夠使用創(chuàng)建的全文索引進(jìn)行查詢。241.認(rèn)識(shí)全文索引全文索引為在字符串?dāng)?shù)據(jù)中進(jìn)行復(fù)雜的詞搜索提供有效支持。全文索引存儲(chǔ)關(guān)于重要詞和這些詞在特定列中的位置的信息。全文查詢利用這些信息，可快速搜索包含具體某個(gè)詞或一組詞的行。

全文索引包含在全文目錄中。每個(gè)數(shù)據(jù)庫可以包含一個(gè)或多個(gè)全文目錄。一個(gè)目錄不能屬于多個(gè)數(shù)據(jù)庫，而每個(gè)目錄可以包含一個(gè)或多個(gè)表的全文索引。一個(gè)表只能有一個(gè)全文索引，因此每個(gè)有全文索引的表只屬于一個(gè)全文目錄。

全文目錄和索引不存儲(chǔ)在它們所屬的數(shù)據(jù)庫中。目錄和索引由

Microsoft搜索服務(wù)分開管理。

全文索引必須在基表上定義，而不能在視圖、系統(tǒng)表或臨時(shí)表上定義。

全文索引和普通索引的區(qū)別如下：

表6-1全文索引和普通索引的區(qū)別普通SQL索引全文索引存儲(chǔ)時(shí)受定義它們所在的數(shù)據(jù)庫的控制。存儲(chǔ)在文件系統(tǒng)中，但通過數(shù)據(jù)庫管理。每個(gè)表允許有若干個(gè)普通索引。每個(gè)表只允許有一個(gè)全文索引。當(dāng)對(duì)作為其基礎(chǔ)的數(shù)據(jù)進(jìn)行插入、更新或刪除時(shí)，它們自動(dòng)更新。將數(shù)據(jù)添加到全文索引稱為填充，全文索引可通過調(diào)度或特定請(qǐng)求來請(qǐng)求，也可以在添加新數(shù)據(jù)時(shí)自動(dòng)發(fā)生。不分組。在同一個(gè)數(shù)據(jù)庫內(nèi)分組為一個(gè)或多個(gè)全文目錄。使用SQLServerManagementStudio、向?qū)Щ騎ransact-SQL語句創(chuàng)建和除去。使用SQLServerManagementStudio、向?qū)Щ虼鎯?chǔ)過程創(chuàng)建、管理和除去。252.用SQLServerManagementStudio向?qū)?chuàng)建教師表全文索引（1）啟動(dòng)SQLServerManagementStudio，在“對(duì)象資源管理器”中展開“數(shù)據(jù)庫”以及下面的“教務(wù)管理”數(shù)據(jù)庫。（2）再展開“教務(wù)管理”下的“表”，在“teacher”表上單擊右鍵。出現(xiàn)如圖6-11所示。（3）如右鍵彈出工菜單中“全文索引”選項(xiàng)為不可用。則可以先單擊圖6-11的快捷菜單上“新建查詢”，如圖6-12所示，在出現(xiàn)的查詢窗口中輸入SQL命令：USE教務(wù)管理goEXECsp_fulltext_database'enable'然后單擊工具欄上的“!執(zhí)行”按鈕，為“教務(wù)管理”數(shù)據(jù)庫啟用全文索引。（4）當(dāng)圖6-11彈出菜單中的“全文索引”可選用時(shí)，如圖6-13所示選擇下級(jí)的“定義全文索引”。（5）打開“全文索引向?qū)А睔g迎對(duì)話框如圖6-14所示。單擊“下一步”按鈕。262.用SQLServerManagementStudio向?qū)?chuàng)建教師表全文索引（7）打開“選擇表列”對(duì)話框如圖6-16所示，選擇需要?jiǎng)?chuàng)建全文索引的列，單擊“下一步”按鈕。（8）打開“選擇更改跟蹤”對(duì)話框如圖6-17所示，根據(jù)提示選擇跟蹤方式（這里選“自動(dòng)”），單擊“下一步”按鈕。（9）打開“選擇目錄”對(duì)話框如圖6-18所示，在“名稱”中輸入全文索引目錄名fulltextindex_teacher并指定目錄位置（可用默認(rèn)），單擊“下一步”按鈕。（10）打開“定義填充計(jì)劃”對(duì)話框如圖6-19所示，如需要可進(jìn)行填充計(jì)劃設(shè)置，這里單擊“下一步”按鈕。（11）打開“全文索引向?qū)дf明”對(duì)話框如圖6-20所示，查看信息有無問題，如有可按“上一步”退回進(jìn)行修改，否則單擊“完成”按鈕，開始創(chuàng)建全文索引。（12）出現(xiàn)“全文索引向?qū)нM(jìn)度”對(duì)話框如圖6-21所示，創(chuàng)建成功后單擊“關(guān)閉”按鈕退出。提示：也可用SQL語句來創(chuàng)建全文索引。273.用教師表全文索引查找姓“潘”和姓“李”的教師信息

如圖6-22所示在查詢窗口中輸入查詢命令，單擊“！執(zhí)行”按鈕，結(jié)果在右下方顯示出來。4.全文索引的啟用、禁用和刪除等操作可在如圖6-13中的彈出菜單中選擇不同的命令進(jìn)行操作即可。28課堂實(shí)踐1在major表的majorid列上創(chuàng)建唯一性聚集索引index_majorid。在class表的classroom列上創(chuàng)建唯一性非聚集索引index_classroom。在學(xué)生表的studentname列上創(chuàng)建非聚集索引index_studentname。在教師表的profession列和teachername列上創(chuàng)建復(fù)合非聚集索引index_profession_teachername。創(chuàng)建學(xué)生表的全文索引并進(jìn)行查詢(可上網(wǎng)查找采用SQL語句進(jìn)行創(chuàng)建全文索引的相關(guān)資料，用SQL語句進(jìn)行操作)。根據(jù)教務(wù)管理的查詢需要自行設(shè)計(jì)并創(chuàng)建3-5個(gè)索引。認(rèn)真記錄各題操作后的結(jié)果情況。29任務(wù)6-2教務(wù)數(shù)據(jù)庫索引管理子任務(wù)1用T－SQL語句查看、更名、刪除索引子任務(wù)內(nèi)容用T-SQL語句查看教師表索引。用T-SQL語句更名教師表索引。用T-SQL語句刪除教師表索引子任務(wù)要求了解用T-SQL管理索引的系統(tǒng)存儲(chǔ)過程及使用的命令格式。能夠用系統(tǒng)存儲(chǔ)過程進(jìn)行索引各種管理操作。注：具體操作步驟基本同上，這里列出SQL語句。301.查看教務(wù)數(shù)據(jù)庫中表teacher的索引信息（1）可以用sp_helpindex系統(tǒng)存儲(chǔ)過程查看教師表的索引。USE教務(wù)管理GOsp_helpindexteacher結(jié)果窗口如圖6-23所示。圖6-23

用sp_helpindex查看索引

311.查看教務(wù)數(shù)據(jù)庫中表teacher的索引信息（2）也可以用sp_help系統(tǒng)存儲(chǔ)過程查看教師表的索引。USE教務(wù)管理GOsp_helpteacher結(jié)果窗口如圖6-24所示。圖6-24

用sp_help查看索引322.用系統(tǒng)存儲(chǔ)過程sp_rename將教師表的索引index_profession_teachername重新命名為index_p_tn在創(chuàng)建索引后，重新命名表的索引可以使用sp_rename系統(tǒng)存儲(chǔ)過程來操作。要重命名的索引一般要以“表名.索引名”的形式給出。SQL語句如下：USE教務(wù)管理GOsp_rename

‘teacher.index_profession_teachername‘,‘index_p_tn'如圖6-25

所示可以看到索引名已經(jīng)改變。圖6-25

索引的重新命名333.用DROP語句將教師表的索引“index_p_tn”刪除如果某個(gè)索引已經(jīng)沒有什么用了，可以用DROP語句將其刪除。同樣被刪除的索引也要用“表名.索引名”的形式給出。DROP語句的語法：DROPINDEX表名.索引名[,...n]，SQL語句如下：USE教務(wù)管理GODROPINDEXteacher.index_p_tn

注意：刪除索引時(shí)要注意，如果索引是使用CREATETABLE語句創(chuàng)建，只能用ALTERTABLE語句來刪除索引。34任務(wù)6-2教務(wù)數(shù)據(jù)庫索引管理子任務(wù)2用SQLServerManagementStudio查看、更名、刪除索引子任務(wù)內(nèi)容用SQLServerManagementStudio查看學(xué)生表的索引。用SQLServerManagementStudio重命名學(xué)生表的索引。用SQLServerManagementStudio刪除學(xué)生表的索引。子任務(wù)要求能用SQLServerManagementStudio索引的查看、重命名、刪除管理。351.用SQLServerManagementStudio查看教務(wù)數(shù)據(jù)庫中表student的索引信息（1）運(yùn)行MicrosoftSQLServerManagementStudio。（2）在對(duì)象資源管理器依次展開“數(shù)據(jù)庫”、“教務(wù)管理”數(shù)據(jù)庫節(jié)點(diǎn)、“Student”表節(jié)點(diǎn)、“索引”節(jié)點(diǎn)。（3）在索引“index_studentID”節(jié)點(diǎn)上單擊右鍵，選擇“屬性”，如圖6-26所示。（4）彈出索引屬性對(duì)話框如圖6-27所示，單擊“選擇頁”下的選項(xiàng)，可以查看相應(yīng)的內(nèi)容。也可對(duì)一些屬性進(jìn)行修改，確定可保存退出。362.用SQLServerManagementStudio重命名教務(wù)數(shù)據(jù)庫中表student的索引信息（1）運(yùn)行MicrosoftSQLServerManagementStudio。（2）在對(duì)象資源管理器依次展開“數(shù)據(jù)庫”、“教務(wù)管理”數(shù)據(jù)庫節(jié)點(diǎn)、“Student”表節(jié)點(diǎn)、“索引”節(jié)點(diǎn)。（3）在某個(gè)具體的索引節(jié)點(diǎn)上單擊右鍵，如圖6-26類似，選擇“重命名”后直接在名字上編輯即可。373.用SQLServerManagementStudio刪除教務(wù)數(shù)據(jù)庫中表student的索引信息（1）運(yùn)行MicrosoftSQLServerManagementStudio。（2）在對(duì)象資源管理器依次展開“數(shù)據(jù)庫”、“教務(wù)管理”數(shù)據(jù)庫節(jié)點(diǎn)、“Student”表節(jié)點(diǎn)、“索引”節(jié)點(diǎn)。（3）在“索引”節(jié)點(diǎn)上單擊右鍵，如圖6-26類似，選擇“刪除”。（4）打開“刪除對(duì)象”對(duì)話框，單擊“刪除”按鈕即可刪除指定索引。38任務(wù)6-2教務(wù)數(shù)據(jù)庫索引管理子任務(wù)3教務(wù)數(shù)據(jù)表索引維護(hù)子任務(wù)內(nèi)容顯示表student的數(shù)據(jù)和索引的碎片信息。重建教師表中的index_teacher_phone索引。更新表student索引的分布統(tǒng)計(jì)頁。子任務(wù)要求能用SQLServerManagementStudio進(jìn)行索引的定期維護(hù)。391.顯示表student的數(shù)據(jù)和索引的碎片信息當(dāng)在表上頻繁進(jìn)行插入、更新和刪除操作時(shí)，表中也就不可避免地會(huì)產(chǎn)生存儲(chǔ)碎片，有時(shí)頁的順序也變得非常混亂，結(jié)果就會(huì)引起整個(gè)查詢性能下降，這時(shí)可以使用DBCCSHOWCONTIG命令來掃描指定表的碎片并確定該表或索引頁是否嚴(yán)重不連續(xù)。（1）DBCCSHOWCONTIG命令的語法如下：DBCCSHOWCONTIG[({'表名'|表id|'視圖名'|視圖id}[,'索引名'|索引id])][WITH{[,[ALL_INDEXES]][,[TABLERESULTS]][,[FAST]][,[ALL_LEVELS]][NO_INFOMSGS]}]

40參數(shù)說明：'表名'|表id|'視圖名'|視圖id：是要檢查碎片信息的表或視圖。如果未指定，則檢查當(dāng)前數(shù)據(jù)庫中的所有表和索引視圖。若要獲得表或視圖ID，請(qǐng)使用OBJECT_ID函數(shù)。'索引名'|索引id：是要檢查其碎片信息的索引。如果未指定，則該語句將處理指定表或視圖的基本索引。若要獲取索引ID，請(qǐng)使用