发表更新MySQL系列23 分钟读完 (大约3404个字)

MySQL系列(31)：InnoDB 全文索引

官方文档-15.6.2.4 InnoDB Full-Text Indexes

Full-text indexes(全文索引)可在基于文本类型的列(CHAR, VARCHAR 或 text 列) 上创建，可以加快对这些列中包含的数据的查询和DML操作。

全文索引被定义为CREATE TABLE语句的一部分，或使用ALTER TABLE或CREATE index添加到现有表。

要执行全文索引需要使用 MATCH() ... AGAINST 语法。Section 12.10, “Full-Text Search Functions”.

InnoDB

全文索引设计

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

倒排索引（英语：Inverted index），也常被称为反向索引，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。通过倒排索引，可以根据单词快速获取包含这个单词的文档列表。

全文索引表

创建InnoDB全文索引时，会创建一组索引表，如下例所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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_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_为前缀，以index_#为后缀。 每个辅助索引表通过与索引表的 table_id 匹配的辅助索引表名称中的十六进制值与索引表相关联。 例如test/opening_lines表的table_id为327，十六进制值为0x147。 如前例所示，“147”十六进制值出现在与 test/opening_lines 表关联的辅助索引表的名称中。

表示全文索引的 index_id 的十六进制值也出现在辅助索引表名中。 例如，在辅助表名称 test/fts_0000000000000147_00000000000001c9_index_1 中，十六进制值 1c9 的十进制值为 457。可以通过查询 Information Schema INNODB_INDEXES 表中的该值（457）来识别在 opening_lines 表（idx）上定义的索引。

1
2
3
4
5
6
7
mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_INDEXES
       WHERE index_id=457;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      457 | idx  |      327 |   283 |
+----------+------+----------+-------+

如果主表是在 file-per-table 表空间中创建的，则索引表存储在它们自己的表空间中。 否则，索引表存储在索引表所在的表空间中。

上述示例中的其他索引表称为普通索引表，用于删除处理和存储全文索引的内部状态。 与为每个全文索引创建的倒排索引表不同，这组表对于在特定表上创建的所有全文索引是通用的。

即使删除了全文索引，公共索引表也会保留。 当删除全文索引时，为该索引创建的 FTS_DOC_ID 列将保留，因为删除 FTS_DOC_ID 列需要重建以前的索引表。 需要公共索引表来管理 FTS_DOC_ID 列。

  • fts_*_deleted 和 fts_*_deleted_cache

    包含已删除但其数据尚未从全文索引中删除的文档的文档 ID (DOC_ID)。 fts_*_deleted_cache 是 fts_*_deleted 表的内存（in_memory）版本。

  • 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_FT_INDEX_CACHE 表以查看最近插入行的标记化数据。

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

innodb_ft_cache_size 变量用于配置全文索引缓存大小（基于每个表），这会影响刷新全文索引缓存的频率。 您还可以使用 innodb_ft_total_cache_size 变量为给定实例中的所有表定义全局全文索引缓存大小限制。

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

全文索引DOC_ID和FTS_DOC_ID列

InnoDB 使用称为 DOC_ID 的唯一文档标识符将全文索引中的词映射到该词出现的文档记录。 该映射需要索引表上的 FTS_DOC_ID 列。 如果未定义 FTS_DOC_ID 列，InnoDB 在创建全文索引时会自动添加一个隐藏的 FTS_DOC_ID 列。 以下示例演示了此行为。

下表定义不包括 FTS_DOC_ID 列：

1
2
3
4
5
6
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 列。

1
2
3
4
5
6
7
8
9
10
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 列，而不会发出警告。

在 CREATE TABLE 时定义 FTS_DOC_ID 列比在已加载数据的表上创建全文索引代价更低。 如果在加载数据之前在表上定义了 FTS_DOC_ID 列，则不必重建表及其索引即可添加新列。

如果不关心 CREATE FULLTEXT INDEX 性能，请忽略 FTS_DOC_ID 列让 InnoDB 为您创建它。 InnoDB 创建一个隐藏的 FTS_DOC_ID 列以及 FTS_DOC_ID 列上的唯一索引 (FTS_DOC_ID_INDEX)。 如果要创建自己的 FTS_DOC_ID 列，则该列必须定义为 BIGINT UNSIGNED NOT NULL 并命名为 FTS_DOC_ID（全部大写），如以下示例所示：

1
2
3
4
5
6
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 列不需要定义为 AUTO_INCREMENT 列，但这样做可以使加载数据更容易。

如果选择自己定义 FTS_DOC_ID 列，则有责任管理该列以避免出现空值或重复值。 FTS_DOC_ID 值不能重复使用，这意味着 FTS_DOC_ID 值必须不断增加。

或者，可以在 FTS_DOC_ID 列上创建所需的唯一 FTS_DOC_ID_INDEX（全部大写）。

1
mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on 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 列。

全文索引删除处理

删除具有全文索引列的记录可能会导致辅助索引表中的大量小删除，从而使对这些表的并发访问成为争用点。

为避免此问题，每当从索引表中删除记录时，已删除文档的 DOC_ID 都会记录在特殊的 FTS_*_DELETED 表中，并且索引记录保留在全文索引中。

在返回查询结果之前，FTS_*_DELETED 表中的信息用于过滤掉已删除的 DOC_ID。 这种设计的好处是删除速度快且成本低。 缺点是删除记录后索引的大小不会立即减小。

要删除已删除记录的全文索引条目，请在 innodb_optimize_fulltext_only=ON 的索引表上运行 OPTIMIZE TABLE 以重建全文索引。 有关更多信息，请参阅优化 InnoDB 全文索引。

全文索引事务处理

InnoDB 全文索引由于其缓存和批处理行为而具有特殊的事务处理特性。 具体来说，全文索引的更新和插入是在事务提交时处理的，这意味着全文搜索只能看到提交的数据。 以下示例演示了此行为。 全文搜索仅在插入的行被提交后才返回结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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 |
+----------+

监控全文索引

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

还可以通过查询 INNODB_INDEXES 和 INNODB_TABLES 查看全文索引和表的基本信息。

MySQL系列(31)：InnoDB 全文索引

http://blog.gxitsky.com/2023/03/18/MySQL-31-InnoDB-Full-Text-Indexes-Design/

作者

光星

发布于

2023-03-18

更新于

2023-03-27

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我
×