企业官网建设流程全解析

讲到MySQL性能优化，LIKE索引失效应该说是最高频的面试题之一了。我自己做后端开发这些年，面试过不少候选人，也反复被人问过这道题。时间久了慢慢发现，这个看似简单的问题，其实藏着面试官好几层考察意图：候选人是不是只会背"以%开头的LIKE不走索引"这条结论？有没有真正理解B+树的存储结构？在线上遇到过类似慢查询时，会不会做完整的定位和修复？

这篇文章不是随手写的面经，而是把原理层、实操层和面试问答层的经验放在一起做一次完整梳理。不管你是准备面试的候选人，还是正在排查线上慢SQL的开发，里面涉及的执行计划分析、索引结构说明、覆盖索引和反转列这些玩法，都能直接用在项目里。标题既然写了"持续更新"，我后面也会把新遇到的真实案例和优化手段陆续补进来。

1. 面试官抛出这道题，真正想摸底的是这四件事

先别急着背答案。很多候选人一听到"LIKE索引失效"，张口就是"以%开头的LIKE用不了索引"，然后眼巴巴等着面试官点头。但说实话，这个回答只能拿到及格边缘的分数，因为它只回答了"是什么"，完全没有触及"为什么"。

我一般会把这个问题拆成四个层次去听候选人的回答：

• 第一个层次，知不知道结论。就是LIKE 'abc%'能用索引，LIKE '%abc'和LIKE '%abc%'通常不能用索引。这个层次是最基本的，背过面试题的人都知道。

• 第二个层次，能不能解释清楚原因。为什么前缀匹配能用，后缀匹配就不能？这里需要引出B+树索引的有序性。叶子节点上的数据按索引列的值从左到右排好序，字符串的比较是一个字符一个字符从左往右比。因此name LIKE '张%'本质上是在索引上查一个区间——从'张'开头的最小字符串到'张'开头的最大字符串，这个区间里的数据在索引页上是物理连续的，MySQL可以快速定位到区间的起点，再顺序扫描。而后缀匹配%张'做不到这点，因为所有以'张'结尾的字符串，在按完整字符串排序的B+树里是东一个西一个的，优化器根本不知道从哪里开始扫。

• 第三个层次，懂不懂EXPLAIN。真正处理过线上问题的人，会习惯性地用EXPLAIN去验证结论。他能说出type=ALL、key=NULL、rows=全表行数这些关键信号，能区分range范围扫描和index索引全扫描之间的区别。这些东西不是靠背，而是靠反复实操喂出来的。

• 第四个层次，有没有解决方案。注意，问"LIKE索引失效怎么办"，考察的重点不是听你发牢骚，而是看你能不能拿出至少一套可以在生产环境落地的方案。比如覆盖索引、反转存储、生成列、全文索引，这些方案各自适用什么场景、有什么代价，要能掰扯清楚。

所以，面试官抛出这道题，看起来只是问一个零碎的知识点，实际上是在摸你的底。合格的回答应该组织成一条从"结论"到"原理"到"验证"再到"修复方案"的完整链路，这才是一个从业者解决问题时的真实思路。下面几个小节，我就按这条链路逐个展开。

2. B+树索引的有序性，决定了LIKE的两种截然不同的命运

2.1 索引叶子节点在物理层面的排列方式

要理解LIKE，先得把B+树索引的存储形态在脑子里搭起来。

InnoDB的索引是B+树结构，主键索引的叶子节点存放整行数据，二级索引的叶子节点存放索引列的值加主键值。每个叶子节点是一个数据页，页内部的数据按索引列排序，页与页之间通过双向链表连接。也就是说，无论你从根节点走到哪个叶子页，只要你愿意，你可以顺着链表把整个索引扫一遍，而且扫出来的结果是严格有序的。

这个"有序"是理解一切问题的钥匙。拿用户表举例，name字段建了二级索引，那么索引中的数据大致是这个样子：

Alice -> pk=103 Bob -> pk=207 Charlie -> pk=502 David -> pk=301 Eve -> pk=178 Frank -> pk=406

所有名字按字典序排列。当你想查所有name LIKE 'C%'的记录时，你其实只是在找Charlie这个区间起点，然后一直往后扫到C开头的名字结束。因为索引有序，这个区间在物理上也是连续的，MySQL定位起来非常快。

2.2 前缀匹配为什么本质上是范围查询

name LIKE 'C%'在MySQL优化器眼里，其实等价于下面这个范围条件：

name >= 'C' AND name < 'D'

这就是一个标准的范围查询（range）。B+树最擅长干的就是范围查询：在根节点二分查找找到'C'这个下界，沿着索引向下定位到第一条记录，然后顺着叶子节点的链表往下扫，直到碰到'D'这个上界为止。整个过程只需要读少数几个索引页，性能相当好。

