MySQL原理和实践

一条SQL的执行过程

1. 客户端连接到服务端的连接器
2. 分析器进行词法分析（解析关键字和字段名）和语法分析（判断是否符合MySQL语法）
3. 如果是查询语句，有缓存则查缓存
4. 优化器决定SQL的执行顺序
5. 表权限校验，执行器调用存储引擎读写接口进行操作。如果是InnoDB写操作具体流程是
   1. 调用查询接口查询数据
   2. 查询引擎从磁盘读取到内存,返回数据
   3. 执行器变更数据，调用查询引擎写入数据
   4. 新数据更新到内存，写入redolog
   5. 执行器写binlog
   6. 提交事务，写数据

redolog 和 binlog

对比

对比项	redo log	binlog
属于哪个模块	InnoDB 存储引擎	MySQL Server 层（与存储引擎无关）
作用	用于 崩溃恢复，保证事务持久性	用于 主从同步、备份恢复、审计等
写入时机	事务提交前就写入	事务提交时统一写入
内容格式	物理日志（记录页的物理变化）	逻辑日志（记录 SQL 或数据变更操作）
是否可部分持久	是（prepare 阶段后就写）	否（只在事务 commit 时才写）
是否可重做恢复	是	否，只能用于逻辑重放（不保证完整性）
是否被 binlog_format 影响	否	是（可选 statement/row/mixed）

写入过程

2PC两阶段提交，具体过程如下

1. 写入 binlog（未 fsync）
2. 写入 redolog 的 prepare 阶段（未提交）
3. fsync binlog（落盘）
4. 写入 redolog 的 commit 标记
5. 返回“提交成功”
   Q：如何做到崩溃恢复的？
   A：

   redo log状态	binlog 状态	崩溃恢复后的处理
   无 prepare	无	事务未开始，无需处理
   prepare ✔	无/未 flush	回滚
   prepare ✔, commit ✔	✔ flush	重做

事务四大特性

ACID
Atomicity（原子性）：事务中的所有操作要么全部完成，要么全部不做
Consistency（一致性）：事务执行前后，数据库必须保持一致性约束
Isolation（隔离性）：多个事务并发执行时，彼此不能相互干扰
Durability（持久性）：一旦事务提交，数据必须永久保存，即使系统崩溃

事务的隔离级别

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	✅ 是	✅ 是	✅ 是
Read Committed	❌ 否	✅ 是	✅ 是
Repeatable Read	❌ 否	❌ 否	✅ 是（InnoDB 实现中为 ❌）
Serializable	❌ 否	❌ 否	❌ 否

1. 脏读（Dirty Read）
   含义：读到未提交事务写的数据。
   例子：事务 A 改了一个值还没提交，事务 B 就读到了这个值。如果 A 回滚了，B 读到的是不存在的脏数据。

2. 不可重复读（Non-repeatable Read）
   含义：同一个事务内，两次读取同一行返回了不同结果。
   例子：事务 A 先查一次 name=’Alice’ 的年龄，事务 B 改了年龄并提交，事务 A 再查年龄变了。

3. 幻读（Phantom Read）
   含义：事务中按条件读出一批数据，后续再读发现“幻影”数据（多出或少了行）。
   例子：事务 A 查 age > 20 的行，事务 B 插入一个 age = 22 并提交，事务 A 再查时结果变了。

MVCC

MVCC，全称是 Multi-Version Concurrency Control（多版本并发控制），是 InnoDB 用来实现事务隔离的关键机制。
在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

索引数据结构

B+树

覆盖索引

在普通索引上，叶节点上存储了相关字段和主键的数据，如果查询的是这部分字段可以避免回表查询，提高查询效率，减少树的搜索次数，显著提升查询性能

主键的选择

• 自增主键的插入可以以追加模式添加数据，避免中间插入导致B+树页分裂
• 普通索引页子节点存储的是主键，因此主键越小越好

当前读和快照读