Sql 全文索引详解.docx

Sql 全文索引详解.docx

《Sql 全文索引详解.docx》由会员分享，可在线阅读，更多相关《Sql 全文索引详解.docx（49页珍藏版）》请在冰豆网上搜索。

Sql全文索引详解

Sql2008全文索引详解

1.

使用全文搜索查询数据

在数据库中快速搜索数据，使用索引可以提高搜索速度，然而索引一般是建立在数字型

或长度比较短的文本型字段上的，比如说编号、姓名等字段，如果建立在长度比较长的文本

型字段上，更新索引将会花销很多的时间。

如在文章内容字段里用like语句搜索一个关键字，

当数据表里的内容很多时，这个时间可能会让人难以忍受。

在SQLServer中提供了一种名为全文索引的技术，可以大大提高从长字符串里搜索数

据的速度，在本章里，将会对全文索引进行详细的介绍。

全文索引的介绍

全文索引与普通的索引不同，普通的索引是以B-tree结构来维护的，而全文索引是一

种特殊类型的基于标记的功能性索引，是由MicrosoftSQLServer全文引擎服务创建和维护

的。

使用全文索引可以快速、灵活地为存储在SQLServer数据库中的文本数据创建基于关

键字查询的索引，与like语句不同，like语句的搜索是适用于字符模式的查询，而全文索引

是根据特定语言的规则对词和短语的搜索，是针对语言的搜索。

在对大量的文本数据进行查询时，全文索引可以大大地提高查询的性能，如对于几百万

条记录的文本数据进行like查询可能要花几分钟才能返回结果，而使用全文索引则只要几秒

钟甚至更少的时间就可以返回结果了。

1.2

全文索引中常用的术语

由于全文索引中使用了较多的新的术语，在此先介绍这些术语：

l全文索引：

一种特殊的索引，能在给定的列中存储有关重要的词及位置的信息，

使用这些信息可以快速进行全文查询，搜索包括特定词或词组的行。

l全文目录：

全文目录是存储全文索引的地方，全文目录必须驻留在与SQLServe

r实例相关联的本地硬盘上，每个全文目录可用于满足数据库内的一个或多个表的

索引需求。

l断字符与词干分析器：

断字符与词干分析器都是用于对全文索引的数据进行语言

分析的。

语言分析通常都会涉及到查找词的边界和组合动词两个方面，其中要查找

词的边界，也就是确定哪几个字符是“词”，称之为“断字”。

__________组合动词也就是词干分

析，用于分析词。

根据语言的不同，语言分析规则也不尽相同，由此可以为每个全

文索引列指定不同的语言。

每种语言的断字符能够使得访问语言生成的词更为准

确。

l标记：

由断字符标识的词或字符串。

l筛选器：

用于从存储在varbinary（max）或image列中的文件内提取指定的文本

类型的文本。

当varbinary（max）或image列中包含带有特定文件扩展名的文档时，

全文搜索会使用筛选器来解释二进制数据，筛选器会从文档中提取文本化信息并用

于建立索引。

l填充（爬网）：

创建维护全文索引的过程叫填充，也叫爬网。

l干扰词：

经常出现，但又不是要搜索的词。

为了精简全文索引，这些词通常会被

忽略。

1.3

全文索引的体系结构

SQLServer的全文索引是由SQLServerFullTextSearch服务来维护的，该服务可以在

Windows操作系统的【管理工具】à【服务】里找到，如图1.1所示，在此可以启动、停

止、暂停、恢复和重新启动该服务。

只有SQLServerFullTextSearch服务在启动状态时，

才能使用全文索引。

图1.1SQLServerFullTextSearch服务

SQLServerFullTextSearch服务由两个部分组件支持：

一个是MicrosoftFull-TextEng

ineforSQLServer（MSFTESQL），也就是SQLServer全文搜索引擎；另一个是Microsoft

Full-TextEngineFilterDeamon（MSFTEFD），也就是全文搜索引擎过滤器。

MicrosoftFull-TextEngineforSQLServer的作用是填充全文索引、管理全文索引和全

文目录、帮助对SQLServer数据库中的数据表进行全文搜索。

MicrosoftFull-TextEngineF

ilterDeamon包含筛选器、协议处理程序和断字符三个组件，其作用是负责从数据表中访问

和筛选数据以及进行断字和词干分析。

其中，筛选器的作用是从文档中提取文本信息，并将

非文本信息和格式化信息（如换行符、字体大小等信息）删除，然后生成文本字符串和属性

的对应，并将它们传递给索引引擎；协议处理程序用于从指定数据库中的表内访问数据；断

