【SqlServer】管理全文索引(FULL TEXT INDEX)

HDWK發表於2021-04-06

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

相關文章