MySQL全文索引FULLTEXT详解：告别低效LIKE模糊查询

我们都知道 InnoDB 在模糊查询数据时使用 %xx 会导致索引失效，但实际开发中这类需求却很常见。例如，搜索引擎需要根据用户输入的关键字进行全文查找，电商网站需要在商品的详细描述中匹配查询条件。这些场景下，传统的 B+ 树索引往往力不从心。

通过数值比较和范围过滤可以完成大多数查询，但对于基于相似度的关键字匹配，就需要更专业的工具——全文索引。

全文索引（Full-Text Search）是一种能够从数据库存储的整本书或整篇文章中查找任意信息的技术。它不仅可以定位到具体的词、句、段，还能进行各种统计与分析。

需要注意的是，早期的 MySQL 中，InnoDB 引擎并不支持全文检索。这一功能从 MySQL 5.6 版本开始才被引入。

倒排索引

全文检索的核心是倒排索引（inverted index）。它与 B+Tree 一样是一种索引结构，但在辅助表中存储的是单词与单词在一个或多个文档中位置之间的映射关系。这种映射通常利用关联数组实现，主要有两种形式：

• inverted file index：{单词， 单词所在文档的id}
• full inverted index：{单词， (单词所在文档的id， 在具体文档中的位置)}

上图为 inverted file index 的关联数组。可以看到单词 “code” 存在于文档 1 和文档 4 中。这种存储方式使得全文查询变得简单：直接根据单词即可找到包含它的文档。

而 full inverted index 存储了更详细的位置信息，例如关键字 “code” 存在于文档1的第6个单词和文档4的第8个单词。虽然它占用了更多空间，但能实现更精确的定位，并支持更复杂的搜索特性。

全文检索

创建全文索引

1. 创建表时创建全文索引
语法如下：

CREATE TABLE table_name (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    author VARCHAR(200),
    title VARCHAR(200),
    content TEXT(500),
    FULLTEXT full_index_name (col_name)
) ENGINE=InnoDB;

创建全文索引后，MySQL 会在后台建立一组特殊的索引表。可以通过以下语句查看：

SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE 'test/%';

如上图所示，通常会创建六个辅助索引表来构成倒排索引。当文档被分词时，单词与其位置信息、关联的 DOC_ID 会根据单词首字符的排序权重，在这些表中进行完全排序和分区。

2. 在已创建的表上创建全文索引
语法如下：

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

使用全文索引

MySQL 的全文检索查询语法是特定的，全文索引只能用于 InnoDB 或 MyISAM 引擎的表，并且只能建立在 CHAR、VARCHAR 或 TEXT 类型的列上。

其基本语法结构如下：

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}

全文搜索使用 MATCH() AGAINST() 语法。MATCH() 后接逗号分隔的要搜索的列名，AGAINST() 接收要搜索的字符串以及一个可选的搜索类型修饰符。全文检索主要分为三种模式：自然语言搜索、布尔搜索和查询扩展搜索。

Natural Language（自然语言模式）

自然语言搜索将搜索字符串解释为人类语言中的短语。MATCH() 默认采用 Natural Language 模式，用于查询包含指定关键字的文档。

我们通过一个示例来理解：

SELECT
    count(*) AS count
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL' );

这条语句查询 title 和 body 列中包含 ‘MySQL’ 关键字的行数。上述查询也可以写成另一种形式，有时效率更高：

SELECT
    count(IF(MATCH ( title, body ) 
        against ( 'MySQL' ), 1, NULL )) AS count
FROM
    `fts_articles`;

第二种写法避免了相关性排序统计，在某些情况下执行更快。

我们还可以查看每条记录的相关性得分：

SELECT
    *,
    MATCH ( title, body ) against ( 'MySQL' ) AS Relevance
FROM
    fts_articles;

相关性的计算基于以下几个条件：

1. 查询词是否在文档中出现。
2. 查询词在文档中出现的次数。
3. 查询词在索引列中的数量。
4. 有多少个文档包含该查询词。

对于 InnoDB 存储引擎的全文检索，还需要注意两个因素：

• 查询词如果位于停止词（stopword）列表中，则会被忽略。
• 查询词的长度必须在系统变量 innodb_ft_min_token_size 和 innodb_ft_max_token_size 定义的区间内。默认值分别是 3 和 84。

例如，查询停止词 ‘for’：

SELECT
    *,
    MATCH ( title, body ) against ( 'for' ) AS Relevance
FROM
    fts_articles;

