MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MySQL全文索引原理与应用

2021-03-303.5k 阅读

一、MySQL 全文索引简介

在数据库应用中，数据检索是一项核心任务。对于简单的查询，普通索引可以满足需求，但当面对复杂的文本搜索，如在大量文本数据中查找包含特定关键词的记录时，普通索引就显得力不从心。MySQL 的全文索引应运而生，它专为处理大规模文本数据的搜索而设计，能够极大地提高文本搜索的效率。

MySQL 的全文索引支持多种语言，包括英语、德语、法语等常见语言，并且能够处理词干提取、停用词过滤等复杂的文本处理任务。例如，在英语中，“running”“runs”“ran”等不同形式的单词，在全文索引中可以通过词干提取将其视为同一词根“run”相关的词汇，从而更智能地匹配搜索条件。

二、全文索引的数据结构

MySQL 的全文索引采用了倒排索引（Inverted Index）的数据结构。倒排索引与普通索引不同，普通索引是基于记录的主键或其他索引列的值来构建索引，通过索引可以快速定位到对应的记录行。而倒排索引则是以文档中的单词为索引项，每个单词对应一个包含该单词的文档列表。

具体来说，倒排索引包含两个主要部分：词汇表（Vocabulary）和倒排列表（Posting List）。词汇表是所有在文本数据中出现的唯一单词的集合，每个单词都有一个唯一的标识。倒排列表则记录了每个单词在哪些文档中出现，以及在文档中的位置等信息。

以一个简单的示例来说明，假设有以下两篇文档：

  • 文档1：“This is a sample text for full - text index”
  • 文档2：“Another sample text to illustrate full - text index concept”

词汇表可能包含 “this”“is”“a”“sample”“text”“for”“full - text”“index”“another”“to”“illustrate”“concept” 等单词。对于单词 “sample”，其倒排列表可能记录该单词出现在文档1和文档2中，并且在文档1中的位置是第4个单词，在文档2中的位置是第2个单词。

这种数据结构使得在进行文本搜索时，MySQL 可以快速定位到包含特定单词的文档，而不需要逐行扫描整个表，大大提高了搜索效率。

三、全文索引的创建与使用

1. 创建全文索引

在 MySQL 中，可以在创建表时或者已有表上添加全文索引。

创建表时添加全文索引：

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT(title, content)
);

在上述示例中，我们创建了一个名为 articles 的表，包含 id、title 和 content 列，并对 title 和 content 列创建了全文索引。

对已有表添加全文索引：

ALTER TABLE articles
ADD FULLTEXT(title, content);

需要注意的是，MySQL 对全文索引的列类型有一定限制，通常 CHAR、VARCHAR、TEXT 类型的列可以创建全文索引。

2. 使用全文索引进行查询

MySQL 提供了 MATCH AGAINST 语法来使用全文索引进行查询。这种语法与普通的 LIKE 查询有很大区别，LIKE 是字符匹配，而 MATCH AGAINST 是基于全文索引的语义匹配，更适合处理文本搜索。

基本的 MATCH AGAINST 查询：

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('sample text' IN NATURAL LANGUAGE MODE);

上述查询会在 articles 表的 title 和 content 列中搜索包含 “sample text” 的记录。IN NATURAL LANGUAGE MODE 表示使用自然语言模式，MySQL 会对查询词进行一些预处理，如去除停用词等。

使用 WITH NATURAL LANGUAGE MODE WITH QUERY EXPANSION：

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('sample text' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);

这种模式下，MySQL 不仅会搜索与 “sample text” 直接匹配的记录，还会根据索引中的相关信息，尝试扩展查询，找到可能相关的其他记录。例如，如果索引中 “sample text” 经常与 “example passage” 一起出现，那么包含 “example passage” 的记录也可能被返回。

四、全文索引的原理细节

1. 分词（Tokenization）

在构建全文索引之前，MySQL 首先要对文本进行分词处理。分词就是将文本按照一定规则拆分成一个个单词（Tokens）。对于不同的语言，分词规则有所不同。

