从锁、MVCC到查询优化器：一篇文章串起 MySQL SQL 执行与性能优化

很多 SQL 问题看起来彼此独立：

• 为什么一条 UPDATE 会卡住？
• 为什么普通 SELECT 没有阻塞写操作？
• 为什么明明建了索引，MySQL 却不走？
• 为什么同一条 SQL 在不同数据量下执行计划不同？
• 为什么 EXPLAIN 看起来正常，SQL 仍然很慢？
• 为什么加内存有时能解决问题，有时却完全没用？

如果只记零散结论，很容易陷入“背八股”。真正有用的方式，是把数据库运行过程串起来看。

这几篇文章实际上可以归纳成一条非常清晰的主线：

锁负责解决并发修改的冲突，MVCC 负责提升并发读取能力，查询优化器负责选择执行路径，索引与缓冲池决定数据访问成本，而慢查询、EXPLAIN 等工具负责告诉我们到底慢在哪里。

本文把这几部分重新组织成一套完整的 MySQL / InnoDB 底层知识框架。

一、先建立整体模型：一条 SQL 到底经历了什么

从宏观上看，一条 SQL 的执行可以拆成几个层次：

SQL
 ↓
语法分析 / 语义检查
 ↓
逻辑查询优化
 ↓
物理查询优化
 ↓
生成执行计划
 ↓
执行器执行
 ↓
访问索引 / 数据页
 ↓
Buffer Pool / Disk I/O
 ↓
事务、锁、MVCC 保证并发一致性

因此 SQL 性能问题大致也可以归为四类：

层次	核心问题
并发控制	是否发生锁等待、死锁、事务冲突
数据可见性	当前事务到底应该看到哪个版本的数据
查询计划	优化器选择了怎样的扫描、连接和排序方式
数据访问	是否走索引、是否回表、是否发生大量磁盘 I/O

理解这四层之后，很多数据库问题就不再是孤立知识点。

---

二、锁：数据库为什么需要“限制并发”

数据库最怕的不是并发，而是没有控制的并发。

订单、库存、余额等数据如果被多个事务同时修改，就可能出现丢失更新、数据覆盖、状态不一致等问题。因此数据库需要通过锁来控制资源访问。

锁的本质可以理解为：

在某个时间范围内，对某个数据资源的访问权限进行约束。

2.1 按锁粒度划分

常见粒度包括：

• 行锁
• 页锁
• 表锁

粒度越小：

• 并发能力越高；
• 锁管理成本越高；
• 同时持有的锁数量也可能越多。

粒度越大：

• 锁管理成本越低；
• 但锁冲突概率更高；
• 并发能力下降。

可以粗略理解为：

锁粒度：

行锁 < 页锁 < 表锁

并发能力：

行锁 > 页锁 > 表锁

锁管理成本：

行锁 > 页锁 > 表锁

InnoDB 的高并发能力，很重要的一点就是它能够进行细粒度的行级锁定。

---

三、共享锁、排他锁与意向锁

从数据库管理角度看，最重要的是：

• 共享锁（Shared Lock，S Lock）
• 排他锁（Exclusive Lock，X Lock）

3.1 共享锁

共享锁主要用于读取。

多个事务可以同时持有同一份数据的共享锁，因此：

S + S：可以共存

但是持有共享锁的数据不能被其他事务随意修改。

文章中使用过类似：

SELECT *
FROM product_comment
WHERE user_id = 912178
LOCK IN SHARE MODE;

它表达的是：

我要读取这批数据，并且在事务结束前，希望这些记录不要被其他事务修改。

---

3.2 排他锁

排他锁用于修改。

当某事务对一条记录持有排他锁之后，其他事务无法再对该记录获得冲突的锁。

典型例子：

SELECT *
FROM product_comment
WHERE user_id = 912178
FOR UPDATE;

以及：

UPDATE product_comment
SET product_id = 10002
WHERE user_id = 912178;

INSERT、UPDATE、DELETE 这类写操作，本质上都需要通过排他性控制保证写入正确性。

---

3.3 为什么还需要意向锁

假设事务 T1 已经锁住了表中的某一行。

