SQL Server 全文搜尋

SQL Server 的全文搜尋(Full-Text Search）是基于分詞的文字檢索功能，依賴于全文索引。全文索引不同于傳統的平衡樹（B-Tree）索引和列存儲索引，它是由資料表構成的，稱作倒轉索引（Invert Index），存儲分詞和行的唯一鍵的映射關系。倒轉索引是在建立全文索引或更新全文索引時，由SQL Server自動建立和維護的。全文索引主要包含三種分析器：分詞器（Word Breaker）、詞幹分析器（stemmer）和同義詞分析器。全文索引中存儲的資料是分詞及其位置等資訊，分詞是基于特定語言的文法規則，按照特定的符号尋找詞語的邊界，把文本分解為“單詞”，每一個單詞叫做一個分詞（term)；全文索引有時會提取分詞的詞幹，把詞幹的多種派生形式存儲為單一詞幹，這個過程叫做提取詞幹；根據使用者提供的自定義同義詞清單，把相關的單詞轉換為同義詞，這個過程叫做提取同義詞。

生成全文索引是把使用者表中的文本資料進行分詞（Word breaker）和提取詞幹（Stemmer），并轉換同義詞（Thesaurus），過濾掉分詞中的停用詞（Stopword），最後把處理之後的資料存儲到全文索引中。把資料存儲到全文資料的過程叫做填充（Populate）或爬蟲（Crawl）程序，全文索引的更新方式可以手動填充，自動填充，或增量填充。

一，全文搜尋的基本元件

1，分詞器

分詞器（Word Breaker），顧名思義，用于分詞，它根據特定語言的文法規則，分割文本中的單詞，分詞器在拆分單詞時，還會記錄每個分詞在字元串中的位置，分詞器把分詞，分詞的位置，文檔ID，全文索引列的序号等資訊的組合，稱作标記（Token）。

例如，對于語句"Kitty is a cute cat"，在全文索引填充時，分詞器把該語句拆分成5個單詞：Kitty，is，a，cute，cat。 如果使用預設的停用詞清單，那麼“is”，“a”都是停用詞，全文索引會把停用詞丢失，隻存儲分詞：Kitty，cute，cat。

雖然停用詞不會添加到全文索引中，但是分詞的位置會被考慮。“Kitty” ，“cute” 和  “cat”的Position 分别是1，4 和 5。通過分詞的位置，全文搜尋能夠進行位置相鄰的查詢：兩個分詞之間最多存在N個單詞。例如，查詢語句：contains(column, 'near((Kitty,cate),3)') 的含義是存在兩個word，“Kitty” 和 “cate”，其最大距離是3，從column中查詢出包含該條件的phrase，字元串“Kitty is a cute cat.”  滿足比對條件。

2，停用詞

停用字詞清單（StopList）是非索引字詞的清單，每個StopList中存儲的分詞都是不會用于搜尋的分詞，叫做停用詞（StopWords），全文索引不會存儲停用詞，但是停用詞所占的位置會被記錄，如果對停用詞進行contians查詢，即使基礎表（underly table）中的字段中存在該停用詞，全文索引也不會傳回任何資料行。通常情況下，停用詞（Stopword）都是常用的單詞，在語句中出現的頻率十分高，過濾掉停用詞，能夠減少全文索引的size，提高全文查詢的性能。

3，詞幹（Stemmer） 和 同義詞（Thesaurus）

詞幹抽取器（Stemmer ）用于把同源單詞轉換為其根形式，能夠轉換為相同根形式的單詞是同源的。例如，對于單詞run，有很多同源的單詞：

• ran
• running
• runs
• runner (perhaps)

同義詞詞典（Thesaurus）是一個XML檔案，用于定義特定語言的同義詞清單，例如，我們可以設定“Author” ， “Writer” ，“journalist”是同義詞。

二，建立全文索引

建立全文索引之前，必須建立全文目錄（Full-Text Catalog），全文目錄用于組織全文索引，是全文索引的容器。每一個全文索引必須屬于一個全文目錄。全文目錄是個邏輯結構，跟資料庫的架構（Schema）相同，根據全文索引的存儲位置無關。

create fulltext catalog catalog_test
as default;      

為了建立全文索引，基礎表上必須存在一個唯一的（unique）、單列的（single-column）、非空的（non-nullable）的索引，全文引擎使用該索引把基礎表上的每行資料映射唯一索引鍵上，倒轉索引存儲的就是該索引鍵和分詞之間的映射關系。

