2.1 第二章 InnoDB 关键特性

1. 插入缓冲

DQL: 数据库查询语言(select)

DML:数据库操作语言(insert,updata,delete)

DDL:数据库定义语言(create)

DCL:数据库控制语言(grant)

1.1 insert buffer

​ insert buffer 可能在磁盘上也可能在内存中

​ 聚集和非聚集索引

​ innodb 三大特性之insert buffer

​ 你插入MySQL的数据真的存到表里了么？

​ insert buffer 作用:进行二级索引(Secondary index,辅助索引)相关的 DML 的性能优化.

​ insert buffer 原理:对于非唯一索引,辅助索引的修改操作并非实时更新索引的叶子页,而是把若干对同一页的更新缓存起来,合并为一次操作,减少IO,转随机IO为顺序IO,这样避免随机 IO 带来的性能损耗,提高了数据库的写性能.

对于非聚集索引的插入/更新操作,

不是每次都直接插入到索引页(index page)中

而是先判断插入的非聚集索引页是否在缓冲池中,

若在,则直接插入

若不在,则先放到一个 Insert Buffer 对象中.

然后在以一定的频率和情况进行 Insert Buffer 和 辅助索引页子节点的 merge(合并).

​ 使用 Insert Buffer 需要满足的条件:

• 索引是辅助索引(secondary index)

  非聚集索引:

  • 解释一:针对数据得索引列生成了一个排好序的索引页,而他本身的数据在物理存储上没有顺序可言.

  • 解释二:叶子节点中存储主键值,根据索引找到叶子节点中的主键值,根据主键值再到聚集索引中得到完整的一行记录.

  聚集索引:

  • 解释一:索引排好的顺序和在物理存储上的存储顺序一致.
  • 解释二:聚集索引的叶子节点存储了一行完整的数据,而二级索引只存储了主键值.

• 索引不是唯一的(unique)

  唯一索引:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一.在对该列进行操作的时候,首先会检查是否重复,如果重复会报 duplicate 错误,拒绝操作.

  ALTER TABLE USER ADD UNIQUE NUMBER_INDEX(NUMBER);

  索引不能是唯一的原因:

  因为在插入缓冲时候,数据库并不会查找索引页来判断记录的唯一性,如果去查找肯定又会有离散读取的情况发生,会导致 insert buffer 失去了意义.

使用 show engine innodb status \G; 查看插入缓冲信息:

• seg size 11336:显示了当前 insert buffer 的大小 11336*16≈117MB
• free list len:代表了空闲列表的长度
• size :代表了已经合并记录页的数量
• inserts:表示插入记录的次数
• merged recs:表示合并的插入记录数量
• merges:表示合并的次数,即实际读取页的次数
• merges:merged recs ≈ 1:3,代表了插入缓冲对于非聚集索引页的离散 IO逻辑请求大约降低了 2/3.

insert buffer 存在的问题:

​ 在写操作密集的场景下,插入缓冲会占用过多的缓冲池内存,默认最大可以占用1/2,可通过修改参数 IBUF_POOL_SIZE_PER_MAX_SIZE 来控制插入缓冲的大小.改为3,表示占用缓冲池的1/3.

1.2 Change Buffer

​ InnoDB 1.0.x 版本引入 Change Buffer,可视为 Insert Buffer 的升级.可以对DML(Insert,Delete,Update)操作都进行缓冲,分别为 insert buffer,delete buffer,purge buffer.

​ 适用对象仍然是 非唯一的辅助索引

​ 对一条数据进行 updata 操作分为两个过程:

• 将该记录标志为已删除 (delete buffer)
• 真正的将该记录删除 (purge buffer)

1.3 Insert Buffer 内部实现

​ Insert Buffer 的数据结构是 B+ 树,MySQL4.1 之前版本,每张表都有一颗 Insert Buffer B+ 树,而现在版本,**全局只有一颗 Insert Buffer B+ 树,**负责对所有表的辅助索引进行 Insert Buffer.存放在 共享表空间中(默认为 indata1).试图通过独立表空间 ibd 文件恢复表中的数据时,往往会导致 check table 失败,因为表的辅助索引数据可能在 Insert Buffer 中,即共享表空间中,所以通过 ibd 文件恢复后,还要使用 repair table 操作重建表上所有的辅助索引.

