企业官网建设流程全解析

搞后端开发的人，几乎都遇到过同一个诡异场景：SQL写得很直白，表上索引也建了，EXPLAIN一执行，possible_keys那栏明明白白写着索引名，结果key却是NULL，直接全表扫描。线上一条慢查询就能把接口拖到两三秒，数据库CPU飙上去，紧接着是一连串连锁反应。MySQL索引失效这个话题，既高频出现在面试题里，也是日常排查慢SQL躲不开的硬仗。这篇文章我把自己这几年踩过的索引失效坑整理了一遍，不单是罗列现象，还会把每条失效背后的原理讲清楚，配EXPLAIN实测步骤作为验证手段。看完之后，你至少能自己定位八成以上的慢查询问题。

1. 索引失效的本质：先搞清楚索引到底是怎么“工作”的

聊失效之前，得先明白索引为什么快。MySQL里InnoDB的索引底层是B+树，它做的事情就是让数据按照索引列的值排列成一种“有序结构”。拿生活中的例子类比，一本电话簿如果按姓氏拼音排好了，你想找“张”姓，直接翻到Z那一带就行；但如果这本电话簿是按电话号码排的，你想按姓氏找人就只能从头翻到尾。

B+树能加速查询，本质是它把“无序的查找”变成“有序的定位”，这个定位依赖三个前提：

• 条件列的值在索引里是排好序的，所以等值查询可以快速定位到叶子节点。
• 条件能直接和索引列进行比较，中间不能隔着任何加工。
• 查询逻辑是“定位到一小片范围”，而不是“排除绝大部分数据”。

索引失效，说白了就是查询计划没办法利用索引的有序性和可比较性，要么是索引树定位不了，要么是定位成本反而比全表扫描更高，优化器直接放弃。

这里要强调一个容易误解的点：索引失效不一定是“物理上不能走索引”，很多时候是“优化器算完账之后觉得不划算”。比如一张表只有一千行，你建了索引，优化器发现全表扫描也就读一千个数据页，比走索引回表还快，那它就选全表扫描。这种也算广义的失效，后面会单独展开。

理解了这层，我们再看那些具体的失效场景就顺理成章了——它们都是在破坏上面三个前提中的一个或几个。

2. 最常见的索引失效场景与原理拆解

2.1 隐式类型转换：列和条件的“性别”对不上

这是新手最容易踩的坑。表里某个字段是VARCHAR，查询时却传了数字，MySQL会隐式地把字符串列转成数字再比较。举个例子：

-- phone 是 VARCHAR(20)，查询条件却是数字 SELECT * FROM user WHERE phone = 13800138000;

看着没毛病，实际上MySQL内部的执行是CAST(phone AS SIGNED) = 13800138000。一旦对索引列做了CAST，索引列的有序排列就失效了——B+树里存的是字符串的顺序，而字符串转成数字后的顺序和字典序并不是一回事，优化器就没法用索引树去定位。

反过来也一样。如果列是INT，条件传了字符串，MySQL会尝试把字符串转数字，这倒不会让索引失效，因为转换发生在常量这一侧。但为了统一规范，开发规范里通常都要求“什么类型的字段就传什么类型的值”，避免任何一方出现隐式转换。

排查技巧：这类问题用EXPLAIN看type就能发现，正常应该是ref，失效后是ALL。修正方式是保证类型一致：

SELECT * FROM user WHERE phone = '13800138000';

2.2 对索引列使用函数或表达式：DATE()、SUBSTR()这些操作

我们经常会在查询条件里写WHERE DATE(create_time) = '2025-01-01'，这种写法非常顺手，但它会让create_time上的索引彻底失效。

原因是索引里存的是create_time的原始值，比如2025-01-01 08:30:00，而查询条件是DATE(create_time) = '2025-01-01'，MySQL需要对每一行的create_time做DATE()运算，再把结果跟常量比较。这个运算结果在B+树里不是连续排布的，所以没办法从根节点往下定位，只能扫描全表。

