SQL全文本检索应用

SQL全文本检索应用 

基本知识

1. SQL Server7 的 DeskTop 版中没有全文本检索。

2. 一个表只能有一个全文本检索。

3. 被检索的表必须有单列的唯一索引。

4. 全文本的索引存储在文件系统中，而非数据库中。

5. 更新全文本索引的过程比常规索引要耗时，而且也不象常规索引那样可以由数据库系统立即更新。

6. 全文本索引包含在全文本目录( Full-Text Catalog )中，每个数据库可以包含一个或多个目录，

   但一个目录不能属于多个数据库。

7. 全文本检索只能在真正的表上创建，不能是视图，系统表，临时表。

8. 全文本检索会忽略某些噪音字( noise Words)，比如英文的 a，the，and，中文的'和'，'是'等等。

9. 如果在查询中包含 noise words ，就会引发错误，在应用程序中应去除这些 noise words。

启动全文本检索服务。

方法A：在企业管理器中打开 Support Services 文件夹，在 Full-Text Search 的右键菜单中选择 Start。

方法B：在 SQL Server Service Manager 的 Services 下拉列表中选择 Microsoft Search，

       并单击 Start/Continue 按钮。

方法C：使用 net start mssearch 的命令行方式。

使用全文本检索向导( Full-Text Indexing Wizard )。

step1. 选择被检索的数据库，在 Tools 的菜单中，选择 Full-text Indexing，

进入欢迎( Welcome )的屏幕，单击 next。

step2. 选择被检索的表，单击 next。

step3. 选择唯一索引，单击 next。

step4. 选择被索引的列，单击 Add，该列显示在右栏中。单击 next。

step5. 选择目录(选择已存在的目录，或创建新的目录)，单击 next。

step6. 选择或创建 population schedule(可选项)，单击 next。

step7. 单击 finish。

使用 SQL-DMO (以 VB 为例)

step1. 在工程的引用中选择 Microsoft SQLDMO Object Library。

step2. 创建 SQLServer 对象。

Dim objSQL As New SQLDMO.SQLServer

objSQL.Connect "localhost", "sa", ""

step3. 创建新的目录，并加入到被索引的数据库目录中。

Dim objCatalog As New SQLDMO.FullTextCatalog

'使 pubs 为全文本检索的数据库

objSQL.Databases("pubs").EnableFullTextCatalogs

'创建新的目录

objCatalog.Name = "ftcPubsTest"

'将新目录加入到目录集合中

objSQL.Databases("pubs").FullTextCatalogs.Add objCatalog

step4. 在表上创建全文本索引。

Dim objTable As New SQLDMO.Table

'指定被索引的表

Set objTable = objSQL.Databases("pubs").Tables("authors")

'指定目录名和唯一索引名

objTable.FullTextCatalogName = "ftcPubsTest"

objTable.UniqueIndexForFullText = "UPKCL_auidind"

objTable.FullTextIndex = True

'指定被索引的列

objTable.Columns("au_lname").FullTextIndex = True

objTable.Columns("au_fname").FullTextIndex = True

'激活该表上的全文本索引

objTable.FullTextIndexActive = True

step5. 启动全文本目录

objCatalog.Start SQLDMOFullText_Full

使用存储过程

step1. 使 pubs 为全文本检索的数据库

USE Pubs

go

sp_fulltext_database 'enable'

step2. 创建新的目录

sp_fulltext_catalog 'ftcPubsTest','create'

step3. 指定被索引的表

sp_fulltext_table 'authors','create','ftcPubsTest','UPKCL_auidind'

step4. 指定被索引的列

sp_fulltext_column 'authors','au_lname','add'

sp_fulltext_column 'authors','au_fname','add'

step5. 激活该表上的全文本索引

sp_fulltext_table 'authors','activate'

step6. 启动全文本目录

sp_fulltext_catalog 'ftcPubsTest','start_full'

CONTAINS 语法

 

我们通常在 WHERE 子句中使用 CONTAINS ，就象这样： SELECT * FROM table_name WHERE CONTAINS(fullText_column,'search contents')。 我们通过例子来学习，假设有表 students，其中的 address 是全文本检索的列。

1. 查询住址在北京的学生

SELECT student_id,student_name

FROM students

WHERE CONTAINS( address, 'beijing' )

remark: beijing是一个单词，要用单引号括起来。

2. 查询住址在河北省的学生

SELECT student_id,student_name

FROM students

WHERE CONTAINS( address, '"HEIBEI province"' )

remark: HEBEI province是一个词组，在单引号里还要用双引号括起来。 

3. 查询住址在河北省或北京的学生

SELECT student_id,student_name

FROM students

WHERE CONTAINS( address, '"HEIBEI province" OR beijing' )

remark: 可以指定逻辑操作符(包括 AND ，AND NOT，OR )。

4. 查询有 '南京路' 字样的地址

SELECT student_id,student_name

