Mysql 面试总结 看这一篇就够了

事务特性ACID

• 原子性A：要么成功，要么失败，不可分割。
• 一致性C：事务执行前后，数据库处于一致性状态，事务成功变化正确。事务失败返回原始阶段。
• 隔离性I：并发下，不同事务操作相同数据，并发事务所做的修改隔离，要么是另一个事务修改前没要么是另一个事务修改后；不存在中间状态。
• 持久性：事务结束后，对数据库的操作必须要永久保存下来（保存在磁盘中）。

事务的隔离级别

• ISOLATION_DEFUALT：后端数据库默认隔离级别。
• ISOLATION_READ_UNCOMMITED：最低级别，允许读尚未提交的数据变更，可能会出现脏读、幻读、不可重复读。
• ISOLATION_READ_COMMITED：RC，允许读取并发事务已提交的数据，可以阻止脏读，但可能会出现幻读、不可重复读。
• ISOLATION_REPEATABLE_READ：RR，同一字段，多次读取结果都是一致的，除非数据本身被修改，可阻止脏读、不可重复读，但仍有幻读。
• ISOLATION_SERIALIZABLE：最高隔离级别，完全服从ACID，没有脏读、幻读、不可重复读，但速度慢，完全锁定事务。

脏读、幻读、不可重复读

• 脏读：一个事务读取了被另一个事务改写但尚未提交的数据，如果数据改变后被回滚，第一个事务读取的数据就会无效。
• 幻读：当事务T1读取几行数据后，另外一个并发事务T2插入了一些记录，幻读就发生了，第一个事务T1发现了一些原来没有的额外数据记录（新增、或删除）。
• 不可重复读：不可重复读发生在一个事务执行多次查询，但每次查询的结果都不同，通常由于另外一个事务在中途做了更新。

MySQL事务的实现

MySQL的事务的四个特性（ACID），是通过InnoDB日志和锁来保证的。

• 事务的隔离性是通过数据库锁的机制实现。
• 事务的持久性是通过Redo Log来实现。
• 事务的原子性和一致性是通过Undo Log实现的。

实现过程：

• 在操作任务数据之前，首先将数据备份到Undo Log中，然后再进行数据的修改操作；
• 出现错误时执行Roll Back，系统可以利用Undo Log恢复到事务开始之前的状态。
• Redo Log是记录新数据的备份，事务提交之前，只将Redo Log持久化即可。
• 系统崩溃时，数据库未持久化，但Redo Log已经持久化，系统可以根据Redo Log将数据恢复并提交。

页和脏页

页：InnoDB是B+树结构，树的每个节点是一个页，在MySQL中，页的大小是16kb，ORA中是8kb。

脏页：内存数据跟磁盘数据页不一致的时候称为这个内存页为“脏页”，一致的称为“干净页”。

正常SQL操作都是写内存和日志，并不会立即同步到磁盘数据，这时候内存和磁盘数据页内容会产生不一致，即脏页；当SQL执行较慢，可能是将脏页同步到磁盘中。

脏页同步

脏页同步实际时机

1、redo log写满时，系统就会停止所有的更新操作，将更新的这部分日志对应的脏页同步到磁盘中，此时所有的更新全部停止，此时写的性能为0，必须等待刷盘一部分脏页后才能更新，这就导致了SQL执行慢，应避免此类情况；

2、系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，则需要先将脏页同步到磁盘，空出来的给其他数据页使用。当淘汰的脏页过多时。会导致查询的响应时间边长。

3、MySQL认为系统空闲时，则会同步一些数据到磁盘。无性能问题。

4、MySQL正常关闭时，会把内存脏页都同步到磁盘中。无性能问题。

刷脏页策略策略

• innodb_io_capacity：redo log中的剩余空间。
• innodb_max_dirty_pages_pct脏页比例上限，默认值是75% 通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的。

redo log（重做日志）

