10bet网址
MySQL 8.0参考手册
相关的文档10bet官方网站 下载本手册 本手册节选

15.6.2.4 InnoDB全文索引

全文索引在基于文本的列上创建(字符VARCHAR,或文本列),以加快对这些列中包含的数据的查询和DML操作。

的一部分定义全文索引创建表语句或添加到现有表中ALTER TABLE创建索引

使用。执行全文搜索匹配()……反对语法。有关使用信息,请参见12.10节“全文查册功能”

InnoDB全文索引在本节的以下主题下描述:

InnoDB全文索引设计

InnoDB全文索引采用倒排索引设计。倒排索引存储单词列表,对于每个单词,存储该单词出现在的文档列表。为了支持接近搜索,还存储了每个单词的位置信息,作为字节偏移量。

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_lines327,其中十六进制值为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全文索引缓存

插入文档时,对其进行标记,并将单个单词和相关数据插入到全文索引中。即使对于小型文档,这个过程也可能导致对辅助索引表的大量小型插入,从而使对这些表的并发访问成为一个争点。为了避免这个问题,InnoDB使用全文索引缓存临时缓存最近插入行的索引表插入。这个内存中的缓存结构一直保存插入,直到缓存满,然后将它们批量刷新到磁盘(到辅助索引表)。您可以查询INFORMATION_SCHEMA。INNODB_FT_INDEX_CACHE表查看最近插入行的标记化数据。

缓存和批量刷新行为避免了对辅助索引表的频繁更新,这可能会在繁忙的插入和更新期间导致并发访问问题。批处理技术还避免了对同一个单词的多次插入,并将重复条目减少到最低限度。不是逐个刷新每个单词,而是将同一单词的插入合并并作为单个条目刷新到磁盘,从而提高了插入效率,同时使辅助索引表尽可能小。

innodb_ft_cache_size变量用于配置全文索引缓存大小(以每个表为基础),该大小会影响全文索引缓存刷新的频率。属性还可以为给定实例中的所有表定义全局全文索引缓存大小限制innodb_ft_total_cache_size变量。

全文索引缓存存储与辅助索引表相同的信息。但是,全文索引缓存仅缓存最近插入行的标记化数据。在查询时,已经刷新到磁盘(辅助索引表)的数据不会返回到全文索引缓存中。辅助索引表中的数据是直接查询的,在返回之前,来自辅助索引表的结果与来自全文索引缓存的结果合并。

InnoDB全文索引DOC_ID和FTS_DOC_ID列

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_INDEXInnoDB自动创建。

请注意

FTS_DOC_ID_INDEX属性不能定义为降序索引,因为InnoDBSQL解析器不使用降序索引。

允许使用的间隙最大FTS_DOC_ID价值与新FTS_DOC_ID取值为65535。

为了避免重新构建表,使用FTS_DOC_ID删除全文索引时保留列。

InnoDB全文索引删除处理

删除具有全文索引列的记录可能会导致辅助索引表中的大量小删除,从而使对这些表的并发访问成为一个争点。为了避免这个问题,我们DOC_ID被删除的文档的一个特殊记录FTS_ * _delete从索引表中删除记录时,索引记录仍保留在全文索引中。返回查询结果前,请先查看FTS_ * _delete表用于过滤已删除的内容DOC_ID这种设计的好处是删除速度快,成本低。缺点是在删除记录后,索引的大小不会立即减小。若要删除已删除记录的全文索引项,请执行优化表的索引表上innodb_optimize_fulltext_only =对重新构建全文索引。有关更多信息,请参见优化InnoDB全文索引

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全文索引

的特殊文本处理方面可以监视和检查InnoDB通过查询以下全文索引INFORMATION_SCHEMA表:

您还可以通过查询的方式查看全文索引和表的基本信息INNODB_INDEXES而且INNODB_TABLES

有关更多信息,请参见章节15.15.4,InnoDB INFORMATION_SCHEMA全文索引表