技术学习生活分享 -【MySQL5.7官方文档翻译：InnoDB FULLTEXT indexes】

MySQL5.7官方文档翻译：InnoDB FULLTEXT indexes

InnoDB全文索引

全文索引被创建在文本列（CHAR, VARCHAR, TEXT）上，用来加快查询和包含这些列的DML操作，以及忽略任何被定义的非用词。

全文索引能被定义作为CREATE TABLE语句的一部分，或者通过ALTER TABLE和CREATE INDEX添加。

执行全文搜索使用MATCH()……AGAINST()语法。

下面是这个章节涵盖的InnoDB全文索引的的几个方面：

InnoDB全文索引设计
InnoDB全文索引表
InnoDB全文索引缓存
InnoDB全文索引Document ID和FTS_DOC_ID列
InnoDB全文索引删除处理
InnoDB全文索引事务处理
监控InnoDB全文索引

InnoDB全文索引设计

InnoDB全文索引使用倒排索引实现，倒排索引存储一系列的词和每个词出现的文件列表。为了支持模糊查询，每个词出现位置的字节偏移量也被存储。

InnoDB全文索引表

对于每一个InnoDB全文索引，一系列的索引表被创建，如下：

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;

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES
	WHERE name LIKE 'test/%';

+----------+----------------------------------------------------+-------+
| table_id | name | space |
+----------+----------------------------------------------------+-------+
| 333 | test/FTS_0000000000000147_00000000000001c9_INDEX_1 | 289 |
| 334 | test/FTS_0000000000000147_00000000000001c9_INDEX_2 | 290 |
| 335 | test/FTS_0000000000000147_00000000000001c9_INDEX_3 | 291 |
| 336 | test/FTS_0000000000000147_00000000000001c9_INDEX_4 | 292 |
| 337 | test/FTS_0000000000000147_00000000000001c9_INDEX_5 | 293 |
| 338 | test/FTS_0000000000000147_00000000000001c9_INDEX_6 | 294 |
| 330 | test/FTS_0000000000000147_BEING_DELETED | 286 |
| 331 | test/FTS_0000000000000147_BEING_DELETED_CACHE | 287 |
| 332 | test/FTS_0000000000000147_CONFIG | 288 |
| 328 | test/FTS_0000000000000147_DELETED | 284 |
| 329 | test/FTS_0000000000000147_DELETED_CACHE | 285 |
| 327 | test/opening_lines | 283 |
+----------+----------------------------------------------------+-------+

前六张表实现了倒排索引，被称为辅助索引表。当传入的文件被标识，单词（也被称为‘标记’）连同位置信息以及相关的Document ID被插入索引表。这些单词根据第一个字符在字符集上的排序权重被排序划分在这六张表上。

倒排索引为了支持并行索引的创建被划分到六个辅助索引表。默认情况下，两个线程用来标识，排序，插入单词和相关的数据到索引表。线程的数量可以通过innodb_ft_pll_degree配置。当在大型表上创建全文索引的时候，可以考虑增加线程的数量。

辅助索引表以前缀为FTS_和后缀为index_*来命名。每个辅助索引表通过名字上的16进制数和被索引的表的table_id匹配进行关联。例如：test/opening_lines的table_id是327，327的16进制是0x147,如前表，147出现在名字上的辅助索引表都被关联到test/opening_lines。

全文索引的index_id的16进制数也出现在辅助索引表的名字里。例如：辅助索引表test/FTS_0000000000000147_00000000000001c9_INDEX_1,16进制0x1c9的十进制为457，被定义在open_lines上的索引idx可以通过查询INFORMATION_SCHEMA.INNODB_SYS_INDEXES里面index_id为457进行确认。

mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
	WHERE index_id=457;

+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
| 457 | idx | 327 | 283 |
+----------+------+----------+-------+

如果全文索引表创建在单表空间(即每个表有单独的.ibd文件)里，那么索引表被存储在自己的表空间中。

