MySQL InnoDB全文索引

InnoDB全文索引
FULLTEXT索引是在基于文本的列(CHAR、VARCHAR或TEXT列)上创建的，以帮助加快对这些列中包含的数据的查询和DML操作，从而省略定义为停止词的任何单词。FULLTEXT索引定义为CREATE TABLE语句的一部分，或者使用ALTER TABLE或CREATE index将其添加到现有表中。全文搜索使用MATCH()…对语法。

InnoDB全文索引设计
InnoDB FULLTEXT索引采用倒排索引设计。倒排索引存储一个单词列表，对于每个单词，存储该单词出现在其中的文档列表。为了支持邻近搜索，每个字的位置信息也以字节偏移量的形式存储。

InnoDB全文索引表
当创建一个InnoDB FULLTEXT索引时，会创建一组索引表，示例如下:

mysql> CREATE TABLE opening_lines (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200),
    -> FULLTEXT idx (opening_line)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'undo/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|      453 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_1 |   452 |
|      454 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_2 |   453 |
|      455 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_3 |   454 |
|      456 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_4 |   455 |
|      457 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_5 |   456 |
|      458 | undo/FTS_00000000000001bf_00000000000002bb_INDEX_6 |   457 |
|      448 | undo/FTS_00000000000001bf_BEING_DELETED            |   447 |
|      449 | undo/FTS_00000000000001bf_BEING_DELETED_CACHE      |   448 |
|      450 | undo/FTS_00000000000001bf_CONFIG                   |   449 |
|      451 | undo/FTS_00000000000001bf_DELETED                  |   450 |
|      452 | undo/FTS_00000000000001bf_DELETED_CACHE            |   451 |
|      447 | undo/opening_lines                                 |   446 |
+----------+----------------------------------------------------+-------+
12 rows in set (0.00 sec)

前六个表表示倒排索引，称为辅助索引表。在对传入文档进行标记时，将单个单词(也称为“标记”)与位置信息和相关文档ID (DOC_ID)一起插入到索引表中。根据单词第一个字符的字符集排序权重，将单词完全排序并在六个索引表中进行分区。

倒排索引被划分为六个辅助索引表，以支持并行索引创建。默认情况下，两个线程对单词和相关数据进行标记、排序和插入索引表。线程的数量可以使用innodb_ft_sort_pll_degree选项进行配置。考虑在大型表上创建FULLTEXT索引时增加线程数。

辅助索引表名的前缀是FTS_，后缀是INDEX_*。每个索引表通过索引表名称中的十六进制值与被索引表相关联，该值与被索引表的table_id相匹配。例如，test/opening_lines表的table_id为447，其十六进制值为0x1bf。如前面的示例所示，“1bf”十六进制值出现在与test/opening_lines表相关联的索引表的名称中。

表示FULLTEXT索引的index_id的十六进制值也出现在辅助索引表名中。例如，在辅助表名undo/FTS_00000000000001bf_00000000000002bb_INDEX_1中，十六进制值2bb的十进制值为699。opening_lines表(idx)上定义的索引可以通过查询INFORMATION_SCHEMA来识别。INNODB_SYS_INDEXES表的值(699)。

mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id=699;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      699 | idx  |      447 |   446 |
+----------+------+----------+-------+
1 row in set (0.00 sec)

如果主表是在每个表文件的表空间中创建的，则索引表存储在它们自己的表空间中。

前面示例中显示的其他索引表被称为公共索引表，用于删除处理和存储FULLTEXT索引的内部状态。与为每个全文索引创建的倒排索引表不同，这组表对于在特定表上创建的所有全文索引都是通用的。

即使删除全文索引，也会保留常见的辅助表。当全文索引被删除时，为索引创建的FTS_DOC_ID列将被保留，因为删除FTS_DOC_ID列将需要重建表。管理FTS_DOC_ID列需要使用普通的辅助表。
.FTS_*_DELETED and FTS_*_DELETED_CACHE

包含已删除但其数据尚未从全文索引中删除的文档的文档id (DOC_ID)。FTS_*_DELETED_CACHE是FTS_*_DELETED表的内存版本。

.FTS_*_BEING_DELETED and FTS_*_BEING_DELETED_CACHE
包含要删除的文档的文档id (DOC_ID)，这些文档的数据目前正在从全文索引中删除。FTS_*_BEING_DELETED_CACHE表是FTS_*_BEING_DELETED表的内存版本。

.FTS_*_CONFIG
存储关于FULLTEXT索引的内部状态的信息。最重要的是，它存储FTS_SYNCED_DOC_ID，它标识已解析并刷新到磁盘的文档。在崩溃恢复的情况下，FTS_SYNCED_DOC_ID值用于标识尚未刷新到磁盘的文档，以便可以重新解析文档并将其添加回FULLTEXT索引缓存。要查看该表中的数据，请查询INFORMATION_SCHEMA.INNODB_FT_CONFIG表。

InnoDB全文索引缓存
插入文档时，将对其进行标记，并将单个单词和相关数据插入到FULLTEXT索引中。这个过程，即使对于小文档，也可能导致对辅助索引表进行大量小的插入，从而使对这些表的并发访问成为争用点。为了避免这个问题，InnoDB使用FULLTEXT索引缓存来临时缓存索引表中最近插入的行。这个内存缓存结构保存插入，直到缓存满，然后将它们批量刷新到磁盘(到辅助索引表)。可以查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE表用于查看最近插入的行的标记化数据。

