全文索引在基于文本的列上创建(字符
,VARCHAR
,或文本
列),以加快对这些列中包含的数据的查询和DML操作。
的一部分定义全文索引创建表
语句或添加到现有表中ALTER TABLE
或创建索引
.
使用。执行全文搜索匹配()……反对
语法。有关使用信息,请参见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))mysql> SELECT table_id, name, space from INFORMATION_SCHEMAINNODB_TABLES WHERE name LIKE 'test/%'+----------+----------------------------------------------------+-------+ | table_id | |名称空间 | +----------+----------------------------------------------------+-------+ | 333 |测试/ fts_0000000000000147_00000000000001c9_index_1 | 289 | 334 | |测试/ fts_0000000000000147_00000000000001c9_index_2 | 290 | 335 | |测试/ fts_0000000000000147_00000000000001c9_index_3 | 291 | 336 | |测试/ fts_0000000000000147_00000000000001c9_index_4 | 292 | 337 | |测试/ fts_0000000000000147_00000000000001c9_index_5 | 293 | 338 | |测试/ fts_0000000000000147_00000000000001c9_index_6 | 294 | 330 | |测试/ fts_0000000000000147_being_deleted | 286 | 331 | |测试/ fts_0000000000000147_being_deleted_cache | 287 | 332 | |测试/ fts_0000000000000147_config | 288 | 328 | |测试/ fts_0000000000000147_deleted | 284 | 329 | |测试/ fts_0000000000000147_deleted_cache | 285 | 327 | |测试/ opening_lines | 283 |+----------+----------------------------------------------------+-------+
前六个索引表组成倒立索引,称为辅助索引表。当传入的文档被标记化时,单个单词(也称为”令牌”),连同位置信息和相关的DOC_ID
.根据单词的第一个字符的字符集排序权重,在六个索引表中对单词进行完全排序和分区。
倒排索引被划分为六个辅助索引表,以支持并行索引创建。默认情况下,有两个线程对索引表进行标记、排序,并将单词和相关数据插入索引表。属性可配置执行此工作的线程数innodb_ft_sort_pll_degree
变量。在大型表上创建全文索引时,考虑增加线程数。
辅助索引表名前缀为fts_
并加上index_
.属性中的十六进制值将每个辅助索引表与索引表相关联#
table_id
索引表的。例如,table_id
的测试/ opening_lines
表327
,其中十六进制值为0x147。如上例所示”147”属性关联的辅助索引表的名称中出现十六进制值测试/ opening_lines
表格
的十六进制值index_id
的全文索引也出现在辅助索引表名中。例如,在辅助表名中测试/ fts_0000000000000147_00000000000001c9_index_1
,十六进制值1 c9
十进制值为457。对象上定义的索引opening_lines
表(idx
)可透过查询INFORMATION_SCHEMA。INNODB_INDEXES
表中显示此值(457)。
mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMAWHERE index_id=457;+----------+------+----------+-------+ | 名字index_id | | table_id |空间 | +----------+------+----------+-------+ | 457 | idx | 327 | 283 | +----------+------+----------+-------+
索引表存储在它们自己的表空间中,如果主表是在file-per-table表空间。否则,索引表存储在索引表所在的表空间中。
前面示例中显示的其他索引表称为公共索引表,用于删除处理和存储全文索引的内部状态。与为每个全文索引创建的倒排索引表不同,这组表对于在特定表上创建的所有全文索引都是通用的。
即使全文索引被删除,公共索引表也会被保留。删除全文索引时,FTS_DOC_ID
为索引创建的列将保留,如删除FTS_DOC_ID
列将需要重新构建先前索引的表。类需要通用索引表来管理FTS_DOC_ID
列。
fts_ * _delete
而且fts_ * _deleted_cache
包含已删除但数据尚未从全文索引中删除的文档的文档id (DOC_ID)。的
fts_ * _deleted_cache
是内存版的fts_ * _delete
表格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
的惟一文档标识符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))
在表上创建全文索引时使用创建全文索引
语法中,返回一个警告,该警告报告InnoDB
是重建表添加的FTS_DOC_ID
列。
mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);查询OK, 0行影响,1警告(0.19秒)记录:0重复:0警告:1 mysql>显示警告;+---------+------+--------------------------------------------------+ | 水平| |消息代码 | +---------+------+--------------------------------------------------+ | 警告| 124 | InnoDB表添加列FTS_DOC_ID重建 | +---------+------+--------------------------------------------------+
使用时返回相同的警告ALTER TABLE
属性的表中添加全文索引FTS_DOC_ID
列。如果在上创建全文索引创建表
时间和不指定FTS_DOC_ID
列,InnoDB
添加一个隐藏FTS_DOC_ID
没有任何警告。
定义一个FTS_DOC_ID
列在创建表
与在已经加载了数据的表上创建全文索引相比,时间成本更低。如果一个FTS_DOC_ID
列是在加载数据之前在表上定义的,因此不需要重新构建表及其索引来添加新列。如果你不关心创建全文索引
性能,去掉了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
列。
在opening_lines上创建唯一索引(FTS_DOC_ID_INDEX);
如果您没有创建FTS_DOC_ID_INDEX
,InnoDB
自动创建。
FTS_DOC_ID_INDEX
属性不能定义为降序索引,因为InnoDB
SQL解析器不使用降序索引。
允许使用的间隙最大FTS_DOC_ID
价值与新FTS_DOC_ID
取值为65535。
为了避免重新构建表,使用FTS_DOC_ID
删除全文索引时保留列。
删除具有全文索引列的记录可能会导致辅助索引表中的大量小删除,从而使对这些表的并发访问成为一个争点。为了避免这个问题,我们DOC_ID
被删除的文档的一个特殊记录FTS_ * _delete
从索引表中删除记录时,索引记录仍保留在全文索引中。返回查询结果前,请先查看FTS_ * _delete
表用于过滤已删除的内容DOC_ID
这种设计的好处是删除速度快,成本低。缺点是在删除记录后,索引的大小不会立即减小。若要删除已删除记录的全文索引项,请执行优化表
的索引表上innodb_optimize_fulltext_only =对
重新构建全文索引。有关更多信息,请参见优化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))mysql >开始;mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES ('Call me Ishmael。)、《赫尔曼·梅尔维尔》、《白鲸》)、(尖叫声响彻天际。’、‘品钦’、‘万有引力的彩虹’)、(‘我是一个隐形人。,“拉尔夫·埃里森”,“隐形人”),(“现在在哪里?”现在谁?现在什么时候?,《塞缪尔·贝克特》,《无名》),(“一见钟情。’、‘约瑟夫·海勒’、‘第二十二条军规’)、(‘所有这些或多或少都发生过。’、‘库尔特·冯内古特’、‘五号屠宰场’)、(达洛维夫人说她要自己去买花。”、“弗吉尼亚·伍尔夫,夫人。(“燃烧是一种乐趣。、《雷·布拉德伯里》、《华氏451度》);mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');+----------+ | 数 (*) | +----------+ | 0 | +----------+ mysql >提交; mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael'); +----------+ | COUNT(*) | +----------+ | 1 | +----------+
的特殊文本处理方面可以监视和检查InnoDB
通过查询以下全文索引INFORMATION_SCHEMA
表:
您还可以通过查询的方式查看全文索引和表的基本信息INNODB_INDEXES
而且INNODB_TABLES
.
有关更多信息,请参见章节15.15.4,InnoDB INFORMATION_SCHEMA全文索引表.