InnoDB & MySQL 全文索引 學習筆記

最近在學習MySQL全文索引的知識，基本搞清了功能以及使用方式，整理了相關資料分享出來一起學習進步哈

目錄

1. 反向索引

2. InnoDB全文檢索

3. MySQL全文檢索

3.1 Natural Language （自然語言模式）

3.2 Boolean(布爾模式)

3.3 Query Expansion(查詢擴充)

4. ngram 分詞器

5. 參考資料

全文檢索(Full-Text Search)是将存儲于資料庫中的整本書或整篇文章中的任意内容資訊查找出來的技術。它可以根據需要獲得全文中有關章、節、段、句、詞等資訊，也可以進行各種統計和分析。

1. 反向索引

在老版本的MySQL資料庫中，InnoDB存儲引擎并不支援全文檢索技術，從InnoDB 1.2.x版本開始，InnoDB存儲引擎開始支援全文檢索，其支援MyISAM存儲引擎的全部功能，并且還支援其他的一些特性，在介紹其使用之前，先介紹下反向索引(invertd index)。

全文檢索通常使用反向索引(invertd index)來實作。反向索引同B+樹索引一樣，也是一種索引結構。它在輔助表(auxiliary table)中存儲了單詞與單詞自身在一個或多個文檔中所在位置之間的映。這通常利用關聯數組實作，其擁有兩種表現形式：

inverted file index, 其表現形式為 {單詞，單詞所在文檔的ID}

full inverted index, 其表現形式為 {單詞，(單詞所在文檔的ID，在具體文檔中的位置)}，如下圖這個例子，表格中存儲表t的内容

全文檢索表 t

DocumentId	Text	DocumentId	Text
1	Pease porridge hot, pease porridge cold	4	Some like it hot, some like it cold
2	Pease porridge in the pot	5	Some like it in the pot
3	Nine days old	6	Nine days old

DocumentId 表示進行全文檢索文檔的Id，Text表示存儲的内容，使用者需要對存儲的這些文檔内容進行全文檢索。例如，查找出現過Some 單詞的文檔Id， 又或者查找單個文檔中出現過兩個Some單詞的文檔Id，等等。

對于 inverted file index 的關聯數組，其存儲的内容如下所示。

inverted file index 的關聯數組

Number	Text	Documents	Number	Text	Documents
1	cold	1,4	8	old	3,6
2	days	3,6	9	pease	1,2
3	hot	1,4	10	porridge	1,2
4	in	2,5	11	pot	2,5
5	it	4,5	12	some	4,5
6	like	4,5	13	the	2,5
7	nine	3,6

可以看到單詞cold存在于文檔1和4中，單詞 days 存在于文檔 3 和 6 中。之後再要進行全文查詢就簡單了。可以直接根據 Documents 得到包含查詢關鍵字的文檔。對于 inverted file index，其僅存取文檔 Id，而 full inverted index 存儲的是一對 (pair)，即(DocumentId, Position)， 是以其存儲的反向索引如下表所示：

full inverted index 的關聯數組

Number	Text	Documents	Number	Text	Documents
1	cold	(1:6)，(4:8)	8	old	(3:3)，(6:3)
2	days	(3:2)，(6:2)	9	pease	(1:1,4)，(2:1)
3	hot	(1:3)，(4:4)	10	porridge	(1:2,5)，(2:2)
4	in	(2:3)，(5:4)	11	pot	(2:5)，(5:6)
5	it	(4:3, 7)，(5:3)	12	some	(4:1,5)，(5:1)
6	like	(4:2,6)，(5:2)	13	the	(2:4)，(5:5)
7	nine	(3:!)，(6:1)

 full inverted index 還存儲了單詞所在的位置資訊，如 code 這個單詞出現在 (1:6)，即文檔 1 的第 6 個單詞為code。相比之下， full  inverted index 占用更多的空間，但是能更好地定位資料，并擴充一些其他的搜尋特性。

2. InnoDB全文檢索

