MySQL · 引擎特性 · InnoDB Fulltext简介

2024-02-14 10:48

本文主要是介绍MySQL · 引擎特性 · InnoDB Fulltext简介,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

前言

从MySQL5.6版本开始支持InnoDB引擎的全文索引,语法层面上大多数兼容之前MyISAM的全文索引模式。 所谓全文索引,是一种通过建立倒排索引,快速匹配文档的方式。MySQL支持三种模式的全文检索模式:

第一种是自然语言模式(IN NATURAL LANGUAGE MODE),即通过MATCH AGAINST 传递某个特定的字符串来进行检索。

第二种是布尔模式(IN BOOLEAN MODE),可以为检索的字符串增加操作符,例如“+”表示必须包含,“-”表示不包含,“*”表示通配符(这种情况,即使传递的字符串较小或出现在停词中,也不会被过滤掉),其他还有很多特殊的布尔操作符,可以通过如下参数控制:


第三种是查询扩展模式(WITH QUERY EXPANSION), 这种模式是自然语言模式下的一个变种,会执行两次检索,第一次使用给定的短语进行检索,第二次是结合第一次相关性比较高的行进行检索。

目前MySQL支持在CHAR、VARCHAR、TEXT类型的列上定义全文索引。

本文只是简单的分析了全文索引涉及到的代码模块以及5.7的一些新特性,源码部分基于MySQL5.7.8-rc版本。更细节的部分并未深入。

创建全文索引

如下例所示,一个简单的创建带全文索引表的SQL:


磁盘上会产生多个文件:

除了t1.frm和t1.ibd外,共分为以下几类表

a)
FTS_000000000000010b_0000000000000154_INDEX_1~6.ibd这6个文件用于存储倒排索引,存储的是分词和位置以及docment ID,根据分词的第一个字符值进行分区,映射到不同的文件中。

文件的命名规则为FTS_{TABLE_ID}_{INDEX_ID}_INDEX_{N}.ibd

b)
FTS_000000000000010b_DELETED.ibd 包含已经被删除的DOC_ID,但还没从全文索引数据中删掉;
FTS_000000000000010b_DELETED_CACHE.ibd 是前者的内存缓存(但是搜索了下代码,只有当fts_cache_t::deleted_doc_ids被使用时,才会在sync时转储到该表中,但并没有发现任何地方使用这个对象)

c)
FTS_000000000000010b_BEING_DELETED_CACHE.ibd
FTS_000000000000010b_BEING_DELETED.ibd
包含了已经被删除索引记录并且正在从全文索引中移除的DOC ID, 前者是后者的内存版本,这两个表主要用于辅助进行OPTIMIZE TABLE时将DELETED/DELETED_CACHED表中的记录转储到其中。

d)
FTS_000000000000010b_CONFIG.ibd
包含全文索引的内部信息,最重要的存储是FTS_SYNCED_DOC_ID,表示已经解析并刷到磁盘的doc id. 在崩溃恢复时,可以根据这个值判断哪些该重新解析并加入到索引cache中。

建全文索引辅助表函数参考:


当对一个已经存在的表上创建全文索引时,InnoDB采用了fork多个线程进行并发构建全文索引项的方法,并发度由参数innodb_ft_sort_pll_degree 控制。因此在restore一个全文索引表时,我们建议先建表、导入数据,再在表上创建全文索引。

参考函数:row_merge_read_clustered_index --> row_fts_start_psort
线程回调函数为fts_parallel_tokenization。

当表上存在全文索引时,就会隐式的建立一个名为FTS_DOC_ID的列,并在其上创建一个唯一索引,用于标识分词出现的记录行。你也可以显式的创建一个名为FTS_DOC_ID的列,但需要和隐式创建的列类型保持一致。

为了维护表上的全文索引信息,全文索引模块定义了大量的类来进行管理,总的来说,如下图所示:

普通DML及查询操作

插入

我们可以通过INNODB_FT_INDEX_CACHE来检查插入记录的分词:


