目的
- 提高对于非聚集索引插入的性能
原因
- 在一个具有非聚集且非唯一索引的表中做插入时,数据页的存放还是按主键(primary key) 进行顺序存放的,但是对于非聚集索引叶子节点的插入不再是顺序的了,这时就需要离散的访问非聚集索引页,由于随机读取的存在而导致了插入操作性能下降,所以需要使用插入缓冲
步骤
- 对于非聚集索引的插入或更新操作,首先判断插入的非聚集索引页是否在缓冲池中
- 若在,则直接插入
- 若不在,则先放入一个Insert Buffer 对象中
- 然后再以一定频率和情况进行Insert Buffer 和辅助索引页子节点的 merge 操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页中)
使用条件
- 索引是辅助索引
- 索引不是唯一的
使用条件中的原理
- 若索引是聚集索引,本身索引中就包含了数据,所以插入数据页即可,没必要使用insert buffer。
- 若索引是唯一非聚集索引,当插入索引页时需要判断唯一性,判断唯一性的过程中需要查找,查找必定可以找出插入的位置(如果不存在插入的索引页时),这时最高效的做法应该是直接将数据插入,但是如此insert buffer 就没有意义
存在问题
- 应用程序进行大量插入操作,且这些操作都涉及了不唯一的非聚集索引
- 若此时MySQL 数据库发生宕机,这样回导致大量的Insert Buffer 并没有合并到实际的非聚集索引中去
- 从而导致恢复的时间过长,在极端情况下甚至需要几个小时
change buffer(略,讲的不是很清楚)
- 可以视为Insert Buffer 的升级
- 分类
- Insert Buffer
- Delete Buffer
- Purge Buffer
- 适用对象:非唯一的辅助索引
- 对应的数据库操作:INSERT、DELETE、UPDATE
- 一个数据操作可能会使用到两种Buffer,例如UPDATE
- Delete Buffer
- PurgeBuffer
- 一个数据操作可能会使用到两种Buffer,例如UPDATE
Insert Buffer 的内部实现
- Insert Buffer 的数据结构是一棵B+树
- MySQL 4.1 之前,每张表有一棵Insert Buffer B+树
- MySQL 4.1 之后,全局只有一棵Insert Buffer B+树 负责对所有的表的辅助索引进行Insert Buffer
- 这棵B+ 树存放在共享表空间。
- 因此,试图通过独立表空间的ibd 文件恢复表中数据时,往往会导致CHECH TABLE 失败。因为表的辅助索引中的数据可能还在Insert Buffer 中
- 所以通过ibd 文件进行恢复后,还需要进行repair table 操作来重建表上所有的辅助索引
- Insert Buffer B+树的非叶节点存放的数据——search key
注:该叶节点存放的是查询的 search key。search key 一共占用9字节, 其中: space 占用4字节,表示待插入记录所在表的表空间id。(在InnDB 存储引擎中,每 个表有唯一的space id,可以通过space id 查询得知是哪张表)。 marker 占用1字节,它是用来兼容老版本的Insert Buffer offset 占用4字节,表示页所在的偏移量
- Insert Buffer B+树的叶节点存放的数据
注:space、marker、offset 同非叶节点中的含义相同,共占9字节。 另外: metadata 占用4 字节,存储的内容如下:
名称 | 字节 | 含义 |
---|---|---|
IBUF_REC_OFFSET_COUNT | 2 | 该记录进入Insert Buffer 的顺序 |
IBUF_REC_OFFSET_TYPE | 1 | |
IBUF_REC_OFFSET_FLAGS | 1 |
secondary index record:就是非聚集索引页
- Insert Buffer B+树在插入时的使用过程
- search key 的构造
- 当一个辅助索引要插入到页时,如果页不在缓冲池中,则InnoDB 存储引擎会构造一个对应search key
- 记录的插入
- 当构造完search key 时,InnoDB会将其插入到B+树中,并且叶节点的构造规则如上所述。search key + metadata + 记录
- search key 的构造
Merge Insert Buffer
- 含义:将Insert Buffer 的记录插入合并到真正的辅助索引中
- 操作发生的时机
- 辅助索引页被读取到缓冲池时
- Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时
- Master Thread 自主每秒或每10秒进行一次Merge Insert Buffer
- 执行merge 操作页数取决于srv_innodb_io_capactiy的百分比