InnoDB全文索引

一只小码农正在路过
532 阅读10分钟

InnoDB全文索引

FULLTEXT索引是在基于文本的列（CHAR， VARCHAR或TEXT列）上创建的， 以帮助加快对这些列中包含的数据的查询和DML操作，而忽略定义为停用词的任何单词。

甲FULLTEXT指数被定义为一个的一部分 CREATE TABLE说明或使用添加到现有的表ALTER TABLE 或CREATE INDEX。

使用MATCH() ... AGAINST语法执行全文搜索。有关用法信息，请参见 “全文搜索功能”。

InnoDB全文索引设计

InnoDB FULLTEXT索引具有倒排索引设计。倒排索引存储单词列表，并且对于每个单词，存储单词出现的文档列表。为了支持邻近搜索，每个单词的位置信息也作为字节偏移量存储。

InnoDB全文索引表

创建InnoDB 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> 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 |
+----------+----------------------------------------------------+-------+

前六个表代表倒排索引，并称为辅助索引表。对传入文档进行标记时，各个单词（也称为 “ 标记 ”）与位置信息和关联的文档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_SYS_INDEXES。

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 |
+----------+------+----------+-------+

如果在每个表文件表 空间中创建主表，则索引表将存储在其自己 的表空间中。

前面示例中显示的其他索引表称为通用索引表，用于删除处理和存储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 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列，但是 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.7.13之前，最大使用FTS_DOC_ID值与新 FTS_DOC_ID值之间的允许间隙 为10000。在MySQL 5.7.13及更高版本中，允许间隙为65535。

为避免重建表，FTS_DOC_ID 删除全文索引时将保留该列。

InnoDB全文索引删除处理

删除具有全文索引列的记录可能会导致辅助索引表中的许多小删除，从而使对这些表的并发访问成为争用点。为避免此问题，每当从索引表中删除DOC_ID记录时，已删除文档的文档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 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全文索引

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

您还可以FULLTEXT通过查询INNODB_SYS_INDEXES和 查看索引和表的 基本信息 INNODB_SYS_TABLES。

有关更多信息，请参见 “ InnoDB INFORMATION_SCHEMA FULLTEXT索引表”。

更多内容欢迎关注我的个人公众号“韩哥有话说”，100G人工智能学习资料，大量后端学习资料等你来拿。

qrcode_for_gh_3214f9e3470a_258.jpg

avatar
文章
阅读
粉丝