InnoDB存储引擎体系架构

InnoDB存储引擎的具体架构如下图所示：

• 上半部分是实例层（计算层），位于内存中；
• 下半部分是物理层，位于文件系统中。

一、实例层（线程+内存）

1、后台线程

InnoDB 的线程结构主要分为主线程结构、I/O线程结构和其他线程结构。

• 主要作用
  • 刷新内存池中的数据，保证缓冲池中的内存缓存是最新的数据
  • 将已经修改的数据文件刷新到磁盘文件，同时保障在数据库发生异常的情况下 InnoDB 能恢复到正常运行状态。

1.1、Master Thread 主线程

• 核心线程，优先级最高，调度其他各线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，触发检查点，合并插入缓冲（INSERT BUFFER），日志缓冲刷新到磁盘、Undo页的回收等。
• 主线程内部有4个循环：主循环（loop）、后台循环（backgroud loop）、刷新循环（flush loop）和暂定循环（suspend loop），其中最主要的是主循环（loop）。

在loop主循环中又包含两种操作，分别为每1s和每10s的操作。

每1s的操作

• 日志刷新缓冲到磁盘（这个操作总是被执行，即使这个事务还没有提交）。
• 最多可能刷新100个脏页到磁盘。
• 执行合并插入缓冲到磁盘。
• 产生 checkpoint。
• 清除无用的 table cache。
• 如果当前没有用户活动，就可能切换到 background loop。

每10s的操作

• 日志刷新缓冲到磁盘（总是）。
• 最多可能刷新100个脏页到磁盘。
• 执行合并插入缓冲到磁盘（总是）。
• 删除无用的undo页（总是）。
• 产生checkpoint。

1.2、IO Thread

• 在INNODB存储引擎中大量使用了AIO（Async IO）来处理写IO请求，这样可以极大提高数据库的性能。

• 有四种IO Thread 分别是（1.0.x开始）：

  • write thread：4个(生产16个)
  • read thread：4个 (生产16个)
  • redo log thread：1个（ 负责把日志缓冲中的内容刷新到redo log中）
  • insert buffer thread：1个（负责把Insert Buffer中的内容刷新到磁盘，将对辅助索引页的修改操作从随机变成顺序IO）

• 可以使用innodb_read_io_threads 和 innodb_write_io_threads参数进行设置。（生产环境配置的16）

• 读线程一定小于写线程

INNODB THREAD 数量查询
SHOW VARIABLES LIKE 'innodb_%io_threads'\G;

1.3、Page Cleaner Thread

• 是在INNODB 1.2.x版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。其目的是为减轻 MASTER THREAD 的工作以及对用于查询线程的堵塞，进一步提高性能。
• 将脏数据写入磁盘，脏数据写入磁盘对应的 redo 就可以覆盖，然后达到 redo 循环使用的目的。

# 后台清理线程数，默认1,最大为64，最好保持跟innodb_buffer_pool_instances一致，但是要根据服务器的CPU实际核数调整，生产当前设置是4
# 参考 show global status like '%buffer_pool_wait_free'; 
# 标志着脏页有没有成为系统的性能瓶颈；如果值很大，则需要增加innodb_page_cleaners值，同时增加写线程。
SHOW VARIABLES Like 'innodb_page_cleaners';

innodb_io_capacity

对于刷新到磁盘页的数量，会按该参数来进行控制，规则如下：

1. 在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity；
2. 在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity的25%。

innodb_max_dirty_pages_pct

控制了 Dirty Page 在 Buffer Pool 中所占的比率,生产设置为40

 

1.4、Purge Thread

• 事务被提交后，其所使用的 undolog 可能不再需要，因此需要 PurgeThread 来回收已经使用并分配的 undo 页。从INNODB 1.1版本开始，可以将 purge 操作从 MASTER THREAD 中抽离出来，来减轻 MASTER THEAD 的工作。这样做的目的是为了进行加快undo页的回收，从而提高CPU的利用率以及提升存储引擎的性能。
• 可以支持多个 purge Thread，（生产环境配置的4，最大可以调整至32个），这样做的目的是为了进一步加快 undo 页的回收。同时由于 Purge Thread 需要离散地读取 undo 页，这样也能更进一步利用磁盘的随机读取性能。

SHOW VARIABLES LIKE 'innodb_purge_threads'\G;

1.5、其他线程结构

• errror monitor thread: 负责数据库报错的线程
• lock monitor thread: 负责监控锁的线程

 

2、内存

• per_thread_buffers=(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size+tmp_table_size)*max_connections
  • sort_buffer_size：主要用于SQL语句在内存中的临时排序
  • join_buffer_size: 表连接使用，用于优化索引
  • read_buffer_size: 表顺序扫描的缓冲，