缓存和批处理刷新行为避免了对辅助索引表的频繁更新，这可能在繁忙的插入和更新期间导致并发访问问题。批处理技术还避免了对同一个单词的多次插入，并最大限度地减少了重复条目。不是逐个刷新每个单词，而是将相同单词的插入合并并作为单个条目刷新到磁盘，从而提高了插入效率，同时使辅助索引表尽可能小。

innodb_ft_cache_size变量用于配置全文索引缓存大小(以每个表为基础)，它影响全文索引缓存刷新的频率。您还可以使用innodb_ft_total_cache_size选项为给定实例中的所有表定义全局全文索引缓存大小限制。

全文索引缓存存储与辅助索引表相同的信息。但是，全文索引缓存仅为最近插入的行缓存标记化的数据。查询时，已经刷新到磁盘(全文辅助表)的数据不会被带回到全文索引缓存中。直接查询辅助索引表中的数据，并且在返回之前，将辅助索引表中的结果与全文索引缓存中的结果合并。

InnoDB全文索引文档ID和FTS_DOC_ID列
InnoDB使用一个唯一的文档标识符，即文档ID (DOC_ID)，将全文索引中的单词映射到该单词出现的文档记录。映射需要索引表上的FTS_DOC_ID列。如果没有定义FTS_DOC_ID列，InnoDB会在创建全文索引时自动添加一个隐藏的FTS_DOC_ID列。下面的示例演示了这种行为。

下面的表定义不包含FTS_DOC_ID列:

mysql> CREATE TABLE opening_lines (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

当使用create FULLTEXT index语法在表上创建全文索引时，会返回一个警告，报告InnoDB正在重建表以添加FTS_DOC_ID列。

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

当使用ALTER TABLE向没有FTS_DOC_ID列的表添加全文索引时，也会返回相同的警告。如果你在create TABLE时间创建一个全文索引，并且没有指定FTS_DOC_ID列，InnoDB会添加一个隐藏的FTS_DOC_ID列，没有警告。

在CREATE TABLE时定义一个FTS_DOC_ID列比在一个已经加载了数据的表上创建一个全文索引要便宜。如果在加载数据之前在表上定义了FTS_DOC_ID列，则不必重新构建表及其索引来添加新列。如果你不关心CREATE FULLTEXT INDEX的性能，可以省略FTS_DOC_ID列，让InnoDB为你创建它。InnoDB创建一个隐藏的FTS_DOC_ID列，并在FTS_DOC_ID列上创建一个唯一的索引(FTS_DOC_ID_INDEX)。如果你想创建自己的FTS_DOC_ID列，该列必须定义为BIGINT UNSIGNED NOT NULL，并命名为FTS_DOC_ID(全大写)，如下所示:

FTS_DOC_ID列不需要定义为AUTO_INCREMENT列，但是AUTO_INCREMENT可以使加载数据更容易。

mysql> CREATE TABLE opening_lines (
    -> FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

如果选择自己定义FTS_DOC_ID列，则负责管理该列，以避免空值或重复值。FTS_DOC_ID的值不能重复使用，这意味着FTS_DOC_ID的值必须不断增加。

可选地，您可以在FTS_DOC_ID列上创建所需的惟一FTS_DOC_ID_INDEX(全部大写)。

mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果你没有创建FTS_DOC_ID_INDEX, InnoDB会自动创建它。

在MySQL 5.7.13之前，已使用的FTS_DOC_ID值与新的FTS_DOC_ID值之间允许的最大差值为10000。在MySQL 5.7.13及以后版本中，允许的间隙是65535。

为了避免重新构建表，在删除全文索引时保留FTS_DOC_ID列。

InnoDB全文索引删除处理
删除具有全文索引列的记录可能会导致辅助索引表中出现大量小的删除，从而使对这些表的并发访问成为争用点。为了避免这个问题，被删除文档的文档ID (DOC_ID)记录在一个特殊的FTS_*_DELETED表中，当一条记录从索引表中删除时，被索引的记录保留在全文索引中。在返回查询结果之前，使用FTS_*_DELETED表中的信息过滤已删除的Document id。这种设计的好处是，删除是快速和廉价的。缺点是在删除记录后索引的大小不会立即减小。要删除已删除记录的全文索引项，可以在已索引的表上运行OPTIMIZE TABLE命令(innodb_optimize_fulltext_only= on)重建全文索引。

InnoDB全文索引事务处理
InnoDB FULLTEXT索引由于其缓存和批处理行为而具有特殊的事务处理特性。具体来说，FULLTEXT索引上的更新和插入是在事务提交时处理的，这意味着FULLTEXT搜索只能看到提交的数据。下面的示例演示了这种行为。FULLTEXT搜索只在提交插入的行之后返回结果。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
    -> ('Call me Ishmael.','Herman Melville','Moby-Dick'),
    -> ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
    -> ('I am an invisible man.','Ralph Ellison','Invisible Man'),
    -> ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
    -> ('It was love at first sight.','Joseph Heller','Catch-22'),
    -> ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
    -> ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
    -> ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)