MySQL技术内幕InnoDB存储引擎学习笔记(第二章)

                       第二章 InnoDB存储引擎

一、实验环境

宿主机系统：windows7

虚拟机：OracleVMVirtualBox

Linux:ubuntukylin-14.04.1-amd64.iso

jdk:1.7.0_101

mysql:5.7.12

书上的mysql版本：5.6.6

二、InnoDB是什么

1、创始人为Heikki Tuuri(1964，芬兰赫尔辛基)，由Innobase Oy公司开发，mysql5.5版本开始是默认的表存储引擎。

2.特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，最有效的使用内存和CPU.

3.已应用于各大型网站，如：Google、Yahoo！、Facebook等。

二、InnoDB版本

1.早期版本随mysql数据库版本更新而更新

2.mysql5.1以后，支持存储引擎开发商以动态的形式加载。所以支持两个版本，一个是静态编译的innoDB版本（老版本）【支持ACID、行锁设计、MVCC】，

另一个是动态加载的InnoDB版本，即InnoDB Plugin,视为InnoDB 1.0.x【增加了compress和dynamic页格式】

3.mysql 5.5,innoDB升级为1.1.x【增加了Linux AIO、多回滚段】

4.mysql 5.6InnoDB升级为1.2.x【增加了全文索引支持，在线索引添加】

三、InnoDB的体系结构

由图可见，InnoDB有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作：维护所有进程/线程需要访问的多个内部数据结构；缓存磁盘上的数据，方便快速的读取，同事在对磁盘文件的数据修改之前在这里缓存；重做日志（redo log）缓冲等

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同事保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

1.后台线程：

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。

Master Thread

是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERTBUFFER）、UNDO页的回收等。

IO Thread

在InnoDB存储引擎中大量使用了AIO来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调（call back）处理。

1.0版本前，有4个IO Thread，分别是write、read、insertbuffer 和log IO Thread，其中linux平台下，数量固定，windows平台可以通过调整参数innodb_file_io_threads来增大IO Thread.

1.1版本开始，弃用参数innodb_file_io_threads，且readthread 和write thread分别增大到了4个，增加参数innodb_read_io_threads和innodb_write_io_threads参数进行设置。

命令：mysql -uroot -p；输入密码后，进入mysql

命令：SHOW VARIABLES LIKE 'innodb_version';显示结果如下，和书上显示的不一样。实验结果显示的版本号和mysql版本号相同，书上显示的是innodb的版本号，即1.0.x

命令：SHOW ENGINE INNODB STATUS\G;（\G来控制结果显示格式）观察innodb中的IO Thread.实验结果和书上显示相同。

Purge Thread

事务提交后，其所使用undolog不再需要，PurgeThread来回收已经使用并分配的undo页。purge可以在master thread 中完成，也可以独立到单独的线程中进行，在配置文件中添加innodb_purge_threads=1来启用独立的purge thread。且1.2版本开始，该参数可设置为>1,进一步加快undo页面的回收。

Page Cleaner Thread

1.2版本中引入，将之前版本中的脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原来masterthread的工作及对于用户查询线程的阻塞，进一步提高性能。

写在后面的话：主要线程为master thread，后续优化后，增加了Page Cleaner Thread做脏页的刷新，Purge Thread。做页面的回收

1.内存：

缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。使用缓冲池来提高数据库的整体性能。缓冲池是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。

在数据库中进行读取页的操作，首先将从磁盘读到的页内存放在（FIX）缓冲池中，下一次再读取相同的页时，首先判断该页是否在缓冲池中。若在，则直接读取该页，否则读取磁盘上的页。对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。刷新动作病史每次页发生更新时触发，而是通过checkpoint的机制刷新回磁盘。

32位操作系统，缓冲池大小最大为3G，建议使用64位系统。可以通过innodb_buffer_pool_size来设置缓冲池的大小。

缓冲池中缓冲的数据页类型有：索引页、数据页、undo页、插入缓冲（insertbuffer）、自适应哈希索引（adaptive hash index）,InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。可以通过innodb_buffer_pool_instances来进行配置，默认值为1，若大于1，则表示有多个缓冲池实例。