​ InnoDB存储引擎层的日志，redo log是用来实现事务的持久性，即当事务在提交时，必须先将该事务的所有操作日志写到磁盘上的 redo log file进行持久化，这也就是我们常说的 Write Ahead Log 策略（先日志后写数据）。有了redo log，在数据库发生宕机时，即使内存中的数据还没来得及持久化到磁盘上，我们也可以通过redo log完成数据的恢复，这样就避免了数据的丢失。

​ 在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中。可以根据redo log日志进行恢复，也就达到了crash-safe。

​ 即WAL即Write Ahead logging技术，是先写日志，再写磁盘。

​ redo log是循环写的，有write pos和checkpoint两个指针，当write pos追上checkpoint时，没有空间记录redo log，checkpoint就向前推进将脏页刷入磁盘。checkpoint之前表示擦除完了的，即可以进行写的，擦除之前会更新到磁盘中，write pos是指写的位置，当write pos和checkpoint相遇的时候表明redo log已经满了，这个时候数据库停止进行数据库更新语句的执行，转而进行redo log日志同步到磁盘中。

​ 作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

bin log（归档日志）

​ 数据库的（和引擎无关）bin log记录了数据库系统所有的更新操作，主要是用来实现数据恢复和主从复制的。一方面，主从配置的MySQL集群可以利用bin log将主库中的更新操作传递到从库中，以此来实现主从数据的一致性；另一方面，数据库还可以利用bin log来进行数据的恢复。没有crash-safe能力。

​ 作用：用于复制。在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。

binlog是Server层自带的日志模块，binlog是逻辑日志，记录本次修改的原始逻辑，说白了就是SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。通过mysqlbinlog可以解析查看binlog日志。binlog日志文件的格式：statement，row，mixed。

• statement格式的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点也很明显，那就是准确性差，遇到SQL语句中有now()等函数会导致不准确
• row格式的binlog中记录的是数据行的实际数据的变更，优点就是数据记录准确，缺点就是日志文件较大。
• mixed格式的binlog是前面两者的混合模式

业界目前推荐使用的是 row 模式，因为很多情况下对准确性的要求是排在第一位的。

redo log和binlog的区别

• redo log和bin log的产生方式不同。redo log是在物理存储引擎层产生，而bin log是在MySQL数据库的Server层产生的，并且bin log不仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对数据库的更改都会产生bin log。
• redo log和binlog的记录形式不同。MySQL Server层产生的bin log记录的是一种逻辑日志，即通过SQL语句的方式来记录数据库的修改；而InnoDB层产生的redo log是一种物理格式日志，其记录的是对于磁盘中每一个数据页的修改。
• redo log和bin log记录的时间点不同。bin log只是在事务提交完成后进行一次写入，而redo log则是在事务进行中不断地被写入，redo log并不是随着事务提交的顺序进行写入的，这也就是说在redo log 中针对一个事务会有多个不连续的记录日志。
• redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
• binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

​ 注：数据库数据存放的文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer。

bin log和redo log的一致性问题

​ 在MySQL内部，在事务提交时利用两阶段提交（内部XA的两阶段提交）解决了上面提到的bin log和redo log的一致性问题。

（redo log记录事务Prepare，bin log写入并持久化、redo log增加commit 标签）

（先写redo log再写bin log）

• 第一阶段： InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。此阶段对binlog不会有任何操作。
• 第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写bin log（先调用write()将bin log内存日志数据写入文件系统缓存，再调用fsync()将bin log文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。

在第一阶段并没有记录完整的redo log（不包含事务的commit标签）。

在第二阶段记录完binlog后再写入redo log的commit 标签。

以第二阶段中bin log的写入与否作为事务是否成功提交的标志。

崩溃恢复过程

​ 如果数据库在记录此事务的binlog之前和过程中发生crash。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在binlog中也没有此事务的记录，所以从库也不会有此事务的数据修改。

​ 如果数据库在记录此事务的binlog之后发生crash