1、第六章,索引和全文索引,2,回顧,子查詢 聯(lián)合查詢 DISTINCT 子句 COMPUTE BY SELECT INTO,3,目標(biāo),索引的含義 創(chuàng)建和刪除索引 全文索引 全文檢索,4,索引概念,索引是對(duì)數(shù)據(jù)庫表中一個(gè)或多個(gè)列的值進(jìn)行排序的結(jié)構(gòu) 索引提供指針以指向存儲(chǔ)在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針,5,索引優(yōu)點(diǎn)缺點(diǎn),優(yōu)點(diǎn) 加快訪問速度 加強(qiáng)行的唯一性 缺點(diǎn) 帶索引的表在數(shù)據(jù)庫中需要更多的存儲(chǔ)空間。 操縱數(shù)據(jù)的命令需要更長(zhǎng)的處理時(shí)間，因?yàn)樗鼈冃枰獙?duì)索引進(jìn)行更新。,6,索引分類,表中各行的物理順序與鍵值的邏輯（索引）順序相同。表只能包含一個(gè)聚集索引 非聚集索引指定表的邏輯順

2、序。,7,Sysindexes表,數(shù)據(jù)庫中的每個(gè)索引和表在表中各占一行 該表存儲(chǔ)在每個(gè)數(shù)據(jù)庫中 表 indid字段表示索引 ID 1 = 聚集索引1 = 非聚集255 = 具有 text 或 image 數(shù)據(jù)的表?xiàng)l目,8,查詢示例不使用索引進(jìn)行查詢,sysindexes,IAM頁,Heap,頁 127,頁128,頁129,頁130,9,查詢示例使用非聚集索引進(jìn)行查詢,sysindexes,Martin,SELECT lastname, firstname FROM member WHERE lastname BETWEEN Masters AND Rudd,10,SELECT lastname

3、, firstname FROM member WHERE lastname = Ota,使用聚集索引進(jìn)行查詢,Martin,11,適合創(chuàng)建索引情況： 該列頻繁用于進(jìn)行搜索。 該列用于對(duì)數(shù)據(jù)進(jìn)行排序。 不適合創(chuàng)建索引情況： 列中僅包含幾個(gè)不同的值。 表中僅包含幾行。,創(chuàng)建索引的指導(dǎo)原則,12,創(chuàng)建索引1-2,CREATE INDEX 語句用于為給定的表創(chuàng)建索引。 該語句通過修改表的物理順序，或者向查詢優(yōu)化器提供表的一個(gè)邏輯順序以提高查詢效率。 只有表的所有者能為表創(chuàng)建索引。,13,CREATE INDEX 語句的語法為：,創(chuàng)建索引2-2,CREATE UNIQUE CLUSTERED|NONC

4、LUSTERED INDEX index_name ON table_name(column_name, column_name) WITH PAD_INDEX ,FILLFACTOR=x ,DROP_EXISTING ,14,填充因子1-3,創(chuàng)建聚集索引后，表中數(shù)據(jù)會(huì)按照索引列的值的順序存儲(chǔ)在數(shù)據(jù)頁中。 表中添加了新記錄或索引列的值發(fā)生修改時(shí)，SQL Server 需要重新組織數(shù)據(jù)存儲(chǔ)模式，以便為新記錄騰出空間并維持?jǐn)?shù)據(jù)的有序存儲(chǔ)。 向已滿的索引頁中添加新行時(shí)，SQL Server 會(huì)將大約一半的行移到新的一頁上，以便為新行騰出空間，從而導(dǎo)致頁拆分。,15,填充因子2-3,頁拆分會(huì)降低性能，

5、且割裂了表中數(shù)據(jù)的存儲(chǔ)。 填充因子是在創(chuàng)建索引時(shí)指定的，它用來在索引的各個(gè)頁上預(yù)留一定比例的可用空間，以供將來進(jìn)行數(shù)據(jù)擴(kuò)充時(shí)使用。 填充因子指定了創(chuàng)建索引后要在頁中輸入數(shù)據(jù)的量。,16,填充因子3-3,如果無須再修改數(shù)據(jù)，可以指定填充因子100，這樣將完全占滿索引頁，因而所占用的存儲(chǔ)空間最少。 如果需要頻繁地修改表中的數(shù)據(jù)，則應(yīng)指定一個(gè)較低的填充因子，以便在數(shù)據(jù)頁中保留更多的空白空間 。,17,聚集索引決定數(shù)據(jù)的物理存儲(chǔ)順序。 一個(gè)表只能有一個(gè)聚集索引。 索引可以包含多個(gè)數(shù)據(jù)列。 示例：,創(chuàng)建聚集索引,CREATE CLUSTERED INDEX CLINDX_titleid ON roysc

6、hed (title_id),18,創(chuàng)建聚集索引的指導(dǎo)原則,選擇唯一值比例高的數(shù)據(jù)列，一般應(yīng)選擇主鍵列 先創(chuàng)建聚集索引，再創(chuàng)建非聚集索引 在創(chuàng)建聚集索引時(shí)使用FILLFACTOR選項(xiàng)以確保在插入新行時(shí)有足夠的可用空間。,19,非聚集索引指定表的邏輯順序。數(shù)據(jù)存儲(chǔ)在一個(gè)位置，索引存儲(chǔ)在另一個(gè)位置，索引中包含指向 數(shù)據(jù)存儲(chǔ)位置的指針。 一個(gè)表可以有多個(gè)（最多249個(gè)）非聚集索引。 示例：,創(chuàng)建非聚集索引,CREATE NONCLUSTERED INDEX NCLINDX_ordnum ON sales (ord_num),20,除非絕對(duì)必要，否則請(qǐng)不要添加非聚集索引，這是因?yàn)榉蔷奂饕枰~外的維

