SQL Server全文索引的硬傷
想象這樣一個場景：在DataBase_name.dbo.Table_name中有一個名為Title(標題)和Contents(內容)的字段，現在需要查詢在Title或者Contents中包括“qq”字符的所有記錄。
面對這樣的一個場景，我們通常都會寫這樣一個腳本：SELECT * FROM DataBase_name.dbo.Table_name WHERE Title LIKE '%qq%' OR Contents LIKE '%qq%'; 沒錯，這也是我第一個想到的方法。
但是我們需要思考的是：隨著時間的推移，數據會越來越大，那個時候我們該如何提高我們的性能?客戶隨時都有可能要求加入對Remark(備注)字段的查詢，難道我們就應該不厭其煩地修改程序代碼?
面對上面的質問，我們需要提醒你的是：①對于這樣的查詢條件，即使Title和Contents上都有索引，我們也無法使用到索引，因為在 '%qq%'的“qq”前面使用了通配符，所以無法使用到索引;如果查詢的條件是'qq%'，那倒是可以利用上索引。②在許多數據庫性能調優的文章上都說OR這個謂詞可以使用SELECT UNION ALL SELECT這樣的方式來提高性能，但是需要提醒大家的是：如果在一條記錄中字段Title和Contents都同時存在“中國”字符的話，那么返回的結果就會出現兩條相同的記錄，如果你希望是唯一的記錄，那么這個時候你就要注意了。③其實有些時候，對于and的操作符，我們可以考慮使用：SQL Server 索引中include的魅力(具有包含性列的索引)
現在回到我們上面提出的疑問上，大概這個時候大家都應該想到了數據庫的全文索引了。全文索引是一種特殊類型的基于標記的功能性索引，由 Microsoft SQL Server 全文引擎 (MSFTESQL) 服務創建和維護。創建全文索引的過程與創建其他類型的索引的過程差別很大。MSFTESQL 不是基于某一特定行中存儲的值來構造 B 樹結構，而是基于要索引的文本中的各個標記來創建倒排、堆積且壓縮的索引結構。(摘自MSDN)
講了那么久，硬傷在哪里呢?可能大家都懷疑我是不是標題黨了，呵呵，馬上就講到，那就是這個全文索引能解決我們一開始提到的場景嗎?回答是否定。為什么呢?因為SQL Server對字符串“tqq.tencent.com”進行分詞和倒排索引后，我們是無法通過查詢條件‘“*qq*”’來返回上面那條字符串的記錄的，這樣的查詢條件只能查詢到類似“qqt.tencent.com”、“www.qq.com”這樣的字符串。SQL Server的分詞應該是正向最大值的分詞方法，它沒有把字符串進行反方向再進行一次分詞和索引，所以只能查詢到詞或短語的前綴符合的記錄。這一點有可能會被大家所忽略掉。
就針對上面的說法，我們來進行測試一下：
- --已經對表Test_FullText_Index的uri,uri_path建立了全文索引.
- --下面的查詢是為了說明CONTAINS與LIKE的區別.
- 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*"'))
- --下圖為執行結果
如何大家有什么好的解決方案可以解決這樣的Like查詢的話，可以拿出來大家探討一下。
主題的內容講完了，下面附帶講一些創建全文索引的步驟和注意事項，懂的童鞋（同學）可以跳過。
設置全文索引的步驟
1：對著數據庫點擊右鍵-選擇屬性-選擇文件，選中“使用全文索引”
2：對著表點擊右鍵-全文索引-定義全文索引
3：點擊下一步，如果這個表中沒有唯一性索引就會出現下圖所示
4：選擇表列，選擇斷字符語言。
5：點擊下一步，這里的選項要注意，如果不想再表、視圖更改的時候更新全文索引，那就選擇不跟蹤更改;這樣就可以選擇是否在創建索引時啟動完全填充了。
6：點擊下一步創建索引要保存的目錄，全文索引的索引文件是以文件的形式保存到硬盤上的。
7：之后就可以設置自動填充、手動跟蹤更改，還有設置計劃了。
全文索引需要注意：
- 表中必須有一個唯一性索引，當并不需要是主鍵。
- 一個表中只能有一個全文索引。
- 你需要告訴你的腳本你想使用全文索引，如何告訴呢?那就是使用關鍵字：CONTAINS、FULLTEXT、 CONTAINSTABLE、FREETEXTTABLE。例如：SELECT * FROM table_name WHERE CONTAINS(fullText_column,'"search contents*"');需要記住CONTAINS等在不同場景、需求下的用法。
- 如果定義了變量作為傳入值，那么就要注意是否需要在set字符的時候的前面加入N標識。
- 要對表設置全文索引，那就得先對數據庫設置了全文索引，這樣點擊表右鍵的時候，“全文索引”選項才能用。
- 腳本在查找的時候是不區分大小寫的。解決辦法：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) 不是基于某一特定行中存儲的值來構造 B 樹結構，而是基于要索引的文本中的各個標記來創建倒排、堆積且壓縮的索引結構。
- 全文索引并不一定能達到like這個謂詞的效果，如LIKE '%qq%'。這正是本篇文章想要說明的。
- 如果數據庫是在移動盤符上，好像就無法設置：數據庫-屬性-文件-“使用全文索引”了，這個時候chckbox是不可用的。(這個大家可以求證一下)
- 關于搜索結果的排序問題，全文索引并沒有這個功能，也就是匹配度排序或者說是相似度排序。
- Lucene中有一個Similarity類，Lucene Practical Scoring Function就包含了得分的計算公式，tf、idf。
CONTAINS的幾種用法
CONTAINS 謂詞可以搜索：
- 詞或短語。
- 詞或短語的前綴。
- 與另一個詞相鄰的詞。
- 由另一個詞的詞形變化而生成的詞(例如，drive 一詞是 drives、drove、driving 和 driven 詞形變化的詞干)。
- 使用同義詞庫確定的另一個詞的同義詞(例如，metal 一詞可能有 aluminum 和 steel 等同義詞)。
原文鏈接：http://www.cnblogs.com/gaizai/archive/2010/05/13/1733857.html
【編輯推薦】
