日志

binlog（归档日志）

介绍

• binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
• binlog 是逻辑日志， 记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
• binlog 是可以追加写入的，“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

三种格式对比

• statement：
  • 记录执行的sql
  • 可能会出现主备不一致的情况（例如插入时使用了Date函数）
• row：
  • 记录每行的变更情况
  • 很占空间
  • 建议使用，便于数据恢复
• mixed：
  • MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格 式，否则就用 statement 格式

写入文件机制

• 事务执行过程中，先把日志写到 binlog cache
• 事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog files 中，并清空 binlog cache
• 根据参数sync_binlog将binlog files的内容fsync到磁盘中

参数

• binlog_cache_size
  • 一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入
  • 系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个 线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
• sync_binlog
  • 0，只 write，不 fsync
  • 1，只 fsync
  • N，表示每次提交事务都 write，但累积 N 个事务后才 fsync
  • 因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将 其设置为 100~1000 中的某个数值，但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事 务的 binlog 日志

redo log

介绍

• redo log 是 InnoDB 引擎特有
• redo log 是物理日志，记录的是在某个数据页上做了什么修改
• redo log 是循环写的，空间固定会用完
• redo log 主要节省的是随机写磁盘的IO消耗(转成顺序写)

写文件流程

• 从头开始写，写到末尾就又回到开头循环写
• wirte pos是当前记录的位置，一边写一边后移
• checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
• checkpoint的前面是已经同步到磁盘的数据
• 当write pos追上checkpoint的时候，代表文件已满，不能再更新，需要先同步一波数据，后移checkpoint

刷脏页

• 当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去
• 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为脏页。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为干净页

刷脏页有下面4种场景（后两种不用太关注“性能”问题）：

• redolog写满了： redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了
• 内存不够用了： 如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页
• MySQL 认为系统“空闲”的时候： 这时系统没什么压力。
• MySQL 正常关闭的时候： 这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快

崩溃恢复

• 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，然后等待刷脏页执行

参数

• innodb_flush_log_at_trx_commit
  • 0，不write也不fsync，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 （mysql程序挂掉，系统没挂，会丢失数据）
  • 1，只fsync，表示每次事务提交时都将 redo log 直接持久化到磁盘
  • 2，只write不fsync，表示每次事务提交时都只是把 redo log 写到 page cache（mysql程序挂掉，系统没挂，就不会丢失数据）
  • InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件 系统的 page cache，然后调用 fsync 持久化到磁盘

日志记录流程

两阶段提交（2PC）

• 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内 存，然后再返回
• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的 一行数据，再调用引擎接口写入这行新数据
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于prepare状态。然后告知执行器执行完成了，随时可以提交事务
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交commit状 态，更新完成

组提交

• 一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好

崩溃恢复时的判断规则

• 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
• 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完 整：
  • 是，则提交事务
  • 否，回滚事务

怎么判断binlog是完整的？

• statement 格式的 binlog，最后会有 COMMIT
• row 格式的 binlog，最后会有一个 XID event

主从复制

流程

• 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要 从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量
• 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread，其中 io_thread 负责与主库建立连接
• 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，通过binary log dump线程发给B
• 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）
• sql_thread 读取中转日志，解析出日志里的命令，并执行

主节点 binary log dump 线程

• 当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容
• 在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放

从节点I/O线程

• 当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log
• I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中

从节点SQL线程

• SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性

双M结构下，循环复制问题解决

• 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id
• 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id
• 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志，所以， 死循环在这里就断掉了

一条SQL语句在MySQL中如何执行的

MySQL 基础架构分析

MySQL 基本架构概览

• 简单来说 MySQL 主要分为Server层和存储引擎层：
  • Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块
    • 连接器：身份认证和权限相关(登录 MySQL 的时候)
    • 查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）
    • 分析器：没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确
    • 优化器：按照 MySQL 认为最优的方案去执行
    • 执行器：执行语句，然后从存储引擎返回数据
  • 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了

Server 层基本组件介绍

• 连接器
  • 连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样
  • 主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的
• 查询缓存(MySQL 8.0 版本后移除)
  • 查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集
  • 连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件
  • MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的
  • 所以，一般在大多数情况下我们都是不推荐去使用查询缓存的
  • MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了
• 分析器
  • MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：
    • 第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步
    • 第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法
  • 完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了
• 优化器
  • 优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等
  • 可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来
• 执行器
  • 当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果

语句分析

查询语句

说了以上这么多，那么究竟一条 sql 语句是如何执行的呢？其实我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student A where A.age=‘18’ and A.name=’ 张三 ';
结合上面的说明，我们分析下这个语句的执行流程：

先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。
b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。
那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

更新语句

2.2 更新语句
以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下：

update tb_student A set A.age=‘19’ where A.name=’ 张三 ';
我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块式 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

•先查询到张三这一条数据，如果有缓存，也是会用到缓存。
•然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
•执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
•更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

•先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
•先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

•判断 redo log 是否完整，如果判断是完整的，就立即提交。
•如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

总结

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等
• SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
• 对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit