MySQL索引与执行计划

1. 这是什么

索引是数据库为了加速查询而建立的数据结构。
执行计划则反映了数据库如何实际执行一条 SQL。

一句话理解：

• 索引解决的是“怎么更快找到数据”
• 执行计划解决的是“数据库最终决定怎么找”

2. 为什么重要

大多数数据库性能问题，本质上都和这两件事有关：

• 索引设计不合理
• SQL 执行路径不合理

学会看执行计划，是 SQL 优化的起点。
因为你不能只靠“感觉”判断 SQL 快不快，而要看数据库真正怎么执行。

3. 先建立直觉：索引为什么会快

如果没有索引，数据库往往只能：

• 从头到尾扫描整张表

这就是全表扫描。
当数据量很小时，你可能感觉不到差异；但数据量一上来，代价就会迅速变大。

索引的本质是：

• 给数据建立更高效的查找路径

所以索引不是“额外挂件”，而是查询性能的核心结构之一。

4. 核心内容

4.1 主键索引与二级索引

在 InnoDB 里，最常见的理解方式是：

• 主键索引：按主键组织的数据主结构
• 二级索引：按其他字段建立的辅助查找结构

一个非常重要的直觉是：

• 通过二级索引找到记录位置后，可能还要再回到主键索引里取完整数据

这就是“回表”概念的基础。

4.2 什么是回表

当查询条件命中了二级索引，但查询结果需要的列不都在索引里时，MySQL 往往需要：

1. 先从二级索引找到主键值
2. 再回到主键索引中取整行数据

这个额外过程就是回表。

所以：

• 索引命中不等于一定足够快
• 是否回表，常常直接影响查询成本

4.3 什么是覆盖索引

如果一次查询所需的列，索引本身就已经全部包含，那么数据库就不需要回表。
这就叫覆盖索引。

它通常更高效，因为：

• 少了一次回主键索引取整行的过程

4.4 联合索引和最左前缀匹配

联合索引就是把多个列按顺序放在一个索引里，例如：

KEY idx_status_created_user (status, created_at, user_id)

最左前缀匹配的核心意思是：

• 索引能否高效使用，和查询条件是否从左往右连续匹配有关

例如上面的索引：

• status
• status, created_at
• status, created_at, user_id

这些通常都更容易有效利用索引。

但如果直接只查：

• user_id

那通常无法充分利用这个联合索引。

4.5 索引不是越多越好

索引虽然能提升查询，但也会带来代价：

• 写入更慢
• 更新更慢
• 删除更慢
• 占用更多存储空间

所以正确的问题不是：

• “能不能再加一个索引”

而是：

• “这个索引的收益是否大于维护成本”

4.6 EXPLAIN 最值得关注什么

看执行计划时，常见值得重点关注这些列：

• type
• key
• rows
• Extra

可以先这样理解：

type

表示访问类型，常用来粗略判断查询好坏。
通常越接近高效索引访问越好，越接近全表扫描越危险。

key

表示实际使用了哪个索引。

如果你明明建了索引，但这里没用上，就要继续分析原因。

rows

表示预估要扫描多少行。
这个值往往能帮助你判断：

• SQL 实际代价可能大不大

Extra

这里常会出现很多关键信息，例如：

• Using where
• Using index
• Using temporary
• Using filesort

其中：

• Using index 往往意味着覆盖索引收益
• Using temporary、Using filesort 常常值得重点关注

5. 学习重点

这一章最重要的是掌握这些判断：

• 索引为什么能让查询更快
• 联合索引为什么要考虑列顺序
• 回表和覆盖索引为什么影响性能
• EXPLAIN 不是装饰，而是优化依据
• 索引优化必须和查询条件一起看

6. 常见问题

6.1 随意加索引导致写入成本增加

只看查询收益，不看写入代价，是最常见误区之一。

6.2 不看执行计划就判断 SQL 快慢

这是“靠感觉优化”的典型表现。
真正的优化应该先看执行计划。

6.3 联合索引顺序设计不合理

联合索引列顺序错了，往往比“没建索引”更让人迷惑，因为它看起来像是“建了但没生效”。

7. 动手验证

这一节是可直接复制到 MySQL 环境中的实验脚本。
当前环境没有 mysql 客户端，所以我没有在本机直接执行，但脚本和观察点都已经整理成可操作形式。

7.1 建测试表

DROP TABLE IF EXISTS orders_demo;

CREATE TABLE orders_demo (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    remark VARCHAR(255) DEFAULT NULL,
    KEY idx_user_id (user_id),
    KEY idx_status_created_user (status, created_at, user_id)
);

7.2 插入测试数据

可以先插入少量样例，或者自己批量造数据。
最少先准备几十到几百行，效果会比 3 行数据更明显。

INSERT INTO orders_demo (user_id, status, amount, created_at, remark) VALUES
(1, 'PAID', 99.00, '2026-01-01 10:00:00', 'a'),
(1, 'PAID', 120.00, '2026-01-02 10:00:00', 'b'),
(2, 'CREATED', 80.00, '2026-01-03 10:00:00', 'c'),
(3, 'PAID', 60.00, '2026-01-04 10:00:00', 'd'),
(4, 'CANCELLED', 30.00, '2026-01-05 10:00:00', 'e');

7.3 观察普通单列索引命中

EXPLAIN SELECT * FROM orders_demo WHERE user_id = 1;

你应该重点看：

• key 是否使用了 idx_user_id
• type 是否优于全表扫描

7.4 观察联合索引命中

EXPLAIN
SELECT user_id, status, created_at
FROM orders_demo
WHERE status = 'PAID'
  AND created_at >= '2026-01-01 00:00:00';

你应该重点看：

• 是否使用 idx_status_created_user
• Extra 中是否可能出现 Using index

这条查询适合观察：

• 联合索引
• 最左前缀匹配
• 覆盖索引

7.5 观察回表

EXPLAIN
SELECT remark
FROM orders_demo
WHERE status = 'PAID'
  AND created_at >= '2026-01-01 00:00:00';

如果 remark 不在联合索引中，就更容易出现需要回表的情况。
你可以对比上一条查询的计划差异。

7.6 观察最左前缀失效场景

EXPLAIN
SELECT *
FROM orders_demo
WHERE user_id = 1
  AND created_at >= '2026-01-01 00:00:00';

这条查询和联合索引 idx_status_created_user(status, created_at, user_id) 的顺序并不完全匹配。
你可以观察：

• 为什么它未必能很好地使用这个联合索引

8. 你应该怎么验证结果

做完上面几条实验后，重点观察：

• key 实际使用了哪个索引
• rows 预估扫描行数是否明显变化
• Extra 是否出现 Using index
• 是否出现 Using temporary 或 Using filesort

9. 练习建议

下面这些练习做完，这一章会更扎实：

• 建一张测试表并设计几个不同索引
• 用 EXPLAIN 对比不同 SQL 的执行结果
• 观察“查询列不同”对回表和覆盖索引的影响
• 总结几类常见查询的索引设计方式

10. 自测问题

• 联合索引为什么要考虑最左前缀？
• 覆盖索引为什么通常更高效？
• 回表为什么会增加查询成本？
• 执行计划里最值得关注哪些信息？
• 为什么索引不是越多越好？

11. 自测核对要点

如果你的回答能覆盖下面这些点，说明这一章基本掌握到位了：

• 主键索引和二级索引的访问路径不同
• 联合索引的列顺序会直接影响命中效果
• 覆盖索引通常优于需要回表的查询
• EXPLAIN 的 type、key、rows、Extra 是关键观察点
• 索引设计必须同时考虑查询收益和写入维护成本