此处实验结果和书上展示略有不同。下面是实验结果：

LRU List、Free List和Flush List

LRU List:用来管理已经读取的页，数据库中的缓冲池是通过LRU(LastestRecent Used,最近最少使用)算法来进行管理的。即最频繁使用的页放在LRU列表的前端，而最少使用的页放在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。

InnoDB中，缓冲池中页的大小默认为16KB。对传统LRU算法进行了优化，在LRU列表中还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU首部，而是放入到LRU列表的midpoint位置。该算法成为 midpoint insertion strategy.默认该位置在LRU列表长度的5/8处，可以由参数innodb_old_blocks_pct控制。

参数的默认值为37，表示新读取到的页插入到LRU列表尾端的37%的位置，差不多3/8。在innodb中把midpoint之后的列表称为old列表，之前为new列表（最活跃的热点数据）。

若将读取到的页放入到LRU首部，那么某些SQL操作可能会使用缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页仅在这次查询中需要，并不是热点数据。如果这些页被放入首部，则热点数据会被从列表中移除，下次读取页时，需要从磁盘读取。

innodb_old_blocks_time读取到mid位置后需要等待多久会加入到LRU列表的热端。

命令：SET GLOBAL innodb_old_blocks_time = 1000;

1.0版本开始，支持压缩页的功能，将原本16KB的页压缩为1kb,2kb,4kb和8kb.对于非16kb的页，是通过unzip_LRU列表来进行管理。

unzip_LRU怎样从缓冲池中分配内存：

1.对unzip_LRU列表下的不同压缩页大小的页分别进行管理。

2.通过伙伴算法分配内存。

举例：从缓冲池中申请页为4kb的大小，过程如下：

1》检查4kb的unzip_LRU列表，检查是否有可用的空闲页。若有，则直接使用。若没有，则检查8kb的列表。

2》若能得到空闲页，将页分成2个4kb的页，存放到4kb的unzip_LRU列表。若不能得到空闲页，从LRU列表中申请一个16kb的页，将其分为1个8kb，2个4kb的页，分别存放于unzip_LRU列表中。

Free List：数据库刚启动时，LRU列表为空，所有页都存放在Free列表中。当需要从缓冲池中分页时，首选从Free列表中查找是否有可用的空闲页，若有，则将该页从free列表中删除，放入到LRU列表中。可通过show engine innodb status来观察LRU列表及free列表的使用情况和运行状态

注：单位为页，每页为16K

重要参数：buffer pool hit rate 表中缓冲池的命中率，通常应该大于95%，如发生小于95%的情况，需观察是否是由于全表扫描引起的LRU列表被污染的问题。

1.2版本开始，可以运行下面语句

select  * frominformation_schema.innodb_buffer_pool_stats \G;

select * from innodb_buffer_page_lru where space=1;

Flush List：LRU列表中的页被修改后，成为脏页（dirty page），即缓冲池中的页和磁盘中的页的数据不一致。数据库通过checkpoint机制将脏页刷新回磁盘，Flush 列表中的页为脏页列表。

show engine innodb status;

select * from innodb_buffer_page_lur whereoldest_modification>0;

重做日志缓冲

innoDB首先将重做日志信息放入到这个缓冲区，然后按一定频率将其杀心到重做日志文件。一般情况下，每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。由参数innodb_log_buffer_size控制，默认为8M.

下面情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

1.master thread每一秒将重做日志缓冲刷新到重做日志文件；

2.每个事务提交时会将重做日志缓冲刷新到重做日志文件；

3.当重做日志缓冲池剩余空间小于一半时，重做日志缓冲刷新到重做日志文件；

额外的内存池

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

四、checkpoint技术

页的操作首先都是在缓冲池中完成的，缓冲池的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池刷新到磁盘。为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了write ahead log策略，即当事务提交时，先写重做日志，再修改页。如因为宕机而导致数据丢失，通过重做日志来完成数据的恢复。这也是事务ACID中D的要求。

