【SqlServer】管理全文索引（FULL TEXT INDEX）

HDWK發表於2021-04-06
SQLServer索引Index

Sql Server中的全文索引（下面統一使用FULLTEXT INDEX來表示全文索引），是一種特定語言搜尋索引功能。它和LIKE的不一樣，LIKE主要是根據搜尋模板搜尋資料，它的效率比FULLTEXT INDEX要低。在幾百萬的字串中，LIKE需要花幾分鐘才能返回的結果，FULLTEXT INDEX可能只需要幾秒鐘。

FULLTEXT INDEX功能是Sql Server的可選項。你可以通過 SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') 命令，來檢查你是否安裝了FULLTEXT INDEX。

 在開始介紹FULLTEXT INDEX之前，還需要介紹幾個重要的相關概念。它們分別是catalog, fulltext index fragment,  

 

Catalog: 在建立FULLTEXT INDEX之前，都需要先建立一個CATALOG， CATALOG是一個虛擬容器物件，它就是用來儲存FULLTEXT Index的，一個CATALOG可以關聯多個FULLTEXT Index索引。CATALOG不屬於任何檔案組。

FULLTEXT Index Fragments:  通常一個FULLTEXT索引都是由多個內部表組成的，這些內部表就被稱為Fragments。當使用者更新表中的資料後，資料庫會對改變的部分自動建立一個Fragment物件（前提是在這個表上建立了FULLTEXT INDEX，並且設定了自動跟蹤改變）。

你可通過sys.fulltext_index_fragments表來查詢所有fragments記錄：

SELECT * FROM sys.fulltext_index_fragments;

輸出：

【SqlServer】管理全文索引（FULL TEXT INDEX）

通過上面的輸出結果，可以看出一個表有了兩個fragment物件。如果像這樣的fragment物件越來越多的話，是會影響FULTEXT INDEX的查詢效率的。為了減少Fragment的數量，可以使用ALTER命令將所有的Fragment整合到一起。

ALTER FULLTEXT CATALOG [your-catalog-name] REORGANIZE;

【SqlServer】管理全文索引（FULL TEXT INDEX）

 

1. 建立和刪除全文索引

建立測試表：

create table Text_Test_Table(
    id int not null IDENTITY(1,1) PRIMARY KEY,
    txt nvarchar(1000) null
);

建立CATALOG:

CREATE FULLTEXT CATALOG my_catalog;

建立全文索引：

CREATE FULLTEXT INDEX ON Text_Test_Table
(  
    txt                         --Full-text index column name     
    Language 2057               --2057 is the LCID for British English  
)  
KEY INDEX PK__Text_Tes__3213E83F0F8DA0F2 ON my_catalog   --Unique index  
WITH CHANGE_TRACKING AUTO                                --Population type;  
GO

上面的表中指定了全文索引的欄位為txt，同時也指定了它的語言為英式英語。你也可以指定為其它語言，在SQL SERVER中用 select * from sys.fulltext_languages; 可以查詢所有支援的語言編號。為欄位指定正確的語言編號是必需的，因為不同的語言編號會使用不同的單詞分割器，不同的單詞分割器會分割出不同的關鍵詞列表，當查詢的時候就需要匹配關鍵詞列表中的資料。

建立全文索引，需要當前表至少包含一個唯一索引（unique index）。上面案例中， PK__Text_Tes__3213E83F0F8DA0F2  是主鍵，滿足唯一性的要求，因此可以用來建立全文索引。

CHANGE_TRACKING AUTO：是指當表中資料有變化時，會自動更新全文索引中的記錄。（除了AUTO，還可以為MANUAL值，表示需要手動去更新全文索引的記錄）。

 

在建立建立全文索引時，為什麼需要指定一個 Unique 索引？

其實這個比較好理解，可以把全文索引理解為一張 關鍵詞與資料表的關係對照表。只有資料表上有一個Unique索引時，全文索引才可以唯一地關聯關鍵詞與資料表之間的關係。

有點繞？直接上程式碼！

--插入測試資料
insert into Text_Test_Table values('She''s great fun, but she''s a few sandwiches short of a picnic.');
insert into Text_Test_Table values('She''s funny, but she only eat a few sandwiches.');

--查詢資料表
select * from Text_Test_Table;

--查詢全文索引關鍵詞對照關係，
--資料庫為Test,資料表為Text_Test_Table
SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id('Test'), object_id('dbo.Text_Test_Table'))

效果對照圖：

索引表中的document_id對應的是Text_Test_Table表中的id欄位。display_term是關鍵詞。occurence_count是關鍵詞的出現次數。document_id就是對應了資料表中唯一約束資料的編號，當唯一約束作用在數字欄位上時，約束欄位的值就會和document_id一樣。如果唯一約束作用在字元欄位上的話，那麼document_id將會是對應資料的編號。 

2. 使用全文索引查詢資料

 

2.1 匹配查詢含有所有關鍵詞的語句（必需包含所有關鍵詞）

 如果要完全匹配查詢的關鍵字，那麼可以用contains函式。

語法：

CONTAINS(欄位，關鍵詞)

關鍵詞需要用冒號""括起來，多個關鍵詞用空格隔開。格式："關鍵詞1 關鍵詞2 關鍵詞3..."。contains中的關鍵詞就相當於AND關鍵詞，這些關鍵詞並不需要連在一起，但是需要所有的關鍵詞都存在。

案例：

-- 查詢Text_Test_Table表中txt欄位包含 a 和 few 和 sandwiches 的關鍵詞的所有資料
declare @terms nvarchar(1000) = '"a few sandwiches"';
select * from Text_Test_Table where contains(txt, @terms);

-- 可以查詢出:
-- she has a few sandwiches
-- a few lemon and sandwiches
-- a man and few person both have sandwiches

2.2 匹配查詢含有關鍵詞的語句（只要含有一個關鍵詞就行了）

 如果要包含所有的關鍵詞，那麼應使用FREETEXT函式。

語法：

FREETEXT(欄位, 關鍵詞)

關鍵詞需要用冒號""括起來，多個關鍵詞用空格隔開。格式："關鍵詞1 關鍵詞2 關鍵詞3..."。freetext中的關鍵詞就相當於OR關聯詞，這些關鍵詞並不需要連在一起，只要有一個關鍵詞存在資料就會被選取。

-- 查詢Text_Test_Table表中txt欄位包含 a 或 few 或 sandwiches 的關鍵詞的所有資料
declare @terms nvarchar(1000) = '"a few sandwiches"';
select * from Text_Test_Table where freetext(txt, @terms);

-- 可以查詢出:
-- she has a few sandwiches
-- sandwiches is good
-- There are few apples

 

注意：

Freetext函式還可以匹配關鍵詞的變體，比如通過關鍵詞catch匹配相應的變體caught, catching, 和 catches 等。但Contains函式想要查詢變體，就必需使用FORMSOF語句。請移步FORMSOF Predicate獲取更詳細的資訊。

 

2.3 案例：查詢有不同匹配精確度的多個欄位

有一張products表，其中有id,name和description欄位, id是主鍵。name 和 description是nvarchar欄位型別。現在要查詢Products中的資料，name必須要包含所有的查詢關鍵字，description至少需要匹配一個提供的關鍵詞。

create table Products(
    id int not null IDENTITY(1,1) PRIMARY KEY,
    [name] nvarchar(1000) null,
    [description] nvarchar(1000) null
);

CREATE FULLTEXT INDEX ON Products
(  
    [name]  Language 2057,
    [description] Language 2057
)  
KEY INDEX PK__Products__3213E83F67020225 ON my_catalog   --Unique index  
WITH CHANGE_TRACKING AUTO                                --Population type;  
GO  

insert into Products values('Mouse Anti-Cattle FOXP3','This is Mouse Anti-Cattle FOXP3 introduction');
insert into Products values('Rabbit Anti-Mouse KRT13','This is Rabbit Anti-Mouse KRT13 introduction');
insert into Products values('Mouse Anti-C elegans FOXP3','This is Mouse Anti-C elegans FOXP3 introduction');

案例查詢SQL語句：

declare @terms nvarchar(255) = 'Mouse Anti';
declare @termsQuote nvarchar(255) = '"'+@terms+'"';
select * from Products  p
JOIN (
SELECT tp.[id],
   (case 
   when name like @terms+'%' then 4
   when name like '%'+@terms then 3
   when contains(name,@termsQuote) then 2
   else 1
   end) as 'sort'
  FROM [Products] tp
  where FREETEXT(description,@termsQuote) or Contains(name,@termsQuote)
  ) tp
ON p.id = tp.id 
order by tp.sort desc;

上面的查詢比使用傳統的LIKE查詢功能要增強不少。其實Contains和Freetext還有許多其它的用法，上面只是展示了一下基本用法，。上面的查詢語句僅僅是做了關鍵詞匹配，在完成接下來的章節學習後，將增加如下功能：

  • 結合SOUNDEX和全文索引，完成查詢語句糾錯機制。
  • 結合全文索引，完成搜素提示功能。

 

  • 1. 全文索引不能查詢同音詞。比如：不同通過rabbet查rabbit。
  • 2. 全文索引不能查詢同義詞。比如：不能通過rat查mouse。
  • 3. 全文索引缺乏糾錯機制，雖然可以通過freetext函式查詢關鍵詞的變體，但是不能糾正輸入錯誤的單詞。比如：可以通過catch查出相應的變體caught, catching, catches等，但如果我輸入的是catsh就匹配不到catch單詞。
  • 4. 全文索引缺少搜尋提示模組，全文索引僅僅是完成搜尋部分，關於搜尋提示卻需要開發者自己去架構實現相應的功能，這增加了開發者的工作量。 

