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