全文检索管理配置、使用手册

/*

说明：全文索引配置主要参考：深圳骄子数据库技术网(http://chinadba.cn/doc1.htm),非常感谢

*/

1.    全文检索概述

一直以来，从数据库列或文件系统中检索特定文本数据曾经是麻烦且花销很大的过程，且经常需要借助第三方工具。

Microsoft® SQL Server™ 提供多文本数据检索系统。SQL Server 2000 提供了一种增强的全文检索服务，使您得以：

l          在后台更新索引。

填充或更新索引不必影响其它任务。可以在后台使用全文索引向导、SQL Server 企业管理器或 SQL Server 代理程序作业调度程序来调度全文索引更新。

l          在全文索引的三种维护方法中选择一种。

根据数据和资源情况，可在完全重建、基于时间戳的增量重建和更改跟踪这三种方法中选择一种来维护全文索引。完全重建方法涉及重新扫描所有行。基于时间戳的增量重建方法仅重新扫描自上次重建（完全重建或增量重建）索引后更改的行。通过使用更改跟踪方法，SQL Server 维护对索引数据的所有更改列表，可使用该列表更新全文索引。有关更多信息，请参见维护全文索引。

l          对 image 列中存储的某些类型的数据进行索引和检索。

使用全文检索，可以对 image 列中存储的某些类型的数据进行索引和查询。全文检索使用几种支持的筛选之一来解释这些数据并析取文本数据，以便进行索引和查询。SQL Server 提供用于 .doc、.xls、.ppt、.txt 和 .htm 文件扩展名的筛选。有关更多信息，请参见筛选支持的文件类型。

一旦对 image 列进行了索引，就可以使用检索谓词 CONTAINS 和 FREETEXT 检索该列。有关更多信息，请参见使用全文谓词查询 Image 列。

l          限制返回匹配的数量。

当在查询中使用 CONTAINSTABLE 或 FREETEXTTABLE 行集函数的可选参数 top_n_by_rank 时，SQL Server 将只返回排在前面的若干匹配项，数量由上面的数字 n 所指定。有关更多信息，请参见使用 CONTAINSTABLE 和 FREETEXTTABLE 赋值行集函数中的"限制结果集"。

全文索引是解决海量数据模糊查询的较好解决办法。

全文检索可以对varchar,text,image型字段进行检索,但一个表最多只能建一个全文索引

SQL Server 2000 引入了对存储在 image 列中的这些类型的数据执行全文检索的能力。

如果没有全文索引，对字符的模糊查询只能对基表进行全表扫描（或索引扫描），

执行模糊查询都需要对全表扫描或索引扫描意味着消耗大量IO。

如果模糊查询经常发生，会造成数据库性能恶化。

2.    全文检索工作流程

SQL Server 2000 的Standard Edition 和Enterprise Edition 版本提供了对表数据中字符串的复杂检索功能---全文检索（Full-Text Searches）。此功能需要在安装时通过自定义安装选项来添加。

Microsoft® SQL Server™ 2000 在接收带全文构造的 Transact-SQL 语句时，使用全文提供程序从 Microsoft 搜索服务检索所需信息。全文构造是 CONTAINS 或 FREETEXT 谓词，或者是 CONTAINSTABLE 或 FREETEXTTABLE 行集函数。如果不知道包含搜索条件的列，全文构造可以在全文索引中引用多列。该进程的流程如下图所示。

这一进程涉及的步骤包括：

1.        应用程序给 SQL Server 实例发送带全文构造的 Transact-SQL 语句。

2.        SQL Server 关系引擎通过查询系统表验证全文构造，确定全文索引是否覆盖列引用。关系引擎将每个 SQL 语句简化为一系列行集操作，并使用 OLE DB 将这些操作传递给基础组件，通常是存储引擎。关系引擎通过全文提供程序而不是存储引擎，将任何全文构造转换成对行集的请求。请求的行集是满足搜索条件和等级的键集，而这个等级表示每个键的数据满足搜索条件的程度。向全文提供程序发送的行集请求命令包括全文检索条件。

