使用sql server2005全文检索

SQL Server 2005的全文检索采用类似Lucece的技术, 为文本检索做index, 尤其适合大文本字段的检索, 性能比Lucece差一些. 著名的stackoverflow网站也使用过SQL server 2005 Full text search,  应该能满足多数性能要求.

 

==============================

安装并启用Full Text Search功能

============

完整安装了SQL Server 2005 企业版后, 在SQL server configure manager中启动全文检索（Fulltext search）服务,却报关联服务不存在. 原因是: 在注册表中, 将 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesql 下的 DependOnService 的键值, 指明要依赖一个NTLMSSP服务, 而这个服务不存在, 简单地重命名这个 DependOnService 为其他名字, 然后重启机器, 就可以启动全文检索服务了. 

 

某个DB如要开启Full Text功能, 需要在该DB的File 选项中, 选中full text复选框. 

 

 

==============================

全文检索某个字段

==============================

入门文章

http://www.sql-server-performance.com/2010/full-text-search-2008/2/

进阶文章

https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

    

有4个全文检索的更新方式, 1个全量更新方式, 3个增量更新方式. 分别是:

 1是全量更新full text 索引, 需要下SQL命令触发 

 2是基于时间戳字段的增量更新机制, 需要下SQL命令触发 

 3是SQL Server自动检查哪些记录发生变化, 并自动更新full text索引, 完全不需要下SQL命令触发.

 4是SQL Server自动检查哪些记录发生变化, 但更新full text索引需要我们通过SQL命令来启动.

 

--全量刷新full text索引

ALTER FULLTEXT INDEX ON Production.Document START FULL POPULATION

 

--基于时间戳的增量刷新full text索引, 表必须有一个时间戳字段

ALTER FULLTEXT INDEX ON Production.Document START INCREMENTAL POPULATION

 

--自动检查基表数据的变化, 并自动更新full text索引

ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO

 

--手动检测基表的变化, 然后我们再通过第2个SQL来手动更新full text索引

ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING Manual

ALTER FULLTEXT INDEX ON Production.Document START UPDATE POPULATION

要注意的是, 通过SQL命令刷新full Text索引, SQL很快就执行完毕, 但并不意味着indexing过程已经结束了, 需要通过函数FULLTEXTCATALOGPROPERTY()来获取indexing的刷新状态, 另外该函数还可以查询index的age和占用空间等属性. 

 

根据stackoverflow上的代码, 可以方便地获取更新状态, 我做了丁点改进, 修改后的代码放在文章的最后.  http://stackoverflow.com/questions/2727911/how-can-i-know-when-sql-full-text-index-population-is-finished

 

表的设计

表要支持全文检索, 这个表必须要有一个 "唯一的" 针对 "单列的" "非空" 索引，设为聚簇索引, (最好有一个单列的主键, 设为聚簇索引, 比如表有如下的约束语句. 

CONSTRAINT [PK_ProductDocs_DocID] PRIMARY KEY CLUSTERED (DocID ASC)

另外, 推荐增加一个timestamp字段, 以支持基于时间戳的更新方式, (sqlserver中一个表只允许有一个时间戳字段，和子增量字段一样, 插入记录时该字段会被自动赋值的)

被全文检索的字段最好采用NVARCHAR或NVARCHAR(MAX), 支持长文本, 比text类型更高效. 

 

==============================

查询

==============================

使用CONTAINS(), FREETEXT(),CONTAINSTABLE()进行全文查找. 

select [productid],[Name],[Description] from Product where contains(Name,'"IBM" and "thinkpad" and "A21"')

--select c1 from ftstable where contains(*,'"apples" and "oranges"')

 

SQL Server 2005 全文搜索__收藏 

http://blog.csdn.net/leamonjxl/article/details/7616120

 

 

 

==============================

性能方面的考虑

==============================

1. 全文检索是一个计算密集应用, 操作系统需要64bit, 多个CPU, 内存需要多一些

2. 使用SQL Server 企业版, 能充分发挥多CPU的计算优势

3. 数据库recovery mode采用simple, 减小数据库本身的磁盘IO

4. 如果全文检索表本身是append表, 我觉得全文检索更新机制采用基于时间戳的增量, 运行速度会更快一些, 需要实测. 

5. 定期 shrink 全文检索的catalog, 方法是定期将全文检索表中冷数据挪到其他表中, 然后再用全量方式刷新缩小后的基表全文索引

6. 表设计方面: 如采用基于时间戳的增量更新方式, 为该时间戳字段设索引, 唯一主键采用bigint字段, 并设为聚集索引.

7. 全文检索表如果比较大, 采用分区表, 将访问该表的io分散在多个磁盘上. 如果表还是太大的话, 将该表作切分, 放到不同机器的SQL server上, 检索的时候, 采用linked server的方式将这些表再组合在一起.

8. 为Full text catalog设置一个专门的数据库文件组, 不是用默认的primary文件组, 分离磁盘IO.

9. 为每个全文索引分配单独的一个catalog,这样好查询每个索引占用空间和更新状态

10. 更多设置, 需要看SQL Server 2005 Full-Text Search: Internals and Enhancements 文档. 中文版http://msdn.microsoft.com/library/ms142560

SQLCat的 SQL Server 2005 full text Best Practices Article 

http://technet.microsoft.com/library/Cc917695

 

================================

推荐使用SQL 2005 SP4

================================

经我实测, SQL 2005在全文检索方面要比SQL 2012做的好, 包括性能方面和速度稳定性方面. 未对比测试SQL 2008, 但考虑到SQL 2008 之后, 创建 catalog 不能指定 filegroup, 我觉得对于io分离很不利, 所以推荐使用SQL 2005。

实测(配置:CPU 2*8 core 33GHz, Memory 4GB), SQL 2005为400M的消息文件建full text 索引, 需要73秒, 性能还不错.  增加消息文件的大小, 耗时和消息文件大小基本按这个比例关系变化.

 

--获取全文检索catalog的状态

DECLARE @CatalogName VARCHAR(MAX)

SET     @CatalogName = 'your_catalog_name'

SELECT

    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated

    , FULLTEXTCATALOGPROPERTY(@CatalogName,'IndexSize') as IndexSize_MB

    , FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus') as PopulateStatusValue

    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')  

        WHEN 0 THEN 'Idle'

        WHEN 1 THEN 'Full Population In Progress'

        WHEN 2 THEN 'Paused'

        WHEN 3 THEN 'Throttled'

        WHEN 4 THEN 'Recovering'

        WHEN 5 THEN 'Shutdown'

        WHEN 6 THEN 'Incremental Population In Progress'

        WHEN 7 THEN 'Building Index'

        WHEN 8 THEN 'Disk Full.  Paused'

        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus

FROM sys.fulltext_catalogs AS cat