MySQL技术内幕：InnoDB存储引擎（第2章 InnoDB存储引擎）

MySQL技术内幕：InnoDB存储引擎（第2章 InnoDB存储引擎）

第2章 InnoDB存储引擎

2.3InnoDB体系结构

• 后台线程

  • master Thread

    核心后台线程，负责：数据异步刷新到磁盘，保证数据一致性，包括：脏页刷新，合并插入缓冲（insert buffer）、undo页的回收等

  • IO Thread

    大量使用AIO处理写请求，提高性能。
    IO Thread负责IO请求的回调（call back）处理。
    分类：write(4)，read(4)，insert buffer(1)和log(1) IO Thread。
    innodb_read_io_threads，innodb_write_io_threads进行设置。
    查看:show engine INNODB status;

    • 详情展示

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)

- Purge Thread

  事务被提交后，其使用的undolog可能不在需要，PurgeThread回收已使用并分配的undo页。
  innodb_purge_threads（4）
  查看：
  show variables where variable_name like '%innodb_purge_threads%';

- Page Cleaner Thread

  处理脏页刷新操作，减轻master thread的工作以及对于用户查询线程的阻塞，提供性能

• 内存

  • 缓冲池

 基于磁盘存储，按照页进行管理。
  通过缓存池提高性能。
  checkpoint机制刷新会磁盘。

• 参数：innodb_buffer_pool_size

  索引页
  数据页
  undo页
  插入缓冲（insert buffer）
  自适应哈希索引（adaptive hash index）
  InnoDB存储的锁信息（lock info）
  数据字典信息（data dictionary）等

• 数据页类型：（图）
  

  • 缓冲池实例

    可以有多个，每个页根据哈希值平均分配到不同缓存池实例中。
    innodb_buffer_pool_instances
    参考：
    show variables where variable_name like ‘%innodb_buffer_pool_instances%’;
    select * from information_schema.INNODB_BUFFER_POOL_STATS;
    show ENGINE INNODB status;

  • LRU、Free、Flush List（midpoint）

    默认缓存页大小：16K
    新页默认放到LRU列表 5/8处，midpoint位置。
    相关参数：innodb_old_blocks_pct=37，表示37%的位置。
    old为midpoint之后的列表，之前为new列表，即热点数据。
    不直接放到首部的原因是因为很容易导致热点数据被移除，下次读取该页时需要访问磁盘。

    innodb_old_block_time，默认1000，即1秒，设置读取到mid位置后多久会被放到热点数据中。

    常用命令：
    查看innodb状态：
    show engine innodb status;
    其他查看：
    select * from innodb_buffer_pool_status;
    select * from innodb_buffer_page_lru;
    

    • innodb状态说明（图）
      

    • unzip_LRU（图）
      
      

      innodb支持压缩功能，将原本16K压缩为1,2,4,8K，并通过unzip_LRU列表进行管理。
      LRU中的页，包含了unzip_LRU中的页。

    • 脏页（dirty page）（图）
      

      LRU列表页被修改后称为脏页。通过checkpoint机制将脏页刷新回磁盘，Flush列表的页即为脏页。脏页既存在于LRU列表中，也存在于Flush列表中。
      table_name为NULL，说明该页属于系统表空间。

  • 重做日志缓冲（redo log buffer）

    引擎首先将重做日志信息放到该缓冲区，然后按一定频率刷新到重做日志文件。
    不用设置很大，一般每秒都会刷新到日志文件，保证每秒产生事务辆在缓冲大小之内即可。
    innodb_log_buffer_size，默认8M。
    查看指令：
    show variables like ‘%innodb_log_buffer_size%’;

    • 刷新到重做日志文件条件

      1.master thread每一秒会刷写
      2.每个事务提交时会刷
      3.当重做日志缓冲池剩余空间小于1/2时

  • 额外的内存池

    在引擎中，对内存的管理是通过内存堆的方式（heap）进行的。每个缓冲池中有帧缓冲、缓冲控制对象，这些对象记录了诸如LRU、锁、等待等信息，这些对象内存需要从额外内存池中申请。
    当申请很大的缓冲池时，应该相应增大该值，

2.4checkpoint技术

问题：
1.即时将脏页刷新到磁盘开销非常大
2.如果宕机未刷新到磁盘的脏页数据丢失

解决办法：
1.事务数据库普遍采用write ahead log策略，先写重做日志，再修改页。当宕机时导致的数据丢失，可通过重做日志来完成数据恢复。保证ACID中的（Durability持久性）的要求。