3.        全文提供程序验证请求并将搜索条件更改为由Microsoft 搜索服务的查询支持组件使用的形式。将请求发送到搜索服务。

4.        查询支持组件使用搜索引擎组件从全文索引析取所请求的数据。然后以行集的形式将这些数据传递回全文提供程序。

5.        全文提供程序将这个行集返回给关系引擎。

6.        关系引擎将它从存储引擎和全文提供程序收到的所有行集进行组合，以生成发送回客户端的最终结果集。

1.    全文检索配置

为方便实现全文索引的配置和测试。建立如下测试环境：

/*

测试环境如下(表必须有唯一索引)：

*/

create table t_fulltext

(

rid int identity(1,1),

title varchar(256) not null,

writetime datetime default(getdate())

)

go

alter table t_fulltext add constraint pk_fulltext primary key ( rid )    --表的唯一索引

go

--初始化数据

insert into t_fulltext(title) values('基金团购开通全国交易渠道路演精彩回放')

insert into t_fulltext(title) values('人民币1:8.04已触手可及 年底将达1:7.75')

insert into t_fulltext(title) values(' 新资金酝酿新突破 捕捉牛市第二暴涨攻击点 ')

insert into t_fulltext(title) values(' [股票]再融资意见:配股额减半 权证将大扩容')

insert into t_fulltext(title) values(' 51公司今披露年报 中小板平均每股收益0.41元')

insert into t_fulltext(title) values(' 中石化否认BP入股 央企整合行情望成燎原之势')

insert into t_fulltext(title) values(' 热点:涉农板块七只牛股看涨 中金看好五只股')

insert into t_fulltext(title) values(' 最新年报送转题材个股 QFII布局 并购2大黑马')

insert into t_fulltext(title) values(' [基金]关注"银行股"基金(表) 基金季报全攻略')

insert into t_fulltext(title) values('[财经]国家统计局发布05年经济发展统计公报')

insert into t_fulltext(title) values(' 难点集中在农业和投资 中澳自贸谈判入第四轮')

insert into t_fulltext(title) values(' 美贸易代表团本周访华 中美知识产权交锋开始')

insert into t_fulltext(title) values(' 欧对中越鞋加税难度加剧 美鞋商加入反对行列')

insert into t_fulltext(title) values(' 金融界:2月28日中文财经媒体精华摘要 讨论区')

insert into t_fulltext(title) values(' [理财]单身理财攻略 从2500到1000万的女教师')

insert into t_fulltext(title) values(' 当亿万富翁的三个条件 五大日常险种投保策略')

insert into t_fulltext(title) values(' [互动]步步生莲花 职业投资者所要遵循的原则 ')

insert into t_fulltext(title) values('大资金追踪:盘中实时爆发的热点机会有哪些? ')

1.1.  配置全文目录

1.1.1.   配置全文目录服务器

开始->程序->Microsoft SQL Server->企业管理器->展开服务器->数据库->TEST数据库->全文目录

如下图所示：

1.1.2.   新建全文目录

右击新建全文目录,如下图：

             

可以修改全文目录的路径，通过将全文目录放在单独的磁盘或磁盘阵列可以提高效率。

建议不要对整个全文目录进行任何形式的填充调度。

1.1.3.   配置全文索引表

选择t_fulltext 表->右击全文索引表->在表上定义全文索引。如下图：

1.1.4.   全文索引配置向导

出现欢迎使用SQL Server 全文索引向导。如下图：

      

1.1.5.   选择唯一索引

为表选择唯一的索引。Pk_fulltext 如下图：

 

1.1.6.   配置全文索引列

选择希望符合全文查询的列和单词断字符的语言(默认是系统安装语言)。如下图：

1.1.7.   选择全文目录

为该表选择先前建立的全文目录。如下图：

1.1.8.   选择和创建填充调度

可以配置表的填充调度和修改，如下图：

 

为能够准实时地把基表的全文索引列的修改（增、删、改）准实时地应用到全文索引，建议使用其它方法进行索引维护(下面介绍)。如果数据不会再修改，则在一次完全填充以后，不用再增加其它类型的表级填充。如果实施到本步骤，不再添加任何形式的表调度，那么基表数据的更新，将不能更新到全文索引。