上面的四點問題中，在討論完下一節《4. 如何檢視自己的資料都被分解成了那些關鍵字》後，筆者會解決上面的侷限性問題，以及提供一些合理的建議。

 

3. 如何檢視自己的資料都被分解成了那些關鍵詞

當我們建立全文索引時，首先需要給全文索引指定一個語言編號，然後全文索引會根據不同的語言編號使用不同的語言分割器，不同的分割器會分割出不同的關鍵詞列表。當使用全文索引查詢資料時，就會匹配分割得到的關鍵詞列表。

 檢視所有註冊了的詞語分割器列表

EXEC sp_help_fulltext_system_components 'wordbreaker';

檢視所有支援的語言列表

select * from sys.fulltext_languages

使用dm_fts_parser分解語句獲得關鍵詞列表

-- 分解語句：She catches a cat
SELECT * FROM sys.dm_fts_parser (
'"She catches a cat"',  --待分解的語句
1033,  --語言編號, 1033代表English，語言編號資訊可以通過檢視sys.fulltext_languages表獲取
0,  -- stoplist: 0表示使用預設的，NULL表示不使用。
0  -- accent_sensitivity，0表示insensitivity，1表示sensitivity
);

--檢視一個表被分解成的所有關鍵詞列表
SELECT * FROM sys.dm_fts_index_keywords_by_document(
db_id('Test'),  -- 資料庫物件ID
object_id('dbo.Text_Test_Table') -- 表物件ID
)

到這裡，我們已經知道如何分解語句獲取關鍵詞列表。接下來我們將繼續優化上面的查詢案例，第一點是如何結合SOUNDEX和全文索引建立關鍵詞搜尋糾錯機制，第二點是如何建立一個搜尋提示功能。

當使用者搜尋某關鍵詞語句時，如果有其它的關鍵詞和使用者搜尋的關鍵詞發音是一樣的，那麼就需要提示給使用者。就類似Google搜尋這樣的提供功能。

【SqlServer】管理全文索引（FULL TEXT INDEX）

當在Google搜尋 she catsh a cat的時候，Google會提示是否是指 she catch a cat.  類似這樣的糾錯提示，我們可以通過下面這個T-SQL實現一個簡單的版本。

declare @searchTerms nvarchar(1000) = N'"she catsh a cat"';
select * from (
select display_term,SOUNDEX(display_term) as st_soundindex from sys.dm_fts_parser(
    @searchTerms,
    (select lcid from sys.fulltext_languages where [name] = 'British English'),--語言需要和Products表使用的語言保持一致
    NULL,
    0
)) st
join (SELECT display_term,SOUNDEX(display_term) o_soundindex
FROM sys.dm_fts_index_keywords_by_document(db_id('Test'), object_id('dbo.Products'))) ot
on st.display_term != ot.display_term and st.st_soundindex = ot.o_soundindex;

效果圖：

【SqlServer】管理全文索引（FULL TEXT INDEX）

 通過上面的指令碼對比，我們就可以查詢出catsh和catch是同音的，然後程式就可以拿到這個結果給使用者做糾錯提示了。

 

關於搜尋提示這部分，非常遺憾，全文索引並未提供相關的功能。這部分需要程式開發自己來實現，這裡筆者談一談自己的實現思路：

  1. 建立一張表單獨維護搜尋提示詞（search_terms）
  2. 給search_terms表中插入一些預定義的搜尋提示詞
  3. 使用者自己搜尋的詞，也可以插入到search_terms表中。但是最終需要運維稽核後，才可以顯示給前端的使用者搜尋。
  4. 當使用者在搜尋框輸入的時，動態載入search_terms表中的稽核資料顯示到前端頁面。完成搜尋提示功能。

到這裡的，全文索引的主體知識點自己都講到了。全文索引就是一個特定語言的搜尋功能（Language-Specific Search）,所以給自己的資料指定語言型別是非常重要的。全文索引的功能較LIKE的功能有所提升，但全文索引也不是萬能的（我們也不能期望全文索引把所有功能都完成），比如：

  • 同義詞搜尋，比如搜rat, 可以顯示mouse 或 rodent.
  • 同義句的搜尋，比如搜尋rat is big, 可以顯示giant mouse monstor之類的。
  • 搜尋提示功能
  • ...

4. 參考文獻

1. sys.dm_fts_index_keywords_by_document (Transact-SQL)

2. Full-Text Search

3. Hands on Full-Text Search in SQL Server

相關文章