MySQL-技术专题-innodb存储引擎

1. change buffer

　　可以看做是Insert buffer升级版，当需要插入或更新一个数据页时，对于非聚簇索引，如果该数据页不在内存当中，Innodb在不影响

一致性的前提下，会将更新操作缓存在change buffer中，可以省去从磁盘读取该页的操作，在以一定的频率和情况下与索引页进行

merge合并，这时通常可以把多个更新页的操作合并为一次磁盘读写，这样就大大提高了插入性能。

什么条件下可以使用change buffer？

　　对于唯一索引，所有的插入和更新操作都要判断其唯一性，要判断就需要把数据页加载到内存当中，这是可以直接更新，自然也就不

需要用change buffer，因此，只有非唯一的二级索引可以使用change buffer。

　　虽然名字是buffer，但change buffer不仅有内存副本，也是会写入磁盘空间的。

2. double write

　　二次写，通俗的说，就是为了防止在写入的过程中发生部分写失败(partial page write)而造成的数据丢失，所以在写数据页之前，先将数据写入共享表空间(ibdata)，然后再写数据页，如果出现数据页损坏，可以通过该页的副本来还原该页，然后在进行redo log重做，这个机制就叫做double write。

　　double write 由两部分组成，一部分是内存中2M的doublewrite buffer，另一部分是磁盘上共享表空间中连续的128个页（两个区）也是2M。执行顺序是 加载脏页信息到double write buffer中->分两次，每次1M写入共享表空间->调用fsync同步磁盘。

3. 自适应哈希索引

　　Innodb的索引是B+树，如果树高为3-4层，那么就需要3-4次io操作来进行检索，而Hash查找的时间复杂度为O(1)，即一般只进行一次查询就能定位数据。Innodb会对表上的索引进行监控，如果发现建立哈希索引可以提升性能，则会为这些热点页建立哈希索引。

　　自适应哈希索引不需要开发人员或者DBA介入，完全由Innodb自动调整。对建立的页有一个要求，就是连续一定次数访问模式必须是一样的，如果是联合索引的不同使用方式，则不能触发自适应哈希索引。例如a,b的联合索引，按a查询和按a,b查询虽然使用的都是同一索引，但视为不同使用方式。

　　哈希索引只能用于等值查询，无法进行范围查询。

4. 刷新相邻页

　　Innodb在刷新一个脏页时，会检测该页所在区的所有页，如果是脏页，则会一起进行刷盘。这样的好处是可以合并IO操作，提高性能。缺点是会将不怎么脏的页一起刷入，但是这些页可能很快又会变脏，所以机械硬盘更需要这个特性，固态硬盘的IOPS超高，建议关闭此特性。

二、Innodb的索引

1. B+树和索引

　　B+树是一种平衡多路树，所有的记录节点都会按键值大小的顺序存放在同一层的叶子节点上，各叶子节点中用指针形成双向链表(实际上链表是基于页)。

　　B+树一般被作为索引使用，分为聚簇索引和二级索引，聚簇索引一般可以认为是主键索引，二级索引是普通索引。

• 聚簇索引：非叶子节点保存主键值，叶子节点保存整张表的行记录，也把聚簇索引的叶子节点称为数据页。
• 二级索引：非叶子节点保存索引值，叶子节点保存主键值。

主键索引尽量使用自增ID，占用空间小，可以在一个页中保存更多的主键记录，减少B+树层次，减少IO查询次数。

如果二级索引中包含全部需要返回的列，则被称为覆盖索引。例如select age from table where age = 27其中age字段包含二级索引。

使用二级索引查询时，在索引树上查询到主键ID，还需要在聚簇索引的索引树上再次查询获取数据页，这个行为被称为回表，如果是覆盖索引查询的话，则可以省略回表的过程，从而提高性能。

索引属性Cardinality，代表了这个索引上不重复值的数量，实际应用中，Cardinality应该尽可能的接近于行数，如果这个值太小，说明索引字段的值重复的非常多，考虑是否可以去掉该索引。

Cardinality的统计是一个取样估计值而非准确值(随机取八个叶子节点，所包含的记录数量平均值乘以叶子节点总数)，如果差别很多，可以使用analysis table命令进行更新。

联合索引按照由左到右顺序进行匹配

2. 索引的优化

　　Multi-Range Read优化