此时事务 T2 想给整个表加排他锁。

如果没有额外机制，数据库就需要检查：

第 1 行有没有锁？
第 2 行有没有锁？
第 3 行有没有锁？
……

这显然很低效。

于是就有了意向锁。

意向锁可以理解成：

在表级别放一个“里面已经有人锁了部分记录”的标记。

常见的有：

• 意向共享锁 IS
• 意向排他锁 IX

例如某个事务准备对表中的部分记录获得排他锁，那么数据库可以先在表上留下 IX。

这样另一个事务如果想锁整张表，只需要检查表级锁状态，而不需要逐行扫描。

可以把它理解成：

行锁：真正锁房间
意向锁：在大门口挂一块“里面有人”的牌子

---

四、乐观锁与悲观锁不是具体锁，而是并发控制思想

这是一个非常容易混淆的地方。

乐观锁和悲观锁本身不是某一种数据库锁类型，而是两种并发控制思想。

4.1 悲观锁

悲观锁假设：

并发冲突很可能发生。

因此在真正操作数据之前，先把资源锁住。

典型方式：

SELECT *
FROM account
WHERE id = 1
FOR UPDATE;

应用场景通常是：

• 写操作多；
• 冲突概率高；
• 库存扣减；
• 金额计算；
• 状态流转。

悲观锁的优点是控制直接，缺点也明显：

• 会发生等待；
• 降低并发；
• 可能出现死锁。

---

4.2 乐观锁

乐观锁假设：

大多数时候并不会发生冲突。

因此读取时不锁数据，真正提交修改的时候再检查数据有没有发生变化。

最常见的方式是增加一个 version 字段：

SELECT id, balance, version
FROM account
WHERE id = 1;

假设读到：

balance = 1000
version = 7

更新时执行：

UPDATE account
SET balance = 900,
    version = version + 1
WHERE id = 1
  AND version = 7;

如果影响行数为 0，说明：

version 已经不是 7
→ 数据被其他事务修改过
→ 当前修改失败

除了版本号，也可以使用时间戳实现类似机制。

---

五、死锁：真正的问题不是“有等待”，而是“循环等待”

这里需要特别区分两个概念：

锁等待 ≠ 死锁

如果事务 A 持有资源，事务 B 等待 A 释放，这只是普通锁等待。

真正的死锁必须出现类似关系：

事务 A 持有资源 X，等待资源 Y
事务 B 持有资源 Y，等待资源 X

形成：

A → 等 B
↑     ↓
└─────┘

这才是死锁。

原文章中的共享锁示例在评论区也被读者指出：如果只是一个事务等待另一个事务释放锁，并没有形成相互等待，那么更准确地说只是锁等待；必须进一步形成循环依赖才构成死锁。

5.1 降低死锁概率的基本原则

统一资源访问顺序

例如所有业务都按照：

账户 A → 账户 B

而不要一部分：

A → B

另一部分：

B → A

缩短事务

事务越长，锁持有时间越长。

因此尽量不要在事务中执行：

• RPC
• HTTP 请求
• 大量计算
• 用户交互
• 不必要的批量查询

一次事务不要锁太多资源

锁的资源越多，形成依赖环的概率越高。

---

六、MVCC：为什么数据库可以做到“读不阻塞写”

如果所有读取都通过锁完成，那么高并发数据库很快就会变成：

读等写
写等读
写等写

这就是 MVCC 要解决的问题。

MVCC 全称：

Multi-Version Concurrency Control

即：

多版本并发控制。

核心思想非常简单：

不要只保存“现在这一个版本”，还要能够找到数据过去的版本。

这样事务在读取时，不一定要去读最新数据，而可以读取一个符合自己事务可见性规则的历史版本。

于是：

写事务修改最新版本
读事务读取自己的快照版本

很多情况下双方就不必互相阻塞。

---

七、快照读与当前读

理解 MVCC 必须先区分两个概念。

7.1 快照读

普通、不加锁的 SELECT 一般属于快照读。

SELECT *
FROM player
WHERE id = 100;

它读取的是：

当前事务按照 MVCC 可见性规则能够看到的数据版本。

