全文檢索 vs 傳統 LIKE 查詢
| 特性 | LIKE '%關鍵字%' | 全文檢索 |
|---|
| 效能 |
全表掃描，大量資料不可用 |
倒排索引，毫秒級回應 |
| 語言感知 |
無 |
斷詞器、字幹擴展、同義詞、鄰近搜尋 |
| 排序 |
無關聯性排名 |
RANK 值 0-1000 依相關性排序 |
| 支援資料類型 |
CHAR/VARCHAR/TEXT |
CHAR/VARCHAR/VARBINARY/TEXT/IMAGE/XML |
核心概念
- 全文目錄：儲存全文索引的容器，對應檔案系統目錄
- 全文索引：建立在單一資料表的單一/多欄位上，每張表最多一個
- 噪音詞：常見無語意詞 (the, is, 的, 是) 依語言過濾，不建立索引
- 斷詞器：依語言規則切分文字 (英文空格、中文雙字/詞典)
- 字幹擴展：run → runs/running/ran
- 同義詞庫：自定義同義詞對 (SQL Server → SQL Server 2000)
查詢語法：CONTAINS / FREETEXT
CONTAINS：精確語法控制
-- 簡單詞彙
SELECT * FROM docs WHERE CONTAINS(content, 'SQL Server');
-- 片語 (需雙引號)
SELECT * FROM docs WHERE CONTAINS(content, '"SQL Server"');
-- 近似/鄰近
SELECT * FROM docs WHERE CONTAINS(content, 'SQL NEAR Server');
SELECT * FROM docs WHERE CONTAINS(content, 'SQL NEAR(Server, 5)'); -- 距離 5 詞內
-- 前綴/字幹
SELECT * FROM docs WHERE CONTAINS(content, 'comput*'); -- computer, computing...
-- 邏輯組合
SELECT * FROM docs WHERE CONTAINS(content, 'SQL AND (Server OR Database)');
SELECT * FROM docs WHERE CONTAINS(content, 'SQL AND NOT Oracle');
-- 加權排序
SELECT TOP 10 *, RANK FROM
FREETEXTTABLE(docs, content, 'SQL Server performance tuning')
ORDER BY RANK DESC;
FREETEXT：自然語言查詢
-- 相當於將輸入拆解為關鍵字、字幹、同義詞自動 OR 組合
SELECT * FROM docs WHERE FREETEXT(content, '如何調教 SQL Server 效能');
整合關聯式查詢：無縫結合
SELECT
d.DocTitle, d.DocAuthor, d.Size,
w.Citizenship
FROM doc_collection d
JOIN writers w ON d.DocAuthor = w.writer_name
WHERE CONTAINS(d.DocText, '"SQL Server" NEAR text');
單一查詢同時具備結構化篩選 (JOIN、WHERE) 與非結構化全文搜尋。
架構組件
| 組件 | 角色 | 進程 |
|---|
| SQL Server 引擎 |
解析 CONTAINS/FREETEXT、產生查詢計畫、回傳結果 |
sqlservr.exe |
| 全文引擎 |
索引建立、斷詞、噪音詞過濾、查詢轉換 |
msftesql.exe (SQL 7.0/2000) |
| 篩選器 |
解析 Office/PDF/HTML/XML 等格式提取純文字 |
IFilter COM 介面 |
| 斷詞器/字幹器 |
語言特定切詞與還原 |
DLL (如 chsbrkr.dll 簡體中文) |
管理操作：預存程序 vs 企業管理器
啟用全文檢索 (資料庫層級)
EXEC sp_fulltext_database 'enable';
建立全文目錄
EXEC sp_fulltext_catalog 'ft_catalog', 'create';
建立全文索引
EXEC sp_fulltext_table 'docs', 'create', 'ft_catalog', 'PK_docs';
EXEC sp_fulltext_column 'docs', 'DocText', 'add';
EXEC sp_fulltext_column 'docs', 'DocTitle', 'add';
EXEC sp_fulltext_table 'docs', 'activate';
填充策略
- 完整填充：重建整個索引，資料量大時耗時
- 增量填充：僅處理變更列 (需
timestamp 欄位)
- 自動填充：即時/背景追蹤變更 (SQL 2000+)
EXEC sp_fulltext_catalog 'ft_catalog', 'start_full';
EXEC sp_fulltext_catalog 'ft_catalog', 'start_incremental';
進階調優建議
- 選擇性建立索引：僅對真正需要全文搜尋的欄位建立
- 噪音詞自訂：編輯
noiseENG.txt (或對應語言檔) 移除業務關鍵字
- 同義詞庫維護：定期審視業務術語同義詞
- 索引碎片整理：
sp_fulltext_catalog 'catalog', 'rebuild'
- 硬體隔離：全文目錄放置於獨立磁碟/SSD，減少與資料檔 IO 爭用
- 記憶體配置：
sp_configure 'full-text search memory', 最大值
現代演進：SQL Server 2008+ 全文檢索整合
- 全文引擎整合進
sqlservr.exe，不再獨立進程
- 支援
FILESTREAM/FileTable 檔案流全文索引
- 語意搜尋：關鍵片語提取、文件相似度
- 語言支援擴展至 100+，含繁體中文斷詞器
- 管理介面統合至 SSMS / T-SQL (
CREATE FULLTEXT INDEX)
