关注我，更多精彩文章第一工夫推送给你

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

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

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

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

3. 当咱们须要更新一条数据时，是须要先从磁盘中取出来，更新后再长久化到磁盘中吗？

   答：不是的，如果这样的话，一条 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 的区别：

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

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

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

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

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 同步比较慢时，就会呈现这种状况，此时只须要让这个办法强制走主库即可