MySQL InnoDB笔记一

MySQL InnoDB笔记一

本文笔记摘自于《Mysql 技术内幕 InnoDB存储引擎》

一、MySQL数据库

​ 数据库的SQL语句执行，需要经过其数据库实例来进行完成。而对于MySQL数据库来说，查询SQL语句的执行会经过如下步骤。应用程序连接MySQL后会分配连接线程。之后通过SQL接口组件接收SQL命令，通过查询分析器对SQL语句进行解析，判断语法正确性。对解析后的SQL使用优化器进行优化，生成执行计划，选择最优计划。之后尝试获取其缓存，如果命中则会直接获取缓存数据。最后将数据进行返回。MySQL数据库体系结构主要有如下部分。

• 连接池组件：对外开放的连接接口，用于处理通讯协议、线程与权限校验。
• 管理服务和工具组件：MySQL的系统管理和控制工具，用于备份恢复、数据复制、集群等。
• SQL接口组件：用来接受用户SQL命令，返回查询结果。
• 查询分析器组件：用来验证SQL命令和解析成优化器可识别格式。
• 优化器组件：优化查询SQL语句，生成不同执行计划，根据算法选择最优计划。
• 缓存组件：用于缓存MySQL中数据页、索引页、插入缓存等数据。
• 插件式存储引擎：MySQL最具有特点的区域，提供了一系列标准的管理和服务支持，存储引擎是底层物理结构的实现，用户可自行来开发存储引擎。
• 物理组件：兼容不同操作系统与存储引擎交互，完成数据存储。

二、InnoDB存储引擎

1. 后台进程

• Master Thread

  是InnoDB引擎核心后台进程，用于将缓存池中数据异步刷新至磁盘，保证数据一致性。其中操作包括脏页刷新、合并插入缓存、UNDO页回收InnoDB1.1之前版本等。Master Thread具有最高的线程优先级，其内部组成有多个循环，分别为：主循环、后台循环、刷新循环InnoDB1.2之前、暂停循环。

• IO Thread

  使用AIO来处理IO请求，IO线程主要负责这些IO的回调处理，读写操作使用线程分离。

• Purge Thread

  InnoDB1.1版本后用于独立处理undo log的回收，减轻Master Thread工作，提高性能。1.2版本后支持多个Purge Thread进程进行回收。

• Page Cleaner Thread

  InnoDB1.2版本后引入，其作用于脏页的刷新。目的减轻Master Thread工作，提高性能。

2. 内存

• 缓存池

  用于弥补CPU速度与磁盘读写速度差距，而创建的内存区域。在数据库读取页操作时，会首先将磁盘读取到的页FIX到缓存池中，提高下次读取效率。对于页的修改操作，是先修改缓存池，再以一定频率刷新到磁盘。其刷新触发是通过Checkpoint机制。

  缓存池中数据页类型有：索引页、数据页、undo页、插入缓存和自适应哈希索引、锁信息、数据字典信息等。缓存池中的数据通过LRU算法进行管理，并对算法进行优化加入midpoint位置。

• redolog缓存

  InnoDB引擎会先将重做日志放入这个缓存区，然后按一定频率刷新到重做日志文件中。缓存不用设置很大，在下列三种情况会将缓存刷新至文件。

  • Master Thread每秒会刷新重做日志缓存。
  • 每个事务提交时会刷新重做日志缓存保证持久性。
  • 缓存池剩余空间小于1/2时刷新重做日志。

• 额外的内存池

  InnoDB引擎中，对内存管理是通过内存堆方式进行。对部分数据结构本身的内存进行分配时，会从额外的内存池中进行申请，当该区域内存不够时，才会在缓存池申请。例如，分配了缓存池，但缓存池中的帧缓存还有对应缓存控制对象，这个对象内存需要从额外内存池进行申请。在申请很大的InnoDB缓存池时，应适当增加此值。

3. Checkpoint技术

​ Checkpoint技术的目的主要是解决以下几个问题：缩短数据库恢复时间、缓存池不够用时刷新脏页、重做日志不可用时刷新脏页。在InnoDB存储引擎中，Checkpoint分为以下两种。

• Sharp Checkpoint（关闭时Checkpoint）

  发生在数据库关闭时将所有脏页都刷新回磁盘。