在英语中，通常以空格、标点符号等作为分隔符进行分词。例如，句子 “This is a sample, text.” 会被拆分成 “This”“is”“a”“sample”“text” 等单词。但对于一些复杂的语言，如中文，分词就不是简单地按分隔符拆分，因为中文没有明显的单词分隔符。MySQL 本身对中文分词支持有限，通常需要借助第三方插件，如 ngram 插件来实现中文分词。

2. 词干提取（Stemming）

词干提取是将单词还原为其基本形式（词干）的过程。如前文提到的，在英语中，“running”“runs”“ran” 等单词经过词干提取后都与 “run” 相关。MySQL 的全文索引在构建过程中会对支持的语言进行词干提取，这有助于提高搜索的召回率。例如，当用户搜索 “run” 时，包含 “running”“runs” 等形式的文档也可能被检索出来。

3. 停用词过滤（Stop - word Filtering）

停用词是指在文本中频繁出现但对语义表达贡献不大的单词，如英语中的 “the”“and”“is” 等，中文中的 “的”“是”“在” 等。在构建全文索引时，MySQL 会自动过滤掉这些停用词，以减少索引的大小，提高索引构建和搜索的效率。例如，对于句子 “The sample text is for testing.”，在构建索引时，“the”“is”“for” 等停用词会被忽略。

4. 索引构建过程

当执行创建全文索引的语句时，MySQL 会按照以下步骤构建索引：

  1. 逐行读取表中的文本数据。
  2. 对读取的文本进行分词处理，生成单词列表。
  3. 对单词列表进行词干提取和停用词过滤。
  4. 将处理后的单词插入到倒排索引结构中，更新词汇表和倒排列表。

在这个过程中，MySQL 会使用一些优化策略来提高索引构建的效率，如批量插入数据以减少磁盘 I/O 操作等。

五、全文索引与普通索引的比较

1. 适用场景

  • 普通索引：适用于简单的精确匹配查询，如根据用户 ID、订单号等唯一标识进行查询。例如，在用户表中根据用户 ID 查询用户信息：
SELECT * FROM users WHERE user_id = 123;

普通索引能够快速定位到对应的记录行。

  • 全文索引：适用于复杂的文本搜索，特别是在大量文本数据中查找包含特定关键词的记录。如在新闻文章表中搜索包含 “人工智能” 相关内容的文章：
SELECT * FROM news_articles WHERE MATCH(content) AGAINST('人工智能' IN NATURAL LANGUAGE MODE);

2. 性能

  • 普通索引：对于简单查询性能非常高，因为其数据结构简单，能够快速定位到记录。但在处理复杂文本搜索，尤其是包含多个关键词、模糊匹配等情况时，性能会急剧下降。例如，使用 LIKE '%keyword%' 这种模糊匹配方式，普通索引无法有效利用，MySQL 可能需要全表扫描。
  • 全文索引：在处理大规模文本搜索时性能优势明显。由于采用倒排索引结构，能够快速定位到包含关键词的文档，并且支持词干提取、停用词过滤等功能，使得搜索更智能、更高效。但对于简单的精确匹配查询，全文索引的性能并不比普通索引有优势，因为其构建和维护相对复杂，会带来一定的开销。

3. 索引大小

  • 普通索引：索引大小相对较小，因为它主要基于记录的主键或其他索引列的值构建，只需要存储索引列的值和对应的记录行指针。
  • 全文索引：由于需要存储词汇表、倒排列表等信息，并且可能包含词干提取、停用词过滤等额外信息，索引大小通常比普通索引大。特别是对于包含大量文本数据的表，全文索引的大小可能会非常可观。

六、全文索引的优化与注意事项

1. 选择合适的索引列

并非所有列都适合创建全文索引。一般来说，只有那些包含大量文本数据且需要进行复杂文本搜索的列才应该创建全文索引。如果对一些短文本列或者很少用于文本搜索的列创建全文索引，不仅会增加索引大小和维护成本，还可能影响数据库的性能。例如，在一个包含用户性别（“男”“女”）的列上创建全文索引就是不合理的。

2. 控制索引数量

虽然全文索引可以提高文本搜索性能，但过多的索引会增加数据库的存储开销和写入性能损耗。每次插入、更新或删除记录时，MySQL 都需要更新相关的索引。因此，应该根据实际查询需求，只创建必要的全文索引。例如，在一个表中有多个文本列，但只有其中一两个列经常用于复杂文本搜索，那么只对这一两个列创建全文索引即可。