字符用于在查询或抓取的文档中确定字符边界位置。

全文索引组件负责对全文索引的初始填充以及当全文索引表中的数据被修改时的更新。

当全文填充（爬网）开始后，数据库引擎会将大量的数据存储到内存里，并通知Microsoft

Full-TextEngineforSQLServer（MSFTESQL）开始进行索引。

当MSFTESQL服务接到进

行索引的通知后，使用协议处理程序组件从内存中取得数据进行处理后生成全文索引。

MSF

TESQL服务会将数据表中的某一列或几列中的字符和二进制数据编制成索引。

在对varbina

ry（max）或image列中的数据编制索引时，筛选器会将基于为该数据指定的文件格式来提取

文本。

在处理索引的过程中MSFTESQL服务通过断字符来将收集到的文本数据分隔成各个

单独的标记或关键字。

这个过程就是全文索引的过程。

由于全文索引与普通的索引不同，全文索引并不是存储在数据表中，而是存储在全文目

录中，所以在使用全文索引来搜索数据时，其运行的流程和普通索引也不一样。

如图1.2所示，当SQLServer发出全文搜索的请求后，会将搜索的条件传递给全文搜

索引擎过滤器，经过MSFTEFD处理后，将数据提供给MSFTESQL，再由MSFTESQL从全文

目录中找到符合要求的数据，再返回给SQLServer进行后续操作。

这就是进行全文搜索的

过程。

图1.2全文搜索流程

1.4

全文目录管理

在前面章节里提到，全文目录的作用是存储全文索引，所以要创建全文索引必须先创建

全文目录。

1

创建全文目录

创建全文目录的方法如下：

（1）启动【SQLServerManagementStudio】，连接到本地默认实例，在【对象资源

管理器】窗口里，选择本地数据库实例à【数据库】à【Northwind】à【存储】à【全文目录】。

（2）右击【全文目录】，在弹出的快捷菜单里选择【新建全文目录】选项。

（3）弹出如图1.3所示的【新建全文目录】对话框，在该对话框的【全文目录名称】

文本框内可以输入全文目录的名称；在【目录位置】文本框内可以输入全文目录的存储路径，

单击其后的【…】按钮可以选择路径，如果不输入的话，默认存储在“ProgramFiles\Microso

ftSQLServer\MSSQL.1\MSSQL\FTData”目录下；在【文件组】下拉列表框里可以选择全文

目录所属的文件组；在【所有者】文本框里可以输入全文目录的所有者；选中【设置为默认

目录】复选框可以将此目录设置为全文目录的默认目录；【区分重音】单选框用于指明目录

是否区分标注字符。

图1.3【新建全文目录】对话框

（4）设置完毕后单击【确定】按钮完成操作。

1

查看与修改全文目录

全文目录添加完毕之后，可以在【对象资源管理器】窗口的【全文目录】树下看到新建

的全文目录。

双击该全文目录，或右击该全文目录，在弹出的快捷菜单里选择【属性】选项，

将会弹出如图1.4所示【全文目录属性】对话框，在该对话框里可以查看全文目录的属性

内容。

在【全文目录属性】对话框里有三个标签：

【常规】选项卡里可以查看和修改全文目录

的设置；【表/视图】选项里可以查看和修改为全文目录分配的表和视图；【填充计划】选

项项里可以添加或修改确定何时填充或重新填充全文目录的计划。

在如图1.4所示的【全文目录属性】对话框里，可以看到全文目录所属的文件组、名

称、上次填充的时间、项计数、填充状态、目录大小、唯一键计数的内容，这些内容是不能

修改的。

可以修改项为：

【默认目录】、【所有者】和【区分重音】三个选项内容。

图1.4查看全文目录属性

在【目录】操作区域，有三个单选按钮，其中【无】表示不执行优化目录、重新生成目

录和重新填充目录操作；选择【优化目录】选项将会优化目录的空间利用率、提高搜索结果

相关排名的准确性，从而提高查询性能；选择【重新生成目录】选项将会删除并重新生成全

文目录，如果对全文目录的属性进行了更改，则必须执行该操作，例如在本对话框中修改了

区分重音选择，则【目录】操作区域会自动变成灰色，系统自动重新生成全文目录；选择【重

新填充目录】选项将会使用数据的最新更改来更新目录。

修改完毕之后，单击【确定】按钮

完成操作。

1

删除全文目录

当全文目录不再需要使用时，可以将其删除，删除方式如下：

（1）启动【SQLServerManagementStudio】，连接到本地默认实例，在【对象资源

