InnoDB全文索引基礎

• 2019 年 10 月 4 日
• 筆記

全文索引：

官方文檔：

https://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

參考：

http://blog.csdn.net/u011734144/article/details/52817766

http://www.cnblogs.com/olinux/p/5169282.html

全文檢索通常使用的是倒排索引。

內容詳見姜大神的InnoDB存儲引擎2 書上 Page231~Page248

InnoDB存儲引擎從1.2.x開始支援全文索引技術，其採用full inverted index的方式。在InnoDB存儲引擎中，將(DocumentID,Postition)視為一個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中。增大該參數可以提高全文檢索性能。但是在宕機時候，未同步到磁碟中的索引資訊可能需要更長的時間進行恢復。

為了支援全文檢索，必須有一個列與word進行映射。在InnoDB中這個列被命名成FTS_DOC_ID，其類型為BIGINT UNSIGNED NOT NULL，並且InnoDB存儲引擎自動會在該列加上一個名為FTS_DOC_ID_INDEX的Unique Index。這些操作由存儲引擎自己完成，用戶也可以在建表時自動添加FTS_DOC_ID,以及對應的Unique Index。由於列名FTS_DOC_ID聚友特殊意義，因此在創建時必須注意相應的類型，否則會報錯。

文檔中的分詞的插入操作是在事務提交時完成，但是對於刪除操作，其在事務提交時，不刪除磁碟Auxiliary Table的記錄，而只是刪除FTS Cache Index記錄，對於Auxiliary Table中被刪除的記錄，存儲引擎會記錄其FTS DOCUMENT ID ,並將其保存在DELETE 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

例子：

> use test;

> 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 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);

查看數據：

> select * from fts_a;

+————+—————————————-+

| FTS_DOC_ID | body                                   |

+————+—————————————-+

|          1 | pease porridge in the pot             |

|          2 | pease porridge hot,pease porridge cold |

|          3 | Nine days old                          |

|          4 | Some like it hot,some like it cold     |

|          5 | Some like it the pot                   |

|          6 | Nine days old                          |

|          7 | I like code days                       |

+————+—————————————-+

7 rows in set (0.00 sec)

> set global innodb_ft_aux_table='test/fts_a';

Query OK, 0 rows affected (0.00 sec)

> SELECT * FROM information_schema.`INNODB_FT_INDEX_TABLE`;

+———-+————–+————-+———–+——–+———-+

| WORD     | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |

+———-+————–+————-+———–+——–+———-+

| code     |            7 |           7 |         1 |      7 |        7 |

| cold     |            2 |           4 |         2 |      2 |       34 |

| cold     |            2 |           4 |         2 |      4 |       30 |

| days     |            3 |           7 |         3 |      3 |        5 |

| days     |            3 |           7 |         3 |      6 |        5 |

| days     |            3 |           7 |         3 |      7 |       12 |

| hot      |            2 |           4 |         2 |      2 |       15 |

| hot      |            2 |           4 |         2 |      4 |       13 |

| like     |            4 |           7 |         3 |      4 |        5 |

| like     |            4 |           7 |         3 |      4 |       17 |

| like     |            4 |           7 |         3 |      5 |        5 |

| like     |            4 |           7 |         3 |      7 |        2 |

| nine     |            3 |           6 |         2 |      3 |        0 |

| nine     |            3 |           6 |         2 |      6 |        0 |

| old      |            3 |           6 |         2 |      3 |       10 |

| old      |            3 |           6 |         2 |      6 |       10 |

| pease    |            1 |           2 |         2 |      1 |        0 |

| pease    |            1 |           2 |         2 |      2 |        0 |

| pease    |            1 |           2 |         2 |      2 |       19 |

| porridge |            1 |           2 |         2 |      1 |        6 |

| porridge |            1 |           2 |         2 |      2 |        6 |

| porridge |            1 |           2 |         2 |      2 |       19 |

| post     |            1 |           1 |         1 |      1 |       22 |

| pot      |            5 |           5 |         1 |      5 |       17 |

| some     |            4 |           5 |         2 |      4 |        0 |

| some     |            4 |           5 |         2 |      4 |       17 |

