InnoDB 層全文索引字典表 | 全方位認識 information_schema

在上一篇中，我們詳細介紹了 InnoDB 層的鎖、事務、及其相關的統計資訊字典表 ，本期我們將為大家帶來系列第七篇《InnoDB 層全文索引字典表 | 全方位認識 information_schema》。

|  INNODB_FT_CONFIG

該表提供查詢有關InnoDB表的FULLTEXT索引和關聯的後設資料資訊

• 查詢此表之前，需要先設定innodb_ft_aux_table='db_name/tb_name'，db_name/tb_name為包含全文索引的表名和庫名

• 查詢該表的賬戶需要有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

root@localhost : test 11:58:58> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY                      | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180  |
| synced_doc_id            | 0    |
| stopword_table_name      |      |
| use_stopword              | 1    |
+---------------------------+-------+
4 rows in set (0.00 sec)

欄位含義如下：

• KEY：表示包含FULLTEXT索引的InnoDB表的後設資料項的名稱

• VALUE：表示與相應的KEY列關聯的值，反映InnoDB表的FULLTEXT索引的某方面的某些限制的值

PS：

• 該表僅用於內部配置使用。並不用做統計資訊

• KEY列的值可能會根據InnoDB全文處理的效能調優和除錯需求而變化。其中記錄的後設資料項名稱值包括： 
  * optimize_checkpoint_limit：OPTIMIZE TABLE語句執行的時間，單位秒 
  * synced_doc_id：下一個要執行的DOC_ID值 
  * stopword_table_name：使用者定義的儲存停用詞表的資料庫/表名。如果未自定義停用詞表，則該項記錄的value列為空 
  * use_stopword：表示是否使用停用詞表，該停用詞表在建立FULLTEXT索引時定義，預設停用詞表為INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD

|  INNODB_FT_BEING_DELETED

該表僅在OPTIMIZE TABLE語句執行維護操作期間作為INNODB_FT_DELETED表的快照資料存放使用。執行OPTIMIZE TABLE語句時，會先清空INNODB_FT_BEING_DELETED表中的資料，儲存INNODB_FT_DELETED表中的快照資料到INNODB_FT_BEING_DELETED表，並從INNODB_FT_DELETED表中刪除DOC_ID。由於INNODB_FT_BEING_DELETED表中的內容通常生命週期較短，因此該表中的資料對於監控或者除錯來說用處並不大

• 該表中預設不記錄資料，需要設定系統配置引數innodb_ft_aux_table=string（string表示db_name.tb_name字串），並建立好全文索引，設定好停用詞等

• 查詢該表的賬戶需要有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 設定innodb_ft_aux_table系統引數
root@localhost : test 11:50:16> SET GLOBAL innodb_ft_aux_table = 'test/test';
Query OK, 0 rows affected (0.00 sec)
# 建立全文索引
root@localhost : test 11:26:30> select * from test;
+------+---------+
| id  | test    |
+------+---------+
|    1 | a b c d |
|    1 | a b c d |
|    2 | a b c d |
+------+---------+
3 rows in set (0.00 sec)
root@localhost : test 11:51:06> alter table test add fulltext i_test(test);
Query OK, 0 rows affected, 1 warning (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 1
# 刪除表中的資料
root@localhost : test 11:55:09> delete from test where id=1;
Query OK, 2 rows affected (0.06 sec)
# 查詢INNODB_FT_DELETED表和INNODB_FT_BEING_DELETED表中的資料，可以發現INNODB_FT_BEING_DELETED為空值，而INNODB_FT_DELETED表存放著被刪除的全文索引值
root@localhost : test 11:56:12> select * from information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
+--------+
2 rows in set (0.00 sec)
root@localhost : test 11:57:10> select * from information_schema.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)
# 執行optimize table語句，然後再次查詢INNODB_FT_BEING_DELETED和INNODB_FT_DELETED表，如果表中資料夠大，在執行optimize table語句期間，可以發現INNODB_FT_DELETED表為空值，INNODB_FT_BEING_DELETED表存放著之前被刪除的全文索引值
root@localhost : test 11:57:15> optimize table test;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op      | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note    | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | status  | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.08 sec)
root@localhost : test 11:58:50> select * from information_schema.INNODB_FT_DELETED;
Empty set (0.00 sec)
root@localhost : test 11:58:55> select * from information_schema.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

欄位含義如下：

• DOC_ID：該欄位表示正在被刪除的行的DOC_ID值。當對錶使用OPTIMIZE TABLE語句將已刪除行的資料從FULLTEXT索引中物理刪除之前，執行了文字搜尋時，此值用於跳過innodb_ft_index_table表中的行

|  INNODB_FT_DELETED

該表提供查詢從InnoDB表的FULLTEXT索引中刪除的行資訊。它的存在是為了避免在InnoDB FULLTEXT索引的DML操作期間進行昂貴的索引重組操作，新刪除的全文索引中單詞的資訊將單獨儲存在該表中，在執行文字搜尋時從中過濾出搜尋結果，該表中的資訊僅在執行OPTIMIZE TABLE語句時清空

