企业官网建设流程全解析

朋友A同学接手了一个内部运营系统，里面有个"模糊搜索"功能，某天慢查询日志突然堆满了 SELECT，每一条扫描行数都接近百万级。检查之后发现，搜索框把所有关键词都拼成了LIKE '%关键词%'的形式，而且业务方为了"防止用户记不全"，还在前端默认让用户输入的内容前后都带上了%。换成LIKE '关键词%'的写法之后，执行计划从type=ALL变成了type=range，查询耗时从几秒掉到几十毫秒。这就是标题里说的"MySQL 中 LIKE 以 '%' 开头导致索引失效"的典型现场。

这篇文章我会把问题讲透三件事：为什么%放开头会让 B+ 树索引用不上、用EXPLAIN怎么验证、以及真实业务里遇到"非要做前缀/后缀模糊匹配"时有哪些可落地的替代思路。适合正在排查慢查询的同学，也适合刚接触索引原理、想搞清楚"索引失效"本质的读者。

1. 一次搜索功能改造引发的"索引失效"现场

先说回到 A 同学那个系统。表面症状其实是经典的"加了索引还是很慢"：user_info表上明明有name字段的单列索引idx_name，但EXPLAIN出来type=ALL、key=NULL，MySQL 直接选择了全表扫描。

当时最关键的一步是还原线上 SQL 的完整形态。因为如果只看到WHERE name LIKE '%张%'，很容易把问题简单归因成"LIKE 带 % 导致索引失效"。但真正要命的还有一种组合情况——查询里还有其他等值条件，比如WHERE status = 1 AND name LIKE '%张%'。这种情况下status = 1理论上能先用联合索引定界，但如果索引顺序建错了，或者LIKE条件在整个 WHERE 里参与过滤的比例太大，优化器照样算出来全表扫更便宜。

所以排查带有模糊匹配的慢查询，第一步不是改 SQL，而是把完整的 SQL、表结构、索引清单、数据量全部列出来。我见过太多人一上来就把LIKE '%x%'改成LIKE 'x%'，结果发现外层还套着一个函数、一个OR、或者一个隐式类型转换，改了也白改。

A 同学那边最终确认了问题就是纯粹的LIKE '%关键词%'。把它改成前缀匹配后，优化器立刻走了idx_name的 range 扫描。这里有一个很重要的认知：MySQL 不是"闻 % 色变"，而是"闻前缀 % 色变"。LIKE 'abc%'完全可以用索引做范围扫描，LIKE '%abc'和LIKE '%abc%'才真正让 B+ 树失去快速定位的能力。要理解这一点，得回到索引数据结构本身。

2. B+ 树的有序性被 % 前缀破坏：从二分查找说起

很多开发同学背结论背得很熟：LIKE '%abc'不走索引。但你要是问他为什么，答案往往很模糊。我习惯用一个"英文字典"的类比来解释。

你手里有一本按字母顺序排好的英文字典，想查一个单词，第一步一定是确定它大概在哪个区间。比如查apple，你知道它排在a开头那一段，直接翻到字典前部。但如果有人只告诉你"我想找一个以tion结尾的单词"，你就没办法直接翻到某一页，因为任何一页都可能出现以tion结尾的词，你唯一靠谱的做法是从第一页翻到最后一页。MySQL 的 B+ 树索引就是这本字典，叶子节点按索引键值从小到大有序排列，查询依赖的也是"先定位到有序序列的某个边界，再向左或向右扫描"。

LIKE 'abc%'为什么能用索引？因为优化器能把这个条件改写成等价的区间扫描：

-- 实际上优化器会把它转换成一个范围条件 name >= 'abc' AND name < 'abd'

下限abc、上限abd都是确定的。B+ 树可以直接二分定位到第一个abc的叶子节点，然后往后顺序扫，直到遇到abd为止。这正是type=range的含义。

但LIKE '%abc'没有这个待遇。它想表达的是"所有以 abc 结尾的字符串"，可 B+ 树只按字符串的从左到右顺序排序。对于"以 abc 结尾"这个条件，你既找不到一个下限，也找不到一个上限，因为任何字符串都可能以abc结尾。优化器只能把整棵索引树的叶子节点从头到尾翻一遍，每一条记录都拉出来判断一下结尾是不是abc。这个动作的成本往往比直接全表扫描还高，于是干脆key=NULL、type=ALL。