　　MMR优化目的是为了减少磁盘的随机访问。在查询二级索引时，先对查询到的结果按照主键进行排序，再去聚簇索引进行回表。

MMR优化还会对范围查询进行拆分，并且会在执行计划中的Extra列显示Using MMR(P224)

Index Condition Pushdown优化

　　ICP优化在进行索引查询，在取出索引的同时，判断是否能进行where条件过滤，换一种说法就是讲where部分放到了存储引擎层。之

前的MySQL版本(5.6之前)需要首先根据索引查出记录，再进行where过滤。

当使用ICP优化是，执行计划中列Extra看到Using index condition提示。

三、锁

latch与lock

latch和lock最大的区别在于，latch是为了保护线程并发过程的资源，lock则是用来保护事务的锁。这里主要讨论后者。

1 Innodb的锁

1.1 锁的类型

行级锁

　　Innodb存储引擎有两种行级锁

• 共享锁(S Lock)：又称读锁
• 排它锁(X Lock)：又称写锁

 

意向锁

• 意向共享锁(IS Lock)：事务想要获得一张表中某几行的共享锁。
• 意向排它锁(IX Lock)：事务想要获得一张表中某几行的排他锁。

意向锁是表级锁，意向锁之间不会发生冲突，并且不会阻塞除全表扫以外的任何请求。

以上的排它共享指的是表锁，意向锁不会与行级锁互斥。

意向锁的作用

　　当一个事务要求加表锁时，需要扫描全表去发现有无被锁定的行，但是有了意向锁，可以省略很多扫描过程，直接发现互斥关系。

1.2 快照读和当前读

快照读

　　快照读又叫一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制的方式来读取当前执行时间数据库中行的数据。并不是所有事务隔离级别下都会采用快照读，仅在事务隔离级别为RR和RC时起作用。

快照读在RR和RC下表现不同，RR事务隔离级别下，快照读总是读取事务开始时的数据版本。而在RC级别下，快照读总是会读取被锁定行的最新一份快照数据。

快照读是不需要上锁的，因此并发性能很好。

快照读使用undo log实现

当前读

　　当前读又被称为一致性锁定读(consistent locking read)，顾名思义，是一种加锁读取的模式。适用于以下两个语句。

select …… for update

select …… lock in share mode

　　select …… for update会对行记录加上X锁，其他事务不能对已锁定的行记录加上任何锁。

　　select …… lock in share mode会对行记录加上S锁，其他事务可以对锁定航加S锁，但加X锁会被阻塞。

1.3 行锁的3种表现形式

InnoDB的行锁有3中算法，分别是：

• Record Lock：单行记录上的锁。
• Gap Lock：间隙锁，锁定一个范围，不包括记录本身。
• Next-Key Lock：邻键锁，可以视为行锁+间隙锁，锁定记录本身并锁定一个范围，范围为左开右闭。

Next-Key Lock可以看做是加锁的基本单位，查找中访问到的元素都会加上前开后闭的Next-Key Lock，但又以下两种情况下会进行优化。

• 当在索引上查询条件为等值时，给唯一索引加锁时next-key lock会退化为行锁。
• 当在索引上查询条件为等值时，向右遍历时遇到最后一个值不满足等值条件时，next-key lock退化为gap lock。

解决幻读问题

幻读问题是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL可能返回之前不存在的行。

幻读演示

表t由1、2、5三行记录组成，会话A在一次事务查询过程中，会话B插入一条数据a=5并提交，导致会话A两次查询的结果不一致，违反了事务的隔离性，及当前事务可以看到其他事务的结果。

 

在RR的事务隔离级别下，InnoDB引入了Gap Lock和Next-Key Lock来解决幻读问题。对于上述的sql语句select * from t where a>2 for update;，锁住的不是5这个单个值，而是对(2,+∞)这个范围加了X锁，因此任何对于这个范围的插入都是不被允许的，从而避免幻读问题。因此要展示幻读问题，需要将事务隔离级别设为RC。

四、Innodb事务的实现

　　事务的隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。

　　undo并不是redo的逆过程，两者都可看做是一个恢复操作，redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。

redo通常是物理日志，记录的是页的物理修改操作。

undo是逻辑日志，根据每行记录进行记录。

1）redo

1. 基本概念