• 該表中的資訊預設不記錄，需要使用innodb_ft_aux_table選項(該選項預設值為空串)指定需要記錄哪個innodb引擎表的資訊，例如：test/test

• 查詢該表的賬戶需要有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 使用innodb_ft_aux_table 選項指定包含全文索引的Innodb引擎表
root@localhost : test 11:41:01> SET GLOBAL innodb_ft_aux_table = 'test/test';
Query OK, 0 rows affected (0.00 sec)
# 刪除表中的行
root@localhost : test 11:41:24> delete from test where id=1;
Query OK, 3 rows affected (0.02 sec)
# 查詢INNODB_FT_DELETED表，此時INNODB_FT_DELETED表中就包含了被刪除的全文索引的DOC_ID值
root@localhost : test 11:41:29> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      4 |
|      5 |
|      6 |
|    10 |
|    11 |
|    12 |
|    13 |
+--------+
7 rows in set (0.00 sec)

欄位含義如下：

• DOC_ID：從innodb_ft_aux_table引數指定的庫表中新刪除的行的全文索引DOC_ID值。該表中的值用於跳過innodb_ft_index_table表中的行查詢。在對innodb_ft_aux_table引數指定的表執行OPTIMIZE TABLE語句時將清除INNODB_FT_DELETED表中的值

| INNODB_FT_DEFAULT_STOPWORD

該表為預設的全文索引停用詞表，提供查詢停用詞列表值。啟用停用詞表需要開啟引數innodb_ft_enable_stopword=ON，該引數預設為ON，啟用停用詞功能之後，如果innodb_ft_user_stopword_table選項（針對指定的innodb引擎表中的全文索引生效）自定義了停用詞庫表名稱值，則停用詞功能使用innodb_ft_user_stopword_table選項指定的停用詞表，如果innodb_ft_user_stopword_table選項未指定，而innodb_ft_server_stopword_table選項（針對所有的innodb引擎表中的全文索引生效）自定義了停用詞庫表名稱值，則同停用詞功能使用innodb_ft_server_stopword_table選項指定的停用詞表，如果innodb_ft_server_stopword_table選項也未指定，則使用預設的停用詞表，即INNODB_FT_DEFAULT_STOPWORD表。

• 查詢該表需要賬戶有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 預設的停用詞列表值如下
admin@localhost : information_schema 06:46:38> select * from INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a    |
| about |
| an    |
| are  |
| as    |
| at    |
| be    |
| by    |
| com  |
| de    |
| en    |
| for  |
| from  |
| how  |
| i    |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the  |
| this  |
| to    |
| was  |
| what  |
| when  |
| where |
| who  |
| will  |
| with  |
| und  |
| the  |
| www  |
+-------+
36 rows in set (0.00 sec)

欄位含義如下：

• value：預設情況下用作InnoDB表的FULLTEXT索引的停用詞列表值。如果innodb_ft_server_stopword_table或innodb_ft_user_stopword_table選項指定了停用詞庫表值，則會覆蓋預設的停用詞表，不使用預設的停用詞表（INNODB_FT_DEFAULT_STOPWORD表）

| INNODB_FT_INDEX_CACHE

該表中提供查詢包含FULLTEXT索引的innodb儲存引擎表中新插入行的全文索引標記資訊。它存在的目的是為了避免在DML操作期間進行昂貴的索引重組，新插入的全文索引的單詞的資訊被單獨儲存在該表中，直到對錶執行OPTIMIZE TABLE語句時、或者關閉伺服器時、或者當快取記憶體中存放的資訊大小超過了innodb_ft_cache_size或innodb_ft_total_cache_size系統配置引數指定的大小才會執行清理

• 該表預設不記錄資料，需要使用innodb_ft_aux_table系統配置引數指定需要記錄哪個表中的新插入行的全文索引資料

• 查詢該表的賬戶需要有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 設定innodb_ft_aux_table選項指定需要記錄那個innodb表中的全文索引新插入的值
root@localhost : test 11:41:01> SET GLOBAL innodb_ft_aux_table = 'test/test';
Query OK, 0 rows affected (0.00 sec)
# 執行插入
root@localhost : test 11:40:57> insert into test values(1,'a b dddd');
Query OK, 1 row affected (0.00 sec)
root@localhost : test 11:41:00> insert into test values(1,'a b dddd');
Query OK, 1 row affected (0.01 sec)
root@localhost : test 11:41:01> insert into test values(1,'a b dddd');
Query OK, 1 row affected (0.00 sec)
# 檢視INNODB_FT_INDEX_CACHE表中的記錄資料
root@localhost : test 11:59:18> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| dddd |            6 |          13 |        8 |      6 |        4 |
| dddd |            6 |          13 |        8 |      7 |        4 |
| dddd |            6 |          13 |        8 |      8 |        4 |
| dddd |            6 |          13 |        8 |      9 |        4 |
| dddd |            6 |          13 |        8 |    10 |        4 |
| dddd |            6 |          13 |        8 |    11 |        4 |
| dddd |            6 |          13 |        8 |    12 |        4 |
| dddd |            6 |          13 |        8 |    13 |        4 |
+------+--------------+-------------+-----------+--------+----------+
8 rows in set (0.00 sec)

