在SQL查询的实际应用中，模糊匹配是非常常见的需求，多数开发人员会优先选择LIKE配合通配符来实现。但当数据量增长到一定程度时，这种方式的性能缺陷会愈发明显，此时可以考虑使用全文索引来替代传统的LIKE查询，提升检索效率。
LIKE查询的性能问题
LIKE查询的性能表现和通配符的位置直接相关，我们可以把常见的使用场景分为三类：
- 后置通配符：比如
LIKE '张三%'，这种场景如果字段上有普通索引，数据库可以利用索引的有序性快速定位到匹配的开头，性能相对较好。 - 前置通配符：比如
LIKE '%张三'或者LIKE '%张三%'，此时索引无法发挥作用，数据库只能进行全表扫描，逐行匹配符合条件的数据，数据量越大查询耗时越长。 - 无通配符：此时LIKE等价于等于判断，性能和使用等于号的场景一致。
当表中数据量达到几十万甚至上百万时，前置通配符的LIKE查询可能需要几秒甚至几十秒才能返回结果，严重影响业务系统的响应速度。
全文索引的工作原理
全文索引是数据库为文本字段提供的专门索引类型，它会对字段内容按照语义进行分词，建立词和记录位置的映射关系。当执行全文检索时，数据库直接通过索引找到匹配的词对应的记录，不需要逐行扫描全表，因此检索效率远高于前置通配符的LIKE查询。
目前主流的关系型数据库都支持全文索引功能，比如MySQL、PostgreSQL、SQL Server等，不同数据库的具体语法略有差异，但核心逻辑一致。
两种方案的实现示例
普通LIKE查询示例
假设我们有一张用户表user，其中bio字段存储用户的个人简介，现在需要查询简介中包含“后端开发”的用户：
-- 创建用户表
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
bio TEXT
);
-- 插入测试数据
INSERT INTO user (name, bio) VALUES
('张三', '我是从事后端开发工作的工程师'),
('李四', '我喜欢研究前端开发技术'),
('王五', '我负责后端开发相关的项目运维');
-- 使用LIKE查询匹配简介中包含后端开发的用户
SELECT id, name, bio FROM user WHERE bio LIKE '%后端开发%';
当user表数据量很大时，这条查询会触发全表扫描，性能较差。
全文索引替换示例
我们可以使用全文索引来优化上述查询，以MySQL为例，实现步骤如下：
-- 为bio字段添加全文索引，索引名称为idx_bio_fulltext
ALTER TABLE user ADD FULLTEXT INDEX idx_bio_fulltext (bio);
-- 使用全文检索语法查询简介中包含后端开发的用户
SELECT id, name, bio FROM user
WHERE MATCH(bio) AGAINST('后端开发' IN NATURAL LANGUAGE MODE);
上述查询会直接通过全文索引定位匹配的记录，不需要全表扫描，在数据量较大时，查询耗时会比LIKE查询降低几个数量级。
两种方案的适用场景
并不是所有场景都适合用全文索引替换LIKE查询，我们可以根据实际需求选择：
| 场景 | 推荐方案 | 原因 |
|---|---|---|
| 数据量小，查询频率低 | LIKE查询 | 实现简单，不需要额外维护索引，性能差异不明显 |
| 数据量大，需要模糊匹配文本内容 | 全文索引 | 检索效率高，适合大文本字段的模糊匹配需求 |
| 需要精确匹配前缀内容 | LIKE后置通配符/普通索引 | 普通索引已经能满足性能要求，不需要引入全文索引 |
| 需要匹配特殊字符、短字符串 | LIKE查询 | 全文索引的分词逻辑可能过滤特殊字符，导致匹配不准确 |
全文索引的使用注意事项
- 全文索引对短文本的分词效果可能不佳，比如单个字符或者两个字符的关键词，可能无法被正确索引，需要结合数据库的配置调整最小分词长度。
- 全文索引会占用额外的存储空间，并且会影响写入数据的性能，因为每次插入、更新数据时都需要同步更新全文索引，所以在写入频繁的表中需要谨慎使用。
- 不同数据库的全文索引语法和支持的功能有差异，比如MySQL的全文索引默认不支持中文分词，需要安装额外的插件或者升级到较高版本才能正常使用，使用前需要确认数据库的相关特性。
优化SQL查询时需要结合实际的业务场景和数据特征选择方案，不要盲目追求新技术，适合的才是最好的。