MySQL性能调优与架构设计【面试题四】

InnoDB引擎的三大特性是什么？

InnoDB的三大特性是：Buffer Pool、自适应Hash索引、双写缓冲区。

自适应Hash索引，InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据。

InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。

Buffer Pool，为了提高访问速度，MySQL预先就分配/准备了许多这样的空间，为的就是与MySQL数据文件中的页做交换，来把数据文件中的页放到事先准备好的内存中。数据的访问是按照页（默认为16KB）的方式从数据文件中读取到 buffer pool中。Buffer Pool按照最少使用算法（LRU），来管理内存中的页。

Buffer Pool实例允许有多个，每个实例都有一个专门的mutex保护。Buffer Pool中缓存的数据页类型有: 索引页、数据页、undo页、插入缓冲（insert buffer)、自适应哈希索引、InnoDB存储的锁信息、数据字典信息（data dictionary)等等。

双写缓冲区，是一个位于系统表空间的存储区域，在写入时，InnoDB先把从缓冲池中的得到的page写入系统表空间的双写缓冲区。之后，再把page写到.ibd数据文件中相应的位置。如果在page写入数据文件的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。

doublewrite是顺序写，开销比较小。所以在正常的情况下, MySQL写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是从doublewrite buffer写到真正的数据文件中。

它的主要作用是为了避免partial
page write（部分页写入）的问题。因为InnoDB的page size一般是16KB，校验和写入到磁盘是以page为单位进行的。而操作系统写文件是以4KB作为单位的，每写一个page，操作系统需要写4个块，中间发生了系统断电或系统崩溃，只有一部分页面是写入成功的。这时page数据出现不一样的情形，从而形成一个"断裂"的page，使数据产生混乱。

redolog和binlog的区别是什么？

答案见下一题

MySQL崩溃后的恢复为什么不用binlog？

1、这两者使用方式不一样

binlog 会记录表所有更改操作，包括更新删除数据，更改表结构等等，主要用于人工恢复数据，而 redo log 对于我们是不可见的，它是 InnoDB 用于保证 crash-safe 能力的，也就是在事务提交后MySQL崩溃的话，可以保证事务的持久性，即事务提交后其更改是永久性的。

一句话概括：binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性。

2、redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。

3、redo log是物理日志，记录的是“在某个数据页上做了什么修改”，恢复的速度更快；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这的c字段加1 ”，binlog有三种日志记录格式Row、SQL、混合模式。

4、redo log是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。

5、最重要的是，当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有。

比如，binlog 记录了两条日志：

给 ID=2 这一行的 c 字段加1

给 ID=2 这一行的 c 字段加1

在记录1入表后，记录2未入表时，数据库
crash。重启后，只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘，哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 ID=2 这行数据来说，都不对。

但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

MySQL如何实现事务的ACID？

参见下个问题。

InnoDB事务是如何通过日志来实现的？

总的来说，事务的原子性是通过 undo
log 来实现的，事务的持久性性是通过 redo log 来实现的，事务的隔离性是通过读写锁+MVCC来实现的。

事务的一致性通过原子性、隔离性、持久性来保证。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。同时一致性也需要应用程序的支持，应用程序在事务里故意写出违反约束的代码，一致性还是无法保证的，例如，转账代码里从A账户扣钱而不给B账户加钱，那一致性还是无法保证。

至于InnoDB事务是如何通过日志来实现的，简单来说，因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo，
然后修改数据页，再记数据页修改的redo。 Redo（里面包括 undo 的修改）
一定要比数据页先持久化到磁盘。

当事务需要回滚时，因为有undo，可以把数据页回滚到前镜像的状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

更详细的回答是：

redo通常是物理日志，记录的是页的物理修改操作，用来恢复提交事务修改的页操作。而undo是逻辑日志，根据每行记录进行记录，用来回滚记录到某个特定的版本。

当事务提交之后会把所有修改信息都会存到redo日志中。redo日志由两部分组成，一个是在内存里的redo log buffer，另一个是在磁盘里的redo log文件。

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

系统重启后读取redo log恢复最新数据。虽然redo
log会在事务提交前做一次磁盘写入，但是这种IO操作相比于buffer pool这种以页（16kb）为管理单位的随机写入，它做的是几个字节的顺序写入，效率要高得多。

redo log buffer中的数据，会在一个合适的时间点刷入到磁盘中。

这个合适的时间点包括：

1、MySQL 正常关闭的时候；

2、MySQL 的后台线程每隔一段时间定时的讲 redo log buffer 刷入到磁盘，默认是每隔 1s 刷一次；

3、当 redo log
buffer 中的日志写入量超过 redo log buffer 内存的一半时，即超过 8MB 时，会触发 redo log buffer 的刷盘；

4、当事务提交时，根据配置的参数 innodb_flush_log_at_trx_commit 来决定是否刷盘。要严格保证数据不丢失，必须得保证 innodb_flush_log_at_trx_commit 配置为 1。

redo log 在进行数据重做时，只有读到了 commit 标识，才会认为这条 redo log 日志是完整的，才会进行数据重做，否则会认为这个 redo log 日志不完整，不会进行数据重做。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行回滚时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

而事务的隔离性，也可以通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，帮助用户实现一致性非锁定读取，这也是MVCC的实现机制的组成部分。

什么是当前读和快照读？

当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。是一种悲观锁的实现。

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC。

什么是MVCC？

MVCC
(Multi-Version Concurrency Control) ，叫做基于多版本的并发控制协议。他是和LBCC（Lock-Based Concurrency
Control）基于锁的并发控制概念是相对的。MVCC是乐观锁的一种实现方式，它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS包括MySQL，都支持了MVCC。

MVCC的底层实现原理是什么？

MVCC实现原理主要是依赖记录中的隐式字段，undo日志
，Read View 来实现的。

MySQL中每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段。DB_TRX_ID是最近修改(修改/插入)事务ID，记录创建这条记录/最后一次修改该记录的事务ID。DB_ROLL_PTR，回滚指针，用于配合undo日志，指向这条记录的上一个版本。

不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，也就是版本链。

事务进行快照读操作的时候产生一个Read
View，记录并维护系统当前活跃事务的ID，因为当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大。

Read View主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本。

RC,RR级别下Read View生成时机的不同，造成RC,RR级别下快照读的结果的不同。RC隔离级别下，是每个快照读都会生成并获取最新的Read View，也就是说事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见。