管理器】窗口里，选择本地数据库实例à【数据库】à【Northwind】à【存储】à【全文目录】。

（2）右击要删除的全文目录名，在弹出的快捷菜单里选择【删除】选项。

（3）在弹出的【对象删除】对话框中单击【确定】按钮完成操作。

如果要一次性删除多个全文目录，可以先在【对象资源管理器】窗口中定位到【全文目

录】树，在【摘要】对话框里选择多个全文目录，然后单击鼠标右键，在弹出的快捷菜单里

选择【删除】选项。

在弹出的【对象删除】对话框中单击【确定】按钮完成操作。

1.5

全文索引管理

在创建完全文目录之后，可以动手创建全文索引了，下面将介绍如何创建、编辑和删除

全文索引。

1

创建全文索引的注意事项

在创建全文索引之前，先介绍创建全文索引要注意的事项：

l全文索引是针对数据表的，只能对数据表创建全文索引，不能对数据库创建全文

索引。

l在一个数据库中可以创建多个全文目录，每个全文目录都可以存储一个或多个全

文索引，但是每一个数据表只能够创建一个全文索引，一个全文索引中可以包含多

个字段。

l要创建全文索引的数据表必须要有一个唯一的针对单列的非空索引，也就是说，

必须要有主键，或者是具备唯一性的非空索引，并且这个主键或具有唯一性的非空

索引只能是一个字段，不能是多字段的组合。

l包含在全文索引里的字段只能是字符型的或image型的字段。

1

创建全文索引

下面以Northwind数据的“文章”表为例，介绍如何创建全文索引：

（1）启动【SQLServerManagementStudio】，连接到本地默认实例，在【对象资源

管理器】窗口里，选择本地数据库实例à【数据库】à【Northwind】à【表】à【文章】。

（2）右击【文章】数据表，在弹出的快捷菜单里选择【全文索引】à【定义全文索引】。

（3）弹出如图1.5所示【全文索引向导】对话框，此对话框中显示的是全文索引向导

的介绍，单击【下一步】按钮。

图1.5【全文索引向导】对话框

（4）弹出如图1.6所示的【选择索引】对话框，此时可以选择要创建全文索引的数据

表的唯一索引，使用该索引作为全文索引的唯一索引。

在【唯一索引】下拉列表框里，列出

该表中所有的唯一索引。

在该对话框里选择唯一索引后，单击【下一步】按钮。

图1.6【选择索引】对话框

（5）弹出如图1.7所示【选择表列】对话框，此时可以选择要加入全文索引的字段。

在该对话框里可以选择一个或多个字段加入全文索引。

SQLServer2005可以对存储在imag

e类型的字段中的文件进行全文搜索。

image类型的字段中可以存入各种文件，但是SQLS

erver2005只支持Word、Excel、PowerPoint、网页和纯文本文件类型。

如果要对image

类型的字段里的文件进行全文搜索，必须还要有一个字符串类型的字段用于指明存储在ima

ge字段中的文件的扩展名。

如图1.7中的文件字段，如果要对其创建全文索引，必须还要

指定类型列。

选择完毕后单击【下一步】按钮。

图1.7【选择表列】对话框

（6）弹出【选择更改跟踪】对话框，在该对话框里可以定义全文索引的更新方式，一

共有三种更新方式：

l【自动】：

选中此单选按钮后，当基础数据发生更改时，全文索引将自动更新；

l【手动】：

如果不希望基础数据发生更改时自动更新全文索引，请选中此单选按

钮。

对基础数据的更改将保留下来。

不过，若要将更改应用到全文索引，必须手动

启动或安排此进程；

l【不跟踪更改】：

如果不希望使用基础数据的更改对全文索引进行更新，请选中

此单选按钮。

设置完毕后单击【下一步】按钮。

图1.8【选择更改跟踪】对话框

（7）弹出如图1.9所示的【选择目录】对话框，在此可以选择全文索引所存储的全文

目录。

如果没有要选择的全文目录，也可以在此新建一个全文目录。

创建全文目录的方法与

上节中所说的一样。

选择完毕后单击【下一步】按钮。

图1.9【选择目录】对话框

（8）弹出如图1.10所示的【定义填充计划】对话框，在此可以创建全文索引和全文

目录的填充计划，也可以单击【下一步】，在创建完全文索引后再创建填充计划。

图1.10【定义填充计划】对话框

（9）弹出如图1.11所示的【全文索引向导说明】对话框，在该对话框里可以看到全

文索引要完成的工作说明，如果有不正确的设置，可以单击【上一步】按钮返回去重新设置，

