美文网首页
学习MYSQL全文索引

学习MYSQL全文索引

作者: woods_deamon | 来源:发表于2023-10-18 11:17 被阅读0次

    一、什么是mysql全文索引

    Full-text indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to speed up queries and DML operations on data contained within those columns.

    这个是官网的描述,全文索引是在基于文本的列(CHAR、VARCHAR 或 TEXT 列)上创建的,以加快对这些列中包含的数据的查询和 DML 操作。

    二、mysql如何设计的

    InnoDB full-text indexes have an inverted index design. Inverted indexes store a list of words, and for each word, a list of documents that the word appears in. To support proximity search, position information for each word is also stored, as a byte offset.

    InnoDB全文索引采用倒排索引设计。倒排索引存储单词列表,并且对于每个单词,存储该单词出现的文档列表。为了支持邻近搜索,每个单词的位置信息也被存储为字节偏移量。

    什么是倒排索引

    倒排索引(Inverted index)是搜索引擎中非常重要的数据结构。它的主要作用是可以通过关键词快速找到包含这个关键词的文档。

    倒排索引的基本思想是:

    对每一个文档,提取其中的关键词,然后建立一个关键词到文档的映射。也就是为每个关键词建立一个到包含该关键词的文档列表的映射。

    比如,对于两个文档:

    文档1:我爱吃苹果

    文档2:苹果是健康的水果

    可以提取以下关键词建立倒排索引:

    我:文档1

    爱:文档1

    吃:文档1

    苹果:文档1,文档2

    是:文档2

    健康:文档2

    水果:文档2

    这样,当搜索“苹果”这个关键词时,可以快速定位到文档1和文档2。你懂了吗?

    三、mysql的全文索引是如何利用倒排索引的

    MySQL中的全文索引也是利用倒排索引实现的,主要步骤如下:

    1. 对文档进行分词,提取关键词。MySQL中的全文索引一般是针对文本类型的字段建立的,会将文本内容分词提取词条
    2. 建立词条到文档的倒排索引。将提取的词条和源文档ID建立映射关系,也就是词条到文档ID的倒排索引。
    3. 对搜索查询进行分词,并在倒排索引中快速定位匹配的文档。当一个搜索查询过来时,也要对查询进行分词,然后在倒排索引中快速找到包含查询词条的文档。
    4. 根据匹配的文档ID将相关记录检索出来。有了匹配的文档ID,就可以直接通过ID取出完整的记录了。
    5. 对结果进行相关性排序。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;
    

    4.1 创建索引

    ALTER TABLE XXX
    ADD FULLTEXT INDEX FULL_INDEX (example_index) with parser ngram ;
    或者
    CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
    

    创建全文索引,并使用中文分词器。ngram这个是mysql5.7之后提供的中文分词器。

    当创建此索引的时候,mysql会隐式的创建一列名为FTS_DOC_ID,可以称之为文档ID(唯一的),所以也会隐式的创建该列的索引,并且是唯一索引,如果你自己显式的创建此字段也是可以的,则您负责管理该列以避免空值或重复值。FTS_DOC_ID值不能重复使用,这意味着FTS_DOC_ID 值必须不断增加。

    4.2 分词查询

    SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
    

    查询分词包含Ishmael的文档,即opening_lines字段包含Ishmael的行数据

    4.3 高级用法

    SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('database AND mysql' IN BOOLEAN MODE);
    

    这将搜索同时包含 'database' 和 'mysql' 的记录。布尔模式可以使用 AND、OR、NOT 等连接词进行组合搜索,等等。

    如果不加后面的 in Boolean mode,就默认使用自然语言模式(NATURAL LANGUAGE MODE)。会根据词频、IDF等算法对匹配结果进行评分和排序

    五、存在缺点

    MySQL全文索引的一些主要缺点包括:

    1. 没有专用的搜索组件功能丰富,性能好

    2. 占用更多存储和内存

    全文索引需要存储额外的倒排索引和词频统计信息,因此会占用更多存储空间和内存。

    1. 字段过多会导致查询效率较低

    全文索引查询效率相比普通索引会略低一些,并且字段过多也会降低查询效率。

    1. 仅支持文本字段
      全文索引仅可以在文本类型字段上创建,非文本字段无法使用全文索引。
    2. 更多的性能消耗,线程占用
      • 对全文索引字段的更新不会立即反映到全文索引结果中
      • 但新增的记录会被异步地合并到全文索引中
      • 全文索引的内容会被自动定期更新,不需要人工重新索引
      • 索引内容的实时性仍然略低于普通索引,可能存在短暂的延迟不一致。

    需要根据实际需求权衡选择是否需要使用全文索引。

    相关文章

      网友评论

          本文标题:学习MYSQL全文索引

          本文链接:https://www.haomeiwen.com/subject/xfqcidtx.html