它未必等于物理意义上的最新版本。

---

7.2 当前读

当前读要求读取最新数据，并参与锁控制。

例如：

SELECT *
FROM player
WHERE id = 100
FOR UPDATE;

以及：

INSERT ...
UPDATE ...
DELETE ...

这些操作都需要基于当前最新状态执行。

因此可以这样记：

普通 SELECT
    ↓
快照读
    ↓
MVCC

SELECT ... FOR UPDATE / DML
    ↓
当前读
    ↓
锁

这也是理解“为什么同一个事务里普通 SELECT 和 FOR UPDATE 行为不一样”的关键。

---

八、InnoDB 的 MVCC：Undo Log + Read View

文章中把 InnoDB MVCC 的核心总结为：

MVCC = Undo Log + Read View

这是一个非常好用的理解模型。

8.1 行记录中的隐藏信息

InnoDB 行记录中会维护与 MVCC 有关的信息。

文章重点介绍了：

• db_trx_id
• db_roll_ptr
• 隐藏行 ID

其中：

db_trx_id

记录最后一次插入或更新该行的事务 ID。

db_roll_ptr

指向 Undo Log 中更早的数据版本。

于是同一条记录就可以形成一条历史版本链：

最新版本
   |
   v
历史版本 1
   |
   v
历史版本 2
   |
   v
历史版本 3

这就是所谓的多版本。

---

九、Undo Log：历史版本从哪里来

假设某条记录经历：

balance = 1000
balance = 900
balance = 800

数据库不能简单地覆盖之后就忘掉旧值。

为了事务回滚以及 MVCC，一些历史信息会被保存在 Undo Log 中。

通过回滚指针，可以沿着版本链向前查找：

当前版本
  ↓
Undo Record
  ↓
更老的 Undo Record
  ↓
……

因此：

Undo Log 负责提供“过去的数据版本”。

但是问题又来了：

历史版本这么多，我到底该看哪个？

这就需要 Read View。

---

十、Read View：决定“哪个版本对我可见”

Read View 的职责不是保存数据，而是：

决定一个数据版本对于当前事务是否可见。

文章使用一个简化模型描述 Read View，其中包含：

• 当前活跃事务集合；
• 活跃事务的上下边界；
• 创建 Read View 的事务 ID。

在读取数据时，大致流程可以理解为：

读取当前记录
      ↓
检查该版本的事务 ID
      ↓
符合当前 Read View？
   /             \
 是               否
 |                |
返回           沿 Undo Log
               找更老版本
                   ↓
            再做可见性判断

最终找到当前事务应该看到的版本。

---

十一、Read Committed 与 Repeatable Read 的核心差别

文章对两个隔离级别的差异给出了一个非常重要的观察。

11.1 Read Committed

在读已提交（RC）下：

一个事务中的多次快照读取可以获得新的 Read View。

因此：

第一次 SELECT
↓
别人提交 UPDATE
↓
第二次 SELECT
↓
可能看到新值

所以会出现不可重复读。

---

11.2 Repeatable Read

在可重复读（RR）下：

事务中的一致性读取会复用事务快照，从而让多次快照读看到一致的数据视图。

例如：

T1 第一次 SELECT：balance = 1000

T2 UPDATE balance = 900
T2 COMMIT

T1 第二次普通 SELECT：仍可能看到 balance = 1000

从应用层面看，这就是“可重复读”。

需要注意的是：

快照读和当前读是两套不同机制。

普通 SELECT 走 MVCC 快照，而 SELECT ... FOR UPDATE 这类当前读需要锁。

---

十二、幻读、Gap Lock 与 Next-Key Lock

文章还进一步介绍了 InnoDB 的三类记录范围锁概念：

• Record Lock
• Gap Lock
• Next-Key Lock

12.1 Record Lock

只锁已经存在的一条索引记录。

[10]

---

12.2 Gap Lock

锁的是索引记录之间的间隙。

例如：

10      20
 |------|
   GAP

它关注的是：

不允许其他事务在这个范围内插入新的索引记录。

---

12.3 Next-Key Lock

可以理解为：

