mysql面试用

MySQL服务器逻辑架构从上往下可以分为三层：

1. 第一层：处理客户端连接、授权认证等。
2. 第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。
3. 第三层：存储引擎，负责MySQL中数据的存储和提取，MySQL中服务器层不管理事务，事务是由存储引擎实现的。

ACID是衡量事务的四个特性：

1、原子性（Atomicity，或称不可分割性）

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。实现原理:undo log

2、一致性（Consistency）

3、持久性（Durability）

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的，实现原理:redo log

4、隔离性（Isolation，非常重要）

隔离性研究的是不同事务之间的相互影响，隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰

mysql日志

MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志。
InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)，其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。

undo log

undo log主要分为两种：

1. insert undo log:代表事务在insert新记录时产生的undo log,只在事务回滚时需要，并且在事务提交后可以被立即丢弃
2. update undo log:事务在进行update或delete时产生的undo log。不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

redo log

Buffer Pool
读的时候：Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool
写的时候：当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）
修改的时候：把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，定时刷到磁盘中，在还没刷的时候，buffer pool中的数据页就与磁盘上的数据页内容是不一致。问题当mysql宕机的时候就会有问题

redo log
针对上面的问题，就引入了redo log。当数据修改：除了修改Buffer Pool中的数据，还会在redo log记录这次操作，当事务提交时，会调用fsync接口对redo log进行刷盘。

MySQL宕机，重启时可以读取redo log中的数据，通过redo log找到需要重新刷新到磁盘文件的记录。

redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

redo log与bin log

MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复

二者是有着根本的不同，保证MySQL宕机也不会影响持久性，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制

隔离性实现原理（Isolation）

“一个事务”写操作对“另一个事务”写操作的影响：锁机制保证隔离性
“一个事务”写操作对“另一个事务”读操作的影响：MVCC保证隔离性

1、隔离性实现原理-锁机制：

锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。
维度区分：行锁与表锁，InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁
专业词：排它锁(写锁)，共享锁(读锁)

2、脏读、不可重复读和幻读

1. 脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读
2. 不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
3. 幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了

3、事务隔离级别

读未提交，读已提交，可重复读,可串行化。隔离级别依次升高。

1、读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少

2、可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少

3、因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（RR)

InnoDB默认的隔离级别是可重复读（RR),需要注意的是，在SQL标准中，RR是无法避免幻读问题的，但是InnoDB实现的RR避免了幻读问题。
InnoDB的隔离级别-可重复读（RR)解决脏读、不可重复读、幻读等问题，使用的是MVCC

4、MVCC

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

当前读:select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读,就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
快照读:像不加锁的select操作就是快照读,不加锁的非阻塞读,即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念

MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

1、隐式字段
每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

1. DB_TRX_ID：6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID。实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了
2. DB_ROLL_PTR: 7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
3. DB_ROW_ID: 6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

如上图，DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID,而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

2、undo日志

3、Read View(读视图)

reds View总结：

Read View是事务开启时，当前所有事务的一个集合，这个数据结构中存储了当前Read View中最大的ID及最小的ID

1. DB_TRX_ID：最近修改(修改/插入)事务ID：记录创建这条记录，最后一次修改该记录的事务ID，要数据提交了，表中的DB_TRX_ID才会写入。
2. trx_list：一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID集合
3. up_limit_id：记录当前trx_list列表中事务ID最小的ID
4. low_limit_id：ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1，不是当前，是整个Read View里面出现过最大的事务Id+1,不过往往也和当前事务活跃事务Id的最大值+1相等

处理流程:

db_trx_id表示当前表中最新记录的事务Id

1、如果db_trx_id<up_limit_id 也就小于当时活跃事务列表中最小的事务id,说明事务db_trx_id的数据在事务创建Read View时都已经提交，这条数据是可见。直接返回该条数据的内容
if (trx_id < up_limit_id) 返回当前db_trx_id所对应的内容

2、如果db_trx_id >= low_limit_id，代表DB_TRX_ID所指向的这条数据，在Read View生成后才出现的，那对当前事务肯定不可见，那就要查询这个undo_log,找到当前事务所指向的上一条内容，通过上一条内容的事务Id，和活跃线程事务Id对比，直到满足数据可见性二要素

if (trx_id >= low_limit_id) 不可见

3、数据的事务Id db_trx_id在low_limit_id和up_limit_id之间，则查找该记录的db_trx_id是否在自己事务的read_view->trx_ids列表中
3.1、如果在：则该记录的当前版本不可见，那就通过当前事务Id去undo_log里面往上找，先找第一条，拿到事务Id和当前活跃事务Id对比，直到满足数据可见性二要素
3.2、如果不在：则该记录的是可见

数据可见的二要素：
1、小于up_limit_id
2、属于up_limit_id和low_limit_id之间，但是read_view又不能包含db_trx_id

面试题1：跳转
面试题2：跳转

本文转载：mysql事务原理：https://www.cnblogs.com/kismetv/p/10331633.html 超详细

本文转载：MVCC的原理：https://www.cnblogs.com/xuwc/p/13873611.html 超详细