mysql从零单排（三）

我的青春好像回头看了我一眼

mysql是一个很经典的数据管理的中间件，里边有很多经典的设计思想和性能优化设计，可以在很多软件设计上借鉴，值得反复学习，梳理不到位的地方还请留言指正探讨。

一、整体架构（MySQL 运行的完整分层）

MySQL 是典型的分层架构，每一层各司其职，上层不关心底层实现，底层只向上提供接口。

1. 连接层

• 概念：MySQL 的「入口大门」，负责处理客户端的连接请求。

• 核心工作：

• • 连接管理：维护客户端与 MySQL 的连接，处理连接的创建、断开、超时

• • 权限验证：验证用户名、密码、IP 白名单，只有通过验证才能进入后续流程

• 白话理解：就像公司的前台，先查工牌、登记信息，没问题才让进公司。

2. 服务层

• 概念：MySQL 的「大脑中枢」，所有 SQL 通用逻辑都在这里处理，和存储引擎无关。

• 核心组件：

• • 解析器：对 SQL 做语法、语义检查，生成解析树，确保 SQL 语法正确

• • 优化器：选择最优执行计划（比如走哪个索引、怎么连表），是 SQL 性能的关键

• • 执行器：调用存储引擎的接口，执行 SQL 并返回结果

• 白话理解：就像公司的技术部，拿到需求（SQL）后，先检查需求是否合理，再设计最优方案，最后交给执行层落地。

3. 存储引擎层

• 概念：MySQL 的「数据管家」，真正负责数据的存储、读取、索引管理，是插件式架构。

• 核心说明：默认使用 InnoDB（支持事务、行锁、崩溃恢复），MyISAM 等老引擎已基本淘汰。

• 白话理解：就像公司的仓库管理员，负责把数据存好、管好，按需取货。

4. 文件层

• 概念：MySQL 数据和日志的「物理存储」，所有数据最终都落盘在这里。

• 核心文件：

• • .ibd：InnoDB 表的数据 + 索引文件

• • redo log：重做日志，保证数据不丢

• • undo log：回滚日志，支撑事务回滚和 MVCC

• • binlog：二进制日志，用于主从复制和数据恢复

• 白话理解：就像公司的档案室，所有文件、记录都永久保存在这里。

---

二、InnoDB 核心内存：Buffer Pool（性能心脏）

Buffer Pool 是 InnoDB 最重要的内存结构，90% 的 MySQL 性能优化都围绕它展开。

1. 作用

• 概念：一块专门的内存区域，用来缓存磁盘上的数据页和索引页。

• 核心价值：把频繁访问的热点数据放在内存里，大幅减少磁盘 IO（内存速度是磁盘的 1000 倍以上）。

• 白话理解：就像你书桌上的常用书，随手就能拿；不常用的书放在书架（磁盘）上，不用每次都跑过去拿。

2. 基本单位：16KB 数据页

• 概念：InnoDB 磁盘和内存交互的最小单位是 16KB 的数据页，不是一行一行读写。

• 原理：哪怕只查询一行数据，也会把这行所在的整页 16KB 加载到 Buffer Pool，利用局部性原理提升性能。

• 白话理解：就像你拿书时，不会只抽一页纸，而是直接把整本书拿过来，下次用直接翻。

3. 页面状态

（1）干净页

• 概念：内存中的数据页，和磁盘上的对应页完全一致，没有被修改过。

• 特点：可以直接被淘汰，不需要刷盘。

（2）脏页

• 概念：内存中的数据页被修改了，但还没同步到磁盘。

• 原理：更新操作不会直接写磁盘，只改内存中的页，变成脏页，后台线程慢慢刷盘，这是 MySQL 快的核心原因。

• 白话理解：就像你在书上做了笔记（改了内存），但还没把书放回书架（没刷盘），这时候书就是「脏页」。

4. 后台线程

Buffer Pool 靠后台线程自动维护，不需要人工干预：

• IO 线程：负责异步读写磁盘，把数据页从磁盘加载到内存，或把脏页刷回磁盘