前缀字符越多，这个范围就越窄。name LIKE 'Ca%'对应的范围是['Ca', 'Cb')，扫描的索引页数量比'C%'少得多。这也解释了一个容易被忽略的问题：那些笼统说"LIKE百分百不走索引"的人，其实根本没搞清楚前缀匹配和范围查询的关系。

2.3 后缀和包含匹配失效，根因是目标记录在索引中不连续

现在反过来说LIKE '%e'。还是看上面那份按名字排好序的索引数据，以'e'结尾的名字有Alice和Eve。这两个名字在索引里一个在最前面，一个在最后面，中间隔着Bob、Charlie、David等一大片不满足条件的记录。

这就有点尴尬了。B+树的有序性只能保证"前缀相同"的数据聚在一起，无法保证"后缀相同"的数据有任何聚集效应。优化器没有办法确定一个起始位置，干脆放弃索引，老老实实全表扫描，把每一行的name字段取出来，用字符串匹配判断是否以'e'结尾。

LIKE '%e%'的道理也一样，这次连开头和结尾都不确定，目标记录在整个索引里分布得更加零散。全表扫描加逐行判断，这是最坏的情况。

顺便说一句，有些资料会说%abc%在某些条件下也能"用上索引"，在后面第3节会专门验证。但即便用上了，那种用法和高效的范围查询也不是一回事，面试时能讲清这个区别，印象分直接上一个台阶。

3. 六种LIKE写法逐一实测，别再凭感觉背结论

3.1 建表造数，准备一个可控的测试环境

我知道很多面试解析文章喜欢直接给结论，但我更建议你把这几种写法亲手跑一遍。在自己环境里验证过的东西，面试讲出来底气完全不一样。

先建一张简单的测试表：

CREATE TABLE `user` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, `age` INT DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

手工插入几条数据不痛不痒，实战中最好造个万级别数据量，EXPLAIN里的rows数字才有参考价值。可以用递归CTE批量造数：

INSERT INTO `user` (`name`, `age`) WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 20000 ) SELECT CONCAT(CHAR(65 + (n % 26)), '_suffix_', n), n % 80 FROM seq;

数据分布均匀，查询结果才具备可比性。造好数据后，开始逐个验证。

3.2 六种写法的执行计划对比

我把几种典型写法的EXPLAIN关键列整理到了一张表里：

前三种走的是索引的range扫描，因为前缀定下来了，优化器可以直接锁定一个索引范围，后面再怎么模糊都不影响范围定位。第四、第五种是典型的全表扫描，type=ALL且key=NULL，逐行匹配。

第六种是我做这道题时容易忘的一个点：LEFT(name, 1) = 'A'虽然逻辑上和LIKE 'A%'是一样的，但索引列被函数包了一层以后，原本有序的索引值在比较之前已经被改写了，B+树的有序性荡然无存，优化器只能放弃索引。面试时主动把这个例子抛出来，比单纯背"%开头失效"要立体得多。

3.3 一个容易被忽略的反例：覆盖索引下的%abc%

很多面试官喜欢在这里追问一句："那%abc%真的一点索引都用不上吗？"

准确的说法是：默认情况下用不上，但在覆盖索引场景下，它可能退而求其次走一次索引全扫描，而不是全表扫描。

看这个场景，把测试表改成联合索引：

CREATE TABLE `user` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, `age` INT DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name_age` (`name`, `age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行：

EXPLAIN SELECT name, age FROM user WHERE name LIKE '%A%'\G

你会发现type=index，key=idx_name_age，Extra=Using index。

这里type=index是索引全扫描，意思是优化器决定不碰聚簇索引里的完整行数据，而是把整个二级索引从头到尾扫一遍。二级索引比聚簇索引小很多，叶子节点只存name、age和主键id，即使全扫描，IO代价也比全表扫描低。Using index表示查询需要的所有列都在这个索引里，不需要回表。

但要注意，这不是"LIKE %abc%走索引了"，而是"覆盖索引让索引全扫描的代价低于全表扫描，优化器选择了扫描更小的索引"。面试时能说出这层区别，说明对优化器的成本估算逻辑有真实理解。

4. 一次后台搜索慢查询，完整的LIKE失效排查链路

4.1 慢查询告警后的第一反应

理论说再多，不如来一个真实场景。某次我在帮一个电商后台系统做性能排查，运营反馈说商品编码搜索页特别慢，输入关键词要等两三秒才出结果。这个功能用的是product_code字段做模糊搜索，商品编码是类似TK20241201A001这种格式的字符串。

第一件事不是改代码，而是先看慢查询日志和当前SQL的执行计划。把SQL捞出来以后，发现是一条很典型的写法：

