SQL Server全文索引的硬傷

作者：我?guī)浌饰以?/span> 2011-04-18 11:00:12

全文索引是非常常用的，在SQL Server中也是比較常見的，全文索引在SQL Server數(shù)據(jù)庫中發(fā)揮了很大的功能，本文將為大家介紹SQL Server全文索引的硬傷。

導(dǎo)讀：關(guān)于SQL Server全文索引的硬傷的講解，下文中將從一個(gè)想象的場景開始，希望下文中講到的內(nèi)容對大家能夠有所幫助。

想象這樣一個(gè)場景：在DataBase_name.dbo.Table_name中有一個(gè)名為Title（標(biāo)題）和Contents（內(nèi)容）的字段，現(xiàn)在需要查詢在Title或者Contents中包括“qq”字符的所有記錄。

面對這樣的一個(gè)場景，我們通常都會寫這樣一個(gè)腳本：SELECT * FROM DataBase_name.dbo.Table_name WHERE Title LIKE '%qq%' OR Contents LIKE '%qq%'; 沒錯(cuò)，這也是我第一個(gè)想到的方法。

但是我們需要思考的是：隨著時(shí)間的推移，數(shù)據(jù)會越來越大，那個(gè)時(shí)候我們該如何提高我們的性能？客戶隨時(shí)都有可能要求加入對Remark（備注）字段的查詢，難道我們就應(yīng)該不厭其煩地修改程序代碼？

面對上面的質(zhì)問，我們需要提醒你的是：①對于這樣的查詢條件，即使Title和Contents上都有索引，我們也無法使用到索引，因?yàn)樵?'%qq%'的“qq”前面使用了通配符，所以無法使用到索引；如果查詢的條件是'qq%'，那倒是可以利用上索引。②在許多數(shù)據(jù)庫性能調(diào)優(yōu)的文章上都說OR這個(gè)謂詞可以使用SELECT UNION ALL SELECT這樣的方式來提高性能，但是需要提醒大家的是：如果在一條記錄中字段Title和Contents都同時(shí)存在“中國”字符的話，那么返回的結(jié)果就會出現(xiàn)兩條相同的記錄，如果你希望是唯一的記錄，那么這個(gè)時(shí)候你就要注意了。③其實(shí)有些時(shí)候，對于and的操作符，我們可以考慮使用：SQL Server 索引中include的魅力（具有包含性列的索引）

現(xiàn)在回到我們上面提出的疑問上，大概這個(gè)時(shí)候大家都應(yīng)該想到了數(shù)據(jù)庫的全文索引了。全文索引是一種特殊類型的基于標(biāo)記的功能性索引，由 Microsoft SQL Server 全文引擎 (MSFTESQL) 服務(wù)創(chuàng)建和維護(hù)。創(chuàng)建全文索引的過程與創(chuàng)建其他類型的索引的過程差別很大。MSFTESQL 不是基于某一特定行中存儲的值來構(gòu)造 B 樹結(jié)構(gòu)，而是基于要索引的文本中的各個(gè)標(biāo)記來創(chuàng)建倒排、堆積且壓縮的索引結(jié)構(gòu)。（摘自MSDN）

講了那么久，硬傷在哪里呢？可能大家都懷疑我是不是標(biāo)題黨了，呵呵，馬上就講到，那就是這個(gè)全文索引能解決我們一開始提到的場景嗎？回答是否定。為什么呢？因?yàn)镾QL Server對字符串“tqq.tencent.com”進(jìn)行分詞和倒排索引后，我們是無法通過查詢條件‘“*qq*”’來返回上面那條字符串的記錄的，這樣的查詢條件只能查詢到類似“qqt.tencent.com”、“www.qq.com”這樣的字符串。SQL Server的分詞應(yīng)該是正向最大值的分詞方法，它沒有把字符串進(jìn)行反方向再進(jìn)行一次分詞和索引，所以只能查詢到詞或短語的前綴符合的記錄。這一點(diǎn)有可能會被大家所忽略掉。

