在SQL数据库的实际使用中，经常需要查询表中包含特定关键字的记录，比如文章内容表查找包含某个技术名词的条目，商品表查找包含特定描述的商品。传统的LIKE '%关键字%'查询方式在数据量较小时可以满足需求，但当表数据达到百万甚至千万级别时，查询性能会急剧下降。FULLTEXT全文索引是数据库提供的专门用于文本关键字检索的功能，能够大幅提升包含关键字行的查询效率。
FULLTEXT全文索引的基本概念
FULLTEXT全文索引是一种特殊类型的索引，主要针对文本类型的字段创建，能够对字段中的文本内容进行分词处理，建立关键字和对应行记录的映射关系。当执行关键字检索时，数据库可以直接通过索引定位到包含关键字的行，不需要逐行扫描全表数据。
目前主流的关系型数据库都支持FULLTEXT全文索引，包括MySQL、PostgreSQL、SQL Server等，不同数据库的实现细节略有差异，但核心的检索逻辑基本一致。需要注意的是，全文索引通常只支持CHAR、VARCHAR、TEXT等文本类型的字段，数值类型、日期类型等字段无法创建全文索引。
创建FULLTEXT全文索引的方法
下面以MySQL数据库为例，介绍创建FULLTEXT全文索引的两种方式，分别是建表时创建和建表后添加。
建表时创建全文索引
在创建表语句中，通过FULLTEXT关键字指定需要创建全文索引的字段即可，示例如下：
-- 创建文章表，同时给title和content字段创建联合全文索引
CREATE TABLE article (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
publish_time DATETIME NOT NULL,
FULLTEXT idx_fulltext_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
建表后添加全文索引
如果表已经存在，可以通过ALTER TABLE或者CREATE INDEX语句添加全文索引，示例如下：
-- 方式1：使用ALTER TABLE添加全文索引 ALTER TABLE article ADD FULLTEXT idx_fulltext_content (content); -- 方式2：使用CREATE INDEX添加全文索引 CREATE FULLTEXT INDEX idx_fulltext_title ON article (title);
创建全文索引后，数据库会自动对索引字段的内容进行分词，分词规则会根据数据库的字符集和全文索引的配置有所不同，比如中文场景下可能需要配置ngram全文解析器来支持中文分词。
使用FULLTEXT检索包含关键字的行
创建好全文索引后，就可以使用MATCH...AGAINST语法来检索包含关键字的行，这是全文索引专用的检索语法，比LIKE查询效率更高。
基础检索语法
基本的检索格式为MATCH(索引字段列表) AGAINST(关键字)，示例如下，查询文章内容中包含“SQL优化”关键字的记录：
-- 查询content字段包含“SQL优化”的文章
SELECT id, title, content
FROM article
WHERE MATCH(content) AGAINST('SQL优化');
如果需要同时匹配多个字段，只需要在MATCH中指定多个字段即可，和创建索引时的字段列表对应：
-- 查询title或者content中包含“数据库”的记录
SELECT id, title, content
FROM article
WHERE MATCH(title, content) AGAINST('数据库');
检索模式说明
AGAINST支持多种检索模式，常用的有以下两种：
- 自然语言模式：默认模式，会检索包含指定关键字的行，并且返回的结果会按照相关性打分排序，匹配度越高的记录排在越前面。
- 布尔模式：通过
IN BOOLEAN MODE指定，支持更复杂的检索条件，比如必须包含某个关键字、必须不包含某个关键字、关键字权重调整等。
布尔模式的示例如下：
-- 布尔模式检索：必须包含“SQL”，不能包含“MySQL”，可以包含“优化”
SELECT id, title, content
FROM article
WHERE MATCH(title, content) AGAINST('+SQL -MySQL 优化' IN BOOLEAN MODE);
FULLTEXT和LIKE查询的对比
为了更清晰地了解两种查询方式的差异，下面从多个维度进行对比：
| 对比维度 | FULLTEXT全文索引检索 | LIKE '%关键字%'查询 |
|---|---|---|
| 大数据量性能 | 高，通过索引定位，不需要全表扫描 | 低，会触发全表扫描，数据量越大越慢 |
| 检索能力 | 支持分词、相关性排序、复杂布尔条件 | 仅支持简单的模糊匹配，不支持分词 |
| 适用场景 | 大文本字段的关键字检索，数据量大的场景 | 小数据量，短文本的简单匹配场景 |
| 索引维护成本 | 较高，插入、更新文本字段时会更新索引 | 无额外索引维护成本 |
使用注意事项
在使用FULLTEXT全文索引时，需要注意以下几点：
- 全文索引的最小分词长度默认是4个字符（MySQL默认配置），小于这个长度的关键字不会被索引，也无法通过全文索引检索到，可以通过修改数据库配置调整这个参数。
- 中文场景下，默认的英文分词器无法正确拆分中文词语，需要配置ngram全文解析器，创建索引时指定
WITH PARSER ngram即可。 - 全文索引的更新是异步的，插入或者更新数据后，可能不会立即被索引到，存在短暂的延迟，如果对实时性要求极高需要额外注意。
- 如果检索的关键字在表中出现的频率非常高，比如超过50%的记录都包含该关键字，全文索引可能会认为该关键字没有区分度，返回空结果，这是全文索引的自然语言模式特性。
通过合理使用FULLTEXT全文索引，能够高效解决SQL中查找包含关键字行的问题，尤其是在数据量较大的业务场景中，相比传统的LIKE查询能带来数倍甚至数十倍的性能提升。开发者可以根据实际的业务需求和数据特点，选择合适的检索方式。