1.1.9.   完成全文索引配置

如下图：

 

由于尚未在表上添加任何调度，因此还需要继续设置。

1.1.10.   更改跟踪填充

在企业管理器中右击 f_fulltext 表，选中“更改跟踪”。如下图：

选中更改跟踪后，以后对基表数据的修改，才能反映到全文索引中。

特别注意：一旦选中“更改跟踪”，根据经验，其实是必须对该表进行完全填充的。

如果是在线使用的全文索引数据库，选中“更改跟踪”会导致性能迅速下降，导致全文索引

无法使用。

1.1.11.   更新后台中的索引

在企业管理器中右击 f_fulltext 表，选中“更新后台中的索引”。如下图：

到本步，全文索引就完全完成了。由于我们选中了“更改跟踪”，此时实际已经开始了表的完全填充。根据经验，160万条全文索引字段为Text类型的表，需要30分钟左右才能完成完全填充。(具体也需要看服务器处理性能)。

1.1.12.   注意事项

如何进行全文索引查询（仅简单介绍）全文索引查询建议使用 CONTAINS 及 FREETEXT 谓词进行查询。如果关键词仅包括干扰词（有关干扰词的信息，请参阅联机帮助干扰词的“干扰词”关键字）

可以通过双击全文目录下的全文索引，查看该全文目录的属性，状态为“空闲”，而项目计数大于0，则表示完全填充已经完成了。如下图：

 

 

1.    全文检索应用

1.1.  使用带有 <simple_term> 的 CONTAINS

下面的示例查找包含词"中"且rid大于10的所有信息。

SELECT *

FROM t_fulltext

WHERE rid >10

      AND CONTAINS(title, '中')

GO      

--结果

/*

14 金融界:2月28日中文财经媒体精华摘要 讨论区    2006-02-28 17:54:01.280

13 欧对中越鞋加税难度加剧 美鞋商加入反对行列       2006-02-28 17:54:01.280

12 美贸易代表团本周访华 中美知识产权交锋开始       2006-02-28 17:54:01.280

11 难点集中在农业和投资 中澳自贸谈判入第四轮       2006-02-28 17:54:01.280

18 大资金追踪:盘中实时爆发的热点机会有哪些?   2006-02-28 17:54:01.280

*/

1.2.  在 <simple_term> 中使用 CONTAINS 和短语

下面的示例返回包含短语 "中美" 或 "中澳" 的所有信息。

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, ' "中美" OR "中澳" ')

GO

--结果

/*

12 美贸易代表团本周访华 中美知识产权交锋开始       2006-02-28 17:54:01.280

11 难点集中在农业和投资 中澳自贸谈判入第四轮       2006-02-28 17:54:01.280

*/

1.3.  使用带有 <prefix_term> 的 CONTAINS

下面的示例返回所有满足该条件的信息：这些信息至少含有一个以 title列中前辍”股”起始的词。

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, ' "股*" ')

GO

--结果

/*

7   热点:涉农板块七只牛股看涨 中金看好五只股  2006-02-28 17:54:01.280

6   中石化否认BP入股 央企整合行情望成燎原之势     2006-02-28 17:54:01.263

5   51公司今披露年报 中小板平均每股收益0.41元   2006-02-28 17:54:01.263

4   [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

9   [基金]关注"银行股"基金(表) 基金季报全攻略 2006-02-28 17:54:01.280

8   最新年报送转题材个股 QFII布局 并购2大黑马    2006-02-28 17:54:01.280

*/

1.4.  使用带有 <prefix_term> 的 CONTAINS 和 OR

下面的示例返回包含字符"股" 或 "中" 的所有信息。

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, '"股*" OR "中*"')

GO

--结果