真正正确的写法是改成范围查询：

-- 把函数操作改成对常量的范围限定 SELECT * FROM user WHERE create_time >= '2025-01-01 00:00:00' AND create_time < '2025-01-02 00:00:00';

这样索引列保持“裸列”，B+树就能直接沿着范围定位，性能差别经常是几十倍。类似的还有WHERE YEAR(create_time) = 2025、WHERE SUBSTR(name, 1, 2) = '张'、WHERE LENGTH(name) > 5等等，只要索引列被函数包住，基本就是失效。

表达式也是同一个道理，比如WHERE id + 1 = 100，索引列参与了运算，优化器没法直接拿id去B+树里找，只能每行算一遍。改成WHERE id = 99就能走主键索引。

2.3 前缀模糊查询：LIKE '%xxx'为什么不行

LIKE 'xxx%'是可以走索引的，B+树的有序性支持“从左往右的匹配”。比如索引列存储了'apple'、'apply'、'banana'，你查LIKE 'app%'，B+树能找到'app'这个前缀的起始位置，然后往后扫一小段，这是典型的range扫描。

但LIKE '%apple'或者LIKE '%apple%'就完全不一样了。后缀匹配没办法确定起点，因为索引是按字符串完整顺序排的，你只知道结尾是什么，不知道开头在哪，B+树的优势完全用不上，只能全表扫描。

业务里确实有中缀模糊搜索的需求，我的建议是：

• 能改成前缀匹配就改，这是最简单有效的方案。
• 中缀匹配需求多的话，别死磕MySQL的LIKE，考虑全文索引或者专业的搜索引擎。
• 如果表数据量不大，硬扛全表扫描也能接受，但要心里有数，数据量上来之后扛不住。

在数据量小的阶段，很多人不把这个当回事，等表到百万行再改就很痛苦了。

2.4 联合索引跳过引导列：最左前缀原则

联合索引是最容易出问题的部分。建一个(a, b, c)的联合索引，B+树是先按a排，a相同再按b排，b相同再按c排。这就像查字典，先按拼音首字母，再按音节，最后按声调。你只提供“声调是第二声”，是没法定位的；但提供“拼音首字母是Z”，就能定位。

失效场景有三种典型：

-- 场景1：完全跳过第一列 WHERE b = 1 AND c = 2; -- 场景2：只用了第一列和第三列，中间列缺失 WHERE a = 1 AND c = 2; -- 场景3：第一列用了范围查询 WHERE a > 1 AND b = 2;

场景1里，索引的第一列a没有出现在条件里，B+树的根节点就不知道往哪走，整个索引失效。场景2里，a能用到索引定位，但c用不到——因为a确定之后，b是不确定的，同一a值下有多个b值，而c只有在b确定时才是有序的。场景3里，a是范围条件，b同样无法继续定位，原理和2.5节讲的范围右侧列失效一致。

最左前缀不是说“必须全部出现”，而是“必须从第一列开始连续出现”。a = 1 AND b = 2能用到两列，a = 1 AND c = 2只能用一列。设计联合索引时，要把等值查询的列放在最前面，范围查询的列往后放，这是铁律。

2.5 范围查询右侧的列失效：范围之后全失效

这个场景值得单独强调，因为它特别隐蔽。联合索引(a, b, c)，查询是WHERE a = 1 AND b > 10 AND c = 1，很多人以为三列都用了索引，实际上只有a和b能用，c直接失效。

原因在于，当限定a = 1且b > 10之后，命中的这些行里，c的排列是乱序的。B+树只保证“b相同的那一组内部c有序”，b不同时c的大小关系和b没有绑定关系。比如b=11的c是50，b=12的c是1，虽然b是连续递增的，但c不是。因此优化器没法在c上继续做等值定位。