如果完全正确则单击【完成】按钮完成操作。

图1.11【全文索引向导说明】对话框

1

查看与修改全文索引

在创建完全文索引之后，右击数据表，在弹出的快捷菜单里选择【全文索引】à【属性】

可以查看全文索引的设置，如图1.12所示。

图1.12【全文索引属性】常规信息对话框

在图1.12中可以看到全文索引的目前设置情况，在该对话框中可以修改是否启用全文

索引和全文索引的更改跟踪信息。

单击【列】标签，打开如图1.13所示的全文索引的列信

息，在此可以修改全文索引所包含的列信息。

图1.13【列】选项卡

1

启用和禁用全文索引

全文索引创建完毕之后，不会立即自动启用，所以需要手动启用全文索引。

启动全文索

引的方法如下：

（1）在【对象资源管理器】窗口里展开树形目录，定位到要启用全文索引的数据表。

（2）右击要启用全文索引的数据表，在弹出的快捷菜单中选择【全文索引】à【启用

全文索引】。

如果要禁用全文索引，也可以采取以下的方法来禁用全文索引：

（1）在【对象资源管理器】窗口里展开树形目录，定位到要启用全文索引的数据表。

（2）右击要禁用全文索引的数据表，在弹出的快捷菜单中选择【全文索引】à【禁用

全文索引】。

1

删除全文索引

当全文索引不再使用时，可以将其删除，删除方法如下：

（1）右击要删除全文索引的数据表，在弹出的快捷菜单里选择【全文索引】à【删除

全文索引】。

（2）在弹出的【删除全文索引】对话框里单击【确定】按钮完成操作。

1.6

填充全文索引

填充全文索引实质上就是更新全文索引，其目的是让全文索引可能够反映最新的数据表

内容。

1

填充全文索引的方式

填充全文索引一共有三种方式：

l完全填充：

完全填充方式通常发生在首次填充全文目录或全文索引时，在前一节

中所说到的“启用全文索引”时，就已经对全文索引进行了一次完全填充，以后就可

以使用基于更改跟踪的填充和基于增量时间戳的填充来维护全文索引。

l基于更改跟踪方式的填充：

SQLServer会记录设置了全文索引的数据表中修改

的行，这些记录存储在日志中，在某个适当时机时将这些更改填入到全文索引中。

l基于增量时间戳方式的填充：

也就是增量填充，在全文索引中更新上次填充之后

更新的行。

增量填充要求索引表中必须有timestamp数据类型的字段，如果没有

该类型的字段，则无法执行增量填充，系统将会以完全填充的方式来取代增量填充

方式进行填充。

1

填充全文索引

由于填充全文索引有三种不同的方式，所以填充全文索引的方法也不相同。

如果要以完

全填充或增量填充方式来填充全文索引，则只要右击全文索引所在的数据表，在弹出的快捷

菜单里选择【全文索引】à【启动完全填充】或【启动增量填充】选项即可。

如图1.1所

示。

图1.14填充全文索引

更改跟踪方式填充全文索引分为手动和自动两种方法，在默认情况下是自动填充，如果

要更改为手动方式，可以右击全文索引所在的数据表，在弹出的快捷菜单里选择【全文索引】

à【手动跟踪更改】选项，如图1.1所示。

选择完毕之后，SQLServer会自动跟踪数据表中的数据更改情况，但并不将其更新到

全文索引中，只有在需要将这些更新反应到全文索引上时，右击全文索引所在的数据表，在

弹出的快捷菜单里选择【全文索引】à【应用跟踪的更改】选项后，才会将更新反应到全文

索引上。

如果在图1.1所示界面里选择了【自动跟踪更改】选项，则由SQLServer自动将记

录的数据表的更改更新到全文索引中，不再需要人工进行填充。

如果在数据库中有多个数据表创建了全文索引，可以使用重新生成索引目录的方法将所

有的全文索引进行完全填充，其方法如下：

（1）在【对象资源管理器】窗口里展开树形目录，定位到【服务器】à【数据库】à

【Northwind】à【存储】à【全文目录】。

（2）右击【全文目录】，在弹出的快捷菜单中选择【全部重新生成】选项。

（3）在弹出的【重新生成所有全文目录】对话框里单击【确定】按钮完成操作。

1

定时填充全文索引

在SQLServer2005中，可以为填充全文索引设置计划，让系统自动定时填充全文索引，

其方法如下：

（1）右击全文索引所在的数据表，在弹出的快捷菜单里选择【全文索引】à【属性】

来查看全文索引的设置，如图1.12所示，在该图中选择【计划】选项。

