[開發環境] MSSQL 全文檢索

MSSQL 全文檢索是 SQL Server 的一個功能，相較於傳統的 SQL 查詢，全文檢索更適用於對文本內容進行關鍵字搜索和相關性匹配，這邊文章是我要紀錄如何安裝及使用，本文介紹以 MSSQL 2019 為例，在開始前，請先完成資料庫引擎服務和搜尋的全文檢索和語意擷取的功能。

建立全文檢索目錄

全文檢索目錄是個虛擬物件，它是用來當做全文檢索索引的容器，同時不屬於任何檔案群組，可使用介面或者語法來進行新增。

-- 新增全文檢索共用目錄
CREATE FULLTEXT CATALOG DefaultFullTextDoc WITH ACCENT_SENSITIVITY = ON
GO

建立全文檢索索引（full-text index）

全文檢索索引中包含單字以及這些單字在資料庫資料表中的哪些資料行內的相關位置資訊。全文檢索引擎會使用全文檢索索引中的資訊來編譯全文檢索查詢，以便快速地在資料表中搜尋特定字詞或字詞組合。

語法新增

-- 建立使用者全文檢索
CREATE FULLTEXT INDEX ON Base_Auth_User (Account, Name) KEY INDEX PK_Base_Auth_User ON DefaultFullTextDoc
GO

由資料表直接新增
由全文檢索屬性目錄新增

全文檢索常用查詢語法

-- 是否安裝全文檢索 
SELECT SERVERPROPERTY('IsFullTextInstalled');

-- 查詢某個目錄的擴展狀態(處理狀態)
/*
０：閒置（Idle ）
１：完整擴展進行中（Full population in progress）
２：已暫停（Paused）
３：調整執行速度（Throttled）
４：復原中（Recovering）
５：已關閉（Shutdown）
６：累加擴展進行中（ Incremental population in progress）
７：正在建立索引（Building index）
８：磁碟已滿， 已暫停。（ Disk is full. Paused.）
９：變更追蹤（Change tracking）
*/
Select FULLTEXTCATALOGPROPERTY('DefaultFullTextDoc', 'PopulateStatus')

-- 查詢各資料表全文檢索的擴展處理狀態
SELECT DB_NAME(database_id) AS 'Database Name',
	database_id AS 'Db Id',
	OBJECT_NAME(table_id) AS 'Table Name',
	table_id  AS 'Table Id',
	population_type_description AS 'Population Desc.',
	status_description AS 'Status Desc.',
	completion_type_description AS 'Completion Desc.',
	start_time
FROM sys.dm_fts_index_population

實測範例

使用數據量 164,532 筆的資料表進行比較，實際上該查詢條件應該只會查出一筆資料，資料夾結構如下圖，底下針對傳統 LIKE 和全文檢索進行比較。

使用數據量 164,532 筆的資料表進行比較，實際上該查詢條件應該只會查出一筆資料，資料夾結構如下圖，底下針對傳統 LIKE 和全文檢索進行比較。
```
-- 使用 Like 查詢
Select count(*) From IT_Wiki where SimpleContent like '%AAQ%'
-- 使用全文檢索
Select count(*) From IT_Wiki where CONTAINS(SimpleContent , 'AAQ')  
```
查詢速度比較。
執行計畫比較。
須注意，全文檢索一旦加入預設會直接進行，若有大資料表要進行的，建議不要在巔峰時刻進行，避免引響效能。

關於斷詞的說明，可以使用底下的語法來判斷全文檢索是如何斷詞的，應該可以發現該查詢條件是與 Like 的查詢方式不一樣的，特別是英文必須要有符號區隔才可以呈現，中文的一些連接詞可能也無法正確地被查詢，所以也可能會導致某些東西可能查不出來，但這有特別解法，與比起能快速的回應，這點東西我認為是可以被犧牲的。

/*
sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity)
參數說明
   query_string：想要查看斷字的字串
   lcid：地區設定識別碼 (LCID)。 1028 台灣、1033 美國
   stoplist_id：停用字詞表的識別碼。如果指定了 0，就會使用系統 STOPLIST。
   accent_sensitivity：控制全文檢索搜尋是否區分變音符號。0不區分，1區分。
*/
Select * From sys.dm_fts_parser ('Lawrence_Shen', 1028, 0, 0) 
Select * From sys.dm_fts_parser ('LawrenceShen', 1028, 0, 0)  
Select * From sys.dm_fts_parser ('走在馬路上看到一台車', 1028, 0, 0)

檢視表資(View)支援全文檢索

若要在 View 支援全文檢索，則必須要滿足必要條件，必須有唯一索引鍵，建立完成後，即可使用與資料表相同的設定來進行，底下是在 View 當中建立索引的參考語法。

CREATE VIEW [dbo].[View_IT_Wiki] WITH SCHEMABINDING 
	AS 
Select Id, Title, SimpleContent, Tags, Cix
From [dbo].[View_IT_Wiki]
GO

CREATE UNIQUE CLUSTERED INDEX IX_View_IT_Wiki ON [dbo].[View_IT_Wiki]([Cix] ASC)
GO

參考網站

VITO の學習筆記 - 使用全文檢索

[SQL SERVER]提高SQL SERVER全文檢索搜尋準確度

Entity Framework 中使用SQL Server全文索引（Full Text Search）

搜尋此網誌

Lawrence Blog