0
0
0

MySQL 索引面试总结：常见错误与标准答案

2026-10-27
技术
易混淆概念
文章摘要
|

本文整理了 MySQL 模拟面试中回答错误、没有回答出来以及理解不够清楚的知识点，重点包括 索引定位、ICP、覆盖索引、EXPLAIN、联合索引设计和慢 SQL 优化。

环境：MySQL 5.7 + InnoDB。

一、索引定位与 ICP 过滤

问题 1：LIKE 'zhang%' 属于索引定位还是 ICP 过滤？

错误回答：

不确定 LIKE 'zhang%' 是通过索引直接定位，还是只能通过 ICP 过滤。

标准答案：

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

普通 B+ 树索引按照索引字段的值排序。

alice
bob
jack
zhang       ← 范围开始
zhangsan
zhangwei
zhao        ← 范围结束

LIKE 'zhang%' 通常可以利用前缀 zhang 进行索引范围定位，从而缩小扫描范围。

EXPLAIN 通常显示：

type: range

但如果改成：

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

因为无法确定字符串开头，普通 B+ 树索引通常无法直接定位匹配范围。

MySQL 可能选择全表扫描，也可能选择全索引扫描。

总结：

  • LIKE 'zhang%'：通常可以使用索引范围定位。

  • LIKE '%zhang'：通常无法使用普通索引进行高效范围定位。

  • 索引定位与 ICP 是两个不同的概念。

注意：具体还会受到索引类型、字符集排序规则和优化器选择的影响。

问题 2：索引定位和 ICP 过滤有什么区别？

错误回答：

知道 ICP 可以减少回表，但不清楚索引定位和 ICP 分别发生在哪个阶段。

标准答案：

假设联合索引：

INDEX idx_user_status(user_id, status)

执行：

SELECT *
FROM orders
WHERE user_id > 100
AND status = 1;

执行过程：

  1. 索引定位： 利用 user_id > 100 确定索引扫描范围。

  2. ICP 过滤： 在扫描索引记录时，利用索引中的 status 判断是否等于 1。

  3. 回表： 对满足条件的索引记录，根据主键读取完整数据。

假设范围扫描涉及 1000 条索引记录，其中只有 100 条满足 status = 1。

执行方式

索引扫描

回表次数

不使用 ICP

1000 条

约 1000 次

使用 ICP

1000 条

约 100 次

以上是假设全部候选记录都需要获取完整行的简化示例。

核心区别：

概念

作用

索引定位

缩小需要扫描的索引范围

索引扫描

读取定位范围内的索引记录

ICP

在存储引擎层提前过滤，减少回表

回表

根据二级索引中的主键读取完整行

记忆：定位减少扫描范围，ICP 减少不必要的回表。

问题 3：联合索引遇到范围条件，后面的字段还能用于定位吗？

假设：

INDEX idx_user_status_time(user_id, status, create_time)

查询：

SELECT *
FROM orders
WHERE user_id = 100
AND status > 1
AND create_time = '2026-10-01';

错误回答：

只要遇到范围条件，后面的索引字段一定无法再用于定位，只能 ICP 过滤。

标准答案：

对于这条 SQL：

  • user_id = 100：等值定位。

  • status > 1：范围定位。

  • create_time = ...：通常不能继续缩小连续的索引扫描范围，但可能参与 ICP 过滤。

原因是联合索引按照：

user_id → status → create_time

排序。

create_time 只在前面的 user_id 和 status 都确定时，才能保证对应分组内有序。

当 status > 1 时，可能涉及多个 status 分组，不能直接根据 create_time 确定一个更小的连续范围。

但不能简单理解为所有范围条件都会阻断后续索引字段。

例如：

WHERE user_id = 100
AND status IN (1, 2)
AND create_time > '2026-10-01'

MySQL 可能将 IN 展开成多个等值范围，并进一步利用 create_time。

总结：判断后续字段能否参与范围定位，要分析联合索引的排序规则以及实际生成的索引范围。

二、全索引扫描与覆盖索引

问题 4：没有 WHERE 就一定不会走索引吗？

错误回答：

没有 WHERE 条件就不会走索引，只能全表扫描。

标准答案：

不一定。

假设存在：

INDEX idx_user_status(user_id, status)

查询：

SELECT user_id, status FROM orders;

MySQL 可能选择扫描整个二级索引。

EXPLAIN：

type: index
Extra: Using index

其中：

  • type = index：全索引扫描。

  • Using index：覆盖索引，不需要回表。

全索引扫描与全表扫描的区别：

类型

扫描对象

type = ALL

整个表（InnoDB 通常扫描聚簇索引）

type = index

整个选定索引

二级索引通常比完整行更紧凑，因此全索引扫描可能比全表扫描更便宜。

但如果需要扫描 1000 万条索引记录，依然可能很慢。

问题 5：type = index 为什么不一定快？

错误回答：

知道 type = index 是全索引扫描，但不清楚为什么使用索引性能还可能很差。

标准答案：

type = index 是全索引扫描，并不代表通过索引快速定位到少量记录。

例如：

type: index
rows: 10000000
Extra: Using index

