点击上方 蓝字关注我们！

1.MySQL的在文件中是如何存储的？

答：数据是存在页
中的，一页
的大小是 16kb, 一个表由很多的页
组成，这些页
组成了 B+树
。

MYSQL内存中，多个这样的数据结构组成一个双向链表

1. SQL语句是如何执行的呢？MySQL的逻辑架构图如下所示：

1. 当我们需要更新一条数据时，是需要先从磁盘中取出来，更新后再持久化到磁盘中吗？

   答：不是的，如果这样的话，一条 SQL 的执行过程太慢了，因为对一个大磁盘文件的读写操作是要耗费大量时间的。

   所以真正的执行过程是，当需要更新或者读取某条数据的时候，会把对应的页加载到内存中的 Buffer Pool
   缓冲池中（默认为 128m 当然为了提高系统的并发度，你可以把这个值设置大一点）

   

当更新数据的时候，如果对应的页在 Buffer Pool 中，则直接更新 BP中的数据页即可，如果不在BP中，才会加载磁盘中对应的页到BP中，然后更新，此时BP中的页则跟磁盘中的页不一致，称为脏页。这些脏页是要被刷回磁盘中的。

①. BP不够用了，要给新加载的页腾位置，所以会利用改进的 LRU 算法，将最近最久未使用的脏页刷回磁盘。

②. 后台线程会在MySQL空闲的时候，将脏页刷回到磁盘中

③. redolog写满时

④. 数据库关闭时会将所有脏页刷回到磁盘中

redo log

问：如果脏页没有刷回，数据库宕机了怎么办？修改不就丢失了吗？

这就要说道 redo log了(重做日志文件，主要是记录数据物理页的修改)，内存中所做的修改都是写到 redo log buffer中的，这是内存中的一个缓冲区，用来存储redo 日志。

Redo log的大小是固定的，比如可以配置一组 4 个文件，每个文件的大小是 1G ，总大小就是 4 G ，从头开始写，写到末尾就从头再次开始写，循环顺序写的效率高于随机写。

write pos
是当前要写的位置，checkpoint
是要擦除的位置，擦除前要把对应的脏页刷回到磁盘中。他两个之间的绿色区域是可以写的位置。当系统能支持的并发比较低的时候，可以看看对应的 redo log 是不是设置的太小了。太小的话会导致频繁的刷脏页，可以通过工具监控 redo log的大小。redo log的大小 = innodb_log_file_size * innodb_log_file_in_group

(默认为2)

redo log 是如何避免数据丢失的？

事务未提交，MySQL宕机，这种情况， Buffer Pool中的数据丢失，并且 redo log buffer中的日志也会丢失，不影响数据。

事务提交成功，redo log buffer中的数据没有刷到磁盘，此时会导致事务提交的数据丢失。

鉴于这种情况，我们可以设置 innodb_flush_log_at_trx_commit来决定redo log 的刷盘策略

# 查看 innodb_flush_log_at_trx_commit的配置
show global variables like 'innodb_flush_log_at_trx_commit'

值为 0 或者 2 的时候都可能会造成事务更新丢失，所以一般系统中的 innodb_flush_log_at_trx_commit的值都会设置成 1

undo log

当我们修改一条数据的时候，会把原来的值写到 undo log 中，当这条更新语句在事务中执行的时候，事务回滚，就可以通过 undo log将数据恢复成原来的值。

undo log在 MVCC 的实现中也扮演了重要的作用：

MVCC的实现

MVCC 多版本并发控制，通过读取指定版本的历史记录，并通过一些手段保证读取的记录符合事务所处的隔离级别，在不加锁的情况下解决读写冲突。

对于使用Innodb 存储引擎的表来说，聚集索引记录中都包含下面2个必要的隐藏列：

• trx_id:一个事务每次对某条聚集索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列
• roll_pointer:每次对某条聚集索引记录进行改动时，都会把旧版本写入undo日志中。这个隐藏列就相当于一个指针，通过它找到该记录修改前的信息。

例如：

一个记录name从貂蝉被依次修改成王昭君，西施，会有如下的记录，多个记录构成一个版本链

首先，复习一下事务的隔离界别：

建表数据如下：

CREATE TABLE `account` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `balance` int(3) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

设置表的隔离级别为读已提交，下面看一下不可重复读的例子：

不可重复读是指在事务1内，读取了一个数据，事务1还没有结束，事务2也访问了这个数据，修改了这个数据并提交，然后事务一又重复读取了这个数据，由于事务2修改了这个数据，所以，事务1两次读取的结果不一致，因此称为不可重复读。

下面设置隔离级别为可重复读：

这里设置成可重复读之后，当前事务就不受其他事务影响了。

MySQL是如何支持这两种隔离级别的呢？MVCC只对这两种隔离级别而言。下面看一下MVCC是如何实现的。

为了判断版本链中哪个版本对当前事务时可见的，MySQL设计出了ReadView的概念。4个重要的内容如下：

• m_ids:在生成ReadView时，当前系统中活跃的事务id列表
• min_trx_id：在生成ReadView时，当前系统中活跃的最小的事务id,也就是m_ids中的最小值
• max_trx_id:在生成ReadView时，系统应该分配给下一个事务的事务id值
• creator_trx_id:生成该ReadView的事务的事务id

当对表中的数据进行改动时，执行 insert/update/delete这些语句的时候，才会为事务分配唯一的事务id,否则一个事务的事务id值默认为0

max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比如现在有事务id为1，2，3这三个事务，之后事务id为3的事务提交了，当有一个新的事务生成ReadView时，m_ids的值就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4

MVCC判断版本链中哪个版本对当前事务是可见的过程如下：

解释：

1. 如果被访问版本的 trx_id = creator_id,意味着当前事务在访问他自己修改过的记录，所以该版本可以被当前事务访问。
2. 如果被访问版本的 trx_id < min_trx_id,表明生成该版本的事务在当前事务生成 ReadView前已经提交，所以该版本可以被当前事务访问。
3. 被访问版本的 trx_id >= max_trx_id,表明生成该版本的事务在当前事务生成 ReadView之后才开启，该版本不可以被当前事务访问
4. 被访问版本的trx_id 是否在 m_ids列表中
• 是：创建 ReadView 时，该版本还是活跃的，该版本不可以被访问。顺着版本链找到下一个版本的数据，继续执行上面的步骤判断可见性，如果最后一个版本还不可见，意味着记录对当前事务不可见
• 否：创建 ReadView时，生成该版本的事务已经被提交，该版本可以访问

下面结合例子理解一下：

Read Committed(读已提交)，每次读取数据前都生成一个 ReadView

下面是三个事务的执行过程，一行代表一个时间点：

分析一下5这个时间点的select 的执行过程：

1. 系统中有两个事务id分别是100和200的事务正在执行
2. 执行 select 语句时生成一个 ReadView, mids=[100,200], min_trx_id = 100, max_trx_id = 201, creator_trx_id = 0(select 这个事务没有执行更改操作，事务id默认是0)
3. 最新版本的name = 西施，该版本的trx_id值100， 在 mid 列表中，不符合可见性的要求，根据 roll_pointer 跳到下一个版本
4. 下一个版本的name = 王昭君，该版本的trx_id = 100 ,也不符合可见性要求，继续跳到下一版本
5. 下一版本name = 貂蝉，该版本的 trx_id = 10(已经提交) ,小于min_trx_id,因此最后返回的name = 貂蝉

下面再分析一下8这个时间点的select 的执行过程：

1. 系统中有一个事务id为200的事务正在执行（事务id=100的事务已经提交）
2. 执行select 语句时生成一个ReadView, mids=[200], min_trx_id = 200, max_trx_id = 201, creator_trx_id = 0 (因为是查询，不涉及修改数据库，默认是0)
3. 最新版本的name = 杨玉环，该版本的trx_id = 200, 在mids列表中，不符合可见性要求，根据 roll_pointer 寻找下一版本
4. 下一版本name = 西施，该版本的trx_id = 100， 小于min_trx_id,因此最后返回的name = 西施

Repeatable Read（可重复读），只在第一次读取数据时生成一个 ReadView

可重复读只在第一次读取时生成一次ReadView,所以每次读到的是相同的版本，所以name一直会是貂蝉。

分析5， 8， 10时间点的select查询操作

1. 系统中有两个正在执行的事务100， 200
2. 第一次执行select 语句生成ReadView, mids=[100,200], min_trx_id = 100, max_trx_id = 200, creator_trx_id = 0 (因为查询不涉及修改数据库，所以默认事务id = 0)
3. 最新版本name = 西施，该版本的trx_id = 100,在mids列表中，不可见，根据 roll_pointer查找下一版本
4. 下一版本是王昭君，trx_id = 100, 在mids中，不可见，下一版本
5. 下一版本是貂蝉，trx_id =10（已经提交），trx_id < min_trx_id,所以最后查询到的结果是貂蝉

分析 8 时间点的select查询

1. 因为可重复读只在事务的第一次查询时候生成 ReadView
2. 最新版本name = 杨玉环， trx_id = 200,在mids中不可见，roll_pointer下一版本
3. 下一版本name=西施，trx_id = 100,在mids中不可见，下一版本
4. name = 王昭君，trx_id = 100, 不可见，下一版本
5. name = 貂蝉，trx_id = 10, trx_id < min_trx_id,可见,结果是貂蝉

分析10 时间点的select查询

同上面的结果一样，需要推到name = 貂蝉才满足 trx_id < (min_trx_id = 100),所以最后的结果是貂蝉。

bin log 主从库之间如何同步数据？

当我们把MySQL的主库数据同步到从库，或者其他数据源时，例如 es, bi库时，只需要订阅主库的binlog即可。

• 和redo log 的区别：

  sync_binlog值	作用
  0	不立即刷盘，将binlog写入os buffer，由操作系统决定何时刷盘 ，有可能会丢失多个事务的数据
  1	将binlog写入os buffer，每n个事务提交后，将os buffer的数据刷盘

  一般来说，将binlog 的刷盘策略设置为 1即可

  接下来看一下将 id=2的行 c 字段加一的操作执行流程

  

1. redo log是Innodb 存储引擎特有的，binlog 是MySQL的 server 层实现的，所有引擎都可以使用
2. redo log 是物理日志，记录的是数据页上的修改， bin log 是逻辑日志，记录的是语句的原始逻辑。
3. redo log 是固定空间，循环写。Binlog 是追加写，当binlog 文件写到一定的大小后会切换到下一个，并不会覆盖以前的日志。

1. 引擎将新数据更新到内存中，将操作记录到 redo log中，此时redo log 处于 prepare状态，然后告知执行器执行完成了，可以提交事务
2. 执行器生成操作的binlog,并把binlog写入磁盘
3. 引擎将写入的redo log 改为提交状态，更新完成。

为什么把redo log的写入拆成 2 个步骤，即 prepare和commit两段提交？

因为不管先写redo Log还是binlog ，崩溃发生后，最终其实都有可能造成原库和用日志恢复的库不一致，而两段提交可以解决这个问题。redo log 和 binlog 具有并行关联，在恢复数据时，redo log 用于恢复主机故障时未更新的物理数据，binlog 用于备份操作，每个阶段的log 操作都是记录在磁盘的，在恢复数据时，redo log状态为 commit则说明binlog也成功，直接恢复数据；如果redo log是 prepare状态，则需要查询对应的binlog 事务是否成功，决定回滚还是执行。

经验之谈：

「1. 数据库支持的并发度不高」

在一些并发要求高的系统中，可以调高Buffer Pool和redo log，这样可以避免频繁的刷脏页，提高并发

「2. 事务提交很慢」

原来我负责的一个系统跑的挺正常的，直到上游系统每天2点疯狂调我接口，然后我这边都是事务方法，事务提交很慢。监控到Buffer Pool和redo log的设置都很合理，并没有太小，所以问题出在哪了？我也不知道

「后来dba排查到原因，把复制方式从半同步复制改为异步复制解决了这个问题」

「异步复制」：MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整

「半同步复制」：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间

「全同步复制」：指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响

「3. 在一个方法中，我先插入了一条数据，然后过一会再查一遍，结果插入成功，却没有查出来」

这个比较容易排查，如果系统中采用了数据库的读写分离时，写插入的是主库，读的却是从库，binlog同步比较慢时，就会出现这种情况，此时只需要让这个方法强制走主库即可。