学习MYSQL全文索引

一、什么是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. 占用更多存储和内存

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

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

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

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

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