SQL Serverの全文検索機能を詳しく解説

SQL Server の全文検索は、単語の分割に基づくテキスト検索機能であり、全文インデックスに依存しています。フルテキスト インデックスは、従来のバランス ツリー (B ツリー) インデックスや列ストレージ インデックスとは異なり、転置インデックス (Invert Index) と呼ばれるデータ テーブルで構成され、単語セグメントと単語間のマッピング関係を保存します。行の一意のキー。転置インデックスは、フルテキスト インデックスの作成時またはフルテキスト インデックスの更新時に、SQL Server によって自動的に作成および維持されます。フルテキスト インデックスには主に、ワード ブレーカー、ステマー、同義語アナライザーの 3 つのアナライザーが含まれています。フルテキスト インデックスに格納されるデータは、単語の分割とその位置などの情報です。単語の分割は、特定の言語の文法規則に基づいて、特定の記号に従って単語の境界を探し、テキストを「単語」に分解します。それぞれの単語は単語セグメンテーション (用語) と呼ばれます。フルテキスト インデックスは、単語セグメンテーションの語幹を抽出し、その語幹の複数の派生形式を 1 つの語幹として保存することがあります。このプロセスはステミングと呼ばれ、関連する単語を同義語に変換します。ユーザーが提供したカスタム同義語リストに基づいて、このプロセスを同義語の抽出と呼びます。

全文インデックスを生成するには、ユーザー テーブル内のテキスト データをセグメント化し (ワード ブレーカー)、語幹を抽出し (ステマー)、同義語を変換し (シソーラス)、単語のセグメント化でストップ ワードをフィルターで除外します (ストップワード)。後続のデータはフルテキスト インデックスに格納されます。データをフルテキスト データに保存するプロセスは、ポピュレート プロセスまたはクロール プロセスと呼ばれます。フルテキスト インデックスの更新方法には、手動入力、自動入力、または増分入力があります。

1. フルテキスト カタログと一意のインデックスを作成する

フルテキスト インデックスを作成する前に、フルテキスト カタログ (フルテキスト カタログ) を作成する必要があります。テキスト インデックスであり、フルテキスト インデックスのコンテナです。すべてのフルテキスト インデックスはフルテキスト カタログに属している必要があります。フルテキスト カタログは論理構造であり、フルテキスト インデックスの保存場所に関係なく、データベース スキーマと同じです。

create fulltext catalog catalog_test
as default;

フルテキスト インデックスを作成するには、一意の単一列の null 非許容インデックスがベース テーブルに存在する必要があります。フルテキスト エンジンは、このインデックスを使用してベース テーブルの行データを結合します。は一意のインデックス キーにマッピングされ、転置インデックスにはインデックス キーと単語セグメント間のマッピング関係が保存されます。

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

2 番目に、フルテキスト インデックスを作成します

各テーブルで作成できるフルテキスト インデックスは 1 つだけです。フルテキスト インデックスを作成するときは、フルテキスト インデックスに格納されているファイル グループ、ストップ ワード リストを考慮する必要があります。フルテキスト インデックスに関連付けられた、フルテキスト インデックスの更新方法、およびテキストに関連付けられた言語。フルテキスト インデックス列はテキスト フィールドである必要があります。例:

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 (言語)

オプション言語はオプションであり、列レベルで言語を指定するために使用されます。言語オプションが指定されていない場合は、SQL Server インスタンスのデフォルト言語が使用されます。システム ビュー sys.fulltext_langages (Transact-SQL) から、システムでサポートされている言語と、それらに対応する LCID および名前を表示します。

2、フルテキストカタログ (fulltext_catalog)

オプション fulltext_catalog_name はフルテキストインデックスのグループを指定するために使用されます、

3、ファイルグループ (filegroup)

オプション filegroup filegroup_name はファイルグループを指定するために使用されますフルテキスト インデックスの保存。ファイル グループが指定されていない場合、フルテキスト インデックスと基になるテーブルは同じファイル グループに保存されます。フルテキスト インデックスの更新は IO 集中型の操作であるため、フルテキスト インデックスをより速く更新するには、ベース テーブルとは異なる物理ハードディスクまたはファイル グループにフルテキスト インデックスを保存するのが最善です。最大 IO 同時実行性。

4. フルテキスト インデックスを埋める方法は、基になるテーブル データが更新されると、自動的に更新される必要があります。これはシステムのデフォルトの動作です。また、フルテキスト インデックスを手動で更新するか、または特定の時間間隔でフルテキスト インデックスを自動的に更新するように設定します。

オプション CHANGE_TRACKING は、フルテキスト インデックス列に関連するデータ更新 (更新、削除、または挿入) をフルテキスト インデックスと同期する必要があるかどうかを指定するために使用されます。

•CHANGE_TRACKING = MANUAL: 手動更新

•CHANGE_TRACKING =AUTO : 自動更新、デフォルト設定、基になるテーブルのデータが変更されると、フルテキスト インデックスが自動的に更新されます。
•CHANGE_TRACKING =OFF、NO POPULATION: 更新しません。オプション NO POPULATION を指定します。これは、フルテキスト インデックスの作成後に更新しないことを示します。インデックスの場合、SQL Server はフルテキスト インデックスを更新 (設定) しません。オプション NO POPULATION が指定されていない場合、SQL Server はフルテキスト インデックスの作成後にフルテキスト インデックスを更新します。

5. ストップ ワード (STOPLIST)

ストップ ワードはノイズ ワードとも呼ばれます。デフォルトでは、フルテキスト インデックスはシステム ストップ ワード リストに関連付けられます。 ）。フルテキスト エンジンは、フルテキスト インデックスにストップ ワードが含まれないように、単語の分割からストップ ワードを削除します。

そうです

3、全文インデックスを作成します

填充全文索引也叫做爬虫（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 中国語 Web サイトの他の関連記事を参照してください。