• global_buffers=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size
  • innodb_buffer_pool_size： 缓存 Innodb 表的数据、索引，以及数据字典等信息。
  • innodb_log_buffer_size：事务在内存中的缓存，即 redo log buffer 的大小。
• total_memory=global_buffers+per_thread_buffers

线程缓存随着连接关闭而释放其实不太对。MySQL并不会把这部分缓存还给操作系统，而只是在 MySQL内部释放，然后重复使用。我把这个现象称为内存高水位现象。

为啥经常出现 MySQL 实际占用物理内存比 InnoDB_Buffer_Pool 的配置高很多而且不释放的现象？

其实多占用的内存大多都是被内存分配器吃掉了。为了更高效的内存管理，内存分配器通常都会占着很多内存不释放；当然还有另一部分原因是内存碎片，会导致内存分配器无法重新利用之前所申请的内存。

不过内存分配器并非永远不释放内存，而是需要达到某个阈值，它才会释放一部分内存给操作系统。

InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作：

• 维护所有进程/线程需要访问的多个内部数据结构
• 缓存磁盘上的数据，方便快速的读取，同时在对磁盘文件的数据修改之前在这里缓存。
• 重做日志(redo log)缓冲。

2.1、缓冲池（Innodb buffer pool）

• InnoDB存储引擎是在磁盘按照页的方式进行管理，是基于磁盘的数据库系统。需要使用缓冲池技术提高数据库的整体性能。

• 缓冲池就是一块内存区域，对数据页的读取先在缓存找，找不到去磁盘加载。 对页的修改，也是先写入缓冲池中的页，后续通过Checkpoint的机制刷新回磁盘。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'\G;

• 一般设置Buffer Pool为物理内存的60%~80%，剩余内存用于session产生的sort和join等，以及运维管理使用。 可以根据 show global status like '%buffer_pool_wait%'的值来看是否需要调整Buffer Pool大小。

• 每个缓冲页对应的控制信息都对应存储在一个控制块中，每个控制开大约占缓冲页大小的5%,innodb_buffer_pool_size并不包含这部分控制块的内存空间大小，也就是说Innodb在为Buffer Pool向操作系统申请连续的内存空间时，这片连续的内存空间会比innodb_buffer_pool_size的值大5%左右。

• 缓冲池中缓存的数据类型有：

索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary），其中占主要部分的是数据页和索引页。

• 从1.0.X版本开始，允许有多个缓冲池实例（参数innodb_buffer_pool_size）。每个页根据哈希值平均分配到不同缓冲池实例中。好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。

 

2.1.1、插入缓冲（Insert buffer part of buffer pool）

Insert buffer

Insert buffer使用需满足以下两个条件：

• 索引是辅助索引（secondary index）;
• 索引不是唯一的（unique）。

• 对于非聚集索引的插入或更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；如不在，则先把 对二级索引的数据操作缓存到插入缓冲(Insert buffer) 。

• 然后再以一定的频率和情况进行Insert buffer和辅助索引叶子节点的merge(合并)操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

Change buffer

Change buffer是Insert buffer的升级，Innodb存储引擎可以对DML操作 ———— INSERT、DELETE、UPDATE都进行缓冲，它们分别是：Insert buffer、Delete buffer、Purge buffer。

show variables like 'innodb_change_buffer_max_size';
# 该值默认为25，表示最多使用1/4的缓冲池空间，该参数最大有限值为50。

Merge Insert buffer

Insert buffer中的记录何时合并（merge）到真正的辅助索引中：

• 辅助索引页被读取到缓冲池时；
• Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时；
• Master Thread

Insert Buffer适用的业务场景

（1）数据库大部分是非唯一索引；

（2）业务是写多读少，或者不是写后立刻读取；

可以使用写缓冲，将原本每次写入都需要进行磁盘IO的SQL，优化定期批量写磁盘。

（1）数据库都是唯一索引；

（2）或者，写入一个数据后，会立刻读取它；

这两类场景，在写操作进行时（进行后），本来就要进行进行页读取，本来相应页面就要入缓冲池，此时写缓存反倒成了负担，增加了复杂度。

 

2.1.2、自适应哈希索引（Adaptive Hash Index）

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。

自适应hash索引只适合搜索等值的查询，如select * from table where index_col=‘xxx’，而对于其他查找类型，如范围查找，是不能使用的。

 

2.1.3、InnoDB内存管理模式

LRU List、Free List（空闲页列表）和 Flush List（脏页列表）

每个buffer分为3种状态：

• free buffer: 此状态下的buffer从未被使用。
• clean buffer: 内存中buffer里面的数据和磁盘page的数据一致
• dirty buffer：内存中新写入的数据还没有刷新到磁盘，跟磁盘中数据不一致