　　redo log用来实现事务持久性，由两部分组成，一部分是内存中的重做日志缓冲，是易失的，另一部分是重做日志文件，是持久的。

　　Force Log at Commit

　　当事务提交(commit)时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的commit操作完成才算完成。这里的日

志指的是重做日志，在Innodb存储引擎当中，由redo log和undo log两部分组成。redo log用来保证事务的持久性，undo log用来帮助

回滚事务及实现MVCC功能。

redo log基本都是顺序写，数据库运行时不需要读取redo log。而undo log时需要随机读写的。

参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略。

1(默认)，表示事务提交时必须调用一次fsync操作。还可设为0和2,0表示事务提交时不进行写入重做日志操作，这个操作仅

在master thread中完成，而在master thread中每1秒会进行一次重做日志文件的fsync操作。

2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。设为0和2可以提高效

率，但是失去了事务ACID的特性。

binlog和重做日志的区别

1. 首先，重做日志是在引擎层产生，而binlog是在mysql server层产生，而且二进制文件不仅仅针对与Innodb引擎，任何存储引擎对于数据库的修改都会产生binlog。
2. 其次，两种日志记录的内容形式不同。MySQL数据库的binlog是一种逻辑日志，其记录的是对应的sql语句。而Innodb的重做日志是物理格式，记录的是每个页的修改。
3. 此外，两种日志写入磁盘的时间点不同，binlog只在事务提交完成后进行一次写入。而重做日志在事务进行中不断被写入，表现为日志并不是随事务的提交顺序进行写入的。

2. log block

　　在Innodb中，重做日志都是以512字节进行存储的。这意味着重做日志文件和重做日志缓存都是以块(block)为单位进行保存的，称之

为重做日志块(redo log block)，每块的大小为512字节。

　　如果一个页产生的日志数量大于512字节，那么需要分割为多个日志块进行存储。

　　由于日志块和磁盘扇区大小一样，都是512字节，因此不需要double write就可以保证原子性。

3. log group

       log group 为重做日志组，其中有多个重做日志文件。log group 保存的是之前在 log buffer 中保存的 log block，因此也是根据块来进行物理存储管理，每个块也是512字节。在InnoDB存储引擎运行过程中，log buffer 根据一定的规则将内存中的 log block 刷新到磁盘。这个规则是：

• 事务提交时
• 当 log buffer 中有一半的内存空间已经被使用时
• log checkpoint 时

redo log file被写满时，会接着写入下一个redo log file，其使用方式为 round-robin.

每个redo log file的前2k空间部分不保存东西，对于每个log group的第一个redo log file的前2k空间保存了四个512字节大小的块

 

4. LSN

LSN是 Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB中，LSN占用8字节，并且单调递增。LSN表示的含义有：

• 重做日志的总量
• checkpoint的位置
• 页的版本

LSN不仅存在于重做日志中，还存在于每个页中。在每个页的头部，有一个值FIL_PAGE_LSN记录了改业的LSN。在页中，LSN表示改业最后刷新时LSN的大小。因为重做日志记录的是每个页的日志，因此页中的LSN用来判断页是否需要进行回复操作。例如，页P1的LSN是10000，而数据库启动时，InnoDB检测到写入重做日志中的LSN为13000，并且该事务已提交，name数据库需要进行恢复操作，将重做日志应用到P1页中。同样的对于重做日志中LSN晓玉P1页的LSN，不需要进行重做，因为P1页中的LSN表示页已经被刷新到该位置。

用户可以通过show engine innodb status查看LSN的情况

...
---
LOG
---
Log sequence number 1133058903319
Log flushed up to   1133058888909
Pages flushed up to 1133058887714
Last checkpoint at  1133058887714
0 pending log writes, 0 pending chkp writes
61923710 log i/o's done, 4.61 log i/o's/second
...

2）undo

1. 基本概念

　　在数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样需要回滚时可以利用undo信息将数据回滚到

修改之前的亚子。

　　undo与redo不同，不存放在重做日志文件中，而是存放在数据库内部的一个特殊段中，被称为undo段（undo segment）。undo段位

于共享表空间当中。

　　undo并不是把数据库物理的恢复到以前的样子，undo是一个逻辑过程，例如对于insert就是用一条delete，update会使用一个相反的

update，将修改前的行放回去。

　　除了回滚操作，undo的另一个作用就是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，

