如何优化模式匹配like %xxx%的查询性能

在数据库日常查询开发中，like %xxx%是非常常见的模式匹配写法，用于匹配字段中包含指定字符串的记录，但这种写法如果没有做针对性优化，很容易引发全表扫描，导致查询性能急剧下降。当表数据量达到百万甚至千万级别时，一次like %xxx%查询可能耗时数秒甚至数十秒，严重影响业务系统的响应速度。

问题场景还原

假设我们有一个用户表user_info，表结构如下，其中username字段存储用户昵称，业务需要支持根据昵称模糊查询用户，查询语句为select * from user_info where username like '%明%'。

-- 用户表结构
CREATE TABLE user_info (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL COMMENT '用户昵称',
    age INT COMMENT '年龄',
    create_time DATETIME COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 原始查询语句
SELECT * FROM user_info WHERE username LIKE '%明%';

当user_info表只有几千条数据时，这个查询可能没有明显性能问题，但如果表数据量增长到100万条，执行这个查询会发现查询耗时超过2秒，查看执行计划可以看到type为ALL，也就是全表扫描。

优化方案分析

方案一：调整索引类型，使用反向索引+前缀匹配

普通B树索引无法支持like %xxx%的查询，因为B树索引是从左到右有序的，前缀模糊可以走索引，但前后模糊无法利用有序性。我们可以把username字段的内容反转后存储到冗余字段，或者查询时先反转匹配条件，再用前缀匹配走索引。

首先给表新增一个冗余字段username_reverse，存储username反转后的字符串，并给这个字段加索引：

-- 新增冗余字段
ALTER TABLE user_info ADD COLUMN username_reverse VARCHAR(50) COMMENT '用户名反转字段';

-- 给冗余字段添加索引
CREATE INDEX idx_username_reverse ON user_info(username_reverse);

-- 更新已有数据的冗余字段值
UPDATE user_info SET username_reverse = REVERSE(username);

后续新增用户时，需要同步维护username_reverse字段的值，比如插入时同时写入反转后的字符串：

-- 插入新用户时同步维护反转字段
INSERT INTO user_info (username, username_reverse, age, create_time) 
VALUES ('张明', REVERSE('张明'), 25, NOW());

查询时，把原来的like %明%转换为对反转字段的前缀匹配：比如要匹配包含明的用户名，反转后是明%前缀匹配，因为原字符串包含明等价于反转后的字符串以明开头：

-- 优化后的查询语句
SELECT * FROM user_info WHERE username_reverse LIKE '明%';

查看执行计划可以看到type变为range，走了idx_username_reverse索引，查询耗时从2秒降低到0.1秒以内。

方案二：使用全文索引替代like查询

如果数据库支持全文索引（比如MySQL 5.7+的InnoDB引擎支持全文索引），可以直接使用全文索引来实现模糊匹配，性能比like %xxx%好很多。

首先给username字段添加全文索引：

-- 添加全文索引
ALTER TABLE user_info ADD FULLTEXT INDEX ft_idx_username (username);

然后使用MATCH...AGAINST语法替代like查询：

-- 使用全文索引查询
SELECT * FROM user_info WHERE MATCH(username) AGAINST('明' IN BOOLEAN MODE);

这种方式的优点是无需维护冗余字段，原生支持模糊匹配，缺点是全文索引有最小分词长度限制（MySQL默认是4个字符），如果匹配的关键词长度小于最小分词长度，可能无法命中索引，需要根据业务场景调整配置。

方案三：限制查询范围减少扫描数据量

如果业务上可以限制查询的范围，比如只查询最近3个月创建的用户，或者只查询年龄在某个区间的用户，可以在like查询前加上其他过滤条件，先缩小扫描的数据量，再执行模糊匹配。

-- 加范围条件缩小扫描数据量
SELECT * FROM user_info 
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
  AND username LIKE '%明%';

如果create_time字段有索引，会先通过时间条件过滤掉大部分历史数据，减少like扫描的行数，从而提升查询性能，这种方式适合业务上有明确范围限制的场景。

不同方案对比

我们可以通过下表对比三种方案的适用场景和优缺点：

总结

like %xxx%的优化没有通用的最优解，需要根据实际的业务场景、数据量、数据库类型来选择合适的方法。如果数据量极大且模糊匹配是核心高频查询，优先选择反向索引或者全文索引的方案；如果业务上有范围限制，可以先尝试加范围条件缩小扫描数据量。同时要注意，优化后需要定期查看执行计划，确认索引是否生效，避免因为数据分布变化导致优化失效。

SQL优化模式匹配like查询数据库索引修改时间：2026-06-27 11:54:46