由三种不同的buffer状态衍生出了三条链表：

• free list: 空闲缓存块列表，判断free buffer是否够用，如果不够用了，就会从lru list 和 flush list链表中释放free buffer，以获得新的空闲buffer
• lru list：Innodb正在使用的缓存块，包含所有读入内存的数据页
• flush list：是需要刷新到磁盘的缓存块列表，包含被修改过的脏页，参数innodb_lru_san_depth,如果你发现你的系统中free list不足，总是需要驱逐脏页来获取空闲的block时，可以适当调大innodb_lru_scan_depth 。该参数表示从每个buffer pool instance的lru上扫描的深度，调大该值有助于多释放些空闲页，避免用户线程去做single page flush；

 

2.1.4、LRU算法

Latest Recent Used，最少使用算法，即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的末端，当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页，进行内存管理。

算法说明

新读取的页会放入缓冲池中点，也即默认情况下所有的新读取的页都会被插入到尾部开始的3/8位置处。在后面的第一次命中（被访问时）的页会被移动到列表的头部。因此，那些读入缓存但是后面从来不会被访问的页也从不会被放入列表的头部，也就会在后面被从缓冲池淘汰。

防止一次性的大查询（比如使用mysqldump或者没有条件的select查询）读取的数据页污染整个LRU列表,影响整体的事务命中率。

innodb_old_blocks_pct

• LRU列表分为两部分,young和old,它最新读取的页不是放在列表的首部，而是放在midpoint的位置。midpoint位置可由参数innodb_old_blocks_pct控制，默认是37，控制旧页子表占整个缓冲池列表的比例，在3/8左右。

SHOW VARIABLES LIKE 'innodb_old_blocks_pct'\G;

innodb_old_blocks_time

表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。所以在执行上述类型的SQL时候，可以先设置这个参数保证原来的LRU列表热点数据不被刷出。

即对某个处于old区的缓冲页进行第一次访问时，就在它对应的控制块中记录下这个访问时间，如果后续的访问时间与第一次访问的时间在innodb_old_blocks_time这个时间内，那么这个区域就保留old区，否则就将它移动到young区域的头部。

由于优化机制的存在，即一个正常访问的数据页，要进入 young 区域，需要隔 1 秒后再次被访问到

针对新插入的数据页需要做二次判断
如果数据页保持1S以上时间,则推进到young头部；

如果数据页不能保持1S,则依然保留到old区,等待被淘汰

改进优点:防止一次性的大查询读取的数据页污染整个LRU列表,影响整体的事务命中率

更进一步优化LRU链表：
只有被访问的缓冲页位于young区域1/4的后面时，才会移动到LRU链表头部，这样就可以降低调整LRU链表的频率，从而提升性能。

HIT_RATE

• 可以通过以下命令观察LRU列表及Free列表的使用情况,其中一个重要的观察变量 —— Buffer pool hit rate，表示该缓冲池的命中率，通常该值应该不小于95%，小于95%，需要观察是否是由于全表扫描引起的LRU列表被污染的问题。

show engine innodb status\G;
# 查询缓冲池hit命中率
SELECT POOL_ID,HIT_RATE,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG FROM information_schema.INNODB_BUFFER_POOL_STATS;

（1 - innodb_buffer_pool_reads/innodb_buffer_pool_read_request）* 100

• 脏页比率计算

(Innodb_buffer_pool_pages_dirty)/(1+Innodb_buffer_pool_pages_data+Innodb_buffer_pool_pages_free)

• 在LRU列表中的页被修改后，被称为脏页（dirty page）,即缓冲池中的页和磁盘上的页产生了不一致，这时数据库会通过CHECKPOINT机制将脏页刷回磁盘，而Flush列表中的页即为脏页列表。
  Modified db pages 为脏页的数量

• Block Nested-Loop Join影响

在使用 Block Nested-Loop Join(BNL) 算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致 IO 压力大以外，还会对 buffer poll 产生严重的影响。

如果了解 InnoDB 的 LRU 算法就会知道，由于 InnoDB 对 Bufffer Pool 的 LRU 算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在 old 区域。如果 1 秒之后这个数据页不再被访问了，就不会被移动到 LRU 链表头部，这样对 Buffer Pool 的命中率影响就不大。

但是，如果一个使用 BNL 算法的 join 语句，多次扫描一个冷表，而且这个语句执行时间超过 1 秒，就会在再次扫描冷表的时候，把冷表的数据页移到 LRU 链表头部。这种情况对应的，是冷表的数据量小于整个 Buffer Pool 的 3/8，能够完全放入 old 区域的情况。如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入 young 区域。