前面例子里面的其他索引表被用做删除处理和存储全文索引的内部状态。

FTS_*_DELETED和FTS_*_DELETED_CACHE:包含被删除的但是还没有从全文索引移除的文本的Document IDS(DOC_ID)。FTS_*_DELETED_CACHE是FTS_*_DELETED在内存中的版本。
FTS_*_BEING_DELETED和FTS_*_BEING_DELETED_CACHE:包含被删除的并且数据正在从全文索引的进程中移除的Document IDS(DOC_ID)。FTS_*_BEING_DELETED_CACHE是FTS_*_BEING_DELETED在内存中的版本。
FTS_*_CONFIG存储全文索引的内部状态，最重要的是，它存储了标志文件被解析和冲刷到磁盘的FTS_SYNCED_DOC_ID。在崩溃恢复时，FTS_SYNCED_DOC_ID的值被用来标识那些没有冲刷到磁盘的文件，以便于重新解析和添加回全文索引缓存。通过查询INFORMATION_SCHEMA.INNODB_TF_CONFIG来查看这个表的数据。

InnoDB全文索引缓存

当行被插入的时候，数据被标记，并且单词和相关的数据会插入全文索引。在这个过程中，即使是非常小的行也会有许多小插入到辅助索引表，使得这些表的并发访问有争用。为避免这个问题，InnoDB使用全文索引缓存临时缓存最近插入索引表的行。缓存在内存中的数据直到缓存被填满，然后批量冲刷到磁盘（辅助索引表）。通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE可以查看最近插入的被标记的数据。

缓存和批量冲刷行为避免了频繁更新辅助索引表导致的密集插入和更新时的并发访问问题。批处理技术也避免了多次插入同一个词和最大限度减少重复的条目。取代冲刷每个词，插入的同一个词被合并然后作为一个条目冲刷到磁盘，提高插入效率，同时保持辅助索引表尽可能的小。

innodb_ft_cache_size变量用来配置全文索引的缓存大小（以表为基础），这个配置影响缓存的冲刷频率。你也可以在给定的实例中使用innodb_ft_total_cache_size选项来配置所有的表的缓存总大小。

全文索引缓存和辅助索引表存储相同的信息。但是，全文索引缓存只缓存最近被插入的被标识的行。已经冲刷到磁盘的数据（辅助索引表）在查询的时候不会走缓存。直接查询辅助索引表返回的结果集是辅助索引表的结果与全文索引缓存内部数据（被冲刷到磁盘之前的缓存数据）结果的合集。

Innodb全文索引Document ID与FTS_DOC_ID列

Innodb使用唯一的文档标识符即Document ID(DOC_ID)去映射全文索引中的词与这些词出现的列。这个映射关系依赖被索引表上的FTS_DOC_ID列。如果FTS_DOC_ID列没有定义，Innodb会在创建全文索引的时候自动创建隐藏的FTS_DOC_ID列。下面的例子演示了这个行为。

下表定义没有包含FTS_DOC_ID列：

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;

当你使用CREATE TABLE INDEX语法创建全文索引的时候，会返回一个警告：Innodb正在重建这张表来添加FTS_DOC_ID列。

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

+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+

使用ALTER TABLE会有同样的警告。如果你在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，如下例子：

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;

小贴士：FTS_DOC_ID列不必定义为AUTO_INCREMENT，但是这样方便导入数据。

如果你选择自己定义FTS_DOC_ID列，那么你必须要保证它不能为空和不能重复。FTS_DOC_ID的值不能重用，也就意味着这个值必须不断增加。

可选的，你可以创建唯一索引FTS_DOC_ID_INDEX（全大写）在FTS_DOC_ID列上。

CREATE TABLE UNIQUE INDEX FTS_DOC_ID_INDEX ON opening_lines(FTS_DOC_ID);

如果你没有创建FTS_DOC_ID_INDEX，Innodb会自动创建。