FROM students

WHERE CONTAINS( address, 'nanjing NEAR road' )

remark: 上面的查询将返回包含 'nanjing road'，

'nanjing east road'，'nanjing west road' 等字样的地址。

A NEAR B，就表示条件： A 靠近 B。

5. 查询以 '湖' 开头的地址

SELECT student_id,student_name

FROM students

WHERE CONTAINS( address, '"hu*"' )

remark: 上面的查询将返回包含 'hubei'，'hunan' 等字样的地址。

记住是 *，不是 %。

6. 类似加权的查询

SELECT student_id,student_name

FROM students

WHERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' )

remark: ISABOUT 是这种查询的关键字，weight 指定了一个介于 0~1之间的数，

        类似系数(我的理解)。表示不同条件有不同的侧重。

7. 单词的多态查询

SELECT student_id,student_name

FROM students

WHERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' )

remark: 查询将返回包含 'street'，'streets'等字样的地址。

对于动词将返回它的不同的时态，如：dry，将返回 dry，dried，drying 等等。

以上例子都使用英文，不使用中文是因为有的查询方式中文不支持，而且我的计算机是英文系统

6. 更新全文本索引的过程比常规索引要耗时，

而且也不象常规索引那样可以由数据库系统立即更新。

可以立即更新的

9. 如果在查询中包含 noise words ，就会引发错误，

在应用程序中应去除这些 noise words。不对，查询时会自己过滤掉noise word，

只有查询的内容全是noise words时才会出现错误

CONTAINSTABLE 语法

我们通常在 FROM 子句中使用 CONTAINSTABLE ，就象这样：SELECT * FROM table_name, 

CONTAINTABLE(fulltext_table,fullText_column,'search condition') WHERE ......。

CONTAINSTABLE 在查询方式上与 CONTAINS 几乎一样，所以就不用赘述了。

CONTAINSTABLE 返回的是符合查询条件的表，在 SQL 语句中我们

可以把它当作一个普通的表来使用。

我们看一个例子，比较这两种表的不同。

SELECT FT_TBL.student_name,FT_TBL.student_score,KEY_TBL.RANK

FROM report AS FT_TBL INNER JOIN

CONTAINSTABLE( student,address,

'ISABOUT (city weight (.8), county wright (.4))' ) AS KEY_TBL

ON FT_TBL.student_id = KEY_TBL.[KEY]

ORDER BY KEY_TBL.RANK

CONTAINSTABLE 返回的表包含有特殊的两列：KEY，RANK。

在第一部分里我们就强调了：被全文索引的表必须有唯一索引。

这个唯一的索引列在返回的表中就成为 KEY。

我们通常把它作为表连接的条件。

在某些网站搜索时，结果中会出现表示匹配程度的数字，RANK 与此类似。

它的值在0~1000之间，标识每一行与查询条件的匹配程度，程度越高，RANK 的值大，

通常情况下，按照 RANK 的降序排列。

FREETEXT 语法

FREETEXT 与 CONTAINS 类似，只是没有 CONTAINS 的精度高。在 CONTAINS 中，

对查询条件的写法有很多要求，而 FREETEXT 就没有，可以是任意的单词，词组或句子。看下面的例子：

SELECT CategoryName

FROM Categories

WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' )

FREETEXTTABLE 语法

和 CONTAINSTABLE 一样，FREETEXTTABLE 返回带有 KEY，RANK 的表。

举例说明：

SELECT FT_TBL.CategoryName, 

FT_TBL.Description,

KEY_TBL.RANK

FROM Categories AS FT_TBL INNER JOIN

FREETEXTTABLE(Categories, Description, 

'sweetest candy bread and dry meat') AS KEY_TBL

ON FT_TBL.CategoryID = KEY_TBL.[KEY]

在 ASP 中使用全文本检索

Dim cnn 

Dim rs

Dim strSQL

strSQL = "SELECT book_name " &_

"FROM books " &_

"WHERE CONTAINS( description, '" & Request("search_condition") & "' )"

Set cnn = Server.CreateObject("ADODB.Connection")

Set rs = Server.CreateObject("ADODB.RecordSet")

cnn.Open "provider=sqloledb;datasource=.;initial catalog=books;user id=sa;pasword=;"

rs.Open strSQL,cnn

上面的例子十分简单，仅为示意。只要掌握了 CONTAINS 和 CONTAINSTABLE 的语法，在使用上和一般的 ADO 查询一样。(完)

 

 

全文索引和全文检索是sql server 7.0的新增功能，它能够对数据中的字符类型列（如varchar、text等类型列）进行索
引，并通过索引实现全文搜索查询。sql server常规索引与全文检索相比，二者的区别如下：

常规索引 全文索引
使用create index或约束定义创建 使用全文索引存储过程创建和删除
通过删除或执行drop index语句删除

当插入、修改或删除数据时，sql server 只能通过任务调度或执行存储过
能够自动更新常规索引内容 程来填充全文索引