这个问题的解法很直接：在设计联合索引时，把等值条件列放在前面，范围条件列放在最后。(a, c, b)这样的顺序就比(a, b, c)更合理——a = 1 AND c = 1先定位，b > 10再扫范围。这里再强调一次，联合索引的列顺序不是拍脑袋定的，是根据SQL模式反推出来的。

2.6 OR条件连接：并集查询的尴尬

WHERE a = 1 OR b = 2，如果a和b都有单列索引，MySQL有可能会用index_merge合并两个索引的结果集，但这是特殊情况。更常见的情况是a有索引而b没有，或者优化器评估合并成本太高，最后整条SQL变成全表扫描。

为什么OR会让索引失效？因为OR表达的是“两个条件满足任意一个即可”，在B+树里没有一条路径能同时覆盖两个分支的并集。走索引的话，要分别查两个分支然后合并去重；全表扫描的话，一遍扫完顺便判断两个条件。当两个分支各自的选择性都不高时，全表扫描往往更快。

实际项目中，我遇到更多的是这种写法：

SELECT * FROM user WHERE status = 1 OR name = '张三';

status的选择性很低，name有索引，但OR之后优化器直接全扫。改写方式是拆成两个查询再用UNION ALL合并：

SELECT * FROM user WHERE status = 1 UNION ALL SELECT * FROM user WHERE name = '张三';

这里要注意UNION ALL不会去重，如果业务上需要去重就换UNION。拆开后每个查询都能独立走索引，性能提升立竿见影。另外，如果OR两边的列都有索引且区分度都很高，MySQL的index_merge也不差，但依赖优化器做合并不如自己控制来得稳。

2.7 索引列参与算术运算或隐式拼接

和函数操作类似，索引列一旦进入算术表达式，索引就废了。看看这个常见写法：

-- 希望查出num*2等于20的记录 WHERE num * 2 = 20;

MySQL无法用num的索引，因为它要先对每一行的num做乘2运算，才能跟20比较。改写方式是把表达式挪到常量一侧：

WHERE num = 20 / 2;

这里20 / 2是常量表达式，MySQL在优化阶段就能算出来，不涉及任何一行数据的计算。

还有个容易忽略的场景是字符串拼接。比如WHERE concat(first_name, last_name) = '张三'，即使你在first_name上建了索引，works也用不上。这种“加工索引列”的思路，本质上和函数操作是一回事：破坏了索引列与B+树之间的直接对应关系。

2.8 否定式查询与空值判断：NOT IN、!=、IS NOT NULL

WHERE status != 1、WHERE id NOT IN (1, 2, 3)、WHERE status IS NOT NULL，这类“排除型”条件，B+树往往帮不上忙。原因是索引擅长“定位命中目标”，不擅长“确认哪些不需要”——要想知道哪些记录不等于某个值，基本上要把整棵索引树扫一遍，区别不大。

判断逻辑很简单：如果status只有两个值（比如0和1），status != 1意味着要命中表中几乎全部数据，这时候全表扫描确实比走索引快，优化器放弃索引是正确决策。这类情况就算你用FORCE INDEX强行走索引，性能也不会变好，反而可能更差。

IS NULL和IS NOT NULL是很多人的盲区。普通二级索引可以存NULL值，但IS NOT NULL往往被优化器判定为扫描大量数据；IS NULL在部分版本里可能用得上索引，前提是索引区分度足够且统计信息认为划算。实务上这两种写法都不太指望索引，能用默认值兜底就兜底，比如给字段设置默认值0或空字符串，业务查询就完全避开NULL判断。

2.9 排序与分组：filesort背后的索引失效

ORDER BY和GROUP BY能不能用上索引，要看排序字段和索引列的顺序、方向是否匹配。联合索引(a, b)，执行ORDER BY b但WHERE里没有限定a，由于索引先按a排序，b的全局顺序是不连续的，MySQL只能把结果集放到内存或磁盘里做filesort。