InnoDB存儲引擎從 1.2.x 版本開始支援全文檢索的技術，其采用 full inverted index 的方式。在 InnoDB存儲引擎中，将 （DocumentId, Position）視為一個 "ilist"。是以在全文檢索的表中，有兩個列，一個是word字段，另一個是 ilist字段， 并且在word字段上設有索引。此外，由于 InnoDB 存儲引擎在 ilist 字段中存放了 Position 資訊，故可以進行 Proximity Search(鄰近檢索)，而MyISAM 存儲引擎不支援該特性，下文給出鄰近檢索的例子。

正如之前所說的那樣，反向索引需要将 word 字段存放到一張表中，這個表稱為 Auxiliary Table ( 輔助表 )。在 InnoDB 存儲引擎中，為了提高全文檢索的并行性能，共有 6 張 Auxiliary Table，目前每張表根據 word 的 Latin 編碼進行分區。

Auxiliary Table 是持久的表，存放于磁盤上。然而在 InnoDB 存儲引擎的全文索引中，還有另外一個重要的概念 FTS Index Cache( 全文檢索索引緩存表 )，其用來提高全文檢索的性能。

FTS Index Cache 是一個紅黑樹結構，其根據( word, ilist) 進行排序。這意味着插入的資料已經更新了對應的表，但是對全文索引的更新可能在分詞操作後還在 FTS Index Cache 中，Auxiliary Table 可能還沒有更新。InnoDB 存儲引擎會批量對 Auxiliary Table  進行更新，而不是每次插入後更新一次Auxiliary Table 。當對全文檢索進行查詢時，Auxiliary Table  首先會将在FTS Index Cache  中對應的word 字段合并到 Auxiliary Table 中，然後再進行查詢。這種merge 操作類似于 Insert Buffer 功能，不同的是 Insert Buffer 是一個持久的對象，并且其是一個B+ 樹的結構。然而 FTS Index Cache 的作用又和 Insert Buffer  類似，它提高了InnoDB 存儲引擎的性能，并且由于其根據紅黑樹排序後進行批量插入，其産生的 Auxiliary Table 相對較小。

Innodb 存儲引擎允許使用者檢視指定反向索引的Auxiliary Table中分詞的資訊，可以通過設定參數 innodb_ft_aux_table 來觀察反向索引的 Auxiliary Table。下面的SQL語句設定檢視 test 資料庫中 fts_a 表的Auxiliary Table：

SET GLOBAL innodb_ft_aux_table="test/fts_a";
           

在上述設定完成後，就可以通過查詢 information_schema 庫中的表 INNODB_FT_INDEX_TABLE 得到表 fts_a 中的分詞資訊。 

對于InnoDB 存儲引擎而言，其總是在事務送出時将分詞寫入到 FTS Index Cache，然後再通過批量更新寫入到磁盤。雖然 InnoDB 存儲引擎通過一種延時的、批量的寫入方式來提高資料庫的性能，但是上述操作僅在事務送出時發生。

當資料庫關閉時，在 FTS Index Cache 中的資料庫會同步到磁盤上的  Auxiliary Table 中。然而，如果當資料庫發生當機時，一些 FTS Index Cache 中的資料庫可能未被同步到磁盤上。那麼下次重新開機資料庫時，當使用者對表進行全文檢索( 查詢或者插入操作)時，InnoDB 存儲引擎會自動讀取未完成的文檔，然後進行分詞操作，再将分詞的結果放入到 FTS Index Cache 中。

參數 innodb_ft_cache_size 用來控制 FTS Index Cache 的大小，預設值為32M。當該緩存滿時，會将其中的（word，ilist）分詞資訊同步到磁盤的 Auxiliary Table中。增大該參數可以提高全文檢索的性能，但是在當機時，未同步到磁盤中的索引資訊可能需要更長的時間進行恢複。

FTS Document ID 是另外一個重要的概念。在 InnoDB 存儲引擎中，為了支援全文檢索，必須有一個列與 word進行映射，在InnoDB 中這個列被命名為 FTS_DOC_ID，其類型必須是 BIGINT UNSIGNED NOT NULL，并且 InnoDB 存儲引擎自動會在該列上加入一個名為 FTS_DOC_ID_INDEX 的 Unique Index。上述這些操作都由 InnoDB 存儲引擎自己完成，使用者也可以在建表時自動添加FTS_DOC_ID，以及相應的Unique Index。由于列名為FTS_DOC_ID 的列具有特殊意義，是以建立時必須注意相應的類型，否則MySQL 資料庫會抛出錯誤，如下圖

