14.6.2.4 InnoDB FULLTEXT 索引
在基于文本的列(CHAR,VARCHAR或TEXT列)上创建FULLTEXT
索引,以帮助加快对这些列中包含的数据的查询和 DML 操作,而忽略定义为停用词的任何单词。
FULLTEXT
索引被定义为CREATE TABLE语句的一部分,或使用ALTER TABLE或CREATE INDEX添加到现有 table 中。
使用MATCH()...反对语法执行全文搜索。有关用法信息,请参见第 12.9 节“全文搜索功能”。
本节以下主题下介绍了InnoDB
FULLTEXT
索引:
InnoDB 全文索引设计
InnoDB
FULLTEXT
索引具有倒排索引设计。倒排索引存储一个单词列 table,对于每个单词,存储单词出现的文档列 table。为了支持邻近搜索,每个单词的位置信息也作为字节偏移量存储。
InnoDB 全文索引 table
创建InnoDB
FULLTEXT
索引时,将创建一组索引 table,如以下示例所示:
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> 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 |
+----------+----------------------------------------------------+-------+
前六个 table 代 table 反向索引,并称为辅助索引 table。对传入文档进行标记时,各个单词(也称为“令牌”)与位置信息和关联的文档 ID(DOC_ID
)一起插入索引 table 中。根据单词第一个字符的字符集排序权重,单词在六个索引 table 中得到完全排序和分区。
倒排索引分为六个辅助索引 table,以支持并行索引创建。默认情况下,两个线程对索引 table 中的单词和相关数据进行标记化,排序和插入。线程数可使用innodb_ft_sort_pll_degree选项配置。在大型 table 上创建FULLTEXT
索引时,请考虑增加线程数。
辅助索引 table 名称的前缀为FTS_
,后缀为INDEX_*
。每个索引 table 都通过与索引 table 的table_id
匹配的索引 table 名称中的十六进制值与索引 table 相关联。例如,test/opening_lines
table 的table_id
是327
,其十六进制值为 0x147.如前面的示例所示,十六进制值“ 147”出现在与test/opening_lines
table 关联的索引 table 的名称中。
table 示FULLTEXT
索引的index_id
的十六进制值也出现在辅助索引 table 名称中。例如,在辅助 table 名称test/FTS_0000000000000147_00000000000001c9_INDEX_1
中,十六进制值1c9
的十进制值为 457.可以通过在INFORMATION_SCHEMA.INNODB_SYS_INDEXEStable 中查询该值(457)来标识在opening_lines
table(idx
)上定义的索引。
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 |
+----------+------+----------+-------+
如果主 table 是在file-per-tabletable 空间中创建的,则索引 table 将存储在其自己的 table 空间中。
上一示例中显示的其他索引 table 称为通用索引 table,用于删除处理和存储FULLTEXT
索引的内部状态。与为每个全文索引创建的倒排索引 table 不同,这组 table 对于在特定 table 上创建的所有全文索引是共有的。
即使删除了全文索引,也会保留公用辅助 table。删除全文索引时,将保留为索引创建的FTS_DOC_ID
列,因为删除FTS_DOC_ID
列将需要重建 table。需要通用的辅助 table 来 ManagementFTS_DOC_ID
列。
FTS_*_DELETED
和FTS_*_DELETED_CACHE
包含已删除但其数据尚未从全文索引中删除的文档的文档 ID(DOC_ID)。 FTS_*_DELETED_CACHE
是FTS_*_DELETED
table 的内存版本。
FTS_*_BEING_DELETED
和FTS_*_BEING_DELETED_CACHE
包含已删除文档的文档 ID(DOC_ID),并且其数据当前正在从全文本索引中删除。 FTS_*_BEING_DELETED_CACHE
table 是FTS_*_BEING_DELETED
table 的内存版本。
FTS_*_CONFIG
存储有关FULLTEXT
索引的内部状态的信息。最重要的是,它存储FTS_SYNCED_DOC_ID
,它标识已分析并刷新到磁盘的文档。在崩溃恢复的情况下,使用FTS_SYNCED_DOC_ID
值来标识尚未刷新到磁盘的文档,以便可以重新分析文档并将其添加回FULLTEXT
索引缓存中。要查看此 table 中的数据,请查询INFORMATION_SCHEMA.INNODB_FT_CONFIGtable。
InnoDB 全文索引缓存
插入文档后,将对其进行标记化,并将各个单词和关联的数据插入FULLTEXT
索引。即使对于小型文档,此过程也可能导致大量小的插入辅助索引 table,从而使同时访问这些 table 成为争执点。为避免此问题,InnoDB
使用FULLTEXT
索引缓存来临时缓存最近插入的行的索引 table 插入。此内存中的高速缓存结构将保留插入,直到高速缓存已满,然后将其批量刷新到磁盘(至辅助索引 table)。您可以查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHEtable 来查看最近插入的行的标记化数据。
缓存和批处理刷新行为避免了对辅助索引 table 的频繁更新,这可能会在繁忙的插入和更新时间期间导致并发访问问题。批处理技术还避免了同一单词的多次插入,并最大程度地减少了重复 Importing。代替单独刷新每个单词,对同一单词的插入进行合并并作为单个条目刷新到磁盘,从而提高了插入效率,同时使辅助索引 table 尽可能小。
innodb_ft_cache_size变量用于配置全文索引缓存大小(基于每个 table),这会影响刷新全文索引缓存的频率。您还可以使用innodb_ft_total_cache_size选项为给定实例中的所有 table 定义全局全文索引缓存大小限制。
全文索引缓存存储与辅助索引 table 相同的信息。但是,全文索引缓存仅缓存最近插入的行的标记化数据。查询时,已刷新到磁盘(到全文本辅助 table)的数据不会带回到全文本索引高速缓存中。直接查询辅助索引 table 中的数据,并将辅助索引 table 中的结果与全文索引缓存中的结果合并,然后再返回。
InnoDB 全文索引文档 ID 和 FTS_DOC_ID 列
InnoDB
使用称为文档 ID(DOC_ID
)的唯一文档标识符将全文索引中的单词 Map 到单词出现的文档记录。Map 要求索引 table 上有一个FTS_DOC_ID
列。如果未定义FTS_DOC_ID
列,则在创建全文索引时InnoDB
会自动添加隐藏的FTS_DOC_ID
列。下面的示例演示了此行为。
下 table 定义不包含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;
当您使用CREATE FULLTEXT INDEX
语法在 table 上创建全文索引时,将返回一条警告,报告InnoDB
正在重建 table 以添加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向没有FTS_DOC_ID
列的 table 中添加全文索引时,将返回相同的警告。如果您在CREATE TABLE时间创建全文索引并且未指定FTS_DOC_ID
列,则InnoDB
将添加隐藏的FTS_DOC_ID
列,而不会发出警告。
与在已加载数据的 table 上创建全文索引相比,在CREATE TABLE时定义FTS_DOC_ID
列要便宜。如果在加载数据之前在 table 上定义了FTS_DOC_ID
列,则不必重建 table 及其索引即可添加新列。如果您不关心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
(全部大写),如以下示例所示:
Note
不必将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;
如果您选择自己定义FTS_DOC_ID
列,则您有责任 Management 该列,以免出现空值或重复值。 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);
如果您没有创建FTS_DOC_ID_INDEX
,则InnoDB
自动创建它。
在 MySQL 5.7.13 之前,使用的最大FTS_DOC_ID
值和新FTS_DOC_ID
值之间的允许间隙为 10000.在 MySQL 5.7.13 及更高版本中,允许的间隙为 65535.
为避免重建 table,删除全文索引时将保留FTS_DOC_ID
列。
InnoDB 全文索引删除处理
删除具有全文索引列的记录可能会导致辅助索引 table 中的许多小删除,从而使对这些 table 的并发访问成为争用点。为避免此问题,每当从索引 table 中删除记录时,已删除文档的文档 ID(DOC_ID
)就会记录在特殊的FTS_*_DELETED
table 中,并且索引记录仍保留在全文索引中。返回查询结果之前,FTS_*_DELETED
table 中的信息用于过滤出已删除的文档 ID。这种设计的好处是删除快速且便宜。缺点是删除记录后不会立即减小索引的大小。要删除已删除记录的全文索引条目,请在带有的索引 table 上运行OPTIMIZE TABLE
以重建全文索引。有关更多信息,请参见优化 InnoDB 全文索引。
InnoDB 全文索引事务处理
InnoDB
FULLTEXT
索引具有缓存和批处理行为,因此具有特殊的事务处理特性。具体来说,FULLTEXT
索引上的更新和插入在事务提交时进行处理,这意味着FULLTEXT
搜索只能看到提交的数据。下面的示例演示了此行为。 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;
mysql> BEGIN;
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');
mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
mysql> COMMIT;
mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
监视 InnoDB 全文索引
您可以通过查询以下INFORMATION_SCHEMA
table 来监视和检查InnoDB
FULLTEXT
索引的特殊文本处理方面:
您还可以通过查询INNODB_SYS_INDEXES和INNODB_SYS_TABLES来查看FULLTEXT
索引和 table 的基本信息。
有关更多信息,请参见第 14.16.4 节“ InnoDB INFORMATION_SCHEMA FULLTEXT 索引 table”。