即使使用覆盖索引，也可能需要扫描 1000 万条索引记录。

判断性能应综合考虑：

字段

判断内容

type

索引访问方式

key

实际使用的索引

rows

预计检查的行数

filtered

预计满足表条件的比例

Extra

是否覆盖索引、是否额外排序等

覆盖索引不代表扫描范围小，也不代表 SQL 一定快。

问题 6：使用了联合索引，就是覆盖索引吗？

错误回答：

只要使用 (user_id, create_time) 联合索引，就属于覆盖索引，不需要回表。

标准答案：

假设订单表包含：

id
user_id
status
create_time
amount

联合索引：

INDEX idx_user_time(user_id, create_time)

InnoDB 二级索引中包含：

user_id + create_time + id

查询一：

SELECT id, user_id, create_time
FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 10;

查询字段全部包含在索引中。

属于覆盖索引，不需要回表。

查询二：

SELECT *
FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 10;

因为 status、amount 等字段不在二级索引中，所以通常需要回表。

即使只查询 10 条记录，也不能称为覆盖索引。

覆盖索引的判断标准：查询需要的全部数据能否从使用的索引中获取。

三、EXPLAIN：Using where 与 Using filesort

问题 7：Using where 说明 SQL 很慢吗？

错误回答：

不清楚 Using where 如何判断 SQL 性能。

标准答案：

Using where 表示执行过程中需要进行 WHERE 条件过滤，并不代表 SQL 一定很慢。

例如：

type: ALL
rows: 10000000
Extra: Using where

全表扫描加上大量预计检查记录，需要重点关注。

而：

type: ref
rows: 10
Extra: Using where

虽然也存在条件过滤，但扫描数量很少，可能非常快。

判断性能的关键是访问方式、扫描的数据量和实际执行成本，而不是只看 Using where。

问题 8：Using filesort 是什么意思？为什么可能很慢？

错误回答：

知道 Using filesort 不一定使用磁盘，但不清楚什么时候出现，以及为什么会影响性能。

标准答案：

Using filesort 表示 MySQL 需要额外执行排序操作，而不能直接依靠选定索引的顺序返回结果。

例如：

SELECT *
FROM orders
WHERE user_id = 100
ORDER BY create_time DESC;

如果只有：

INDEX idx_user(user_id)

MySQL 能利用 user_id 定位用户订单，却不能保证订单按照 create_time 排列。

因此可能需要额外排序：

Extra: Using filesort

filesort 可能完全在内存中执行，也可能涉及磁盘临时文件。

是否慢主要取决于：

  1. 参与排序的记录数量。

  2. 每条记录需要保存的排序数据大小。

  3. 排序可使用的内存。

  4. 是否需要磁盘参与排序。

  5. 是否能利用 LIMIT 优化。

优化方式：

CREATE INDEX idx_user_time
ON orders(user_id, create_time);

此时：

SELECT *
FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 10;

MySQL 可以先定位到 user_id = 100 的索引范围，再按 create_time 反向扫描。

通常不需要 filesort，但 SELECT * 仍可能需要回表。

Using filesort 不一定慢，关键看排序代价。

四、联合索引设计

问题 9：一千万订单，只有两个联合索引名额，如何设计？

需求：

-- 1. 查询用户某个状态的订单
SELECT * FROM orders
WHERE user_id = 100 AND status = 1;

-- 2. 查询用户最近的订单
SELECT * FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 10;

-- 3. 查询某个状态下最近的订单
SELECT * FROM orders
WHERE status = 1
ORDER BY create_time DESC
LIMIT 10;

错误回答：

(user_id, status)
(status, user_id)

只考虑了 WHERE 条件，忽略了最近订单需要根据 create_time 排序。

标准答案：

索引设计必须综合考虑：

WHERE + ORDER BY + LIMIT

如果最近订单查询最重要，可以设计：

(user_id, create_time)
(status, create_time)

如果第一条用户状态组合查询更重要，可以考虑：

(user_id, status, create_time)
(status, create_time)

但需要注意：

WHERE user_id = 100
ORDER BY create_time DESC

对于 (user_id, status, create_time)，因为 status 没有固定，索引不能保证当前用户全部记录按 create_time 整体有序。

因此无法直接依赖这个联合索引消除该查询的排序。

面试重点：索引不只是为 WHERE 服务，也要考虑排序和 LIMIT。

五、索引失效与区分度

问题 10：INT 字段用字符串查询，会导致索引失效吗？

错误回答：

INT 字段如果使用字符串条件查询，一定会导致索引失效。

标准答案：

不一定。

例如：

-- user_id 为 INT
SELECT * FROM orders WHERE user_id = '100';

MySQL 通常可以将字符串常量转换为数字，仍然使用索引。

更需要警惕的是：

-- phone 为 VARCHAR
SELECT * FROM user WHERE phone = 13800138000;

字符串字段和数字比较时，可能涉及列值的隐式数值转换，影响索引使用。

最佳实践：查询参数类型与数据库字段类型保持一致。

问题 11：什么是索引区分度？为什么低区分度索引可能不生效？

