系统表介绍
INNODB_FT_INDEX_TABLE 存储了反转索引信息，用以处理带有全文索引的innodb 表
| 列名 | 含义 |
|---|---|
| WORD | 词 |
| FIRST_DOC_ID | 全文索引中第一个文档的编号 |
| LAST_DOC_ID | 全文索引中最后一个文档的编号 |
| DOC_COUNT | 文档数量 |
| DOC_ID | 文档ID |
| POSITION | 在相关文档中的位置 |
实例
mysql> drop table if exists articles ;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
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 ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> OPTIMIZE TABLE articles;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| dao.articles | optimize | status | OK |
+--------------+----------+----------+----------+
1 row in set (0.01 sec)
mysql> SET GLOBAL innodb_ft_aux_table = 'dao/articles';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ;
+------------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------+--------------+-------------+-----------+--------+----------+
| 1001 | 5 | 5 | 1 | 5 | 0 |
| After | 3 | 3 | 1 | 3 | 22 |
| DBMS | 2 | 2 | 1 | 2 | 15 |
| DataBase | 2 | 2 | 1 | 2 | 31 |
| MySQL | 2 | 7 | 6 | 2 | 0 |
| MySQL | 2 | 7 | 6 | 3 | 11 |
| MySQL | 2 | 7 | 6 | 4 | 11 |
| MySQL | 2 | 7 | 6 | 5 | 5 |
| MySQL | 2 | 7 | 6 | 6 | 0 |
| MySQL | 2 | 7 | 6 | 7 | 0 |
| MySQL | 2 | 7 | 6 | 7 | 41 |
| Never | 5 | 5 | 1 | 5 | 21 |
| Optimizing | 4 | 4 | 1 | 4 | 0 |
| Security | 7 | 7 | 1 | 7 | 6 |
| Tricks | 5 | 5 | 1 | 5 | 11 |
| Tutorial | 2 | 2 | 1 | 2 | 6 |
| Use | 3 | 3 | 1 | 3 | 7 |
| Well | 3 | 3 | 1 | 3 | 17 |
| YourSQL | 6 | 6 | 1 | 6 | 10 |
| comparison | 6 | 6 | 1 | 6 | 44 |
| configured | 7 | 7 | 1 | 7 | 20 |
| database | 6 | 6 | 1 | 6 | 35 |
| following | 6 | 6 | 1 | 6 | 25 |
| mysqld | 5 | 5 | 1 | 5 | 31 |
| properly | 7 | 7 | 1 | 7 | 31 |
| root | 5 | 5 | 1 | 5 | 41 |
| run | 5 | 5 | 1 | 5 | 27 |
| show | 4 | 4 | 1 | 4 | 37 |
| stands | 2 | 2 | 1 | 2 | 20 |
| through | 3 | 3 | 1 | 3 | 37 |
| tutorial | 4 | 4 | 1 | 4 | 25 |
| went | 3 | 3 | 1 | 3 | 32 |
| you | 3 | 3 | 1 | 3 | 28 |
+------------+--------------+-------------+-----------+--------+----------+
33 rows in set (0.00 sec)