（2）弹出如图1.15所示全文索引填充计划对话框，在该对话框中单击【新建】按钮。

图1.15全文索引填充计划对话框

（3）弹出如图1.16所示的【新建全文索引表计划】对话框。

l在【名称】文本框里可以输入该计划名称。

l在【计划类型】下拉列表框里，可选项有“SQLServer代理启动时自动启动”：

也就是在S

QLServer代理启动时自动填充全文索引；“CPU空闲时启动”：

也就是当CPU空闲时填充全

文索引；“执行一次”：

在指定时间时填充全文索引，只填充一次；“重复执行”：

可以设定多

次填充全文索引的方式。

l如果选中【已启用】复选框，则启用该填充全文索引的计划。

l在【执行一次】区域里，可以设置计划类型为“执行一次”的执行时间，在到达该时间时，

自动填充全文索引。

l在【频率】区域里，可以设置计划类型为“重复执行”的执行频率。

在【执行】下拉列表框

里可选择项为“每天”、“每周”和“每月”，分别用于指定每天、每周和每月执行填充计划的频

率。

l在【持续时间】区域里可以设置该计划的开始日期与结束日期，其中结束日期可以设置为

无结束日期，也就是永远执行下去。

图1.16【新建全文索引表计划】对话框

（4）设置完毕后单击【确定】按钮完成计划设计，返回如图1.17所示对话框。

图1.17全文索引填充计划对话框

（5）在如图1.17所示对话框里，可以选择全文索引填充计划所要执行的填充方式，

设置完毕后单击【确定】按钮完成操作。

注意：

如果单击【确定】按钮之后出现如图1.18所示的失败对话框，请先安装SQL

Server2005SP1（下载地址为：

ylang=zh-cn&

FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc），然后再安装版本为9.0.2153的SQL

Server2005累积修补程序包（下载地址为：

n）。

图1.18添加计划失败对话框

使用类似的方法也可以定时填充全文目录：

（1）右击全文目录名，在弹出的快捷菜单里选择【属性】选项。

（2）在弹出的如图1.4所示的【全文目录属性】对话框里选择【填充计划】选项。

（3）弹出与1-15所示的填充计划对话框，其添加计划的步骤与创建全文索引的填充

计划几乎相同，在此就不再赘述了。

1.7

使用全文搜索查询

设置完全文索引并填充完毕之后，就可以通过全文搜索来查询数据了。

使用全文搜索来

查询数据所用到的T-SQL语句也是SELECT语句，只是在设置查询条件时和前面所说过的S

ELECT语句的查询条件设置有些不同。

在T-SQL语言中，可以在SELECT语句的WHERE子

句里设置全文搜索的查询条件，也可以在FROM子句里设置查询条件，此时将返回结果作

为FROM子句中的表格来使用。

如果要在WHERE子句里设置全文搜索的查询条件，可以使用CONTAINS和FREETEXT

两个谓词；如果要在FROM子句里设置全文搜索的查询条件，可以使用CONTAINSTABLE

和FREETEXTTABLE两个行集值函数。

1

使用CONTAINS

搜索

CONTAINS用于SELECT语句的WHERE子句中，可以支持使用复杂的语法在字符列中

搜索词、子句、衍生字或位置相近的字符串。

使用CONTAINS谓词可以在数据表中使用以

下五种形式搜索数据：

l简单词：

也就是可以搜索一个或多个特定的词或短语。

词可以包括一个或多个字

符，中间没有空格或标点。

短语可以是由空格分隔的多个词组成，但词之间可以有

标点也可以没标点。

l派生词：

也就是可以搜索特定词的变形，一般是指英语中的单词，其有过去式、

现在式、将来式等不同的形式。

派生词是指可以包含该单词的所有其他形式。

l前缀词：

也就是可以搜索指定文本开头的词或短语。

一般也用于英文单词中，可

以指定一个英文单词的前几个字母来作为搜索条件。

l加权词：

也就是可以给多个搜索条件加上权值，加权值越高的记录排在越前面。

l邻近词：

也就是可以搜索与另一个词或短语相邻近的词或短语。

下面分别介绍如何使用这些不同的方式来搜索数据。

1.7.1.1CONTAINS的语法

CONTAINS的语法代码如下：

CONTAINS

（{column_name|（column_list）|*}--列名

''--搜索语

句

[,LANGUAGElanguage_term]--发出查询时所用

的语言

）

:

:

=

{--简单词搜索方式

|--前缀词搜索方式

|--派生词搜索方式

|--邻近词搜索方式

|--权重词搜索方式

}

|{（）--搜索语句

[{