關於SQL Server 的全文搜尋功能詳解

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）相同，根據全文索引的儲存位置無關。

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等来开发功能更强大的全文搜索功能。

相关推荐：

PlateSpin备份时SQL Server的信息介绍

Python操作SQL Server数据库的方法

SQL server 数据库的数据完整性

以上是關於SQL Server 的全文搜尋功能詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！