由于优化机制的存在，一个正常访问的数据页，要进入 young 区域，需要隔 1 秒后再次被访问到。但是，由于我们的 join 语句在循环读磁盘和淘汰内存页，进入 old 区域的数据页，很可能在 1 秒之内就被淘汰了。这样，就会导致这个 MySQL 实例的 Buffer Pool 在这段时间内，young 区域的数据页没有被合理地淘汰。

也就是说，这两种情况都会影响 Buffer Pool 的正常运作。 大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响，你可以考虑增大 join_buffer_size 的值，减少对被驱动表的扫描次数。

 

2.2、重做日志缓冲（redo log buffer）

InnoDB存储引擎首先将重做日志先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。innodb_log_buffer_size一般不用设置为很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小即可。生产设置为64MB。

SHOW VARIABLES LIKE 'innodb_log_buffer_size'\G;

重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：

1. Master Thread 每1秒将重做日志刷新到重做日志文件;
2. 每个事务提交时，通过innodb_flush_log_at_trx_commit参数来控制，该参数有3个值，分别是0、1、2：

• 0的含义：redo log buffer 每隔1s会将redo log buffer中的数据写入redo log文件，
• 1的含义：每次事务提交时，都会触发redo log thread将日志缓冲中的数据写入文件，并“flush”到磁盘，该设置下是最安全的模式，保证数据库在主机断电、OS crash下不会丢失任何已提交的数据。
• 2的含义：每次事务提交，都会把redo log buffer的数据写入redo log 文件，但是不会同时刷新到磁盘。

三种模式下，0是性能最好，但是不太安全，MySQL进程一旦崩溃会导致丢失一秒的数据，1是安全性最高，但数据库性能最慢，2是介于两者之间，由于事务已经刷新到OS cache，数据库宕机不会丢数据，如果服务器宕机，就会丢失最近1s的事务。
3. 当重做日志缓冲池剩余空间小于1/2时。

 

2.3、double write buffer

主要解决由于宕机引起的物理写入操作中断，数据页不完整的问题。

Insert buffer带给Innodb存储引擎的是性能上的提升，那么double write(两次写)带给InnoDB存储引擎的是数据页的可靠性。

double write 由两部分组成，一部分是内存中的double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个区（extent），大小同样为2MB。

在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的double write buffer，之后通过double write buffer再分2次，每次 1MB 顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为double write是连续的，因此这个过程是顺序写入的，开销不是很大。

在完成double write页的写入后，再将double write buffer中的页写入各个表空间文件中，此时的写入是离散的。

通过以下命令观察double write运行的情况，如果发现系统在高峰时Innodb_dblwr_pages_written:Innodb_dblwr_writes远小于 64:1，说明系统写入压力并不是很高，Innodb_buffer_pool_pages_flushed表示当前从缓冲池刷新到磁盘页的数量，在默认情况下所有页的刷新首先都需要放入到doublewrite中，因此该变量应该基本与Innodb_dblwr_pages_written一致：

show status  like 'Innodb_dblwr%';
show status like 'Innodb_buffer_pool_pages_flushed%';

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，Innodb存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。
 

2.4、额外的内存池

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的。在对一些数据本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

因此，在申请了很大的InnoDB缓冲池时，也应考虑相应的增加这个值。

 

2.5、Innodb内存优化

2.5.1、innodb_buffer_pool_size 的设置

在保证操作系统及其他程序有足够的内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问 Innodb 表需要的磁盘 I/O 就越少，性能就越高，在一个专用的数据库服务器上，可以将80%的物理内存分配给InnoDB buffer pool,但一定要注意避免设置过大而导致页交换。

2.5.2、调整 old sublist 大小

在 LRU list 中，old sublist 的比例由系统参数 innodb_old_blocks_pct决定，其取值范围是 5~95，默认值是75（约等于3/4）。

show global variables like '%innodb_old_blocks_pct%';

可以根据 Innodb Monitor 的输出信息来调整 innodb_old_blocks_pct 的值，例如，在没有较大表扫描或索引扫描的情况下，如果 young/s 的值很低，可能需要适当增大 innodb_old_blocks_pct 的值或减小 innodb_old_blocks_time 的值。

 

2.5.3、调整 innodb_old_blocks_time 的设置

innodb_old_blocks_time 参数决定了缓存数据块由 old sublist 转移到 young sublist 的快慢，当一个缓存数据块被插入到midpoint（old sublist）后,至少要在 old sublist 停留超过 innodb_old_blocks_time（ms）后，才有可能被转移到 new sublist。