SELECT id, product_code, product_name, price FROM product WHERE product_code LIKE CONCAT('%', 'TK2024', '%') ORDER BY id DESC LIMIT 20;

业务代码里其实是用%关键字%来匹配的，用户在搜索框里输入什么，就往SQL里拼什么。这种"只要用户输入的是完整编码片段就帮忙匹配"的需求，听起来很人性化，但对数据库来说就是最需要警惕的查询模式。

4.2 从EXPLAIN到索引设计的一步步核对

我直接在测试库上执行了EXPLAIN：

id: 1 select_type: SIMPLE table: product type: ALL key: NULL rows: 130000 Extra: Using where; Using filesort

type=ALL，key=NULL，rows=130000，全表13万行数据，每一行都得做一次字符串包含判断，最后还要filesort排序。执行计划一出来，慢的原因已经基本清楚了。

我接着查了表结构，product_code字段上其实已经有一个唯一索引。那为什么没用上？因为LIKE '%TK2024%'以%开头，索引的有序性帮不上忙，优化器在成本估算阶段就主动放弃了索引。即便商品编码的格式相当规则，理论上可以拆出前缀，但SQL里没有体现这一点，数据库不会自动帮你做这种"语义推断"。

后面我又确认了字符集和排序规则。表是utf8mb4_general_ci，大小写不敏感，这意味着即使改成LIKE 'TK2024%'，索引扫描时也要多做一层大小写匹配的处理。不过在这个case里，根因还是以%开头导致的索引失效，字符集只算次要因素。

4.3 确认根因后，和业务对齐真实查询模式

排查到这里，我已经能下结论：SQL写法导致索引失效，%关键字%让B+树无法定位起始位置。但这里还有一个容易被跳过的关键步骤——去找业务方确认，用户搜索商品编码时，到底是输入完整编码，还是输入一个模糊片段？

这个case的实际情况是，运营人员经常复制Excel里的完整编码过来查，但系统做了"模糊"处理，兼容了部分编码。也就是说，真实业务里大部分情况是完整编码精确匹配，只有少数情况需要模糊搜索。这就是修复方案的重要切入点。

如果这步没做直接硬改，很可能出现这种情况：你辛辛苦苦搞了一套全文索引，结果业务说我们大部分时候其实是精确查询，那不是典型的杀鸡用牛刀？

5. 不改SQL怎么救，改SQL怎么救，三个可落地方案

5.1 覆盖索引加索引条件下推，先榨干现有索引

在动手改表结构之前，先看一个性价比极高的操作：把SELECT的字段都塞进索引，让查询变成覆盖索引。

假设商品搜索功能必须支持LIKE '%keyword%'，但查询只需要返回product_code和id，那我可以建一个联合索引：

ALTER TABLE product ADD KEY idx_code_status (product_code, status);

当SQL写成：

SELECT id, product_code FROM product WHERE product_code LIKE '%TK2024%' AND status = 1;

在MySQL 5.6以上，优化器可能会走idx_code_status的索引全扫描，Extra里出现Using where; Using index，每一行判断都在索引层面完成，不回表。这个优化虽然仍然是全扫描，但因为扫描的是比聚簇索引小得多的二级索引，IO开销会明显下降。

索引条件下推（ICP）在这里也扮演了重要角色。MySQL 5.6引入的ICP优化，允许在索引遍历过程中，把索引包含列上可判断的条件提前过滤。比如联合索引(product_code, status)，在扫描索引的时候，status=1这个条件就能直接在索引层面过滤掉，进一步减少需要回表的行数。这个机制在LIKE场景里特别有用，后面第6节再细讲。

5.2 反转存储配合生成列，专门对付后缀匹配

如果业务模式是"查某个后缀的编码"，比如运营经常搜LIKE '%A001'，这时候可以直接把字段反转过来存。

MySQL 5.7开始支持生成列（GENERATED COLUMN），你可以建一个自动反转的字段，并在这上面建索引：

ALTER TABLE product ADD COLUMN product_code_rev VARCHAR(50) GENERATED ALWAYS AS (REVERSE(product_code)) STORED; ALTER TABLE product ADD KEY idx_code_rev (product_code_rev);

查询时的写法变成：

SELECT * FROM product WHERE product_code_rev LIKE REVERSE('%A001') || '%';

REVERSE('%A001')计算结果是'100A%'，拼接%号以后实际是把反转后的值做前缀匹配，索引又能正常走range扫描了。

这里有个很重要的细节：REVERSE函数作用的对象是字符串常量，不是索引列，所以索引不会被函数摧毁。如果写成REVERSE(product_code) LIKE '%A001%'，那就又把索引列包进函数里，方案彻底失去意义。

反转列方案需要占用额外的存储空间，生成列如果是STORED的还会在写入时多一次计算，但对查询性能的改善是实打实的。线上如果真有高频后缀搜索场景，这个方案比全文索引更可控。

