文件名称：sql2005全文检索.doc
文件大小：506KB
文件格式：DOC
更新时间：2012-09-29 04:18:26
sql2005全文检索
SQL Server 2005全文检索技术在网站上的应用实录 http://tech.ddvip.com 2008年01月21日 社区交流 Excel服务器-用Excel做管理系统 关键字： DataGuard checkpoint 动态查询 synonyms 卸载SQL 2005 SQL Server群集 内容摘要：本文提供了网站如何设计和实现基于SQL Server 2005的全文检索实例，希望能对正在使用SQL Server 2005构建网站搜索的同仁有所裨益。 一、前言 “人类失去搜索，世界将会怎样?”，同样，很难想象一个拥有极大信息量的行业网站门户没有站内全文搜索将会出现怎样的局面，网站全文检索对于挖掘网站信息和整合网站资源的价值是不言而喻的。我们看到，通用型的搜索引擎已经成为了互联网世界的门户，而对于一个信息量极其丰富的网站而言，全网搜索或许会成为本网站的门户，正是所谓的门户之门。 实现网站全文检索有几种常见方案，比如应用数据库全文检索，开源搜索引擎，使用Google API等， 本文我们将就如何使用SQL Server 2005多快好省地建立网站全文检索展开探讨。 二、全文检索技术说明 1、应用背景 先介绍下Z网站，它是国内投融资行业最大的行业门户，网站拥有海量的融资信息、投资信息、招商信息、创业信息、商机信息、资讯信息、产权交易信息、招标投标信息、中标信息、会展信息等投融资行业的信息资源。网站全文检索实现的首要需求就在于能够让用户输入诸如行业关键字后系统能快速返回给符合用户在全范围内查找条件的记录, 从而有效地利用资源，更好地满足用户需求。 2、主要检索技术的区别 有了数据但是没有被使用，那么这些数据不应该被称为信息。它们无非是不断充斥设备和网络的比特而已，但是如何把数据挖掘出来提供给需要的人员，检索技术是其中非常有效的途径之一。 现基于微软平台，针对SQL Server 2005提供的全文检索技术进行介绍。与关系数据查询、多维数据库查询和基于XML的XQuery、XPath不同，全文检索技术主要处理对象是基于超大 数据量的文本数据和结构化的二进制数据上类似LIKE的模糊查询。主要区别见下表。 表1：全文检索与关系数据库查询、多维数据查询、XML查询的对比 关系数据库查询 多维数据查询 XML查询 全文检索 检索技术 SQL MDX XQuery、XPath SQL (extension) 主要处理对象 关系二维数据 结构化多维数据 层次型数据 大容量二维和层次型数据的模糊检索 主要应用领域 一般的OLTP类应用 一般的OLAP类分析型应用 面向Internet、Intranet的松散耦合SOA应用 企业/网站内部信息/知识管理类应用 索引 大量使用非聚簇索引，一般保存在数据库中。 通过层次型、保存中间结果的方式，通过不同的轴向快速定位信息剖面。 基于XPath的索引，索引一般保存在数据库中。 基于关键字的索引，保存在文件系统中。每个表仅支持一个索引。 3、全文检索技术简要介绍 全文检索主要应用领域如下： （1）大数据量、超大数据量的结构化平文本数据和模糊匹配查找（Char、Varchar、Nvarchar）。 （2）大数据量、超大数据量的层次型XML数据展开后的查找---含模糊查找（Xml type）。 （3）标准格式的二进制非结构化Word数据的查找（VarBinary[max]、Image）。 与其他检索技术不同的是，全文检索不仅仅提供词汇层次的查询支持，而且可以根据语言环境、不同语言的特点，甚至于用户自定义的配置提供不同语义级的大容量的数据模糊匹配检索支持。为了提供语义层次的检索，SQL Server 2005的全文检索明确了如下几个概念： （1）断字符（Word Breaker）：因为对于不同的语言，哪些符号可以用于词汇的分割是不同的，因此全文检索支持不同语言环境的不同断字符。 （2）标记（Token）：是由断字符标识的词或字符串。由于划分是基于特定语言完成的，因此也可以做到语义层次的支持。 （3）干扰词（Noise Word）：主要是那些经常出现，但是对于检索没有多少帮助的词汇。例如：英语中的“a”、 “and”、 “is”、 “the”，汉语中的“的”、 “不”、 “以”、 “了”等。SQL Server 2005中提供配置文件，允许用户自定义自己语言、甚至与本行业、本企业的检索干扰词。 （4）词干分析器（Stemmer）：通过断字符分割后，根据具体的语言和该语言的语法规程生成的特定词汇的变形。 这个即是搜索引擎常提到的分词技术。 （5）同义词：即便是同一个语言，在检索的情况下也存在同义词如何处理的问题。如果一个检索系统不能够识别近义词，而只能识别完全匹配的词汇，那对于我们中文这种表义的语言而言会带来很大不便。同样的，一个行业内部也有很多同义词或者是缩略语。例如如下的词语。 广播行业：“ABC”与“英国ABC广播公司”基本上类似，但是也可能和“澳大利亚广播公司”混淆。 *行文：“ABC”与南美的“阿根廷、巴西、智利三国”是同义词。 不仅如此，由于日常使用的习惯，我们在口语表达和书面语表达上也有区别，这个也需要预先定义。例如，很多口头常用的技术产品“Win2K”、 “WinXP”等，一般都很正式的称为“Windows 2000”和 “Windows XP”，因此SQL Server 2005上也提供类似词汇替换的支持，而且这些支持也是与具体语言相关的。 4、SQL Server 2005全文检索的技术架构 让我们首先得问自己，什么是全文检索？它是指Microsoft SQL Server 2005 具备针对 Microsoft SQL Server 表中基于纯字符的数据进行全文查询的功能。全文查询可以包括词和短语，或者词或短语的多种形式。 当我们明确了全文检索概念之后，我们来看看它的内部结构。 Microsoft SQL Server 2005 中的全文搜索由 Microsoft Full-Text Engine for SQL Server (MSFTESQL) 服务提供支持。MSFTESQL 服务有两个角色，即索引支持和查询支持。 全文搜索的组件 全文搜索的体系结构由下列组件构成： ◆Microsoft Full-Text Engine for SQL Server (MSFTESQL) ◆Microsoft Full-Text Engine Filter Daemon (MSFTEFD)，它包含下列组件： （1）筛选器 （2）协议处理程序 （3）断字符 SQL Server中的全文搜索的体系结构如下所示。 图1 让我们逐一介绍两类重要的引擎及其在整个体系结构中的重要作用。 Full-Text Engine for SQL Server (MSFTESQL) MSFTESQL 服务负责进行下列操作： ◆填充全文索引。 ◆管理全文索引和全文目录。 ◆帮助对 SQL Server 数据库中的表进行全文搜索。 让我们来看看SQL Server 2005全文索引的过程 全文填充（也称为爬网或爬虫）开始后，数据库引擎会将大批数据存入内存并通知 Microsoft SQL Server 全文引擎 (MSFTESQL) 服务开始进行索引。MSFTESQL 服务将对表的某一列或几列中的字符和格式化二进制数据编制索引。全文引擎将使用协议处理程序组件从内存中取出数据并进行进一步处理，从而生成全文索引。 对存储在 varbinary(max) 或 image 列中的数据编制索引时，筛选器（实现了 IFilter 接口）将基于为该数据指定的文件格式（例如，Microsoft Word）来提取文本。在某些情况下，筛选器组件会要求将 varbinary(max) 或 image 数据写到服务帐户 Temp 目录中，而不是将其存入内存。 在处理过程中，通过断字符将收集到的文本数据分隔成各个单独的标记或关键字。用于标记化的语言将在列级指定，或者也可以通过筛选器组件在 varbinary(max)、image 或 xml 数据内标识。 还可能执行其他处理以删除干扰词并在将标记存储到全文索引或索引片断中之前将这些标记规范化。 Microsoft Full-Text Engine for SQL Server 的功能 SQL Server 2005 为全文引擎提供了并行安装方式。这意味着对于每个 SQL Server 实例，都存在一个专用的 MSFTESQL 实例，其中包括专用的组件（例如断字符和筛选器）、资源（例如内存）和配置（例如服务级设置，实例级的 resource_usage 是一个更具体的例子）。单个 MSFTESQL 服务实例可管理相关联的 SQL Server 实例的全文索引。利用 MSFESQL 服务，SQL Server 可超出 SQL 标准对字符串列执行更为复杂的搜索。 MSFTESQL 服务担当了以下角色： 索引支持 MSFTESQL 实现了为数据库定义的全文目录和索引。 查询支持 MSFTESQL 可处理全文搜索查询并确定索引中符合全文选择条件的项。对于符合选择条件的每一项，MSFTESQL 将向 SQL Server 服务返回相应行的标识外加一个排名值，而后者将使用这些信息来构造查询结果集。所支持的查询类型包括： ◆搜索词或短语。 ◆搜索位置邻近的词。 ◆搜索词的变形。 可管理性支持 全文目录和索引不会存储在 SQL Server 数据库中。MSFTESQL 服务负责管理全文目录。 Microsoft Full-Text Engine Filter Daemon MSFTEFD 由负责从表中访问和筛选数据以及进行断字和词干分析的组件组成。 ◆筛选器 筛选器的任务是从文档中提取文本化信息流，并舍弃所有非文本化信息和格式化信息。筛选器将生成文本字符串和属性/值对，并依次将它们传递给索引引擎。 筛选器可从特定的文档格式（如 Microsoft Word 文档或文本文件）中提取文本化信息。例如，Microsoft 提供了 Microsoft Office 筛选器，此筛选器可以从 Word、Microsoft Excel 和 Microsoft PowerPoint 文件中提取文本和属性。其他筛选器用于处理 HTML 或电子邮件。还可以使用第三方筛选器。 ◆SQL 协议处理程序 在 SQL Server 2005 中，SQL 协议处理程序的工作是从指定数据库中的表内访问数据。 断字符 断字符是用于在查询或抓取的文档中确定字符流中的字符边界位置。有关详细信息。 全文搜索的索引组件负责全文索引的初始填充，以及当全文索引表中的数据被修改时该索引的后续更新。为了提升全文索引过程的效率，Microsoft SQL Server 2005 改进了全文收集机制的体系结构，从而大大增强了性能。 Microsoft Full-Text Engine for SQL Server (MSFTESQL) 服务是一个全文索引和搜索引擎。MSFTESQL 引擎基于 Microsoft Search (MSSearch) 技术，它与 Microsoft SQL Server 2005 Database Engine 进行了最为紧密的集成。 全文引擎作为 MSFTESQL 服务在操作系统上运行。SQL Server 与 MSFTESQL 必须拥有相同的服务帐户。默认情况下将安装 MSFTESQL 服务，但是只有在使用全文搜索时才会运行此项服务。 三、网站全文检索设计 1、架构设计 采用OLTP交易数据库和OLAP数据仓库（用于搜索和分析）分离的模式，OLTP作为OLAP的数据源通过SQL Server Integration Services (SSIS)定期导入到OLAP数据仓库环境中，OLAP采用星型结构以便于更好地满足搜索和将来的数据挖掘。 OLAP数据仓库的建立目标为了检索和数据挖掘，故其事实表的建立可以反范式原则设计。 图2 2、原型设计（由于篇幅限制，这里仅给出搜索主页和主要资源查询页面） 图3 3、数据库设计 图4 4、数据字典 MainInfoTab(信息主表) CapitalInfoTab（资本信息明细表） ProjectInfoTab（项目信息明细表） MerchantInfoTab（*招商信息明细表） 四、数据库全文检索实现 至此，我们已经完成了全文检索的设计工作，现在可以来实现它的功能啦！ 首先， 让我们建立检索表的全文检索，全文检索要求唯一索引，故需要在相关表建立唯一聚集索引。 第二步，使用SQL DDL或者SQL Server Management Studio建立表的全文检索。 1）使数据库支持全文检索。 图5 或者通过键入命令 EXEC SP_FULLTEXT_DATABASE 'Enable' 命令达到同样效果。 2）定义表的全文检索目录和索引字段。 在表的鼠标右键通过点击“全文检索定义全文检索”将弹出如下执行向导，本向导执行完毕则该表的全文检索业已完毕。 a)选择要索引的字段 图6 或者键入如下命令： CREATE FULLTEXT INDEX ON TableName KEY INDEX PK_IndexName ON DB WITH CHANGE_TRACKING AUTO ALTER FULLTEXT INDEX ON TableName ADD ColumnName b)选择或创建新的索引目录。 图7 c)定义填充计划。 图8 至此，表的全文索引已经建立完毕，表示只要键入SQL 指令就可以完成全文检索功能。 第三步，开发存储过程并把结果集分页，以供前台页面调用返回查询的结果。 1) 建立找资本全文检索储存过程USP_CaptialInfo_FullIndex。 /* 找资本全文索引开发过程 */ CREATE PROCEDURE USP_CaptialInfo_FullIndex ( @TableViewQueryNameVarchar( 1024 ), --传入的查询字符窜 @SearchKeyword nvarchar(100), --传入的查询关键字 @SelectStr Varchar( 500 ), --选择列字符串 @Criteria Varchar( 8000 ), --查询条件 @Sort Varchar( 255 ), --排序字符串 @FristTopNum int, --显示的第一页置顶的数目 @Page bigint OUTPUT , --显示的当前页号 @CurrentPageRowbigint, --页大小(显示多少行) @TotalCount bigint output, --通过该查询条件，返回的查询记录的总页数 @Totaltimes bigint output --所有搜索时间，以秒为单位 ) as DECLARE @starttime datetime, @endtime datetime SELECT @starttime = getdate() IF ISNULL(@SearchKeyword,'') !='' OR RTRIM(@SearchKeyword)!='' BEGIN SET @TableViewQueryName = ' SELECT '+ ' ROW_NUMBER() OVER (ORDER BY RANK DESC) AS SerialNumber ,'+ ' F.[rank], '+ ' p.*' + ' FROM'+ ' FREETEXTTABLE( CapitalInfoFactTab , (ProvinceName, CityName, CountyName, Keyword,Title ,IndustryBName , shortcontent, ComAbout , ComBreif) , '+ ''''+@SearchKeyword+ ''''+') AS f '+ ' INNER JOIN CapitalInfoFactTab AS p '+ ' ON f.[key] = p.infoID ' EXEC [USP_GetFrontDataList_ByFullIndex] @TableViewQueryName, @SearchKeyword, @SelectStr, @Criteria, @Sort, @FristTopNum, @Page OUTPUT , @CurrentPageRow , @TotalCount OUTPUT END ELSE BEGIN EXEC dbo.GetFrontDataList 'dbo.ProjectInfoFactTab', 'InfoID', @SelectStr,@Criteria,@Sort,0, @Page output, @CurrentPageRow, @TotalCount output END SELECT @endtime = getdate() SELECT @Totaltimes = DATEdiff(Ms, @starttime ,@endtime) RETURN 2)建立通用分页存过程[USP_GetFrontDataList_ByFullIndex]。由于性能考虑，返回给前台页面需要网站数据库端即完成分页。 CREATE PROCEDURE [dbo].[USP_GetFrontDataList_ByFullIndex] ( @TableViewQueryName Varchar( 1024 ), --Table或View或者Query的名字或字符串 @Key Varchar( 50 ), --关键字 @SelectStr Varchar( 500 ), --选择列字符串 @Criteria Varchar( 8000 ),--查询条件 @Sort Varchar( 255 ), --排序字符串 @FristTopNum INT, --显示的第一页置顶的数目 @Page BIGINT OUTPUT, --显示的当前页号 @CurrentPageRow BIGINT, --页大小(显示多少行) @TotalCount BIGINT OUTPUT --通过该查询条件，返回的查询记录的总页数 ) ASSET NOCOUNT ONif charindex(';',@Criteria)>0 or charindex('--',@Criteria)>0 or charindex('/*',@Criteria)>0 or charindex('*/',@Criteria)>0 or charindex('syscolumns',@Criteria)>0 orcharindex('sysfiles',@Criteria)>0 or charindex('char(124)',@Criteria)>0 or charindex('1=1',@Criteria)>0 RETURN DECLARE @TotalStr nVarchar(4000) DECLARE @Str nVarchar(4000) DECLARE @TopRowNum bigint IF @SelectStr IS NULL AND RTRIM(LTRIM(@Criteria)) = '' SET @SelectStr = '*' IF @FristTopNum IS NULL AND @FristTopNum < 0 BEGIN SET @FristTopNum = 0 END ELSE IF @FristTopNum > @CurrentPageRow BEGIN SET @FristTopNum = @CurrentPageRow ENDIF @CurrentPageRow > 0 BEGIN IF @Criteria IS NOT NULL AND RTRIM(LTRIM(@Criteria)) <> '' BEGIN SET @TotalStr = 'SELECT @TotalCount=COUNT(*) FROM ' +'('+ @TableViewQueryName +')'+ ' T ' + ' WHERE ' + @Criteria END ELSE BEGIN SET @TotalStr = 'SELECT @TotalCount=COUNT(*) FROM ' +'('+@TableViewQueryName +')'+ ' T ' END PRINT @TotalStr EXEC sp_ExecuteSql @TotalStr, N'@TotalCount bigint output',@TotalCount output SET @TotalCount = @TotalCount + isnull(@FristTopNum ,0) DECLARE @TotalPage bigint SET @TotalPage = @TotalCount/@CurrentPageRow IF @TotalCount%@CurrentPageRow > 0 BEGIN SET @TotalPage = @TotalPage + 1 END IF @Page <= 0 BEGIN SET @Page = 1 END IF @TotalPage > 0 AND @Page > @TotalPage BEGIN SET @Page = @TotalPage END--组织查询语句 SET @Str = 'SELECT ' + @SelectStr + ' FROM (' + @TableViewQueryName + ') T WHERE T.SerialNumber >' + cast ((@Page-1) as varchar(10)) + '*' +cast( @CurrentPageRow as varchar(10))+ ' AND T.SerialNumber <= '+ cast (@Page as varchar(10)) + '*' +cast( @CurrentPageRow as varchar(10))IF @Sort IS NOT NULL ANDRTRIM(LTRIM(@Sort)) <> '' BEGIN IF @Criteria IS NOT NULL AND RTRIM(LTRIM(@Criteria)) <> '' BEGIN SET @Str = @Str + ' AND (' + @Criteria + ') ORDER BY '+@Sort END ELSE BEGIN SET @Str = @Str + ' AND (' + @Criteria + ') ORDER BY '+@Sort END END ELSE BEGIN IF @Criteria IS NOT NULL AND RTRIM(LTRIM(@Criteria)) <> '' BEGIN SET @str = @str + ' AND (' + @Criteria + ') ' END END --对无记录时当前页数的处理 IF @TotalCount=0 BEGIN SET @Page = 0 END END EXEC sp_ExecuteSql @Str 由于本行业网站可以提高如下几类信息资源，现列表分示如下： ID 检索内容 数据表 检索命令示例 1 投资 CapitalInfoTab DECLARE @RC int DECLARE @TableViewQueryName varchar(1024) DECLARE @SearchKeyword nvarchar(100) DECLARE @SelectStr varchar(500) DECLARE @Criteria varchar(8000) DECLARE @Sort varchar(255) DECLARE @FristTopNum int DECLARE @Page bigint DECLARE @CurrentPageRow bigint DECLARE @TotalCount bigint DECLARE @Totaltimes bigint -- TODO: 在此处设置参数值。 SET @SearchKeyword = '地产项目' SET @SelectStr = '*' SET @Sort = '' SET @Page= 1 SET @CurrentPageRow = 20 EXECUTE @RC = [InvestDM].[dbo].USP_CapitalInfo_FullIndex @TableViewQueryName ,@SearchKeyword ,@SelectStr ,@Criteria ,@Sort ,@FristTopNum ,@Page ,@CurrentPageRow ,@TotalCount OUTPUT ,@Totaltimes OUTPUT SELECT @Page,@TotalCount,@Totaltimes 2 融资 ProjectInfoTab DECLARE @SearchKeyword nvarchar(100) --传入的查询关键字 SET @SearchKeyword = '深圳' SELECT p.title, p.infoid, f.[rank] , keyword,title,provinceName,cityName,CountyName FROM FREETEXTTABLE([ProjectInfoFactTab], (provinceName,cityName,CountyName,keyword,title), @SearchKeyword) AS f INNER JOIN [ProjectInfoFactTab] AS p ON f.[key] = p.infoID ORDER BY RANK DESC 3 招商 MerchantInfoTab DECLARE @SearchKeyword nvarchar(100) --传入的查询关键字 SET @SearchKeyword = '深圳' SELECT ROW_NUMBER() OVER (ORDER BY RANK DESC) AS SerialNumber , F.[rank], p.* FROM FREETEXTTABLE( MerchantInfoFactTab , (ProvinceName, CityName, CountyName, Keyword, Title ,IndustryBName , shortcontent, ZoneAbout , ZoneAboutBrief, MerchantTypeName ,MerchantAttributeName, CooperationDemandName ) , @SearchKeyword) AS f INNER JOIN MerchantInfoFactTab AS p ON f.[key] = p.infoID 4 资讯 NewsTab DECLARE @SearchKeyword nvarchar(100) --传入的查询关键字 SET @SearchKeyword = '深圳' SELECT ROW_NUMBER() OVER (ORDER BY RANK DESC) AS SerialNumber , F.[rank] , p.* FROM FREETEXTTABLE( NewsInfoFactTab , (AreaName, Keyword, Title , DisplayTitle, subtitle ,Summary , Content ,NewsIndustryName , NewsTypeName ) , @SearchKeyword ) AS f INNER JOIN NewsInfoFactTab AS p ON f.[key] = p.infoID