SQL模糊查询是业务开发中高频使用的查询方式，主要用于匹配包含特定字符的记录，但错误的LIKE写法很容易导致原本生效的索引失效，进而引发全表扫描，大幅降低查询效率。理解索引失效的底层逻辑，掌握正确的LIKE优化方法，是开发者优化数据库性能的重要技能。
一、SQL模糊查询索引失效的核心原因
要理解索引为什么会失效，首先需要明确B+树索引的匹配规则：B+树索引是按照索引列的值从小到大有序排列的，查询时只能从索引的最左前缀开始匹配，才能快速定位到目标数据。
常见的LIKE模糊查询有三种写法，其中两种会直接破坏最左前缀匹配规则：
- 左模糊查询：条件为
LIKE '%关键词'，需要匹配所有以任意字符开头、以目标关键词结尾的记录，索引的有序性无法被利用，只能全表扫描。 - 全模糊查询：条件为
LIKE '%关键词%'，需要匹配所有包含目标关键词的记录，同样无法利用索引的有序性，触发全表扫描。 - 右模糊查询：条件为
LIKE '关键词%'，匹配以目标关键词开头的记录，符合最左前缀匹配规则，索引可以正常生效。
我们可以通过执行计划直观看到不同写法的差异，以下是测试用的表结构和查询示例：
-- 创建测试表，id为主键，name字段建立普通索引
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
INDEX idx_name (name)
);
-- 插入测试数据
INSERT INTO user_info (name, age) VALUES ('张三', 20), ('张小明', 22), ('李四', 25), ('王张伟', 28);
-- 右模糊查询，查看执行计划
EXPLAIN SELECT * FROM user_info WHERE name LIKE '张%';
-- 左模糊查询，查看执行计划
EXPLAIN SELECT * FROM user_info WHERE name LIKE '%张';
-- 全模糊查询，查看执行计划
EXPLAIN SELECT * FROM user_info WHERE name LIKE '%张%';
执行上述三个EXPLAIN语句后，会发现右模糊查询的type字段为range，说明用到了索引范围扫描；而左模糊和全模糊查询的type字段为ALL，说明触发了全表扫描，索引没有生效。
二、LIKE写法的优化方案
1. 优先使用右模糊查询
如果业务场景允许，尽量把模糊查询的条件调整为右模糊形式，比如原本需要查询名字中包含“张”的用户，如果业务只需要匹配名字以“张”开头的用户，就可以把LIKE '%张%'改为LIKE '张%'，直接利用索引提升查询速度。
2. 业务层拆分关键词
如果必须使用全模糊查询，且查询的关键词长度较长，可以在业务层对关键词进行拆分，比如要查询包含“数据库优化”的记录，可以拆分为“数据库”和“优化”两个关键词，分别用右模糊查询后再取交集，减少全表扫描的范围。
-- 拆分后的查询逻辑，假设两个右模糊查询都能用到索引 SELECT * FROM user_info WHERE name LIKE '数据库%' INTERSECT SELECT * FROM user_info WHERE name LIKE '优化%';
3. 使用覆盖索引减少回表
如果查询的字段全部包含在索引中，即使使用左模糊或全模糊查询，也可能避免回表操作，提升查询效率。比如只需要查询用户的name字段，而name字段有索引，那么全模糊查询时虽然会扫描整个索引树，但不需要回表查询聚簇索引，性能比全表扫描要好。
-- 覆盖索引查询，只查询name字段，idx_name索引包含该字段 SELECT name FROM user_info WHERE name LIKE '%张%';
4. 反向索引改造
对于固定的左模糊查询场景，可以对字段值进行反转存储，比如原本存储的name是“张三”，反转后存储为“三张”，查询左模糊LIKE '%张'时，改为查询反转字段的右模糊LIKE '张%'，就可以利用索引。不过这种方式需要额外维护反转字段，适合查询模式固定的场景。
-- 添加反转字段并建立索引
ALTER TABLE user_info ADD name_reverse VARCHAR(50);
UPDATE user_info SET name_reverse = REVERSE(name);
CREATE INDEX idx_name_reverse ON user_info(name_reverse);
-- 查询名字以张结尾的用户，原本是LIKE '%张'，现在改为
SELECT * FROM user_info WHERE name_reverse LIKE REVERSE('张') || '%';
三、注意事项
不是所有的LIKE查询都会绝对导致索引失效，比如MySQL的like查询在某些情况下，即使使用全模糊，如果优化器判断全表扫描的成本比索引扫描更高，也可能选择使用索引，但这种情况并不稳定，不能作为常规优化方案。另外，索引是否生效还和表的数据量、索引选择性等因素有关，实际优化时建议结合执行计划具体分析。
优化模糊查询时，首先要明确业务需求是否真的需要左模糊或全模糊查询，优先从业务层面调整查询条件，再结合技术手段优化，才能达到最好的性能效果。