MySQL 面试题（二）：索引、回表与执行计划

2026-07-22 MySQL 1311 字 · 约 4 分钟
MySQL索引性能优化
所属系列：MySQL 面试题 · 第 2 / 4 篇

索引题的重点不是背“哪些情况会失效”，而是理解数据如何组织、优化器如何估算成本，以及如何用执行计划验证。

为什么 InnoDB 使用 B+ 树

B+ 树适合磁盘和页式存储：

  • 非叶子节点只保存键和子节点指针，单页可以容纳更多索引项；
  • 树高较低，查询需要的随机 I/O 次数少；
  • 叶子节点按键有序并形成链表，适合范围扫描；
  • 等值、范围、排序和最左前缀可以复用同一结构。

Hash 索引适合等值查找，但不支持范围和有序扫描。InnoDB 的自适应哈希索引由引擎自动管理，不能等同于手工创建的 B+ 树索引。

聚簇索引和二级索引

InnoDB 主键索引的叶子节点保存整行数据，因此也叫聚簇索引。

二级索引的叶子节点保存“索引列 + 主键值”。使用二级索引查询其他列时，需要先取得主键，再访问聚簇索引，这个过程叫回表。

CREATE INDEX idx_user_status ON orders(user_id, status);

SELECT amount
FROM orders
WHERE user_id = 10 AND status = 'paid';

如果索引没有包含 amount，通常需要回表。将 amount 加入索引可能形成覆盖索引，但会增加索引大小和写放大，需要根据查询频率权衡。

联合索引的最左前缀

索引 (a, b, c) 的排序方式是先按 a，再按 b，最后按 c，因此通常可以支持：

a
a, b
a, b, c
a 的等值条件 + b 的范围条件

只查询 b 或 c 通常不能直接利用这棵索引的有序定位能力。但优化器可能使用跳跃扫描等策略，所以最终仍应看执行计划，而不是机械判断“必然失效”。

范围条件后面的列一定失效吗

不一定。

对于 (user_id, created_at, status)：

WHERE user_id = 10
  AND created_at >= '2026-07-01'
  AND status = 'paid'

created_at 用于确定扫描范围，status 通常不能继续缩小 B+ 树扫描边界，但可能通过索引条件下推（ICP）在存储引擎层过滤，减少回表。

常见索引效果不佳的原因

隐式类型转换

字符串列使用数字条件可能发生转换：

-- phone 是 VARCHAR
WHERE phone = 13800138000

应使用：

WHERE phone = '13800138000'

对索引列做函数计算

WHERE DATE(created_at) = '2026-07-24'

可以改成范围：

WHERE created_at >= '2026-07-24 00:00:00'
  AND created_at <  '2026-07-25 00:00:00'

或者使用经过评估的函数索引/生成列。

前导模糊匹配

LIKE '%mysql' 无法从普通 B+ 树的开头定位。全文搜索、大规模关键词检索应考虑全文索引或专用搜索服务。

选择性太低

性别、布尔状态等列单独建索引通常过滤能力弱，但与高选择性字段组成联合索引后可能有价值。

如何设计联合索引

按真实查询设计，而不是“一列一个索引”：

  1. 统计高频查询和慢查询；
  2. 确定等值、范围、排序和返回列；
  3. 通常先放稳定的等值条件，再考虑范围与排序；
  4. 控制索引数量和宽度；
  5. 用生产规模数据验证执行计划。

示例：

SELECT id, created_at, amount
FROM orders
WHERE user_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

候选索引可以是：

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);

EXPLAIN 重点看什么

EXPLAIN FORMAT=TREE
SELECT ...

重点关注：

  • 实际使用的索引；
  • 预计扫描行数；
  • 访问方式是否为全表扫描、索引范围扫描或唯一查找；
  • 是否出现临时表、文件排序；
  • 过滤条件放在存储引擎还是 Server 层；
  • 估算行数是否明显偏离实际。

MySQL 8 可以直接运行：

EXPLAIN ANALYZE
SELECT ...

它会执行 SQL 并返回实际耗时、循环次数和实际行数。不要对具有副作用或生产风险的语句直接使用。

为什么优化器不使用索引

可能原因包括：

  • 全表扫描成本更低；
  • 统计信息过期；
  • 返回数据比例过高；
  • 索引无法满足排序，成本不占优；
  • 参数或数据分布倾斜；
  • 隐式转换或表达式改变了访问条件。

先执行：

ANALYZE TABLE orders;
EXPLAIN ANALYZE SELECT ...;

FORCE INDEX 应是经过验证的最后手段，因为数据分布变化后提示可能成为负优化。

深分页如何优化

SELECT *
FROM orders
ORDER BY id
LIMIT 100000, 20;

数据库仍要扫描并丢弃前 100000 行。已知上一页最后一个 ID 时，优先使用游标分页：

SELECT *
FROM orders
WHERE id > ?
ORDER BY id
LIMIT 20;

若必须跳页，可先通过覆盖索引取得主键，再回表连接，但仍要评估扫描成本。

面试速答

索引越多越好吗

不是。索引会占空间，并增加插入、更新、删除、页分裂、缓存和备份成本。

主键为什么不宜过长

所有二级索引叶子节点都会保存主键，主键越长，二级索引越大。

什么是索引下推

索引条件下推（ICP）让存储引擎在遍历二级索引时先判断可由索引列完成的条件，再决定是否回表。

下一篇将介绍事务、MVCC、隔离级别和各种锁的边界。

相关文章