checkpoint技术的目的是为了解决如下问题：

1.缩短数据库的恢复时间，当数据库发生宕机时，数据库只需对checkpoint后的重做日志进行恢复。

2.缓冲池不够用时，将脏页刷新到磁盘，当缓冲池不够用时，需强制执行checkpoint，将脏页刷回磁盘。

3.重做日志不可用时，刷新脏页。宕机时，数据库恢复操作不需要的重做日志的部分，可以被覆盖重用，此时，需要强制产生checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

有两种checkpoint

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

fuzzy checkpoint:分四种情况。

master thread checkpoint:以每秒或每10秒的速度从缓冲池的脏页列表中刷新一定比例的页会磁盘。异步。

FLUSH_LRU_LIST checkpoint:LRU列表中需要有100个空闲页，如不够，会将LRU列表尾端的页移除。如果这些页中有脏页，需进行checkpoint。用户可以通过innodb_lru_scan_depth控制LRU列表中可用页的数量。

Async/Sync Flush Checkpoint:保证重做日志的循环使用的可用性。

Dirty Page too much checkpoint:脏页数据太大，强制进行checkpoint，刷新一部分脏页到磁盘。

五、Master thread工作方式

有最高的线程优先级别。内部由多个循环组成：主循环，后台循环，刷新循环，暂停循环。master   thread会根据数据库运行的状态在四个循环中进行切换。

loop(主循环):两大部分操作，每秒钟的操作和每10秒的操作。

每秒的操作：

1.日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）（为什么再大的事务，提交（commit）的时间也很短）；

2.合并插入缓冲（可能）；判断前一秒发生的IO是否小于5次，若小于，可执行。

3.至多刷新100个innoDB的缓冲池中的脏页到磁盘（可能）；判断当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）是否超过了配置文件中(innodb_max_dirty_pages_pct)的阈值,如超过，则将100个脏页写入磁盘中。

4.如果当前没有用户活动，则切换到background  loop （可能）；

每10秒的操作：

1.刷新100个脏页到磁盘（可能），判断过去10秒内磁盘的IO操作是否小于200次，如果是，将100个脏页刷新到磁盘。

2.合并至多5个插入缓冲（总是）

3.将日志缓冲刷新到磁盘（总是）

4.删除无用的undo页（总是）

5.刷新100个或者10个脏页到磁盘（总是）

background loop：若当前没有用户活动或者数据库关闭时，会切换到这个循环。执行以下操作：

1.删除无用的undo页（总是）

2.合并20个插入缓冲（总是）

3.跳回到主循环（总是）

4.不断刷新100个页直到符合条件。

1.2.x之前：

增加参数innodb_io_capacity表示磁盘吞吐量，默认为200.刷新到磁盘的页的数量，会按照innodb_io_capacity百分比来控制，规则为：

1.在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5%

2.在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity.

如当前存储设备有比较好的IO速度，可以将该参数值调高。

修改参数默认值innodb_max_dirty_pages_pct=75,既加快了刷新脏页的频率，又能保证磁盘IO的负载。

增加参数：innodb_adaptive_flushing（自适应的刷新）

1.2.x：

从master thread线程分离到一个单独的pagecleaner thread，从而减轻了master thread的工作，进一步提高了系统的并发性。

六、InnoDB关键特性

插入缓冲（insert buffer ）

插入缓冲既是缓冲池的一部分，也是物理页的一个组成部分。

插入聚集索引,即（primary key）一般是顺序的，不需要磁盘的随机读取。

对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个insert buffer对象中。再以一定的频率和情况进行insert buffer和辅助索引叶子节点的merge操作，大大提高了非集簇索引插入的性能。

insert buffer的使用需同时满足两个条件：

1.索引是辅助索引（secondaryindex）

2.索引不是唯一的。（unique）

存在问题：在写密集情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占到1/2，该比例通过参数ibuf_pool_size_per_max_size来控制。

changebuffer

