Documentation Home
MySQL 8.0 参考手册  / 第 15 章 InnoDB 存储引擎  / 15.15 InnoDB INFORMATION_SCHEMA 表  /  14.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT 索引表

14.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT 索引表

下表提供了 FULLTEXT索引的元数据:

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_FT%';
+-------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_FT%) |
+-------------------------------------------+
| INNODB_FT_CONFIG                          |
| INNODB_FT_BEING_DELETED                   |
| INNODB_FT_DELETED                         |
| INNODB_FT_DEFAULT_STOPWORD                |
| INNODB_FT_INDEX_TABLE                     |
| INNODB_FT_INDEX_CACHE                     |
+-------------------------------------------+

表格概览

笔记

除了 INNODB_FT_DEFAULT_STOPWORD表格之外,这些表格最初都是空的。在查询其中任何一个之前,将 innodb_ft_aux_table系统变量的值设置为包含FULLTEXT索引的表的名称(包括数据库名称)(例如,test/articles)。

示例 14.5 InnoDB FULLTEXT 索引 INFORMATION_SCHEMA 表

此示例使用带有FULLTEXT 索引的表来演示 FULLTEXT索引 INFORMATION_SCHEMA表中包含的数据。

  1. 创建一个带FULLTEXT索引的表并插入一些数据:

    mysql> CREATE TABLE articles (
             id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
             title VARCHAR(200),
             body TEXT,
             FULLTEXT (title,body)
           ) ENGINE=InnoDB;
    
    mysql> 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 tutorial we show ...'),
           ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
           ('MySQL vs. YourSQL','In the following database comparison ...'),
           ('MySQL Security','When configured properly, MySQL ...');
  2. innodb_ft_aux_table 变量设置为具有 FULLTEXT索引的表的名称。如果未设置此变量,则InnoDB FULLTEXT INFORMATION_SCHEMA表为空,但 INNODB_FT_DEFAULT_STOPWORD.

    SET GLOBAL innodb_ft_aux_table = 'test/articles';
  3. 查询INNODB_FT_INDEX_CACHE 表,该表显示有关FULLTEXT索引中新插入行的信息。为了避免在 DML 操作期间进行昂贵的索引重组,新插入行的数据保留在FULLTEXT 索引缓存中,直到OPTIMIZE TABLE运行(或直到服务器关闭或超过缓存限制)。

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5;
    +------------+--------------+-------------+-----------+--------+----------+
    | WORD       | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
    +------------+--------------+-------------+-----------+--------+----------+
    | 1001       |            5 |           5 |         1 |      5 |        0 |
    | after      |            3 |           3 |         1 |      3 |       22 |
    | comparison |            6 |           6 |         1 |      6 |       44 |
    | configured |            7 |           7 |         1 |      7 |       20 |
    | database   |            2 |           6 |         2 |      2 |       31 |
    +------------+--------------+-------------+-----------+--------+----------+
  4. 启用 innodb_optimize_fulltext_only 系统变量并OPTIMIZE TABLE在包含 FULLTEXT索引的表上运行。此操作将索引缓存的内容刷新FULLTEXT到主FULLTEXT索引。 innodb_optimize_fulltext_only 更改OPTIMIZE TABLE语句对 InnoDB表的操作方式,并旨在在对 InnoDB具有 FULLTEXT索引的表进行维护操作期间临时启用。

    mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
    
    mysql> OPTIMIZE TABLE articles;
    +---------------+----------+----------+----------+
    | Table         | Op       | Msg_type | Msg_text |
    +---------------+----------+----------+----------+
    | test.articles | optimize | status   | OK       |
    +---------------+----------+----------+----------+
  5. 查询INNODB_FT_INDEX_TABLE 表以查看有关主 FULLTEXT索引中数据的信息,包括有关刚刚从 FULLTEXT索引缓存中刷新的数据的信息。

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;
    +------------+--------------+-------------+-----------+--------+----------+
    | WORD       | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
    +------------+--------------+-------------+-----------+--------+----------+
    | 1001       |            5 |           5 |         1 |      5 |        0 |
    | after      |            3 |           3 |         1 |      3 |       22 |
    | comparison |            6 |           6 |         1 |      6 |       44 |
    | configured |            7 |           7 |         1 |      7 |       20 |
    | database   |            2 |           6 |         2 |      2 |       31 |
    +------------+--------------+-------------+-----------+--------+----------+

    由于操作刷新了 索引缓存 , 该INNODB_FT_INDEX_CACHE表现在为空。OPTIMIZE TABLEFULLTEXT

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5;
    Empty set (0.00 sec)
  6. test/articles表中删除一些记录。

    mysql> DELETE FROM test.articles WHERE id < 4;
  7. 查询INNODB_FT_DELETED 表。FULLTEXT该表记录了从索引中删除的行 。为了避免在 DML 操作期间进行昂贵的索引重组,有关新删除记录的信息被单独存储,在您进行文本搜索时从搜索结果中过滤掉,并在您运行时从主搜索索引中删除 OPTIMIZE TABLE

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
    +--------+
    | DOC_ID |
    +--------+
    |      2 |
    |      3 |
    |      4 |
    +--------+
  8. 运行OPTIMIZE TABLE以删除已删除的记录。

    mysql> OPTIMIZE TABLE articles;
    +---------------+----------+----------+----------+
    | Table         | Op       | Msg_type | Msg_text |
    +---------------+----------+----------+----------+
    | test.articles | optimize | status   | OK       |
    +---------------+----------+----------+----------+

    INNODB_FT_DELETED表现在应该是空的。

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
    Empty set (0.00 sec)
  9. 查询INNODB_FT_CONFIG 表。该表包含有关 FULLTEXT索引和相关处理的元数据:

    • optimize_checkpoint_limitOPTIMIZE TABLE:运行停止 后的秒数。

    • synced_doc_id: 下次 DOC_ID再发。

    • stopword_table_namedatabase/table用户定义的停用词表的名称。VALUE如果没有用户定义的停用词表,则该列为空 。

    • use_stopword:表示是否使用停用词表,在 FULLTEXT创建索引时定义。

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
    +---------------------------+-------+
    | KEY                       | VALUE |
    +---------------------------+-------+
    | optimize_checkpoint_limit | 180   |
    | synced_doc_id             | 8     |
    | stopword_table_name       |       |
    | use_stopword              | 1     |
    +---------------------------+-------+
  10. Disable innodb_optimize_fulltext_only,因为它只是暂时启用:

    mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;