创建一个带有 FULLTEXT 索引的表并插入一些数据：Create a table with a FULLTEXT index and insert some data:

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

将 innodb_ft_aux_table 变量设置为带有 FULLTEXT 索引的表名。如果未设置此变量，InnoDB FULLTEXT INFORMATION_SCHEMA 表将为空，唯一的例外是 INNODB_FT_DEFAULT_STOPWORD。Set the innodb_ft_aux_table variable to the name of the table with the FULLTEXT index. If this variable is not set, the InnoDB FULLTEXT INFORMATION_SCHEMA tables are empty, with the exception of INNODB_FT_DEFAULT_STOPWORD.

mysql> SET GLOBAL innodb_ft_aux_table = 'test/articles';

查询 INNODB_FT_INDEX_CACHE 表，该表显示 FULLTEXT 索引中新插入行的信息。为了避免在 DML 操作期间进行昂贵的索引重组，新插入行的数据会保留在 FULLTEXT 索引缓存中，直到执行 OPTIMIZE TABLE（或者直到服务器关闭或缓存限制被超过）。Query the INNODB_FT_INDEX_CACHE table, which shows information about newly inserted rows in a FULLTEXT index. To avoid expensive index reorganization during DML operations, data for newly inserted rows remains in the FULLTEXT index cache until OPTIMIZE TABLE is run (or until the server is shut down or cache limits are exceeded).

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 |
+------------+--------------+-------------+-----------+--------+----------+

启用innodb_optimize_fulltext_only系统变量，并对包含FULLTEXT索引的表运行OPTIMIZE TABLE。此操作会将FULLTEXT索引缓存的内容刷新到主FULLTEXT索引中。innodb_optimize_fulltext_only改变了OPTIMIZE TABLE语句在InnoDB表上的操作方式，旨在在对带有FULLTEXT索引的InnoDB表进行维护操作时临时启用。Enable the innodb_optimize_fulltext_only system variable and run OPTIMIZE TABLE on the table that contains the FULLTEXT index. This operation flushes the contents of the FULLTEXT index cache to the main FULLTEXT index. innodb_optimize_fulltext_only changes the way the OPTIMIZE TABLE statement operates on InnoDB tables, and is intended to be enabled temporarily, during maintenance operations on InnoDB tables with FULLTEXT indexes.

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;

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

查询 INNODB_FT_INDEX_TABLE 表以查看主 FULLTEXT 索引中的数据，包括刚刚从 FULLTEXT 索引缓存刷新出来的数据的信息。Query the INNODB_FT_INDEX_TABLE table to view information about data in the main FULLTEXT index, including information about the data that was just flushed from the FULLTEXT index cache.

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 TABLE 操作已经刷新了 FULLTEXT 索引缓存。The INNODB_FT_INDEX_CACHE table is now empty since the OPTIMIZE TABLE operation flushed the FULLTEXT index cache.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5;
Empty set (0.00 sec)

从 test/articles 表中删除一些记录。Delete some records from the test/articles table.

mysql> DELETE FROM test.articles WHERE id < 4;

查询 INNODB_FT_DELETED 表。该表记录从 FULLTEXT 索引中删除的行。为了避免在 DML 操作期间进行代价高昂的索引重组，新删除记录的信息会单独存储，在进行文本搜索时会从搜索结果中过滤掉，并且在执行 OPTIMIZE TABLE 时从主搜索索引中移除。Query the INNODB_FT_DELETED table. This table records rows that are deleted from the FULLTEXT index. To avoid expensive index reorganization during DML operations, information about newly deleted records is stored separately, filtered out of search results when you do a text search, and removed from the main search index when you run OPTIMIZE TABLE.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      4 |
+--------+

运行 OPTIMIZE TABLE 以删除已删除的记录。Run OPTIMIZE TABLE to remove the deleted records.

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

INNODB_FT_DELETED 表现在应该是空的。The INNODB_FT_DELETED table should now be empty.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
Empty set (0.00 sec)

查询 INNODB_FT_CONFIG 表。该表包含有关 FULLTEXT 索引及相关处理的元数据：Query the INNODB_FT_CONFIG table. This table contains metadata about the FULLTEXT index and related processing:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 8     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+

禁用 innodb_optimize_fulltext_only，因为它只打算临时启用：Disable innodb_optimize_fulltext_only, since it is intended to be enabled only temporarily:

mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;