在mysql的查询场景中，使用like进行模糊匹配是非常常见的需求，但这类查询如果写法不当很容易导致索引失效，进而让查询变成全表扫描，大幅降低性能。了解like查询的索引使用规则，掌握对应的优化方法，是数据库性能优化的重要部分。
like查询的索引失效规则
mysql中B+树索引是按照索引列的值有序存储的，like查询的索引使用情况主要和通配符的位置有关：
- 当通配符
%放在匹配字符串的开头时，比如like '%abc'，索引会失效，因为无法确定匹配范围的起始点，只能全表扫描。 - 当通配符
%放在匹配字符串的结尾时，比如like 'abc%'，索引可以正常使用，因为可以从abc开头的位置向后匹配。 - 当通配符
%同时出现在开头和结尾时，比如like '%abc%'，索引同样会失效。
我们可以通过执行计划验证这个规则，假设有一张用户表user，在username字段上建立了普通索引：
-- 创建测试表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO `user`(`username`, `age`) VALUES ('zhangsan', 20), ('lisi', 22), ('wangwu', 25), ('zhaoliu', 28);
-- 查看like查询的执行计划
EXPLAIN SELECT * FROM `user` WHERE `username` LIKE 'zhang%';
EXPLAIN SELECT * FROM `user` WHERE `username` LIKE '%san';
执行上述语句后可以看到，第一个查询的type是range，表示使用了索引范围扫描；第二个查询的type是ALL，表示进行了全表扫描，索引没有生效。
like查询的优化方案
1. 尽量使用前缀匹配
如果业务允许，优先将%放在匹配字符串的末尾，使用like '前缀%'的形式，这样可以正常触发索引。比如要匹配所有姓张的用户，就可以写成like '张%'，而不是like '%张%'。
2. 使用覆盖索引
如果查询的字段都包含在索引中，即使like查询本身无法使用索引进行匹配，也可能会因为覆盖索引减少回表操作提升性能。比如上面的用户表，如果只需要查询username和id字段，这两个字段都在idx_username索引中（主键id会默认加入二级索引的叶子节点），可以改写查询：
-- 使用覆盖索引的查询 EXPLAIN SELECT `id`, `username` FROM `user` WHERE `username` LIKE '%san%';
执行计划中的Extra字段会出现Using index，表示使用了覆盖索引，虽然还是需要扫描整个索引树，但不需要回表查询聚簇索引，性能比全表扫描要好。
3. 使用全文索引
如果需要进行全文模糊匹配，比如搜索内容中包含某个关键词，可以使用mysql的全文索引。首先给字段创建全文索引：
-- 创建全文索引
ALTER TABLE `user` ADD FULLTEXT INDEX `ft_idx_username` (`username`);
-- 使用全文索引查询
SELECT * FROM `user` WHERE MATCH(`username`) AGAINST('san');
全文索引适合长文本的模糊匹配场景，比like查询的效率高很多，但需要注意全文索引的分词规则，以及最小搜索长度等参数的配置。
4. 反向索引法
如果必须做后缀匹配（比如like '%abc'），可以把字段的内容反转后存储到一个新字段，给新字段建立索引，查询时把匹配条件也反转，变成前缀匹配。比如要匹配username以san结尾的记录：
-- 添加反转字段并建立索引
ALTER TABLE `user` ADD `username_reverse` varchar(50) DEFAULT NULL;
UPDATE `user` SET `username_reverse` = REVERSE(`username`);
CREATE INDEX `idx_username_reverse` ON `user`(`username_reverse`);
-- 查询时反转匹配条件
SELECT * FROM `user` WHERE `username_reverse` LIKE REVERSE('san') || '%';
这种方法需要额外维护反转字段，适合固定后缀匹配的场景。
其他常见的mysql索引失效场景
除了like查询的不当使用，还有很多其他情况会导致mysql索引失效：
- 对索引字段进行函数操作或者表达式计算，比如
WHERE YEAR(create_time) = 2023，索引会失效。 - 查询条件中使用
or连接，且or两侧的字段不是都有索引，索引会失效。 - 查询条件中索引字段发生隐式类型转换，比如字符串字段用数字匹配，
WHERE username = 123，索引会失效。 - 查询优化器认为全表扫描比使用索引更高效，比如查询的数据量占表总数据量的比例很高时，会自动放弃使用索引。
- 使用
!=、<>、is null、is not null等条件时，部分情况下索引会失效。
总结
优化mysql中带有like条件的查询，核心是尽量避免通配符放在匹配字符串的开头，优先使用前缀匹配。如果无法满足前缀匹配的需求，可以根据场景选择覆盖索引、全文索引或者反向索引的方法。同时要熟悉其他索引失效的场景，在编写查询语句时避开这些坑，才能最大程度发挥索引的作用，提升数据库查询性能。