​ Insert Buffer 是一颗 B+ 树,由叶节点和非叶节点组成.非叶节点存放查询的 search key.结构如下:

• 共占9字节
• space(占4字节)表示待插入记录所在表的表空间 id,每个表有唯一的 space id
• maker(占1字节):用来兼容老版本的 Insert Buffer
• offset(占用4字节):表示页所在的偏移量
• 当一个索引页要插入到页(space,offset)时,如果不在缓冲池中,则根据上面的规则构造一个 search key,接着查询 insert buffer 这颗 B+ 树,然后再将这个记录插入到 Insert Buffer B+ 树的叶子节点中.

​ 对于插入到 Insert Buffer B+ 树叶子节点的记录,并不是直接将待插入的记录插入,而是根据下面规则构造:

• metadata占用4个字节:

  IBUF_REC_OFFSET_COUNT : 2

  ​ 用来排序每个记录进入 insert buffer 的顺序.这个值还记录了进入Insert Buffer 的顺序,通过这个顺序回放才能得到正确的记录值.

  IBUF_REC_OFFSET_TYPE : 1

  IBUF_REC_OFFSET_FLAGS : 1

• 第5列开始 实际插入记录的各个字段.相比原插入记录,多了13个字节的开销

1.4 Insert Buffer Bitmap

​ 启动 Insert Buffer 索引后,辅助索引页(space,offset)中的记录可能被插入到 Insert Buffer B+树中,所以为了保证每次 Merge Insert Buffer 页必须成功,还需要一个特殊的页用来标记每个辅助索引页(space,offset)的可用空间----Insert Buffer BitMap.

• 每个 Insert Buffer Bitmap 页用来追踪 16384 个辅助索引页,也就是 256个区(Extent).
• 每个 Insert Bufffer Bitmap 页都在 16384 个页中的第二个页中.
• 每个辅助索引页在 Insert Buffer Bitmap 页中占用 4 位(bit).由下表2-3中的数据构成.

1.5 Merge Insert Buffer

​ Merge Insert Buffer 操作可能发生的几种情况:

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

  例如执行 select 操作,

  • 需要检查 Insert Buffer Bitmap 页,确认该辅助页是否有记录存在于 insert buffer B+ 树中.
  • 若有,则将Insert Buffer B+ 树中该页的记录插入到该辅助索引中

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

  Insert Buffer Bitmap 页用来追踪每个辅助索引页的可用空间,并至少有1/32页的空间.若插入辅助索引记录时检测到插入记录后可用空间小于1/32页,则会进行强制合并操作,即强制读取辅助索引页,将Insert Buffer B+ 树中该页的记录及待插入的记录插入到辅助索引页中.

• Master Thread

2. 两次写

​ 详解MySQL两次写的设计及实现

​ Insert Buffer 给 InnoDB 存储引擎带来了性能上的提升,而 doublewrite 带给 innodb 存储引擎数据页的可靠性.

解决的问题: 当数据库宕机时,可能 InnoDB 存储引擎正在写入某个页到表,而这个页只写了一部分,比如16K 的页,只写了前 4 K,之后就宕机了,这种情况称为部分写失效.未使用 doublewrite 技术前,曾出现因为部分写失效而导致数据丢失的情况.

解决方案:

• 通过 redo log 进行恢复 redo log 记录的是对页的操作,如果这个页本身损坏了,则无法恢复了.
• 因此,在应用 redo log 之前 用户需要一个副本,在写入失效发生时,先通过副本来还原页,在进行重做. 这就是 doublewrite

dobulewrite 流程

• doublewrite 有两部分,一部分在内存中,一部分在共享表(默认为 ibdata1)中.大小都是2MB.
• 1)对脏页进行刷新,不直接写磁盘,通过 memcpy 将脏页先复制到 内存中的 doublewrite buffer 中,
• 2)通过 doublewrite buffer 分两次,每次1MB顺序写入共享表空间的物理磁盘上,然后电泳 fsyn 函数,同步磁盘,避免缓冲写带来的问题
• 3)将 doublewrite buffer 中的页离散的写入各个表空间文件中

观察 doublewrite 的运行情况