在插入一条记录时,对应的堆栈如下:

在向原表上插入完成记录后,会去判断表上是否有全文索引(DICT_TF2_FTS),如果有的话,则将插入记录对应的doc id提取出来(fts_get_doc_id_from_row),并缓存到事务对象中。

删除

删除操作不会直接从全文索引里直接删除,因此依然可以从INNODB_FT_INDEX_CACHE中查到分词信息

相关堆栈:


更新

更新非全文索引列,不会修改FTS_DOC_ID列的值。如果更新了全文索引列,在InnoDB的实现是删除老的DOC,并插入新的DOC

堆栈为:


可见所有DML的操作,都走接口函数fts_trx_add_op,划分为两种操作:FTS_INSERT及FTS_DELETE;当前事务涉及的doc id被存储到trx->fts_trx中,在执行SQL的过程中并没有更新全文索引,而是在事务提交时进行的。

在缓存操作时,维护了两个结构,一个是trx->fts_trx->savepoints,维护了事务全局的全文索引操作,另外一个是trx->fts_trx->last_stmt,维护的是当前SQL操作的doc id,前者在事务结束时处理,后者在SQL结束时清空。

查询

对于全文索引的查询,采用新的接口函数,分为两步

第一步,根据检索词搜集符合条件的doc id


在搜集满足查询条件的doc id时,首先读取DELETED表中记录的doc id,这些doc id随后被用做过滤。

第二步,根据搜集到的doc id,找到对应的记录,使用的索引是dict_table_t::fts_doc_id_index,也就是建立在隐藏列FTS_DOC_ID上的唯一索引。


通常查询返回的结果是根据rank排序的,InnoDB的全文检索排序规则和sphinx类似,基于 BM25 和 TF-IDF算法。

rank的计算算法如下:


IDF的计算参阅函数:fts_query_calculate_idf
ranking计算:fts_query_calculate_ranking

如果使用多个单词匹配到,则把各个单词各自的rank累加起来。官方博客有一篇文章专门对此进行了介绍。

事务操作

事务内回滚
正在事务内回滚某个语句,或者回滚到某个savepoint时,需要将对应的操作记录也要删除。维护了trx->fts_trx->last_stmt,在单条SQL结束时释放(trx_mark_sql_stat_end )。如果SQL回滚,就根据last_stmt中维护的doc id从全局savepoints中清理掉本条SQL的doc id。

相关堆栈:


回滚到savepoint

事务提交

相关堆栈:


在调用fts_commit时,会根据不同的操作类型,调用fts_add增加全文索引项,调用fts_delete删除全文索引项。

由于在插入记录时,先分词、分解成多个词插入辅助表中,因此一条insert可能产生多个小的插入。这种写入放大可能是不可承受的。InnoDB采用了一种优化的方案:创建一个内存cache,临时缓存插入操作,当cache满时再批量刷到磁盘,这样做的好处是:

  • 避免重复存储相同的单词

  • cache size 通过参数innodb_ft_cache_size控制

  • 查询会将cache和磁盘数据进行merge

在事务提交时,调用函数fts_add_doc_by_id

  • 首先根据doc id,使用doc_id所在的索引进行查询,找到刚刚插入的记录项对应的聚集索引记录。

  • 遍历表上全部的聚集索引,根据全文索引对应的fts_get_doc_t(fts_cache_t::get_docs)构建fts_doc_t,对文档根据选择的parser进行分词(fts_tokenize_document函数或者fts_tokenize_document_next),具体的文档存储到fts_doc_t::text中。

  • 将上一步获得的分词加入到cache中(fts_cache_add_doc)

  • 如果当前cache的大小超过配置的innodb_ft_cache_size,或者全局cache的大小超过innodb_ft_total_cache_size(fts_need_sync被设置为true),则进行一次sync,将该表缓存的数据刷到全文索引文件中(fts_sync),并清空cache。

