SQL Server中全文檢索搜尋與Like的差異分析

SQL Server中全文檢索搜尋與Like的差異分析

http://fecbob.pixnet.net/blog/post/38990643-sql-server%E4%B8%AD%E5%85%A8%E6%96%87%E6%AA%A2%E7%B4%A2%E6%90%9C%E5%B0%8B%E8%88%87like%E7%9A%84%E5%B7%AE%E7%95%B0%E5%88%86%E6%9E%90

在SQL Server中，Like關鍵字可以實現模糊查詢，即確定特定字串是否與制定模式相匹配。
這裡的模式可以指包含常規字元和萬用字元。在模式匹配過程中，常規字元必須與字串中
指定的字元完全符合。不過通過使用萬用字元可以改變這個規則，如使用?等萬用字元可以
與字串的任意部分相匹配。故Like關鍵字可以在資料庫中實現模糊查詢。

另外資料庫庫管理員也可以利用全文檢索搜尋功能對SQL Server資料表進行查詢。
在可以對給定的標進行全文查詢之前，資料庫管理元必須對這個資料表建立全文索引。
全文索引也可以實現類似Like的模糊查詢功能。如在一張人才簡歷表中查找符合特定字串的
資訊等等。雖然說Like關鍵字與全文檢索搜尋在功能上大同小異，但是在實現細節上有比較
大的差異。作為資料庫管理員需要瞭解這個差異，並選擇合適的實現模式。

1. 查詢效率上的差異。

通常情況下，Like關鍵字的查詢效率還是比較快的。特別是對於結構化的資料，
Like的查詢效率、靈活性方面是值得稱道的。但是對於一些非機構化的文本資料，
如果通過Like關鍵字來進行模糊查詢的話，則其執行效率並不是很理想。
特別是對於全文查詢來說，其速度要慢得多。而且隨著記錄數量的增多，類似的差異更明顯。
如在一張表中，有三百萬行左右的文本資料，此時如果利用Like關鍵字來查找相關的內容，
則可能需要幾分鐘的時間才能夠返回正確的結果。相反，對於同樣的資料通過採用全文檢索
搜尋功能的話，則可能只需要1分鐘不到甚至更多的時間及可以返回結果。
故當文本資料的行數比較多時，如在一萬行以上，則此時資料庫管理員若採用
全文檢索搜尋功能的話，則可以比較明顯的改善資料庫的查詢效率。

2. 對空白字元的敏感性。

在資料庫中如果採用Like關鍵字進行模糊查詢，則在這個關鍵字後面的所有字元都有意義。
如現在使用者使用like 「abcd 」(帶有兩個空格)查詢時，則後面的空白字元對於Like關鍵字
也是敏感的。也就是說，如果使用者利用上面這條語句進行查詢時，則被查詢的內容必須
也是「abcd 」(帶有兩個空格)這種類型的資料才會被返回。如果被查詢的內容是「abcd 」
(不帶空格或者帶有一個空格)則資料庫系統會認為這與查詢準則不相符合，
故不會返回相關的記錄。故Like關鍵字對於空格是比較敏感的。為此在使用Like關鍵字時候
需要特別注意這個問題。如果使用者或者程式開發人員不能夠確定abcd後面到底是否有空格
，則可以通過萬用字元拉實現。即可以利用」%abcd%」為條件陳述式。如此的話，
無論abcd前面或者後面是否有空格，則都會被查詢出來。但是全文檢索搜尋的話，
通常情況下系統會把空格忽略掉。即在全文檢索搜尋功能中，系統會先對查詢準則語句
進行優化。如果發現空格的話，則往往會實現把空格過濾掉。故全文檢索搜尋的話，
對於空格等特殊字元往往是不敏感的。

3. 對於一些特殊字元的處理要求。

由於資料類型不同，其資料存儲方式也不同。為此某些特殊的資料類型可能無法通過
Like關鍵字來實現模糊查詢。如對於辦好char和Varchar資料的模式的字串比較可能無法
通過Like關鍵字來實現。也就是說，Like關鍵字後面帶的條件陳述式僅對字元模式有效，
不能夠使用Like條件陳述式來查詢格式化的二進位資料等等。為此如果資料庫管理元要
採用Like關鍵字，則其必須瞭解每種資料類型的存儲方式以及導致Like關鍵字比較失敗的原因。
知己知彼，百戰百勝。只有如此資料庫管理員才能夠避免因為在不恰當的地方採用了Like關鍵
字而造成查詢的錯誤。不過值得高興的是，Like關鍵字支援ASCII模式匹配與Unicode模式匹配。
如果Like關鍵字的所有參數都為ASCII字元資料類型，則Like關鍵字會自動採用ASCII模式匹配。
如果其中任何一個參數為Unicode資料類型，則系統會把所有的參數都轉換為Unicode資料類型，
並執行Unicode模式匹配。另外需要注意的是，如果Like關鍵字加上Unicode的資料類型則
後面條件陳述式的空格是有效的，即比較時會考慮到後面出現的空格。
但是如果資料類型不是Unicode的，則對後面的空格不敏感。即比較時，
是否存在空格對於最後的結果不會有影響。