文檔中分詞的插入操作是在事務送出時完成，然而對于删除操作，其在事務送出時，不删除磁盤  Auxiliary Table 中的記錄，而隻是删除  FTS Index Cache 中的記錄，并将其儲存在 DELETED auxiliary table 中。在設定參數 innodb_ft_aux_table 後，使用者同樣可以通路 information_schema 庫下的表 INNODB_FT_DELETED 來觀察删除的 FTS Document ID。

由于文檔的DML 操作實際并不删除索引中的資料，相反還會在對應的DELETED 表中插入記錄，是以随着應用程式的允許，索引會變得非常大，即使索引中的有些資料已經被删除，查詢也不會選擇這類記錄。為此，InnoDB 存儲引擎提供了一種方式，允許使用者手動地将已經存儲的記錄從索引中徹底删除，該指令是 optimize table。因為 optimize table 還會進行一些其他的操作，如 Cardinality 的重新統計，若使用者希望僅對反向索引進行操作，那麼可以通過參數 innodb_optimize_fulltext_only進行設定，如：

set global innodb_optimize_fulltext_only=1;

optimize table fts_a;
           

若被删除的文檔非常多，那麼 optimize table 操作可能需要占用非常多的時間，這會影響影響應用程式的并發性，并極大地降低使用者的響應時間。使用者可以參數 innodb_ft_num_word_optimize 來限制每次實際删除的分詞數量。該參數的預設值為2000。

下面通過例子來觀察，首先通過如下代碼建立表 fts_a:

create table fts_a (
	FTS_DOC_ID bigint unsigned auto_increment not null,
	body text,
	primary key(FTS_DOC_ID));

insert into fts_a select null, 'Pease porridge in the pot';
insert into fts_a select null, 'Pease porridge hot, pease porridge cold';
insert into fts_a select null, 'Nine days old';
insert into fts_a select null, 'Some like it hot, some like it cold';
insert into fts_a select null, 'Some like it in the pot';
insert into fts_a select null, 'Nine days old';
insert into fts_a select null, 'I like code days';
create fulltext index idx_fts on fts_a(body);
           

上述代碼建立了表 fts_a，由于body字段是進行全文檢索的字段，是以建立一個類型為 FULLTEXT的索引。這裡首先導入資料，然後再進行反向索引的建立，這也是比較推薦的一張方式，建立完成後觀察表中的資料：

通過設定 innodb_ft_aux_table 來檢視分詞對應的資訊：

SET GLOBAL innodb_ft_aux_table="study/fts_a";

select * from information_schema.INNODB_FT_INDEX_TABLE;
           

可以看到每個word都對應了一個DOC_ID 和 POSITION。此外，還記錄了FIRST_DOC_ID、LAST_DOC_ID以及 DOC_COUNT，分别代表了該 word 第一次出現的文檔ID，最後一次出現的文檔ID，以及該word在多少個文檔中存在。若執行如下語句，會删除FTS_DOC_ID 為7 的文檔：

delete from fts_a where FTS_DOC_ID=7;
           

由于之前的介紹，InnoDB 存儲引擎并不會直接删除索引中對應的記錄，而是将删除的文檔ID插入到DELETED表，是以可以進行如下的查詢：

select * from information_schema.INNODB_FT_DELETED;
           

可以看到删除的文檔ID插入到了表 INNODB_FT_DELETED中，若使用者想要徹底删除反向索引中該文檔的分詞資訊，那麼可以運作如下的SQL語句：

set global innodb_optimize_fulltext_only=1;
optimize table fts_a;
select * from information_schema.INNODB_FT_BEING_DELETED;
           

通過上面的例子可看到，運作指令 optimize table 可将記錄進行徹底的删除，并且徹底删除的文檔ID會記錄到表 INNODB_FT_BEING_DELETE中，此外，由于7這個文檔ID已經被删除，是以不允許再次插入這個文檔ID，否則資料庫會抛出錯誤。