更隐蔽的是排序方向的问题。ORDER BY a ASC, b DESC，一个升序一个降序，InnoDB的索引扫描可以正着走也可以倒着走，但同一个索引树同时满足两列不同方向就比较困难，大概率还是filesort。

遇到排序慢，优先考虑调整索引列顺序让排序字段成为索引的“紧邻排列”，而不是直接加内存参数。filesort不一定比索引排序慢很多，但当排序数据集远超sort_buffer_size时，磁盘临时文件就会拖垮性能，这时候通过索引消除排序的收益非常明显。

2.10 优化器主动“降级”：统计信息与成本判断

前几节讲的都是SQL写法破坏索引结构，这一节讲的是索引本身没问题，但优化器“不领情”。即使SQL完全满足索引匹配规则，如果优化器根据统计信息判断“走索引回表的成本 > 全表扫描成本”，它就会放弃索引。

触发这种情况的条件通常有：

• 查询条件命中表里过大的数据比例，比如超过20%到30%。
• 索引区分度低，比如性别字段，只有两个值，走索引反而要多次回表。
• 表数据量小，几百行数据全扫也就几个页，走索引反而多一次额外IO。
• 统计信息过期，MySQL对数据分布的估算不准。

解决思路不是硬逼优化器，而是先把SQL和数据分布看清楚。如果是因为命中比例确实太高，走索引本来就无益；如果是因为统计信息过期，执行ANALYZE TABLE刷新一下统计信息往往就好了。实在优化器犯倔，可以用FORCE INDEX指定索引，但这属于最后手段，我一般只在临时救火时用。

3. 用EXPLAIN实测，验证索引到底有没有失效

理论讲再多，不如动手查一次。EXPLAIN是MySQL自带的执行计划查看工具，用法就是在SQL前面加EXPLAIN关键字：

-- 注意：EXPLAIN也可以用于UPDATE、DELETE、INSERT EXPLAIN SELECT * FROM user WHERE phone = 13800138000;

3.1 先看四个关键字段

执行EXPLAIN之后，结果集里有几列需要重点关注：

type是ALL的时候基本可以断定全表扫描。type是ref说明走了普通索引等值查询，range说明走了范围扫描，index表示扫描了整棵索引树但没用树定位能力——比如覆盖索引扫描全量，这种虽然用了索引，但效果不一定好。

3.2 一个完整的对比实验

建一张简单的测试表：

CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, create_time DATETIME NOT NULL, KEY idx_phone (phone), KEY idx_create_time (create_time) ) ENGINE=InnoDB;

先看失效写法：

EXPLAIN SELECT * FROM user WHERE phone = 13800138000;

结果里type大概率是ALL，key是NULL，rows接近全表行数。这是因为VARCHAR列和数字常量比较发生了隐式CAST。

改成正确写法：

EXPLAIN SELECT * FROM user WHERE phone = '13800138000';

这时候type变成ref，key显示idx_phone，rows骤降，Extra里可能还出现Using index condition，说明索引生效了。

再试函数包裹的情况：

EXPLAIN SELECT * FROM user WHERE DATE(create_time) = '2025-01-01';

type是ALL，key是NULL。改成范围查询：

EXPLAIN SELECT * FROM user WHERE create_time >= '2025-01-01 00:00:00' AND create_time < '2025-01-02 00:00:00';

type变成range，key显示idx_create_time，rows和执行时间都有明显改善。

3.3 key_len：判断联合索引实际用到了几列

key_len这个字段很多人忽略，但它非常有用。它表示MySQL在索引里实际使用到的字节数，通过计算可以反推联合索引到底生效了几列。

假设一个联合索引(a, b, c)，三列都是INT NOT NULL，那么每列占4字节。如果key_len是4，说明只用到了第一列a；如果key_len是8，说明用到了a和b两列；如果是12，三列全部用上。VARCHAR的计算稍微复杂：VARCHAR(20)在utf8mb4字符集下，最大长度20乘以4等于80字节，再加2字节长度标识，如果允许NULL再加1字节，得出83字节。