可以根据 Innodb Monitor 的输出信息来调整 innodb_old_blocks_time 的值， 在进行表扫描时，如果 non-youngs/s很低，young/s很高，可能需要适当增大innodb_old_blocks_time 的值，以防止表扫描将真正的热数据淘汰。这个值可以动态调整，如果要进行大的表扫描操作，可以很方便地临时做调整。

2.5.4、调整缓冲池数量，减少内部对缓冲池数据结构的争用

MySQL 内部不同线程对 Innodb 缓存池的访问在某些阶段是互斥的，这种内部竞争也会导致性能问题，尤其是在高并发和 buffer pool 较大的情况下。对于较大的缓存池，适当增大 innodb_buffer_pool_instances 配置参数，可以降低并发导致的内部缓存访问冲突，改善性能。

2.5.5、控制 innodb buffer 刷新，延长数据缓存时间，减缓磁盘 I/O

在 Innodb 找不到干净的可用缓存页或检查点被触发等情况下，Innodb的后台线程就会开始把“脏的缓存页”回写到磁盘文件中，这个过程叫缓存刷新。

Innodb buffer pool的刷新快慢主要取决于两个参数：

• innodb_max_dirty_pages_pct，它控缓存池中脏页的最大比例，默认值是50%，如果脏页的数量达到或者超过该值，Innodb 的后台线程就开始缓存刷新。
• innodb_io_capacity，它代表磁盘的 IO 能力，其值在一定程度上代表磁盘每秒可完成 I/O 的次数，默认值是200，对于固态硬盘和由多个磁盘组成的盘阵，其值可适当加大，对于固态硬盘来说，建议设置成2000或者更高。

若 innodb_buffer_pool_wait_free 增长较快，则说明 Innodb 经常在等待空闲缓存页，如果无法增大缓存池，那么应将 innodb_max_dirty_pages_pct 的值调小，innodb_io_capacity 或者将 innodb_io_capacity 的值调高，以加快脏页的刷新。

2.5.6、Innodb doublewrite

对于要求超高性能，又能容忍极端情况下少量数据的丢失，可以在配置文件中增加 innodb_doublewrite = 0 参数设置来关闭 doublewrite，以尽量满足性能方面要求。

 

2.6、调整用户服务线程排序缓存区

• 如果通过 show global status 看到 sort_merge_passes 的值很大，可以考虑通过调整参数 sort_buffer_size 的值来增大排序缓存区，以改善带有 order by 子句或 group 子句 SQL 的性能。
• 对于无法通过索引进行连接操作的查询，可以尝试通过增大 join_buffer_size 的值来改善性能

不过需要注意的是，sort_buffer 和 join_buffer 都是面向客户服务线程分配的，如果设置过大可能造成内存浪费，甚至导致内存交换。尤其是 join_buffer，如果是多表关联的复杂查询，还可能会分配多个 join buffer，因此，最好的策略是设置较小的全局的 join_buffer_size，面对需要做复杂查询操作连接的session单独设置较大的 join_buffer_size

 

二、物理层

物理层在逻辑上分为

• 系统表空间
• 用户表空间
• Redo日志

物理层在物理上分为

• MySQL是通过文件系统对数据和索引进行存储的。
• MySQL从物理结构上可以分为日志文件和数据索引文件。
• 日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储。

1、系统表空间

系统表空间包括以下内容：

• Innodb数据字典（Innodb相关对象的元数据）
• Double write Buffer
• Change Buffer 磁盘部分
• Undo logs
• 在系统表空间中创建的任何表和索引数据

1.1、Undo log

• undo log主要记录数据的逻辑变化，有两个作用：事务回滚、MVCC
• 用于存放事务修改之前的数据（undo log记录了有关如
• 何撤销事务对聚集索引记录的最新更改信息），基于undo实现了MVCC和一致性非锁定读。
• 在MySQL中，把修改前的数据存放于undo log，通过回滚指针与主数据关联。
  同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。
• MySQL 8.0 默认对 undo 进行了分离操作，即不需要在初始化中手动配置参数，默认会在 MySQL 数据目录中生成两个10 MB 的 undo 表空间 “undo_001” 和 “undo_002”，并且可以在线增加和删除 undo 表空间进行动态扩容和收缩

参数 innodb_rollback_segments 设置每个 undo 表空间的回滚段的数量。
在 MySQL 5.7 这个参数被用来设置所有 Undo 表空间的回滚段数量。最大 128，就是说一个 MySQL 实例最多 128 个回滚段。
注意高能时刻！
MySQL 8.0 放开了这个限制，使得这个参数设置限制在每个表空间。也就是说每个表空间最多 128 个回滚段，可以设置多个表空间！也就解决了 MySQL 5.7 在持续高并发时，事务争抢回滚段不足造成的资源抢占，减少了相关的锁开销。

1.2、MVCC

1.2.1、隐藏列