• Fuzzy Checkpoint（运行时Checkpoint）

  在InnoDB运行时采用的Checkpoint进行页刷新，即只刷新一部分脏页。其触发机制分以下几种。

  • Master Thread Checkpoint

    主线程刷新，以每秒或每十秒速度从缓存池刷新一定比例脏页。

  • FLUSH_LRU_LIST Checkpoint

    InnoDB引擎因需要保证100空闲页可供使用。在用户查询线程InnoDB1.2之前或Page Cleaner线程InnoDB1.2之后空闲页数不足时，会回收LRU尾端页，如果这些页存在脏页时进行的Checkpoint。

  • Async/Sync Flush Checkpoint

    在重做日志不可用时，会强制一些页刷新至磁盘，其刷新的脏页选择是随机的。本次刷新页数据记作checkpoint_age，根据所定义参数会产生async_water_mark和sync_water_mark变量。在InnoDB1.2后会在Page Cleaner Thread中，checkpoint_age>async_water_mark时刷新脏页数据，使checkpoint_age<async_water_mark。在InnDB1.2前则会在用户查询进程,并在checkpoint_age>async_water_mark时阻塞查询用户线程，checkpoint_age>sync_water_mark时阻塞全部用户线程。

  • Dirty Page too much Checkpoint

    脏页数据太多，超过参数设置比例时，会强制执行Checkpoint。

4. InnoDB关键特性

• 插入缓存

  • Insert Buffer

    InnoDB引擎对于非聚集索引的插入和更新并不是每次都会直接插入索引页，当插入的非聚集索引页不在缓存池中时，会先放入Insert Buffer中，然后再以一定频率将Insert Buffer和辅助索引页子节点进行merge操作，这样能将多个插入合并到一个操作中，提高非聚集索引的插入效率。Insert Buffer的使用条件要满足下列两点。

    • 索引是辅助索引。
    • 索引不是唯一的因为判断唯一性是需要先进行离散读取。

    需要注意的有如下几点。在InnoDB宕机情况下，因Insert Buffer缓存，需要花费更多时间进行恢复。在写密集情况Insert Buffer会占用过多缓存池内存。

    Insert Buffer的底层数据结构为B+树，全局只有一棵，放在共享表空间，负责全部表中辅助索引的Insert Buffer。其中B+树非叶子节点存放查询的search key，叶子节点具体存放辅助索引记录。下表为其非叶子节点结构。

    4 byte	1 byte	4 byte
    space	marker	offset
    表唯一id	旧版本兼容	页所在偏移量

    Insert Buffer的合并操作会发送于以下几种情况。

    • 辅助索引页被读取到缓存池时
    • Insert Buffer Bitmap页追踪该辅助索引页发现无可用空间时。Insert Buffer Bitmap用于追踪每个辅助页的可用空间
    • Master Thread

  • Change Buffer

    在InnoDB1.0后引入，对DML操作都进行缓存，分别是Insert Buffer、Delete Buffer、Purge Buffer。Change Buff同样只适用于非唯一的辅助索引。

• 两次写

  doublewrite用于保证数据页的可靠性。在写应用重做日志前，会先通过页的副本来还原改页，再进行重做，可以避免部分写失效所带来的问题。

  doublewrite由两部分组成，一部分位于内存，大小为2MB，一部分位于磁盘，大小为两个区，通样是2MB。在对缓存池的脏页刷新时，不会直接写磁盘，而是先复制到doublewrite buffer中，然后分为两次，每次1MB顺序写到共享表空间的物理磁盘上，最后将doublewrite buffer中的页离散写到各个表空间。。

• 自适应哈希索引

  InnoDB会监控表上各索引页的查询，如果观察到建立hash索引会带来速度提升，则可能建立hash索引。其建立条件如下。

  • 对该页的连续访问模式必须一致，并且访问次数大于100次。
  • 通过该模式访问次数超过N次，N = 页中记录数 / 16.

• 异步IO

  InnoDB采用AIO来处理磁盘操作，会异步处理所有IO请求，并且会对IO请求进行Merge操作。

• 刷新邻接页

  当刷新一个脏页，并会刷新该页所在区的所有脏页，因采用AIO会将这些合并为一个IO处理。但在固态硬盘与高频率写操作表中不适合使用。

二、文件

1. 表空间文件（tablespace）

​ InnoDB采用将存储的数据按表空间进行存放设计。所有基于InnoDB引擎的表都会记录到该共享表空间中。用户也可以通过设置innodb_file_pre_table=ON，来使每个表产生独立表空间，这些表空间只会存储该表数据、索引和插入缓存BITMAP等信息。

2. 二进制日志（bin log）

​ bin log记录了对MySQL数据库执行的所有写操作。主要用于数据的恢复、复制与审计。默认情况为关闭，配置中可通过修改sync_binlog值开启。

• sync_binlog = 0：关闭bin log日志。

• sync_binlog = 1：同步写入bin log日志，即每次提交写入，建议master-slave架构下选用。

• sync_binlog = N：开启bin log buffer，写入N次bin log buffer后，异步落盘至bin log磁盘文件。

  MySQL5.1版本后引入binlog_format参数，可用于设置bin log的日志格式，该参数为动态参数，可在运行环境下进行修改。其参数值分别如下。

STATEMENT	ROW	MIXED
记录逻辑SQL语句	记录行更改情况	混合模式，默认采用STATEMENT。

