SQL Server의 전체 텍스트 검색 기능에 대한 자세한 설명

SQL Server의 전체 텍스트 검색은 단어 분할을 기반으로 하는 텍스트 검색 기능이며 전체 텍스트 인덱싱을 사용합니다. 전체 텍스트 인덱스는 기존의 균형 트리(B-Tree) 인덱스 및 열 저장 인덱스와 달리 반전 인덱스(Invert Index)라는 데이터 테이블로 구성되며 단어 세그먼트와 단어 세그먼트 간의 매핑 관계를 저장합니다. 행의 고유 키. 반전된 인덱스는 전체 텍스트 인덱스를 만들거나 전체 텍스트 인덱스를 업데이트할 때 SQL Server에서 자동으로 만들고 유지 관리합니다. 전체 텍스트 인덱스에는 주로 단어 분리기, 형태소 분석기, 동의어 분석기의 세 가지 분석기가 포함되어 있습니다. 전체 텍스트 인덱스에 저장되는 데이터는 단어 분할 및 해당 위치와 같은 정보입니다. 단어 분할은 특정 언어의 문법 규칙을 기반으로 특정 기호에 따라 단어 경계를 찾아 텍스트를 "단어"로 분해합니다. 각 단어를 단어 분할(용어)이라고 합니다. 전체 텍스트 인덱스는 때때로 단어 분할의 어간을 추출하고 어간의 여러 파생 형태를 단일 어간으로 저장합니다. 이를 어간 기반의 동의어로 변환합니다. 사용자가 제공한 사용자 정의 동의어 목록에서 이 과정을 동의어 추출이라고 합니다.

전체 텍스트 인덱스를 생성하려면 사용자 테이블에서 텍스트 데이터를 분할(Word breaker)하고 어간을 추출(Stemmer)하고, 동의어를 변환(Thesaurus)하고, 단어 분할에서 중지 단어(Stopword)를 필터링하는 것입니다. , 최종 처리 후속 데이터는 전체 텍스트 인덱스에 저장됩니다. 데이터를 전체 텍스트 데이터로 저장하는 프로세스를 채우기 또는 크롤링 프로세스라고 합니다. 전체 텍스트 인덱스의 업데이트 방법은 수동 채우기, 자동 채우기 또는 증분 채우기일 수 있습니다.

1. 전체 텍스트 카탈로그 및 고유 인덱스 만들기

전체 텍스트 인덱스를 만들기 전에 전체 텍스트 카탈로그(전체 텍스트 카탈로그)를 만들어야 합니다. 텍스트 인덱스이며 전체 텍스트 인덱스의 컨테이너입니다. 모든 전체 텍스트 인덱스는 전체 텍스트 카탈로그에 속해야 합니다. 전체 텍스트 카탈로그는 전체 텍스트 인덱스의 저장 위치에 관계없이 데이터베이스 스키마와 동일한 논리적 구조입니다.

create fulltext catalog catalog_test
as default;

전체 텍스트 인덱스를 생성하려면 Null을 허용하지 않는 고유한 단일 열 인덱스가 기본 테이블에 있어야 합니다. 전체 텍스트 엔진은 이 인덱스를 사용하여 기본 테이블의 각 항목을 결합합니다. 고유한 인덱스 키에 매핑되며, 반전된 인덱스에는 인덱스 키와 단어 세그먼트 간의 매핑 관계가 저장됩니다.

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, 언어(언어)).

옵션 언어는 선택 사항이며 열 수준에서 언어를 지정하는 데 사용됩니다. 이 옵션의 값은 언어 이름 또는 LCID일 수 있습니다. 언어 옵션이 지정되지 않으면 SQL Server 인스턴스의 기본 언어가 사용됩니다. 시스템 보기 sys.fulltext_언어s ​​(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 옵션을 지정하여 전체 텍스트를 생성한 후 이를 나타냅니다. index, SQL Server는 전체 텍스트 인덱스를 업데이트(채우기)하지 않습니다. Option NO POPULATION을 지정하지 않으면 SQL Server는 전체 텍스트 인덱스를 생성한 후 전체 텍스트 인덱스를 업데이트합니다.

5. 중지 단어(STOPLIST)

중지 단어(StopWord)는 의미 없는 단어라고도 합니다. 각 전체 텍스트 인덱스는 기본적으로 시스템 중지 단어와 연결됩니다. (시스템 중지 목록). 전체 텍스트 엔진은 전체 텍스트 인덱스에 중지 단어가 포함되지 않도록 단어 분할에서 중지 단어를 제거합니다.

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!