09-九、MySQL 面试题 InnoDB 中的全文检索索引 ( 上 )
在基于文本类型的列 ( char
、varchar
和 text
) 上创建全文 ( FULLTEXT
) 索引,并忽略任何定义为停用词的单词, 可以加快对这些列中包含的数据的查询和 DML 操作。
可以在使用 CREATE
TABLE
语句创建表时指定全文索引,也可以在创建表之后使用 ALTER
TABLE
或 CREATE
INDEX
将全文索引添加到现有表中
全文索引的使用方式一般为使用 MATCH()... AGAINST
语法执行全文搜索。
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)
) 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 |
+----------+----------------------------------------------------+-------+
前 6 个表 test/fts_*_index_*
表表示倒排索引表,也称之为 「 辅助索引表 」。
辅助索引表名称以 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
表中查询此值来识别在 opening_lines
表 ( idx ) 上定义的索引 ( 457 )
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
表的内存版本
* 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
表
当对传入的文档进行分词时。单个单词 ( 也称之为 Token
) 会和位置信息、关联的文档 ID ( DOC_ID
) 一起插入索引表中。
插入时会使用基于 「 字母顺序 」对 Token
的第一个单词进行完全排序,并将结果按排序权重分布到这 6 个索引表中。
倒排索引划分了 6 个辅助索引表以支持并行索引创建。默认情况下,会创建两个线程对单词和关联数据进行分词,排序和插入索引表。我们可以使用innodb_ft_sort_pll_degree
选项配置线程数。在大型表上创建全文索引时,可以考虑适当增加线程数。
后记
请,千万不要在 MyISAM
和 InnoDB
表的区别时说 「 InnoDB 不支持全文索引了 」 那已经是过去时了…千万千万
希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")
看完两件小事
如果你觉得这篇文章对你挺有启发,我想请你帮我两个小忙:
- 把这篇文章分享给你的朋友 / 交流群,让更多的人看到,一起进步,一起成长!
- 关注公众号 「方志朋」,公众号后台回复「666」 免费领取我精心整理的进阶资源教程
![JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区,是给开发者用的 Hacker News,技术文章由为你筛选出最优质的干货,其中包括:Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者,你每天都可以在这里找到技术世界的头条内容。](https://static.javajike.com/img/common/wx-fzp.png)
本文著作权归作者所有,如若转载,请注明出处
转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com