3. 定期维护索引

随着数据的不断插入、更新和删除，索引可能会出现碎片化，影响性能。MySQL 提供了 OPTIMIZE TABLE 语句来对表和索引进行优化。定期执行 OPTIMIZE TABLE 可以重新组织表和索引的数据，提高查询性能。例如：

OPTIMIZE TABLE articles;

另外，如果表中的数据发生了大量的变化，如数据量大幅增长或数据结构发生改变，可能需要重新创建索引以获得最佳性能。

4. 考虑使用覆盖索引

覆盖索引是指查询所需的数据都可以从索引中获取，而不需要回表操作。在使用全文索引时，如果查询的列都包含在全文索引的列中，可以通过合理设计查询来利用覆盖索引，提高查询性能。例如，对于 articles 表，如果只查询 title 列，并且 title 列包含在全文索引中，可以这样查询：

SELECT title FROM articles WHERE MATCH(title) AGAINST('sample' IN NATURAL LANGUAGE MODE);

这样可以避免回表操作，直接从索引中获取数据，提高查询效率。

七、全文索引在不同存储引擎中的支持

MySQL 支持多种存储引擎，不同存储引擎对全文索引的支持有所不同。

1. InnoDB 存储引擎

InnoDB 是 MySQL 默认的存储引擎，从 MySQL 5.6 版本开始，InnoDB 对全文索引提供了很好的支持。InnoDB 的全文索引采用倒排索引结构，支持多种语言的分词、词干提取和停用词过滤。InnoDB 还支持事务，这使得在进行数据插入、更新和删除操作时，全文索引的维护更加可靠。例如，在一个事务中插入多条记录并创建全文索引，InnoDB 能够保证操作的原子性，要么全部成功，要么全部回滚。

2. MyISAM 存储引擎

MyISAM 是 MySQL 早期常用的存储引擎，它也支持全文索引。MyISAM 的全文索引同样基于倒排索引结构，但在一些功能上与 InnoDB 略有不同。例如，MyISAM 不支持事务，在数据插入、更新和删除操作时，可能会导致索引维护的不一致性。另外，MyISAM 的全文索引在处理大数据量时的性能可能不如 InnoDB。不过，MyISAM 在一些简单场景下，如只读数据的表，仍然可以提供较好的全文索引性能。

3. 其他存储引擎

除了 InnoDB 和 MyISAM，MySQL 还有一些其他存储引擎，如 Memory、Archive 等。Memory 存储引擎主要用于内存表，它不支持全文索引，因为其设计目的是提供快速的内存数据存储和简单的查询，不适合处理复杂的文本搜索。Archive 存储引擎主要用于归档数据，它也不支持全文索引，因为其重点在于数据的压缩存储，而不是数据检索性能。

八、全文索引的高级应用

1. 多语言全文搜索

MySQL 的全文索引支持多种语言，在实际应用中，可以利用这一特性实现多语言的文本搜索。例如，在一个国际化的新闻网站中，文章可能以多种语言发布。可以对不同语言的文章内容列分别创建全文索引，并在查询时指定相应的语言模式。

-- 创建包含英文和法文文章的表
CREATE TABLE international_news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    english_content TEXT,
    french_content TEXT,
    FULLTEXT(english_content) WITH PARSER ngram,
    FULLTEXT(french_content) WITH PARSER ngram
);

-- 英文文章搜索
SELECT * FROM international_news
WHERE MATCH(english_content) AGAINST('breaking news' IN NATURAL LANGUAGE MODE);

-- 法文文章搜索
SELECT * FROM international_news
WHERE MATCH(french_content) AGAINST('actualités importantes' IN NATURAL LANGUAGE MODE);

2. 结合其他技术实现更强大的搜索

可以将 MySQL 的全文索引与其他技术结合，实现更强大的搜索功能。例如，与搜索引擎框架（如 Elasticsearch）结合。Elasticsearch 是一个分布式的全文搜索引擎，具有更高的可扩展性和搜索性能。可以将 MySQL 中的数据同步到 Elasticsearch 中，利用 Elasticsearch 的高级搜索功能，如分布式搜索、更灵活的查询语法等，同时保留 MySQL 的数据存储和事务管理功能。