• 在InnoDB中，每一行都有2个隐藏列DATA_TRX_ID和DATA_ROLL_PTR(如果没有定义主键，则还有个隐藏主键列)：
  • DATA_TRX_ID表示最近修改该行数据的事务ID
  • DATA_ROLL_PTR则表示指向该行回滚段的指针，该行上所有旧的版本，在undo中都通过链表的形式组织，而该值，正式指向undo中该行的历史记录链表
• 整个MVCC的关键就是通过DATA_TRX_ID和DATA_ROLL_PTR这两个隐藏列来实现的。

1.2.2、事务链表

• MySQL中的事务在开始到提交这段过程中，都会被保存到一个叫trx_sys的事务链表中，这是一个基本的链表结构：
  
• 事务链表中保存的都是还未提交的事务，事务一旦被提交，则会被从事务链表中摘除。

1.2.3、ReadView

ReadView说白了就是一个数据结构，在SQL开始的时候被创建。这个数据结构中包含了3个主要的成员：ReadView{low_trx_id, up_trx_id, trx_ids}，在并发情况下，一个事务在启动时，trx_sys链表中存在部分还未提交的事务，那么哪些改变对当前事务是可见的，哪些又是不可见的，这个需要通过ReadView来进行判定，首先来看下ReadView中的3个成员各自代表的意思：

1. low_trx_id表示该SQL启动时，当前事务链表中最大的事务id编号，也就是最近创建的除自身以外最大事务编号；
2. up_trx_id表示该SQL启动时，当前事务链表中最小的事务id编号，也就是当前系统中创建最早但还未提交的事务；
3. trx_ids表示所有事务链表中事务的id集合。

 

• 根据上图所示，所有数据行上DATA_TRX_ID小于up_trx_id的记录，说明修改该行的事务在当前事务开启之前都已经提交完成，所以对当前事务来说，都是可见的。而对于DATA_TRX_ID大于low_trx_id的记录，说明修改该行记录的事务在当前事务之后，所以对于当前事务来说是不可见的。
• 注意，ReadView是与SQL绑定的，而并不是事务，所以即使在同一个事务中，每次SQL启动时构造的ReadView的up_trx_id和low_trx_id也都是不一样的，至于DATA_TRX_ID大于low_trx_id本身出现也只有当多个SQL并发的时候，在一个SQL构造完ReadView之后，另外一个SQL修改了数据后又进行了提交，对于这种情况，数据其实是不可见的。
• 最后，至于位于（up_trx_id, low_trx_id）中间的事务是否可见，这个需要根据不同的事务隔离级别来确定。对于RC的事务隔离级别来说，对于事务执行过程中，已经提交的事务的数据，对当前事务是可见的，也就是说上述图中，当前事务运行过程中，trx1~4中任意一个事务提交，对当前事务来说都是可见的；而对于RR隔离级别来说，事务启动时，已经开始的事务链表中的事务的所有修改都是不可见的，所以在RR级别下，low_trx_id基本保持与up_trx_id相同的值即可。

 

2、用户表空间

• 以.ibd为后缀的文件，文件包含insert buffer的bitmap页、叶子页（这里存储真正的用户数据）、非叶子页。
• Innodb表是索引组织表，采用B+树组织存储，数据都存储在叶子节点中，分支节点（即非叶子页）存储所有分支查找的数据值。

3、Redo log

• Redo是物理日志，记录的是数据页的物理变化

• 重做日志是在崩溃恢复期间使用的基于磁盘的数据结构文件，用于恢复不完整提交事务写入的数据。

• 根据 WAL（Write-Ahead Logging，日志先行）原则，在提交事务时会先使用redo log持久化事务发生修改的部分数据（只要redo log落盘并打上commit标记就表示事务已经持久化） 。

• InnoDB是事务的存储引擎，其通过Force Log at Commit 机制实现事务的持久性。
  即当事务提交时，先将 redo log buffer 写入到 redo log file 进行持久化，待事务的commit操作完成时才算完成。这种做法也被称为 Write-Ahead Log(预先日志持久化):在持久化一个数据页之前，先将内存中相应的日志页持久化。

• MySQL 的内部两阶段提交，是为了解决 binlog 和 redo log 的一致性（在 crash recovery 的过程中, 如果发现某个事务的 redo log 已经完成 prepare 阶段, 但未完成 commit，那么会验证该事务是否在 binlog 中，如存在，则进行提交，否则进行回滚）。

• Redo logs循环使用，当达到一定存储阈值时触发checkpoint刷脏页操作，同时也会在MySQL实例异常宕机后重启，Innodb表数据自动还原恢复过程中使用。

