在 「 MySQL InnoDB 中的全文检索索引 ( 上 ) 」 章节中,我们学习了 MySQL InnoDB 全文索引的相关的表。当插入文档时,会对其进行分词,也就是 Token
化,并将单个单词和相关数据插入到全文索引中。
InnoDB 全文索引缓存
这个过程,即使要插入的文档非常小,也可能会导致在辅助索引表中进行大量的小插入,从而使这些表的并发访问成为性能的瓶颈。为了避免此问题,InnoDB 使用全文索引缓存 ( cache ) 来临时缓存最近要插入到辅助索引表中的行。
此内存缓存结构会一直持有插入的数据,直到缓存已满,然后批量将它们刷新到磁盘 ( 到辅助索引表 ) 。我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE
表以浏览最近插入的行的 Token
化的数据。
缓存和批处理刷新行为避免了对辅助索引表的频繁更新,这种频繁更新可能导致在繁忙的插入和更新时间期间出现并发访问问题。批处理机制还避免了对同一个单词的多次插入,并最大限度地减少了重复条目。这种机制不是单独刷新每个单词,而是将同一个单词的插入合并并作为单个条目刷新到磁盘,从而提高插入效率,同时保持辅助索引表尽可能小。
我们可以通过 innodb_ft_cache_size
配置项设置全文索引缓存大小 ( 基于每个表 ),这会影响全文索引缓存的刷新频率。 我们还可以使用innodb_ft_total_cache_size
配置选项为给定 MySQL 实例中的所有表定义一个全局全文索引缓存大小限制。
全文索引缓存存储与辅助索引表相同的信息。但是,全文索引缓存仅缓存最近插入的行的 Token
化的数据。当使用全文索引检索数据时,已刷新到磁盘 ( 到全文辅助表 ) 的数据不会返回到或更新到全文索引缓存中。只会直接查询辅助索引表中的数据,并在返回之前将辅助索引表的结果与全文索引缓存的结果合并。
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
列,而不会发出警告。
在 CREATE TABLE
创建表时就定义 FTS_DOC_ID
列比在已加载数据的表上创建全文索引有着更快的性能。
-
如果在加载数据之前就在表上定义了
FTS_DOC_ID
列,那么创建全文索引时,就不用添加新列,也就不必重建该表及其索引 -
如果你并不关心
CREATE FULLTEXT INDEX
性能,那么请忽略FTS_DOC_ID
列以让InnoDB
为我们自动创建。InnoDB 会自动创建一个隐藏的FTS_DOC_ID
列,并在该列上创建一个唯一索引FTS_DOC_ID_INDEX
-
如果你想要创建自己的
FTS_DOC_ID
列,则必须将该列定义为BIGINT UNSIGNED NOT NULL
并命名为FTS_DOC_ID
( 全部大写 )如下例所示
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` 约束,但添加了 `AUTO_INCREMENT` 约束可以使加载数据更容易
-
如果你选择自己定义 FTS_DOC_ID 列,则你需要负责管理列以避免空值 ( NULL ) 或重复值。 FTS_DOC_ID 值无法重用,意味着 FTS_DOC_ID值必须不断增加
为了解决这个问题,你可以选择在 FTS_DOC_ID 列上创建必须的唯一索引
FTS_DOC_ID_INDEX
( 全部大写 )
mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);
-
如果你没有为 FTS_DOC_ID 创建 FTS_DOC_ID_INDEX,InnoDB 会自动创建它。
注意: FTS_DOC_ID_INDEX 不能定义为降序索引,因为 InnoDB SQL 解析器不使用降序索引
最大使用的 FTS_DOC_ID 值与新的 FTS_DOC_ID 值之间的允许间隔为 65535
为避免重建表,删除全文索引时将保留 FTS_DOC_ID 列。
结束语
看完这篇文章,我只想说,就一个全文索引还这么复杂....
感谢 MySQL 为我们做了那么多默认的工作
Java 面试宝典是大明哥全力打造的 Java 精品面试题,它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题,而是一套完整的 Java 知识体系,一套你 Java 知识点的扫盲贴。
它的内容包括:
- 大厂真题:Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
- 原创内容:Java 面试宝典内容全部都是大明哥原创,内容全面且通俗易懂,回答部分可以直接作为面试回答内容。
- 持续更新:一次购买,永久有效。大明哥会持续更新 3+ 年,累计更新 1000+,宝典会不断迭代更新,保证最新、最全面。
- 覆盖全面:本宝典累计更新 1000+,从 Java 入门到 Java 架构的高频面试题,实现 360° 全覆盖。
- 不止面试:内容包含面试题解析、内容详解、知识扩展,它不仅仅只是一份面试题,更是一套完整的 Java 知识体系。
- 宝典详情:https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
- 宝典总览:https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
- 宝典进展:https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw
目前 Java 面试宝典累计更新 400+ 道,总字数 42w+。大明哥还在持续更新中,下图是大明哥在 2024-12 月份的更新情况:
想了解详情的小伙伴,扫描下面二维码加大明哥微信【daming091】咨询
同时,大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ,回复【面试题】 即可免费领取。