sql全文检索.docx
《sql全文检索.docx》由会员分享，可在线阅读，更多相关《sql全文检索.docx（28页珍藏版）》请在冰豆网上搜索。
sql全文检索
资讯|操作系统|编程|平面|动画|网络|安全|通信|认证|办公|服务器|数据库|企业应用|软件应用|存储|电子机械|QQ|SOA
∙MySQL
∙SQLServer
∙Oracle
∙DB2
∙PostgreSQL
∙SQLite
∙Sybase
∙Informix
∙Foxpro
∙Access
∙SQL语言
∙数据仓库
∙数据库理论
豆豆网>技术应用>数据库>SQLServer>正文
SQLServer2005全文检索技术在网站上的应用实录
2008年01月21日 社区交流
关键字：
DataGuardcheckpoint动态查询synonyms卸载SQL2005SQLServer群集
内容摘要：
本文提供了网站如何设计和实现基于SQLServer2005的全文检索实例，希望能对正在使用SQLServer2005构建网站搜索的同仁有所裨益。
一、前言
“人类失去搜索，世界将会怎样?
”，同样，很难想象一个拥有极大信息量的行业网站门户没有站内全文搜索将会出现怎样的局面，网站全文检索对于挖掘网站信息和整合网站资源的价值是不言而喻的。
我们看到，通用型的搜索引擎已经成为了互联网世界的门户，而对于一个信息量极其丰富的网站而言，全网搜索或许会成为本网站的门户，正是所谓的门户之门。
实现网站全文检索有几种常见方案，比如应用数据库全文检索，开源搜索引擎，使用GoogleAPI等，本文我们将就如何使用SQLServer2005多快好省地建立网站全文检索展开探讨。
二、全文检索技术说明
1、应用背景
先介绍下Z网站，它是国内投融资行业最大的行业门户，网站拥有海量的融资信息、投资信息、招商信息、创业信息、商机信息、资讯信息、产权交易信息、招标投标信息、中标信息、会展信息等投融资行业的信息资源。
网站全文检索实现的首要需求就在于能够让用户输入诸如行业关键字后系统能快速返回给符合用户在全范围内查找条件的记录,从而有效地利用资源，更好地满足用户需求。
2、主要检索技术的区别
有了数据但是没有被使用，那么这些数据不应该被称为信息。
它们无非是不断充斥设备和网络的比特而已，但是如何把数据挖掘出来提供给需要的人员，检索技术是其中非常有效的途径之一。
现基于微软平台，针对SQLServer2005提供的全文检索技术进行介绍。
与关系数据查询、多维数据库查询和基于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数据展开后的查找---含模糊查找（Xmltype）。
（3）标准格式的二进制非结构化Word数据的查找（VarBinary[max]、Image）。
与其他检索技术不同的是，全文检索不仅仅提供词汇层次的查询支持，而且可以根据语言环境、不同语言的特点，甚至于用户自定义的配置提供不同语义级的大容量的数据模糊匹配检索支持。
为了提供语义层次的检索，SQLServer2005的全文检索明确了如下几个概念：
（1）断字符（WordBreaker）：
因为对于不同的语言，哪些符号可以用于词汇的分割是不同的，因此全文检索支持不同语言环境的不同断字符。
（2）标记（Token）：
是由断字符标识的词或字符串。
由于划分是基于特定语言完成的，因此也可以做到语义层次的支持。
（3）干扰词（NoiseWord）：
主要是那些经常出现，但是对于检索没有多少帮助的词汇。
例如：
英语中的“a”、“and”、“is”、“the”，汉语中的“的”、“不”、“以”、“了”等。
SQLServer2005中提供配置文件，允许用户自定义自己语言、甚至与本行业、本企业的检索干扰词。
（4）词干分析器（Stemmer）：
通过断字符分割后，根据具体的语言和该语言的语法规程生成的特定词汇的变形。
这个即是搜索引擎常提到的分词技术。
（5）同义词：
即便是同一个语言，在检索的情况下也存在同义词如何处理的问题。
如果一个检索系统不能够识别近义词，而只能识别完全匹配的词汇，那对于我们中文这种表义的语言而言会带来很大不便。
同样的，一个行业内部也有很多同义词或者是缩略语。
例如如下的词语。
广播行业：
“ABC”与“英国ABC广播公司”基本上类似，但是也可能和“澳大利亚广播公司”混淆。
政府行文：
“ABC”与南美的“阿根廷、巴西、智利三国”是同义词。
不仅如此，由于日常使用的习惯，我们在口语表达和书面语表达上也有区别，这个也需要预先定义。
例如，很多口头常用的技术产品“Win2K”、“WinXP”等，一般都很正式的称为“Windows2000”和“WindowsXP”，因此SQLServer2005上也提供类似词汇替换的支持，而且这些支持也是与具体语言相关的。
4、SQLServer2005全文检索的技术架构
让我们首先得问自己，什么是全文检索？
它是指MicrosoftSQLServer2005具备针对MicrosoftSQLServer表中基于纯字符的数据进行全文查询的功能。
全文查询可以包括词和短语，或者词或短语的多种形式。
当我们明确了全文检索概念之后，我们来看看它的内部结构。
MicrosoftSQLServer2005中的全文搜索由MicrosoftFull-TextEngineforSQLServer（MSFTESQL）服务提供支持。
MSFTESQL服务有两个角色，即索引支持和查询支持。
全文搜索的组件
全文搜索的体系结构由下列组件构成：
◆MicrosoftFull-TextEngineforSQLServer（MSFTESQL）
◆MicrosoftFull-TextEngineFilterDaemon（MSFTEFD），它包含下列组件：
（1）筛选器
（2）协议处理程序
（3）断字符
SQLServer中的全文搜索的体系结构如下所示。
图1
让我们逐一介绍两类重要的引擎及其在整个体系结构中的重要作用。
Full-TextEngineforSQLServer（MSFTESQL）
MSFTESQL服务负责进行下列操作：
◆填充全文索引。
◆管理全文索引和全文目录。
◆帮助对SQLServer数据库中的表进行全文搜索。
让我们来看看SQLServer2005全文索引的过程
全文填充（也称为爬网或爬虫）开始后，数据库引擎会将大批数据存入内存并通知MicrosoftSQLServer全文引擎（MSFTESQL）服务开始进行索引。
MSFTESQL服务将对表的某一列或几列中的字符和格式化二进制数据编制索引。
全文引擎将使用协议处理程序组件从内存中取出数据并进行进一步处理，从而生成全文索引。
对存储在varbinary（max）或image列中的数据编制索引时，筛选器（实现了IFilter接口）将基于为该数据指定的文件格式（例如，MicrosoftWord）来提取文本。
在某些情况下，筛选器组件会要求将varbinary（max）或image数据写到服务帐户Temp目录中，而不是将其存入内存。
在处理过程中，通过断字符将收集到的文本数据分隔成各个单独的标记或关键字。
用于标记化的语言将在列级指定，或者也可以通过筛选器组件在varbinary（max）、image或xml数据内标识。
还可能执行其他处理以删除干扰词并在将标记存储到全文索引或索引片断中之前将这些标记规范化。
MicrosoftFull-TextEngineforSQLServer的功能
SQLServer2005为全文引擎提供了并行安装方式。
这意味着对于每个SQLServer实例，都存在一个专用的MSFTESQL实例，其中包括专用的组件（例如断字符和筛选器）、资源（例如内存）和配置（例如服务级设置，实例级的resource_usage是一个更具体的例子）。
单个MSFTESQL服务实例可管理相关联的SQLServer实例的全文索引。
利用MSFESQL服务，SQLServer可超出SQL标准对字符串列执行更为复杂的搜索。
MSFTESQL服务担当了以下角色：
索引支持
MSFTESQL实现了为数据库定义的全文目录和索引。
查询支持
MSFTESQL可处理全文搜索查询并确定索引中符合全文选择条件的项。
对于符合选择条件的每一项，MSFTESQL将向SQLServer服务返回相应行的标识外加一个排名值，而后者将使用这些信息来构造查询结果集。
所支持的查询类型包括：
◆搜索词或短语。
◆搜索位置邻近的词。
◆搜索词的变形。
可管理性支持
全文目录和索引不会存储在SQLServer数据库中。
MSFTESQL服务负责管理全文目录。
MicrosoftFull-TextEngineFilterDaemon
MSFTEFD由负责从表中访问和筛选数据以及进行断字和词干分析的组件组成。
◆筛选器
筛选器的任务是从文档中提取文本化信息流，并舍弃所有非文本化信息和格式化信息。
筛选器将生成文本字符串和属性/值对，并依次将它们传递给索引引擎。
筛选器可从特定的文档格式（如MicrosoftWord文档或文本文件）中提取文本化信息。
例如，Microsoft提供了MicrosoftOffice筛选器，此筛选器可以从Word、MicrosoftExcel和MicrosoftPowerPoint文件中提取文本和属性。
其他筛选器用于处理HTML或电子邮件。
还可以使用第三方筛选器。
◆SQL协议处理程序
在SQLServer2005中，SQL协议处理程序的工作是从指定数据库中的表内访问数据。
断字符
断字符是用于在查询或抓取的文档中确定字符流中的字符边界位置。
有关详细信息。
全文搜索的索引组件负责全文索引的初始填充，以及当全文索引表中的数据被修改时该索引的后续更新。
为了提升全文索引过程的效率，MicrosoftSQLServer2005改进了全文收集机制的体系结构，从而大大增强了性能。
MicrosoftFull-TextEngineforSQLServer（MSFTESQL）服务是一个全文索引和搜索引擎。
MSFTESQL引擎基于MicrosoftSearch（MSSearch）技术，它与MicrosoftSQLServer2005DatabaseEngine进行了最为紧密的集成。
全文引擎作为MSFTESQL服务在操作系统上运行。
SQLServer与MSFTESQL必须拥有相同的服务帐户。
默认情况下将安装MSFTESQL服务，但是只有在使用全文搜索时才会运行此项服务。
三、网站全文检索设计
1、架构设计
采用OLTP交易数据库和OLAP数据仓库（用于搜索和分析）分离的模式，OLTP作为OLAP的数据源通过SQLServerIntegrationServices（SSIS）定期导入到OLAP数据仓库环境中，OLAP采用星型结构以便于更好地满足搜索和将来的数据挖掘。
OLAP数据仓库的建立目标为了检索和数据挖掘，故其事实表的建立可以反范式原则设计。
图2
2、原型设计（由于篇幅限制，这里仅给出搜索主页和主要资源查询页面）
图3
3、数据库设计
图4
4、数据字典
MainInfoTab（信息主表）
CapitalInfoTab（资本信息明细表）
ProjectInfoTab（项目信息明细表）
MerchantInfoTab（政府招商信息明细表）
四、数据库全文检索实现
至此，我们已经完成了全文检索的设计工作，现在可以来实现它的功能啦！
首先，让我们建立检索表的全文检索，全文检索要求唯一索引，故需要在相关表建立唯一聚集索引。
第二步，使用SQLDDL或者SQLServerManagementStudio建立表的全文检索。
1）使数据库支持全文检索。
图5
或者通过键入命令 EXECSP_FULLTEXT_DATABASE 'Enable' 命令达到同样效果。
2）定义表的全文检索目录和索引字段。
在表的鼠标右键通过点击“全文检索定义全文检索”将弹出如下执行向导，本向导执行完毕则该表的全文检索业已完毕。
a）选择要索引的字段
图6
或者键入如下命令：
CREATEFULLTEXTINDEXONTableNameKEYINDEXPK_IndexNameON DBWITHCHANGE_TRACKINGAUTO
ALTERFULLTEXTINDEXONTableNameADDColumnName
b）选择或创建新的索引目录。
图7
c）定义填充计划。
图8
至此，表的全文索引已经建立完毕，表示只要键入SQL指令就可以完成全文检索功能。
第三步，开发存储过程并把结果集分页，以供前台页面调用返回查询的结果。
1）建立找资本全文检索储存过程USP_CaptialInfo_FullIndex。
/*
找资本全文索引开发过程
*/
CREATEPROCEDUREUSP_CaptialInfo_FullIndex
（
@TableViewQueryNameVarchar（1024）,--传入的查询字符窜
@SearchKeyword nvarchar（100）,--传入的查询关键字
@SelectStr Varchar（500）, --选择列字符串
@Criteria Varchar（8000）,--查询条件
@Sort Varchar（255）, --排序字符串
@FristTopNum int, --显示的第一页置顶的数目
@Page bigintOUTPUT , --显示的当前页号
@CurrentPageRowbigint, --页大小（显示多少行）
@TotalCount bigintoutput, --通过该查询条件，返回的查询记录的总页数
@Totaltimes bigintoutput --所有搜索时间，以秒为单位
）
as
DECLARE@starttimedatetime,
@endtimedatetime
SELECT @starttime=getdate（）
IF ISNULL（@SearchKeyword,''）!
=''OR RTRIM（@SearchKeyword）!
=''
BEGIN
SET@TableViewQueryName=
'SELECT '+
'ROW_NUMBER（）OVER（ORDERBYRANKDESC）ASSerialNumber,'+
'F.[rank],'+
'p.*'+
'FROM'+
'FREETEXTTABLE（CapitalInfoFactTab,（ProvinceName,CityName,
CountyName,Keyword,Title,IndustryBName,shortcontent,ComAbout,ComBreif）,
'+''''+@SearchKeyword+''''+'）ASf'+
'INNERJOIN CapitalInfoFactTab ASp '+
'ONf.[key]=p.infoID'
EXEC[USP_GetFrontDataList_ByFullIndex]@TableViewQueryName,
@SearchKeyword,@SelectStr,@Criteria,@Sort,@FristTopNum,
@PageOUTPUT,@CurrentPageRow,@TotalCountOUTPUT
ENDELSEBEGIN
EXECdbo.GetFrontDataList'dbo.ProjectInfoFactTab','InfoID',
@SelectStr,@Criteria,@Sort,0,@Pageoutput,@CurrentPageRow,
@TotalCountoutput
END
SELECT @endtime=getdate（）
SELECT @Totaltimes =DATEdiff（Ms,@starttime,@endtime）
RETURN
2）建立通用分页存过程[USP_GetFrontDataList_ByFullIndex]。
由于性能考虑，返回给前台页面需要网站数据库端即完成分页。
CREATEPROCEDURE[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 BIGINTOUTPUT --通过该查询条件，返回的查询记录的总页数
）
ASSETNOCOUNTONifcharindex（';',@Criteria）>0orcharindex（'--',@Criteria）>0orcharindex（'/*',@Criteria）>0or
charindex（'*/',@Criteria）>0orcharindex（'syscolumns',@Criteria）>0 orcharindex（'sysfiles',@Criteria）>0
orcharindex（'char（124）',@Criteria）>0 orcharindex（'1=1',@Criteria）>0
RETURN
DECLARE@TotalStr nVarchar（4000）
DECLARE@Str nVarchar（4000）
DECLARE@TopRowNumbigint
IF @SelectStrISNULLAND
RTRIM（LTRIM（@Criteria））=''
SET@SelectStr='*'
IF@FristTopNumISNULLAND@FristTopNum<0
BEGIN
SET@FristTopNum=0
END
ELSEIF@FristTopNum>@CurrentPageRow
BEGIN
SET@FristTopNum=@CurrentPageRow
ENDIF @CurrentPageRow>0
BEGIN
IF@CriteriaISNOTNULLAND
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'@TotalCountbigintoutput',@TotalCountoutput
SET@TotalCount=@TotalCount+isnull（@FristTopNum ,0）
DECLARE@TotalPagebigint
SET@TotalPage=@TotalCount/@CurrentPageRow
IF@TotalCount%@CurrentPageRow>0
BEGIN
SET@TotalPage=@TotalPage+1
END
IF@Page<=0
BEGIN
SET@Page=1
END
IF@TotalPage>0AND
@Page>@TotalPage
BEGIN
SET@Page=@TotalPage
END--组织查询语句
SET @Str= 'SELECT '+ @SelectStr+'FROM（'+ @TableViewQueryName +'）TWHERE T.SerialNumber>'+
cast（（@Page-1） asvarchar（10））+'*'+cast（@CurrentPageRowasvarchar（10））+
升级会员 