5.3 全文索引与ngram，处理中文包含匹配的最终手段

如果是真正的中文全文检索需求，比如商品名称搜索、文章内容搜索这种，前面那些招数就都不够用了。你需要的是全文索引（FULLTEXT）。

MySQL 5.7.6之后内置了ngram全文解析器，按字节对中文切词，你可以建一个中文全文索引：

ALTER TABLE article ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;

查询用MATCH ... AGAINST：

SELECT * FROM article WHERE MATCH(title) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

全文索引的匹配逻辑和LIKE不一样，它把文本切成词元建立倒排索引，天然适合"包含某词"的检索场景。但它也有明显代价：索引占用空间大，写入变慢，而且ngram的分词质量会直接影响搜索结果。对那种核心业务必须用全文搜索的场景，我的建议是别硬扛，直接上专门的检索引擎，数据库里只存原始数据。

回到面试场景，只要能讲清楚覆盖索引、反转列、全文索引这三条路的适用场景和代价，这道题的方案部分基本就过关了。

6. 面试追问环节：索引条件下推与字符集排序规则

6.1 ICP如何让%abc%在联合索引中起死回生

面试官问到这，通常还会抛出下一句："那索引条件下推是怎么回事？"这个话题和LIKE索引失效是深度绑定的。

我用一个具体例子来讲。假设有一张订单表，联合索引是(customer_name, order_date)，SQL是这样的：

SELECT * FROM orders WHERE customer_name LIKE '张%' AND order_date >= '2024-01-01';

在没有ICP的MySQL 5.6之前，执行流程是这样的：先从索引里找到所有customer_name满足张%的主键，回表把完整行捞出来，再用order_date条件过滤。如果名字是'张'开头的人非常多，回表次数就很可观。

开启ICP之后，MySQL在扫描二级索引的过程中，发现order_date也是索引的一部分，于是边走索引边判断order_date >= '2024-01-01'，只对同时满足两个条件的记录回表。回表次数大幅降低，这是实打实的性能提升。

放到LIKE场景里，Using index condition这个Extra标记就是ICP生效的直接证据。比如WHERE name LIKE '张%' AND age > 20，在联合索引(name, age)上，age条件可以在索引遍历时提前过滤，减少回表。面试时提到你观察过这个Extra标记，比干背概念有说服力得多。

6.2 大小写敏感排序规则对LIKE前缀匹配的影响

还有一个容易被忽略但面试官很爱深挖的点：排序规则对LIKE的索引使用有影响。

MySQL里常见的排序规则有utf8mb4_general_ci和utf8mb4_bin，_ci结尾的是大小写不敏感，_bin是按二进制比较。

如果在大小写不敏感的排序规则下执行name LIKE 'abc%'，结果会同时匹配abc和ABC。数据库为了做到这一点，索引节点的比较逻辑会更复杂，执行计划上可能出现Using index condition，索引扫描的范围边界判断也比二进制的宽松。

举一个直观的例子，utf8mb4_bin下LIKE 'A%'的范围是['A', 'B')，非常干净。而在_ci下，'a'和'A'在比较时被认为是同一个字符，'A%'实际要跨越的大小写组合边界更多。虽然通常不至于让索引失效，但在性能和排查时都要把它当作一个变量因素来考虑。

这个点为什么加分？因为它证明你不只是在背书，而是真正看过多字节字符在索引里的比较规则，知道字符集和排序规则能影响执行计划。我面过一些候选人，能把话题聊到这一层的，几乎都是真的被线上问题逼着研究过的人。

6.3 一个可以直接套用的回答框架

最后把这道题的完整回答框架整理一下，准备面试的同学可以按这个思路组织语言：

1. 先给核心结论：LIKE 'xxx%'能用索引，LIKE '%xxx'和LIKE '%xxx%'通常会失效。
2. 再说原理：B+树索引叶子节点有序，前缀匹配等价于范围查询，后缀匹配没有聚集位置，只能全表扫描。
3. 用EXPLAIN佐证：对比type=range和type=ALL，注意区分key=NULL和key=idx_name。
4. 补充边界情况：覆盖索引下%xxx%可能走type=index的索引全扫描，比全表扫描好，但和range不是一回事。
5. 抛出方案：覆盖索引加ICP、反转列加生成列、全文索引，并说明各自的代价。
6. 进阶补充：字符集排序规则的影响、函数包裹索引列带来的失效问题。

按这个框架答下来，至少能把这道经典题答出自己的深度。我个人在面试中更看重的，其实是候选人有没有在真实环境里被这种问题折磨过，并且形成了一套可复用的解决链路。把这些经验沉淀下来，不管是面试还是排障，都会越用越顺手。