Ausführliche Erläuterung der Volltextsuchfunktion von SQL Server

Die Volltextsuche von SQL Server ist eine Textabruffunktion, die auf Wortsegmentierung basiert und auf der Volltextindizierung basiert. Der Volltextindex unterscheidet sich vom herkömmlichen ausgeglichenen Baumindex (B-Tree) und dem Spaltenspeicherindex. Er besteht aus einer Datentabelle, die als invertierter Index (Invert Index) bezeichnet wird, und speichert die Zuordnungsbeziehung zwischen Wortsegmenten und dem eindeutige Schlüssel der Zeilen. Invertierte Indizes werden von SQL Server automatisch erstellt und verwaltet, wenn ein Volltextindex erstellt oder aktualisiert wird. Der Volltextindex enthält hauptsächlich drei Analysefunktionen: Wörtertrennung, Wortstammerkennung und Synonymanalyse. Bei den im Volltextindex gespeicherten Daten handelt es sich um Informationen wie die Wortsegmentierung und deren Position. Die Wortsegmentierung basiert auf den grammatikalischen Regeln einer bestimmten Sprache, sucht nach Wortgrenzen anhand bestimmter Symbole und zerlegt den Text in „Wörter“. Jedes Wort wird als Wortsegmentierung (Begriff) bezeichnet. Der Volltextindex extrahiert manchmal den Wortstamm und speichert mehrere abgeleitete Formen des Wortstamms als einen einzigen Wortstamm. Er wandelt verwandte Wörter in Synonyme um auf der vom Benutzer bereitgestellten benutzerdefinierten Synonymliste. Dieser Vorgang wird als Extrahieren von Synonymen bezeichnet.

Beim Generieren eines Volltextindex werden die Textdaten in der Benutzertabelle in Wörter segmentiert (Worttrennung), Stämme extrahiert (Stemmer), Synonyme konvertiert (Thesaurus) und Stoppwörter herausgefiltert werden die Wortsegmentierung (Stopword) und speichert schließlich die verarbeiteten Daten im Volltextindex. Der Prozess des Speicherns von Daten in Volltextdaten wird als Auffüllen- oder Crawl-Prozess bezeichnet. Die Aktualisierungsmethode des Volltextindex kann manuelles Ausfüllen, automatisches Ausfüllen oder inkrementelles Ausfüllen sein.

1. Erstellen Sie einen Volltextkatalog und einen eindeutigen Index

Bevor Sie einen Volltextkatalog erstellen (Volltextkatalog). Der Katalog wird zum Organisieren des Volltextindex verwendet und ist der Container des Volltextindex. Jeder Volltextindex muss zu einem Volltextkatalog gehören. Der Volltextkatalog ist eine logische Struktur, die unabhängig vom Speicherort des Volltextindex dem Datenbankschema entspricht.

create fulltext catalog catalog_test
as default;

Um einen Volltextindex zu erstellen, muss in der Basistabelle ein eindeutiger, einspaltiger, nicht nullbarer Index vorhanden sein, und die Volltext-Engine verwendet diesen Index für jede Datenzeile In der Basistabelle wird ein eindeutiger Indexschlüssel zugeordnet, und der invertierte Index speichert die Zuordnungsbeziehung zwischen dem Indexschlüssel und der Wortsegmentierung.

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

2. Erstellen Sie einen Volltextindex

Jede Tabelle kann nur einen Volltextindex erstellen. Beim Erstellen eines Volltextindex müssen Sie die darin gespeicherte Dateigruppe berücksichtigen der Volltextindex und die Deaktivierung der Volltextindexzuordnung. Die Wortliste, die Aktualisierungsmethode des Volltextindex und die mit dem Text verknüpfte Sprache. zum Beispiel:

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, Sprache (Sprache)

Option Sprache ist optional und wird verwendet, um die Sprache auf Spaltenebene anzugeben. Der Wert dieser Option kann der Name oder die LCID von sein die Sprache. Wenn die Sprachoption nicht angegeben ist, wird die Standardsprache der SQL Server-Instanz verwendet. Zeigen Sie die vom System unterstützten Sprachen und ihre entsprechenden LCIDs und Namen in der Systemansicht sys.fulltext_linguals ​​(Transact-SQL) an.

2, Volltextkatalog (fulltext_catalog)

Option fulltext_catalog_name wird verwendet, um die Gruppe des Volltextindex anzugeben,

3, Dateigruppe (filegroup)

Option Dateigruppe Dateigruppenname wird verwendet, um die Dateigruppe anzugeben, in der der Volltextindex gespeichert ist. Wenn keine Dateigruppe angegeben ist, werden der Volltextindex und die zugrunde liegende Tabelle in derselben Dateigruppe gespeichert. Da das Aktualisieren des Volltextindex ein IO-intensiver Vorgang ist, ist es für eine schnellere Aktualisierung des Volltextindex am besten, den Volltextindex auf einer anderen physischen Festplatte oder Dateigruppe als der Basistabelle zu speichern maximale IO-Parallelität.

4. Die Vorgehensweise zum Füllen des Volltextindex

ist die gleiche wie bei der Aktualisierung der zugrunde liegenden Tabellendaten ist das Standardverhalten des Systems. Es kann auch eine manuelle Aktualisierung des Volltextindex oder eine automatische Aktualisierung des Volltextindex in bestimmten Zeitintervallen konfiguriert werden.

Option CHANGE_TRACKING wird verwendet, um anzugeben, ob Datenaktualisierungen (Aktualisieren, Löschen oder Einfügen) in Bezug auf Volltextindexspalten mit dem Volltextindex synchronisiert werden müssen,
•CHANGE_TRACKING = MANUAL: manuell update
•CHANGE_TRACKING = AUTO: Automatisch aktualisieren, Standardeinstellung, wenn sich die zugrunde liegenden Tabellendaten ändern, wird der Volltextindex automatisch aktualisiert,
•CHANGE_TRACKING =OFF, NO POPULATION: Nicht aktualisieren, geben Sie die Option NO an POPULATION gibt an, dass SQL Server den Volltextindex nach der Erstellung des Volltextindex nicht aktualisiert (auffüllt). Wenn die Option NO POPULATION nicht angegeben ist, aktualisiert SQL Server den Volltextindex nach der Erstellung des Volltextindex .

5, Stoppwort (STOPLIST)

Stoppwort (StopWord) wird auch Füllwort genannt. Standardmäßig wird jeder Volltextindex mit einer Stoppwortliste verknüpft index Zugeordnet ist das Systemstoppwort (Systemstoppliste). Die Volltext-Engine entfernt Stoppwörter aus der Wortsegmentierung, sodass der Volltextindex keine Stoppwörter enthält.

STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

Drittens füllen Sie den Volltextindex aus

填充全文索引也叫做爬虫（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 数据库的数据完整性

Das obige ist der detaillierte Inhalt vonAusführliche Erläuterung der Volltextsuchfunktion von SQL Server. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!