全文索引是在基于文本的列(CHAR
、
VARCHAR
或
TEXT
列)上创建的,以加速对这些列中包含的数据的查询和 DML 操作。
全文索引被定义为语句的一部分
或使用
orCREATE TABLE
添加到现有表中。
ALTER TABLE
CREATE INDEX
全文搜索是使用MATCH()
... AGAINST
语法执行的。有关使用信息,请参阅
第 12.10 节,“全文搜索功能”。
InnoDB
全文索引在本节的以下主题下进行了描述:
创建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> 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 |
+----------+----------------------------------------------------+-------+
前六张索引表构成倒排索引,称为辅助索引表。当传入文档被标记化时,单个单词(也称为
“标记”)连同位置信息和关联的
DOC_ID
. 基于单词第一个字符的字符集排序权重,单词在六个索引表中被完全排序和分区。
倒排索引被划分为六个辅助索引表,以支持并行索引创建。默认情况下,两个线程对单词和相关数据进行标记、排序和插入到索引表中。执行此工作的线程数可使用该
innodb_ft_sort_pll_degree
变量进行配置。在大型表上创建全文索引时,请考虑增加线程数。
辅助索引表名称以 为前缀
fts_
和后缀
。每个辅助索引表通过与索引表的匹配的辅助索引表名称中的十六进制值与索引表相关联
。例如,表
的 是
,其十六进制值为 0x147。如上例所示,“ 147 ”十六进制值出现在与该表关联的辅助索引表的名称中。
index_
#
table_id
table_id
test/opening_lines
327
test/opening_lines
index_id
表示全文索引
的十六进制值也出现在辅助索引表名中。例如,在辅助表名称
test/FTS_0000000000000147_00000000000001c9_INDEX_1
中,十六进制值1c9
的十进制值为 457。opening_lines
表 ( idx
) 上定义的索引可以通过查询
INFORMATION_SCHEMA.INNODB_SYS_INDEXES
该值 (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 |
+----------+------+----------+-------+
索引表在
innodb_file_per_table
启用时存储在它们自己的表空间中。如果
innodb_file_per_table
禁用,则索引表存储在
InnoDB
系统表空间(空间 0)中。
由于 MySQL 5.6.5 中引入的错误,启用InnoDB
时会在系统表空间(空间 0)
中创建索引表innodb_file_per_table
。该错误已在 MySQL 5.6.20 和 MySQL 5.7.5 中修复 (Bug#18635485)。
上述示例中的其他索引表称为普通索引表,用于删除处理和存储全文索引的内部状态。与为每个全文索引创建的倒排索引表不同,这组表对于在特定表上创建的所有全文索引是通用的。
即使删除了全文索引,公共索引表也会保留。当删除全文索引时,
FTS_DOC_ID
为该索引创建的列会保留,因为删除该FTS_DOC_ID
列需要重建以前索引的表。需要公共索引表来管理该
FTS_DOC_ID
列。
FTS_*_DELETED
和FTS_*_DELETED_CACHE
包含已删除但其数据尚未从全文索引中删除的文档的文档 ID (DOC_ID)。
FTS_*_DELETED_CACHE
是表的内存版本FTS_*_DELETED
。FTS_*_BEING_DELETED
和FTS_*_BEING_DELETED_CACHE
包含已删除文档的文档 ID (DOC_ID),其数据当前正在从全文索引中删除。该
FTS_*_BEING_DELETED_CACHE
表是表的内存版本FTS_*_BEING_DELETED
。FTS_*_CONFIG
存储有关全文索引的内部状态的信息。最重要的是,它存储
FTS_SYNCED_DOC_ID
,它标识已解析并刷新到磁盘的文档。在崩溃恢复的情况下,FTS_SYNCED_DOC_ID
值用于标识尚未刷新到磁盘的文档,以便可以重新解析文档并将其添加回全文索引缓存。要查看此表中的数据,请查询该INFORMATION_SCHEMA.INNODB_FT_CONFIG
表。
插入文档时,它会被标记化,单个单词和相关数据会被插入到全文索引中。这个过程,即使对于小文档,也会导致大量小的插入到辅助索引表中,从而使对这些表的并发访问成为争论的焦点。为避免此问题,InnoDB
使用全文索引缓存来临时缓存最近插入的行的索引表插入。这种内存中的缓存结构会保留插入,直到缓存已满,然后将它们批量刷新到磁盘(到辅助索引表)。您可以查询该
INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE
表以查看最近插入的行的标记化数据。
缓存和批量刷新行为避免了对辅助索引表的频繁更新,这可能导致在繁忙的插入和更新时间期间出现并发访问问题。批处理技术还避免了对同一个词的多次插入,并最大限度地减少了重复条目。不是单独刷新每个单词,而是将同一单词的插入合并并作为单个条目刷新到磁盘,从而提高插入效率,同时保持辅助索引表尽可能小。
该innodb_ft_cache_size
变量用于配置全文索引缓存大小(基于每个表),这会影响刷新全文索引缓存的频率。innodb_ft_total_cache_size
您还可以使用该变量
为给定实例中的所有表定义全局全文索引缓存大小限制
。
全文索引缓存存储与辅助索引表相同的信息。但是,全文索引缓存仅缓存最近插入行的标记化数据。已经刷新到磁盘(到辅助索引表)的数据在查询时不会带回全文索引缓存。直接查询辅助索引表中的数据,辅助索引表的结果与全文索引缓存的结果合并返回。
InnoDB
使用称为 the 的唯一文档标识符将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;
当您使用语法在表上创建全文索引时
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.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
列的表添加全文索引时返回相同的警告。如果创建全文索引CREATE TABLE
时没有指定FTS_DOC_ID
列,
则InnoDB
添加一个隐藏
FTS_DOC_ID
列,没有警告。
与在已加载数据的表上创建全文索引相比,一次
定义FTS_DOC_ID
列的
成本更低。CREATE TABLE
如果FTS_DOC_ID
在加载数据之前在表上定义了列,则不必重建表及其索引即可添加新列。如果您不关心CREATE FULLTEXT
INDEX
性能,请忽略该
FTS_DOC_ID
列以便
InnoDB
为您创建它。
InnoDB
创建一个隐藏
FTS_DOC_ID
列以及列上的唯一索引 ( FTS_DOC_ID_INDEX
)
FTS_DOC_ID
。如果要创建自己的FTS_DOC_ID
列,则必须将列定义为BIGINT UNSIGNED NOT NULL
并命名
FTS_DOC_ID
(全部大写),如以下示例所示:
列FTS_DOC_ID
不需要定义为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
列,则您有责任管理该列以避免出现空值或重复值。FTS_DOC_ID
值不能重复使用,这意味着FTS_DOC_ID
值必须不断增加。
或者,您可以在列上创建所需的唯一
FTS_DOC_ID_INDEX
(全部大写)
FTS_DOC_ID
。
mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);
如果您不创建FTS_DOC_ID_INDEX
,
InnoDB
则会自动创建它。
MySQL 5.6.31之前,最大已用
FTS_DOC_ID
值和新
FTS_DOC_ID
值之间允许的差距为10000。在MySQL 5.6.31及之后,允许的差距为65535。
为避免重建表,FTS_DOC_ID
删除全文索引时会保留该列。
删除具有全文索引列的记录可能会导致辅助索引表中的大量小删除,从而使对这些表的并发访问成为争用点。为避免此问题,每当从索引表中删除记录时DOC_ID
,已删除文档的记录都会记录在特殊
FTS_*_DELETED
表中,并且索引记录保留在全文索引中。在返回查询结果之前,FTS_*_DELETED
表中的信息用于过滤掉删除的DOC_ID
秒。这种设计的好处是删除速度快且成本低。缺点是删除记录后索引的大小不会立即减小。要删除已删除记录的全文索引条目,OPTIMIZE TABLE
请在索引表上
运行innodb_optimize_fulltext_only=ON
以重建全文索引。有关更多信息,请参阅
优化 InnoDB 全文索引。
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;
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
INNODB_SYS_INDEXES
您还可以通过查询和
查看全文索引和表的基本信息
INNODB_SYS_TABLES
。
有关更多信息,请参阅 第 14.15.4 节,“InnoDB INFORMATION_SCHEMA FULLTEXT 索引表”。