作为一个天天跟慢SQL打交道的DBA/后端开发我想“模糊查询导致索引失效”这件事应该算是MySQL使用中最经典的痛点之一了。先问个问题你写WHERE name LIKE %张%的时候是不是被面试官或同事问过一句“索引会失效你知道吗”但业务需求就摆在那里——前端搜索框就要求“包含”而不是“前缀匹配”两边必须加%这该怎么办这篇内容我打算从最底层的索引原理开始把“为什么%关键词%走不了索引”这个根本问题掰开揉碎地讲清楚然后重点给出落地可行的几种方案覆盖索引、延迟关联、全文索引、生成列反向存储。每种方案都给到可以直接抄作业的建表语句、SQL写法和EXPLAIN验证结果最后补上我在实际项目中踩过的坑和选型建议。先说结论模糊查询的“索引失效”不是死局MySQL里至少有三四种办法能绕过去关键看你愿不愿意改表结构、改查询方式。1. 为什么模糊查询在MySQL里这么“费劲”1.1 先搞懂B树索引的“左前缀”原则绝大多数MySQL索引用的是B树结构它有几个特点叶子节点按索引列的值从小到大有序排列并且叶子节点之间通过指针串联形成一个有序的双向链表。所以搜索引擎要想高效工作就必须利用这个“有序性”去定位起点。当执行WHERE name LIKE 张%时MySQL可以在索引树里找到第一个以“张”开头的记录然后沿着链表顺序往下扫直到不满足条件为止。这种匹配叫范围匹配它只需要从索引树中定位一次起始位置扫描到边界就停代价可控所以能走索引。而当执行WHERE name LIKE %张%时问题出现了这条语句的条件不再是一个确定的前缀而是“包含”关系。查询优化器无法定位索引树中的准确起点因为满足条件的记录可能散落在索引树的任何位置。换句话说你无从利用B树的有序性进行快速剪枝只能全表/全索引扫描。1.2 从EXPLAIN看索引失效的“现场”看个最直观的例子。假设有张用户表CREATE TABLE t_user ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL DEFAULT , phone VARCHAR(20) NOT NULL DEFAULT , PRIMARY KEY (id), KEY idx_name (name) ) ENGINEInnoDB;分别执行EXPLAIN SELECT * FROM t_user WHERE name LIKE 张%; EXPLAIN SELECT * FROM t_user WHERE name LIKE %张%;第一条的结果里type是rangekey是idx_name走索引。第二条的结果里type变成了ALLkey为 NULL全表扫描。这就是最经典的“索引失效场景”。参数解释一下type列代表访问类型从好到差一般是system const eq_ref ref range index ALL。range至少是范围扫描还能享受索引带来的有序性福利而ALL是全表扫意味着每一行都要做字符串匹配判断数据量大一点就是灾难。1.3 两张图看懂为什么“后缀匹配”和“包含匹配”差异那么大从B树结构理解会更清晰LIKE 张%索引树是一种字典目录你知道要找“张”这个音翻到那一页顺着目录往下读即可。LIKE %张或LIKE %张%你只知道关键词可能在姓名中间或结尾如同拿到一本没有目录的书只能从头到尾逐页翻找哪个句子里出现过“张”字。这个类比基本还原了MySQL索引扫描的真实逻辑。所以凡是%在关键词左边无论是LIKE %张还是LIKE %张%索引大概率都用不上。这也是标题里说的“字段两边都能加上%”的核心矛盾所在。这里有一个常被忽略的细节在InnoDB中LIKE 张%能走索引本质上不是“前缀匹配”有多特殊而是它转换成了张 name 下一条这样的范围条件优化器可以把索引当作范围查询的跳板。理解了这一点后面很多“曲线救国”的思路就顺理成章了。2. 方案一覆盖索引让优化器“白白扫码”2.1 覆盖索引的原理先看一个现象有时LIKE %张%明明没走索引但你只查索引字段时EXPLAIN却显示type indexExtra Using index。这不是优化器灵光一闪而是Covering Index覆盖索引在起作用。覆盖索引 查询所需的所有字段恰好都包含在某个二级索引中。此时即使需要全索引扫描也不需要回表拿数据因为索引B树的叶子节点上已经带着你要的所有字段。回表是随机IO全索引扫描是顺序IO后者的成本要比随机IO低非常多。所以虽然扫描范围仍然是“全索引”但省去了回表性能会大幅提升。2.2 实操从全表扫到“索引全扫”以上面的用户表为例业务上经常需要根据姓名做模糊过滤同时只需要返回姓名和电话。ALTER TABLE t_user ADD INDEX idx_name_phone (name, phone);然后执行EXPLAIN SELECT name, phone FROM t_user WHERE name LIKE %张%;结果大概率是id | select_type | table | type | key | key_len | Extra 1 | SIMPLE | t_user | index| idx_name_phone | 87 | Using where; Using indextype从ALL变成indexExtra出现Using index。虽然typeindex仍不算高效但至少是顺序扫描整个索引而非全表而且不需要回表IO成本和cpu匹配负担都降了下来。数据量大到150万行时这个优化通常能带来3~5倍的性能提升。注意一个关键点不要把业务需要的所有字段都加进索引索引是额外占用磁盘和内存的。覆盖索引的精髓是把查询频率极高的“提交组合”放进索引里比如这里的name phone。2.3 延迟关联把“回表”成本压到最低覆盖索引还有个高级用法叫延迟关联Deferred Join。思路很简单先用覆盖索引快速定位满足模糊条件的ID列表再通过主键批量回表查完整记录。SELECT t.* FROM t_user t INNER JOIN ( SELECT id FROM t_user WHERE name LIKE %张% ) tmp ON t.id tmp.id;内层子查询只查id而id是主键天然在索引里存在所以会走Using index但要回表的只有内层满足条件的那几行。如果命中集合很小整体性能比直接全表扫描好得多。这个方案特别适用于那些“模糊查询条件很散但查询结果集较小”的场景。我见过一个订单管理系统的案例运营人员根据收货人姓名模糊筛选订单1800万行的订单表直接LIKE %某某%跑了12秒改成延迟关联后降到500毫秒。为什么有这么大差距因为满足条件的订单一般就几十条先全索引扫一遍拿到ID代价可控再按ID回表取数据只取几十条整体开销自然小。注意这个优化手段的前提是索引本身足够窄不要让不必要的字段进入索引。另一点是如果模糊条件命中率太高内层子查询会把ID列表拉得很大延迟关联的优势会被削弱。3. 方案二全文索引让“两边%”真正走索引3.1 全文索引和普通索引的底层差异看到这你可能会问“有没有办法让两边都加%的查询真正走索引而不是靠扫描”答案是有那就是全文索引FULLTEXT INDEX。全文索引的底层设计思路与B树不同它更像一本书末尾的“主题词索引”——先把文本中的字/词切出来建立“词语 - 文档ID列表”的映射关系。查询时直接查这个映射表跟位置无关所以它天然支持“包含”语义不受前后缀限制。MySQL 5.7.6以后InnoDB终于原生支持了全文索引并且提供了一个专门解决中文分词问题的插件ngram解析器。3.2 建全文索引 用MATCH AGAINST查假设有篇文章表CREATE TABLE t_article ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(200) NOT NULL, content TEXT, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;创建全文索引ALTER TABLE t_article ADD FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram;这样建出来的是一个同时覆盖title和content两个字段的全文索引。注意关键词WITH PARSER ngram不加的话MySQL默认用空格分词对中文基本无效。查询时改成SELECT * FROM t_article WHERE MATCH(title, content) AGAINST(大数据 IN NATURAL LANGUAGE MODE);执行计划里type会是fulltext表示真的走了全文索引。这个MATCH AGAINST的写法和普通LIKE完全不同它是专门为全文检索设计的语法。自带一个基本的分词验证示例假设表里有两条记录一条标题是“大数据平台实践”另一条是“数据治理与大数据架构”。执行上面MATCH查询时两条都会被命中因为“大数据”这个关键词被ngram切出来以后在两条记录的索引项里都存在。3.3 ngram分词器的参数与调优ngram的默认分词长度是2也就是把“你好世界”切分成“你好”、“好世”、“世界”三个token。ngram_token_size这个参数可以调但它是全局参数修改后必须重启MySQL实例而且不同版本对动态修改的支持不一样生产环境最好在配置文件里固定下来。[mysqld] ngram_token_size2选2还是选1要看业务搜索词比较短比如城市名、人名可以设成1但索引体积会变大默认2在大多数中文场景下是平衡点。对应到搜索行为如果你想查“张”、“王”这种单字默认的token_size2是命中不了的需要配合前缀词搜索或用LIKE兜底。另一个常被忽略的点全文索引不支持LIKE %xxx%语法你只能通过MATCH AGAINST来使用它。如果你的业务代码里全是LIKE拼字符串改造起来会有一些工作量需要把SQL改成MATCH AGAINST的写法同时注意关键字过滤、停止词等细节。3.4 全文索引的适用场景与性能指标实测下来几十万级别的文本表全文索引的查询响应时间比LIKE %词%能快一到两个数量级。核心原因就是它从“一个词一个词地扫”变成了“直接查倒排表”。不过它也有明显的限制和成本全文索引的维护代价高写入时需要对文本分词并建立/更新倒排表插入和更新速度会变慢。对content这种大文本字段全文索引占用的磁盘空间可能比数据本身还大。并不是所有MySQL版本/存储引擎都支持。老版本MyISAM有全文索引但InnoDB是从5.6开始支持且部分细节不断变化生产环境建议先验证版本。所以全文索引适合“对中长文本做包含检索”的场景比如文章搜索、消息记录搜索、商品描述搜索。如果你只是在一张几万行的配置表里查名字全文索引性价比不高因为建索引比扫描全表还慢。4. 方案三生成列 反向存储索引字面意义上的“两边都能加%”4.1 思维转换把后缀匹配改成前缀匹配刚才说过LIKE 关键字%能走索引LIKE %关键字走不了。那能不能把数据倒过来存把后缀匹配变成前缀匹配比如我想找“名字以’三‘结尾”的人如果存了一列“名字的反转”那REVERSE(name)就变成了以’三‘开头查询时用LIKE 三%就能走索引。MySQL 8.0支持生成列Generated Column我们可以让数据库自动维护一个反向存储的字段4.2 建表和查询实战CREATE TABLE t_user_rev ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, name_rev VARCHAR(50) GENERATED ALWAYS AS (REVERSE(name)) STORED, PRIMARY KEY (id), KEY idx_name_rev (name_rev) ) ENGINEInnoDB;插入数据时完全不用管name_rev数据库会自动把name的反转串写进去INSERT INTO t_user_rev (name) VALUES (张三), (李四), (王三);然后查询“名字以‘三’结尾”的行EXPLAIN SELECT * FROM t_user_rev WHERE name_rev LIKE 三%;这条的效果等价于原来的WHERE name LIKE %三但执行计划里type是rangekey是idx_name_rev走了索引。4.3 那“两边都加%”怎么用这个方案看到这里聪明的读者应该反应过来了把 “%关键词%” 拆成“前缀”或“后缀”的组合再分别用生成列去优化。比如要查name LIKE %三%它的语义等价于name LIKE 三%或name LIKE %三中任意一种能覆盖关键字出现在中间的情况比如名字中是否包含“三”可以查name LIKE %三对应name_rev LIKE 三%还可以配合name LIKE 三%把开头命中的也查出来两者取并集就能覆盖所有“包含”场景而且两者都能走索引一个走idx_name一个走idx_name_rev。如果你需要更复杂的多字关键词可以组合多个条件比如name LIKE %三%变成SELECT * FROM t_user_rev WHERE name LIKE 三% OR name_rev LIKE 三%;虽然这里用了两个条件做并集但每个分支都可以走索引整体比全表扫强得多。这里有个很重要的细节生成列必须是STORED不能用VIRTUAL。因为VIRTUAL生成列的值不落盘InnoDB在它上面建二级索引时本质上还需要计算实际执行计划很可能仍然不能利用传统的范围扫描。而STORED会把值真正写到磁盘上面建的idx_name_rev才是一个普通的B树索引LIKE 三%才能走范围扫描。4.4 生成列方案的优点、坑与使用边界这个方案最爽的地方在于它对业务代码侵入性极小。你还是用LIKE只是把这个“反转查询”封装到SQL里不用改成MATCH AGAINST那套复杂的语法。但需要注意几个坑生成列表达式必须是“确定性”的REVERSE(name)没问题但如果你函数里用了随机数、当前时间之类建列会直接报错。name_rev字段会占用额外存储空间如果名字是VARCHAR(50)反向列也得50个字符等于每行数据多了一倍的存储成本。多关键词的“包含”查询比如LIKE %三%丰%生成列方案就不好使了。这种更推荐全文索引或者下文提到的搜索引擎方案。从实际项目来说这个方案最适合“后缀/中缀匹配基数比较低的业务字段”比如车牌号尾号、订单号末尾几位、用户姓名/昵称等。有一个很典型的场景是查“尾号2388的订单”直接用order_no LIKE %2388全表扫建一个反向生成的索引列后变成order_no_rev LIKE 8832%查询性能直线上升。5. 方案四数据外移ES等搜索引擎救场5.1 什么时候该跳出MySQL的圈子如果你的业务已经发展到“搜索是核心功能”的程度比如电商后台的商品搜索、社区的内容检索那不应该再纠结“MySQL怎么优化模糊查询”。MySQL的索引设计、全文检索都不是为这种重度检索场景准备的。比较常见的做法是引入Elasticsearch。ES底层用的是倒排索引天生就是做“包含/模糊/分词查询”的。把MySQL中的数据同步到ES用ES做检索拿到ID列表后再回到MySQL取明细这套“读写分离”的架构在业界非常成熟。5.2 ES方案的核心思路与同步问题比如商品表id, name, category, description同步到ES后可以这么查{ query: { match: { name: 智能手表 } } }ES内部会把“智能手表”切词为“智能”、“手表”、“智能手表”再通过倒排索引快速定位文档。匹配速度和结果排序都远胜MySQL。同步方式有三种常见选择业务双写写MySQL同时写ES。实现简单但需要处理一致性问题。Binlog监听同步通过Canal等组件订阅MySQL binlog增量同步到ES。异步存在秒级延迟但对业务代码无侵入。定时全量重建适合数据量不大、变更不频繁的场景比如几千条配置的字典表直接每天凌晨重建一次ES索引即可。这里就不过多展开ES的部署和调优了。作为一篇文章我想强调的是技术选型本质上是在“实现成本”和“查询体验”之间做权衡。如果你只是几个字段的模糊搜索MySQL的索引技巧足够用如果搜索是核心功能ES或专门搜索服务该上就上。6. 常见问题与“避坑”经验6.1 索引建了EXPLAIN却还是全表扫描这种情况很常见。除了%在左边的问题还有一个容易被忽略的点字符集不一致导致索引失效。比如表字段是utf8mb4但查询条件传参的字符集却是utf8MySQL会对列传参做隐式转换一转换就不能用索引了。排查方法很简单SHOW FULL COLUMNS FROM t_user; SHOW VARIABLES LIKE character_set_connection;确保连接字符集和表字段字符集一致通常统一用utf8mb4即可。另外如果你在字段上做了函数运算比如WHERE LOWER(name) LIKE %张%索引照样失效。解决办法是把函数放到等号右边或者干脆再加一个“小写冗余列”并建索引。6.2 全文索引创建失败或查不出数据最典型的问题就是没有指定WITH PARSER ngramMySQL默认对中文分词失效全文索引里根本没被正确索引查什么都匹配不上。还有停止词stopword问题某些常见的字词如“的”、“了”、“是”默认不会被索引如果你搜索全是这类词的组合可能就是查不到。另外全文索引的字段类型必须是CHAR、VARCHAR或TEXT你要是往BLOB字段上建是建不了的。如果MySQL版本低于5.7.6InnoDB还不支持全文索引要考虑使用其他方案。6.3 生成列索引没生效有读者可能会遇到这种情况建了生成列和索引但WHERE name_rev LIKE 三%的type还是ALL。大概率是建表时用了VIRTUAL而不是STORED或者给生成列建的索引类型选错了比如建成了普通二级索引但查询时对生成列做了类型转换。再造一个测试表验证一下即可重点是确认STORED关键字没有丢。6.4 常见问题速查表现象主要原因解决方案LIKE %xx%全表扫%在左侧B树无法定位起始位置覆盖索引/全文索引/生成列反向索引字段建了索引但查询不走字符集不一致或字段上有函数运算统一字符集避免对字段使用函数全文索引查询无结果未用ngram解析器或停止词过滤建索引时加WITH PARSER ngram检查分词参数生成列索引不生效使用VIRTUAL生成列改成STORED让值真实落盘数据量大时LIKE仍然很慢索引覆盖不足或结果集过大使用延迟关联缩小回表范围必要时引入ES6.5 一个真实的性能对比案例我在一个订单系统里做过对比测试订单表约150万行。以“收货人姓名包含‘王’”为例方案查询耗时约备注直接LIKE %王%1.8s全表扫覆盖索引(name, phone)380ms全索引扫省回表延迟关联 覆盖索引120ms小结果集场景提速明显生成列反转 name_rev LIKE 王%80ms只覆盖“名字以王结尾”场景加粗提示一下这些数字只是单次测试参考不代表所有环境都一致。具体能优化到多少取决于表结构、硬件、数据分布和查询命中率。但趋势很清楚只要别让MySQL老老实实全表扫选择合适的手段性能都会有可观提升。7. 如何根据业务场景选择最合适的方案7.1 选型对照表业务场景推荐方案原因列表页按名称“包含”筛选查询字段固定覆盖索引/延迟关联改动小、性能收益高文章、消息、日志等长文本“包含”搜索全文索引 ngram原生支持分词和相关性排序车牌号、订单号等尾部精确匹配生成列反向索引把后缀匹配转化为前缀匹配走索引搜索是核心功能需要分词/权重/聚合引入ES或专业搜索引擎MySQL索引模型不适合重度检索数据量小几万行内直接LIKE即可优化优先级低不要过度设计7.2 学完这招之后还能怎么用其实这套思路完全可以延伸到其他场景。比如“手机号脱敏查询”你只知道末尾4位正常是phone LIKE %8888全表扫建一个phone_rev生成列瞬间变成前缀匹配。再比如用户昵称、邮箱前缀匹配都可以用同一个套路。核心思想一句话让查询条件尽可能利用B树的有序性把不能定位的“包含”问题改造成能定位的“前缀/后缀”问题。7.3 最后再分享一个小技巧如果你在MySQL 8.0上工作还可以把生成列和函数索引结合直接对表达式建索引比如CREATE INDEX idx_name_lower ON t_user ((LOWER(name)));这样即使业务里写了WHERE LOWER(name) zhang也能用上索引。这类“索引设计”的细节在面试和实际调优里都是加分项。我个人在实际项目里最常用的套路其实是“覆盖索引 延迟关联”的组合因为它对现有代码的改动最小收益又很稳定。只有在字段特别多、模糊匹配频率非常高时我才会考虑加全文索引或上ES。评估的时候一定要先看命中集大小和数据量级不要一上来就堆方案不然优化了个寂寞还白白多了存储和运维成本。踩过几次坑之后我现在最想提醒后人的是模糊查询的优化从来不是单纯写一条SQL就能解决的而是在建表、建索引、写查询这三层上一起配合。你只能在建表时想好哪些字段需要被搜索在建索引时设计好索引覆盖和冗余列在查询时选择合适的写法这套流程走通了MySQL的模糊查询一点都不“废”。