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

这里所说的“对索引列进行未建立函数索引的运算”，可以用下面的例子理解。

普通索引：

KEY idx_amount (amount)

保存并排序的是原始 amount：

10 → 20 → 30 → 40

查询条件却是：

WHERE amount + 1 = 31

MySQL 需要比较的是 amount + 1 的结果，而普通索引中没有保存这个结果。它通常无法直接在 idx_amount 中查找 31，而需要对候选记录计算表达式。

这个条件可以直接改写为：

WHERE amount = 30

如果业务确实需要长期按照某个表达式查询，MySQL 8.0.13 及以上版本支持函数索引：

CREATE INDEX idx_amount_plus_one
ON orders ((amount + 1));

之后与索引表达式匹配的查询才可以直接检索表达式结果：

WHERE amount + 1 = 31

也可以创建生成列，再为生成列建立索引：

ALTER TABLE orders
ADD COLUMN order_date DATE AS (DATE(created_at)),
ADD INDEX idx_order_date (order_date);

需要注意：

• 函数索引保存的是表达式计算结果，不是让任意函数都自动使用原列索引；
• 查询中的表达式需要与索引定义匹配；
• 函数索引会占用空间，也会增加写入和更新成本；
• 能通过等价范围条件改写的 SQL，通常优先改写，未必需要新增索引。

五、隐式类型转换

假设订单号是字符串：

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，也可能根据成本选择其他计划。

Index Merge 是什么

Index Merge 是 MySQL 在同一张表上分别扫描多个索引，再合并这些索引扫描结果的访问方式。

假设存在两个单列索引：

KEY idx_user_id (user_id),
KEY idx_status (status)

查询：

SELECT *
FROM orders
WHERE user_id = 100
   OR status = 1;

MySQL 可以分别得到：

idx_user_id 扫描结果：满足 user_id=100 的行 ID
idx_status  扫描结果：满足 status=1 的行 ID
                         ↓
                    合并并去重
                         ↓
                    读取完整记录

在 EXPLAIN 中通常表现为：

type: index_merge
key: idx_user_id,idx_status
Extra: Using union(...)

Index Merge 主要有三种合并方式：

Using sort_union(...) 是什么意思

先看一个典型查询。假设 key_col1 和 key_col2 分别建立了单列索引：

SELECT *
FROM orders
WHERE key_col1 < 10
   OR key_col2 < 20;

优化器可以分别扫描两个索引，但这两个范围扫描产生的行 ID 不能直接按 union 算法边扫描边归并。此时可能采用 sort-union：

扫描 key_col1 < 10，收集行 ID
                  \
                   → 汇总全部行 ID → 按行 ID 排序 → 去重 → 读取完整记录
                  /
扫描 key_col2 < 20，收集行 ID

以 InnoDB 二级索引为例，这里的“行 ID”通常可以理解为索引记录中携带的主键值。排序的对象是这些用于定位记录的标识，不是对最终查询结果执行 ORDER BY。

Using union(...) 与 Using sort_union(...) 的关键区别是：

• union 可以对多个索引扫描产生的有序行 ID 流直接归并；
• sort_union 需要先收集所有行 ID，再排序和去重，因此在完成收集与排序前不能开始返回记录。

所以 sort_union 通常比直接的 union 多出行 ID 缓冲、排序和去重成本。候选记录很多时，优化器也可能认为全表扫描更便宜，最终不选择 Index Merge。

需要特别区分：

• Using sort_union(...) 是同一张表内部多个索引扫描结果的合并算法；
• SQL 的 UNION 是多个查询结果集的集合运算；
• Using filesort 通常表示为了满足 ORDER BY 等要求进行额外排序。

这三者不是一回事。

Index Merge 从哪个版本开始支持

Index Merge 从 MySQL 5.0 开始引入。在此之前，一张表在一次查询访问中通常至多选择一个索引；MySQL 5.0 开始可以在同一张表上扫描多个索引并合并结果。

需要注意，Index Merge 本身是 MySQL 5.0 就有的访问方式，而用于显式影响它的 INDEX_MERGE、NO_INDEX_MERGE 优化器 Hint 是后续版本才增加的能力，不应把两者的版本混为一谈。

例如：

WHERE user_id = 100
  AND status = 1

优化器可能分别扫描两个索引后取交集。不过，很多情况下建立符合查询模式的联合索引：

KEY idx_user_status (user_id, status)

会比扫描并合并两个单列索引更直接。

因此 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 在整棵联合索引中并不是全局连续有序，通常无法直接利用该索引消除排序。

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

• 排序列顺序与联合索引不匹配；
• 联合索引中，排序列前面存在没有被固定为常量的索引列；
• 对排序列执行表达式或函数；
• 多张表连接后按非驱动表字段排序；
• 混合升降序与索引定义不匹配。

