大厂高频：为什么你的 MySQL 索引总是失效？（收藏版）

Java2Top
49 阅读11分钟

hello，大家好，我是小龙，《Java面试手册—大白话专项突击大厂面试高频考题~》，持续为您更新，觉得不错，可以点赞+评论+转发~

言归正题，我们时常发现：明明建了索引，SQL 也写得规规矩矩，为什么执行计划却绕过了索引？

  • 模糊匹配就真的无法走索引了吗？
  • 多列联合索引，是不是用到了其中一列就能加速？
  • 覆盖索引、最左前缀、隐式转换……你真的搞清楚它们的边界了吗？

不少人第一次建索引时，常常带着满满的期待：一条SQL语句，三秒变三毫秒。可现实往往不如人意。有时候索引建上了，查询却依旧慢如蜗牛；用 EXPLAIN 一看，MySQL 根本不鸟你的索引，直接全表扫过去了。

为什么会这样？索引这玩意儿，真的只是"建了就能快"这么简单吗？

为什么你的索引总是失效-思维导图.png

接下来，会从以上几个点展开，我们不妨先了解一下 索引本质，是怎样维护这些存储得数据地。

索引的本质：B+树如何加速查询？

在深入索引失效原因前，我们必须先弄清楚：索引到底是什么？

MySQL InnoDB 存储引擎的索引是基于 B+树实现的。这种数据结构有几个关键特性：

  1. 有序存储：键值按顺序排列在叶子节点中
  2. 多级索引：非叶子节点存储键值和指针，形成层级结构
  3. 叶子节点相连：所有叶子节点通过链表相连，方便范围查询

MySQL_BPlusTree_Index.png 当你执行一个查询时，MySQL 会从 B+树的根节点开始，比较键值大小，逐层向下，最终定位到叶子节点，找到对应的数据行或数据行指针。这个过程将原本需要 O(n) 的全表扫描，优化为 O(log n) 的树形结构查找。

举个例子，在一个百万级的表中，B+树高度可能只有 3-4 层，这意味着只需 3-4 次磁盘 I/O 就能定位到目标数据，而全表扫描则需要成百上千次 I/O。

了解 索引底层基本结构后，我们不妨从一个最常见、也最容易误判的情况说起——字符串模糊匹配

为什么左模糊查询让 B+树"束手无策"？

很多人第一次吃瘪，大概率是在使用 LIKE '%xxx' 查询的时候。

你可能会说：字段 name 上已经建了普通 B+树索引，那下面这条 SQL 为啥走不了索引？

当你执行：

SELECT * FROM users WHERE name LIKE '%zhang%';

B+树索引为何失效？让我们看看查询过程：

B+树的查找过程需要一个确定的起点。当你使用'%zhang%'时，匹配的第一个值可能是"azhang"，也可能是"zhangsan"，甚至"lizhangwei"。B+树无法确定从哪个节点开始查找。

具体到遍历过程：

  1. B+树需要从根节点开始定位第一个可能匹配的叶子节点
  2. 但'%zhang%'可能匹配任何位置包含"zhang"的值
  3. B+树无法利用其有序性确定起点
  4. 只能退化为全表扫描，检查每一行

这就像你拿到一本按姓名首字母排序的通讯录，要找"所有名字中包含'明'字的人"——你别无选择，只能从头翻到尾。

而当你改为右模糊查询：

SELECT * FROM users WHERE name LIKE 'zhang%';

B+ 树便可以精确定位到以 "zhang" 开头的第一个叶子节点，然后沿着叶子节点链表顺序扫描，直到不再满足前缀条件。这就是为什么右模糊能用索引，而左模糊不能。

联合索引的内部结构与最左匹配原则

假设我们有一个联合索引：

CREATE INDEX idx_age_name_salary ON employees(age, name, salary);

在 B+树中，这个索引的键值是如何组织的？实际上，它会按照 (age, name, salary) 的顺序构成一个"复合键"，类似于：

(25, "Alice", 5000) -> 数据行指针
(25, "Bob", 6000) -> 数据行指针
(26, "Alice", 5500) -> 数据行指针
...

重要的是：B+树中的排序是按第一列完全排序，第一列相同时按第二列排序，依此类推。

反映到此处，就是先按 age 排序，age 相同时按 name 排序，name 相同时按 salary 排序。

这种结构决定了"最左匹配原则"：查询条件必须按照索引列的顺序使用，从最左边开始，不能跳过中间列。

从B+树角度看：

  1. WHERE age = 30：可以直接定位到age=30的节点
  2. WHERE age = 30 AND name = 'Jack'：先定位age=30，再在这些节点中找name='Jack'
  3. WHERE name = 'Jack'：无法利用B+树结构，因为name不是最左列，B+树无法直接定位到name='Jack'的节点

比如：

-- 能用索引：使用了第一列 age
SELECT * FROM employees WHERE age = 30;
​
-- 能用索引：使用了前两列 age 和 name
SELECT * FROM employees WHERE age = 30 AND name = 'Jack';
​
-- 不能用索引：跳过了第一列 age
SELECT * FROM employees WHERE name = 'Jack';

第三个查询无法使用索引，因为 B+树无法在不知道 age 的情况下，直接定位到 name='Jack' 的记录。这就像在一个按"省-市-区"排序的地址簿中，直接查找"朝阳区"而不指定省市一样，只能全表扫描。

范围查询与索引列顺序：>< 条件如何打乱最左匹配？

最左匹配原则有一个关键补充：范围条件后的列无法用于索引匹配。

考虑这个查询：

SELECT * FROM employees WHERE age > 30 AND name = 'Jack' AND salary = 10000;

B+树的遍历过程是：

  1. 定位到第一个age>30的节点
  2. 沿着叶子节点链表顺序扫描所有age>30的记录
  3. 对于每条记录，再检查name和salary条件

虽然条件涵盖了所有索引列，但只有 age 这一列能用到索引！为什么？

当 B+树遇到范围条件 age > 30 时，它能定位到第一个 age > 30 的节点，然后沿着叶子节点链表扫描。但在这个扫描过程中，后续节点的 name 和 salary 不再保证是有序的（相对于整体扫描范围而言），所以无法继续利用 B+树的有序特性进行快速定位。

从全局看，B+树确实是按(age,name,salary)排序，但在age>30这个子集中，name 和 salary的顺序是打散的。

这就是为什么在设计联合索引时，我们通常建议：将等值条件的列放在前面，范围条件的列放在最后。

例如，如果查询模式是：

WHERE age = ? AND name LIKE 'prefix%' AND salary BETWEEN ? AND ?

那么最优的索引顺序是 (age, name, salary)，因为 age 是等值查询，name 是右模糊（可以视为范围的特例），salary 是明确的范围。

等值和范围的顺序打乱最左匹配

让我们通过具体例子理解范围查询如何影响索引使用：

对于索引(a,b,c)：

  1. WHERE a = 1 AND b > 2 AND c = 3

    • a列：完全使用（等值）
    • b列：部分使用（范围）
    • c列：不使用（b是范围，c无法继续利用索引）

  2. WHERE a > 1 AND b = 2 AND c = 3

    • a列：部分使用（范围）
    • b列和c列：不使用（a是范围，后续列无法继续利用索引）

  3. WHERE a = 1 AND b = 2 AND c > 3

    • a列：完全使用（等值）
    • b列：完全使用（等值）
    • c列：部分使用（范围）

从B+树遍历角度看，一旦遇到范围条件，就会产生一个"扇出"效应，导致后续列无法继续利用B+树的有序特性。

索引下推

在 MySQL 5.6 之前，利用索引查询时，只能使用索引的最左前缀列进行过滤，其他条件必须在获取行数据后再过滤。

例如，对于索引 (age, name, salary) 和查询：

SELECT * FROM employees WHERE age = 30 AND name LIKE 'J%';

旧版 MySQL 会：

  1. 利用 age=30 定位记录
  2. 取出所有 age=30 的行
  3. 然后再过滤 name LIKE 'J%'

而 MySQL 5.6 引入的"索引下推"(Index Condition Pushdown, ICP) 优化，允许在索引遍历过程中，将 name LIKE 'J%' 的条件也下推到索引层面进行过滤，减少了回表操作。

这意味着即使某些列因为最左匹配原则无法用于索引定位，它们仍然可能用于索引层面的数据过滤，这是一个常被忽视的性能优化点。