3. 重做日志（redo log）

​ redo log为InnoDB引擎所专有的日志，主要负责记录InnoDB中的事务。当实例或介质失败时，可用过重做日志进行恢复，保证数据完整性。redo log在磁盘中拥有至少一个重做日志组，每个重做日志组至少有两个重做日志文件，每个重做日志大小一致，对于重做日志文件的写入采用循环写方式进行。重做日志记录的为每个页更改的物理情况。

MySQL上次无论是否正常关闭，都会对redo log进行恢复，因其为物理日志，故恢复时间要远快于逻辑日志。

​ 重做日志的写入是先写入redo log buffer中，后通过一定策略条件，顺序写入重做日志文件中。在InnoDB引擎中重做日志都是以512 byte进行分块存储。因与磁盘扇区大小相同，故写入属于原子性操作，不需要额外进行double write。重做日志保证了事务的原子性与持久性。而redo log buffer刷新磁盘时机除主线程每秒定时与检测缓存空间过半时以外。还有innodb_flush_log_at_trx_commit参数控制，该参数值分别如下。

0	1	2
只等待主线程刷新	事务提交时同步刷新	事务提交时异步刷新

​ 对于上表中innodb_flush_log_at_trx_commit取值，同步刷新的性能开销要远大于其余两项，但为了保证事务的持久性还是要进行开启。重做日志的格式各版本都有不同，有着众多类型，但它们都有这相同的基本格式如下。

redo_log_type	space	page_no	redo_log_body
重做日志类型	表空间ID	页偏移量	数据部分

4. 撤消日志（undo log）

​ undo同样是作用于InnoDB引擎的日志，与redo不同。undo主要用于对事物进行回滚与支持MVCC，其记录的为逻辑日志，位于共享表空间中的undo段中。undo log会产生redo log，因为需要持久性的保护。undo中主要记录与先前相反的操作。

​ InnoDB引擎对undo的管理同样采用段的方式，通过rollback segment进行管理，每个回滚段记录了1024个undo log segment，而在每个undo log segment进行页的申请，InnoDB1.1版本后支持最大128个回滚段，故支持最多128*1024个并发事务。事务在undo log segment分配页并写入undo log这个过程同样会记录重做日志。当时事务提交时会进行如下两个操作。

• 将undo log放入列表，供purge操作。

• 判断undo log所在页是否可以重用，如果可以会分配给下个事务。

  事务提交后可能不会立即立即回收undo log及其所在页，而是放入一个链表，是否可回收有purge线程判断。purge线程会判断undo页使用空间是否小于3/4，如果小于则新的undo log记录会放在当前undo log后面，否则进行回收。

  undo log分为insert undo log和update undo log。对于insert undo log是由insert产生其本身对其他事务不可见，故在事务提交时会立即删除，不会经过purge操作。update undo log记录的是update和delete操作，因为改undo log可能需要提供MVCC机制，故不能在事务提交时删除，而是放在链表等待purge线程删除。InnoDB并不会真正回收这些空间，而是标记为可用空间，以供下次使用undo时使用。

  delete操作并不会直接删除记录，而是将记录标记为逻辑删除，而记录的真正删除是位于purge线程完成。而update操作实际是分两步完成，首先标记原主键记录为已删除，之后插入一条新的记录。

三、InnoDB存储结构

1. 表存储结构

​ 对于InnoDB引擎存储结构，所有的数据都被逻辑地存放到表空间中，表空间由段(segment)、区(extent)、页(page)组成。

• 表空间：分为共享表空间和独立表空间。如果启用独立表空间，则会将数据、索引、插入缓存Bitmap页存放至独立表空间中。而回滚信息、插入缓存索引页、系统事务、二次写缓存等还是存放至共享表空间。
• 段(segment)：表空间是由各个段组成，其中常见段分为数据段、索引段、回滚段等。在InnoDB引擎中因其为聚集索引，数据段即为叶子结点，索引段即为非叶子节点。
• 区(extent)：区是由连续页组成的空间，任何情况下每个区大小都为1MB。为了保证区中页的连续性、InnoDB默认一次从磁盘申请4~5个区，一个区有64个连续页。在段初始时，会先用32个页大小的碎片页进行数据存放，节省小表与undo段的资源开销，用完之后才会进行区资源的申请。
• 页(page)：页是InnoDB磁盘管理的最小单位。在InnoDB默认每个页大小为16KB，其常见类型有：
  • 数据页（B-tree Node）
  • undo页（undo Log Page）
  • 系统页（System Page）
  • 事务数据页（Transaction system Page）
  • 插入缓存位图（Insert Buffer Bitmap）
  • 插入缓存空闲列表页（Insert Buffer Free List）
  • 未压缩的二进制大对象页（Uncompressed BLOB Page）
  • 压缩的二进制大对象页（compressed BLOB Page）