就針對上面的說法，我們來進(jìn)行測試一下：

--已經(jīng)對表Test_FullText_Index的uri,uri_path建立了全文索引.
--下面的查詢是為了說明CONTAINS與LIKE的區(qū)別.
SELECT ID,uri,uri_path
FROM Test_FullText_Index
where uri LIKE '%qq%'
AND ID NOT IN(SELECT ID FROM Test_FullText_Index WHERE CONTAINS(uri,'"qq*"'))

下圖為執(zhí)行結(jié)果

如何大家有什么好的解決方案可以解決這樣的Like查詢的話，可以拿出來大家探討一下。

主題的內(nèi)容講完了，下面附帶講一些創(chuàng)建全文索引的步驟和注意事項(xiàng)，懂的童鞋（同學(xué)）可以跳過。

設(shè)置全文索引的步驟：

1：對著數(shù)據(jù)庫點(diǎn)擊右鍵-選擇屬性-選擇文件，選中“使用全文索引”

2：對著表點(diǎn)擊右鍵-全文索引-定義全文索引

3：點(diǎn)擊下一步，如果這個(gè)表中沒有唯一性索引就會出現(xiàn)下圖所示

4：選擇表列，選擇斷字符語言。

5：點(diǎn)擊下一步，這里的選項(xiàng)要注意，如果不想再表、視圖更改的時(shí)候更新全文索引，那就選擇不跟蹤更改；這樣就可以選擇是否在創(chuàng)建索引時(shí)啟動完全填充了。

6：點(diǎn)擊下一步創(chuàng)建索引要保存的目錄，全文索引的索引文件是以文件的形式保存到硬盤上的。

7：之后就可以設(shè)置自動填充、手動跟蹤更改，還有設(shè)置計(jì)劃了。

全文索引需要注意：

表中必須有一個(gè)唯一性索引，當(dāng)并不需要是主鍵。
一個(gè)表中只能有一個(gè)全文索引。
你需要告訴你的腳本你想使用全文索引，如何告訴呢？那就是使用關(guān)鍵字：CONTAINS、FULLTEXT、CONTAINSTABLE、FREETEXTTABLE。例如：SELECT * FROM table_name WHERE CONTAINS(fullText_column,'"search contents*"');需要記住CONTAINS等在不同場景、需求下的用法。
如果定義了變量作為傳入值，那么就要注意是否需要在set字符的時(shí)候的前面加入N標(biāo)識。
要對表設(shè)置全文索引，那就得先對數(shù)據(jù)庫設(shè)置了全文索引，這樣點(diǎn)擊表右鍵的時(shí)候，“全文索引”選項(xiàng)才能用。
腳本在查找的時(shí)候是不區(qū)分大小寫的。解決辦法：SELECT * FROM Table_name WHERE Column_name='A' COLLATE Chinese_PRC_CS_AI;或者SELECT * FROM Table_name WHERE ASCII(Column_name) = ASCII('A');
Microsoft SQL Server 全文引擎 (MSFTESQL) 不是基于某一特定行中存儲的值來構(gòu)造 B 樹結(jié)構(gòu)，而是基于要索引的文本中的各個(gè)標(biāo)記來創(chuàng)建倒排、堆積且壓縮的索引結(jié)構(gòu)。
全文索引并不一定能達(dá)到like這個(gè)謂詞的效果，如LIKE '%qq%'。這正是本篇文章想要說明的。
如果數(shù)據(jù)庫是在移動盤符上，好像就無法設(shè)置：數(shù)據(jù)庫-屬性-文件-“使用全文索引”了，這個(gè)時(shí)候chckbox是不可用的。（這個(gè)大家可以求證一下）
關(guān)于搜索結(jié)果的排序問題，全文索引并沒有這個(gè)功能，也就是匹配度排序或者說是相似度排序。
Lucene中有一個(gè)Similarity類，Lucene Practical Scoring Function就包含了得分的計(jì)算公式，tf、idf。

【編輯推薦】