使用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