每个表可以建立多个常规索引 每个表只能有一个全文索引
索引不能分组 同一个数据库中的多个全文索引可
以组织为一个全文目录
常规索引存储在数据库文件中 全文索引存储在文件系统中

为了支持全文索引操作，sql server 7.0新增了一些新存储过程和transact-sql语句，使用这些存储过程创建全文索引的
具本步骤为（括号内为每步所调用的存储过程名称）：

（1）启动数据库的全文处理功能（sp_fulltext_datebase）；
（2）建立全文目录（sp_fulltext_catalog）；
（3）在全文目录中注册需要全文索引的表（sp_fulltext_table）；
（4）指出表中需要全文检索的列名（sp_fulltext_column）
（5）为表创建全文索引（sp_fulltext_table）；
（6）填充全文索引（sp_fulltext_catalog）。

例：
use pubs
go
exec sp_fulltext_database 'enable'
--为titles表建立全文索引数据元，其中create为建立，activate为激活，deactivate为关闭表全文索引的激活状态，使
它不再参加全文目录填充，drop为删除；create参数中，后面跟的是全文目录名称和索引列名。
--下面语句为pubs数据库中的titles表创建全文索引数据元，存储该数据元的全文目录为FT_pubs，所使用的唯一索引为
UPKCL_titleidind（title表中为title_id列的PRIMARY KEY约束所建立的唯中索引）
sp_fulltext_table titles,'create','FT_pubs','upkcl_titledind'

--激活它
sp_fulltext_table titles,'activate'

--指定参加全文索引的列
sp_fulltext_column 'titles','title','add'
sp_fulltext_column 'titles','notes','add'