欄位含義如下：

• WORD：從新插入行的全文索引列值文字中提取的單詞文字

• FIRST_DOC_ID：該單詞在FULLTEXT索引中出現的第一個DOC_ID值

• LAST_DOC_ID：該單詞在FULLTEXT索引中出現的最後一個DOC_ID值

• DOC_COUNT：該單詞在FULLTEXT索引中出現的行數。同一個單詞可以在快取表中多次出現，但每個DOC_ID列值和POSITION列值的組合只會出現一次（即具有唯一性）

• DOC_ID：新插入的行的DOC_ID值

• POSITION：由DOC_ID值標識的該單詞在文件中的特定位置。該值並不是絕對的位置，它是新增一行記錄時，WORD列值字串在全文索引列值的整個字串中的位置偏移量（相當於python字串物件中的下標位置，例如：新增全文索引列值為'edf edfa eeeesdfs'，而WORD列值記錄為'eeeesdfs'，那麼POSITION列值記錄為9，表示WORD列值是從整個全文索引列值字串'edf edfa eeeesdfs'的第9個位置開始記錄的）

|  INNODB_FT_INDEX_TABLE

該表中提供查詢關於innodb表全文索引中用於反向文字查詢的倒排索引的分詞資訊

• 可以透過設定innodb_ft_aux_table來觀察倒排索引的輔助表：SET GLOBAL innodb_ft_aux_table='test/test'; 設定之後，就可以在information_schema下的表INNODB_FT_INDEX_TABLE得到表test中的分詞資訊，為了支援全文檢索，必須有一個列與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 test;（該操作會將全文索引的快取資訊重新整理到磁碟）

• 若被刪除的文件很多，那麼OPTIMIZE TABLE操作可能佔用非常多的時間，會影響到程式併發性，並極大的降低使用者的響應時間，使用者可以透過引數innodb_ft_num_word_optimize來限制每次實際刪除的分詞數量，預設為2000

• 查詢該表的賬戶需要有PROCESS許可權，該表為Memory引擎臨時表

下面是該表中儲存的資訊內容

# 啟用innodb_optimize_fulltext_only系統配置引數
root@localhost : test 12:28:29> SET GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.00 sec)
# 執行最佳化表語句
root@localhost : test 12:28:41>  OPTIMIZE TABLE test;
+-----------+----------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+-----------+----------+----------+----------+
| test.test | optimize | status  | OK      |
+-----------+----------+----------+----------+
1 row in set (0.02 sec)
# 設定innodb_ft_aux_table 系統配置引數為剛剛執行最佳化的表
root@localhost : test 12:28:48> SET GLOBAL innodb_ft_aux_table = 'test/test';
Query OK, 0 rows affected (0.00 sec)
# 查詢INNODB_FT_INDEX_TABLE 表中記錄的值
root@localhost : test 12:28:55> select * from information_schema.INNODB_FT_INDEX_TABLE ;
+----------+--------------+-------------+-----------+--------+----------+
| WORD    | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+----------+--------------+-------------+-----------+--------+----------+
| edf      |            9 |          10 |        2 |      9 |        0 |
| edf      |            9 |          10 |        2 |    10 |        0 |
| edfa    |            9 |          10 |        2 |      9 |        4 |
| edfa    |            9 |          10 |        2 |    10 |        4 |
| eeee    |            8 |          8 |        1 |      8 |        4 |
| eeeesdf  |            9 |          9 |        1 |      9 |        9 |
| eeeesdfs |          10 |          10 |        1 |    10 |        9 |
| dddd    |            3 |          5 |        3 |      3 |        4 |
| dddd    |            3 |          5 |        3 |      4 |        4 |
| dddd    |            3 |          5 |        3 |      5 |        4 |
| ddde    |            6 |          6 |        1 |      6 |        4 |
| ddee    |            7 |          7 |        1 |      7 |        4 |
+----------+--------------+-------------+-----------+--------+----------+
12 rows in set (0.00 sec)

欄位含義如下：與INNODB_FT_INDEX_CACHE表欄位含義相同

本期內容就介紹到這裡，本期內容參考連結如下：

https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-config-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-being-deleted-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-deleted-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-default-stopword-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-index-table-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-index-cache-table.html

| 作者簡介

羅小波·沃趣科技高階資料庫技術專家

IT從業多年，主要負責MySQL 產品的資料庫支撐與售後二線支撐。曾參與版本釋出系統、輕量級監控系統、運維管理平臺、資料庫管理平臺的設計與編寫，熟悉MySQL體系結構，Innodb儲存引擎，喜好專研開源技術，多次在公開場合做過線下線上資料庫專題分享，發表過多篇資料庫相關的研究文章。