Next-Key Lock = Record Lock + Gap Lock

即：

• 锁住已有记录；
• 同时锁住相邻范围。

它的意义就在于范围当前读场景下，可以限制其他事务插入新的满足条件的数据。

文章将其与 MVCC 一起用于解释 InnoDB 在可重复读隔离级别下如何处理幻读问题。

可以这样理解：

快照读的一致性
    ↓
MVCC / Read View

当前读的范围稳定性
    ↓
Record / Gap / Next-Key Lock

---

十三、为什么 MVCC 能提升并发

如果没有 MVCC：

读数据 → 加锁
写数据 → 等读锁释放

有了 MVCC：

读事务
 ↓
历史快照

写事务
 ↓
最新版本

于是可以做到：

• 读不必总是阻塞写；
• 写不必总是阻塞普通读取；
• 大量 SELECT 不必都转化为锁竞争；
• 降低部分死锁发生概率；
• 提高数据库吞吐量。

这也是为什么现代关系型数据库普遍采用某种形式的多版本并发控制。

---

十四、查询优化器：SQL 写的是“我要什么”，不是“怎么做”

理解完并发控制之后，再来看 SQL 执行。

SQL 是声明式语言。

例如：

SELECT u.name, o.amount
FROM user u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

我们只描述了：

我要哪些数据。

却没有指定：

• 先查 user 还是 orders；
• 用哪个索引；
• 是全表扫描还是范围扫描；
• 两张表怎么 JOIN；
• 是否需要临时表；
• 是否需要排序。

这些事情需要查询优化器决定。

---

十五、一条 SQL 的优化过程

可以概括为：

SQL
 ↓
Parser
 ↓
语法树
 ↓
逻辑优化
 ↓
候选执行方案
 ↓
物理优化
 ↓
执行计划
 ↓
Executor

15.1 逻辑查询优化

逻辑优化主要做的是：

等价重写。

也就是说，不改变 SQL 的语义，但换一种更容易执行的形式。

例如：

• 谓词简化；
• 条件重写；
• 子查询优化；
• 连接简化；
• 外连接消除；
• 调整等价条件表达式。

它的数学基础可以理解为关系代数。

---

15.2 物理查询优化

逻辑上确定“要做什么”以后，还需要决定：

具体用什么算法做。

例如 JOIN 可能存在多种物理实现方式。

同样一个查询，也可能存在：

全表扫描
索引扫描
范围扫描
不同索引
不同 JOIN 顺序
不同 JOIN 算法

于是优化器要在这些候选计划中做选择。

---

十六、RBO 与 CBO

文章介绍了两类典型优化思想。

16.1 RBO：基于规则

RBO：

Rule-Based Optimizer

核心思想：

使用预定义规则和经验选择执行路径。

可以类比为：

老司机凭经验选路。

特点：

• 规则稳定；
• 行为相对容易预测；
• 不需要复杂代价计算；
• 但难以适应不同数据分布。

---

16.2 CBO：基于代价

CBO：

Cost-Based Optimizer

它会估算不同执行方案的 Cost：

Plan A → Cost 100
Plan B → Cost 20
Plan C → Cost 56

最终选择估算代价较小的方案。

它更像导航软件：

距离
拥堵
道路等级
历史数据
实时情况

综合计算后选择路线。

---

十七、CBO 的 Cost 到底是什么

文章用一个经典模型解释：

Cost ≈ I/O Cost + CPU Cost

进一步还可能考虑：

总代价 =
I/O
+ CPU
+ Memory
+ Remote
+ ...

其中最值得关注的是 I/O 和 CPU。

17.1 I/O Cost

例如：

• 读取多少数据页；
• 读取多少索引页；
• 页面是否已经在内存；
• 是否需要磁盘随机读取。

17.2 CPU Cost

例如：

• 比较多少个 key；
• 判断多少行；
• 执行多少表达式；
• 做多少排序和计算。

因此一个执行计划的优劣，不是简单的：

“走索引 = 一定快”

而是：

走这个索引的总成本
vs
直接扫表的总成本

---

十八、为什么“有索引却不走索引”

这是理解 CBO 后最自然的结论。

