转贴：使用 Microsoft SQL Server 2000 的全文搜索功能构建 Web 搜索应用程序

llcong

http://www.microsoft.com/china/msdn/archives/library/dnsql2k/html/sql_fulltextsearch.asp

使用 Microsoft SQL Server 2000 的全文搜索功能构建 Web 搜索应用程序

Andrew B. Cencini
Microsoft Corporation
2002年12月
　　
　　适用于：
    Microsoft® SQL™ Server 2000
　　摘要：学习如何充分利用 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_”列。如以下示例所示：
-- 如果您可以调整架构，这非常有效 – 每个类别
-- 都成为自己的列（或表格），并且需要命中的
-- 全文索引也较少。这明显需要作一些解释……
SELECT [key], [rank]
FROM FREETEXTTABLE(Articles, Body_Sports, 'Ichiro')

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

其他性能技巧

　　人们在聊天时常常问我的另一个问题是如何才能分页显示全文查询结果。换句话说，如果我要发出“root beer”查询，一次在某一 Web 页上显示 40 个结果，并且只希望返回该页面上的 40 个结果（例如，如果我在第三页，我希望仅返回第 81 至第 120 条结果）。
　　对于分页显示结果，我曾见过多种方法，但没有一种方法能够做到百分之百有效。我所推荐的方法可以最大程度地减少全文查询执行的次数（实际上，对于要分页显示的每个结果集只需执行一次），并将 Web 服务器用作一个简单的缓存。从更高的层面来讲，您只需在全文查询中检索一个完整的主键和排位值行集合（如果需要，可以在架构中使用最佳选择并提取常用过滤器），并将其存储在 Web 服务器的内存中（这取决于您的应用程序和负载，想象将