在mysql的查询场景中，like模糊查询是匹配不确定内容时的常用手段，但很多开发者发现使用like查询后查询速度明显下降，甚至出现全表扫描的情况，这大多和索引的使用规则有关。了解like查询的索引生效条件，掌握对应的优化方法，对提升数据库性能非常重要。
mysql like查询的索引生效规则
mysql中like查询能否走索引，核心和通配符的位置有关，常见的规则如下：
- 当通配符
%放在匹配字符串的开头时，比如like '%abc'，索引无法生效，会触发全表扫描。 - 当通配符
%放在匹配字符串的结尾时，比如like 'abc%'，如果查询的字段有对应的普通索引，索引可以正常生效。 - 当通配符
%同时出现在匹配字符串的开头和结尾时，比如like '%abc%'，普通索引无法生效，查询会走全表扫描。
验证索引生效情况
我们可以创建测试表来验证上述规则，首先创建测试表和索引：
-- 创建测试表
CREATE TABLE test_user (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO test_user (username, email) VALUES
('zhangsan', 'zhangsan@ipipp.com'),
('lisi', 'lisi@ipipp.com'),
('wangwu', 'wangwu@ipipp.com'),
('zhaoliu', 'zhaoliu@ipipp.com');
接着使用EXPLAIN关键字查看不同like查询的执行计划：
-- 通配符在结尾，索引生效 EXPLAIN SELECT * FROM test_user WHERE username LIKE 'zhang%'; -- 通配符在开头，索引不生效 EXPLAIN SELECT * FROM test_user WHERE username LIKE '%san'; -- 通配符在两端，索引不生效 EXPLAIN SELECT * FROM test_user WHERE username LIKE '%zhang%';
执行后可以看到，只有第一种查询的type字段为range，说明走了索引，后两种的type为ALL，属于全表扫描。
mysql模糊查询的优化方法
1. 调整通配符位置
如果业务场景允许，尽量把通配符放在匹配字符串的结尾，比如需要查询用户名以某个字符开头的用户，就使用like 'xxx%'的形式，这样普通索引就可以正常生效，避免全表扫描。
2. 使用覆盖索引
如果查询的字段都在索引中，即使like查询的通配符在开头，也可能通过覆盖索引减少回表操作，提升查询效率。比如上面的测试表，如果只需要查询用户名，不需要查询邮箱，可以这样写查询语句：
-- 覆盖索引，查询字段都在idx_username索引中 EXPLAIN SELECT username FROM test_user WHERE username LIKE '%zhang%';
虽然这个查询还是会扫描整个索引树，但不需要回表查询数据行，性能比全表扫描要好。
3. 使用全文索引
如果需要进行包含关系的模糊查询，比如查询内容中包含某个关键词，普通like查询加通配符在两端无法走索引，这时候可以使用mysql的全文索引。首先给字段添加全文索引：
-- 给username字段添加全文索引 ALTER TABLE test_user ADD FULLTEXT INDEX ft_idx_username (username);
然后使用MATCH AGAINST语法进行查询：
-- 使用全文索引查询包含zhang的用户名
SELECT * FROM test_user WHERE MATCH(username) AGAINST('zhang');
全文索引适合大文本字段的模糊匹配场景，查询效率远高于like '%关键词%'的形式。
4. 使用应用层缓存
如果模糊查询的结果集变化不频繁，可以把查询结果缓存到应用层，比如使用Redis缓存查询结果，下次查询时先查缓存，没有缓存再查数据库，减少数据库的查询压力。
5. 使用反向索引配合like查询
如果必须查询以某个字符串结尾的内容，比如查询邮箱以某个域名结尾的用户，可以把字段内容反转后存储，再给反转后的字段加索引。比如邮箱是zhangsan@ipipp.com，反转后是moc.ppipi@nahzgnahz，查询以ipipp.com结尾的邮箱时，就可以查询反转后字段以moc.ppipi开头的内容，这样就能走索引。实现方式如下：
-- 添加反转邮箱字段和索引
ALTER TABLE test_user ADD COLUMN reverse_email VARCHAR(100);
UPDATE test_user SET reverse_email = REVERSE(email);
ALTER TABLE test_user ADD INDEX idx_reverse_email (reverse_email);
-- 查询邮箱以ipipp.com结尾的用户，相当于反转后字段以moc.ppipi开头
SELECT * FROM test_user WHERE reverse_email LIKE REVERSE('%ipipp.com') || '%';
这种方式适合固定后缀的查询场景，需要额外维护反转字段的数据一致性。
注意事项
在使用like查询优化时，需要注意索引的选择性，如果字段的重复值很多，即使走了索引，查询效率也可能不高。另外，全文索引有最小搜索长度的限制，默认是4个字符，小于4个字符的关键词无法匹配到结果，需要根据实际业务调整配置。同时，不要盲目添加索引，过多的索引会影响插入和更新数据的性能，需要根据实际查询场景合理设计索引。