隐式转换：当索引列与条件类型不匹配时

观察这两个查询：

-- mobile 是 varchar 类型
SELECT * FROM users WHERE mobile = 13800138000;
SELECT * FROM users WHERE mobile = '13800138000';

第一个查询会导致索引失效，因为发生了隐式类型转换。MySQL 会将查询改写为：

SELECT * FROM users WHERE CAST(mobile AS SIGNED) = 13800138000;

这在索引列上应用了函数，破坏了 B+树的有序性。

从 B+树的角度看，这相当于要求：

  1. 取出每个 mobile 值
  2. 转换为数字
  3. 与 13800138000 比较

这种操作无法利用 B+树的快速定位能力，只能全表扫描。

函数操作：为什么索引列"被计算"就会失效？

当你在索引列上应用函数时：

SELECT * FROM orders WHERE YEAR(create_time) = 2023;

索引同样会失效。因为 B+树中存储的是原始的 create_time 值，而查询条件是对这些值应用 YEAR() 函数后的结果。

从 B+树遍历角度看，这需要：

  1. 访问每个叶子节点
  2. 取出 create_time 值
  3. 计算 YEAR(create_time)
  4. 与 2023 比较

这完全无法利用 B+树的有序特性，只能全表扫描。

正确的做法是改写为范围查询：

SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

这样 B+树可以直接定位到范围边界，然后顺序扫描符合条件的记录。

为什么有时优化器会"无视"你的索引？

有时即使条件完全符合索引使用规则，MySQL 也选择了全表扫描。这是因为查询优化器会评估多种执行计划的成本，选择认为最高效的那个。

如果 MySQL 估计走索引需要访问的行数超过表总行数的一定比例（通常是 20%-30%），它会认为全表扫描更高效。这是因为：

  1. 索引查询需要额外的随机 I/O（回表操作）
  2. 随机 I/O 比顺序 I/O（全表扫描）更昂贵
  3. 当访问行数过多时，随机 I/O 的总成本超过了顺序 I/O

你可以通过 EXPLAIN 命令查看 MySQL 的行数估计和选择依据：

EXPLAIN SELECT * FROM users WHERE age > 20;

如果 rows 值接近表总行数，优化器可能会选择全表扫描。

覆盖索引：避免回表的利器

当查询的所有列都包含在索引中时，MySQL 可以直接从索引获取数据，而无需回表查询完整行，这称为"覆盖索引"。

例如，对于索引 (age, name, salary)：

-- 覆盖索引：所有列都在索引中
SELECT age, name, salary FROM employees WHERE age > 30;
​
-- 非覆盖索引：需要回表查询 address
SELECT age, name, address FROM employees WHERE age > 30;

第一个查询的执行计划中，你会看到 Extra 列显示 "Using index"，表示使用了覆盖索引，这通常比需要回表的查询快很多。

从 B+树角度看，覆盖索引避免了从索引叶子节点获取指针，再去聚簇索引查询完整行的过程，减少了 I/O 操作。

总结：让索引高效工作的核心法则

理解了 B+树的工作原理，我们可以总结出让索引高效工作的核心法则：

  1. 保持索引列的"原样" ：避免函数运算和类型转换
  2. 遵循最左匹配原则：从左到右使用索引列，不跳过
  3. 范围查询后的列无法用于索引匹配：调整索引列顺序，把范围条件放最后
  4. 合理设计联合索引：考虑查询模式和列的选择性
  5. 利用覆盖索引：减少回表操作
  6. 理解优化器的选择：有时全表扫描确实比索引查询更快

索引不是魔法，它是一种数据结构，有其固有的优势和限制。只有深入理解其工作原理，才能真正掌握如何让它为我们所用。

思考题

如果一个联合索引是 (a, b, c)，查询条件是 WHERE a > 10 AND b = 20 AND c = 30，那么哪些列能用到索引？如果调整为 WHERE a = 10 AND b > 20 AND c = 30，又会如何？这背后的 B+树遍历过程有何不同？

推荐阅读：

拆解 Java 类加载机制：双亲委派模型真的被打破了吗？

明明加了 volatile，为什么数据还是错的？

avatar
文章
阅读
粉丝