• 一共写了 6325194 个页,但实际写入次数 100399
• 符合 64 : 1 ,当系统高峰时比值远小于 64:1 ,说明系统写入压力不高.

如何通过 doublewrite 恢复数据

• 从 共享表空间中的 doublewrite 中找到该页的一个副本,将其复制到表空间文件,在应用重做日志.

SHOW GLOBAL STATUS 中 InnoDB_pool_pages_flushed 变量表示当前从缓冲池中刷新到磁盘页的数量.根据之前的介绍,默认情况下,所有页的刷新首先都需要放入到 doublewrite 中.因此变量应该和 Innodb_dblwr_pages_writen 一致.但是在 mysql 5.5.24 版本前,这两个变量值不一致.后面被修复. Innodb_dblwr_pages_writen 在所有版本中都是正确的.

3. 自适应哈希索引(Adaptive Hash Index)

​ 哈希查找的时间复杂度是 O(1).B+树的查找次数取决于树高,生产环境中,B+树的高度一般为3~4层.

​ InnoDB 会监控表上各索引页的查询.如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,即 自适应哈希索引(AHI).AHI 是通过缓冲池中的 B+ 树页构造而来的,因此建立速度很快,而且不需要对整张表结构构建哈希索引.会根据访问频率和模式自动为某些热点数据页建立哈希索引.

AHI 的要求

• 这个页的连续访问模式必须一样(查询条件一样).

  对于联合索引页(a,b)

  where a = “xxx”

  where a = “xxx” and b = “xxx”

  交替执行上面的两种查询,不会对该页构造 AHI

• 以该模式连续访问 100次

• 页通过该模式访问 N次,其中 N = 页中记录 *1/16

• 注: AHI 是数据库自优化,不需要 DBA 人为调整

查看 AHI 使用情况

• 黑体字:每秒使用哈希搜索 和 每秒不使用哈希搜索
• 哈希索引只能用来搜索等值查询(select … where a = “xxx”)

4. 异步 IO(AIO)

​ 与 AIO 对应的是 Sync IO,即每进行一次 IO 操作,需要等待此次操作结束才能继续接下来的操作.但是如果用户发出的是一条索引扫描的查询,那么这条 SQL 查询语句可能需要扫描多个索引页,也就是需要进行多次 IO 操作.每扫描一个页等待其完成后在进行下一次的扫描,这是没有必要的. 用户可以在发出一个 IO 请求后立即再发出另一个 IO 请求,当全部 IO 请求发送完毕之后,等待所有 IO 操作完成, 这就是 AIO.

​ AIO 的一个优势就是可以进行 IO Merge 操作,也就是将多个 IO 合并为 1 个 IO,这样可以提高 IOPS 的性能.

​ InnoDB 1.1.x 之前: AIO 通过 InnoDB 存储引擎中的代码模拟实现

​ InnoDB 1.1.x 开始: 提供了内核级别的 AIO 支持.Native AIO.需要 libiao 库支持.

5.刷新邻接页

​ InnoDB 提供了 Flush Neighbor Page(刷新邻接页)的特性. 工作原理为:当刷新一个脏页时,InnoDB 会检测该页所在区(extent)的所有页,如果是脏页,那么一起进行刷新. 这样的好处:通过AIO可以将多个 IO 写入操作合并为一个 IO操作,该工作机制在机械硬盘下有着显著的优势,但是需要考虑下面的问题:

• 可能会将不怎么脏的页进行了写入,而之后该页又会编程脏页?
• SSD 有着较高的 IOPS ,是否还需要这个特性?(SSD建议关闭此特性)

6. 启动 关闭 与 恢复

在关闭时,参数 innodb_fast_shutdown 影响着InnoDB 表存储引擎的行为.

• 0:表示MySQL关闭时,InnoDB完成所有的 full purge 和 merge insert buffer,并将所有的脏页刷新回磁盘.(耗时较长)
• 1(默认值):不需要完成 full purge 和 merge insert buffer,将缓冲池中一些数据的脏页刷新回磁盘
• 2:不完成 full purge 和 merge insert buffer ,也不将缓冲池中的数据脏页写回磁盘,而是将日志都写入日志文件,这样不会有事务的丢失,下次启动时,进行恢复操作.

innodb_force_recovery 影响了整个 InnoDB 的恢复情况.

7. 小结