若该记录已经被其他事务占用，当前事务可以通过读取之前的行版本信息，以此实现非锁定读取。

　　最后一点，undo也会生成redo信息，也就是undo log的产生会伴随着redo log产生，这是因为undo log也需要持久性的保护。

2. undo存储管理

　　InnoDB存储引擎对undo的管理同样适用段的方式，但这个段和以前的段有所不同。首先InnoDB有rollback segment，每个回滚段中

记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请。每个undo log segment可以支持一个快照读事务。

InnoDB 1.1之前只有一个rollback segment，所以只支持1024个同时在线的事务，1.1版本开始支持最大128个rollback segment。1.2版本

开始，可以通过参数来配置rollback segment文件所在路径、rollback segment个数等。

需要特别注意的是，事务在undo log segment分配页并写入undo log的这个过程同样需要写入重做日志。当事务提交时，InnoDB存储引擎

会做两件事情：

• 将undo log放入列表中，以供之后的purge操作
• 判断undo log所在的页是否可以重用，若可以分配给下个事务使用

事务提交后并不能马上删除undo log及undo log所在的页，因为可能有其他事务需要通过undo log来得到行记录之前的版本。所以事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断。

insert的undo log可以在事务结束时直接删除，而不需要加入链表中等待purge，因为由于事务隔离性的要求，只有本事务可以看到。

delete并没有真正删除，只是增加了删除标记，等待purge时最终删除。

update更新非主键时，增加一条更新的undo log，更新主键时，其实分两步进行，先将原记录标记为已删除，之后插入一条新的记录，因此会对应两条undo log。

3）purge

如上一小节所述，delete和update操作可能并不直接删除原有的数据。purge操作用来最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理。因为这时其他事务可能正在引用这一行，所以InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来进行判断。如果该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。

4）group commit

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志都已经写入磁盘。然而磁盘的fsync性能是有限的，为了提高磁盘的fsync效率，当前数据库都提供了group commit功能，即以此fsync可以刷新确保多个事务日志被写入文件。对于InnoDB存储引擎来说，事务提交时会进行两个阶段的操作：

1. 修改内存中事务对应的信息，并且将日志写入重做日志缓冲。
2. 调用fsync将确保日志都从重做日志缓冲写入磁盘。

步骤2 相对于步骤1 是一个缓慢的过程，因为存储引擎要和磁盘打交道。担当由事务进行这个过程时，其他事务可以进行步骤1 的操作，正在提交的事务完成提交操作后，再次进行步骤2 ，可以建多个事务的重做日志通过一次fsync刷新到磁盘，这样就大大减少了磁盘的压力，从而提高了数据库的整体性能，对于写入更新频繁的操作，group commit的效果尤为明显。

在InnoDB1.2之前，开启binlog之后，InnoDB的group commit会失效，导致性能下降，从而导致性能下降。

MySQL 5.6 采用BLGC（Binary Log Group Commit）来解决这个问题，不但重做日志进行组提交，binlog也采用了组提交，此外还移除了原先的锁prepare_commit_mutex，大大提高了整体性能。

多个并发提交的事务在写redo log或binlog前会被加入到一个队列中，队列头部的事务所在的线程称为leader线程，其它事务所在的线程称为follower线程：

• Flush阶段：leader线程负责为队列中所有的事务进行写binlog操作（写入缓存），此时，所有的follower线程处于等待状态，
• Sync阶段：leader线程调用一次fsync操作，将binlog持久化
• Commit阶段：通知follower线程可以继续往下执行（通知InnoDB把redo log刷盘）。

可以通过

• binlog_group_commit_sync_delay=N：在等待N 微秒后，进行binlog刷盘操作
• binlog_group_commit_sync_no_delay_count=N:如果队列中的事务数达到N个，就忽视binlog_group_commit_sync_delay的设置，直接开始刷盘

来进行配置。

事务的隔离级别

SQL标准定义的四个隔离级别

Read Uncommitted

Read Committed

Repeartable Read

Serializable

InnoDB默认的隔离级别是Repeatable Read，在RR隔离级别下，InnoDB使用Next-Key Lock来避免幻读。因此，InnoDB的RR级别其实已经实现了SQL标准Serializable级别的要求。

在RC隔离级别下，InnoDB不会使用Gap Lock锁算法。