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

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'
innodb_flush_log_at_trx_commit值作用
0提交事务时，不会将redo log buffer中的数据写入os buffer，而是每秒写入os buffer并刷到磁盘
1提交事务时，必须把redo log从内存刷入到磁盘文件中
2提交事务时，将rodo log写入os buffer中，默认每隔1s将os buffer中的数据刷入磁盘

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