• 当Redo log文件总空间中，检查点位置的LSN和最新写入的LSN差值（checkpoint_age）达到的Redo log总空间的 75% 以后，Innodb会进行异步刷新操作，直到降至75%以下，并释放Redo log的空间；当checkpoint_age达到文件总量大小的 90% 后，会触发同步刷新，此时InnoDB处于挂起状态无法操作。

  • lsn（Log Sequence number）：指当前系统已经写入的redo日志量，包括写入到log buffer 中的redo日志；
  • flushed_to_disk_lsn（Log flushed up to）: 当前系统已经写入磁盘的 redo 日志量；
  • Pages flushed up to 表示flush链表中被最早修改的那个页面对应的oldest_modification属性值
  • checkpoint_lsn（Last checkpoint at）:表示当前系统中可以被覆盖的redo 日志总量是多少

• Redo的整体流程
  

  • 将原始数据从磁盘中读入内存中，修改数据在内存中的拷贝
  • 生成一条redo log并写入redo log buffer，记录的是数据被修改后的值
  • 当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
  • 定期将内存中修改的数据刷新到磁盘中

 

4、存储引擎组件联动举例

以一个 Update 场景加以说明。假设有一个Update语句正在执行：

update test set idx=2 where id = 10;

执行流程入下（这里主要以InnoDB存储引擎体系结构中的组件为主）：

1. 在Server层进行词法解析，解析成MySQL认识的语法，查询什么表、什么字段，并生成查询路径树，选择最优查询路径；
2. 到了InnoDB存储引擎这里，先判断id=10这行数据对应的页是否在缓冲池中，如果不在，则将id=10记录对应的页从datafile中读入InnoDB缓冲池中（如果该页已经在缓冲池中，则省去了读入这一步），并对相关记录加独占锁。

假设不存在，然后通过 B+Tree 读取到磁盘的索引页加载到BP缓冲池中 ，如何加载到BP缓冲池中：

首先 通过 space id 和 page no 哈希计算之后把 索引页加载到指定的 buffer pool instance 中
判断 free list 是否有空闲页可用(Innodb_buffer_pool_pages_free、 Innodb_buffer_pool_wait_free)，没有则淘汰脏页或者lru list的Old页

把数据页 copy到 free list中，然后加载到 lru list的 old区的 midpoint（头部）；

通过二分法查找该页对应的记录，试图给这个SQL涉及到的行记录加上排他锁，过程如下：

如果事务当前记录的行锁被其他事务占用的话，就需要进入锁等待；

进入锁等待之后，同时判断会不会由于自己的加入导致了死锁；

检测到没有锁等待和不会造成死锁之后，行记录加上排他锁。

3. 将idx修改之前的值和对应的主键、事务ID原来的信息写入Undo Tablespace 的回滚段中。
4. 更改缓存页中的数据，填写事务编号，使用回滚指针指向undo log中的修改前的行，并将更新记录和新生成的LSN值（日志序列号）写入Log Buffer中，更新完之后在缓冲池中这个页就是脏页了。
5. 在提交事务时，根据 innodb_flush_log_at_trx_commit的设置，用不同的方式将Log Buffer中的更新记录刷新到redo log中，然后写binglog，写完binlog开始commit（binlog同步到磁盘），binlog同步之后把binlog文件名和position（binglog文件内的位置）也写到redo log中，然后再redo log中写入一个commit标记，那次此时完成这个事务的提交。接下来释放独占锁。
6. 后台I/O线程根据需要择机将缓存中合适的脏页刷新到磁盘数据文件中。当然，在刷新脏页时先要拷贝一份到双写缓冲区中，当双写缓冲区中的文件落盘之后（刷新到共享表空间ibdata），再从缓冲池中把脏页刷新到各个数据文件中。

5、MySQL 8.0新特性

• 将InnoDB表的数据字典和Undo都从共享表ibdata中彻底分离出来了，以前需要ibdata文件中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。
• temporary临时表空间可以配置多个物理文件，而且均为InnoDB存储引擎并能创建索引，这样加快了处理的速度。
• 用户可以像Oracle数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。

 

Checkpoint技术

解决问题

Checkpoint（检查点）技术的目的是解决一下几个问题：

• 缩短数据库的恢复时间;

当数据库宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页已经刷新到磁盘。故数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。

• 缓冲池不够用时，将脏页刷新到磁盘；

当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。

• 重做日志不够用时，刷新脏页。（redo log 空间满了，促进释放 redo log 空间）

在InnoDB存储引擎内部，有两种Checkpoint，分别为：

• Sharp Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数 innodb_fast_shutdown=1

• Fuzzy Checkpoint

 

Fuzzy Checkpoint

在InnoDB存储引擎可能发生以下几种情况的Fuzzy Checkpoint：

