企业官网建设流程全解析

作为一个天天跟慢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`) ) ENGINE=InnoDB;

分别执行：

EXPLAIN SELECT * FROM t_user WHERE name LIKE '张%'; EXPLAIN SELECT * FROM t_user WHERE name LIKE '%张%';

第一条的结果里，type是range，key是idx_name，走索引。第二条的结果里，type变成了ALL，key为 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 = index，Extra = 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 index

type从ALL变成index，Extra出现Using index。虽然type=index仍不算高效，但至少是顺序扫描整个索引而非全表，而且不需要回表，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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建全文索引：

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_size=2

选2还是选1要看业务：搜索词比较短（比如城市名、人名）可以设成1，但索引体积会变大；默认2在大多数中文场景下是平衡点。对应到搜索行为，如果你想查“张”、“王”这种单字，默认的token_size=2是命中不了的，需要配合前缀词搜索或用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`) ) ENGINE=InnoDB;

插入数据时完全不用管name_rev，数据库会自动把name的反转串写进去：

INSERT INTO t_user_rev (`name`) VALUES ('张三'), ('李四'), ('王三');

然后查询“名字以‘三’结尾”的行：

EXPLAIN SELECT * FROM t_user_rev WHERE name_rev LIKE '三%';

这条的效果等价于原来的WHERE name LIKE '%三'，但执行计划里type是range，key是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，但查询条件传参的字符集却是utf8，MySQL会对列传参做隐式转换，一转换就不能用索引了。

排查方法很简单：

SHOW FULL COLUMNS FROM t_user; SHOW VARIABLES LIKE 'character_set_connection';

确保连接字符集和表字段字符集一致，通常统一用utf8mb4即可。另外，如果你在字段上做了函数运算，比如WHERE LOWER(name) LIKE '%张%'，索引照样失效。解决办法是把函数放到等号右边，或者干脆再加一个“小写冗余列”并建索引。

6.2 全文索引创建失败或查不出数据？

最典型的问题就是没有指定WITH PARSER ngram，MySQL默认对中文分词失效，全文索引里根本没被正确索引，查什么都匹配不上。还有停止词（stopword）问题：某些常见的字词（如“的”、“了”、“是”）默认不会被索引，如果你搜索全是这类词的组合，可能就是查不到。

另外，全文索引的字段类型必须是CHAR、VARCHAR或TEXT，你要是往BLOB字段上建是建不了的。如果MySQL版本低于5.7.6，InnoDB还不支持全文索引，要考虑使用其他方案。

6.3 生成列索引没生效？

有读者可能会遇到这种情况：建了生成列和索引，但WHERE name_rev LIKE '三%'的type还是ALL。大概率是建表时用了VIRTUAL而不是STORED，或者给生成列建的索引类型选错了，比如建成了普通二级索引但查询时对生成列做了类型转换。再造一个测试表验证一下即可，重点是确认STORED关键字没有丢。

6.4 常见问题速查表

6.5 一个真实的性能对比案例

我在一个订单系统里做过对比测试，订单表约150万行。以“收货人姓名包含‘王’”为例：

加粗提示一下：这些数字只是单次测试参考，不代表所有环境都一致。具体能优化到多少，取决于表结构、硬件、数据分布和查询命中率。但趋势很清楚：只要别让MySQL老老实实全表扫，选择合适的手段，性能都会有可观提升。

7. 如何根据业务场景选择最合适的方案？

7.1 选型对照表

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的模糊查询一点都不“废”。