具体实现可以通过定时任务或者数据库触发器来实现数据同步。例如，当在 MySQL 中插入一篇新文章时，通过触发器调用同步脚本，将文章内容同步到 Elasticsearch 中。这样，在进行搜索时，可以先在 Elasticsearch 中进行快速的全文搜索，获取相关文章的 ID，然后再从 MySQL 中获取完整的文章信息。

3. 自定义分词和词干提取

虽然 MySQL 自带了一些语言的分词和词干提取功能，但在某些特殊场景下，可能需要自定义分词和词干提取规则。MySQL 提供了插件机制来实现这一点。例如，可以编写一个自定义的分词插件，根据业务需求对文本进行分词。对于一些特定领域的术语，可能需要按照领域特定的规则进行拆分和处理。同样，对于词干提取，也可以根据业务需求自定义词干提取算法，以更好地满足文本搜索的需求。

九、常见问题与解决方法

1. 全文索引不生效

原因：

  • 查询语法错误，如使用了不支持全文索引的 LIKE 查询而不是 MATCH AGAINST。
  • 索引列类型不匹配，如在不支持的列类型上创建了全文索引。
  • 数据量过小，MySQL 可能优化掉全文索引的使用。

解决方法：

  • 检查查询语法，确保使用 MATCH AGAINST 语法进行全文索引查询。
  • 确认索引列的类型是否符合要求，如 CHAR、VARCHAR、TEXT 等。
  • 如果数据量过小，可以尝试增加数据量，或者通过 FORCE INDEX 强制使用全文索引：
SELECT * FROM articles FORCE INDEX(fulltext_index_name)
WHERE MATCH(title, content) AGAINST('sample' IN NATURAL LANGUAGE MODE);

2. 搜索结果不准确

原因：

  • 分词、词干提取或停用词过滤规则不符合业务需求。
  • 使用了不恰当的查询模式，如 WITH QUERY EXPANSION 可能导致返回结果过于宽泛。

解决方法：

  • 检查分词、词干提取和停用词过滤规则，根据业务需求进行调整。如果是中文搜索，可以考虑使用更适合中文的分词插件。
  • 调整查询模式，根据实际需求选择合适的查询模式，如在需要精确匹配时避免使用 WITH QUERY EXPANSION。

3. 索引维护性能问题

原因：

  • 频繁的插入、更新和删除操作导致索引碎片化。
  • 索引数量过多，增加了维护成本。

解决方法：

  • 定期使用 OPTIMIZE TABLE 语句对表和索引进行优化，减少索引碎片化。
  • 根据实际查询需求，合理减少不必要的索引，降低维护成本。

十、代码示例综合应用

假设我们正在开发一个博客系统，需要对博客文章进行全文索引以实现高效的搜索功能。以下是一个完整的示例，包括表创建、数据插入、索引创建和查询操作。

1. 创建博客文章表

CREATE TABLE blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 插入示例数据

INSERT INTO blog_posts (title, content) VALUES
('Introduction to MySQL Full - Text Index', 'This article will introduce the principles and applications of MySQL full - text index. We will cover how to create, use and optimize full - text index in MySQL.'),
('Benefits of Using Full - Text Index', 'Full - text index in MySQL can greatly improve the efficiency of text - based searches. It is especially useful when dealing with large amounts of text data.');

3. 创建全文索引

ALTER TABLE blog_posts
ADD FULLTEXT(title, content);

4. 使用全文索引进行查询

-- 搜索包含 “MySQL full - text index” 的文章
SELECT * FROM blog_posts
WHERE MATCH(title, content) AGAINST('MySQL full - text index' IN NATURAL LANGUAGE MODE);

-- 使用查询扩展搜索相关文章
SELECT * FROM blog_posts
WHERE MATCH(title, content) AGAINST('MySQL full - text index' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);

通过以上步骤，我们可以在博客系统中有效地利用 MySQL 的全文索引实现高效的文章搜索功能。在实际应用中，可以根据具体需求进一步优化和扩展这些操作，如结合用户权限控制、搜索结果排序等功能，提供更完善的用户体验。