MySQL｜一条SQL是如何执行的

#MySQL [字体 小·中·大]

一条 SQL 是如何执行的

  1. 首先客户端通过 tcp/ip 发送一条 sql 语句到 server 层的 SQL interface

  2. SQL interface 接到该请求后，先对该条语句进行解析，验证权限是否匹配

  3. 验证通过以后，分析器会对该语句分析,是否语法有错误

  4. 接下来是优化器器生成相应的执行计划，选择最优的执行计划

  5. 之后会是执行器根据执行计划执行这条语句。在这一步会去 open table,如果该 table 上有 MDL 锁，则等待，如果没有，则加在该表上加短暂的 MDL(S) (如果 opend_table 太大,表明 open_table_cache 太小。需要不停的去打开 frm 文件)

  6. 进入到引擎层，首先会去 innodb_buffer_pool 里的 data dictionary(元数据信息)得到表信息

  7. 通过元数据信息,去 lock info 里查出是否会有相关的锁信息，并把这条 update 语句需要的锁信息写入到 lock info 里(锁这里还有待补充)

  8. 然后涉及到的老数据通过快照的方式存储到 innodb_buffer_pool 里的 undo page 里, 并且记录 undo log 修改的 redo (如果 data page 里有就直接载入到 undo page 里，如果没有，则需要去磁盘里取出相应 page 的数据，载入到 undo page 里)

  9. 在 innodb_buffer_pool 的 data page 做 update 操作。并把操作的物理数据页修改记录到 redo log buffer 里。

由于 update 这个事务会涉及到多个页面的修改，所以 redo log buffer 里会记录多条页面的修改信息。

因为 group commit 的原因，这次事务所产生的 redo log buffer 可能会跟随其它事务一同 flush 并且 sync 到磁盘上

  1. 同时修改的信息，会按照 event 的格式, 记录到 binlog_cache 中。(这里注意 binlog_cache_size 是 transaction 级别的,不是 session 级别的参数, 一旦 commit 之后，dump 线程会从 binlog_cache 里把 event 主动发送给 slave 的 I/O 线程)

  2. 之后把这条 sql,需要在二级索引上做的修改，写入到 change buffer page，等到下次有其他 sql 需要读取该二级索引时，再去与二级索引做 merge (随机 I/O 变为顺序 I/O,但是由于现在的磁盘都是 SSD,所以对于寻址来说,随机 I/O 和顺序 I/O 差距不大)

  3. 此时 update 语句已经完成，需要 commit 或者 rollback。这里讨论 commit 的情况，并且双 1

  4. commit 操作，由于存储引擎层与 server 层之间采用的是内部 XA(保证两个事务的一致性,这里主要保证 redo log 和 binlog 的原子性), 所以提交分为 prepare 阶段与 commit 阶段

  5. prepare 阶段,将事务的 xid 写入，将 binlog_cache 里的进行 flush 以及 sync 操作(大事务的话这步非常耗时)

  6. commit 阶段，由于之前该事务产生的 redo log 已经 sync 到磁盘了。所以这步只是在 redo log 里标记 commit

  7. 当 binlog 和 redo log 都已经落盘以后，如果触发了刷新脏页的操作，先把该脏页复制到 doublewrite buffer 里，把 doublewrite buffer 里的刷新到

innodb 架构

MySQL 架构

  • server 层：连接管理、解析分析 SQL，操作存储引起
    • 连接器：连接管理，权限验证
    • 查询缓存：缓存查询结果，命中直接返回
    • 分析器：词法分析、语法分析
    • 优化器：生成执行接话，索引选择
  • 存储引擎：存储数据，提供读写接口个
    • MyISQM
    • InnoDB

查询缓存

查询缓存是 MySQL8.0 以下版本为了提高查询的速度都一个功能。

查询缓存的更新策略是当表中的数据发生变化，则删除表的查询缓存，弊端是当表存在大量的更新维护缓存一个大的开销，收益没有开销大。查询缓存也是有它的用武之地的在表数据不经常更新的情况下是个不错的选择。

redo log（重做日志）

为什么要有 redo log？为了实现事务的持久性，innodb 增加了 redo log，它是 innodb 特有的日志，redo 日志是一个环形的日志记录器，当有一个记录要变更时，innodb 先将变更操作写入 redo log，同时存储引擎会在适当的时候将这个更新的操作写到磁盘。

redo log 的写入过程。redo log 是一个环形日志记录器，其中 write pos 是当前日志写的位置、checkpoint 是日志擦除的位置，一个写入一个擦出。如果当 write pos 追上 checkpoint（写满了）此时就要停下所有的更新操作，将已写的更新到数据文件。

redo log 的地位。redo log 保证了 MySQL 即使发生异常或重新，记录的日志都不会丢失，保证了事务的持久性。MySQL 这种技术也被称为 WAL（Write Ahead Loggin）先写日志，他的关键点是：先写日志、再落盘。

binlog（归档文件）

binlog 能用来做备份恢复和主从复制。

binlog 的类型：

  • row：记录日志被修改的形式，只有 value，不存在连表
    • 优：能够保证记录数据的一致性，每个修改语句的值是一样的
    • 缺：日志较大占空间
  • statement：记录执行的 SQL 语句
    • 缺：会引起数据不一致，例如 SQL 中使用了 date 函数，则再次执行时间就不一样了
  • mixed：row 和 statement 的折中方案，MySQL 判断 SQL 是否会引发主备不一致，如果会引起就用 row，否则用 statement。

binlog 与 redo log 的区别是：

  1. redo log 是 innodb 引擎特有的；binlog 是 MySQL Server 层的实现，所有引擎都可以使用
  2. redo log 是物理日志，记录的是“在某个物理页上做了什么”；binlog 是逻辑日志，记录了 SQL 语句原始逻辑或操作，例如给 id=2 的字段 c 字段加 1.
  3. redo log 是循环写的，大量写会导致空间紧张；binlog 是追加写的，文件到一定大小就切换下一个

以一个 update 操作为例，redo log 与 binlog 的交互流程是这样的：

  1. 执行器调用存储引擎的接口查找 id=2 的数据，存储引擎依靠 B+树结构进行搜索，如果这个记录在内存中就直接返回给执行器；否则，需要将该记录从磁盘读入内存，然后返回。
  2. 执行器拿到记录，把对应的值修改，得到一个新纪录，然后在调用存储引擎层的接口写入新数据。
  3. 存储引擎将数据更新到内存中（innodb_buffer_pool），同时将这个操作记录到 redo log 中，此时 redo log 处于 prepare 状态。然后告知执行器完成了可以提交事务。
  4. 执行器生成这个操作的 binlog，并将其写入磁盘。
  5. 执行器调用存储层的事务提交接口，引擎把刚刚写入的 redo log 的状态改为 commit 状态，更新完成。

两段提交

两段协议分为两步：

  1. 预提交
  2. 最终提交

使用两段提交为了保持 redo log 与 binlog 的一致。举个例子：

写 reod log 后 Server 挂掉了，之后的 binlog 没有写，此时因为 redo log 还没有最终提交，崩坏恢复对未提交的操作进行回滚，此时数据仍是一致性的。

数据库系统之外，两段协议已常用在跨系统保持数据的一致性。

总结

MySQL 服务端分为两层：Server 层和存储引擎层。

Server 层负责连接管理， SQL 解析、优化和执行管理；存储引擎负责数据存储及索引维护。

两个重要日志模块：redo log、binlog。

两段提交协议（2PC）。

博客没有评论系统，可以通过 邮件 评论和交流。 Top↑