接下來介紹下 stopword 清單( 停用詞 )，其表示該清單中的word 不需要對其進行索引分詞操作。例如，對于the 這個單詞，由于其不具有具體的意義，是以将其視為 stopword。InnoDB 存儲引擎有一張預設的 stopword 清單，其在 information_schema下，表名為 INNODB_FT_DEFAULT_STOPWORD，預設有36個stopword。此外使用者也可以通過參數 innodb_ft_server_stopword_table 來自定義stopword 清單，如下：

// 檢視預設停用詞表
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

// 自定義停用詞表
create table user_stopword(
    value VARCHAR(30)
    ) ENGINE= INNODB;

set global innodb_ft_server_stopword_table = "test/user_stopword";
           

使用Innodb 存儲引擎的全文檢索還存在以下的限制：

1. 每張表隻能有一個全文檢索的索引
2. 由多列組合而成的全文檢索的索引列必須使用相同的字元集與排序規則。
3. 不支援沒有單詞界定符（delimiter）的語言，如中文、日語、韓語等(這一點可以通過自定義分詞器ngram來解決，下文有介紹)。

3. MySQL全文檢索

介紹完了InnoDB 的全文檢索，接下來看下MySQL的全文檢索。

MySQL資料庫支援全文檢索（Full-Text Search）的查詢，其文法為：

MATCH (col1, col2,...) AGAINST (expr [search_modifier])

search_modifier:
{
    IN NATURAL LANGUAGE MODE
  | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
  | IN BOOLEAN MODE
  | WITH QUERY EXPANSION 
}
           

MySQL 資料庫通過 MATCH()···AGAINST()文法支援全文檢索的查詢，MATCH 指定了需要被查詢的列，AGAINST指定了使用何種方法去進行查詢。下面将對各種查詢模式進行詳細的介紹。

3.1 Natural Language （自然語言模式）

全文檢索通過MATCH函數進行查詢，預設采用 Natural Language 模式，其表示查詢帶有指定word的文檔，對于上文建立的表fts_a，查詢body字段中帶有Pease 的文檔，若不使用全文索引技術，則允許使用如下語句：

select * from fts_a where like '%Pease%'
           

顯然該語句不能使用B+樹索引，若采用全文檢索技術，可以使用下面的SQL語句進行查詢：

select * from fts_a where match(body) against ('porridge' in natural language mode);
           

由于NATURAL LANGUAGE MODE 是預設的全文檢索查詢模式，是以可以省略查詢修飾符，即上述語句可以寫為：

select * from fts_a where match(body) against ('porridge');
           

在where條件中使用MATCH函數，查詢傳回的結果是根據相關性（Relevance）進行降序排序的，即相關性最高的結果放在第一位。相關性的值是一個非負的浮點數字，0表示沒有任何相關性。根據MySQL官方文檔可知，其相關性的計算依據以下四個條件：

1. word是否在文檔中出現。
2. word在文檔中出現的次數。
3. word在索引列中的數量。
4. 多少個文檔中包含該word。

對于上述查詢，由于 Porridge 在文檔2中出現了2次，因而具有更高的相關性，故第一個顯示。MySQL相關性的計算公式可以查詢官方文檔得到，這裡不再詳述。

使用者可以通過如下SQL語句檢視相關性：

select fts_doc_id, body, match(body) against ('Porridge' in natural language mode) as Relevance from fts_a;
           

 對于InnoDB 存儲引擎的全文檢索，還需要考慮一下的因素：

1. 查詢的word 在 stopword 列中，忽略該字元串的查詢。
2. 查詢的word 的字元長度是否在區間 [innodb_ft_min_token_size, innodb_ft_max_token_size] 中。

如果詞在stopword 中，則不對該詞進行查詢。參數 innodb_ft_min_token_size和 innodb_ft_max_token_size 控制InnoDB 存儲引擎查詢字元的長度，當長度小于innodb_ft_min_token_size 或者 大于innodb_ft_max_token_size 時，會忽略該詞的搜尋。在InnoDB 存儲引擎中，參數 innodb_ft_min_token_size 預設值為3，參數 innodb_ft_max_token_size 預設值為84。