在MYSQL5.7.13之前，被使用的最大的FTS_DOC_ID值与新的FTS_DOC_ID值的差距10000，在MYSQL5.7.13以及之后的版本，最大可以是65535。

InnoDB全文索引删除处理

删除一个全文索引的纪录会导致辅助索引表上的许多小删除，使这些表的并发访问有争用。为了避免这个问题，每当在被索引的表中删除记录，同时这条记录仍然在全文索引中，就记录被删除文件的Document ID（DOC_ID）在FTS_*_DELETED里面。在返回查询结果之前，FTS_*_DELETED里面的信息用来过滤被删除的Document IDs。这种设计的好处是删除操作是快速和廉价的。缺点是索引的大小没有在删除记录后立即减小。为了移除被删除的全文索引记录的条目，你必须在被设置了innodb_optimize_fulltext_only = ON 的表上运行OPTIMIZE TABLE来重建全文索引。

InnoDB全文索引事务处理

全文索引由于它的缓存和批处理行为使其具有特殊的事务处理特征。具体来说，更新和插入在事务提交时被执行，这也就意味着，全文搜索只能看到已经提交的数据。下面的例子演示了这个行为。全文索引仅在插入行被提交之后返回一个结果。

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;

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 |
+----------+
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+

监控InnoDB全文索引

你可以通过查询INFORMATION_SCHEMA里面的表来监控和检查InnoDB全文索引特殊的文本处理：

INNODB_FT_CONFIG
INNODB_FT_INDEX_TABLE
INNODB_FT_INDEX_CACHE
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_DELETED
INNODB_FT_BEING_DELETED

你也能通过查询INNODB_SYS_INDEXES和INNODB_SYS_TABLES来查看全文索引和表的基础信息。

2018.02.03更新一波

上面提到可以自己创建FTS_DOC_ID列和索引，这样方便查看索引表里面的数据对应哪条数据。

但是今天发现一个问题，人工创建FTS_DOC_ID列和索引的时候，在更新的时候会产生错误，如下：

[HY000][182] Invalid InnoDB FTS Doc ID

手册上有相应的说明：

在表创建时添加FTS_DOC_ID列，请确保在更新FULLTEXT索引列时更新FTS_DOC_ID列，因为FTS_DOC_ID必须随每个INSERT或UPDATE单调递增。如果您选择不在表创建时添加FTS_DOC_ID，并让InnoDB为您管理DOC ID，InnoDB将添加FTS_DOC_ID作为隐藏列，并进行下一个CREATE FULLTEXT INDEX调用。但是，这种方法需要重建表，会影响性能。

上面只是说明了如何做，但是并没有说产生这个问题的原因，其实原因很简单：

当我们更新数据的时候，其实全文索引内部做了这样几件事情：

将被更新数据的FTS_DOC_ID移动到INNODB_FT_DELETED表，标记为删除。INNODB_FT_INDEX_TABLE不变
给更新的数据条目指定新的FTS_DOC_ID（也就是上面说的单调递增，否则可能会重复）
查询的时候根据INNODB_FT_INDEX_TABLE查询出所有的结果，然后去INNODB_FT_DELETED过滤掉删除的数据。

所以，当我们想更新数据并保持FTS_DOC_ID不变的时候，如果这样成功了，那我们检索的时候就会一直把这条数据过滤掉，所以MySQL这时候会出现错误，让你强制更新FTS_DOC_ID列。

其实就是内部机制使然。

调试方法论

通过下面这些sql可以查看全文索引情况：

SET GLOBAL innodb_ft_aux_table = 'db/table';
select * from INFORMATION_SCHEMA.INNODB_FT_CONFIG;

-- 这几个比较常用
select * from INFORMATION_SCHEMA.INNODB_FT_DELETED;
select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

（完）

本文作者：吴泽辉
本文链接：https://mutex.top/posts/9cebedf4/
发表日期：2017年5月26日
版权声明：本文章为原创，采用《知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议》进行许可