通过EXPLAIN之后核对key_len，能快速发现“我以为索引全用上了，实际上只用了前缀列”这类隐蔽问题。

3.4 进阶：用OPTIMIZER_TRACE看优化器的内心戏

EXPLAIN告诉你怎么执行，但有时候你想知道“为什么这么选”，可以用OPTIMIZER_TRACE。MySQL会记录优化器做决策时的详细过程，包括每个执行计划的成本估算、为什么不选某个索引等。

SET SESSION optimizer_trace = 'enabled=on'; SELECT * FROM user WHERE phone = 13800138000; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET SESSION optimizer_trace = 'enabled=off';

输出里的rows_estimation能看到每个索引的预估扫描行数，considered_execution_plans能看到最终候选方案。这个工具适合排查“明明索引存在但优化器不选”的疑难杂症，看一次内部决策过程，比猜半天强多了。

4. 避坑指南与线上排查实录

理论场景聊完了，分享几个我在真实项目里遇到过的案例，每一个都花了不少时间定位，希望你能跳过这些坑。

4.1 字符集不一致导致的隐式转换

有一次排查线上慢查询，order表和user表做JOIN，关联字段都叫user_id，各自也都建了索引，但执行计划显示驱动表全表扫描。检查字段类型，两个表都是BIGINT，类型完全一致，但就是不走索引。

后来发现两张表的字符集不一样，一张是utf8mb4，一张是latin1。MySQL在做JOIN时，如果两张表的字符集不同，低优先级的字符集会被隐式转换成高优先级，这个转换发生在关联字段上，相当于再次对索引列做了一次隐式CAST。

修复方式是统一两个表的字符集，或者至少保证关联字段的字符集一致。这个坑非常隐蔽，遇到JOIN不走索引时，先查两张表的字符集和排序规则。

4.2 WHERE DATE()查询拖垮接口的案例

一个报表接口，查询条件是WHERE DATE(create_time) = CURDATE()，数据量500万，接口从1秒慢慢退化到3秒。EXPLAIN一看type是ALL，key是NULL。

改成create_time >= '2025-01-01 00:00:00' AND create_time < '2025-01-02 00:00:00'之后，执行时间从2秒多降到20毫秒左右，接口响应直接恢复。改动只有一行SQL，但性能差异是百倍级别。这个案例我讲过很多次，因为它太典型了：不是没建索引，而是函数包裹把索引毁了。

4.3 区分度低的列，建了索引也别指望

还有一个案例是给status字段建了索引，字段只有两个值：0和1，数据分布是90%的1和10%的0。查询WHERE status = 1的时候，优化器选择全表扫描——这个选择完全正确，因为命中90%的数据，走索引意味着90%的行都要回表，效率远不如直接全扫。

遇到这类需求，正确的做法不是优化SQL，而是考虑数据分布本身。如果业务确实经常需要查“少数派”的0，可以考虑把“0”单独摘出来建一个部分索引，或者用其他手段避免扫描大量数据。

4.4 常见问题速查表

4.5 写SQL时养成两个习惯

最后说两个我自己很受用的习惯。第一个是“索引列独立原则”——凡是在WHERE、JOIN、ORDER BY、GROUP BY里出现的索引列，保持它是“裸列”，不要对它做任何函数、运算、拼接。这个习惯养成之后，很多失效问题在写SQL的阶段就自动避免了。

第二个是“每一条慢查询都先看EXPLAIN”——不要凭感觉猜，先看type、key、key_len、rows、Extra这五个字段，基本能定位百分之八十的问题。再解决不了就上OPTIMIZER_TRACE，看优化器是怎么做成本决策的。排查次数多了，你自然就能一眼看出SQL里的隐患。

我个人的体会是，索引本身是门“让数据有序可查”的手艺活，理解得越透，踩的坑就越少。写SQL时多想一步，线上就能少熬一次夜。这个账，怎么算都划算。