创建一个包含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 表的操作方式，旨在在维护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 |
+------------+--------------+-------------+-----------+--------+----------+

由于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;