假设一张表 100 万行，某字段只有：

男
女

两个值。

即使这个字段有索引：

CREATE INDEX idx_gender ON user(gender);

查询：

SELECT *
FROM user
WHERE gender = '男';

如果会命中 50 万行：

索引扫描
+
大量回表

可能比：

直接顺序扫描大量数据页

还贵。

于是优化器完全可能不走这个索引。

所以真正的问题不是：

为什么 MySQL 不听我的？

而是：

优化器根据统计信息，认为哪条路径成本更低？

---

十九、CBO 为什么也可能选错

CBO 并不是“真理机器”。

它依赖很多输入：

• 表统计信息；
• 索引基数；
• 数据分布；
• 优化器参数；
• I/O 成本参数；
• 内存状态；
• SQL 结构。

只要估算与真实数据偏差很大，就可能出现：

估算最优
≠
真实执行最优

而且优化器本身也需要消耗 CPU。

对于复杂 SQL，理论上的 JOIN 组合可能非常多，因此优化器还必须进行搜索空间剪枝。

所以优化器真正追求的通常不是数学意义上的绝对最优，而是：

在有限优化时间内，找到足够好的执行计划。

---

二十、索引：B+ Tree 为什么成为 InnoDB 的主力结构

文章比较了 B+ Tree 和 Hash。

20.1 B+ Tree 适合

• 等值查询；
• 范围查询；
• 排序；
• 前缀匹配；
• 联合索引。

例如：

WHERE id = 100

以及：

WHERE id BETWEEN 100 AND 1000

都很适合 B+ Tree。

---

20.2 Hash 更擅长等值查找

Hash 的优势在于：

key → hash → bucket

理想情况下定位速度非常快。

但是 Hash 天然不适合：

• 范围查询；
• 顺序扫描；
• ORDER BY；
• 联合索引前缀查询。

因为 Hash 之后的数据不再保持原有顺序关系。

---

二十一、自适应 Hash 索引：给 B+ Tree 再加一层“快捷入口”

InnoDB 的一个有趣机制是 Adaptive Hash Index。

可以把它理解成：

热点 B+ Tree 访问路径的 Hash 快捷入口。

文章中的思路是：

普通访问：

查询条件
 ↓
B+ Tree
 ↓
叶子节点
 ↓
数据页

热点访问可能建立：

查询条件
 ↓
Adaptive Hash
 ↓
快速定位热点页

它不是让开发者手工创建一个新的 Hash 索引，而是由 InnoDB 根据访问模式自行维护。

因此很适合用一句话记忆：

Adaptive Hash Index 是“索引的索引”。

---

二十二、联合索引与最左前缀

假设创建：

INDEX idx_xyz(x, y, z)

B+ Tree 中的排序逻辑可以理解为：

先按 x 排
x 相同时按 y 排
x、y 相同时再按 z 排

因此：

WHERE x = 1

可以使用。

WHERE x = 1 AND y = 2

也可以继续缩小范围。

WHERE x = 1 AND y = 2 AND z = 3

可以完整利用联合索引的排序层级。

但是：

WHERE y = 2 AND z = 3

缺少最左侧的 x，无法直接按照这棵树的首层排序快速定位。

---

22.1 WHERE 中字段书写顺序不是关键

下面两个条件逻辑等价：

WHERE x = 1 AND y = 2

WHERE y = 2 AND x = 1

查询优化器会进行逻辑重写。

因此“最左”指的是：

联合索引的列定义顺序。

不是 SQL 文本中 WHERE 条件出现的顺序。

---

22.2 遇到范围条件之后怎么办

按照文章对联合索引的简化解释：

INDEX(x, y, z)

WHERE x = 1
  AND y > 10
  AND z = 3

x 可以用于等值定位，y 进入范围查找；范围条件之后，z 通常不能继续用于进一步缩小这次 B+ Tree 的连续搜索区间。

因此设计联合索引时一个常见思路是：

高频等值条件
→ 放前面

范围条件
→ 通常放在后面

最终是否真正有效，仍然应该通过 EXPLAIN 和真实数据验证，而不是只背规则。