LIKE '%abc%'就更复杂了，它想匹配的是"任意位置包含 abc"，本质上是一个子串搜索，跟 B+ 树的有序性完全不沾边。

还有一个概念值得提：sargable（可搜索）谓词。一个谓词如果能被优化器转换为索引上的范围比较，就是 sargable；如果不行，就是 non-sargable。LIKE 'abc%'是 sargable，因为它可以映射成区间比较；LIKE '%abc'是 non-sargable，因为它无法映射成任何区间。理解这个词，你就能举一反三——WHERE YEAR(create_time) = 2024也是对create_time列套函数，破坏了有序性，同样属于 non-sargable。

再补充一点容易混淆的地方：MySQL 8.0 支持的倒序索引（descending index），很多人以为能用它解决LIKE '%abc'这类后缀匹配问题。实际不行。倒序索引解决的是"升序索引无法高效处理ORDER BY col DESC"的排序问题，它仍然是按一个固定方向维护有序性的，对"以 abc 结尾"这种在字符串内部做模糊匹配的需求没有帮助。这个点经常被误解，我这里先摆出来，后面第 5 节再讲真正能落地的反向匹配方案。

3. 用 EXPLAIN 做证据链：三种写法实测记录

原理讲了半天，不如直接上EXPLAIN看证据。下面的测试环境是我本地搭的，表结构和数据量故意做得接近真实业务：

CREATE TABLE user_info ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, email VARCHAR(128) NOT NULL, status TINYINT NOT NULL DEFAULT 1, create_time DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入了大约 100 万行模拟数据，name字段的取值分布参照常见中文姓名场景，张、李、王等姓氏占了相当比例。需要注意的是，我并没有用FORCE INDEX去干扰优化器，所有结果都是默认成本模型算出来的。

先看第一类写法，前缀匹配：

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

type=range，rows约 20 万。这 20 万的含义是：MySQL 在索引树上确定了name >= '张' AND name < '张\x00'? 严格说是'张'到'张'的下一个字符排序边界之间的区间。后面 20 多万行需要回表取完整记录，但好在回表次数和匹配行数一致，不存在额外的"无差别遍历"。

再看第二类写法，后缀匹配：

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

这就是标题说的"索引失效"。key=NULL，全表扫，rows直接就是全表行数。注意Extra里的Using where，说明 MySQL 是在聚簇索引扫描过程中逐行做LIKE判断的。

再看第三类写法：中间匹配，但查询列收窄到索引能覆盖的范围。

EXPLAIN SELECT id, name FROM user_info WHERE name LIKE '%张%';

这个结果很微妙。它用了索引idx_name，但type=index不是range，而是索引全扫描。也就是说，MySQL 决定把整棵辅助索引树的叶子节点从头扫到尾，但因为查询只需要id和name（辅助索引的叶子节点正好包含索引键 + 主键），不需要回表，所以这次"全索引扫描"的成本比"全聚簇索引扫描"低，优化器就选了它。

这三组对比能回答很多人的疑问：所谓%开头导致索引失效，不是说 MySQL 从此完全不碰这个索引了，而是它无法用索引做高效的区间定位。在某些"刚好覆盖查询列"的情况下，它仍然可能选择扫描整个索引——但你要清醒，type=index依然是全量遍历，数据量大到一定程度照样扛不住。

我还额外测了一下小数据量场景。把表清空只留 500 行，三条 SQL 的结果几乎全部变成type=ALL。因为表实在太小，顺序读聚簇索引的成本比"索引树遍历 + 回表"低太多，优化器直接选择最简单粗暴的方式。这说明"索引失效"并不是一个绝对开关，而是优化器基于成本模型的取舍结果。

4. 优化器这么做到底合不合理：回表成本与选择率

你可能想问：既然LIKE '%张'不能做区间定位，那 MySQL 为什么不用idx_name做一次"索引全扫描 + 逐行过滤"，再回表取数据呢？答案藏在回表成本和选择率两个词里。