过滤条件如何影响索引顺序

假设存在联合索引：

KEY idx_abc (a, b, c)

下面的查询可以利用索引顺序：

SELECT *
FROM t
WHERE a = 1
  AND b = 2
ORDER BY c;

因为 a、b 都固定为常量，剩余记录天然按照 c 排序：

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

如果只固定 a：

SELECT *
FROM t
WHERE a = 1
ORDER BY c;

索引记录先按照 b 排序，再在相同 b 中按照 c 排序：

(a=1, b=1, c=1)
(a=1, b=1, c=9)
(a=1, b=2, c=2)
(a=1, b=2, c=8)

从整体看，c 的顺序是 1、9、2、8，并不是全局有序，因此通常仍需 filesort。

范围条件也可能产生类似效果：

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

扫描结果首先按不同的 b 值分组，每个 b 内部才按 c 排序，所以不能直接保证全局 c 有序。

为什么按非驱动表字段排序通常需要额外排序

假设查询：

SELECT o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-07-01'
ORDER BY u.name;

假设优化器选择 orders 作为第一个非 const 表，也就是先按订单时间读取订单，再逐条根据 user_id 查询用户：

按 orders.created_at 读取订单
            ↓
逐条查找对应 users
            ↓
得到的 u.name 没有全局顺序
            ↓
对连接结果执行 filesort

即使 users.name 上有索引，这个索引也只保证从 users 出发扫描时的姓名顺序，不能保证“先读取订单、再查用户”得到的连接结果按姓名有序。

如果优化器能够把 users 调整为驱动表，并且从 users.name 索引按顺序读取，再连接订单，就可能利用该顺序。但是否能够这样执行，还取决于过滤条件、连接关系、成本估算和 LIMIT 等因素。

索引方向与查询方向如何匹配

MySQL 8.0 支持降序索引，也支持反向扫描 B+ 树索引。

索引：

KEY idx_ab (a ASC, b ASC)

通常可以支持：

ORDER BY a ASC,  b ASC
ORDER BY a DESC, b DESC

第二条可以通过整体反向扫描完成。

但混合方向：

ORDER BY a ASC, b DESC

与 (a ASC, b ASC) 的排序关系不同。要直接使用索引顺序，应建立方向相匹配的索引：

KEY idx_ab_mixed (a ASC, b DESC)

这个索引也可以通过整体反向扫描支持：

ORDER BY a DESC, b ASC

所以更准确的规则是：

所有列同向时，升序索引可以正向或反向扫描；升降序混合时，索引各列的方向关系需要与查询一致，或者与查询整体相反。

MySQL 5.7 及更早版本虽然允许在索引定义中书写 DESC，但通常不会真正按降序存储键值。讨论索引方向时必须结合实际 MySQL 版本。

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

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

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

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

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

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

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

ANALYZE TABLE orders;

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

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

十二、如何用 EXPLAIN 判断

基础排查可以执行：

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 100;

重点观察：

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. system：表中只有一行

system 是 const 的特殊情况，表示优化器判断当前表只有一行。

访问过程可以理解为：

整张表只有一行
      ↓
直接读取这一行

它在普通 InnoDB 业务表的执行计划中并不常见。看到 system 时，不需要把它理解成某种特殊索引；它表达的是表规模已经确定为单行，因此没有查找范围可言。

3. 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 的核心不是“使用了等号”，而是：

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

4. 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 的区别是：

5. 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。

6. 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。

具体来说，范围优化器可以把：

WHERE id IN (10, 20, 30)

转换为同一个索引上的三个单点区间：

[10, 10]
∪
[20, 20]
∪
[30, 30]

执行时并不是从 10 一直扫描到 30，而是分别在同一棵 B+ 树中定位这些单点范围，再合并访问结果：

定位 id=10
定位 id=20
定位 id=30
     ↓
返回三个范围的记录

因此，SQL 语义上虽然是多个等值条件，物理访问方式仍属于“扫描一个或多个索引范围”，type 可以显示为 range。

这与 Index Merge 不同：

• IN (10,20,30) 通常是在同一个索引上构造多个范围；
• Index Merge 是扫描同一张表的多个索引，再合并结果。

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

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

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

7. ICP：在回表前用索引中的字段过滤

ICP 全称是 Index Condition Pushdown，中文通常称为“索引条件下推”。

仍然使用联合索引：

KEY idx_abc (a, b, c)

查询：

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

a=1 AND b>10 可以确定索引扫描范围，但 c=3 通常不能继续缩小这个连续范围。

没有 ICP 时，过程近似为：