1.0.x开始，innodb对DML操作insert、delete、update都进行缓冲，分别是insert buffer、delete buffer 、purge buffer

适用对象：非唯一的辅助索引

将一条记录进行update的操作分为两个过程：

1.将记录记为已删除

2.真正将记录删除

delete buffer 对应将记录标记为删除

purge buffer 对应 将记录真正删除

参数innodb_change_buffering,来开启各种buffer选项。

insertbuffer 的内部实现

mysql全局只有一颗B+树，负责对所有的表的辅助索引进行insert buffer。存放在共享表空间中。

两次写（double write）

两次写主要带来的是数据页的可靠性。

数据库发生宕机时，某个页只写了一部分，称为部分写失效。

当写入失效发生时，先通过页的副本来还原该页，再进行重做，就是 double write

参数skip_innodb_doublewrite可以禁止使用doublewrite功能。

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

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

自适应哈希索引(adaptive hash index)

哈希是一种非常快的查找方法，在一般情况下，这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。

自适应哈希索引（AHI）:innodb会对表上各个索引页的查询进行监控，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。是通过缓冲池的B+树页构造而来，因此建立的速度很快。innodb会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。

AHI要求：

1.对这个页的连续访问模式必须一样，即查询条件一样。

2.以该模式访问了100次

3.页通过该模式访问了N次，其中N=页中记录*1/16

数据库自动优化，无需DBA对数据库进行人为调整。

哈希索引智能搜索等值查询

可以通过参数innodb_adaptive_hash_index来控制禁用或启动该特性。

异步IO(async IO)

Async IO:每进行一次IO操作，需要等待此次操作结束后才能继续下来的操作。

AIO:用户在发出一个IO请求后立即发出另外一个IO请求，当全部IO请求发送完毕后，等待所有的IO操作完成。可进行IO Merge操作，将多个IO合并为一个IO.

read  ahead 方式的读取和脏页的刷新全部由AIO完成

刷新邻接页(flush neighbor page)

当刷新一个脏页时，会检测该页所在区的所有页，如果是脏页，一起刷新。

问题：

是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页。

固态硬盘有较高的IOPS，是否还需要这个特性？

so,用参数innodb_flush_neighbors来控制是否启用该特性。0表示关闭。

七、启动、关闭和恢复

innodb_fast_shutdown:可取值（0,1,2），默认为1

0：关闭时，要完成所有的fullpurge和merge insert buffer，并且将所有的脏页刷新回磁盘。

1：不需要完成0时的操作，但是在缓冲池中的一些数据脏页还是会刷新回磁盘

2：不完成0,1的操作，而是将日志都写入日志文件。下次数据库启动时，会进行恢复操作。

innodb_force_recovery: 默认为0，可取值（0~6），1~6大的数字包含了前面所有数字表示的影响。

0：进行所有的恢复操作，当不能进行恢复时，把错误写进错误日志中去。

1：忽略检查到的corrupt页

2：阻止master  thread 线程的运行。

3：不进行事务的回滚操作

4：不进行插入缓冲的合并操作

5：不查看撤销日志

6：不进行前滚操作。

 

 

补充：

1.书中无数次提到了页这个词，百度结果： innodb的最小物理存储分配单位是page页

2.对书中提到的另外一个概念：UUID，进行了了解，详见博文http://blog.csdn.net/lanonola/article/details/51956781和http://blog.csdn.net/lanonola/article/details/51956913

使用UUID比使用自增作为主键的优点：粗浅认识，分布式优点明显，如涉及到分表，UUID更适合。

3.固态硬盘：固态硬盘的接口规范和定义、功能及使用方法上与普通硬盘几近相同，外形和尺寸也基本与普通的2.5英寸硬盘一致。固态硬盘具有传统机械硬盘不具备的快速读写、质量轻、能耗低以及体积小等特点，同时其劣势也较为明显。尽管IDC认为SSD已经进入存储市场的主流行列，但其价格仍较为昂贵，容量较低，一旦硬件损坏，数据较难恢复等；并且亦有人认为固态硬盘的耐用性（寿命）相对较短。