---

二十三、Buffer Pool：数据库为什么非常吃内存

InnoDB 的数据最终存储在磁盘。

但是：

内存访问速度
>>
磁盘随机 I/O

如果每次查询都直接访问磁盘，数据库性能会非常差。

因此 InnoDB 使用 Buffer Pool 缓存大量热点数据。

文章中列出的 Buffer Pool 内容包括：

• 数据页；
• 索引页；
• 锁相关信息；
• 自适应 Hash；
• 数据字典相关信息；
• 其他内部结构。

核心目标只有一个：

尽量把高频磁盘访问变成内存访问。

---

二十四、Buffer Pool 的两个关键词：局部性与热数据

24.1 热数据

内存不可能无限大。

假设：

磁盘数据：500 GB
Buffer Pool：32 GB

那么数据库必须决定：

哪些页面更值得留在内存？

答案自然是：

访问频率更高的数据

---

24.2 局部性与预读

程序访问数据往往具有局部性：

访问某个位置之后，很可能马上访问附近位置。

因此数据库可以进行预读：

你读 page 100
↓
系统判断你很可能还会读附近 page
↓
提前加载

从而减少未来磁盘 I/O。

---

二十五、Buffer Pool 和 Query Cache 不是一回事

这是非常经典的混淆。

Buffer Pool 缓存

缓存的是：

数据页 / 索引页等数据库页面

目标是：

减少磁盘 I/O

---

Query Cache 缓存

文章介绍的旧版 Query Cache 缓存的是：

SQL 查询结果

即：

SQL
↓
之前执行过完全匹配的查询？
↓
直接返回结果

但是它存在明显问题：

• 命中条件苛刻；
• 表数据变化后缓存容易失效；
• 维护成本高。

文章也明确指出，MySQL 8.0 已经移除了这一查询缓存机制。

因此：

Buffer Pool ≠ Query Cache

二者虽然都叫“缓存”，但工作层次完全不同。

---

二十六、SQL 慢了以后，正确姿势不是立刻“加索引”

性能优化最怕的一件事就是：

看到慢 SQL
↓
猜一个索引
↓
上线
↓
祈祷

更可靠的方式应该是：

观察
↓
定位
↓
分析
↓
修改
↓
验证

文章给出的性能分析主线非常值得保留：

服务器状态
↓
慢查询日志
↓
EXPLAIN
↓
更细粒度执行分析
↓
索引 / SQL / 参数 / 架构优化

---

二十七、第一步：先判断到底是什么慢

SQL 响应时间长，大致可能来自：

执行时间
+
等待时间

执行时间长可能是：

• 扫描数据过多；
• JOIN 成本高；
• 排序量大；
• 聚合量大；
• 没有合适索引；
• 临时表开销大。

等待时间长可能是：

• 锁等待；
• I/O 等待；
• 资源竞争；
• Buffer Pool 压力；
• 并发过高。

所以看到：

SQL took 10s

并不能直接推出：

SQL 本身计算了 10s

它也可能有 9 秒都在等。

---

二十八、慢查询日志：先把真正慢的 SQL 抓出来

文章使用：

SHOW VARIABLES LIKE '%slow_query_log%';

查看慢查询日志。

开启：

SET GLOBAL slow_query_log = 'ON';

设置阈值：

SET GLOBAL long_query_time = 3;

当 SQL 执行时间超过阈值后，会被记录。

这一步解决的是：

到底哪些 SQL 慢？

而不是凭感觉翻项目代码，把所有 SQL 全部优化一遍。

后者通常成本非常高，而且很容易把时间花在根本不重要的 SQL 上。

---

二十九、EXPLAIN：优化器最终到底选了什么计划

定位慢 SQL 后，下一步通常是：

EXPLAIN
SELECT ...

EXPLAIN 的核心价值是：

把优化器选择的执行计划展示出来。

常见关注字段包括：

• table
• type
• possible_keys
• key
• key_len
• ref
• rows
• filtered
• Extra

---

三十、EXPLAIN 的 type 怎么理解

文章列出的常见访问类型包括：

ALL
index
range
index_merge
ref
eq_ref
const
system