checkpoint技术目的：
1.缩短数据库恢复时间
2.缓冲池不够用时，将脏页刷新到磁盘
3.重做日志不可用时，刷新脏页

• 缩短数据库恢复时间

  宕机时，checkpoint之前的脏页都已经刷盘，只需要对checkpoint之后的重做日志进行恢复，缩短恢复时间。

• 缓冲池不够用刷新脏页

  当LRU溢出最近最少使用页时，若此页为脏页，则需要强制执行checkpoint，将脏页刷新到磁盘

• 重做日志不可用刷新脏页

  重做日志都是循环使用的，不是无限大的。当需要被使用的重做日志将被覆盖时，必须强制产生checkpoint，将缓冲池中的脏页刷新到至少到当前重做日志位置。

2.5MasterThread工作方式（刷脏页、重做日志、合并插入缓冲、undo页）

由多个loop组成：
主循环（loop）
后台循环（background loop）
刷新循环（flush loop）
暂停循环（suspend loop）

• 主循环（loop）

  • 每1秒执行

    1.日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）
    2.合并插入缓冲（innodb_io_capacity*5%）（可能）
    3.当前脏页占比大于__mindmap__topicinnodb_max_dirty_pages_pct（脏页占比，默认75%）时，刷新innodb_io_capacity个脏页到磁盘（可能）
    或者（如果开启自适应刷新）
    执行自适应刷新
    4.如果没有用户活动切换到backgroup loop

  • 每10秒执行

    1.刷新innodb_io_capacity个脏页到磁盘（可能的情况下）
    2.合并至多innodb_io_capacity5%个插入缓冲（总是）
    3.将日志缓冲刷新到磁盘（总是）
    4.删除无用的Undo页（总是）
    5.脏页大于innodb_max_dirty_pages_pct，将innodb_io_capacity刷新到磁盘；如果小于则刷新innodb_io_capacity10%到磁盘

• 后台循环（background loop）

  1.删除无用undo页（总是）
  2.合并innodb_io_capacity个插入缓冲（总是）
  3.跳回到住循环（总是）

  • 刷新循环（flush loop）（脏页占比高，不停刷脏页）

    脏页占比大于innodb_max_dirty_pages_pct时，
    不断刷新innodb_io_capacity个脏页，直到符合条件。

    • 挂起循环（suspend_loop）

      如果flush loop中么有事情，会切换到suspend_loop，将master thread挂起，等待事件发生。

• 相关参数

  • innodb_io_capacity

    1.合并插入缓冲时，合并插入缓冲数量为innodb_io_capacity * 5%
    2.从缓冲区刷新脏页时，数量为innodb_io_capacity

    该值默认为200。SSD盘可调高该值

  • innodb_max_dirty_pages_pct（脏页占比，默认75%）

  • innodb_adaptive_flushing（自适应刷新）

    该值影响每秒刷新脏页数量。原本是小于innodb_max_dirty_pages_pct比例不刷新的。现通过开启该参数，会通过buf_flush_get_desired_flush_rate判断产生重做日志（redo log）的速度，决定合适刷新脏页的数量。

  • innodb_purge_batch_size（控制回收undo页数量，默认20）

• loop（图片）

  • 
    

  • innodb1.2.x版本master thread
    

参考

2.6Innodb关键特性

