SQL Serverの全文検索機能を詳しく解説-mysql チュートリアル-php.cn

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