和插入相似,删除操作也可能产生大量小的删除操作, 为了避免这种情况,维持一个表,来记录被删除的doc id, 但记录依然存在于原文件中。删除操作的提交函数为fts_delete,将被删除的记录doc_id插入到DELETED辅助表中。

事务模块涉及的几个关键类包括:

同步缓存

在满足一定条件时,全文索引需要进行一次sync操作,将数据同步到全文索引文件中,大概包含以下集中情况需要sync:

  • cache数据占用的内存超过限制

  • 后台线程fts_optimize_thread在shutdown调用,将所有表进行一次sync。

  • ha_innobase::optimize调用(执行optimize table)

  • row_merge_read_clustered_index:创建一个新的临时表并读入数据后,进行一次sync调用

同步操作的入口函数为fts_sync,大体流程为:

  • 针对每个索引,调用函数fts_sync_index:通过函数fts_select_index计算写入的索引文件,再将分词节点信息写入到文件(函数fts_write_node), 倒排索引的记录内容使用结构体fts_node_t进行描述,存储结构如下图所示:

  • 调用fts_sync_commit提交sync操作:

    • 更新CONFIG表记录的最大SYNC的DOC ID(fts_cmp_set_sync_doc_id);

    • 若fts_cache_t::deleted_doc_ids不为空,将其加入到DELETED_CACHE辅助表中(fts_sync_add_deleted_cache

    • 清空cache 并重新初始化

Optimize table

当你修改了某些配置(例如最小token size时),或者希望重组全文索引时,可以执行optimize table。由于原始optimize table操作会产生整个表的重建,耗时太久,因此InnoDB引入了一个参数innodb_optimize_fulltext_only来控制该行为。当开启该选项时,如果执行optimize table,就只优化全文索引,而不会去重建表,入口函数为ha_innobase::optimize:


首先调用函数fts_sync_table,将表上在内存中cache的数据刷到全文索引文件中;
然后调用函数fts_optimize_table,我们主要分析集中在第二步。

fts_optimize_table函数流程如下:

  • 如果BEGING_DELETED表中没有数据(例如第一次调用optimized table),则将DELETED表中的数据转储到BEING_DELETED表中,相当于拿到了一个快照,执行的SQL操作为:


参考函数:fts_optimize_create_deleted_doc_id_snapshot

  • 从BEING_DELETED/BEING_DELETED_CACHE表中读取已经被删除的doc id,这些doc id在随后的索引优化中将被忽略掉。
    参考函数:fts_optimize_read_deleted_doc_id_snapshot

  • 调用fts_optimize_indexes 对每个索引进行优化,相关堆栈如下:


  • 当在所有索引上完成optimize后,调用fts_optimize_purge_snapshot,主要操作包括: a)


从DELETE和DELETE_CACHE表中将doc id删除,参考函数fts_optimize_purge_deleted_doc_ids

b)


从BEING_DELETED及BEING_DELETED_CACHE中删除对应的doc id。
参考函数: fts_optimize_purge_deleted_doc_id_snapshot

后台线程

InnoDB启动时,会创建一个后台线程,线程函数为fts_optimize_thread,工作队列为fts_optimize_wq,其主要目的是在满足一定条件时,对表自动进行optimize操作。

在如下两种情况,会向fts_optimize_wq中增加元组:

  • fts_optimize_add_table: 创建或打开一个新的带全文索引的表时,创建一个类型为FTS_MSG_ADD_TABLE并包含表对象指针的MSG,加入到fts_optimize_wq中,这些表禁止被从数据词典中驱逐。

  • fts_optimize_remove_table: 删除表、DDL、释放表对象(dict_mem_table_free)、删除全文索引(fts_drop_index)等操作时,会创建一个类型为FTS_MSG_DEL_TABLE的MEG,加入到fts_optimize_wq队列中。

fts optimize线程对于FTS_MSG_ADD_TABLE类型的会将相应的表加入到调度队列,对于FTS_MSG_DEL_TABLE,则从调度队列中删除。其调度队列的成员类型为fts_slot_t。