可以看到，尽管 ‘for’ 在文档 2 和 4 中出现，但由于它是停止词，所以相关性得分为 0。理解这些查询优化的底层规则，有助于我们更好地设计搜索策略。

Boolean（布尔模式）

布尔搜索使用一套特殊的查询语言规则。查询字符串不仅包含要搜索的词，还可以包含指定逻辑关系的运算符，例如要求某个词必须存在或不得存在，或者调整词的权重。

例如，查询包含“Pease”但不包含“hot”的文档（+ 表示必须存在，- 表示必须不存在）：

select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);

布尔全文检索支持的运算符包括：

• +：表示该词必须存在。
• -：表示该词必须不存在。
• (no operator)：表示该词是可选的，但如果出现，其相关性会更高。
• @distance：表示查询的多个单词之间的距离需在 distance 字节之内。这被称为 临近搜索（Proximity Search）。例如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)。
• >：表示出现该词时增加相关性。
• <：表示出现该词时降低相关性。
• ~：表示允许出现该词，但出现时相关性为负（即起抑制作用）。
• *：通配符，表示以该词开头的单词。例如 lik* 可以匹配 lik、like、likes。
• " "：双引号表示精确匹配一个短语。

下面通过一系列示例来演示布尔模式的使用：

示例1：使用 + 和 -

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );

查询包含 ‘MySQL’ 但不包含 ‘YourSQL’ 的行。

示例2：无运算符（可选词）

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BOOLEAN MODE );

查询 ‘MySQL’ 或 ‘IBM’，同时包含两者的行相关性更高。

示例3：使用 @ 进行临近搜索

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BOOLEAN MODE );

查询 “DB2” 和 “IBM” 两个词之间距离在 3 字节之内的行。

示例4：使用 > 和 < 调整相关性

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)' IN BOOLEAN MODE );

查询同时包含 ‘MySQL’, ‘database’, ‘DBMS’ 的行，但包含 ‘database’ 的行相关性高于包含 ‘DBMS’ 的行。

示例5：使用 ~ 降低相关性

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BOOLEAN MODE );

查询包含 ‘MySQL’ 的行，但如果某行同时包含 ‘database’，则降低其相关性。

*示例6：使用 `` 通配符**

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( 'My*' IN BOOLEAN MODE );

查询包含以 ‘My’ 开头的单词（如 My, MySQL, MySQ）的行。

示例7：使用 " " 进行短语搜索

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH ( title, body ) AGAINST ( '"MySQL Security"' IN BOOLEAN MODE );

查询包含确切短语 ‘MySQL Security’ 的行。

Query Expansion（查询扩展模式）

查询扩展搜索是对自然语言搜索的增强，常用于用户输入的关键词过短或过于宽泛，需要系统进行“智能联想”时。例如，用户搜索 “database”，系统可能还会返回包含 “MySQL”、“Oracle”、“RDBMS” 的文档。

通过在查询中添加 WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以开启自动相关性反馈。该过程分为两个阶段：

1. 第一阶段：根据原始搜索词进行全文索引查询。
2. 第二阶段：利用第一阶段结果中高相关性的文档的分词，进行第二轮全文检索查询。

来看一个对比示例：

-- 创建索引
create FULLTEXT INDEX title_body_index on fts_articles(title,body);

-- 使用 Natural Language 模式查询 ‘database’
SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH(title,body) AGAINST('database');

-- 使用 Query Expansion 模式查询 ‘database’
SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH(title,body) AGAINST('database' WITH QUERY expansion);

可以看到，使用查询扩展后，返回了更多与“数据库”概念相关的文档（如包含 MySQL 的文章）。但需要注意的是，查询扩展可能会引入一些不相关的结果，因此在使用时应权衡利弊。

删除全文索引

1. 直接使用 DROP INDEX 删除

DROP INDEX full_idx_name ON db_name.table_name;

2. 使用 ALTER TABLE 删除

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

小结

本文从倒排索引的原理出发，详细介绍了 MySQL 中 FULLTEXT 索引的创建、使用（包括自然语言、布尔、查询扩展三种模式）和删除方法。通过具体的 SQL 示例，展示了如何利用全文索引实现高效、灵活的文本搜索，以替代效率低下的 LIKE '%...%' 模糊查询。

掌握全文索引对于处理MySQL中的文本搜索需求至关重要，它不仅是提升搜索性能的工具，更是实现数据库智能化查询的基石。希望这篇文章能帮助你在实际项目中更好地应用这一技术。如果你对数据库底层索引设计或其它高级特性有更多兴趣，欢迎在云栈社区与大家交流探讨。

---