可以做一个粗粒度理解：

ALL

全表扫描。

整张表逐行看

通常需要重点关注。

---

index

全索引扫描。

虽然读取的是索引，但仍然可能扫描大量索引记录。

---

range

索引范围扫描。

例如：

WHERE id > 1000

---

ref

通过非唯一索引查找一个或多个匹配值。

例如：

WHERE user_id = 100

如果 user_id 是普通索引。

---

eq_ref

JOIN 中通过主键或唯一索引查找唯一记录。

---

const

通过主键或唯一索引与常量进行比较，并且最多命中一条记录。

例如：

WHERE id = 100

---

需要注意的是，这些 type 更适合用于理解访问方式，而不是简单当成一个绝对性能排行榜。

真正分析 SQL 时，还要结合：

rows
filtered
key
Extra
实际执行时间
数据分布

一起判断。

---

三十一、Using Index：为什么覆盖索引很重要

如果 EXPLAIN 的 Extra 中出现：

Using index

通常说明查询可以直接从索引中获得需要的列。

例如建立：

INDEX idx_user_text(user_id, comment_text)

查询：

SELECT user_id, comment_text
FROM product_comment
WHERE user_id = 100;

如果所有需要的数据都能从索引叶子节点获得，就可能不必回主键索引再次取整行。

普通二级索引查询可能是：

二级索引
 ↓
得到主键
 ↓
聚簇索引
 ↓
读取完整行

覆盖索引则可能变成：

二级索引
 ↓
直接返回

少一次回表，通常意味着更少的随机访问。

---

三十二、SHOW PROFILE：文章中的细粒度分析工具

文章还介绍了：

SET profiling = 'ON';

然后：

SHOW PROFILES;
SHOW PROFILE;

用于查看 SQL 各阶段耗时，例如：

starting
checking permissions
Opening tables
optimizing
statistics
executing
Sending data
...

这能帮助判断：

CPU 花在哪里？
I/O 花在哪里？
等待花在哪里？

不过原文章已经明确提醒：

SHOW PROFILE 将被弃用。

所以在现代 MySQL 环境中，更重要的是理解它背后的分析思想：

不要只看总耗时，要拆分 SQL 各阶段到底把时间花在了哪里。

具体使用哪一种工具，应以当前 MySQL 版本支持的性能诊断能力为准。

---

三十三、把五篇文章串起来：数据库性能其实是一个完整系统

现在把前面的知识重新连起来。

假设线上有一条 SQL 很慢：

UPDATE account
SET balance = balance - 100
WHERE user_id = 10001;

第一步不能直接说：

加索引！

而应该逐层判断。

第 1 层：是不是锁等待

另一个事务是否已经锁住 user_id = 10001？

如果是，那么问题属于：

事务 / 锁

而不一定是查询计划。

---

第 2 层：SQL 到底扫描了多少数据

用 EXPLAIN 看：

type
key
rows
filtered
Extra

如果扫描几十万行，才进入索引或 SQL 结构优化。

---

第 3 层：优化器为什么这么选

如果明明有索引却不用，需要进一步思考：

• 索引选择性是否太低；
• 统计信息是否导致错误估算；
• 回表成本是否太高；
• 查询返回比例是否过大；
• 联合索引顺序是否不合理。

---

第 4 层：是不是 I/O

如果执行计划看起来合理，但仍然慢：

Buffer Pool 命中率？
磁盘 I/O？
数据是否远大于内存？
是否突然扫描冷数据？

---

第 5 层：是不是已经到了单库极限

如果：

• SQL 已优化；
• 索引合理；
• 参数正常；
• 锁竞争已控制；
• 硬件没有明显问题；

仍然到达瓶颈，就需要进入架构层：

读写分离
分库
分表
缓存
拆分热点

---

三十四、一套更实用的 SQL 排障顺序

线上遇到 SQL 慢，可以按照下面顺序检查。

Step 1：确认现象

先回答：

一直慢？
偶发慢？
高峰期慢？
只有某些参数慢？

---

Step 2：区分等待和执行

判断：

Lock Wait？
I/O Wait？
CPU？
真正执行耗时？