idx_name是二级索引，它的叶子节点只存name和主键id。SELECT *需要返回所有字段，所以每次在二级索引里找到一个满足条件的记录，都必须根据主键回到聚簇索引去取完整行。这个回表动作大概率是随机 IO：第一次可能命中第 1000 页，第二次跳到第 5 页，第三次又跳到第 52000 页，磁盘的寻道成本远高于顺序扫描。

如果LIKE '%张%'的过滤率很高，比如只有千分之几的记录满足条件，那回表次数不多，走索引全扫描还能接受。但实际业务里，一个字符如果出现在姓名中间，命中率通常不会低到离谱，像'%张%'这种能命中几万到几十万行的条件，回表成本就完全失控了。优化器一算，发现"索引全扫描 + 大量回表"比"聚簇索引全表顺序扫"还慢，自然选择后者。

这里还牵扯到 MySQL 5.6 以后引入的索引条件下推（Index Condition Pushdown，ICP）。EXPLAIN里Extra显示Using index condition，就是 ICP 生效了。它的作用是：在二级索引扫描的过程中，存储引擎先把LIKE这类条件在索引行上过滤一遍，只有可能命中的记录才回表，而不是等回表之后再让 Server 层过滤。

打个比方，你在图书馆找一批以"通史"结尾的书。图书管理员把整个卡片柜翻了一遍，但他在翻卡片的同时就先把"结尾不是通史"的卡片扔到一边，只让你针对少数剩下的卡片去书架取书。这个动作确实能减少你跑书架的趟数。但请注意：管理员还是得把整个卡片柜翻完。ICP 减少的是回表次数，不能减少索引/数据遍历的量。所以LIKE '%张%'这种条件即使有 ICP，扫描行数依然是全表规模，慢还是慢。

选择率（selectivity）的影响也很大。假设name列 90% 的值都是"张三"，那么WHERE name LIKE '张三%'即使形式上是合法的前缀匹配，优化器也会评估出全表扫描更划算，因为匹配行太多了。这解释了为什么有些情况下你写LIKE '张三%'，EXPLAIN依然显示type=ALL。索引不是神，它只在"能显著缩小范围"的时候才划算。

5. 真遇到"前缀模糊查询"业务时怎么拆解

讲清楚原理，接下来是实操环节。如果业务确实需要"以某字符串开头/结尾/包含"，但表又大到不能随便全表扫，应该怎么办？我按成本从低到高讲。

5.1 覆盖索引：把查询列收窄到索引内部

回到上面的第三组测试。SELECT id, name FROM user_info WHERE name LIKE '%张%'能走index全索引扫描，靠的就是二级索引idx_name已经包含了name和主键id两个必要字段，不用回表。

所以如果你的列表页只展示"ID 和名字"，那么构建一个(name, id)的覆盖索引，让查询恰好只用索引列，是一个零改业务逻辑就能立刻见效的手段。要注意：type=index仍然是全量遍历索引树，它只是比全表扫描便宜一些，数据量继续增长时瓶颈依然存在。这个方案适合"中小表、模糊搜索低频、返回字段极少"的场景。

5.2 后缀匹配：用反转生成列把 %xxx 变成 xxx%

这是个很有意思的技巧，专治LIKE '%abc'。思路是：既然 B+ 树不能从右往左找，那我们就给字符串反向存一份，让"后缀"变成"前缀"。

MySQL 8.0 支持生成列和函数索引，可以这样建：

ALTER TABLE user_info ADD COLUMN rev_name VARCHAR(64) GENERATED ALWAYS AS (REVERSE(name)) STORED, ADD INDEX idx_rev_name (rev_name);

这里REVERSE(name)会把"abc"变成"cba"。原本等价于name LIKE '%abc'的查询，现在可以改写为：

SELECT * FROM user_info WHERE rev_name LIKE CONCAT(REVERSE('abc'), '%');

也就是rev_name LIKE 'cba%'，这是标准的前缀匹配，能走idx_rev_name做 range 扫描。

用索引前，有几个必须提醒的坑：