/*

7     热点:涉农板块七只牛股看涨 中金看好五只股  2006-02-28 17:54:01.280

14   金融界:2月28日中文财经媒体精华摘要 讨论区    2006-02-28 17:54:01.280

6     中石化否认BP入股 央企整合行情望成燎原之势     2006-02-28 17:54:01.263

13   欧对中越鞋加税难度加剧 美鞋商加入反对行列       2006-02-28 17:54:01.280

5     51公司今披露年报 中小板平均每股收益0.41元   2006-02-28 17:54:01.263

12   美贸易代表团本周访华 中美知识产权交锋开始       2006-02-28 17:54:01.280

4     [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

11   难点集中在农业和投资 中澳自贸谈判入第四轮       2006-02-28 17:54:01.280

18   大资金追踪:盘中实时爆发的热点机会有哪些?   2006-02-28 17:54:01.280

9     [基金]关注"银行股"基金(表) 基金季报全攻略 2006-02-28 17:54:01.280

8     最新年报送转题材个股 QFII布局 并购2大黑马    2006-02-28 17:54:01.280

*/

1.5.  使用带有 <proximity_term> 的 CONTAINS

下面的示例返回在词 "股" 附近有词 "涨" 的所有信息。

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, '股 NEAR 涨')

GO

--结果

/*

7     热点:涉农板块七只牛股看涨 中金看好五只股  2006-02-28 17:54:01.280

*/

1.6.  使用带有 <generation_term> 的 CONTAINS

下面的示例搜索具有“大”形式的词的所有信息：”大扩” 和 “大资金” 等等。

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, ' FORMSOF (INFLECTIONAL, 大) ')

GO

--结果

/*

4     [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

18   大资金追踪:盘中实时爆发的热点机会有哪些?   2006-02-28 17:54:01.280

16   当亿万富翁的三个条件 五大日常险种投保策略       2006-02-28 17:54:01.280

8     最新年报送转题材个股 QFII布局 并购2大黑马    2006-02-28 17:54:01.280

*/

1.7.  使用带有 <weighted_term> 的 CONTAINS

下面的示例搜索包含词 “中”、”股” 或 “大” 以及给予每个词不同加权的所有信息。

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, 'ISABOUT (中 weight (.8),

   股 weight (.4), 大 weight (.2) )' )

GO

--结果

/*

7     热点:涉农板块七只牛股看涨 中金看好五只股  2006-02-28 17:54:01.280

14   金融界:2月28日中文财经媒体精华摘要 讨论区    2006-02-28 17:54:01.280

6     中石化否认BP入股 央企整合行情望成燎原之势     2006-02-28 17:54:01.263

13   欧对中越鞋加税难度加剧 美鞋商加入反对行列       2006-02-28 17:54:01.280

5     51公司今披露年报 中小板平均每股收益0.41元   2006-02-28 17:54:01.263

12   美贸易代表团本周访华 中美知识产权交锋开始       2006-02-28 17:54:01.280

4     [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

11   难点集中在农业和投资 中澳自贸谈判入第四轮       2006-02-28 17:54:01.280

18   大资金追踪:盘中实时爆发的热点机会有哪些?   2006-02-28 17:54:01.280

9     [基金]关注"银行股"基金(表) 基金季报全攻略 2006-02-28 17:54:01.280

16   当亿万富翁的三个条件 五大日常险种投保策略       2006-02-28 17:54:01.280

8     最新年报送转题材个股 QFII布局 并购2大黑马    2006-02-28 17:54:01.280

*/

1.8.  使用带变量的 CONTAINS

下例使用变量而非特定的搜索术语。

DECLARE @SearchWord varchar(30)

SET @SearchWord ='股票'

SELECT * FROM t_fulltext WHERE CONTAINS(title, @SearchWord)

--结果

/*

4     [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

*/

1.9.  使用 AND、OR 和 AND NOT 组合全文检索运算符

可以在 CONTAINS 谓词的搜索条件之间使用圆括号和布尔运算符（AND、AND NOT 和 OR）。假定 t_fulltext表中的一行或多行包含关于”股票”和”中国”的信息。要检索包含关于任何一种配方类型的文本的行，请在"股票"和"中国"短语之间使用一个 OR。如下：

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, ' "股票" OR "中国" ' )

GO

--结果

/*

4     [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

*/

 

