InnoDB 层全文索引字典表 | 全方位认识 information_schema

迈不过友情╰ 2022-02-24 13:26 83阅读 0赞

在上一篇中，我们详细介绍了[InnoDB 层的锁、事务、及其相关的统计信息字典表][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存储引擎，喜好专研开源技术，多次在公开场合做过线下线上数据库专题分享，发表过多篇数据库相关的研究文章。

[InnoDB]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA==&mid=2247484824&idx=1&sn=8e441507ebfcc91b522d1d99c2a61406&chksm=fb242c95cc53a583f406525cf10976975104157000f31ef17922623f3f2f6558c39115e5a32e&scene=21#wechat_redirect