创建一个包含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 系统变量，并对包含FULLTEXT 索引的表运行OPTIMIZE TABLE。此操作会将FULLTEXT 索引缓存的内容刷新到主FULLTEXT 索引中。 innodb_optimize_fulltext_only 会更改OPTIMIZE TABLE 语句对InnoDB 表的操作方式，它旨在在维护操作期间暂时启用，以用于包含FULLTEXT 索引的InnoDB 表。

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

由于OPTIMIZE TABLE 操作已将FULLTEXT 索引缓存刷新，因此INNODB_FT_INDEX_CACHE 表现在为空。

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

禁用innodb_optimize_fulltext_only，因为它旨在仅在临时情况下启用

mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;