创建一个带有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的FULLTEXTINFORMATION_ SCHEMA表将是空的，除了INNODB_FT_DEFAULT_STOPWORD表外。

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

查询INNODB_FT_INDEX_CACHE表，该表显示了在一个FULLTEXT索引中插入的新行信息。为了避免在DML操作期间昂贵的索引重组，新的插入行数据直到OPTIMIZE TABLE运行（或服务器关闭或缓存限制超出）才会被写入FULLTEXT索引。

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表的操作方式，并旨在临时启用，以便在维护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 TABLE操作刷新了FULLTEXT索引缓存。

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操作期间进行昂贵的索引重组，关于新删除的记录信息单独存储，filtered out of search results when you do a text search，并且在运行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     |
+---------------------------+-------+

禁用innodb_optimize_fulltext_only，因为它仅临时启用：

mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;