但是如果資料庫管理員才用全文檢索搜尋的話，往往沒有這方面的顧慮。
因為全文檢索搜尋不僅支援傳統的字元模式，而且還支援其他的資料模式。
另外通過全文檢索搜尋，還可以用來查詢格式化的二進位資料。為此如果在資料表中，
資料模式不統一或者需要對二進位資料進行查詢的話，則必這建議資料庫管理員需要
採用全文檢索搜尋，而不是採用Like關鍵字。

4. 逸出字元對查詢的影響。

如現在在資料表中有百分制的數值。如某個序號為10的產品的不合格率為10%。
此時使用者可能需要找出這個合格率為10%的內容，並進行後續的操作。
但是10%其中的%是一個比較特殊的字元，它是資料庫中的萬用字元。如果利用Like 」10%」
進行查詢的話，則資料庫會把10與10%的內容都查找出來。顯然這不符合我們的需要。
為了避免這種萬用字元等特殊字元給Like查詢帶來的不利影響，則需要通過Escape子句來
搜索包含一個或者多個特殊萬用字元的字串。如上面的例子中，要把%當作一般字元而
不是萬用字元，就必須提供Escape關鍵字和轉義符號。如果Like模式中的轉義符後面沒有字元，
則該模式無效並且 LIKE 返回False。如果轉義符後面的字元不是萬用字元，
則將放棄轉義符並將該轉義符後面的字元作為該模式中的常規字元處理。
不過在全文檢索搜尋中就不會受到這個逸出字元的影響。

如現在在資料庫中有abcd、ab、abef、ab*等行。現在資料庫管理員希望能夠查找出
以ab字元開頭的行，即實現首碼搜索。此時資料庫管理員就可以通過’ 「top*」’
這個條件陳述式來完成。此時系統就會返回所有與星號之前制定的文本相匹配的文本。
如果此時資料庫管理員只想查找ab*的記錄，則就可以使用’ top*’(不包含雙引號)條件陳述式
來完成查詢需求。即如果未在文本和星號前後加上雙引號的話，則全文檢索搜尋將不把星號
當作萬用字元。這就比使用逸出字元要簡單的多。

5. 具體應用的差異。

由於全文檢索搜尋與Like關鍵字在功能與性能上的一些差異，故他們在應用領域上也有所差別。
SQL Server資料庫在設計的時候，也是讓他們各自負責一塊領域。如相比Like掛泥漿案子而言，
全文瑣碎可能根據下面這些內容來實現特定的查詢。如可以根據一個或則多個特定的詞和短語
來進行查詢;可以通過特定詞的變形來進行查詢;如可以與另一個詞或短語鄰近的詞或者短語;
如可以對特定詞的同義字形式來進行查詢;如可以通過加權值的詞或者短語來實現查詢等等。
正是因為全文檢索搜尋這些特異功能，決定了全文檢索搜尋在一些特定的場合中特別有用。

據考試大瞭解，全文檢索搜尋在如下幾個應用領域有比較突出的表現。一是在電子商務網站上，
使用者可以通過全文檢索搜尋功能在網站主頁上根據產品規格或者名字來實現模糊查詢。
二是在一些人才網站上，可以通過學歷、工作經驗、技術特長等條件在後臺資料庫中查找
需要的人才資訊等等。不管是什麼樣的商業應用場景，全文檢索搜尋的基本管理工作和
開發工作是相同的。不過，在給定的商業應用場景中，可以對全文索引和查詢進行優化
以使其滿足業務目標。例如，對於電子商務來說，最大限度地提高性能可能比對結果
進行排序、檢索的準確性(實際上有多少個現有匹配項是由全文查詢返回的)或支援多種語言
更重要。對於律師事務所來說，首先需要考慮的可能是返回所有可能存在的匹配項。
到目前為止，筆者參與過電子商務專案、律師案例庫等幾個專案中都採用了全文檢索搜尋功能，
都取得了比較不錯的效果。

總的來說，在一些簡單查詢中，使用Like關鍵字來實現模糊查詢可能會取得比較好的效果。
但是在一些比較複雜的查詢應用中，特別是需要在大文本中查詢相關的內容，
則最好通過全文檢索搜尋來實現查詢。此時後者無論在性能上、還是在準確度上都會有
比較出色的表現。