create unique index uidx_dbLogID 
on [dbo].[DatabaseLog]
([DatabaseLogID]);      

每個表隻能建立一個全文索引，建立全文索引時，必須考慮全文索引存儲的檔案組，全文索引關聯的停用詞清單，全文索引的更新方式，以及跟文本關聯的語言，全文索引列必須是文本字段，例如：

create fulltext index 
on [dbo].[DatabaseLog]
(
[tsql] language 1033
)
key index ui_dbLogID
on (catalog_test,filegroup [primary]) 
with(change_tracking=off ,no population ,stoplist=system);      

1，語言（language）

選項 language 是可選的，用于指定列級别的語言，該選項的值可以是語言的名稱或LCID，如果沒有指定language選項，那麼使用SQL Server執行個體的預設語言。從系統視圖 sys.fulltext_languages (Transact-SQL)中檢視系統支援的語言及其對應的LCID 和名稱。

2，全文目錄（fulltext_catalog）

選項fulltext_catalog_name 用于指定全文索引的分組，

3，檔案組（filegroup）

選項 filegroup filegroup_name 用于指定全文索引存儲的檔案組，如果沒有指定檔案組，那麼全文索引和基礎表存儲在相同的檔案組中。由于更新全文索引是IO密集型操作，是以，為了更快的更新全文索引，最好把全文索引存儲在不同于基礎表的的實體硬碟或檔案組上，以達到最大的IO并發。

4，填充全文索引的方式

和普通的索引相同，當基礎表資料更新時，全文索引必須自動更新，這是系統預設的行為，也可以配置手動更新全文索引，或者間隔特定的時間點自動更新全文索引。

選項CHANGE_TRACKING 用于指定跟全文索引列相關的資料更新（Update，Delete，或Insert）是否需要同步到全文索引，

• CHANGE_TRACKING = MANUAL ：手動更新
• CHANGE_TRACKING =AUTO：自動更新，預設設定，當基礎表資料變化時，全文索引自動更新，
• CHANGE_TRACKING =OFF , NO POPULATION：不更新，指定選項NO POPULATION，表明在建立全文索引之後，SQL Server不會更新（populate）全文索引；如果未指定選項NO POPULATION，在建立全文索引之後，SQL Server更新全文索引。

5，停用詞（STOPLIST）

停用詞（StopWord）也稱作噪音詞，每一個全文索引都會關聯一個停用詞清單，預設情況下，全文索引關聯的是系統停用詞（system stoplist）。全文引擎把停用詞從分詞中删除，使全文索引不會包含停用詞。

STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }  

三，填充全文索引

填充全文索引也叫做爬蟲（crawl）程序，或填充（Population）程序。由于建立或填充全文索引會消耗大量的系統（IO、記憶體）資源，是以盡量選擇在系統空閑時對全文索引進行填充。在建立全文索引時，通過指定選項 CHANGE_TRACKING= MANUAL，或 CHANGE_TRACKING= OFF, NO POPULATION，建立的全文索引不會立即填充，使用者可以選擇在系統空閑時，使用 alter fulltext index 語句執行填充操作。隻有填充全文索引之後，全文索引才包含基礎表的分詞資料。

alter fulltext index 
on table_name
start { full | incremental | update } population;      

更新全文索引有三種方式：

• FULL POPULATION：全部填充，從基礎表中擷取每一行，重新編入全文索引；
• INCREMENTAL POPULATION：增量填充，前提是基礎表中包含timestamp字段，從上一次填充之後，隻把更新之後的資料編入全文索引；
• UPDATE POPULATION：更新填充，從上一次填充之後執行更新（insert、update、或delete）操作的資料行重新編入索引；

在建立全文索引時，如果指定CHANGE_TRACKING=AUTO   或   CHANGE_TRACKING=  OFF ， 那麼建立的全文索引會立即開始填充程序。 

四，使用 contains 謂詞查詢全文索引

如果想要在查詢中使用全文索引，通常使用CONTAINS謂詞來調用全文索引，實作比LIKE關鍵字更複雜的文本比對查詢，而LIKE關鍵字是模糊比對，不會調用全文索引。

例如，利用contains謂詞執行單個分詞的完全比對查詢：