当表上删除的数据量超过一千万(FTS_OPTIMIZE_THRESHOLD)行时,就会触发一次自动optimize table,但两次optimize的间隔不应低于300秒(FTS_OPTIMIZE_INTERVAL_IN_SECS)。

监控

我们可以通过几个INFORMATION_SCHEMA下的全文索引表来监控全文索引状态。


想要从information_schema表中查询信息,需要先设置变量innodb_ft_aux_table,值为你要查询表的"dbname/tablename"。

全文索引停词

停词(STOP WORD)用于在分词时忽略那些常见的不重要的单词,InnoDB目前内建的停词可以从information_schema.INNODB_FT_DEFAULT_STOPWORD读取,用户也可以自己定义停词列表,方法很简单:创建一个和nformation_schema.INNODB_FT_DEFAULT_STOPWORD一模一样的表,将你想要的停词加入到其中,然后设置innodb_ft_server_stopword_table值为你创建的表名:"dbname/tabname"。

你也可以使用会话级别的参数innodb_ft_user_stopword_table来指定你想要的停词表。和上述创建规则一致。具体的参阅官方文档

另外配置项innodb_ft_min_token_sizeinnodb_ft_max_token_size 用于表示一个单词的字符长度范围,在这个范围的连续字符串才会被当作一个单词。 然而如果使用ngram解析器的话,有效单词长度受参数ngram_token_size控制。

可以关闭参数innodb_ft_enable_stopword,这样在分词时也会把预设的停词考虑进去。

InnoDB全文索引插件

从MySQL 5.7.3开始InnoDB支持全文索引插件,用户可以以Plugin的模式来定义自己的分词规则,或是引入社区开发的全文索引解析器,例如某些专业领域的分词,可能具有不同的规则。

全文索引插件有两种角色:第一种是替换内建的parser,读取输入文档,进行解析后,将分词传送给server; 另一种角色是作为内建parser的协作者,可以把输入文档处理过后,再传送给内建parser。

如果你已经有一个基于MYISAM的全文索引插件了,也可以根据这篇官方文档的介绍,将其修改成InnoDB全文索引插件。

InnoDB N-gram parser

从MySQL5.7.6版本开始提供了一种内建的全文索引ngram parser,可以很好的支持CLK字符集(中文,韩文,日文),CLK有个共同点就是单词不像英语习惯那样根据空格进行分解的,因此传统的内建分词方式无法准确的对类似中文进行分词。

ngram parser内建在代码中,该解析器默安装,你可以通过指定索引属性(WITH PARSER ngram)来利用该parser,例如:


N-Gram使用一种特殊的方式来进行分词,举个简单的例子,假设要对单词'abcd'进行分词,那么其分词结果为:

N取决于ngram_token_size`的设置,默认值为2.

对于停词的处理, N-Gram和内建的parser不同,即只要每个token包含了(而不是精确匹配)停词,就不对其进行索引; 另外空格总是作为一个停词,因此在分词取token时,空格会被忽略掉。

在执行查询时,用户传递的搜索词也会基于N-Gram进行分解后进行检索。 具体的例子可以参阅官方博客的描述。

除了N-gram parser外,官方也支持了另外一种名为MeCab Parser的插件,主要用于日语分词,但需要手动安装。


原文:https://yq.aliyun.com/articles/224#

这篇关于MySQL · 引擎特性 · InnoDB Fulltext简介的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/708291

相关文章

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

ASIO网络调试助手之一:简介

多年前,写过几篇《Boost.Asio C++网络编程》的学习文章,一直没机会实践。最近项目中用到了Asio,于是抽空写了个网络调试助手。 开发环境: Win10 Qt5.12.6 + Asio(standalone) + spdlog 支持协议: UDP + TCP Client + TCP Server 独立的Asio(http://www.think-async.com)只包含了头文件,不依

[MySQL表的增删改查-进阶]

🌈个人主页:努力学编程’ ⛅个人推荐: c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构,刷题刻不容缓:点击一起刷题 🌙心灵鸡汤:总有人要赢,为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC