面试专题

MySQL 索引在什么情况下会失效？

从联合索引、函数运算、隐式转换、LIKE、OR、选择性和优化器成本等角度，系统解释 MySQL 索引无法使用、部分使用及主动放弃索引的常见场景。

难度：深入更新：2026-07-25

MySQL 索引在什么情况下会失效？

面试回答

所谓“索引失效”并不是 MySQL 的正式术语，实际需要区分三种情况：

  1. SQL 写法或索引结构决定了索引无法用于快速定位，例如联合索引没有满足最左前缀、对索引列进行未建立函数索引的运算、LIKE 以通配符开头。
  2. 索引可以使用，但只能使用其中一部分。例如联合索引 (a,b,c) 查询 a=1 AND b>10 AND c=3 时，通常可以用 a、b 确定扫描范围，c 可能通过索引条件下推继续过滤，却不能进一步缩小连续的索引扫描区间。
  3. 索引具备使用条件，但优化器估算全表扫描成本更低，于是主动不选。例如查询返回大量数据、索引选择性很低或回表成本过高。

常见场景包括：

  • 联合索引不满足最左前缀；
  • 在索引列上执行函数、计算或不匹配的表达式；
  • 比较两侧数据类型、字符集或排序规则不兼容，引发隐式转换；
  • LIKE '%keyword' 或 LIKE '%keyword%' 以通配符开头；
  • OR 的某个分支缺少可用索引，且优化器无法采用合适的 Index Merge；
  • !=、NOT IN、IS NOT NULL 等条件命中数据过多；
  • 查询需要返回表中很大比例的数据，优化器认为全表扫描更便宜；
  • 索引统计信息不准确，导致优化器成本估算偏差。

判断索引是否真正有效，不能只看 possible_keys，应使用 EXPLAIN 或 EXPLAIN ANALYZE，重点观察实际选择的 key、访问类型 type、预计或实际扫描行数，以及是否发生大量回表。

一句话总结

索引失效要区分“不能用、只用一部分、能用但优化器不选”；排查时先看索引结构和 SQL 写法，再用执行计划验证实际扫描成本。

详细讲解

一、先区分三种“索引失效”

日常讨论经常把所有没有达到预期性能的情况都叫作“索引失效”，但这会掩盖真正的问题。

1. 索引无法用于定位

索引的有序结构与查询条件不匹配，MySQL 无法根据该条件确定一个有效的索引查找范围。

例如索引为：

KEY idx_user_time (user_id, created_at)

查询只使用第二列：

SELECT *
FROM orders
WHERE created_at >= '2026-07-01';

传统的联合索引范围查找无法绕过第一列，直接按照第二列定位连续区间。

2. 只使用了部分索引能力

SQL 使用了索引，但索引没有把扫描范围缩小到预期程度。

例如：

KEY idx_abc (a, b, c)

查询：

SELECT *
FROM t
WHERE a = 1
  AND b > 10
  AND c = 3;

通常可以使用 a=1 AND b>10 构造索引扫描范围。由于 b 是范围条件，c=3 一般不能继续把这个范围切成更小的连续区间。

但这不等于 c 完全没用。满足条件时，MySQL 可以通过索引条件下推（Index Condition Pushdown，ICP），在索引层先判断 c=3，减少回表数量。

3. 优化器主动不选择索引

索引本身可用，但 MySQL 优化器根据统计信息估算：

使用二级索引扫描 + 大量回表
                >
顺序扫描整张表

于是执行计划选择全表扫描。

这种情况不是索引结构失效，而是成本选择。强行使用索引不一定更快。

二、联合索引不满足最左前缀

假设存在联合索引：

KEY idx_abc (a, b, c)

B+ 树中的记录首先按 a 排序；a 相同时，再按 b 排序；a、b 都相同时，才按 c 排序。

可以近似理解为：

(a=1, b=1, c=1)
(a=1, b=1, c=5)
(a=1, b=2, c=2)
(a=2, b=1, c=3)

因此以下条件通常能够使用联合索引定位：

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

最后一个条件仍然可以通过 a=1 定位范围，只是缺少 b 后，c=3 通常不能继续缩小查找区间。

以下查询没有使用最左列：

WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

它们通常无法使用传统的最左前缀方式直接定位。

需要注意，MySQL 8.0 在满足条件时可能使用 Skip Scan，把缺失的最左列按多个可能值分别扫描。因此“缺少最左列一定完全不用索引”也不是绝对结论，最终必须以执行计划为准。

三、范围条件之后的列还能不能使用

常见说法是：

联合索引遇到范围查询后，后面的列全部失效。

这个说法过于绝对。

索引为：

KEY idx_abc (a, b, c)

查询：

WHERE a = 1 AND b > 10 AND c = 3

更准确的理解是：

  • a=1 和 b>10 可以共同确定扫描范围；
  • c=3 通常不能继续缩小这个连续范围；
  • 如果启用了 ICP，c=3 仍可能在索引层参与过滤；
  • 如果查询列都包含在索引中，还可能形成覆盖索引，避免回表。

所以“能否参与范围定位”“能否参与索引层过滤”和“能否避免回表”是三个不同问题。

另外，IN 在很多情况下会被转换成多个等值范围，不应简单地把所有 IN 都视为遇到范围后索引失效。

四、在索引列上进行函数或计算

假设 created_at 上有普通索引：

KEY idx_created_at (created_at)

下面的写法把函数作用在索引列上：

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-07-25';

普通索引保存的是原始 created_at 值，而不是 DATE(created_at) 的计算结果。MySQL 通常无法直接根据普通索引定位函数结果。

可以改写为范围条件：

SELECT *
FROM orders
WHERE created_at >= '2026-07-25 00:00:00'
  AND created_at <  '2026-07-26 00:00:00';

类似问题还包括：

WHERE amount + 1 = 100
WHERE LOWER(username) = 'tom'
WHERE CAST(order_no AS UNSIGNED) = 1001

应优先把运算移动到常量一侧：

WHERE amount = 99

如果业务必须长期按某个表达式查询，可以根据 MySQL 版本与场景考虑函数索引，或为表达式建立生成列并对生成列创建索引。

五、隐式类型转换

假设订单号是字符串：

order_no VARCHAR(32),
KEY idx_order_no (order_no)

推荐使用相同类型进行比较：

WHERE order_no = '10001'

如果写成：

WHERE order_no = 10001

MySQL 需要按照类型转换规则比较字符串与数字，可能对索引列执行转换，导致无法按原有字符串顺序高效定位。

排查时要检查：

  • 数字列是否与字符串参数比较；
  • 字符串列的字符集是否一致；
  • 字符串列的排序规则是否兼容；
  • JOIN 两侧字段的类型、长度、字符集和排序规则是否匹配。

例如两个字符串列字符集不兼容时，即使两侧都有索引，也可能无法高效使用索引完成连接。

最稳妥的原则是：

SQL 参数类型应与索引列类型一致，JOIN 字段的类型和字符规则也应保持一致。

六、LIKE 以通配符开头

索引为：

KEY idx_name (name)

以下前缀匹配通常可以利用 B+ 树的有序性：

WHERE name LIKE 'zhizhi%'

MySQL 可以定位到以 zhizhi 开头的索引范围。

以下写法无法确定字符串起始位置：

WHERE name LIKE '%zhizhi'
WHERE name LIKE '%zhizhi%'

普通 B+ 树索引通常无法据此快速定位范围。

如果查询是高频的任意位置文本搜索，应根据业务考虑：

  • FULLTEXT 全文索引；
  • Elasticsearch 等搜索系统；
  • 调整业务模型，保存可索引的前缀或结构化字段。

需要注意，如果查询需要的列全部包含在索引中，优化器仍可能选择扫描整个索引，因为索引比数据行更窄。此时执行计划中的 type 可能是 index，但这仍然是全索引扫描，并不是高效的范围查找。

七、OR 中存在无法使用索引的分支

假设只有 user_id 有索引：

SELECT *
FROM orders
WHERE user_id = 100
   OR remark = 'manual';

即使第一部分能够使用索引，为了找出满足 remark='manual' 的记录，MySQL 仍可能需要扫描大量数据，最终选择全表扫描。

常见优化方式包括：

  1. 为确实需要的条件建立合适索引；
  2. 在语义允许且执行计划更优时拆成两个查询并使用 UNION ALL；
  3. 检查重复数据处理，不能为了用索引直接把 OR 改坏；
  4. 观察优化器是否采用 Index Merge，而不是假设多个单列索引一定会被组合。

如果 OR 两侧分别有可用索引，MySQL 可能选择 Index Merge，也可能根据成本选择其他计划。

八、否定条件和低选择性条件

以下条件经常返回大量记录：

WHERE status != 1
WHERE type NOT IN (1, 2)
WHERE deleted_at IS NOT NULL

这些操作符并不意味着语法上绝对不能使用索引。真正的问题通常是选择性：

如果条件会命中表中大部分记录，使用二级索引定位后再逐条回表，可能比全表扫描更贵。

同理，以下字段即使有索引，也可能因为区分度太低而不被选择：

性别
是否删除
只有少量枚举值的状态
大量相同值的业务类型

低基数字段并非永远不适合索引。如果它与其他字段组成联合索引、查询命中比例很低，或者能够形成覆盖索引，依然可能产生价值。

另外，IS NULL 可以使用索引，不应把它列为固定的索引失效规则。

九、查询返回数据过多

假设表中有 100 万行，而条件预计返回 80 万行：

SELECT *
FROM orders
WHERE status = 1;

如果 status 是二级索引，执行过程可能是：

扫描大量二级索引记录
        ↓
根据主键逐条回表
        ↓
读取完整行

大量离散回表的成本可能高于直接扫描聚簇索引，因此优化器会主动选择全表扫描。

可以从以下方向优化：

  • 确认业务是否真的需要返回如此多的数据；
  • 增加更有区分度的查询条件；
  • 建立符合查询模式的联合索引；
  • 只查询必要字段，评估覆盖索引；
  • 分页或分批处理；
  • 避免用 FORCE INDEX 掩盖不合理的数据访问方式。

十、ORDER BY 或 GROUP BY 没有匹配索引顺序

索引不仅可以用于 WHERE 过滤，也可以帮助排序和分组，但需要满足相应的顺序条件。

例如索引：

KEY idx_user_time (user_id, created_at)

查询：

SELECT *
FROM orders
WHERE user_id = 100
ORDER BY created_at;

通常可以利用索引中同一 user_id 下 created_at 有序的特点。

但下面的查询未限定最左列：

SELECT *
FROM orders
ORDER BY created_at;

created_at 在整棵联合索引中并不是全局连续有序，通常无法直接利用该索引消除排序。

以下情况也可能无法直接使用索引顺序：

  • 排序列顺序与联合索引不匹配；
  • 过滤条件打断了所需的索引顺序；
  • 对排序列执行表达式或函数；
  • 多张表连接后按非驱动表字段排序；
  • 索引方向与查询要求不匹配，且当前版本或索引定义无法支持。

此时 EXPLAIN 的 Extra 中可能出现 Using filesort。

需要注意，Using filesort 表示没有直接利用索引顺序完成排序，并不保证真的使用磁盘文件；数据较小时也可能在内存中排序。

十一、索引统计信息不准确

MySQL 优化器依赖统计信息估算：

  • 条件会命中多少行；
  • 不同索引的选择性；
  • 扫描和回表成本；
  • JOIN 的顺序及成本。

如果数据分布发生剧烈变化，而统计信息未能反映真实情况，优化器可能选错索引或改用全表扫描。

可以先通过执行计划验证估算行数与实际行数是否偏差明显，再根据情况评估：

ANALYZE TABLE orders;

MySQL 8.0 还可以使用直方图帮助优化器理解非索引列或数据分布不均匀字段的值分布。

不要在没有验证的情况下频繁更新统计信息，也不要把所有执行计划问题都归因于统计信息。

十二、如何用 EXPLAIN 判断

基础排查可以执行：

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 100;

重点观察：

字段关注点
possible_keys理论上可能使用哪些索引
key优化器最终选择了哪个索引
key_len实际使用的索引长度，可辅助判断联合索引用到哪一部分
type访问方式，如 const、ref、range、index、ALL
rows预计需要检查的行数
filtered经过表条件过滤后预计保留的百分比
Extra是否覆盖索引、ICP、额外排序或临时表等

1. type 到底表示什么

type 表示 MySQL 访问当前表中记录的方式。在 EXPLAIN FORMAT=JSON 中，对应字段名是 access_type。

它回答的不是“有没有索引”这么简单，而是：

MySQL 准备通过唯一键直接取一行、按普通索引查一组记录、扫描一个索引范围，还是扫描整棵索引或整张表？

官方文档通常按照下面的顺序描述常见访问类型：

system
  ↓
const
  ↓
eq_ref
  ↓
ref
  ↓
range
  ↓
index
  ↓
ALL

越靠前通常代表定位越精确，但这不是脱离数据量的绝对性能排名。

例如：

  • 小表使用 ALL 可能只扫描几十行，成本很低；
  • ref 虽然比 range 靠前，但如果某个值匹配几十万行，仍然可能很慢；
  • range 如果只扫描十行，通常会非常高效；
  • index 虽然使用了索引，却可能把整个索引扫描一遍。

所以查看 type 后，还必须结合 key、rows、filtered、Extra 以及实际耗时判断。

2. const：通过主键或唯一键最多定位一行

const 表示当前表最多只有一条记录满足条件，并且这条记录在查询开始时只需要读取一次。

典型场景是使用常量匹配主键或完整唯一索引：

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(128) NOT NULL,
    UNIQUE KEY uk_email (email)
);

查询主键：

SELECT *
FROM users
WHERE id = 100;

或者查询唯一键：

SELECT *
FROM users
WHERE email = 'tom@example.com';

由于主键或唯一键能够保证最多返回一行，MySQL 可以直接定位目标记录：

常量值
  ↓
主键或唯一索引查找
  ↓
最多一条记录

如果是联合唯一索引，需要把能够确定唯一记录的索引列完整匹配，才容易形成 const：

UNIQUE KEY uk_tenant_order (tenant_id, order_no)

WHERE tenant_id = 10
  AND order_no = 'A1001'

如果只查询：

WHERE tenant_id = 10

可能返回多行，通常就不是 const。

const 的核心不是“使用了等号”，而是：

通过常量和唯一约束，能够确定当前表最多只返回一条记录。

3. eq_ref：JOIN 时每条前表记录最多匹配一行

eq_ref 最常见于多表 JOIN。

它表示对于前面表产生的每一条记录，MySQL 都通过主键或 UNIQUE NOT NULL 索引，在当前表中最多查到一条记录。

例如：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    KEY idx_user_id (user_id)
);

SELECT o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id;

假设执行顺序是先读取 orders，再根据 o.user_id 查询 users：

orders 的一条记录
        ↓
u.id = o.user_id
        ↓
通过 users 主键最多定位一行

此时 users 表的访问类型可能是 eq_ref。

const 和 eq_ref 的区别是：

类型查找值来自哪里典型场景
constSQL 中的常量WHERE id = 100
eq_ref前面表的列值u.id = o.user_id

4. ref：通过非唯一索引等值查找一组记录

ref 表示使用普通索引或联合索引的非唯一前缀进行等值查找，可能返回多条具有相同索引值的记录。

例如：

KEY idx_status (status)

SELECT *
FROM orders
WHERE status = 1;

如果 status=1 对应多条订单，访问过程是：

在 idx_status 中定位 status=1 的起点
                  ↓
连续读取所有 status=1 的索引记录
                  ↓
按需回表

因此，ref 不代表只读取一行。它可能读取几行，也可能读取几十万行，实际成本取决于索引选择性和回表数量。

联合索引只使用左侧一部分时，也可能出现 ref：

KEY idx_tenant_status (tenant_id, status)

WHERE tenant_id = 10

虽然使用了索引最左列，但同一个租户可能有大量记录，所以仍然是非唯一匹配。

JOIN 中如果使用普通索引关联，也常见 ref：

SELECT *
FROM users u
JOIN orders o ON o.user_id = u.id;

对于每个用户，普通索引 orders.idx_user_id 可能匹配多条订单，因此 orders 的访问类型可能是 ref，而不是 eq_ref。

5. range：扫描索引中的一个或多个区间

range 表示 MySQL 先根据条件确定一个或多个索引区间，然后只扫描这些区间中的记录。

典型范围查询：

SELECT *
FROM orders
WHERE created_at >= '2026-07-01'
  AND created_at <  '2026-08-01';

索引访问过程可以理解为：

定位 2026-07-01 的索引位置
              ↓
沿叶子节点向后扫描
              ↓
到达 2026-08-01 时停止

常见能够形成 range 的条件包括：

  • >、>=、<、<=；
  • BETWEEN；
  • IN (...) 形成多个离散范围；
  • IS NULL；
  • 能确定前缀范围的 LIKE 'abc%'；
  • 某些 <> 或其他可转化为索引区间的条件。

例如：

WHERE id IN (10, 20, 30)

虽然使用的是多个等值条件，优化器也可能把它组织成多个索引范围，因此 type 显示为 range。

range 是否高效，主要看范围大小：

扫描 10 条索引记录       → 通常很快
扫描 80 万条索引记录     → 仍可能很慢

所以不能看到 range 就直接认定 SQL 已经优化完成。

6. index：把整棵索引扫描一遍

index 很容易被误解为“查询已经通过索引精准定位”。

实际上，type=index 通常表示：

MySQL 没有通过索引条件缩小扫描区间，而是把选中的整棵索引从头到尾扫描一遍。

它与 ALL 的共同点是都可能读取全部记录；区别在于：

  • index 扫描索引树；
  • ALL 扫描表数据。

例如：

KEY idx_user_id (user_id)

SELECT user_id
FROM orders;

如果查询只需要 user_id，二级索引已经包含全部所需数据，MySQL 可以扫描更窄的 idx_user_id，而不必扫描保存完整行的聚簇索引。

执行计划可能显示：

type: index
key: idx_user_id
Extra: Using index

这里的 Using index 表示覆盖索引：查询需要的数据可以直接从索引取得，不需要回表。

另一个常见场景是利用索引顺序避免额外排序：

SELECT id
FROM orders
ORDER BY created_at;

如果存在适合的 created_at 索引，优化器可能按索引顺序扫描全部记录，以避免 filesort。

index 可能比 ALL 快，因为二级索引通常更窄，缓存命中和读取的数据量更小；但它本质上仍是全索引扫描，数据量大时成本依然很高。

7. ALL：扫描整张表

ALL 表示全表扫描。

对于 InnoDB，可以把它近似理解为扫描保存完整行数据的聚簇索引：

聚簇索引第一条记录
        ↓
逐条向后读取
        ↓
检查 WHERE 条件
        ↓
直到最后一条记录

典型原因包括：

  • 查询条件没有可用索引；
  • 在索引列上进行了导致无法定位的函数或转换；
  • LIKE 以通配符开头；
  • 条件会返回表中大部分数据；
  • 表很小，全表扫描成本更低；
  • 优化器根据统计信息判断使用索引加回表更贵。

例如：

SELECT *
FROM orders
WHERE remark LIKE '%manual%';

普通 B+ 树索引无法按照任意位置包含关系定位，执行计划很可能是 ALL。

但 ALL 不等于必须立刻创建索引。

如果表只有几十行，或者查询本来就要读取 80% 的数据，全表扫描可能是合理选择。真正需要关注的是：

  • 表有多大；
  • rows 预计扫描多少行；
  • 查询执行频率多高；
  • 是否处于 JOIN 的内层并被重复扫描；
  • 实际耗时和资源消耗是否不可接受。

