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

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

但是我們需要思考的是：隨著時(shí)間的推移，數(shù)據(jù)會(huì)越來越大，那個(gè)時(shí)候我們?cè)撊绾翁岣呶覀兊男阅?客戶隨時(shí)都有可能要求加入對(duì)Remark(備注)字段的查詢，難道我們就應(yīng)該不厭其煩地修改程序代碼?

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

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

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

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

--已經(jīng)對(duì)表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：對(duì)著數(shù)據(jù)庫(kù)點(diǎn)擊右鍵-選擇屬性-選擇文件，選中“使用全文索引”

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

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

4：選擇表列，選擇斷字符語(yǔ)言。

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

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

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

全文索引需要注意：

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

CONTAINS的幾種用法

CONTAINS 謂詞可以搜索：

• 詞或短語(yǔ)。
• 詞或短語(yǔ)的前綴。
• 與另一個(gè)詞相鄰的詞。
• 由另一個(gè)詞的詞形變化而生成的詞(例如，drive 一詞是 drives、drove、driving 和 driven 詞形變化的詞干)。
• 使用同義詞庫(kù)確定的另一個(gè)詞的同義詞(例如，metal 一詞可能有 aluminum 和 steel 等同義詞)。

原文鏈接：http://www.cnblogs.com/gaizai/archive/2010/05/13/1733857.html

【編輯推薦】

1. 養(yǎng)成一個(gè)SQL好習(xí)慣帶來一筆大財(cái)富
2. 客戶的一次疏忽，DBA的一次噩夢(mèng)
3. 數(shù)據(jù)庫(kù)的輪回
4. 講述MySQL索引和優(yōu)化的故事
5. 擦亮自己的眼睛去看SQL Server