7、護(hù)開銷 在頻繁訪問的列上建立索引 默認(rèn)情況下，CREATEINDEX語句創(chuàng)建的是非聚集索引 在相異值多的數(shù)據(jù)列上創(chuàng)建非聚集索引，如名和姓的組合項(xiàng),創(chuàng)建非聚集索引的指導(dǎo)原則,21,聚集索引和非聚集索引可以創(chuàng)建為唯一索引或組合索引。 唯一索引不允許索引列中存在重復(fù)的值。 組合索引允許在創(chuàng)建索引時(shí)使用兩列或更多的列。,索引的特性,22,由于每一行都包含一個(gè)唯一的值，唯一索引可以確保實(shí)體完整性。 鍵列中的NULL 值也算作唯一值。 不能在包含重復(fù)數(shù)據(jù)的列上創(chuàng)建唯一索引。 在創(chuàng)建唯一索引前必須更新或刪除重復(fù)的數(shù)據(jù)。,創(chuàng)建唯一索引的指導(dǎo)原則,23,創(chuàng)建組合索引的指導(dǎo)原則,當(dāng)需要頻繁地將兩列或多列作為一個(gè)整

8、體進(jìn)行 搜索時(shí)，可以創(chuàng)建組合索引。 創(chuàng)建組合索引時(shí)先列出唯一性最好的列。 組合索引中列的順序和數(shù)量會(huì)影響查詢的性能。,24,創(chuàng)建索引后，可能需要查找有關(guān)索引的信息，例如查看在某個(gè)表上創(chuàng)建的索引的列表或者表中的索引列。 系統(tǒng)存儲(chǔ)過程sp_helpindex報(bào)告有關(guān)表的索引的信息。 語法：,查看索引,sp_helpindex ,25,SQL Server使用查詢優(yōu)化器選擇執(zhí)行查詢的最佳方式 ，包括選擇要使用的索引，查詢優(yōu)化器以頁I/O為標(biāo)準(zhǔn)，嘗試選出成本最低的方法 。 SQL Server中可以強(qiáng)制查詢優(yōu)化器選擇某個(gè)特定的索引。 制查詢優(yōu)化器選擇某個(gè)特定的索引。 語法 (INDEX=index_n

9、ame),SELECT * FROM sales (INDEX =nclindx_ordnum) WHERE ord_num = P3087a,在查詢強(qiáng)制使用中索引,示例,26,使用DROP INDEX語句可以將不需要的索引從 數(shù)據(jù)庫中刪除，以騰出空間 DROP INDEX語句不適用于用PRIMARY KEY或 UNIQUE 約束創(chuàng)建的索引 DROP INDEX也不能用于刪除系統(tǒng)表的索引 語法： DROP INDEX table_name.index_name 示例：,刪除索引,DROP INDEX sales.NCLINDX_ordnum,27,全文搜索,SQL Server使用全文索引執(zhí)行

10、全文搜索。 可以在無結(jié)構(gòu)文本上建立索引，以便能夠在文本 中搜索特定的條目。 該功能是通過Microsoft搜索服務(wù)提供的。使用Microsoft搜索服務(wù)，可以用語言搜索條件實(shí)現(xiàn)復(fù)雜的搜索。 語言搜索可用于搜索單詞或短語、相鄰的幾個(gè)目標(biāo)詞和不同形式的詞語。,28,全文索引跟蹤表中的重要詞語。 該索引用于包含索引詞的搜索和高級(jí)搜索選項(xiàng)，如短語搜索。 全文索引需要一個(gè)包含唯一鍵值的數(shù)據(jù)列。 添加、修改或刪除表中數(shù)據(jù)時(shí)，必須手動(dòng)更新全文索引。,全文索引,29,所有的全文索引都存儲(chǔ)在全文目錄中。 全文目錄只能通過 Windows 和搜索服務(wù)查看。 默認(rèn)情況下，一個(gè)數(shù)據(jù)庫的所有全文索引都存放在一個(gè)全文目錄

11、中。 如果索引非常大，系統(tǒng)管理員也可以將目錄分割成多個(gè)目錄。,全文目錄,30,使用全文索引,可以在企業(yè)管理器中使用全文索引向?qū)?chuàng)建全文索引。 創(chuàng)建全文索引后，就可以對(duì)創(chuàng)建了該索引的表進(jìn)行全文查詢。 全文查詢使用CONTAINS和FREETEXT這兩個(gè)謂詞。,31,搜索包含基于字符的數(shù)據(jù)類型的列，查找： 單個(gè)詞語或短語的匹配項(xiàng) 特定距離內(nèi)的詞語或 加權(quán)匹配項(xiàng) 搜索不區(qū)分大小寫。 示例,SELECT title FROM titles WHERE CONTAINS(title,computer or cooking or silicon),CONTAINS 謂詞,32,FREETEXT關(guān)鍵字在列中搜索與搜索條件中的詞語意義相匹配但形式卻并不完全匹配的值 搜索字符串被分割成多個(gè)搜索短詞，然后再查找