SQL Server 全文搜尋功能、全文索引方式介紹

悅光陰發表於2019-01-30
SQLServer索引

　　SQL Server 的全文搜尋(Full-Text Search)是基於分詞的文字檢索功能，依賴於全文索引。全文索引不同於傳統的平衡樹(B-Tree)索引和列儲存索引，它是由資料表構成的，稱作倒轉索引(Invert Index)，儲存分詞和行的唯一鍵的對映關係。倒轉索引是在建立全文索引或更新全文索引時，由SQL Server自動建立和維護的。

　　全文索引主要包含三種分析器：分詞器(word Breaker)、詞幹分析器(stemmer)和同義詞分析器。全文索引中儲存的資料是分詞及其位置等資訊，分詞是基於特定語言的語法規則，按照特定的符號尋找詞語的邊界，把文字分解為“單詞”，每一個單詞叫做一個分詞(term);全文索引有時會提取分詞的詞幹，把詞幹的多種派生形式儲存為單一詞幹，這個過程叫做提取詞幹;根據使用者提供的自定義同義詞列表，把相關的單詞轉換為同義詞，這個過程叫做提取同義詞。

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

一、建立全文目錄和唯一索引

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

  1. create fulltext catalog catalog_test
  2. as default;

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

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

二、建立全文索引

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

  1. create fulltext index
  2. on [dbo].[DatabaseLog]
  3. (
  4. [tsql] language 1033
  5. )
  6. key index ui_dbLogID
  7. on (catalog_test,filegroup [primary])
  8. 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)。全文引擎把停用詞從分詞中刪除，使全文索引不會包含停用詞。?

  1. STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

三、填充全文索引

　　填充全文索引也叫做爬蟲(crawl)程式，或填充(Population)程式。由於建立或填充全文索引會消耗大量的系統(IO、記憶體)資源，因此儘量選擇在系統空閒時對全文索引進行填充。在建立全文索引時，透過指定選項 CHANGE_TRACKING= MANUAL，或 CHANGE_TRACKING= OFF, NO POPULATION，新建的全文索引不會立即填充，使用者可以選擇在系統空閒時，使用 alter fulltext index 語句執行填充操作。只有填充全文索引之後，全文索引才包含基礎表的分詞資料。?

  1. alter fulltext index
  2. on table_name
  3. start { full | incremental | update } population;

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

　　1、FULL POPULATION：全部填充，從基礎表中獲取每一行，重新編入全文索引;

　　2、INCREMENTAL POPULATION：增量填充，前提是基礎表中包含timestamp欄位，從上一次填充之後，只把更新之後的資料編入全文索引;

　　3、UPDATE POPULATION：更新填充，從上一次填充之後執行更新(insert、update、或delete)操作的資料行重新編入索引;

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

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

　　如果想要在查詢中使用全文索引，通常使用CONTAINS謂詞來呼叫全文索引，實現比LIKE關鍵字更復雜的文字匹配查詢，而LIKE關鍵字是模糊匹配，不會呼叫全文索引。

　　例如，利用contains謂詞執行單個分詞的完全匹配查詢：?

  1. select [tsql]
  2. from [dbo].[DatabaseLog]
  3. 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?

  1. CONTAINS(Name, '"Mountain" OR "Road" ')
  2. CONTAINS(Name, ' Mountain OR Road ')

2、字首查詢

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

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

　　3，查詢同義詞(thesaurus)或詞幹(stemmer)

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

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

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

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

4、距離查詢

　　使用 near 函式，查詢匹配相鄰分詞的資料行，near函式的定義如下，用於需要在查詢模式中指定距離查詢的查詢模式：?

  1. 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的順序存在於字串中。?

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

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

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

　　這個查詢會匹配下面的文字，注意，內部的搜尋分詞CC沒有計算距離：?

  1. BB one two CC three four five AA

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

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

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

總結

　　以上所述是小編給大家介紹的SQL Server 全文搜尋功能介紹，希望對大家有所幫助。

相關文章