下面是一个完整的例子：
--在执行该脚本程序之前启动sql server的全文搜索服务，即microsoft search服务
use pubs --打开数据库
go
--检查pubs是否支持全文索引，如果不支持全文索引，则使用sp_fulltext_datebase打开该功能
if (select databaseproperty ('pubs','IsFulltextEnables'))=0
execute sp_fulltext_database 'enable'
--建立全文目录FT_pubs
execute sp_fulltext_catalog 'FT_pubs','create'
--为titles表建立全文索引数据元
execute sp_fulltext_table 'titles','FT_pubs','UPKCL_titleidind'
--设置全文索引列名
execute sp_fulltext_column 'titles','title','add'
execute sp_fulltext_column 'titles','notes','add'
--建立全文索引
execute sp_fulltext_table 'FT_pubs','activate'
--填充全文索引目录
execute sp_fulltext_catalog 'FT_pubs','start_full'
GO
--检查全文目录填充情况
WHILE FulltextCatalogProperty("FT_pubs','PopulateStatus')<>0
BEGIN
--如果全文目录正处于填充状态，则等待30秒后再检测一次
WAITFOR DELAY ‘0：0：30’
END
--全文目录填充完成后，使用全文目录检索

--查询title列或notes列中包含有database或computer字符串的图书名称
SELECT title
FROM title
where CONTAINTS(title,'database')
or contains(notes,'database')
or contains(title,'computer')
or contains(notes,'computer')

 

 

学习如何充分利用 SQL Server 2000 的全文搜索功能。本文包含有关实现最大吞吐率和最佳性能的几点提示和技巧。

目录

简介

全文搜索功能简介

配置全文搜索功能

全文查询

排位和优化

其他性能技巧

小结

附录 A：实现全文搜索功能的最佳选择

附录 B：使用最佳选择、结果分页和有效全文查询逻辑的示例应用程序

附录 C：资源

简介

使用 Microsoft? SQL? Server 2000 的全文搜索功能，可以对在非结构化文本数据上生成的索引执行快速、灵活的查询。常用的全文搜索工具是网站的搜索引擎。为了帮助读者理解全文搜索功能的最佳使用方法，本文介绍了大量抽象概念；并对优化全文索引和查询以实现最大吞吐率和最佳性能，提供了几点提示和技巧。

全文搜索功能简介

全文搜索功能在 SQL Server 7.0 中引入。全文搜索的核心引擎建立在 Microsoft Search (MSSearch) 技术上，Microsoft Exchange 和 Microsoft SharePoint? Portal Server 等产品中也采用了此项技术。

SQL Server 7.0 全文搜索中公开的功能可提供基本的文本搜索功能，并使用早期版本的 MSSearch；而 SQL Server 2000 的全文搜索实现则包含一组可靠的索引和查询功能，并在 SQL Server 7.0 的基础之上添加了几项增强功能。这些增强功能包括：通过 Microsoft 群集服务完全支持群集操作，能够过滤和索引 IMAGE 列中存储的文档，提供改进的语言支持，以及在性能、可缩放性和可靠性方面进行了改进。

MSSearch 生成、维护和查询文件系统中（而不是 SQL Server 中）存储的全文索引。MSSearch 进行全文索引时使用的逻辑和物理存储单元是目录。全文目录在每个数据库中包含一个或多个全文索引 - 可以为 SQL Server 中的每个表创建一个全文索引，且索引中可以包含该表中的一列或多列。每个表只能属于一个目录，且每个表只能创建一个索引。我们将简单介绍有关组织全文目录和索引的最佳方案 - 但首先，让我们来简单了解一下全文搜索的工作原理。

配置全文搜索功能

要为 SQL Server 中存储的文本数据创建全文索引，应该先完成以下几步准备工作。第一步是以全文方式启用包含要生成索引的文本数据的数据库（如果您尚未执行此操作）。

注意：执行以下语句将丢弃并重新创建属于要启用全文搜索的数据库的所有全文目录。除非要重新创建全文目录，否则请确保在要启用的特定数据库中未创建任何全文目录。

如果您是 sysadmin 角色的成员或此数据库的 db_owner，可以继续进行并发出以下语句：

use Northwind

exec sp_fulltext_database 'enable'

 

接下来，您需要创建全文目录，以存储全文索引。正如前面所提到的，此目录中的数据存储在文件系统中（而不是 SQL Server 中），因此，在考虑全文目录的存储位置时应该仔细选择。除非指定其他位置，否则全文目录将存储在 FTDATA 目录（位于 Microsoft SQL Server\MSSQL 存储位置中）的子目录中。以下是在非默认位置创建全文目录的方法：

exec sp_fulltext_catalog 'Cat_Desc', 'create', 'f:\ft'

 

在本例中，全文目录将创建为“f:\ft”的子目录，如果您查看文件系统的该部分，将看到它有了自己的目录。MSSearch 使用的全文目录的命名规则是：

SQL+dbid+catalogID

 

目录 ID 从 00005 开始，并且每新建一个目录就递增 1。

如果可能的话，最好在其所在的物理驱动器上创建全文目录。如果生成全文索引的进程需要进行大量的 I/O 操作（具体而言，就是从 SQL Server 中读取数据，然后向文件系统写入索引），则应避免使 I/O 子系统成为瓶颈。

那么，全文目录有多大呢？通常情况下，全文目录的系统开销比 SQL Server 中存储的数据（对其进行全文索引）量高出大约 30%；但是，此规则取决于数据中唯一单词（或主键）的分布，以及被您视为是干扰词的单词的分布。干扰词（或终止词）是指要排除在全文索引和查询以外的词语（因为它们不是您感兴趣的搜索词，而且出现频率很高，所以只会使索引变得很大，而不会有实际效果）。稍后，我们将介绍有关干扰词选择方面的注意事项，以及如何优化干扰词以改善查询性能。

如果您尚未执行此操作，请在每个要生成全文索引的表上创建一个唯一的单列非空索引。这个唯一索引用于将表中的每一行映射到 MSSearch 内部使用的一个唯一可压缩主键。接下来，您需要让 MSSearch 知道您要为表创建全文索引。对表发出以下语句可将该表添加到所选的全文目录中（在本例中，它是我们在前面创建的“Cat_Desc”）：

exec sp_fulltext_table 'Categories', 'create', 'Cat_Desc',

   'PK_Categories'

 

下一步是向此全文索引添加列。您可以为每一列选择一种语言，如果该列的类型为 IMAGE，则必须再指定一列，以指示 IMAGE 列的每一行中存储的文档类型。

在列语言选择方面，有一些重要但尚未成文的注意事项。这些注意事项与文本的标记方式以及 MSSearch 对文本的索引方式有关。被索引的文本是通过一个称作单词分隔符（用作单词边界标记）的组件提供的。在英文中，单词分隔符通常是空格或某种形式的标点符号；而在其他语言中（例如德语），单词或字符可以组合在一起；因此，所选的列语言应表示要存储在该列的行中的语言。如果不确定，最好的方法通常是使用中性单词分隔符（只使用空格和标点符号执行标记功能）。选择列语言的另一个好处是“寻根溯源”。全文查询中的寻根溯源是指在特定语言中搜索某一单词的所有变化形式的过程。

选择语言的另一个考虑因素与数据的表示方法有关。对于非 IMAGE 列数据来说，不需要执行特殊的过滤操作；而文本通常需要将单词分隔组件按原样传递。单词分隔符主要用于处理书面文本。因此，如果文本中有任何类型的标记（例如 HTML），则在索引和搜索过程中，语言精确性将不会很高。这种情况下，您有两种选择 - 首选方法是只将文本数据存储在 IMAGE 列中，并指明其文档类型，以便对其进行过滤。如果不选择此方法，则可以考虑使用中性单词分隔符，并且可能的话，在干扰词列表中添加标记数据（例如 HTML 中的“br”）。在指定了中性语言的列中不能进行任何基于语言的寻根溯源，但有些环境可能会要求您选择此方法。

在知道列选项后，通过发出以下语句在全文索引中添加一列或两列：

exec sp_fulltext_column 'Categories', 'Description', 'add'

 

您可能注意到，此处未指定任何语言 - 这种情况下，将使用默认的全文语言。可以通过系统存储过程“sp_configure”为服务器设置默认全文语言。

将所有列添加到全文索引后，即可执行填充操作。填充方法之多实在是不胜枚举，此处不作详细介绍。在本例中，只需对表启动完全填充，并等待它执行完毕：

exec sp_fulltext_table 'Categories', 'start_full'

 

您可能希望使用 FULLTEXTCATALOGPROPERTY 或 OBJECTPROPERTY 函数来监视填充状态。要获取目录填充状态，可以执行：

select FULLTEXTCATALOGPROPERTY('Cat_Desc', 'Populatestatus')

 

通常情况下，如果完全填充正在进行，则返回的结果是“1”。有关如何使用 FULLTEXTCATALOGPROPERTY 和 OBJECTPROPERTY 的详细信息，请参阅 SQL Server Books Online。

全文查询

查询全文索引与执行 SQL Server 中的标准关系型查询略有不同。由于索引是在 SQL Server 外部进行存储和管理的，因此全文查询处理大部分由 MSSearch 完成（因此，那些一部分是关系型、一部分基于全文的查询将被单独处理），这样做有时会损害性能。

从本质上说，执行全文查询时，查询词传递给 MSSearch，后者遍历其内部数据结构（索引），并向 SQL Server 返回主键和排位值。如果执行 CONTAINS 或 FREETEXT 查询，则通常看不到主键或排位值，但如果执行 CONTAINSTABLE 或 FREETEXTTABLE 查询，则将获得这些值，然后这些值通常会与基表合并在一起。与基表合并主键的进程需要很高的系统开销 - 稍后，我们将向您介绍一些巧妙的方法以尽量减少或完全避免这种合并。

如果您通过不断思考，对全文查询如何返回数据有了一个初步了解，就可以推测出 CONTAINS/FREETEXT 查询仅执行 CONTAINSTABLE/FREETEXTTABLE 查询并与基表进行合并。有了这样的了解，您应该避免使用这些类型的查询，除非不这样做的开销更高。在 Web 搜索应用程序中，使用 CONTAINSTABLE 与 FREETEXTTABLE 比使用不带 TABLE 的同类函数好得多。

到现在为止，您已经知道全文查询是用来从 SQL Server 之外存储的 MSSearch 索引中访问数据的特殊方法，还知道如果盲目地与基表进行合并，就会遇到麻烦。应该了解的另外一个重要内容是 CONTAINS 样式查询与 FREETEXT 样式查询之间的本质差别。

CONTAINS 查询用于对所查询的所有词语执行完全匹配查询。无论您只查找单个单词，还是查找以“orange”开头的所有单词，系统只返回包含所有搜索词的结果。因此，CONTAINS 查询速度很快，因为它们通常返回很少的结果，并且不需要执行过多的附加处理。CONTAINS 查询的缺点包括令人生厌的干扰词过滤问题。经验丰富的开发人员以及过去使用过全文搜索的数据库管理员，在试图匹配只包含单个干扰词的单词或词组时，曾遇到过“您的查询只包含干扰词”这样令人吃惊的错误。要避免收到此错误，方法之一是在执行全文查询之前过滤出干扰词。向包含干扰词的 CONTAINS 查询返回结果是不可能的，因为此类查询只返回与整个查询字符串完全匹配的结果。由于干扰词不是全文索引项，因此包含干扰词的 CONTAINS 查询不会返回任何行。

FREETEXT 查询消除了 CONTAINS 查询中偶尔出现的所有警告说明。当发出 FREETEXT 查询时，实际上发出的是词根查询。因此，当您搜索“root beer”时，“root”和“beer”包含其所有形式（寻根溯源与语言相关；所用的语言由生成索引时指定的全文列语言确定，并且在所有查询的列中必须相同），并且系统将返回至少与这些词语之一匹配的所有行。

FREETEXT 查询的负面影响是它们通常比 CONTAINS 查询耗用更多的 CPU - 因为要寻根溯源以及返回更多的结果，就需要包含更复杂的排位计算。不过，基于 FREETEXT 的查询非常灵活，而且速度非常快，是基于 Web 的搜索应用程序中通常使用的最佳选择。

排位和优化

我经常遇到使用全文搜索的用户，他们问我排位编号是什么意思，以及如何将排位编号转换成某种用户可以理解的值。对这个问题，回答可长可短，在这里我将进行简要回答。简单而言，这些排位编号不如结果返回的顺序那样重要。也就是说，当您按照排位对结果进行排序时，总是首先返回关联程度最高的结果。排位值本身常常变化 - 全文搜索使用概率排位算法，即返回的每个文档的关联性受全文索引中的任何或所有其他文档的直接影响。

有些人认为，一种有助于增加某些行排位的技巧是在这些行的全文索引列中重复常用的搜索关键字。尽管在某种程度上，这种方法可能会提高这些行因某些关键字而首先返回的几率，但在其他情况下，可能会适得其反 - 而且还存在使词语查询性能降低的风险。较好的解决方案是为搜索应用程序实现“最佳选择”系统（请参阅以下示例），这样就可以确保首先返回某些文档。多次重复使用关键字会使这些特定关键字的全文索引扩大，并使得 MSSearch 在查找正确行和计算排位时浪费时间。如果全文索引数据量很大，并尝试使用了此方法，您可能会发现某些全文查询很耗时。如果能够实现更细致（也可能更精确）的“最佳选择”系统，您会发现它明显改善了查询性能。

多次重复数据的另一个问题与用于组合关系型查询和全文查询的常用技巧有关。许多使用全文搜索的用户都深受此问题的困扰，每当他们试图将某种过滤器应用于全文查询返回的结果时，便会遇到这样的问题。正如前面所说的，全文查询为每个匹配行返回一个主键和一个排位 - 要收集有关这些行的任何详细信息，必须与它的基表进行合并。由于从无限制的全文查询中可能会返回任意数量的结果，因此合并可能需要大量系统开销。人们发现避免合并的一个有效方法是只在全文索引中添加要过滤的数据（如果可能）。换句话说，如果用户要从报纸上所有文章的正文中搜索关键字“Ichiro”，并且只希望返回该报上体育专栏中的文章，则查询语句通常如下所示：

-- [方法 1：]

-- 开销最高：先全部选择，然后再合并和过滤

SELECT ARTICLES_TBL.Author, ARTICLES_TBL.Body, ARTICLES_TBL.Dateline,

   FT_TBL.[rank]

FROM FREETEXTTABLE(Articles, Body, 'Ichiro') AS FT_TBL

INNER JOIN Articles AS ARTICLES_TBL

ON FT_TBL.[key] = ARTICLES_TBL.ArticleID

WHERE ARTICLES_TBL.Category = 'Sports'

 

-- [方法 2：]

-- 可以使用，但会导致意外结果并变慢，或者会返回不准确的结果：

-- 执行全文过滤，并且只提取主键和排位

-- （处理在 Web 服务器上完成）

SELECT [key], [rank]

FROM CONTAINSTABLE(Articles, *, 'FORMSOF(INFLECTIONAL('Ichiro')

      AND "sports"')

 

这两个查询要么不必要地占用大量系统开销，要么存在返回错误结果的可能性（在第二个查询中，“sports”很可能出现在所有类型的文章中）。这两项技术还存在其他变体，但这是两种非常简单的模型。如果可行，我通常建议您对数据进行水平划分。即，“类别”列的每个可能值都自成一列（或表），并且与该文章相关的可搜索关键字仅存储在此列中。采用此方法，而不是使用一个“正文”列和一个“类别”列，可以去掉“类别”列，而使用存储可搜索关键字的“Body_<category>”列。如以下示例所示：

-- 如果您可以调整架构，这非常有效 ‐ 每个类别

-- 都成为自己的列（或表格），并且需要命中的

-- 全文索引也较少。这明显需要作一些解释……

SELECT [key], [rank]

FROM FREETEXTTABLE(Articles, Body_Sports, 'Ichiro')

 

对于包含大量数据，且这些数据可适应此架构（或许是主架构）更改的系统，其性能会得到显著的提高。但在何时应用多个过滤器或不应用过滤器方面却有着明显的限制。当然，还有其他的方法可以解决这些问题。通过以上示例，您会了解一种将某些搜索条件抽象到架构的方法 - 实际上是“欺骗”优化程序（更确切的说是“成为”优化程序），因为在 SQL Server 本身的全文查询中当前不存在本地优化。

其他性能技巧

人们在聊天时常常问我的另一个问题是如何才能分页显示全文查询结果。换句话说，如果我要发出“root beer”查询，一次在某一 Web 页上显示 40 个结果，并且只希望返回该页面上的 40 个结果（例如，如果我在第三页，我希望仅返回第 81 至第 120 条结果）。

对于分页显示结果，我曾见过多种方法，但没有一种方法能够做到百分之百有效。我所推荐的方法可以最大程度地减少全文查询执行的次数（实际上，对于要分页显示的每个结果集只需执行一次），并将 Web 服务器用作一个简单的缓存。从更高的层面来讲，您只需在全文查询中检索一个完整的主键和排位值行集合（如果需要，可以在架构中使用最佳选择并提取常用过滤器），并将其存储在 Web 服务器的内存中（这取决于您的应用程序和负载，想象将 <32 字节的典型主键大小与 <4 字节的排位大小相加 [等于 <36 字节]，然后乘以通常返回的结果集 <1000 行，最后等于 <35K。假定一个在任何给定时间返回 <1000 个活动查询结果集中的一个活动缓存集，您将发现此活动缓存集在 Web 服务器上占用的内存少于 35MB - 这还可以接受）。

为了分页显示结果，该进程只遍历 Web 服务器的内存中存储的数组，并对 SQL Server 发出 SELECT 以便只显示需要显示的行和列。这又回到了全文查询仅返回主键和排位的概念中 - SELECT（甚至许多这样的查询语句）比全文查询的速度快许多倍。使用 SELECT 而不是与基表合并多个行，并结合多个其他策略，您可以保留 SQL Server 计算机上更多的 CPU 周期，并且更有效、更划算地利用 Web 领域。

另一种可以替代 Web 服务器端缓存的方法是在 SQL Server 自身中缓存结果集，并定义多种用于浏览这些结果的方法。虽然本文着重说明 Web 服务器 (ASP) 级别的应用程序设计，但 SQL Server 的可编程功能还为生成高性能的 Web 搜索应用程序提供了强大的框架。

小结

Microsoft SQL Server 2000 的全文搜索功能为索引和查询数据库中存储的非结构化文本数据提供了可靠、快速而灵活的方法。如果要广泛地将这种快速、准确的搜索功能应用于各种应用程序，那么很有必要充分利用其速度和精确性，来实现全文搜索解决方案。通过分布计算负载并通过某些巧妙的方式对数据进行组织，可以省下钱来购买其他硬件和软件，以摆脱因不必要的缓慢查询带来的困扰。在开发优秀的搜索应用程序时，通常要考虑到许多因素和注意事项，希望本文提供的信息和示例对您学习使用 SQL Server 2000 生成出色的 Web 搜索应用程序会有所帮助。

附录 A：实现全文搜索功能的最佳选择

改进全文查询性能和有效性的一种可行方法是实现“最佳选择”系统。此系统是一种很简单的方法，可确保某些与特定查询表达式匹配的行先于其他行返回。最佳选择没有复杂的预编程逻辑（例如，SharePoint Portal Server 就包含这样的逻辑），因此，通常是首选办法。

在本示例中挑选出最佳选择，并将唯一的主键和一些关键字存储在单独的表中。FREETEXTTABLE 查询对（非常小的）最佳选择表执行，并且从该查询中返回的任何结果都与对基表的 FREETEXTTABLE 查询结果一同返回。在给定这些搜索条件下，最先返回的将是所有“最佳选择”行，随后是被 MSSearch 视为关联程度最高的行（以递减顺序返回）。

下面是一个非常简单的用于创建最佳选择系统的示例脚本。

use myDb

 

create table documentTable(ftkey int not null, document ntext)

create unique index DTftkey_idx on documentTable(ftKey)

 

/*

   在此插入文档

   （要生成全文索引的所有文档）

*/

 

-- 为所有文档表创建全文目录和索引

exec sp_fulltext_catalog 'documents_cat', 'create', 'f:\ftCats'

exec sp_fulltext_table 'documentTable', 'create', 'documents_cat',

      'DTftkey_idx'

exec sp_fulltext_column 'documentTable', 'document', 'add'

exec sp_fulltext_table 'documentTable', 'start_change_tracking'

exec sp_fulltext_table 'documentTable', 'start_background_updateindex'

 

/*

   现在创建最佳选择表和索引

   （添加应该始终最先返回的文档）

*/

create table bestBets(ftKey int not null, keywords ntext)

create unique index BBftkey_idx on bestBets(ftKey)

 

/*

   在此插入最佳选择

*/

 

-- 为最佳选择表创建全文目录和索引

exec sp_fulltext_catalog 'bestBets_cat', 'create', 'f:\ftCats'

exec sp_fulltext_table 'bestBets', 'create', 'bestBets_cat', 'BBftkey_idx'

exec sp_fulltext_column 'bestBets', 'keywords', 'add'

exec sp_fulltext_table 'bestBets', 'start_change_tracking'

exec sp_fulltext_table 'bestBets', 'start_background_updateindex'

 

首先创建了一个通用的“所有文档”表，用于存储所有要全文索引的文档。通常情况下，文档表中包含其他列，但在本文中，只包含两列 - 主键索引和文档本身。全文目录和索引是为文档表而创建的。

接着创建了“最佳选择”表，用于存储所有全文查询中首先返回的特殊文档。此表只需具有全文主键列和文档本身（对将某些文档作为查询目标的策略进行优化，包括在该文档本身不包含的文档中添加其他关键字）。全文目录和索引是为最佳选择表而创建的。

最佳选择表和文档表可以共享文档（最佳选择文档还存储在常规文档表中，它们共享同一个主键值），也可以相互排斥（最佳选择文档只存储在最佳选择表中）。为便于检索，使最佳选择表与文档表互斥更为容易 - 这样做就无需从最佳选择和返回的普通搜索结果行集合中删除共享操作。另一方面，使用此方法维护文档可能很难实现，因为在此方法中，要在查询中添加逻辑来删除返回的行集合之间的共享文档。

如果给定上面的表，则可以创建两个存储过程，以便对最佳选择表和文档表进行搜索。可使用 Web 服务器级别的逻辑或其他存储过程来缓存和显示所需结果（与最佳选择一起使用时，请参阅下面有关缓存、显示和分页的一个完整、有效的示例）。

首先，创建一个用于检索最佳选择行（如果有）的存储过程：

create procedure BBSearch @searchTerm varchar(1024) as

 

select [key], [rank] from freetexttable(bestBets, keywords, @searchTerm) order by [rank] desc

 

确保已对传入搜索字符串进行清理，以避免在服务器上随意执行 T-SQL，并确保用单引号将该字符串括起。这种情况下，使用 FREETEXTTABLE 比使用 CONTAINSTABLE 要好，因为 FREETEXTTABLE 将采用寻根溯源功能，并找到与任何搜索词相匹配的最佳选择。

接下来，第二个存储过程检索与常规搜索标准匹配的文档（如果有）：

create procedure FTSearch @searchTerm varchar(1024) as

 

select [key], [rank] from freetexttable(documentTable, keywords, @searchTerm) order by [rank] desc

 

此外，请确保已清理传入搜索字符串，并用单引号将该字符串括起。

执行这些存储过程时，应该在两个存储过程中传入相同的搜索词，首先执行最佳选择搜索，然后执行普通全文搜索。下一节更全面地介绍了在构建 Web 搜索应用程序时，如何与其他全文搜索技术一起使用最佳选择。

附录 B：使用最佳选择、结果分页和有效全文查询逻辑的示例应用程序

在本例中，我们实现了一个几乎利用了本文介绍的所有优化方案的 Web 搜索应用程序。我们对联机零售商目录使用简单的搜索引擎方案，并假定在通信量很高的情况下，所有用户都期待在很短的响应时间内获得结果。本示例使用了前一节中的最佳选择表和存储过程。

此应用程序只是一些可用于实现最佳全文搜索性能的高级策略的简单示例。本示例使用了 ASP，也可使用 ISAPI、ASP.NET 或其他平台来实现具有各自优缺点的类似解决方案。会话对象并不一定对所有应用程序都适用，如果使用不当，可能带来一定程度的危险。在本例中，我们使用会话对象来实现快速有效的缓存机制 - 当然还有许多其他方法可以在不同程度上实现该功能。

 

 

 

 

关于安装设置和基本编程请参见Microsoft SQL Server 全文索引服务安装设置篇(http://www.chinaasp.com/faq/showfaq.asp?id=1234)和Microsoft SQL Server 全文索引服务编程篇(http://www.chinaasp.com/faq/showfaq.asp?id=1235)。

SQL 7的全文检索和Index Server的检索方式非常类似。

Contains

AND, or, NOT

可以在Contains中很方便使用逻辑表达式

Example:

Select username from member where contains(userinfo,'"作家" AND "木匠"')

Select username from member where contains(userinfo,'"作家" or "木匠"')

Select username from member where contains(userinfo,'"作家" AND NOT "木匠"')

NEAR

这是一个在普通的逻辑表达式中没有的关键字，意思是很简单，就是说找到靠近的两个词

Example:

Select Content from MicrosoftRecord where contains(Content,'"比尔·盖茨" NEAR "保罗·艾伦"')

这就表示要找到全文中包含比尔·盖茨和保罗·艾伦，并且两个词相隔不远。

FORMSOF INFLECTIONAL

这个功能可以查找单词的各种形式，比如过去式、复数、动词形式、名词形式等。可惜对中文没什么用

Example:

Select ProductName FROM Products Where CONTAINS(ProductName,'FORMSOF (INFLECTIONAL, dry)')

*

这个功能可以查找单词的前缀，不过对中文也没有什么用处

Example:

Select ProductName FROM Products Where CONTAINS(ProductName, '"dis*"')

ISABOUT WEIGHT

这个功能可以给复合查询时不同的条件以不同的权重，以决定返回的记录集的顺序

Select CategoryName, Description FROM Categories Where CONTAINS(Description, 'ISABOUT spread weight (.8), sauces weight (.4), relishes weight (.2) )' )

权重的值可以从0.0到1.0

ContainsTable

它的使用方式和Contains基本相同，这里就不再重复介绍了。要提到的是它返回的是一张供你进一步查询的表，而不是一个查询条件。

FreeText

如果使用这种方式，那么查询的时候会使用分词技术来实现模糊查询，并且过滤掉一些非关键词，比较类似于Contains中的FORMSOF，可惜对中文也没有什么支持

Example:

Select CategoryName FROM Categories Where FREETEXT (Description, 'sweetest candy bread and dry meat' )

FreeTextTable

它和FreeText的差别就跟Contains和ContainsTable的差别一样。