扫描满足 a=1、b>10 的二级索引记录
                ↓
每一条都根据主键回表
                ↓
取得完整行后判断 c=3

开启 ICP 后，由于 c 也存储在联合索引中，存储引擎可以先检查索引记录中的 c：

扫描满足 a=1、b>10 的二级索引记录
                ↓
直接在索引记录中判断 c=3
        ├─ 不满足：跳过，不回表
        └─ 满足：根据主键回表

它的主要收益是：

减少不必要的聚簇索引回表，而不是进一步缩小最初的索引扫描范围。

在 EXPLAIN 的 Extra 中通常显示：

Using index condition

ICP 与覆盖索引也不同：

• ICP：仍可能需要回表，只是先在索引层过滤；
• 覆盖索引：查询所需数据全部在索引中，最终也不需要回表，Extra 常见 Using index。

只有能够利用当前索引记录判断的条件，才适合下推到存储引擎；依赖其他表、存储函数或无法从索引记录取得的数据，通常不能这样处理。

8. 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 快，因为二级索引通常更窄，缓存命中和读取的数据量更小；但它本质上仍是全索引扫描，数据量大时成本依然很高。

9. ALL：扫描整张表

ALL 表示全表扫描。

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

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

典型原因包括：

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

例如：

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

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

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

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

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

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

10. 常见类型对比

还应单独记住 JOIN 中的 eq_ref：

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

11. EXPLAIN 与 EXPLAIN ANALYZE 的区别

EXPLAIN 只展示优化器计划如何执行 SQL，并不会真正执行普通 SELECT：

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 100;

其中的 rows、filtered、cost 等主要来自统计信息和成本模型，是估算值。

EXPLAIN ANALYZE 会真正执行查询，并在执行计划中同时展示估算值和实际运行数据：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 100;

典型输出片段：

Index lookup on orders using idx_user_id
  (cost=10.2 rows=20)
  (actual time=0.08..0.15 rows=350 loops=1)

可以重点对比：

• cost：优化器估算成本；
• 前一个 rows：预计返回行数；
• actual time：实际首行时间和完成时间；
• 后一个 rows：每次循环实际返回行数；
• loops：该执行节点实际执行次数。

两者的核心区别：

EXPLAIN ANALYZE 能发现这类问题：

优化器预计返回 20 行
实际返回 350000 行

这种巨大偏差可能与统计信息、数据倾斜、条件相关性或估算模型有关。

使用时必须注意：

• 它会真实执行查询，慢查询会真实占用 CPU、I/O 和锁资源；
• 对复杂 SQL 应优先在测试或影子环境执行；
• 不要在生产高峰期随意分析可能扫描海量数据的语句；
• 对数据修改语句尤其谨慎，不能把它当成完全无副作用的静态分析工具。

12. 不要只根据 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 通常表示覆盖索引，不等于“使用索引定位”；
• 预计扫描行数少，也可能因估算偏差与实际执行差异很大。

十三、排查顺序

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

1. 确认慢 SQL、参数值和数据量，不能只拿脱离现场的 SQL 模板分析。
2. 查看表结构、索引顺序、字段类型、字符集和排序规则。
3. 使用 EXPLAIN 检查 key、type、rows、filtered 和 Extra。
4. 检查联合索引是否满足最左前缀，范围条件之后是否只剩过滤能力。
5. 检查索引列上是否存在函数、计算或隐式转换。
6. 估算查询命中比例和回表次数，判断优化器放弃索引是否合理。
7. 对比估算行数与实际行数，检查统计信息是否明显失真。
8. 在可控环境中使用 EXPLAIN ANALYZE 验证真实执行路径。
9. 调整 SQL 或索引后，在可控环境中使用 EXPLAIN ANALYZE 验证实际执行情况，并结合线上灰度后的查询耗时、扫描行数、回表量及资源指标持续观察，不能只看静态执行计划就宣布优化完成。只有涉及高并发、容量上限或锁竞争的场景，才需要进一步进行专项压测。

十四、常见错误结论

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

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

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

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

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

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

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

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

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

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

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

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

十五、参考资料

• MySQL 8.0 Reference Manual：How MySQL Uses Indexes
• MySQL 8.0 Reference Manual：Optimization and Indexes
• MySQL 8.0 Reference Manual：EXPLAIN Statement
• MySQL 8.0 Reference Manual：Index Merge Optimization
• MySQL 8.0 Reference Manual：Index Condition Pushdown Optimization
• MySQL 8.0 Reference Manual：ORDER BY Optimization
• MySQL 8.0 Reference Manual：CREATE INDEX Statement
• MySQL 8.0 Reference Manual：Optimizer Use of Generated Column Indexes