特别是在 JOIN 中，如果前表返回一万行，而后表对每条前表记录都执行一次 ALL，成本可能被放大得非常严重。

8. 五种类型对比

type核心含义是否精准定位可能读取的记录数常见场景
const通过常量匹配主键或唯一键是最多 1 行WHERE id=100
ref通过普通索引做等值查找是，但结果不唯一1 行到大量记录WHERE status=1
range扫描一个或多个索引区间定位区间取决于范围大小BETWEEN、IN、>
index扫描整棵索引否通常为整个索引覆盖索引全扫描、按索引顺序遍历
ALL扫描整张表否通常为整张表无可用索引或全表扫描更便宜

还应单独记住 JOIN 中的 eq_ref：

对前表的每一条记录，通过当前表的主键或唯一非空索引最多匹配一行。

9. 不要只根据 type 判断性能

下面两个计划：

计划 A：type=ref，rows=500000
计划 B：type=range，rows=20

虽然官方列表中 ref 排在 range 前面，但计划 B 很可能扫描更少、执行更快。

同理：

计划 A：type=ALL，rows=30
计划 B：type=ref，rows=100000

对只有 30 行的小表执行全表扫描，不一定是问题。

因此，合理的判断方式是：

type
  +
key / key_len
  +
rows / filtered
  +
Extra
  +
EXPLAIN ANALYZE 的实际行数与耗时

type 是执行计划的重要入口，但不是最终结论。

几个常见误区：

  • possible_keys 有值，不代表最终使用了索引；
  • key 有值，不代表查询一定高效；
  • type=index 通常表示全索引扫描，不等于范围定位；
  • type=ALL 是全表扫描，但小表全表扫描可能就是合理计划；
  • Using index 通常表示覆盖索引，不等于“使用索引定位”；
  • 预计扫描行数少，也可能因估算偏差与实际执行差异很大。

MySQL 8.0.18 及以上版本可以使用：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 100;

它会真正执行查询，并显示实际行数和耗时。生产环境使用前应确认 SQL 的成本与副作用，特别是复杂查询或数据修改语句。

十三、排查顺序

遇到“明明建了索引却很慢”，可以按下面顺序检查：

  1. 确认慢 SQL、参数值和数据量，不能只拿脱离现场的 SQL 模板分析。
  2. 查看表结构、索引顺序、字段类型、字符集和排序规则。
  3. 使用 EXPLAIN 检查 key、type、rows、filtered 和 Extra。
  4. 检查联合索引是否满足最左前缀，范围条件之后是否只剩过滤能力。
  5. 检查索引列上是否存在函数、计算或隐式转换。
  6. 估算查询命中比例和回表次数，判断优化器放弃索引是否合理。
  7. 对比估算行数与实际行数，检查统计信息是否明显失真。
  8. 在可控环境中使用 EXPLAIN ANALYZE 验证真实执行路径。
  9. 调整 SQL 或索引后重新压测，不能只看执行计划就宣布优化完成。

十四、常见错误结论

1. 联合索引中间断了一列，整个索引都失效

不准确。最左侧连续部分仍可能用于定位，后续列也可能通过 ICP 或覆盖索引发挥作用。

2. 遇到范围查询，后面的索引列完全没用

不准确。后续列通常不能继续缩小连续范围，但仍可能用于索引层过滤或覆盖查询。

3. !=、NOT IN、IS NULL 一定不走索引

不准确。是否选择索引取决于数据分布、命中比例、覆盖情况和优化器成本估算。

4. key 不为 NULL 就说明索引使用良好

不准确。可能扫描了大部分索引，也可能发生大量回表。

5. 全表扫描一定比索引慢

不准确。小表或需要读取大部分数据时，顺序全表扫描可能是成本更低的方案。

6. 使用 FORCE INDEX 就能解决问题

不准确。强制索引可能暂时改变执行计划，也可能在数据分布变化后变得更慢。应先找到优化器不选择索引的真实原因。

十五、参考资料

DISCUSSION

评论与讨论

留下你的想法