建立具有 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;