第二章 innodb存储引擎 · MYSQL体系知识点详记

## innodb * 第一个完整支持ACID事务的MySQL存储引擎（BDB是第一个支持事务的MySQL存储引擎,现在已停止开发）。 #### 特性： * 行锁设计，支持mvcc ，支持外键，提供一致性非锁定读，同时被设计用来最有效地利用以及试用内存和cpu。 #### 体系架构： innodb 存储引擎有多个内存块，可以认为这些内存快组成了一个大的内存池。 * 维护所有进程/线程需要访问的多个内部数据结构。 * 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。 * 重做日志（redo log）缓冲 #### innodb 后台线程： ###### Master Thread : * 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER），undo页的回收等。 ###### IO Thread : * 在InnoDB存储引擎中大量使用了AIO（Async IO）来处理写IO请求，这样可以极大提高数据库的性能。 * IO Thread的工作主要是负责这些IO请求的回调（call back）处理。 * InnoDB 1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO thread。 * 在Linux平台下，IO Thread的数量不能进行调整，但是在Windows平台下可以通过参数innodb_file_io_thread来增大IO Thread。 ###### PURGE Thread : * 事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。 * 在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。 ###### Page Cleaner Thread： * Page Cleaner Thread 是在InnoDB 1.2.x版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。 ### 内存: #### 缓冲池： * 缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。 * **InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统（Disk-base Database）** * 在数据库总进行读取页的操作首先将从磁盘独到的页存放在缓冲池中，这个过程叫将**页FIX 在缓冲池中**。下一次再读相同页时，首先判断该页是否在缓冲池中。若在缓冲池中，称**该页在缓冲池中命中，直接读取该页。否则，读取磁盘上的页。** * 对于数据库中页的修改操作，首先修改在缓冲池的页，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为**checkpoint的机制刷新回磁盘**。同样这样是为了提高数据库的整体性能。其缓冲池的配置通过参数innodb_buffer_pool_size来设置。 * 缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等 * 一个数据库允许有多个缓冲池实例（innodb 1.0 x开始）。每个页根据哈希值平均分配到冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。可以通过参数innodb_buffer_pool_instances来进行配置，默认为1。 #### 缓冲池管理：数据库中的缓冲池是通过LRU（Latest Recent Used，最近最少使用）算法来进行管理的。即最频繁使用的页在lru列表的前端，而最少使用的在lru列表的末端。当缓冲池不能存放新读取的页时，将首先释放lru列表中尾端的页。 * InnoDB存储引擎中，**缓冲池中页的大小默认为16KB**，同样使用LRU算法对缓冲池进行管理 * 在InnoDB存储引擎中，**LRU列表中加入midpoint位置。新读取到的页**，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，**而是放到LRUmidpoint的位置**，这个算法在innoDB存储引擎下称为Midpoint insertion strategy。默认配置在lru列表长度的5/8处。 * 在InnoDB存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单地理解为new列表中的页都是最为活跃的热点数据。 * InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端 * 当页从LRU列表中的old部分加入到new部分时，成为**page made young** 而因为innodb_old_blocks_time的设置而导致页没有从old部分移到new部分的操作为**page not made young** * innodb引擎自1.0之后开始支持压缩页的功能，即原本16kb的页压缩成1kb，2kb，4kb，8kb。对于非16KB的页通过unzip_LRU列表来进行管理。 * 在LRU列表中的页被修改后，称该页为脏页，即缓冲池中的页和磁盘上的页数据产生了不一致。这时数据库通过checkpoint机制讲脏页刷新回磁盘，而flush列表中的页即为脏页列表。脏页既存在于LRU列表中，也存在与FLUSH列表中。LRU列表时用来管理缓冲池中页的可用性，FLUSH列表用来管理将页刷新回磁盘，二者互不影响。 #### 缓冲池利用率： * 这里还有一个重要的观察变量——Buffer pool hit rate，表示缓冲池的命中率，这个例子中为100%，说明缓冲池运行状态非常良好。通常该值不应该小于95%。若发生Buffer pool hit rate的值小于95%这种情况，用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题。 * 还可以通过表INNODB_BUFFER_PAGE_LRU来观察每个LRU列表中每个页的具体信息 * InnoDB存储引擎从1.0.x版本开始支持压缩页的功能，即将原本16KB的页压缩为1KB、2KB、4KB和8KB ### 重组日志缓冲（redo log buffer） innodb的内存区除了缓冲池外，还有重做日志缓冲（redo log buffer）。 innodb首先将重做日志放入到这个缓冲区，然后按一定过得频率将其刷新到重做日志文件。重做日志缓冲一般不需要很大，一般每秒钟会将缓冲刷新到日志文件。缓冲大小通过innodb_log_buffer_size控制，默认为8MB。 * 为了避免数据库宕机时数据丢失的问题，当前数据库系统普遍采用 write ahead log 策略，**即当事务提交时，先写重写日志，再修改页**。当由于发生宕机而导致数据丢失师，可以通过重做日志来完成数据的修改。这也是ACID中的D（持久性）的要求。 * 缓冲池刷新到日志文件的三种情况： - master thread 每一秒将重做日志缓冲刷新到重做日志文件 - 每个事务提交时会将日志缓冲刷新到重做日志文件 - 当重做日志缓冲剩余空间小于1/2时 * 在innodb存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的，当该区域的内存不够时，会从缓冲池中进行个申请。 ### checkpoint技术 * checkpoint（检查点）技术解决的问题： - 缩短数据库恢复时间 - 缓冲池不够用时，将脏页刷新到新磁盘。 - 重做日志不可用时，刷新脏也。 * 当数据库发生宕机的时候，数据库不需要重做所有的日志，因为checkpoint之前的页都已经刷新回磁盘。故数据库只需对chckpoint后的重做日志进行恢复。这样就大大缩短恢复时间。 * 在innodb中通过lsn（log sequence number）来标记版本的。lsn是八字节的数字。 * checkpoint分为两种： - **sharp checkpoint**：（默认设置）当数据库关闭时将所有页刷新磁盘 (参数：innodb_fast_shutdown=1) - **fuzzy checkpoint**：只刷新一部分脏页回磁盘 innodb在运行时采用fuzzy checkpoin进页的刷新。 * fuzzy checkpoint应用情况： - **master thread checkpoint ：**差不多已每秒或每十多秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，用户查询线程不会阻塞。 - **Flush_LRU_List checkpoint ：** innodb存储引擎需要保证LRU列表中需要保证有差不多100个空闲页可供使用。 - **Asyn/Sync flush chceckpoint ：** 重做日志不可用的情况，需要强制将一些页刷新会磁盘。5.6之前同步（async）会阻塞发现问题的用户查询线程， sync flush 会阻塞所有的用户插叙线程。 5.6之后刷新的操作放到单独的page cleaner thread中故不会阻塞用户查询线程 - **dirty page too much checkpoint ：** 脏页太多 innodb 存储引擎强制刷新checkpoint。也是为了保证缓冲池有足够可用的页。 ##### Master Thread 工作方式 master thread 具有最高的线程优先级别。其内部由多个循环组成：主循环，后台循环，刷新循环，暂停循环。 master Thread 会根据数据库运行的状态在loop，background loop ，flushloop 和suspend loop 中进行切换。 * **loop 主循环：** 包括两大部分的操作(大概情况负载大的情况下肯个有延迟）: 每秒钟的操作每十秒钟的操作。 - 每秒的操作 * 日志缓冲刷新到磁盘，即使这个事务还没提交 [总是] —————（innodb存储引擎仍然每秒将重做日志缓冲中的内容刷新到重做日志文件，这也是为什么在大的事务提交时间也是很短的原因） * 合并插入缓冲 [可能] —————（并不是每秒都发生。innodb会判断前一秒内发生的io次数是否小于五次，如果小于五次，innodb会认为当前的io压力很小，可以执行合并插入缓冲的操作） * 至多刷新100个innodb的缓池中的脏页到磁盘（可能） * 如果当前没有用户活动，则切换到background loop（可能） - 每十秒的操作： * 刷新100个脏页到磁盘 [可能] * 合并至多五个插入缓冲 [总是] * 将日志缓冲刷新到磁盘 [总是] * 删除无用的undo页 [总是] * 刷新一百个或者10个脏页到磁盘 [总是] * 以上过程中，innodb会判断过去10秒之内磁盘的io操作是否小于200次，存储引擎认为当前有足够的io操作能力，因此将100个脏页刷新到磁盘。 * **background loop :** 若当前没有用户活动（数据库空闲时）或者数据库关闭，就会切到这个循环。 * background会执行以下操作： - 删除无用的undo页 [总是] - 合并20个插入缓冲 [总是] - 跳回到主循环 [总是] - 不断刷新100个页直到符合条件（可能，跳到flush loop完成） * 若flush loop中也没有上面事情可以做，innodb会切换到suspend loop,将Master Thread挂起，等待事件的发生。 * innodb1.0版本带来一个参数是innodb_adaptive_flsuhing (自适应地刷新)，该值影响每秒刷新脏页的数量。以前版本的刷新规则是脏页在缓冲池所占的比例小于innodb_max_dirty_pages_pcts时，不刷新脏页。 * innodb1.2中将刷新脏页的操作从master thread中分离到一个单独的page cleaner thread，从而减轻了master thread 的工作，进一步提升系统的并发性。 ### innoDB关键特性： * 插入缓冲 * 两次写 * 自适应hash索引 * 异步io * 刷新邻接页 #### 插入缓冲( insert buffer) 在innoDB存储引擎中，主键是行唯一的标识符。通常应用程序中进行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。对于非聚集索引叶子节点的插入不再是顺序的了，这是就需要离散地访问非聚集索引页。（B+树的特性决定了非聚集索引插入的离散性，因此性能会下降） * innoDB开创性地设计了 **insert buffer** ，对于非聚集索引的插入或更新操作，不是每次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在则直接插入；不在则先放入到一个insert buffer对象中。然后在以一定的频率和情况进行insert buffer和辅助索引叶子结点的merge操作。这时通常能将多个插入合并到一个操作中，大大提高了对于非聚集索引插入的性能。 * insert buffer的使用需要满足以下两个条件：－索引是辅助索引－索引不是唯一的 * insert buffer的数据结构是一个B＋树。在全局只有一棵insert buffer B＋树，负责对所有的表的辅助索引进行insert buffer这棵树存在共享表空间中，默认也就是IB data1中。 * insert buffer 是一棵B＋树，因此其也由页节和非叶节点组成。非叶节点存放的是search key 。 [ space marker offset]space表示待插入记录所在表的表空间id space占4个字节，marker占用一个字节，用来兼容老版本的insert buffer。offset表示页所在的偏移量，只用4个字节。 ##### change buffer innodb1.0开始引入change buffer 可以视为insert buffer的升级。innodb 对dml操作－insert delete update都进行缓冲他们分别：insert buffer，delete buffer，purge buffer。对象也是非唯一的辅助索引。 ##### merge insert buffer * 辅助索引页被读取到缓冲池时 * insert buffer bitmap页追踪到该辅助索引页已无可用空间时； * master thread #### 两次写（doublewrite）两次写带给innodb存储引擎的是数据也的可靠性。当数据库发生宕机时，会产生部分写失效。innodb未使用两次写前，因为这个会导致数据丢失现象。在应用重做日志之前，用户需要一个页的副本，当写入失效发生时先通过页的副本先还原该页，在进行重做，这就是doublewrite。操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，innodb可以从共享表空间中找到该页的一个副本，将其复制到表空间文件，在应用重做日志进行恢复。（参数skip_innodb_doublewrite可以禁用两次写功能） * 两次写由两部分组成，一部分是内存中的doublewrite buffer 大小为2Mb，另一位一部分是由物理磁盘上共享表空间的连续的128个页，即两个区，大小同样为2Mb。 * 在对缓冲池的脏页进行刷新时，并不直接谢磁盘，而是通过memcpy函数将脏页先复制到内存中doublewrite buffer，之后通过double write buffer在分两次每次1Mb顺序的写入共享表空间的物理磁盘上，然后调用fsync函数，同步磁盘，避免缓冲写带来的问题。 #### 自适应哈希索引 innodb 会监控对表上各索引页的查询您。如果观察到建立hash索引可以带来速度提升，则建立hash索引，称之为自适应hash索引（AHI）。AHI 是通过缓冲池的b+树页构造而来的，因此建立的速度很快，而且不需要对整张表构建hash索引。innodb会自动根据访问的频率来为某些热点页建立hash索引。 * AHI有一个要求，即对这个页的连续访问模式必须是一样的。 * innodb_adaptive_hash_index可以禁用或启动此特性，默认AHI为开启状态。 #### 异步io AIO 可以进行merge IO操作，也就是将多个IO合并成一个IO，提高IOPS的性能。启用Native AIO，恢复速度可以提高75%。在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，即磁盘的写入操作则全部由AIO完成。 #### 刷新邻接页 Flush Neighbor Page（刷新邻接页）的特性。其工作原理为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。 #### 关闭启动与恢复 * 在关闭时，参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。该参数可取值为0、1、2，默认值为1 - 0表示关闭时完成所有full purge和merge insert buffer并且刷新所有的脏页到磁盘。 - 1表示不完成上述的full purge和merge insert buffer，但是在缓冲池中的一些数据脏页还是会刷新回磁盘 - 2表示不完成上述的full purge和merge insert buffer操作也不将缓冲池中的缓存页率先你回磁盘二十将日志写入日志文件，下次启动时进行恢复操作。 * DB需要完成所有的full purge和merge insert buffer，并且将所有的脏页刷新回磁盘。这需要一些时间，有时甚至需要几个小时来完成。如果在进行InnoDB升级时，必须将这个参数调为0 * innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。默认为0，当不能进行有效恢复时，如数据页发生了corruption，MySQL数据库可能发生宕机 ,并把错误写入错误日志中去。 * 参数innodb_force_recovery还可以设置为6个非零值：1～6。大的数字表示包含了前面所有小数字表示的影响。具体情况如下 - 1.( SRV FORCE IGNORE CORRUPT):忽略检查到的 corrupt页。 - 2 （SRV FORCE NO BACK GROUND)阻止 Master Thread线程的运行，如 Master Thread线程需要进行 full purge操作，而这会致 crash - 3( SRV FORCE NO TRX UNDO):不进行事务的回滚操作。 - 4( SRV FORCE NO IBUF MERGE):不进行插人缓冲的合并操作。 - 5 ( SRV FORCE NO UNDO LOG SCAN:不査看撤销日志( Undo Log)， INNODB存储引擎会将未提交的事务视为已提交。 - 6( SRV FORCE NO LOG REDO:不进行前滚的操作。 - 需要注意的是，在设置了参数 innodb force recovery大于0后，用户可以对表进行select、 create和drop操作，但insert、 update和 delete这类DML操作是不允许的。