select [tsql] 
from [dbo].[DatabaseLog] 
where contains([tsql], 'searchword', language 1033);      

全文查詢跟Like相比，速度更快，支援的搜尋功能更複雜，使用contains謂詞，不僅能夠執行分詞的完全比對或分詞的字首比對查詢，還能夠執行基于詞根的查詢，基于自定義同義詞的查詢，基于距離和順序的相鄰分詞查詢。但是，和Like 相比，contains謂詞不能進行字尾比對查詢。

contains謂詞傳回的結果是布爾值，如果全文索引列中包含指定的關鍵字或查找模式（pattern），傳回TRUE；否則，傳回FALSE。

contains謂詞支援word查詢和短語查詢，word是指單個分詞，短語（phrase）是由多個word和間隔的空格組成的，對于短語，必須使用雙引号，将多個word組成一個短語。

1，邏輯組合查詢

使用and ，and not， 或 or 邏輯運算符 比對多個word 或 多個phrase

CONTAINS(Name, '"Mountain" OR "Road" ')
CONTAINS(Name, ' Mountain OR Road ')      

2，字首查詢

使用contains謂詞進行字首比對，和like 'prefix%'功能相同，隻不過contains謂詞使用“*”作為通配符，“*”比對0，1或多個字元，字首比對的寫法是：'"prefix*"'，全文索引隻能執行字首比對。

CONTAINS(Name, ' "Chain*" ')
CONTAINS(Name, '"chain*" OR "full*"')      

3，查詢同義詞（thesaurus）或詞幹（stemmer）

Stemmer（詞幹），例如，根據文法規程，英語的動詞 根據數（單數，複數），人稱，時态的不同而存在不同的變化形式，這些單詞都是同源的。

CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ')      

THESAURUS （同義詞），需要導入XML進行配置，SQL Server 提供一個預設的Thesaurus file，是Empty的。如果在Thesaurus file 配置“Author”，“Writer”，“journalist” 是同義詞，在使用fulltext index查詢時，隻要滿足任意一個同義詞，都比對成功。

CONTAINS(Description, ' FORMSOF (THESAURUS, author) ')      

4，距離查詢

使用 near 函數，查詢比對相鄰分詞的資料行，near函數的定義如下，用于需要在查詢模式中指定距離查詢的查詢模式：

NEAR ( ( { <simple_term> | <prefix_term> } [ ,…n ] )  [, <maximum_distance> ] [, <match_order> ] )       

例如：使用Near 函數指定相鄰分詞的距離和比對順序，near((term1,term2，term3),5)表示任意兩個term之間的距離不能超過5, near((term1,term2，term3),5,true)，表示任意兩個term的距離不能超過5，并且按照 term1，term2，term3的順序存在于字元串中。

--regardless of the intervening distance and regardless of order
CONTAINS(column_name, 'NEAR(term1,"term3 term4")')
--searches for "AA" and "BB", in either order, within a maximum distance of five
CONTAINS(column_name, 'NEAR((AA,BB),5)')
--in the specified order with regardless of the distance
CONTAINS(column_name, 'NEAR ((Monday, Tuesday, Wednesday), MAX, TRUE)')      

對于 near((term1,term2,term3),5,true)，term1 和 term5之間最多存在5個term，不包括内部的搜尋分詞，“term2”，例如：

CONTAINS(column_name, 'NEAR((AA,BB,CC),5)')      

這個查詢會比對下面的文本，注意，内部的搜尋分詞CC沒有計算距離：

BB one two CC three four five AA      

例如，在原文本中，分詞bike和control的最大距離不能超過10，分詞bike必須出現在分詞control的前面：

CONTAINS(Comments , 'NEAR((bike,control), 10, TRUE)')      

SQL Server提供的全文搜尋功能，比LIKE關鍵字豐富，具備初級的全文搜尋功能，速度快，維護簡單，缺點是，全文搜尋功能非常有限，在實際的開發中，可以配合開源的全文搜尋引擎，例如，Solr，Elasticsearch等來開發功能更強大的全文搜尋功能。

參考文檔：

Full-Text Search (SQL Server)

CONTAINS (Transact-SQL)

CREATE FULLTEXT CATALOG (Transact-SQL)

Get Started with Full-Text Search

SQLSERVER全文搜尋

Improve the Performance of Full-Text Queries

作者

：悅光陰

出處

：http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。