• 这个方案只解决"确定后缀、前缀未知"的查询。业务如果需求是名前缀包含xx，后缀包含yy，比如'%xyz%abc'，反转过来变成'cba%zyx%'，前缀通配符又回来了，优化器依然只能全扫。所以反转方案不要滥用。
• REVERSE()对字符集敏感，中文在utf8mb4下反转是按字符处理的，基本没问题。但生成的列参与排序时，排序规则仍然按rev_name的 collation 走，逻辑是一致的。
• 生成列有STORED和VIRTUAL两种，STORED会额外占用存储空间，VIRTUAL不占。MySQL 8.0 里虚拟生成列也可以建索引，STORED只是更保险一些，按自己磁盘情况选。

如果用的是 MySQL 8.0.13 以上，也可以直接建函数索引，写法更短：

CREATE INDEX idx_name_rev ON user_info ((REVERSE(name)));

查询时写WHERE REVERSE(name) LIKE 'cba%'，优化器才能匹配到该函数索引。函数索引本质上也是"预计算 + 存储键值"，原理和生成列一样。

5.3 真正的"任意位置子串匹配"：全文索引与分词器

如果需求是"文章正文里搜某个词"，LIKE '%关键词%'在数据量大之后基本无解，这时候应该考虑全文索引（FULLTEXT）。

对于中文内容，InnoDB 的全文索引默认支持英文分词，中文得用内置的ngramparser。建表时可以这样创建：

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body) WITH PARSER ngram;

查询语法也不再是LIKE，而是：

SELECT * FROM articles WHERE MATCH(title, body) AGAINST('数据库优化' IN BOOLEAN MODE);

ngram默认分词粒度是 2（bigram），也就是说一个两个字的词会被拆成多个连续的二字组合来匹配。这个方案对付"包含某词"的效果远好于LIKE '%词%'，因为全文索引有自己的倒排索引结构，查询时直接查词元，不需要全表遍历。

但要明白它的边界：全文索引匹配的是词/分词单元，不是任意连续子串。你想搜abcd然后希望匹配到xxabcdyy这种连续四字符的中间片段，全文索引做不到，LIKE '%abcd%'可以。这其实是两种不同的搜索语义，选择方案之前得先跟业务确认清楚。另外全文索引在高频写入场景下维护成本较高，写在文档/日志表上的效果比写在订单流水表上好得多。

5.4 什么时候该把搜索工作交给外部系统

最后一条路比较"重"，但当模糊搜索变成产品的核心功能时，绕不开。

判断标准很简单：如果业务里大量查询都是"任意位置子串匹配"、数据量突破几千万行、并发量又不低，那继续在 MySQL 里硬扛是不理智的。正确做法是把搜索能力从业务库里拆出来，交给专门的搜索引擎/搜索中间件。MySQL 继续负责事务性数据存储，搜索集群负责分词、倒排索引、相关性排序，两者通过同步任务保持数据一致。

很多团队一听到"引入搜索引擎"就头大，其实如果一个系统的模糊搜索只是运营后台偶尔用用，完全没必要动这个级别的手术。先用覆盖索引、反转生成列、全文索引这三板斧，能挡住 80% 以上的场景。等接口 QPS、查询响应时间、索引维护成本都压不住了，再谈升级架构。

6. 我的一些实操心得

排查这类问题多了，有几个习惯很值得保留。

第一，新 SQL 上线前养成跑EXPLAIN的习惯，重点看type和rows。type=ALL或者rows接近全表行数，直接拦住，别等线上慢查询日志来打脸。第二，看到LIKE '%x%'别急着改，先问业务：你是真的需要任意位置匹配，还是只是"用户可能记不全开头"？很多时候把交互改成前缀匹配，业务体验并没有变差，性能问题却当场消失。第三，永远不要指望FORCE INDEX来硬解%开头的 LIKE。强制执行idx_name后，MySQL 会老老实实扫完整个索引再逐条回表，我实测过很多次，比全表扫还要慢一截，属于"看着用了索引，实则更糟"的典型。

最终总结经验就是一句话：%放在开头破坏了 B+ 树作为有序结构最重要的"定界"能力，任何想让这类查询变快的思路，要么把比较方向改掉，要么换成另一种检索数据结构，跟"加不加索引"本身没有关系。希望这篇内容能帮你少走我当年走过的弯路。