图片 3

向SQL Server全文索引进军,艰难历程

全文检索可以对varchar,text,image型字段进行检索,但一个表最多只能建一个全文索引SQL
Server 2000 引入了对存储在 image
列中的这些类型的数据执行全文检索的能力。如果没有全文索引,对字符的模糊查询只能对基表进行全表扫描,执行模糊查询都需要对全表扫描或索引扫描意味着消耗大量IO。如果模糊查询经常发生,会造成数据库性能恶化。本篇为简单起见,仅在varchar型字段上图文演示非常完整的而且是高效可行的全文索引创建及维护过程.1:在企业管理器中展开要建立全文索引的数据库,右击“全文目录”,选择“新建全文目录”如
图1
所示。可以修改全文目录的路径,通过将全文目录放在单独的磁盘或磁盘阵列可以提高效率。建议不要对整个全文目录进行任何形式的填充调度。===================
图1
===================2:接下来对具体的表添加全文索引。全文索引不能建立在视图上,只能建立在有唯一索引的基础表上。下图2
演示如何对表建立全文索引。演示中的 t_fulltext
表的建表及初始化数据的脚本如下:create table t_fulltext(rid int
identity(1,1),title varchar(256) not null,writetime datetime
default(getdate()))goalter table t_fulltext add constraint pk_fulltext
primary key ( rid )go–初始化数据insert into t_fulltext(title)
values(‘山东蓬莱河西金矿透水事故2人死亡3人失踪(21:29)’)insert into
t_fulltext(title) values(‘泰国将在广西南宁设立总领事馆(21:10)’)insert
into t_fulltext(title)
values(‘河北安徽等12省减免农业税等政策落实到位(20:43)’)insert into
t_fulltext(title) values(‘台湾学者托梦预测陈水扁元旦讲话(20:04)’)insert
into t_fulltext(title)
values(‘澳门特首何厚铧发表元旦献辞向市民问候新年(20:02)’)insert into
t_fulltext(title)
values(‘南水北调首个建成项目山东段济平干渠通水成功(19:48)’)insert into
t_fulltext(title) values(‘组图:北京110摩托车巡警亮相(18:07)’)insert
into t_fulltext(title) values(‘福建广西发生患者死亡事件
医院责任人被处理(17:33)’)insert into t_fulltext(title)
values(‘北京大容量公交车运营首日遭遇乘客爆满(17:30)’)insert into
t_fulltext(title)
values(‘北京公安系统今天全部停休严查五环内放炮(17:30)’)insert into
t_fulltext(title)
values(‘研究发现海龙基因酶对禽流感病毒有杀伤作用(17:27)’)insert into
t_fulltext(title)
values(‘上海世博会筹办进入实质性操作面临五大挑战(17:03)’)insert into
t_fulltext(title)
values(‘台媒体称陈水扁元旦祝词必将老调重弹(15:30)’)insert into
t_fulltext(title) values(‘珠三角咸潮影响范围小于去年(15:12)’)insert
into t_fulltext(title)
values(‘上海市民体验轨道交通4号线列车(组图)(14:53)’)insert into
t_fulltext(title)
values(‘广州亿元集资诈骗案主犯王日升被判死缓(图)(14:44)’)insert into
t_fulltext(title) values(‘
评论:取消农业税让民众共享经济增长成果(17:30)’)insert into
t_fulltext(title)
values(‘香港媒体谈美国应对中国新策略:合作与防范(15:57)’)insert into
t_fulltext(title)
values(‘专访中国地震局局长陈建民:地震是能够预测的(15:56)’)insert into
t_fulltext(title)
values(‘评论:野蛮公交莫成城市丑陋形象代言人(13:27)’)insert into
t_fulltext(title)
values(‘台媒体称当局与民间企业对立非民众之福(12:50)’)===================
图2 ===================3:出现的欢迎界面如 图3=================== 图3
===================4:为全文索引选择唯一索引,如 图4===================
图4
===================5:选择全文索引的列,注意一张表只能建一个全文索引。建议不要选择断词的语言,这样实际会使用默认的断词语言。如图5
所示。 通过运行sp_configure ‘default full-text
language’可以查看默认的断词语言,如我的机器上运行上条语句,config_value
值是 2052它表示简体中文。其它代码所表示的语言可以查阅Book
Online的“default full-text language”关键词。由于SQL
Server对中文断词并不是很高明,因此并不能依靠SQL
Server带给您非常智能的搜索,这意味着您的全文搜索结果,可能有不希望出现的记录,或您希望出现的记录没有出现。
=================== 图5 ===================6:将表指派到全文目录,如 图6
所示,也可以通过此界面创建新目录。=================== 图6
===================7:接下来会提示对表进行填充调度,为能够准实时地把基表的全文索引列的修改准实时地应用到全文索引,建议使用下面介绍的其它方法进行索引维护。如
图7
所示,建议不要在这里添加任何调度,直接点击“下一步”。如果数据不会再修改,则在一次完全填充以后,不用再增加其它类型的表级填充。如果实施到本步骤,不再添加任何形式的表调度,那么基表数据的更新,将不能更新到全文索引。
=================== 图7 ===================8:如 图8
所示出现的完成界面提示中点击“完成
”,请稍微等待直到出现下一界面。=================== 图8
===================9:在如 图9 所示的界面中点击“确定”。
由于尚未在表上添加任何调度,因此还需要继续设置。=================== 图9
===================10:在企业管理器中右击 f_fulltext 表,如 图10
所示,选中“更改跟踪”。选中更改跟踪后,以后对基表数据的修改,才能反映到全文索引中。特别注意:一旦选中“更改跟踪”,根据经验,其实是必须对该表进行完全填充的。如果是在线使用的全文索引数据库,选中“更改跟踪”会导致性能迅速下降,导致全文索引无法使用。
=================== 图10 ===================11: 在企业管理器中右击
f_fulltext 表,如 图11
所示,选中“更新后台中的索引”。到本步,全文索引就完全完成了。由于我们选中了“更改跟踪”,此时实际已经开始了表的完全填充。根据经验,300万条全文索引字段长为
50 的表,需要8到10小时才能完成完全填充。=================== 图11
===================12:可以通过双击全文目录下的全文索引,查看该全文目录的属性,如
图12
所示,状态为“空闲”,而项目计数大于0,则表示完全填充已经完成了。===================
图12 ===================13:如何进行全文索引查询全文索引查询建议使用
CONTAINS 及 FREETEXT 谓词进行查询。如果关键词仅包括干扰词 select * from
t_fulltext where contains(title,’3’)则会报如下错误服务器: 消息
7619,级别 16,状态 1,行
1全文操作运行失败。查询子句只包含被忽略的词。使用下面的语法,能避免出现错误,且能够确实查询出正确的记录。select
* from t_fulltext where contains(title,'”*3*”‘)13
台媒体称陈水扁元旦祝词必将老调重弹(15:30) 2005-12-31 23:17:17.67010
北京公安系统今天全部停休严查五环内放炮(17:30) 2005-12-31 23:17:17.65317
评论:取消农业税让民众共享经济增长成果(17:30) 2005-12-31 23:17:17.6709
北京大容量公交车运营首日遭遇乘客爆满(17:30) 2005-12-31 23:17:17.6538
福建广西发生患者死亡事件 医院责任人被处理(17:33) 2005-12-31
23:17:17.653全文索引的简单介绍就到这里了,希望能起个抛砖引玉的作用。

 

转自:

     
写这篇文章本来不在计划之内,以前也没有接触过全文索引。偶然的一次备课中。突然有了灵感。决定将自己创建及其使用全文索引的过程记录下来,以备后续使用。

最近搜索了一下全文检索,发现了一些问题,现在总结如下:

至于全文索引的概念,网上一大堆,这里就不再赘述。直接切入正题。

全文索引和查询概念(摘自SQL 联机帮助)

创建全文索引步骤:

全文索引、查询和同步化最主要的设计要求是,在注册进行全文检索的所有表上都有一个唯一的全文键列(或者单列主键)。全文索引对使用的重要字及其所在位置进行跟踪。

1:对着数据库点击右键-选择属性-选择文件,选中“使用全文索引”

例如,假定有一个对 DevTools 表的全文索引。全文索引可能指出在 Abstract
列的第 423 个和第 982 个单词处找到了单词 Microsoft,所在的行与 ProductID
6
关联。该索引结构支持对所有包含被索引单词的项进行有效检索,以及高级检索操作,如短语检索和邻近检索。

图片 1

为防止全文索引因包含很多对检索没有帮助的词而变得臃肿,a、and、is 或 the
这类额外的词都忽略不计。例如,指定”the products ordered during these
summer months”与指定”products ordered during summer
months”是一样的。有这两个字符串的行都会被返回。

 

目录 \Mssql\Ftdata\Sqlserver\Config
下提供了多种语言的干扰词列表。在安装带有全文检索支持的 Microsoft®
SQL Server™
时会创建这个目录,并同时安装干扰词文件。干扰词文件可以编辑。例如,高技术公司的系统管理员可以把单词
computer
添加到他们的干扰词表中去。(如果编辑干扰词文件,则必须在更改生效之前重新填充全文目录。)下表显示了干扰词文件及其相应的语言。

该选项只要勾选就可以了,默认都是勾选的。

干扰词文件      语言

2:对着表点击右键-全文索引-定义全文索引


3:点击下一步,如果这个表中没有唯一性索引就会出现下图所示

Noise.chs      简体中文
Noise.cht      繁体中文
Noise.dat      语言中性
Noise.deu      德语
Noise.eng      英语(英国)
Noise.enu      英语(美国)
Noise.esn      西班牙语
Noise.fra      法语
Noise.ita      意大利语
Noise.jpn      日语
Noise.kor      韩文
Noise.nld      荷兰语
Noise.sve      瑞典语

图片 2

在处理全文查询时,检索引擎将满足检条件的行的键值返回给 Microsoft SQL
Server。比如有一个 SciFi 表,其中 Book_No 列是主键列。

 

Book_No   Writer     Title

4:选择表列,选择断字符语言。


5:点击下一步,这里的选项要注意,如果不想再表、视图更改的时候更新全文索引,那就选择不跟踪更改;这样就可以选择是否在创建索引时启动完全填充了。

A025     Asimov      Foundation’s Edge
A027     Asimov      Foundation and Empire
C011     Clarke      Childhood’s End
V109     Verne       Mysterious Island

6:点击下一步创建索引要保存的目录,全文索引的索引文件是以文件的形式保存到硬盘上的。

假定想使用一个全文检索查询来查找包含单词 Foundation
的书名。在本例中,将从全文索引获得值 A025 和 A027。然后 SQL Server
用这些键值和其它栏的信息响应该查询。

7:之后就可以设置自动填充、手动跟踪更改,还有设置计划了。

下表显示了存储全文索引数据所使用的语言。这些语言基于 SQL Server
安装期间选择的 Unicode 排序规则区域设置标识符。

上述步骤虽然简单,但是我在创建时,碰到了N多问题

Unicode 排序规则区域设置标识符    全文数据存储所用的语言

问题描述:


01.首先我对表点击右键的时候,全文索引菜单项是灰色,不可用状态

中文注音符号(台湾)              繁体中文 
汉语拼音                         简体中文 
中文笔画                         简体中文 
中文笔画(台湾)                 繁体中文 
荷兰语                           荷兰语 
英语(英国)                     英语(英国) 
法语                            法语 
通用 Unicode                    英语(美国) 
德语                            德语 
德文电话簿                       德语 
意大利语                         意大利语 
日语                            日语 
日语 Unicode                    日语 
韩文                            韩文 
韩文 Unicode                    韩文 
西班牙语(现代)                 西班牙语 
瑞典/芬兰语                      瑞典语 

如下图

此列表中没有的其它所有 Unicode
排序规则区域设置标识符值都映射到使用空格分隔单词的中性语言单词的断字符和词干分隔符。

 图片 3

说明  Unicode
排序规则区域设置标识符设置用于所有可进行全文索引的数据类型(如
char、nchar 等)。如果为 char、varchar 或 text
类型列的排序次序设置的语言类型,不是 Unicode
排序规则区域设置标识符语言,那么在对 char、varchar 和 text
类型的列进行全文索引和查询时,仍然使用 Unicode
排序规则区域设置标识符值。

通过查询资料发现需要执行如下命令开启该菜单项目   

创建全文索引(以索引image列为例,其他类型字段大致一样)