| some     |            4 |           5 |         2 |      5 |        0 |

+———-+————–+————-+———–+——–+———-+

可以看到每個word對應一個DOC_ID和POSITION。此外，還記錄了FIRST_DOC_ID、LAST_DOC_ID、DOC_COUNT分別代表該word第一次出現文檔的ID,最後一次出現的文檔ID，以及該word在多少個文檔中存在。

若此時執行下面的SQL語句，會刪除FTS_DOC_ID為7的文檔

> DELETE FROM fts_a WHERE FTS_DOC_ID=7;

InnoDB存儲引擎並不會直接刪除索引中對應的記錄，而是將刪除的文檔ID插入到DELETED表

> SELECT * FROM information_schema.`INNODB_FT_DELETED`;

+——–+

| DOC_ID |

+——–+

|      7 |

+——–+

如果用戶想要徹底刪除倒排索引中該文檔的分詞資訊，可以執行：

> SET GLOBAL innodb_optimize_fulltext_only=1;

> OPTIMIZE TABLE fts_a;

+————+———-+———-+———-+

| Table      | Op       | Msg_type | Msg_text |

+————+———-+———-+———-+

| iot2.fts_a | optimize | status   | OK       |

+————+———-+———-+———-+

驗證；

> SELECT * FROM information_schema.`INNODB_FT_DELETED`;

+——–+

| DOC_ID |

+——–+

|    7 |

+——–+

> SELECT * FROM information_schema.`INNODB_FT_BEING_DELETED`;

+——–+

| DOC_ID |

+——–+

|    7 |

+——–+

> SELECT count(*) FROM information_schema.`INNODB_FT_INDEX_TABLE`;   — INNODB_FT_INDEX_TABLE表裡面剩餘的行數

+———-+

| count(*) |

+———-+

|     24 |

+———-+

運行OPTIMIZE TABLE 可以將記錄徹底刪除，並且徹底刪除的文檔ID會記錄到INNODB_FT_BEGIN_DELETED中。

此外，由於FTS_DOC_ID為7的這個文檔已經被刪除，因此不允許在插入這個文檔ID，否則會拋出異常

> INSERT INTO fts_a SELECT 7,'I like this days';

ERROR 182 (HY000): Invalid InnoDB FTS Doc ID

stopword列表(stopword list)是本節最後闡述的一個概念，其表示該列表中的word不需要對其進行索引分詞操作。例如，對於the這個單詞，由於其不具有具體的意義，因此將其視為stopword，InnoDB存儲引擎有一張默認的stopword列表，在information_schema架構下，表名為INNODB_FT_DEFAULT_STOPWORD，默認為36個stopword可以通過參數 innodb_ft_server_stopword_table 來定義stopword列表，如

> CREATE TABLE test.user_stopword (value VARCHAR(30) NOT NULL DEFAULT '' ) ENGINE=INNODB DEFAULT CHARSET=utf8;  #此處必須為utf8不然會碰到bug

> SET GLOBAL innodb_ft_server_stopword_table='test/user_stopword';

這樣的話， 

使用全文檢索還有以下限制：

1 每張表只能有一個全文檢索的索引

2 由多列組合而成的全文檢索的索引必須使用相同的字符集與排序規則

3 不支援沒有單詞界定符delimiter的語言，如中文 日文漢語等。

全文檢索語法：

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

MATCH指定了需要被查詢的列。AGAINST指定了使用何種方法去進行查詢。

查詢模式有3種：Natural Language 、 Boolean  、 Query Expansion

1、Natural Language （默認的全文檢索查詢模式）

test> SELECT * FROM fts_a WHERE MATCH(body) AGAINST ('Porridge' in natural language mode);

+————–+———————————————-+

|   FTS_DOC_ID | body                           |

|————–+———————————————-|

|            2 | pease porridge hot,pease porridge cold |

|            1 | pease porridge in the post         |

+————–+———————————————-+

傳統查詢：

test> explain extended SELECT * from fts_a where body like '%Porridge%';

+——+—————+———+——–+—————–+——–+———–+——–+——–+————+————-+