错误回答：

不知道索引区分度的含义。

标准答案：

索引区分度通常可以用不同值数量与总记录数量的比例衡量。

区分度 = 不同值数量 / 总记录数量

例如 1000 万条订单，status 只有：

0 待支付
1 已支付
2 已取消

区分度：

3 / 10000000

如果 90% 的订单都是已支付：

SELECT *
FROM orders
WHERE status = 1;

该查询会命中大量记录。

如果走二级索引，还需要大量回表，优化器可能认为全表扫描成本更低。

因此，即使存在 status 索引，也不一定选择它。

索引区分度越高，通常越有利于减少扫描范围，但是否使用索引仍由查询成本决定。

问题 12：possible_keys 有值，但 key = NULL，说明什么？

错误回答：

能判断没有使用索引，但对优化器为什么不选择索引的原因理解不充分。

标准答案：

例如：

possible_keys: idx_status
key: NULL
type: ALL

说明：

  • possible_keys：可能适用的索引。

  • key：实际使用的索引。

  • key = NULL：没有选择索引作为访问路径。

  • type = ALL：全表扫描。

即使索引可能适用，优化器也可能因为预计扫描记录过多、回表成本高等原因，选择全表扫描。

六、深分页优化

问题 13：为什么 LIMIT 1000000,20 很慢？如何优化？

错误回答：

没有 WHERE，所以一定不走索引；ORDER BY DESC 一定需要额外排序；LIMIT 很大所以慢。

标准答案：

SELECT *
FROM orders
ORDER BY create_time DESC
LIMIT 1000000, 20;

MySQL 通常需要处理前面约 1000020 条有序记录，跳过前 100 万条，最终返回 20 条。

即使使用了 create_time 索引，也可能需要大量索引扫描和回表。

优化方案一：延迟关联。

SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    ORDER BY create_time DESC, id DESC
    LIMIT 1000000, 20
) t ON o.id = t.id
ORDER BY o.create_time DESC, o.id DESC;

推荐结合索引：

CREATE INDEX idx_time_id
ON orders(create_time, id);

InnoDB 二级索引包含主键，因此实际是否需要显式加入 id，应结合索引结构与执行计划判断。

这种方式可以减少深分页过程中的大量回表，但无法避免跳过前 100 万条索引记录。

优化方案二：游标分页。

假设上一页最后一条订单：

create_time = 2026-10-01 12:00:00
id = 1000

下一页：

SELECT *
FROM orders
WHERE create_time < '2026-10-01 12:00:00'
   OR (
       create_time = '2026-10-01 12:00:00'
       AND id < 1000
   )
ORDER BY create_time DESC, id DESC
LIMIT 20;

这种方式根据上一页最后一条记录的位置继续查询，避免使用很大的 OFFSET。

适用于连续翻页、无限滚动等场景。

七、EXPLAIN 常用字段速查

字段

含义

type = const

通过主键或唯一索引等值匹配，最多匹配一条

type = ref

非唯一索引或唯一索引前缀的等值匹配

type = range

索引范围扫描

type = index

全索引扫描

type = ALL

全表扫描

possible_keys

可能适用的索引

key

实际选择的索引

rows

预计检查的行数

filtered

预计通过表条件过滤的记录比例

Using index

覆盖索引

Using index condition

使用 ICP

Using where

条件过滤

Using filesort

额外排序

Using temporary

使用内部临时表

注意：

Using index 不等于索引快速定位；Using index condition 不等于覆盖索引。

八、面试总结

本次面试中，需要重点加强以下几个知识点：

知识点

存在的问题

索引定位与 ICP

混淆缩小扫描范围和提前过滤

联合索引范围定位

对范围条件后面的索引字段理解不够准确

全索引扫描

误认为没有 WHERE 就不能走索引

覆盖索引

误认为使用联合索引就不需要回表

Using where

不清楚如何结合执行计划判断性能

Using filesort

不熟悉出现条件和性能影响

联合索引设计

只考虑 WHERE，忽略 ORDER BY

隐式类型转换

误认为字段类型与条件类型不同就一定索引失效

索引区分度

不清楚定义以及优化器放弃索引的原因

深分页优化

不熟悉延迟关联与游标分页

SQL 优化的标准分析思路

拿到一条 SQL 后，按照以下步骤分析：

  1. WHERE： 哪些字段能够用于索引定位？需要扫描多少条索引记录？

  2. ICP： 哪些条件能够在存储引擎扫描索引时提前过滤？

  3. SELECT： 是否属于覆盖索引？是否需要回表？

  4. ORDER BY： 能否利用索引顺序？是否存在 Using filesort？

  5. LIMIT： 是否存在深分页问题？能否提前减少需要读取的数据？

  6. EXPLAIN： 综合 type、key、rows、Extra 等字段分析执行计划，并通过实际测试验证。

最终要记住：索引优化不是简单判断 SQL 有没有使用索引，而是判断它扫描了多少记录、过滤了多少记录、回表了多少次，以及是否需要额外排序。

支持与分享

如果这篇文章对你有帮助，欢迎分享给更多人或者给予支持！

评论