• 刷脏线程：批量把脏页刷到磁盘，平衡性能和数据安全

• LRU 管理线程：管理 Buffer Pool 的淘汰机制，删除不常用的冷数据

5. 改进版 LRU（淘汰机制）

Buffer Pool 空间有限，满了需要淘汰数据，InnoDB 用改进版 LRU 解决传统 LRU 的痛点：

• Young 区（5/8） ：存放热点数据，长期驻留内存

• Old 区（3/8） ：存放新加载的数据，只有被再次访问才会升入 Young 区

• 核心作用：避免全表扫描等一次性操作，把 Buffer Pool 里的热点数据全部冲掉

• 白话理解：就像你把书桌分成「常用区」和「临时区」，临时放的书如果不用，就直接放回书架，不占用常用区空间。

---

三、事务 ACID（事务的四大核心特性）

事务是 MySQL 保证数据安全的最小单位，ACID 是它的四大基石，缺一不可。

1. 原子性 A（Atomicity）

• 概念：事务中的所有操作，要么全部成功，要么全部失败回滚，绝对不能出现「只成功一半」。

• 实现原理：靠 undo log（回滚日志） 实现。事务执行时，把修改前的数据记录到 undo log，回滚时用 undo log 恢复数据。

• 白话理解：就像转账，A 给 B 转 100 块，必须 A 扣钱、B 加钱同时完成，只要有一步失败，全部撤销。

2. 一致性 C（Consistency）

• 概念：事务执行前后，数据库的完整性约束必须保持一致。

• 实现原理：靠 MVCC + 锁 + 日志 + 数据库约束 共同保证，比如转账前后总金额不变、唯一键不冲突。

• 白话理解：就像公司做账，收支必须平衡，不能出现账对不上的情况。

3. 隔离性 I（Isolation）

• 概念：多个事务并发执行时，互相不干扰，就像各自在独立空间执行。

• 实现原理：靠 MVCC + ReadView（读视图） 实现，每个事务只能看到自己的快照，不被其他事务影响。

• 白话理解：就像两个人同时用同一个账本，各自看自己的版本，不会互相改乱。

4. 持久性 D（Durability）

• 概念：事务一旦提交，数据就永久保存在数据库，哪怕服务器断电、崩溃重启，数据也绝对不会丢失。

• 实现原理：靠 redo log（重做日志） 实现，用 WAL（先写日志再写磁盘）机制，崩溃后用 redo log 恢复数据。

• 白话理解：就像你把重要文件签字盖章后存档，就算电脑坏了，文件也不会丢。

---

四、三大日志体系（MySQL 数据安全的三大保障）

这三个日志是 MySQL 所有特性的底层支撑，分别对应不同的核心功能。

1. undo log（回滚日志）

• 概念：InnoDB 专属日志，记录数据修改前的历史版本。

• 核心作用：

• • 记录修改前版本：每次修改数据，都会把旧数据拷贝到 undo log

• • 支撑 MVCC 版本链：用 undo log 串联数据的所有历史版本，实现多版本并发控制

• • 事务回滚：事务回滚时，用 undo log 把数据恢复到修改前的状态，保证原子性

• 白话理解：就像写文章的「撤销键」，改坏了可以一键恢复，同时保留历史版本给别人读。

2. redo log（重做日志）

• 概念：InnoDB 专属日志，记录数据页的物理修改（比如「某页第 100 字节改成了 20」）。

• 核心作用：

• • 物理日志・页修改：记录的是磁盘页的实际修改，不是逻辑 SQL

• • WAL 先写日志再刷盘：事务提交时，先写 redo log，再慢慢把脏页刷到磁盘，大幅提升性能

• • 崩溃恢复，保证持久性：服务器崩溃重启后，用 redo log 把未刷盘的修改补全，保证数据不丢

• • 循环写，固定大小：redo log 是环形结构，写满后覆盖旧日志，不会无限膨胀

