一、什么是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中的全文索引也是利用倒排索引实现的,主要步骤如下:
- 对文档进行分词,提取关键词。MySQL中的全文索引一般是针对文本类型的字段建立的,会将文本内容分词提取词条。
- 建立词条到文档的倒排索引。将提取的词条和源文档ID建立映射关系,也就是词条到文档ID的倒排索引。
- 对搜索查询进行分词,并在倒排索引中快速定位匹配的文档。当一个搜索查询过来时,也要对查询进行分词,然后在倒排索引中快速找到包含查询词条的文档。
- 根据匹配的文档ID将相关记录检索出来。有了匹配的文档ID,就可以直接通过ID取出完整的记录了。
- 对结果进行相关性排序。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全文索引的一些主要缺点包括:
没有专用的搜索组件功能丰富，性能好
占用更多存储和内存
全文索引需要存储额外的倒排索引和词频统计信息,因此会占用更多存储空间和内存。
- 字段过多会导致查询效率较低
全文索引查询效率相比普通索引会略低一些,并且字段过多也会降低查询效率。
- 仅支持文本字段
全文索引仅可以在文本类型字段上创建,非文本字段无法使用全文索引。 - 更多的性能消耗，线程占用
- 对全文索引字段的更新不会立即反映到全文索引结果中
- 但新增的记录会被异步地合并到全文索引中
- 全文索引的内容会被自动定期更新,不需要人工重新索引
- 索引内容的实时性仍然略低于普通索引,可能存在短暂的延迟不一致。
需要根据实际需求权衡选择是否需要使用全文索引。