|   id | select_type   | table   | type   |   possible_keys |    key |   key_len |    ref |   rows |   filtered | Extra       |

|——+—————+———+——–+—————–+——–+———–+——–+——–+————+————-|

|    1 | SIMPLE        | fts_a   | ALL    |          <null> | <null> |    <null> | <null> |      6 |        100 | Using where |

+——+—————+———+——–+—————–+——–+———–+——–+——–+————+————-+

全文索引查詢：

test> explain extended SELECT * from fts_a where match(body) against ('Porridge' in natural language mode);

+——+—————+———+———-+—————–+———+———–+——–+——–+————+————-+

|   id | select_type   | table   | type     | possible_keys   | key     |   key_len |    ref |   rows |   filtered | Extra       |

|——+—————+———+———-+—————–+———+———–+——–+——–+————+————-|

|    1 | SIMPLE        | fts_a   | fulltext | idx_fts         | idx_fts |         0 | <null> |      1 |        100 | Using where |

+——+—————+———+———-+—————–+———+———–+——–+——–+————+————-+

可以看到用到了全文索引。

在WHERE條件中使用MATCH函數，查詢返回的結果是根據相關性進行排序的，即相關性最高的結果放在第一位。0表示沒有任何的相關性。

相關性的計算依賴下面4個條件：

1、word是否在文檔中出現

2、word在文檔中出現的次數

3、word在索引列中的數量

4、多少個文檔包含該word

上面的那個select 查詢中，文檔2中porridge出現了2次，因此排在上面。

查詢相關性的SQL語句：

test> SELECT fts_doc_id,body , match(body) AGAINST ('Porridge' in natural language mode) as Relevance from fts_a ORDER BY Relevance DESC;

+————–+————————————————————-+————-+

|   fts_doc_id | body                                                        |   Relevance |

|————–+————————————————————-+————-|

|           29 | i am porridge ,and you is porridge,and we are all porridge. |   0.271857  |

|            2 | pease porridge hot,pease porridge cold                      |   0.181238  |

|            1 | pease porridge in the post                                  |   0.0906191 |

|            3 | Nine days old                                               |   0         |

|            6 | Nine days old                                               |   0         |

|           27 | I like this days                                            |   0         |

|           28 | hello world                                                 |   0         |

+————–+————————————————————-+————-+

對於全文檢索，還需要考慮以下的因素：

查詢的word在stopword列中，忽略該字元串的查詢。

查詢的word的字元長度是否在區間[innodb_ft_min_token_size, innodb_ft_max_token_size]內。(默認是3-84個字元長度)

如：test> SELECT fts_doc_id,body , match(body) AGAINST ('the' in natural language mode) as Relevance from fts_a ORDER BY Relevance desc ;

+————–+————————————————————-+————-+

|   fts_doc_id | body                                                        |   Relevance |

|————–+————————————————————-+————-|

|            1 | pease porridge in the post                                  |           0 |

|            2 | pease porridge hot,pease porridge cold                      |           0 |

|            3 | Nine days old                                               |           0 |

|            6 | Nine days old                                               |           0 |

|           27 | I like this days                                            |           0 |

|           28 | hello world                                                 |           0 |

+————–+————————————————————-+————-+

2、Boolean Language 

test> SELECT * from fts_a where match(body) against('+Pease – hot' in boolean mode);

+————–+—————————-+

|   FTS_DOC_ID | body                       |

|————–+—————————-|

|            1 | pease porridge in the post |

+————–+—————————-+

boolean全文檢索支援以下的幾種操作符：

官網文檔：https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

1 加號表示必須出現

2 減號表示一定不能出現

3 (no operator) 表示該word是可選的，但是如果出現，其相關性會更高

4 @distance 表示查詢的多個單詞之間的距離是否在distance之內，distance的單位是單詞。這種全文索引的查詢也稱為proximity Search。

如MATCH(body)AGAINST('"Pease pot"@20' IN BOOLEAN MODE) 表示字元串Pease和pot之間的距離需在20個單詞範圍內。

5 > 表示出現該單詞時增加相關性

6 < 表示出現該單詞時降低相關性

