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

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

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

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

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

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

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

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

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

  索引不能是唯一的原因:

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

• size :代表了已经合并记录页的数量
• inserts:表示插入记录的次数
• merges:表示合并的次数,即实际读取页的次数

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

? 适用对象仍然是 非唯一的輔助索引

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

文件恢复表中的数据时,往往会导致 check table 失败,因为表的辅助索引数据可能在 Insert Buffer 中,即共享表空间中,所以通过 ibd 文件恢复后,还要使用 repair table 操作重建表上所有的辅助索引.

• space(占4字节)表示待插入记录所在表的表空间 id,每个表有唯一的 space id
• offset(占用4字节):表示页所在的偏移量

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

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

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

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

• 若有,则将Insert Buffer B+ 树中该页的记录插入到该辅助索引中

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

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

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

• 2)通过 doublewrite buffer 分两次,每次1MB顺序写入共享表空间的物理磁盘上,嘫后电泳 fsyn 函数,同步磁盘,避免缓冲写带来的问题

• 符合 64 : 1 ,当系统高峰时比值远小于 64:1 ,说明系统写入压力不高.

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

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

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

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

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

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

• 以该模式连续访问 100次

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

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

查看 AHI 使用情况

• 黑体字:每秒使用哈希搜索 和 每秒不使用哈希搜索

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

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

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

• 2:不完成 full purge 和 merge insert buffer ,也不将缓冲池中的数据脏页写回磁盘,而是将日志都写入日志文件,这样不会有事务的丢失,下次启动时,进行恢复操作.