• 插入缓冲（Insert Buffer）

  • 注意

    并不是所有主键插入顺序都是自增的。比如UUID这类的，是随机的。即使主键是自增的，但是插入指定值，而不是null，也会导致插入非连续的情况

  • 作用：提高非唯一（unique）辅助索引插入性能

    对于非聚集索引的插入或更新操作，不是每次直接插入到索引页，而是先判断该索引页是否在缓冲池中，若在，则直接插入；若不再，则先放到Insert Buffer对象中。再以一定频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，通常能将多个插入合并到一个操作中（因为在一个索引页中），大大提高非聚集索引插入性能。

  • 条件：辅助索引；非唯一（unique）

  • 缺点：

    1.大批量插入时，大量非唯一辅助索引写入到Insert buffer中，还没合并到实际非聚集索引页中，发生宕机。恢复可能需要很长时间。
    2.如果是唯一索引，需要离散去读取索引页进行判断，所以不能是唯一索引。

  • 信息说明（图）
    1.seg size：当前Insert Buffer的大小 11336*16K=177M
    2.free list len:空闲队列长度
    3.size：已经合并记录页数量
    4.Inserts：插入记录数
    5.merged recs：合并记录数量
    6.merges：合并次数，也就是实际读取也的次数。
    merges：merged recs=1:3，说明对非聚集索引页的离散IO逻辑请求降低了2/3.
    

  • 参数：

    1. 旧版本：IBUF_POOL_SIZE_PRE_MAX_SIZE=3，即为最大为缓冲池1/3内存

  • Change Buffer（为Insert Buffer升级版）

    1. 对DML操作（Insert，Delete，Update）都进行缓冲，分别为：Insert Buffer， Delete Buffer， Purge Buffer

    • 参数：

      1. innodb_change_buffering：开启各种buffer选项，inserts，deletes，purges，changes（deletes、inserts），all，none。默认：all
         2.innodb_change_buffer_max_size控制大小，默认25，即最多使用1/4的缓冲空间，最大有50%。

  • 内部实现（B+树）（包含插入记录、辅助索引页）

    全局只有一颗Insert Buffer B+树，存放在共享表空间，默认ibdata1.

    • 非叶子节点（space（4），marker（1），offset（4））

    • 叶子节点（space（4），marker（1），offset（4），metadata（4），二级索引记录）

    • 结构：辅助索引页（space，page_no）

      space：表空间id，每个表唯一
      offset、page_no：索引页

    • Insert Buffer Bitmap（保证每次合并成功）

      标记辅助索引页可用空间
      标记该辅助索引页是否有记录缓存在Insert Buffer B+数中
      该页是否为Insert Buffer B+数的索引页

  • Merge Insert Buffer（合并插入）执行条件：

    • 辅助索引页被读取到缓冲池时

      发现有插入记录存在，则合并到辅助索引页中

    • Insert Buffer Bitmap页追踪到该辅助索引页已无可用空间时

      插入辅助索引记录时检测到插入后剩余空间会小于1/32页，会强制进行一个合并操作。即强制读取辅助索引页，并将记录插入到辅助索引页中。

    • master thread（每秒/每10秒）

• 两次写（double write）

  • 问题：

    部分写失效（paratial page write）：宕机时，页只写了一部分，比如4K（总计16K）。
    重做日志记录的是对页的物理操作。即应用重做日志前，需要有一个页的副本，当写入失效时，先通过该副本进行还原该页，再进行重做。

  • 组成：

    1.内存中2M的doublewrite buffer
    2.物理磁盘上共享表空间中连续128页，即2个分区（extent），大小为2M。

  • 流程：（图）

    1.刷新脏页时，先复制到内存中doublewrite buffer中
    2.buffer再分两次（1M）顺序写入到共享表空间磁盘上，并同步刷盘（fsync），避免缓存问题。（顺序写，开销不大）
    3.完成后，再讲buffer中页离散写入到各表空间文件中。
    

  • 参数：innodb_doublewrite相关

• 自适应哈希索引（adaptive hash index）

  引擎会监控对表上各索引的查询，如果能提升速度，则建立哈希索引（自适应哈希索引，Adaptive Hash Index， AHI），AHI是通过缓冲池B+树页构造的，建立速度很快。自动根据访问频率和模式为热点页建立哈希索引。

  • 要求：

    1.访问模式必须是一样的（查询条件一样）
    2.访问了100次
    3.页通过该模式访问了N次，N=页中记录*1/16

  • 参数：innodb_adaptive_hash_index（图）

    • 

• 异步IO（Async IO）

  • 说明：

    由IO线程发出IO请求，不用等待，可发出其他IO请求，发送完成后，等待所有IO操作完成。

  • 优势：

    1.不会阻塞线程
    2.可将多个（连续页读写操作）IO合并成一个IO

  • 参数：innodb_use_native_aio

• 刷新邻接页（Flush Neighbor Page）

  • 说明：

    1.当刷新一个脏页时，检查该页所在区（extent）的所有页，如果是脏页，则一起刷新。

  • 优势：

    将多个IO合并为一个IO，对传统机械硬盘优势明显

  • 参数：innodb_flush_neighbors（固态硬盘建议关闭：0）

2.7启动、关闭与恢复

• 关闭参数说明：innodb_fast_shutdown

  0表示关闭时，需要完成所有的full purge和merge inset buffer，并将所有脏页刷新回磁盘，耗时较长。
  1为默认值，不需要完成full purge和merge insert buffer操作，但是需要将一些脏页刷新到磁盘。
  2表示不完成full purge和merge insert buffer操作，也不用刷新脏页回磁盘，只是将日志写入完成（不会丢失事务）。下次启动时进行恢复，恢复时间可能较长。

• 恢复参数：innodb_force_recovery（0-6，默认0，自动恢复）

  默认0，进行完整的恢复操作
  1-6，大数字包含前面所有小数字表示的影响（根据用户设定进行部分或不恢复，由用户自己进行恢复）