全文索引架構組件
- 全文目錄：文件系统級容器，存儲倒排索引文件 (`.fti` 等)，一個數據庫可有多個目錄。
- 全文索引：建立在表的單列/多列上，要求表有**唯一非空單列索引** (通常為 PK)。
- 斷詞器/詞幹提取器：按語言 (LCID) 分詞、提取詞根，支持中文/英文/日文等 50+ 語言。
- 停用詞表：過濾高頻無意義詞 (的/和/is/the)，可自定義。
- 同義詞表：擴展查詢匹配 (計算機 ⇄ 電腦 ⇄ PC)。
系統存儲過程創建全流程 (兼容 2005/2008/R2/2012+)
USE pubs; -- 示例數據庫
GO
-- 1. 啟用數據庫全文功能
IF DATABASEPROPERTY('pubs', 'IsFulltextEnabled') = 0
EXEC sp_fulltext_database 'enable';
GO
-- 2. 創建全文目錄
EXEC sp_fulltext_catalog 'FT_Pubs', 'create';
GO
-- 3. 為表註冊全文索引 (需指定唯一鍵索引名)
EXEC sp_fulltext_table 'titles', 'create', 'FT_Pubs', 'UPKCL_titleidind'; -- PK 索引名
GO
-- 4. 添加全文索引列 (指定語言 LCID: 1033=英文, 2052=中文簡體)
EXEC sp_fulltext_column 'titles', 'title', 'add', 1033;
EXEC sp_fulltext_column 'titles', 'notes', 'add', 1033;
GO
-- 5. 激活表全文索引
EXEC sp_fulltext_table 'titles', 'activate';
GO
-- 6. 啟動完全填充 (首次/重建)
EXEC sp_fulltext_catalog 'FT_Pubs', 'start_full';
GO
-- 7. 等待填充完成 (生產環境建議異步作業輪詢)
WHILE FULLTEXTCATALOGPROPERTY('FT_Pubs', 'PopulateStatus') <> 0
BEGIN
WAITFOR DELAY '00:00:30';
END
GO
增量/手動/排程填充策略
| 模式 | 命令 | 適用場景 | 開銷 |
|---|
| 完全填充 | `start_full` | 初始建立、架構變更後重建 | 高 (全表掃描) |
| 增量填充 | `start_incremental` | 有 `TIMESTAMP` 列、頻繁 DML、近實時 | 低 (僅變更行) |
| 更新填充 | `start_update_population` | 無 `TIMESTAMP` 列、定時批量更新 | 中 |
| 手動填充 | `start_full` + 排程作業 | 完全可控窗口、離峰執行 | 可控 |
最佳實踐：表添加 `ROWVERSION` 列 → 啟用增量填充 → Agent 作業每 5-15 分鐘執行 `sp_fulltext_catalog 'FT_Pubs', 'start_incremental'`。
核心查詢函數與實戰模式
| 函數 | 語法 | 特點 | 典型場景 |
|---|
| CONTAINS | `CONTAINS(column, '')` | 精確/前綴/邏輯/鄰近/加權 | 精準檢索、後台管理、法律/醫療合規 |
| FREETEXT | `FREETEXT(column, 'freetext_string')` | 自然語言、詞幹擴展、同義詞、排序相關性 | 用戶面向搜索框、類 Google 體驗 |
| CONTAINSTABLE | `CONTAINSTABLE(table, column, condition)` | 返回 `KEY, RANK`，可 JOIN 排序 | 相關性排序、分頁、多表聯合檢索 |
| FREETEXTTABLE | `FREETEXTTABLE(table, column, freetext)` | 返回 `KEY, RANK`，自然語言相關性 | 智能推薦、模糊匹配 |
CONTAINS 進階語法速查
-- 精確短語
CONTAINS(notes, '"database administration"')
-- 前綴匹配 (單詞開頭)
CONTAINS(title, '"comp*"') -- computer, company, compatible...
-- 邏輯組合
CONTAINS(notes, 'database AND (administration OR management)')
-- 鄰近搜索 (SQL 2008+)
CONTAINS(notes, 'NEAR((database, server), 5, TRUE)') -- 5 詞內、順序無關
-- 加權排序 (CONTAINSTABLE)
SELECT t.title, k.RANK
FROM titles t
JOIN CONTAINSTABLE(titles, (title, notes), 'ISABOUT (database WEIGHT(0.8), server WEIGHT(0.6))') k
ON t.title_id = k.[KEY]
ORDER BY k.RANK DESC;
性能優化與運維要點
- 全文目錄放置獨立磁盤/SSD：I/O 隔離，避免影響數據文件。
- 填充排程錯峰：業務低峰 (02:00-04:00) 執行完全填充，增量填充間隔 ≥ 5 分鐘。
- 監控填充狀態：`FULLTEXTCATALOGPROPERTY(cat, 'PopulateStatus')` / `PopulateCompletionAge` / `ItemCount` / `IndexSize` 寫入巡檢表。
- 碎片整理：`ALTER FULLTEXT CATALOG FT_Pubs REORGANIZE` (合併碎片段) / `REBUILD` (重建)，月度執行。
- 備份包含全文目錄：`BACKUP DATABASE ... FILEGROUP = 'FT_Pubs'` (2005/2008 需單獨備份目錄文件夾，2012+ 支持文件組備份)。
- 語言選擇：中文內容**必須**指定 `2052` (簡體) 或 `1028` (繁體)，否則按英文斷詞導致索引無效。
遷移到現代架構建議
- SQL Server 2008 R2 後全文搜索整合進數據庫引擎 (不再依賴 MSFTESQL 服務)，性能穩定性大幅提升。
- 複雜語義搜索、向量檢索、多模態檢索建議引入 **Elasticsearch / OpenSearch / Azure AI Search**，SQL Server 全文作為結構化+全文混合查詢的補充。
- 容器化部署時，全文目錄掛載持久化卷，確保填充狀態不隨容器重啟丟失。
