SQL Server全文索引的硬傷
想象這樣一個場景：在DataBase_name.dbo.Table_name中有一個名為Title(標(biāo)題)和Contents(內(nèi)容)的字段，現(xiàn)在需要查詢在Title或者Contents中包括“qq”字符的所有記錄。
面對這樣的一個場景，我們通常都會寫這樣一個腳本：SELECT * FROM DataBase_name.dbo.Table_name WHERE Title LIKE '%qq%' OR Contents LIKE '%qq%'; 沒錯，這也是我第一個想到的方法。
但是我們需要思考的是：隨著時間的推移，數(shù)據(jù)會越來越大，那個時候我們該如何提高我們的性能?客戶隨時都有可能要求加入對Remark(備注)字段的查詢，難道我們就應(yīng)該不厭其煩地修改程序代碼?
面對上面的質(zhì)問，我們需要提醒你的是：①對于這樣的查詢條件，即使Title和Contents上都有索引，我們也無法使用到索引，因為在 '%qq%'的“qq”前面使用了通配符，所以無法使用到索引;如果查詢的條件是'qq%'，那倒是可以利用上索引。②在許多數(shù)據(jù)庫性能調(diào)優(yōu)的文章上都說OR這個謂詞可以使用SELECT UNION ALL SELECT這樣的方式來提高性能，但是需要提醒大家的是：如果在一條記錄中字段Title和Contents都同時存在“中國”字符的話，那么返回的結(jié)果就會出現(xiàn)兩條相同的記錄，如果你希望是唯一的記錄，那么這個時候你就要注意了。③其實有些時候，對于and的操作符，我們可以考慮使用：SQL Server 索引中include的魅力(具有包含性列的索引)
現(xiàn)在回到我們上面提出的疑問上，大概這個時候大家都應(yīng)該想到了數(shù)據(jù)庫的全文索引了。全文索引是一種特殊類型的基于標(biāo)記的功能性索引，由 Microsoft SQL Server 全文引擎 (MSFTESQL) 服務(wù)創(chuàng)建和維護。創(chuàng)建全文索引的過程與創(chuàng)建其他類型的索引的過程差別很大。MSFTESQL 不是基于某一特定行中存儲的值來構(gòu)造 B 樹結(jié)構(gòu)，而是基于要索引的文本中的各個標(biāo)記來創(chuàng)建倒排、堆積且壓縮的索引結(jié)構(gòu)。(摘自MSDN)
講了那么久，硬傷在哪里呢?可能大家都懷疑我是不是標(biāo)題黨了，呵呵，馬上就講到，那就是這個全文索引能解決我們一開始提到的場景嗎?回答是否定。為什么呢?因為SQL Server對字符串“tqq.tencent.com”進行分詞和倒排索引后，我們是無法通過查詢條件‘“*qq*”’來返回上面那條字符串的記錄的，這樣的查詢條件只能查詢到類似“qqt.tencent.com”、“www.qq.com”這樣的字符串。SQL Server的分詞應(yīng)該是正向最大值的分詞方法，它沒有把字符串進行反方向再進行一次分詞和索引，所以只能查詢到詞或短語的前綴符合的記錄。這一點有可能會被大家所忽略掉。
就針對上面的說法，我們來進行測試一下：
- --已經(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)建全文索引的步驟和注意事項，懂的童鞋（同學(xué)）可以跳過。
設(shè)置全文索引的步驟
1：對著數(shù)據(jù)庫點擊右鍵-選擇屬性-選擇文件，選中“使用全文索引”
2：對著表點擊右鍵-全文索引-定義全文索引
3：點擊下一步，如果這個表中沒有唯一性索引就會出現(xiàn)下圖所示
4：選擇表列，選擇斷字符語言。
5：點擊下一步，這里的選項要注意，如果不想再表、視圖更改的時候更新全文索引，那就選擇不跟蹤更改;這樣就可以選擇是否在創(chuàng)建索引時啟動完全填充了。
6：點擊下一步創(chuàng)建索引要保存的目錄，全文索引的索引文件是以文件的形式保存到硬盤上的。
7：之后就可以設(shè)置自動填充、手動跟蹤更改，還有設(shè)置計劃了。
全文索引需要注意：
- 表中必須有一個唯一性索引，當(dāng)并不需要是主鍵。
- 一個表中只能有一個全文索引。
- 你需要告訴你的腳本你想使用全文索引，如何告訴呢?那就是使用關(guān)鍵字：CONTAINS、FULLTEXT、 CONTAINSTABLE、FREETEXTTABLE。例如：SELECT * FROM table_name WHERE CONTAINS(fullText_column,'"search contents*"');需要記住CONTAINS等在不同場景、需求下的用法。
- 如果定義了變量作為傳入值，那么就要注意是否需要在set字符的時候的前面加入N標(biāo)識。
- 要對表設(shè)置全文索引，那就得先對數(shù)據(jù)庫設(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)，而是基于要索引的文本中的各個標(biāo)記來創(chuàng)建倒排、堆積且壓縮的索引結(jié)構(gòu)。
- 全文索引并不一定能達到like這個謂詞的效果，如LIKE '%qq%'。這正是本篇文章想要說明的。
- 如果數(shù)據(jù)庫是在移動盤符上，好像就無法設(shè)置：數(shù)據(jù)庫-屬性-文件-“使用全文索引”了，這個時候chckbox是不可用的。(這個大家可以求證一下)
- 關(guān)于搜索結(jié)果的排序問題，全文索引并沒有這個功能，也就是匹配度排序或者說是相似度排序。
- 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
【編輯推薦】
- 養(yǎng)成一個SQL好習(xí)慣帶來一筆大財富
- 客戶的一次疏忽，DBA的一次噩夢
- 數(shù)據(jù)庫的輪回
- 講述MySQL索引和優(yōu)化的故事
- 擦亮自己的眼睛去看SQL Server