此例检索 t_fulltext表中 title包含”股”，但是不包含”中金”和” 中小”和”中石”的所有行。

SELECT  *

FROM t_fulltext

WHERE CONTAINS(title, ' 股 AND NOT ("中*" )' )

GO

--结果

/*

4     [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

9     [基金]关注"银行股"基金(表) 基金季报全攻略 2006-02-28 17:54:01.280

8     最新年报送转题材个股 QFII布局 并购2大黑马    2006-02-28 17:54:01.280

*/

 

此例获title中提到了词”股”和”基金”。

SELECT *

FROM t_fulltext

WHERE CONTAINS(title, ' 股 AND 基金 ' )

GO

--结果

/*

9     [基金]关注"银行股"基金(表) 基金季报全攻略 2006-02-28 17:54:01.280

*/

可以将短语和谓词结合起来检索单词和短语的组合。例如，可以检索所有包含”股”或”中”或”大”但是不包含”美”的行。使用 CONTAINS 谓词的 WHERE 子句的形式如下：

SELECT *

FROM t_fulltext

WHERE CONTAINS (title, '("股" or "中" or "大") AND NOT "美"')

GO

--结果

/*

7     热点:涉农板块七只牛股看涨 中金看好五只股  2006-02-28 17:54:01.280

14   金融界:2月28日中文财经媒体精华摘要 讨论区    2006-02-28 17:54:01.280

6     中石化否认BP入股 央企整合行情望成燎原之势     2006-02-28 17:54:01.263

5     51公司今披露年报 中小板平均每股收益0.41元   2006-02-28 17:54:01.263

4     [股票]再融资意见:配股额减半 权证将大扩容  2006-02-28 17:54:01.263

11   难点集中在农业和投资 中澳自贸谈判入第四轮       2006-02-28 17:54:01.280

18   大资金追踪:盘中实时爆发的热点机会有哪些?   2006-02-28 17:54:01.280

9     [基金]关注"银行股"基金(表) 基金季报全攻略 2006-02-28 17:54:01.280

16   当亿万富翁的三个条件 五大日常险种投保策略       2006-02-28 17:54:01.280

8     最新年报送转题材个股 QFII布局 并购2大黑马    2006-02-28 17:54:01.280

*/

2.    全文检索建议

如果正在对不到一百万行的表进行全文索引，那么几乎不需要进行性能优化。如果对大型 SQL Server 表（其中包含创建大型全文目录的上百万行）进行全文索引，那么这会持续进行大量的读取和写入活动，因此必须配置 SQL Server 和全文目录，以通过跨多个硬盘驱动器进行负载平衡来获得最优磁盘 I/O 性能。同时还需要考虑硬件配置、Microsoft Windows® 2000 或 Windows NT® 4.0 系统配置、SQL Server 2000 配置、全文目录和数据库文件的实际位置。

l          硬件考虑事项

多个 CPU：一到四个 500 MHz Xeon III 处理器。

内存：1 到 4 GB 的物理 RAM。

具有几个通道的多磁盘控制器，或具有多通道的单磁盘控制器。

磁盘 I/O 子系统：RAID0（没有容错保护的磁盘条带化）、RAID0+1 和 RAID5。

l          全文索引和目录考虑事项

全文索引或填充全文目录应该在系统活动较少时进行，通常在数据库维护窗口完成。

将全文目录文件放置在它自己的磁盘控制器上，或有多个通道的单个磁盘控制器上单独的通道中。

将数据库文件放置在与全文目录文件不同的独立的磁盘控制器上，或有多个通道的单个磁盘控制器上单独的通道中。

具有四百万或两千万行的 SQL 表的全文索引可能要花费几个小时或几天来完成。考虑知识库文章 Q240867 中提供的选项："INF：如何移动、复制和备份 SQL 7.0 全文目录文件夹和文件"。

因此建议在大数据量并且要求查询效率高的条件下。使用全文索引。我曾经测试过160W记录。普通查询消耗32秒，应用全文索引<1S。全文索引的填充也是很消耗资源的问题。具体看业务需求。比如数据入库频率等相关因素，来权衡是否采用全文索引。