---

Step 3：定位慢 SQL

使用：

慢查询日志
监控平台
数据库性能视图

---

Step 4：看 EXPLAIN

重点：

key
type
rows
filtered
Extra

---

Step 5：看索引设计

检查：

• 是否满足最左前缀；
• 是否存在高选择性索引；
• 是否可以构造覆盖索引；
• 是否存在大量回表；
• 是否出现索引扫描量过大。

---

Step 6：看事务和锁

检查：

• 是否有长事务；
• 是否有锁等待；
• 是否有死锁；
• 是否在事务里执行外部 RPC；
• 是否访问资源顺序不一致。

---

Step 7：看 Buffer Pool 和 I/O

检查：

• 热数据是否能驻留内存；
• 是否存在大量冷数据读取；
• 是否大量随机 I/O；
• 是否出现周期性缓存失效。

---

Step 8：最后才考虑架构扩展

例如：

缓存
读写分离
分库分表
水平扩容

不要把架构复杂度当成第一选择。

---

三十五、几个值得长期记住的结论

1. 锁的目的不是让数据库变慢，而是保护一致性

没有锁，并发写入的数据正确性就无法保证。

真正需要优化的是：

锁的范围
锁的时间
锁的顺序

而不是“消灭所有锁”。

---

2. MVCC 的本质是空间换并发

它通过：

历史版本
+
可见性判断

降低读写互相阻塞。

也就是说：

保存更多版本
换取更高并发

---

3. 普通 SELECT 与 FOR UPDATE 完全不是一个世界

普通 SELECT → 快照读 → MVCC

FOR UPDATE → 当前读 → Lock

搞清楚这一点，很多事务隔离问题都会突然简单很多。

---

4. 有索引不代表一定走索引

优化器选择的是：

估算总代价较低的方案

而不是：

“只要有索引就必须用”

---

5. EXPLAIN 不是 SQL 优化的终点

EXPLAIN 告诉我们的是：

优化器准备怎么执行。

但真正的慢还可能来自：

• 锁等待；
• Buffer Pool；
• I/O；
• 数据倾斜；
• 统计信息；
• 高并发；
• 运行时资源竞争。

---

6. SQL 优化的核心不是“技巧”，而是降低数据访问成本

无论是：

索引
覆盖索引
减少回表
减少扫描
减少 JOIN
Buffer Pool
MVCC

最终都在做同一件事：

用更少的资源完成同样的数据计算。

---

三十六、最终知识图谱

可以用下面这张逻辑图把整组知识串起来：

             ┌──────────────┐
             │   SQL 请求    │
             └──────┬───────┘
                    │
                    ▼
          ┌──────────────────┐
          │ Parser / Analyzer │
          └────────┬─────────┘
                   │
                   ▼
        ┌──────────────────────┐
        │   Query Optimizer     │
        │                      │
        │ Logic + Physical     │
        │ RBO / CBO            │
        └──────────┬───────────┘
                   │
                   ▼
           ┌──────────────┐
           │ Execution Plan│
           └───────┬──────┘
                   │
       ┌───────────┴───────────┐
       │                       │
       ▼                       ▼
┌──────────────┐       ┌──────────────┐
│    Index      │       │ Buffer Pool  │
│ B+Tree / AHI  │       │ Data / Index │
└──────┬───────┘       └──────┬───────┘
       │                       │
       └───────────┬───────────┘
                   │
                   ▼
           ┌──────────────┐
           │   InnoDB      │
           └───────┬──────┘
                   │
        ┌──────────┴──────────┐
        │                     │
        ▼                     ▼
  ┌───────────┐         ┌───────────┐
  │   Lock    │         │   MVCC    │
  │ S/X/Intent│         │ Undo + RV │
  └───────────┘         └───────────┘

最终可以用一句话概括：

优化器决定“怎么找”，索引决定“找得快不快”，Buffer Pool 决定“要不要去磁盘找”，MVCC 决定“应该看到哪个版本”，锁决定“谁现在可以改”。

当这五个模块连起来以后，MySQL 的很多底层行为其实就不再神秘了。