创建一个带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 ...');

将innodb_ft_aux_table 变量设置为具有 FULLTEXT索引的表的名称。如果未设置此变量，则InnoDB FULLTEXT INFORMATION_SCHEMA表为空，但 INNODB_FT_DEFAULT_STOPWORD.

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

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

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

查询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)

从 test/articles表中删除一些记录。

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

查询INNODB_FT_DELETED 表。FULLTEXT该表记录了从索引中删除的行 。为了避免在 DML 操作期间进行昂贵的索引重组，有关新删除记录的信息被单独存储，在您进行文本搜索时从搜索结果中过滤掉，并在您运行时从主搜索索引中删除 OPTIMIZE TABLE。

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

运行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)

查询INNODB_FT_CONFIG 表。该表包含有关 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     |
+---------------------------+-------+

Disable innodb_optimize_fulltext_only，因为它只是暂时启用：

mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;