• 白话理解：就像写日记，先把要做的事记在本子上，再慢慢去做，就算中途断电，回来照着日记就能把事做完。

3. binlog（二进制日志）

• 概念：MySQL Server 层的逻辑日志，记录所有修改操作（SQL 语句 / 行数据变化），所有存储引擎都能用。

• 核心作用：

• • 逻辑日志・Server 层：记录的是逻辑操作，不是物理页修改

• • 主从复制：主库把 binlog 发给从库，从库执行 binlog 里的操作，实现主从数据同步

• • 数据恢复：数据库误删后，用 binlog 可以把数据恢复到误删前的状态

• • 两阶段提交保证一致：为了保证 redo log 和 binlog 的一致性，MySQL 用两阶段提交，避免主从不一致、数据丢失

• 白话理解：就像数据库的「操作录像」，所有修改都录下来，既能给从库同步，又能出问题时回溯恢复。

---

五、MVCC 多版本并发控制（MySQL 高并发的灵魂）

MVCC 是 InnoDB 实现「读不加锁、写不加读锁」的核心，让 MySQL 能支持高并发读写。

1. 隐藏字段

每行数据都有两个隐藏字段，是 MVCC 的基础：

• trx_id：事务 ID，记录哪个事务修改了这行数据

• roll_pointer：回滚指针，指向 undo log 里的上一个历史版本

• 白话理解：每行数据都有两个「身份证」，一个记录谁改的，一个记录改之前的样子。

2. 版本链：undo log 串联历史数据

• 概念：每次修改数据，都会生成一个新版本，用 roll_pointer 把旧版本串起来，形成一条版本链。

• 白话理解：就像给数据拍「成长照」，每次修改都拍一张，按时间串起来，随时能看任何一个历史版本。

3. ReadView 快照（读视图）

• 概念：事务执行时，MySQL 生成的一个快照，记录了当前所有事务的状态（哪些在运行、哪些已提交）。

• 核心作用：

• • 决定可见性：事务只能看到在 ReadView 生成前已经提交的版本，看不到未提交的版本

• • 读不加锁、写不加读锁：读操作不用加锁，直接读历史版本，写操作也不阻塞读，实现高并发

• 白话理解：就像给事务拍了一张「当前数据库的照片」，事务全程都看这张照片，别人怎么改都不影响。

4. 快照读：普通 select 无锁

• 概念：普通的 select 查询，就是快照读，读的是 ReadView 对应的历史版本，不加任何锁。

• 白话理解：就像看照片，不用碰真实数据，自然不会被写操作阻塞，速度极快。

5. 解决：脏读、不可重复读

• 脏读：A 事务读到 B 事务未提交的数据，B 回滚后数据无效。MVCC 让 A 只能看到已提交的版本，解决脏读。

• 不可重复读：A 事务内两次读同一行数据，结果不一样（B 事务修改并提交）。MVCC 让 A 全程看同一个快照，两次读结果一致，解决不可重复读。

---

六、MySQL 锁机制（并发写安全的保障）

锁是 MySQL 控制并发写、保证数据一致性的核心，不同的锁对应不同的场景。

1. 行锁 Record Lock

• 概念：只锁某一行数据，其他行不受影响。

• 特点：粒度最小，并发最高，是 InnoDB 最常用的锁。

• 白话理解：就像给某一行上了锁，只有拿到钥匙的人能改，其他人只能等，但不影响其他行。

2. 间隙锁 Gap Lock

• 概念：锁一段空白的区间，不让别人在这个区间里插入数据。

• 作用：防止幻读（区间内插入新数据）。

• 白话理解：就像给一段空位置上了锁，别人不能在这段空位置里插新数据，避免出现「幻觉一样的新行」。

3. 临键锁 Next-Key Lock

• 概念：行锁 + 间隙锁的组合，既锁行，又锁行前后的间隙。

• 核心说明：InnoDB 默认的锁算法，在 RR（可重复读）隔离级别下默认开启。

• 核心作用：解决幻读的核心，彻底防止区间内插入新数据。

