全文索引的原理及定义
全文索引时将存储在数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获取全文中有关章，节，段，句，词等信息，也可以进行各种统计和分析。
之前的MySQL数据库中，INNODB存储引擎并不支持全文索引技术，大多数的用户转向MyISAM存储引擎，不过这可能进行表的拆分，并需要将进行全文索引的数据存储为MyIsam表。这样的确能够解决逻辑业务的需求，但是却丧失了INNODB存储引擎的事务性，而这在生产环境应用中同样是非常关键的。
从INNODB1.2.x版本开始，INNODB存储引擎开始支持全文索引，其支持myisam的全部功能，并且还支持其他的一些特性。
倒排索引
全文索引通常使用倒排索引来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在的位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式。
- inverted file index, 其表现形式为{单词， 单词所在文档的ID}
- full inverted index，其表现形式为{单词，（单词所在文档的ID， 再具体文档中的位置）}
【具体来说就是inverted file index只能根据单词找到对应的文档，而full inverted index不仅能找到对应的文档，还能找到单词在文档中的具体位置】
INNODB全文索引
INNODB存储从1.2版本开始支持全文索引的技术，其采用full inverted index的技术。在INNODB存储引擎中将（documentid， position）视为一个“ilist”。因此在全文索引的表中，有两个列，一个是word字段，另一个是ilist字段，并且在word字段上设有索引。此外，INNODB存储引擎在ilist字段上存放了position信息，故可以进行proximity search(邻近查找)，而myisam不支持这个。
倒排索引需要将word字段存放在一张表中，这个表称为辅助表。在INNODB存储引擎中，为了提高全文检索的并行性能，共有6张辅助表，目前每张表根据word的latin编码进行分区。辅助表时持久的表，存放于磁盘上。然而在innodb存储引擎的全文索引中，还有另外一个重要的概念FTS index cache（全文索引缓存），其用来提高全文检索的性能。FTS index cache是一个红黑树结构，其根据（word， ilist）进行排序。这意味着插入的数据已经更新了对应的表，但是对全文索引的更新可能在分词操作后还在FTS index cache中，辅助表可能还没有更新。innodb存储引擎会批量对辅助表进行更新，而不是每次插入后更新一次辅助表。当对全文索引进行查询时，辅助表会首先将FTS index cache中对应的word字段合并到辅助表中，然后再进行查询。
innodb存储引擎允许用户查看指定倒排索引的辅助表中的分词信息，可以通过设置参数innodb_ft_aux_table来观察倒排索引的辅助表。
mysql> set global innodb_ft_aux_table="mytest/tb1"; #对应的表上必须有全文索引，不然会报错。 Query OK, 0 rows affected (0.00 sec) mysql> use information_schema; #在information_schema库中，查看对应表的分词信息
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc innodb_ft_index_table;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| WORD | varchar(337) | NO | | | |
| FIRST_DOC_ID | bigint(21) unsigned | NO | | 0 | |
| LAST_DOC_ID | bigint(21) unsigned | NO | | 0 | |
| DOC_COUNT | bigint(21) unsigned | NO | | 0 | |
| DOC_ID | bigint(21) unsigned | NO | | 0 | |
| POSITION | bigint(21) unsigned | NO | | 0 | |
+--------------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
#对于innodb存储引擎，总是在事务提交时将分词写入FTS Index Cache,然后再通过批量更新写入到磁盘。虽然INNODB存储引擎通过一种延时，批量的写入方式来提高数据库的性能，
#但是上述操作仅在事务提交时发生。
当数据库关闭时，在FTS Index Cache中的数据库会同步到磁盘上的辅助表。然而，如果当数据库发生宕机时，一些FTS Index Cache中的数据可能未被同步到磁盘上。那么下次重启数据库时，当用户对表进行全文索引时，INNODB存储引擎会自动读取未完成的文档，然后进行分词操作，再将分词的结果放入到FTS Index Cache中。
参数innodb_ft_cache_size用来控制FTS Index Cache的大小。当该缓存满时，会将其中的（word，ilist）分词信息同步到磁盘的辅助表中。增大该参数可以提高数据库的全文索引性能，但是在宕机时，未同步到磁盘中的索引信息可能需要更长时间恢复。
在innodb存储引擎中，为了支持全文检索，必须有一个列与word进行映射，在innodb中这个列被命名为FTS_DOC_ID，其类型必须为BIGINT UNSIGNED NOT NULL,并且innodb存储引擎会在该列上加上一个名为FTS_DOC_ID_INDEX的唯一索引。上述操作由innodb存储引擎自己完成，用户也可以在创建表时手动添加，主要对应的约束条件。
文档中分词的插入操作是在事务提交时完成，然而对于删除操作，其再事务提交时，不删除磁盘辅助表中的记录，而只是删除FTS cache index中的记录。对于辅助表中被删除的记录，innodb存储引擎会记录其FTS Document id,并将其保存在deleted辅助表中。在设置innodb_ft_aux_table后，用户同意可以方位information_schema架构下的表innodb_ft_deleted来观察删除的FTS Document ID。
由于文档的DML操作实际并不删除索引中的数据，相反还会在对应deleted表中插入记录，因此随着应用程序的允许，索引会变得非常大，即使索引中的有些数据已经被删除，查询也不会选择这类记录。innodb提供了一种方式，允许用户手工地将已经删除的记录从索引中彻底删除，该命令就是OPTIMIZE TABLE.因为这个命令还会进行一些其他的操作，如cardinality的重新统计等，若用户希望仅对倒排索引进行操作，可以通过如下参数设置。
mysql> set global innodb_optimize_fulltext_only = ON; Query OK, 0 rows affected (0.00 sec) mysql>
若删除的文档非常多，那么OPTIMIZE TABLE操作可能需要占用非常多的时间，这样会影响应用程序的并发性，并极大地降低用户的响应时间。用户可以通过innodb_ft_num_word_optimize来限制每次实际删除分词的数量。该参数默认数值为2000.
mysql> show variables like "innodb_ft_num_word_optimize"; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | innodb_ft_num_word_optimize | 2000 | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql>
上面提到过创建表的全文索引时，还有一个列叫做FTS_DOC_ID；这个列我们可以自己在创建表时创建，也可以让innodb存储引擎自动创建，innodb存储引擎自动创建的是隐藏的列。但是在官方文档中建议自己创建。
Defining an FTS_DOC_ID column at CREATE TABLE time is less expensive than creating a full-text index on a table that is already loaded with
data. If an FTS_DOC_ID column is defined on a table prior to loading data, the table and its indexes do not have to be rebuilt to add the new
column. If you are not concerned with CREATE FULLTEXT INDEX performance, leave out the FTS_DOC_ID column to have InnoDB create it for you.
InnoDB creates a hidden FTS_DOC_ID column along with a unique index (FTS_DOC_ID_INDEX) on the FTS_DOC_ID column. If you want to create your own
FTS_DOC_ID column, the column must be defined as BIGINT UNSIGNED NOT NULL and named FTS_DOC_ID (all upper case), as in the following
官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html
下面演示一个实例：
create table tb3(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body text,
FULLTEXT INDEX ft_index (body),
UNIQUE INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID)
);
#上面提到过，除了创建FTS_DOC_ID，还要在这个字段上创建一个名为FTS_DOC_ID_INDEX的唯一索引；
在表中插入数据：
mysql> insert into tb3 select NULL, "pLease porridge in the pot";
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tb3 select NULL,"please say sorry";
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tb3 select NULL,"nine years old";
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tb3 select NULL,"some like it hot, some like it cold";
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tb3 select NULL,"i like coding";
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tb3 select NULL,"fuck the company";
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
然后设置innodb_ft_aux_table的值，查看分词信息：
mysql> select * from tb3; +------------+-------------------------------------+ | FTS_DOC_ID | body | +------------+-------------------------------------+ | 1 | pLease porridge in the pot | | 2 | please say sorry | | 3 | nine years old | | 4 | some like it hot, some like it cold | | 5 | i like coding | | 6 | fuck the company | +------------+-------------------------------------+ 6 rows in set (0.00 sec) mysql> set global innodb_ft_aux_table="mytest/tb3"; #设置查看分词信息 Query OK, 0 rows affected (0.00 sec) mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from innodb_ft_index_table; #MySQL5.7中加入了一个cache表，这时候table表时空的 Empty set (0.01 sec) mysql> select * from innodb_ft_index_cache; +----------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +----------+--------------+-------------+-----------+--------+----------+ | coding | 5 | 5 | 1 | 5 | 7 | | cold | 4 | 4 | 1 | 4 | 31 | | company | 6 | 6 | 1 | 6 | 9 | | fuck | 6 | 6 | 1 | 6 | 0 | | hot | 4 | 4 | 1 | 4 | 13 | | like | 4 | 5 | 2 | 4 | 5 | | like | 4 | 5 | 2 | 4 | 18 | | like | 4 | 5 | 2 | 5 | 2 | | nine | 3 | 3 | 1 | 3 | 0 | | old | 3 | 3 | 1 | 3 | 11 | | please | 1 | 2 | 2 | 1 | 0 | | please | 1 | 2 | 2 | 2 | 0 | | porridge | 1 | 1 | 1 | 1 | 7 | | pot | 1 | 1 | 1 | 1 | 23 | | say | 2 | 2 | 1 | 2 | 7 | | some | 4 | 4 | 1 | 4 | 0 | | some | 4 | 4 | 1 | 4 | 18 | | sorry | 2 | 2 | 1 | 2 | 11 | | years | 3 | 3 | 1 | 3 | 5 | +----------+--------------+-------------+-----------+--------+----------+ 19 rows in set (0.00 sec)
有关innodb_ft_index_cache的作用：
The INNODB_FT_INDEX_CACHE table provides token information about newly inserted rows in a FULLTEXT index. To avoid expensive index reorganization
during DML operations, the information about newly indexed words is stored separately, and combined with the main search index only when OPTIMIZE
TABLE is run, when the server is shut down, or when the cache size exceeds a limit defined by the innodb_ft_cache_size or innodb_ft_total_cache_size
system variable.
#INNODB_FT_INDEX_CACHE提供关于新插入全文索引行的信息，为了避免在DML操作期间，昂贵的索引重组，新索引字段的信息单独存储。只有当运行OPTIMIZE TABLE命令，或者服务器
关机，或者缓存的大小超过innodb_ft_cache_size或者innodb_ft_total_cache_size的限制时，才会合并索引信息。
执行命令，合并全文索引的信息：
mysql> set global innodb_optimize_fulltext_only = ON; Query OK, 0 rows affected (0.00 sec) mysql> optimize table mytest.tb3; +------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+----------+----------+----------+ | mytest.tb3 | optimize | status | OK | +------------+----------+----------+----------+ 1 row in set (0.02 sec) mysql> select * from innodb_ft_index_cache; #执行之后，缓存中没有数据，table表中有对应的分词信息。 Empty set (0.00 sec) mysql> select * from innodb_ft_index_table; +----------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +----------+--------------+-------------+-----------+--------+----------+ | coding | 5 | 5 | 1 | 5 | 7 | | cold | 4 | 4 | 1 | 4 | 31 | | company | 6 | 6 | 1 | 6 | 9 | | fuck | 6 | 6 | 1 | 6 | 0 | | hot | 4 | 4 | 1 | 4 | 13 | | like | 4 | 5 | 2 | 4 | 5 | | like | 4 | 5 | 2 | 4 | 18 | | like | 4 | 5 | 2 | 5 | 2 | | nine | 3 | 3 | 1 | 3 | 0 | | old | 3 | 3 | 1 | 3 | 11 | | please | 1 | 2 | 2 | 1 | 0 | | please | 1 | 2 | 2 | 2 | 0 | | porridge | 1 | 1 | 1 | 1 | 7 | | pot | 1 | 1 | 1 | 1 | 23 | | say | 2 | 2 | 1 | 2 | 7 | | some | 4 | 4 | 1 | 4 | 0 | | some | 4 | 4 | 1 | 4 | 18 | | sorry | 2 | 2 | 1 | 2 | 11 | | years | 3 | 3 | 1 | 3 | 5 | +----------+--------------+-------------+-----------+--------+----------+ 19 rows in set (0.00 sec)
删除全文索引的一行记录：
mysql> delete from tb3 where fts_doc_id=3; Query OK, 1 row affected (0.05 sec) mysql> select * from information_schema.innodb_ft_deleted; +--------+ | DOC_ID | +--------+ | 3 | +--------+ 1 row in set (0.00 sec)
#The INNODB_FT_DELETED table stores rows that are deleted from the FULLTEXT index for an InnoDB table. To avoid expensive index reorganization
during DML operations for an InnoDB FULLTEXT index, the information about newly deleted words is stored separately, filtered out of search
results when you do a text search, and removed from the main search index only when you issue an OPTIMIZE TABLE statement for the InnoDB table.
#删除的时候并不会真实的删除，只是把删除的row记录在INNODB_FT_DELETED表中，查询的时候会过滤掉，如果你执行了OPTIMIZE TABLE语句，那么才会真正的删除。
mysql> set global innodb_optimize_fulltext_only = ON; Query OK, 0 rows affected (0.00 sec) mysql> optimize table mytest.tb3; #执行完这条语句之后，才会真正的删除。 +------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+----------+----------+----------+ | mytest.tb3 | optimize | status | OK | +------------+----------+----------+----------+ 1 row in set (0.03 sec)
mysql> select * from information_schema.innodb_ft_being_deleted;
+--------+
| DOC_ID |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
#The INNODB_FT_BEING_DELETED table provides a snapshot of the INNODB_FT_DELETED table; it is used only during an OPTIMIZE TABLE maintenance
operation. When OPTIMIZE TABLE is run, the INNODB_FT_BEING_DELETED table is emptied, and DOC_ID values are removed from the INNODB_FT_DELETED
table. Because the contents of INNODB_FT_BEING_DELETED typically have a short lifetime, this table has limited utility for monitoring or
debugging.
#INNODB_FT_BEING_DELETED表是INNODB_FT_DELETED的快照，在手动运行OPTIMIZE TABLE期间使用，当OPTIMIZE TABLE运行时，INNODB_FT_BEING_DELETED表被清空，并且DOC_ID
#的值从INNODB_FT_DELETED中删除，因为INNODB_FT_BEING_DELETED表中的内容生命周期很短，因此使用这个表的作用是有限的。
stopword：在分词操作时，有些词因为没有实际的意义，并不需要对其进行分次操作。例如，对于定冠词the。在innodb存储引擎中有一张默认的stopword表，如下！
mysql> show create table information_schema.INNODB_FT_DEFAULT_STOPWORD\G *************************** 1. row *************************** Table: INNODB_FT_DEFAULT_STOPWORD Create Table: CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT_STOPWORD` ( `value` varchar(18) NOT NULL DEFAULT '' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>
除了使用innodb存储引擎默认的stopword表，用户还可以使用innodb_ft_server_stopword_table来定义stopword列表。
mysql> create table user_stopword( value varchar(30) ); Query OK, 0 rows affected (0.04 sec) mysql> set global innodb_ft_server_stopword_table="mytest/user_stopword"; Query OK, 0 rows affected (0.00 sec)
innodb存储引擎的全文索引还存在以下限制：
- 每张表只能有一个全文索引。
- 由多列组合而成的全文索引必须使用相同的字符集与排序规则。
- 不支持没有单词界定符的语言，如中文，日语，韩语等。
博文中的内容摘自： MySQL技术内幕-innodb存储引擎。以及官方文档。