7 ~ 表示運行出現該單詞，但是出現時相關性為負

8 * 表示以該單詞開頭的單詞，如lik* 可以表示lik、like、likes之類的。

9 " 表示短語。

"例子：

找出有Pease且有hot的文檔：

test> SELECT * from fts_a where match(body) against('+Pease +hot' in boolean mode);

+————–+—————————————-+

|   FTS_DOC_ID | body                                   |

|————–+—————————————-|

|            2 | pease porridge hot,pease porridge cold |

+————–+—————————————-+

找出有Pease或者hot的文檔：

test> SELECT * from fts_a where match(body) against('Pease hot' in boolean mode);

+————–+—————————————-+

|   FTS_DOC_ID | body                                   |

|————–+—————————————-|

|            2 | pease porridge hot,pease porridge cold |

|            1 | pease porridge in the post             |

+————–+—————————————-+

找出2個單詞之間距離不超過8的文檔：

test> SELECT * from fts_a where match(body) against('"lirulei days" @8' in boolean mode);

+————–+—————————————————–+

|   FTS_DOC_ID | body                                                |

|————–+—————————————————–|

|           31 | i am lirulei, and happy qixi days                   |

+————–+—————————————————–+

根據是否有單詞like或者pot進行相關性統計，並把出現pot的文檔的相關性提高。（文檔4中雖然有2個like，但是沒有pot，因此相關性沒有文檔1和5高）

test> SELECT fts_doc_id, body,  match(body) against('like > pot' in boolean mode) as Relevance from fts_a ORDER BY Relevance desc ;

+————–+—————————————-+————-+

|   fts_doc_id | body                                   |   Relevance |

|————–+—————————————-+————-|

|            5 | Some like it the pot                   |    1.43142  |

|            1 | pease porridge in the pot              |    1.29601  |

|            4 | Some like it hot,some like it cold     |    0.270814 |

|            7 | I like code days                       |    0.135407 |

|            2 | pease porridge hot,pease porridge cold |    0        |

|            3 | Nine days old                          |    0        |

|            6 | Nine days old                          |    0        |

+————–+—————————————-+————-+

對上面的查詢增加個降低出現some的文檔的權重的查詢條件：

test> SELECT fts_doc_id, body,  match(body) against('like >hot <some' in boolean mode) as Relevance from fts_a ORDER BY Relevance desc ;

+————–+—————————————-+————-+

|   fts_doc_id | body                                   |   Relevance |

|————–+—————————————-+————-|

|            2 | pease porridge hot,pease porridge cold |    1.29601  |

|            4 | Some like it hot,some like it cold     |    1.15884  |

|            7 | I like code days                       |    0.135407 |

|            1 | pease porridge in the pot              |    0        |

|            3 | Nine days old                          |    0        |

|            6 | Nine days old                          |    0        |

|            5 | Some like it the pot                   |   -0.568583 |

+————–+—————————————-+————-+

找出cod開頭的文檔：

test> SELECT fts_doc_id, body,  match(body) against('cod*' in boolean mode) as Relevance from fts_a ORDER BY Relevance desc ;

+————–+—————————————-+————-+

|   fts_doc_id | body                                   |   Relevance |

|————–+—————————————-+————-|

|            7 | I like code days                       |    0.714191 |

|            1 | pease porridge in the pot              |    0        |

|            2 | pease porridge hot,pease porridge cold |    0        |

|            3 | Nine days old                          |    0        |

|            4 | Some like it hot,some like it cold     |    0        |

|            5 | Some like it the pot                   |    0        |

|            6 | Nine days old                          |    0        |

+————–+—————————————-+————-+

短語查找（注意要加上引號，不然查詢結果是不正確的）

test> SELECT * from fts_a where  match(body) against('"days old"' in boolean mode)  ;

+————–+—————+

|   FTS_DOC_ID | body          |

|————–+—————|

|            3 | Nine days old |

|            6 | Nine days old |

+————–+—————+

3、Query Expansion

參見姜的書上Page247-248

官方文檔：https://dev.mysql.com/doc/refman/5.6/en/fulltext-query-expansion.html