• 白话理解：就像给某一行和它前后的空位置都上了锁，别人既改不了这行，也插不了新行，完美解决幻读。

4. 死锁

• 概念：两个事务互相持有对方需要的锁，无限等待，谁也无法继续执行。

• 解决方法：

• • 自动检测回滚：MySQL 会自动检测死锁，回滚其中一个事务，让另一个继续执行

• • 按顺序加锁避免：业务上按固定顺序更新数据（比如先锁 id=1，再锁 id=2），从根源避免死锁

• 白话理解：就像两个人互相堵路，谁也过不去，只能让一个人退一步，路就通了。

---

七、幻读解决方案（MySQL 如何彻底解决幻读）

1. 先搞懂：什么是幻读？

事务 A 第一次查询：3 条数据；事务 B 插入一条并提交；事务 A 再查询：变成 4 条。就像幻觉一样多了行，这就是幻读。

2. 快照读

• 原理：普通 select 是快照读，读的是事务开始时的 ReadView 对应的历史版本。

• 效果：事务 A 全程看的是事务开始时的快照，B 插入的新数据不在快照里，A 看不见，自然不会幻读。

• 白话理解：事务 A 看的是「过去的照片」，B 插入的新数据是「现在的新东西」，照片里没有，A 自然看不到。

3. 当前读

• 原理：select for update、update、delete 这些操作是当前读，会加临键锁（行锁 + 间隙锁）。

• 效果：锁住查询到的行和前后的间隙，别人不能插入、不能修改、不能删除，自然不会出现新行，幻读被堵住。

• 白话理解：给查询的范围上了锁，别人插不进来，自然不会出现新行。

4. 最终：MVCC + 临键锁 双重解决

• 快照读靠 MVCC 读历史版本，看不见新数据；

• 当前读靠临键锁锁住范围，禁止插入新数据；

• 双重保险，彻底解决幻读问题。

---

八、性能优化核心（MySQL 优化的终极方向）

所有 MySQL 优化，最终都围绕这几个核心展开：

1. 索引优化

• 核心原理：索引是排好序的 B+ 树，用空间换时间，把全表扫描 O (n) 变成 O (log n)。

• 关键要点：

• • B + 树：矮胖结构，IO 次数少，叶子节点链表相连，范围查询快

• • 最左匹配：联合索引必须从左到右连续匹配，中间不能断

• • 覆盖索引：索引包含查询所需的所有字段，不需要回表，性能拉满

2. SQL 优化

• 核心目标：避免全表扫描、避免文件排序、避免临时表。

• 关键要点：

  • 避免 Using filesort：给排序字段加索引，用索引排序代替文件排序

  • 避免 Using temporary：给分组字段加索引，避免创建临时表

  • 避免 select *：只查需要的字段，优先使用覆盖索引

3. 内存优化

• 核心配置：innodb_buffer_pool_size，设置为物理内存的 50%~70% 。

• 原理：Buffer Pool 越大，能缓存的热点数据越多，磁盘 IO 越少，性能越快。

• 白话理解：给书桌留更大的空间，常用书都放在桌上，不用每次跑书架拿。

4. 减少回表

• 概念：二级索引查到主键后，再去主键索引查整行数据，就是回表，会增加 IO。

• 优化方法：用覆盖索引，把查询需要的字段都放进索引，直接从索引取数据，不需要回表。

5. 减少脏页刷盘压力

• 原理：大事务会产生大量脏页，导致刷盘压力骤增，性能下降。

• 优化方法：控制大事务，把大事务拆成小事务，减少脏页的产生，平衡刷盘压力。

---

🎯 总结

Buffer Pool 是 InnoDB 的心脏，用内存缓存减少磁盘 IO；undo log 支撑 MVCC 实现回滚与无锁读，redo log 保证崩溃不丢数据，binlog 实现主从与恢复；MVCC 解决读并发，锁解决写并发，MVCC + 临键锁双重解决幻读，整套机制共同实现事务 ACID，让 MySQL 既快又稳。