• Master Thread Checkpoint
  • 差不多以每隔1秒或每隔10秒的速度，从缓冲池的脏页列表刷新一定比例的页回磁盘。这个过程是异步的，即此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。
  • 相关参数是 innodb_io_capacity
• FLUSH_LRU_LIST Checkpoint
  • InnoDB 存储引擎需要保证 LRU 列表中需要有差不多100个空闲页可供使用，这个检查被放在一个单独的Page Cleaner 线程中进行，通过参数 innodb_lru_scan_depth 控制 LRU 列表中可用页的数量，该值默认是1024。
• Async/Sync Flush Checkpoint
  • 指的是重做日志 redo log 不可用的情况下，这时需要强制将一些页刷新到磁盘，而此时脏页是从脏页列表中选取的。
  • 若将已经写入到重做日志的 LSN 记为 redo_lsn，将已经刷新回磁盘最新页的 LSN 记为 checkpoint_lsn ,则可定义：checkpoint_age = redo_lsn - checkpoint_lsn

异步水位  async_water_mark = 75%*total_redo_log_file_size
同步水位  sync_water_mark = 90%*total_redo_log_file_size

1. 当 checkpoint_age < async_water_mark，不需要刷新任何脏页到磁盘
1. 当 async_water_mark < checkpoint_age < sync_water_mark时触发Async Flush，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age < async_water_mark

• Dirty Page too much Checkpoint
  • 脏页数量太多，导致InnoDB存储引擎强制进行Checkpoint,用参数 innodb_max_dirty_pages_pct控制，默认值是75。（生产是40，建议是25%~50%，调低是为了避免后期脏页刷新影响整体数据库的TPS性能）,MySQL 8.0中改参数的默认值是90%

 

LSN

LSN全称是：log sequence number。

它就是一个序列号。并且表空间中的数据页、缓存页、内存中的rodo log、磁盘中的redo log以及checkponit都有LSN标记。

LSN 有啥用呢？比如MySQL重启时会对比数据页的LSN和redo log的LSN的大小，如果前者的LSN比后者小。说明数据页中缺失了一部分数据。如果满足其他数据恢复的条件，MySQL就会将LSN之后的这些redo 进行一次回放，完成数据的恢复。

举个需要重做的例子：假设你使用的是MySQL集群，从库通过binlog同步主库的数据。
理论上：你开启了事物，然后一顿操作然后提交事物。在你操作的过程中MySQL会为你记录undo log、redo log parpare、binlog、redo log commit。（两阶段提交）
然而不幸的是，当MySQL写完binlog、且未来得及写 redo log commit完成的事物最终的提交就挂了。
那MYSQL重启，由于未来得及commit，脏数据页没有刷新到磁盘上，所以重启时得到的数据时不准确的，但是，实际上MySQL会根据方才的redo log重做。因为binlog已经写完了，那就意味着从库已经完成了数据的同步。如果它不重做的话，它相对于从库就缺失了一部分数据，导致主从数据不一致。

show engine innodb status\G;show engine innodb status\G

Innodb关键特性

• 支持事务：主要面向在线事务处理（OLTP）应用。特点是:
  • 行锁设计
  • 支持外键
  • 支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。

• 数据放在一个逻辑的表空间，这个表空间像黑盒一样由INNODB存储引擎自身进行管理。它可以将每个INNODB存储引擎的表单独存放到一个独立的ibd文件中。
• 通过使用多版本并发控制(MVCC)来获得高并发性
• 实现了SQL标准的四种隔离级别。默认为REPEATABLE(可重复度)级别。使用一种被称为next-keylocking的策略来避免幻读现象的产生。
• 提供插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用功能。
• 表中数据采用聚集的方式存放，因此每场表的储存都是按主键的顺序进行存放，如果没有显式指定主键，则会为每一行生成一个6字节的ROWID，并作为主键。

查看数据库锁支持的存储引擎

SHOW ENGINES\G;

连接MySQL的几种方式：
TCP/IP、命名管道和共享内存、UNIX域套接字。

异步IO（Async IO）

在Innodb存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，即磁盘的写入操作则全部由AIO完成。

AIO的另一个优势是可以进行IO Merge操作，也就是将多个IO合并成1个IO，这样可以提高IOPS的性能。

刷新邻接页（Flush Neighbor Page）

Innodb存储引擎会检测所在区（extent）的所有页，如果是脏页，那么一起进行刷新，这样做的好处是通过AIO可以将多个IO写入操作合并成一个IO操作，故该工作机制在传统机械磁盘有着显著的优势。

传统机械磁盘将参数innodb_flush_neighbors=1，对于固态硬盘有在超高IOPS性能的磁盘，可以将该参数设为0，即关闭此特性。