3.2 Boolean(布爾模式)

MySQL資料庫允許使用 IN BOOLEAN MODE 修飾符來進行全文檢索。當使用該修飾符時，查詢字元串的前後字元會有特殊的含義，例如下面的語句要求查詢有字元串 Pease 但沒有 hot 的文檔，其中 + 和 - 分别表示這個單詞必須出現，或者一定不存在。

select * from fts_a where match(body) against ('+Pease -hot' in boolean mode)\G;
           

Boolean 全文檢索支援一下幾種操作符：

操作符	含義
+	表示該word必須存在
-	表示該word必須被排除
(空)	表示該word是可選的，但是如果出現，其相關性會更高
@distance	表示查詢的多個單詞之間的距離是否在distance之内，distance的機關是詞。這種全文檢索的查詢也稱為Proximity Search。如MATCH (body) AGAINST (' "Peace pot"@30 in boolean mode) 表示字元串Peace 和 pot 之間的距離需在30個詞以内。
>	表示出現該單詞時增加相關性
<	表示出現該單詞時降低相關性
~	表示允許出現該單詞，但是出現時相關性為負（全文檢索查詢允許負相關性）
*	表示以該單詞開頭的單詞，如lik*，表示可以是lik、like，又或者是likes。
"	表示短語

下面展示每個操作符對應的例子：

1. 查找既有 pease 又有 hot 的文檔：

select * from fts_a where match(body) against ('+pease +hot' in boolean mode);
           

2. 查找隻有pease 但沒有 hot 的文檔

select * from fts_a where match(body) against ('+pease -hot' in boolean mode);
           

3. 查找有pease 或 hot 的文檔

select * from fts_a where match(body) against ('pease hot' in boolean mode);
           

 4. Proximity Search 臨近查找

select * from fts_a where match(body) against (' "hot cole"@4' in boolean mode);
select * from fts_a where match(body) against (' "hot cole"@3' in boolean mode);
           

hot 和 cole 之間距離有{, pease porridge cole} 四個詞，是以後一語句不滿足條件。

5. > 增加相關性

select fts_doc_id, body, MATCH(body) AGAINST ('like >pot' in boolean mode) AS Relevance from fts_a;
           

上述SQL語句查詢根據是否有單詞like 或 pot 進行相關性統計，并且出現單詞pot後相關性需要增加。文檔4雖然有兩個like單詞，但是沒有pot，是以相關性沒有1和5高。

6. < 降低相關性

下面的查詢增加了 '<some' 的條件得到的結果：

select fts_doc_id, body, MATCH(body) AGAINST ('like >hot <some' in boolean mode) AS Relevance from fts_a;
           

可以發現文檔5的相關性變為了負，這是因為雖然其中存在like單詞，但是也存在some單詞，是以根據查詢條件，其相關性變為了負相關。

7. * 通配符

select * from fts_a where MATCH(body) AGAINST ('po*' in boolean mode);
           

可以看到最後結果中的文檔包含以po開頭的單詞。

8. 短語查詢

select * from fts_a where MATCH(body) AGAINST('like it hot' in boolean mode);
           

select * from fts_a where MATCH(body) AGAINST('"like it hot"' in boolean mode);
           

可以看到第一條SQL語句沒有使用 ""将 like it hot 視為一個短語，而隻是将其視為3個單詞，是以傳回4個結果，而第二條SQL語句使用"likt it hot"，是以查詢的是短語，故僅文檔4符合查詢條件。

3.3 Query Expansion(查詢擴充)

MySQL資料庫還支援全文檢索的擴充查詢。這種查詢通常在查詢的關鍵詞太短，使用者需要implied knowledge（隐含知識）時進行。例如，對于單詞database的查詢，使用者可能希望查詢的不僅僅是包含database的文檔，可能還指那些包含MySQL、oracle、DB2單詞。而這時可以使用Query Expansion模式開啟全文檢索的implied knowledge。

通過在查詢短語中添加 WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以開啟blind query expansion（又稱為automatic relevance feedback）。該查詢分為兩個階段：

1. 第一階段：根據搜尋的單詞進行全文索引查詢。
2. 第二階段：根據第一階段産生的結果分詞後再進行一次全文檢索查詢。

下面結合一個例子來說明，首先建立測試表：

create table articles(
	id int unsigned auto_increment not null primary key,
	title VARCHAR(200),
	body text,
	fulltext(title, body))engine=InnoDB;

insert into articles (title, body) values
    ('MySQL Tutorial', 'DBMS stands for DataBase ...'),
    ('How To Use MySQL Well', 'After you went through a ...'),
    ('Optimizing MySQL', 'In this turitual we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparision ...'),
    ('MySQL Security','When configured properly, MySQL ...'),
    ('Tuning DB2','For IBM database ...'),
    ('IBM History','DB2 history for IBM ...');
           

在這個例子中，并沒有顯示建立FTS_DOC_ID列，是以InnoDB存儲引擎會自動建立該列，并添加唯一索引。此外，表articles 的全文檢索索引是根據列title 和 body 的聯合索引。接着根據database 關鍵字進行的全文索引查詢：

select * from articles where match(title, body) against ('database');
           

可以看到，查詢傳回了3條記錄，body字段包含database關鍵字。接着開啟Query Expansion，觀察結果：

select * from articles where match(title, body) against ('database' with query expansion);
           

可以看到最後得到8條結果，除了之前包含的 database的記錄，也有包含title 或 body 字段中包含MySQL、DB2的文檔，這就是Query Expansion。

由于Query Expansion 的全文檢索可能帶來許多非相關性的查詢，是以在使用時，使用者可能需要非常謹慎。

4. ngram 分詞器

前面我們說過，InnoDB存儲引擎内置的分詞器不支援中文、日文等語言，因為這些語言并不使用空格作為分詞符，為了解決這個問題，MySQL提供了

ngram

全文解析器。自MySQL5.7.6版起，MySQL将

ngram

全文解析器作為内置的伺服器插件，這意味着當MySQL資料庫伺服器啟動時，MySQL會自動加載該插件。 MySQL支援用于InnoDB和MyISAM存儲引擎的

ngram

全文解析器。

根據定義，

ngram

是來自文本序列的多個字元的連續序列。

ngram

全文解析器的主要功能是将文本序列标記為

n

個字元的連續序列。

以下說明了

ngram

全文解析器如何标記不同值

n

的文本序列：

n = 1: '全','文','檢','索'
n = 2: '全文', '文檢', '檢索'
n = 3: '全文檢', '文檢索'
n = 4: '全文檢索'
           

要建立使用

ngram

全文解析器的

FULLTEXT

索引，可以在CREATE TABLE，ALTER TABLE或

CREATE INDEX

語句中添加

WITH PARSER ngram

。

CREATE TABLE ft_chn (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT ( title , body ) WITH PARSER NGRAM
)  ENGINE=INNODB CHARACTER SET UTF8;
           

值得注意的是，ngram的自然語言搜尋模式中，搜尋項被轉換為

ngram

值的并集。在ngram布爾模式中，搜尋項被轉換成

ngram

短語搜尋。

預設情況下，

ngram

中的令牌大小(

n

)為

2

，要更改令牌大小，請使用

ngram_token_size

配置選項，這是個隻讀選項，有兩種配置方式,值的範圍是：

1

到

10

，一種是啟動時傳參，另一種是寫到配置檔案裡：

// 啟動時傳參
mysqld --ngram_token_size=1

// 寫到配置檔案裡
[mysqld]
ngram_token_size=1
           

MySQL全文索引的基本知識就總結到這裡。

5. 參考資料

1. 《MySQL技術内幕 InnoDB存儲引擎第二版》姜承堯著

2.  MySQL ngram全文解析器

3. 關于反向索引讨論

4. 反向索引

5. MySQL · 引擎特性 · InnoDB 全文索引簡介

================================================================================================

Linux應用程式、核心、驅動、背景開發交流讨論群(745510310)，感興趣的同學可以加群讨論、交流、資料查找等，前進的道路上，你不是一個人奧^_^。...