InnoDB FULLTEXT 全文索引
FULLTEXT
索引是在基于文本的列(CHAR
,VARCHAR
或TEXT
列)上创建的,以帮助加快对这些列中包含的数据的查询和DML操作,而忽略定义为停用词的所有单词。
甲FULLTEXT
指数被定义为一个的一部分CREATE TABLE
说明或使用添加到现有的表ALTER TABLE
或CREATE INDEX
。
使用MATCH()... AGAINST
语法执行全文搜索。有关用法信息,请参见“全文搜索功能”。
InnoDB
FULLTEXT
本节中的以下主题描述了索引:
- InnoDB全文索引设计
- InnoDB全文索引表
- InnoDB全文索引缓存
- InnoDB全文索引文档ID和FTS_DOC_ID列
- InnoDB全文索引删除处理
- InnoDB全文索引事务处理
- 监控InnoDB全文索引
InnoDB全文索引设计
InnoDB
FULLTEXT
索引具有倒排索引设计。倒排索引存储单词列表,并且对于每个单词,存储单词出现的文档列表。为了支持邻近搜索,每个单词的位置信息也作为字节偏移量存储。
InnoDB全文索引表
创建InnoDB
FULLTEXT
索引时,将创建一组索引表,如以下示例所示:
mysql>CREATE TABLE opening_lines ( id INTUNSIGNED AUTO_INCREMENT NOT NULLPRIMARY KEY , opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200),FULLTEXT idx (opening_line) )ENGINE =InnoDB; mysql>SELECT table_id,name , spacefrom INFORMATION_SCHEMA.INNODB_TABLESWHERE 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 | +---------- +---------------------------------------------------- +------- +
前六个表代表反向索引,并称为辅助索引表。对传入文档进行标记时,各个单词(也称为“标记”)与位置信息和关联的文档ID(DOC_ID
)一起插入索引表中。根据单词第一个字符的字符集排序权重,单词在六个索引表中得到完全排序和分区。
倒排索引分为六个辅助索引表,以支持并行索引创建。默认情况下,两个线程对索引表中的单词和相关数据进行标记化,排序和插入。线程数可以使用该innodb_ft_sort_pll_degree
选项配置。FULLTEXT
在大型表上创建索引时,请考虑增加线程数。
辅助索引表名称以前缀fts_
和后缀index_*
。每个索引表通过索引表名称中与table_id
索引表的匹配的十六进制值与索引表相关联。例如,table_id
所述的test/opening_lines
表是327
,为此,十六进制值是0x147。如前面的示例所示,十六进制值“ 147 ”出现在与该test/opening_lines
表关联的索引表的名称中。
表示的十六进制值index_id
的的FULLTEXT
索引也出现在辅助索引表名。例如,在辅助表名称中test/fts_0000000000000147_00000000000001c9_index_1
,十六进制值1c9
的十进制值为457。可以通过查询表中的该值(457)来识别opening_lines
表(idx
)上定义的索引INFORMATION_SCHEMA.INNODB_INDEXES
。
mysql>SELECT index_id,name , table_id, spacefrom INFORMATION_SCHEMA.INNODB_INDEXESWHERE index_id=457; +---------- +------ +---------- +------- + | index_id | name | table_id | space | +---------- +------ +---------- +------- + | 457 | idx | 327 | 283 | +---------- +------ +---------- +------- +
如果在每个表文件表空间中创建主表,则索引表将存储在其自己的表空间中。
上一示例中显示的其他索引表称为通用索引表,用于删除处理和存储FULLTEXT
索引的内部状态。与为每个全文索引创建的倒排索引表不同,这组表对于在特定表上创建的所有全文索引是共有的。
即使删除了全文索引,也会保留公用辅助表。删除全文索引时,将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
存储有关
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 INTUNSIGNED AUTO_INCREMENT NOT NULLPRIMARY KEY , opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200) )ENGINE =InnoDB;
当您使用CREATE FULLTEXT INDEX
语法在表上创建全文索引时,将返回警告,报告InnoDB
正在重建表以添加FTS_DOC_ID
列。
mysql>CREATE FULLTEXT INDEX idxON 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
列,但是AUTO_INCREMENT
可以使加载数据更加容易。
mysql>CREATE TABLE opening_lines ( FTS_DOC_ID BIGINTUNSIGNED AUTO_INCREMENT NOT NULLPRIMARY 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_INDEXon opening_lines(FTS_DOC_ID);
如果您未创建FTS_DOC_ID_INDEX
,InnoDB
则会自动创建。
注意
FTS_DOC_ID_INDEX
不能定义为降序索引,因为InnoDB
SQL解析器不使用降序索引。
最大FTS_DOC_ID
使用价值与新FTS_DOC_ID
价值之间的允许差距为65535。
为避免重建表,FTS_DOC_ID
删除全文索引时将保留该列。
InnoDB全文索引删除处理
删除具有全文索引列的记录可能会导致辅助索引表中的许多小删除,从而使对这些表的并发访问成为争用点。为避免此问题,每当从索引表中删除DOC_ID
记录时,已删除文档的Document ID()就会记录在特殊FTS_*_DELETED
表中,并且索引记录仍保留在全文索引中。返回查询结果之前,FTS_*_DELETED
该表用于过滤出已删除的文档ID。这种设计的好处是删除既快速又便宜。缺点是删除记录后不会立即减小索引的大小。要删除已删除记录的全文索引条目,请OPTIMIZE TABLE
在带有索引的表上运行innodb_optimize_fulltext_only=ON
以重建全文索引。有关更多信息,请参见优化InnoDB全文索引。
InnoDB全文索引事务处理
InnoDB
FULLTEXT
索引由于具有缓存和批处理行为,因此具有特殊的事务处理特性。具体来说,FULLTEXT
索引的更新和插入是在事务提交时处理的,这意味着FULLTEXT
搜索只能看到提交的数据。下面的示例演示了此行为。该FULLTEXT
搜索只返回插入的行被提交之后的结果。
mysql>CREATE TABLE opening_lines ( id INTUNSIGNED AUTO_INCREMENT NOT NULLPRIMARY 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_linesWHERE MATCH(opening_line)AGAINST ('Ishmael'); +---------- + | COUNT(*) | +---------- + | 0 | +---------- + mysql>COMMIT ; mysql>SELECT COUNT(*)FROM opening_linesWHERE MATCH(opening_line)AGAINST ('Ishmael'); +---------- + | COUNT(*) | +---------- + | 1 | +---------- +
监控InnoDB全文索引
您可以InnoDB
FULLTEXT
通过查询下INFORMATION_SCHEMA
表来监视和检查索引的特殊文本处理方面:
INNODB_FT_CONFIG
INNODB_FT_INDEX_TABLE
INNODB_FT_INDEX_CACHE
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_DELETED
INNODB_FT_BEING_DELETED
您还可以FULLTEXT
通过查询INNODB_INDEXES
和参见索引和表的基本信息INNODB_TABLES
。
有关更多信息,请参见“ InnoDB INFORMATION_SCHEMA FULLTEXT索引表”。