MySQL Innodb关键特性之插入缓冲(insert buffer)

什么是insert buffer?

插入缓冲,也称之为insert buffer,它是innodb存储引擎的关键特性之一,我们经常会理解插入缓冲时缓冲池的一个部分,这样的理解是片面的,insert buffer的信息一部分在内存中,另外一部分像数据页一样,存在于物理页中。

在innodb中,我们知道,如果一个表有自增主键,那么对于这个表的默认插入是非常快的,注意,这里的主键是自增的,如果不是自增的,那么这个插入将会变成随机的,就可能带来数据页分裂的开销,这样,插入就不是顺序的,就会变慢。还有一种情况,就是如果我们插入的id不是顺序的,而是随机的,那么即使有自增主键,那么插入的速度也不会特别快。

如果我们定义了一个表,包含一个主键和一个非聚集索引,如下:

create table t(

a int auto_increment,

b varchar(30),

primary key(a),

key (b)

);

当我们按照主键a进行插入的时候,对于非聚集索引,也就是常说的二级索引b,它的插入不是顺序的,插入性能必然会下降。

Innodb存储引擎针对这种情况,设计了Insert Buffer,对于非聚集索引的插入或者更新操作,不是每一次插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,如果在,则直接插入,如果不在,则先放入一个insert buffer中,告诉数据库这个非聚集的索引已经插入到了叶子节点,实际上并没有插入,只是存放在另外一个位置,然后再以一定的频率和情况进行Insert buffer和辅助索引叶子节点合并操作。这种时候,经常能将多条记录的插入合并到一个操作中,这样就大大提高了非聚集索引离散插入的性能。

insert buffer的触发条件?

insert buffer需要满足两个条件才能被使用,第一,索引是辅助索引,也就是二级索引,第二,索引不是唯一的。当满足上述两个条件的时候,就可以使用insert buffer,从而提高数据库的插入操作性能。

这里需要注意,如果在程序进行了大量操作的时候发生了MySQL数据库的宕机,那么肯定有大量的insert buffer没有合并到实际的非聚集索引中去,恢复可能会造成很长的时间。

为什么不能是唯一索引?

之所以不支持唯一索引,是因为如果辅助索引是唯一索引,那么在插入时需要校验唯一性,校验唯一性的时候就会发生离散读取,从而又增加了开销,那么insert buffer得不偿失。

我们可以通过show engine innodb status来查看insert buffer的使用情况,如下:

mysql--root@localhost:dms_alimetadata 20:35:24>>show engine innodb status\G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

其中size代表了已经合并记录页的数量,free list len代表了空闲列表的长度,seg size显示了当前insert buffer的大小为2*16KB

引入Change Buffer的概念

最新的MySQL5.7已经支持change buffer,事实上,它在innodb 1.0.x版本已经引入,这个change buffer 可以理解为insert buffer的升级,也就是对常见的DML语言都可以进行缓冲,包含insert delete以及update,对应的分别是insert buffer,delete buffer以及purge buffer。

当然,change buffer的使用对象仍然是非唯一的辅助索引。

这里我们以update操作为例,update的过程可以拆分为两个部分:

第一个部分是将记录的delete_mask标记为删除,如果你不了解delete_mask,可以在4月9号的文章中去看。第二个部分是真正的将记录删除。

而delete buffer对应的是update的第一个过程,purge buffer对应的是第二个部分。

在innodb中,我们可以通过参数innodb_change_buffering来开启buffer的各种选项,该参数可选的值为inserts,deletes,purges,changes,all,none等,其中inserts,deletes和purges就是前面讨论过的情况,changes表示开启inserts和deletes,all表示开启所有,默认的参数如下:

mysql--root@localhost:dms_alimetadata 21:13:37>>show variables like '%buffering%';        
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+
1 row in set (0.01 sec)

我们还可以通过innodb_change_buffer_max_size来控制change_buffer的最大使用内存数量,该参数的默认值是25,也就是1/4,示例如下:

mysql--root@localhost:dms_alimetadata 21:20:52>>show variables like '%innodb_change_buffer_max_size%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.00 sec)

在上面的show engine innodb status命令的输出结果中,显示了merged operation和discarded operation,其中insert 表示insert buffer的操作次数,delete mark表示delete buffer的操作次数,而delete表示purge buffer的操作次数,discarded operation表示当change buffer发生merge时,表已经被删除,此时就无需进行合并。

Insert Buffer的实现?

insert buffer的数据结构是一棵B+树,类似聚集索引一样,全局只有一棵insert buffer B+树,它负责对所有的表进行insert buffer,而这棵B+树放在共享表空间中,也就是ibdata1文件中,因此,试图通过ibd文件恢复表数据的时候可能会出现check table失败,原因是表的辅助索引中的数据可能还在insert buffer中,所以通过ibd文件恢复文件之后,还需要进行repair table操作来重建表上的辅助索引。

insert buffer既然是一棵树,那么必定有叶子节点和非叶子节点,非叶子节点存放的是查询的search key值,它的构造如下:

+---------+------------+-------+
| space   |   marker   | Value |
+---------+------------+-------+

这个结构一共占用9个字节,其中,space表示待插入的记录所在的表的表空间id,这个id是每个表都要有的唯一的id,其中space占用4个字节,marker占用1个字节,用来兼容老版本的insert buffer,offset占用4个字节,表示页所在的偏移量。

辅助索引的插入过程?

当一个辅助索引要插入到数据页的时候,如果这个数据页不在缓冲池中,那么innodb会根据规则构造一个search key,接下来将这个记录插入到insert buffer的B+树里面去,插入的过程中,需要对这个记录进行一些构造,最终插入的结果是类似下面这样的一条记录:

+---------+------------+-------+------------+------+-------+------+-------+
| space   |   marker   | Value | metadata   |      |       |      |       |
+---------+------------+-------+------------+------+-------+------+-------+

可以发现,最后面多了一个metadata的字段和4个其他的字段,先来说说metadata的字段,它占用4个字节,它用来排序每个记录进入insert buffer的顺序,从第5列开始,就是实际插入记录的各个字段的值了,因此和单纯的数据记录相比,insert buffer需要额外13个字节的开销。

为了保证每次merge insert buffer成功,需要设置一个特殊的数据页来标记每个辅助索引页的可用空间,这个数据页的类型为insert buffer bitmap,这个页可以追踪很多辅助索引页的可用空间。这里简单了解一下,下面会解释它的用法。

Merged Insert Buffer的时机?

我们前面已经知道,当插入记录的辅助索引页不在缓冲池中的时候,需要将辅助索引记录插入到这棵B+树中,后续会从insert buffer中往真正的辅助索引中进行合并,那么什么时候进行合并呢?

1、辅助索引页被读取到缓冲池的时候

2、insert buffer Bitmap追踪到该辅助索引页已经没有足够的可用空间时,一般的阈值是辅助索引页空间的1/32

3、master thread每秒执行一次merge insert buffer的操作

以上就是MySQL Innodb关键特性之插入缓冲(insert buffer)的详细内容,更多关于Innodb特性之插入缓冲的资料请关注我们其它相关文章!

(0)

相关推荐

  • MySQL MyISAM 与InnoDB 的区别

    区别: 1. InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务: 2. InnoDB支持外键,而MyISAM不支持.对一个包含外键的InnoDB表转为MYISAM会失败: 3. InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高.但

  • 浅谈innodb的索引页结构,插入缓冲,自适应哈希索引

    Physical Structure of an InnoDB Index 所有的innodb索引都是btree索引,索引记录保存在叶子上,默认的索引页大小是16K.当有新的记录插入时,innodb出于对将来的insert和update操作的考虑,会尝试留下1/16的空闲页大小. 如果索引记录是完全按照索引记录的大小顺序插入的,那么索引也将填满整个页大小的15/16,如果插入顺序完全随机,那么索引页基本上填充为1/2至15/16自建.如果填充因子低于1/2,innodb会尝试重建b-tree.

  • MySQL InnoDB 锁的相关总结

    1.  Shared and Exclusive Locks shared lock (译:共享锁) exclusive lock (译:排它锁.独占锁) InnoDB实现了标准的行级锁,其中有两种类型的锁,共享锁(shared locks)和排他锁(exclusive locks). A shared (S) lock permits the transaction that holds the lock to read a row. An exclusive (X) lock permits

  • mysql innodb的重要组件汇总

    innodb包涵如下几个组件 一.innodb_buffer_pool: 它主要用来缓存数据与索引(准确的讲由于innodb中的表是由聚集索引组织的,所以数据只不是过主键这个索引的叶子结点). 二.change buffer: 1 如果更新语句要更新二级索引的记录,但是记录所在的页面这个里面并没有在innodb_buffer_pool中,innodb会把这个对二级索引 面页的更新动作缓存到innodb_buffer_pool的一个特定区域(change buffer):等到之后如果有别的事务B要

  • Mysql InnoDB的锁定机制实例详解

    1.InnoDB的锁定机制 InnoDB存储引擎支持行级锁,支持事务处理,事务是有一组SQL语句组成的逻辑处理单元,他的ACID特性如下: 原子性(Atomicity): 事务具有原子不可分割的特性,要么一起执行,要么都不执行. 一致性(Consistency): 在事务开始和事务结束时,数据都保持一致状态. 隔离性(Isolation): 在事务开始和结束过程中,事务保持着一定的隔离特性,保证事务不受外部并发数据操作的影响. 持久性(Durability): 在事务完成后,数据将会被持久化到数

  • 如何区分MySQL的innodb_flush_log_at_trx_commit和sync_binlog

    innodb_flush_log_at_trx_commit和sync_binlog  两个参数是控制MySQL磁盘写入策略以及数据安全性的关键参数. show variables like "innodb_flush_log_at_trx_commit"; innodb_flush_log_at_trx_commit: 0: 由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file,并调用文件系统的sync操作,将日志刷新到磁盘.

  • 获取 MySQL innodb B+tree 的高度的方法

    前言 MySQL 的 innodb 引擎之所以使用 B+tree 来存储索引,就是想尽量减少数据查询时磁盘 IO 次数.树的高度直接影响了查询的性能.一般树的高度在 3~4 层较为适宜.数据库分表的目的也是为了控制树的高度.那么如何获取树的高度呢?下面使用一个示例来说明如何获取树的高度. 示例数据准备 建表语句如下: CREATE TABLE `user` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(100) CHARAC

  • 修改MySQL数据库引擎为InnoDB的操作

    PS:我这里用的PHPStudy2016 1.修改时停止MySQL 2.修改my.ini default-storage-engine=INNODB 3.修改后删除D:\phpStudy\MySQL\data下ib开头的日志文件 4.启动MySQL 设置好后,通过navicat创建与目标数据库一致编码的新数据库,然后复制过来即可.复制过来的数据表数据引擎与源数据表数据引擎一致. 注:修改数据表引擎 alter table tableName type=InnoDB 补充:InnoDB和MyISA

  • MySQL创建数据表时设定引擎MyISAM/InnoDB操作

    我在配置mysql时将配置文件中的默认存储引擎设定为了InnoDB.今天查看了MyISAM与InnoDB的区别,在该文中的第七条"MyISAM支持GIS数据,InnoDB不支持.即MyISAM支持以下空间数据对象:Point,Line,Polygon,Surface等." 作为一个地理信息系统专业的学生(其实是测绘专业)来讲,能存储空间数据的数据库才是好数据库,原谅我是数据库小白的身份. 有三种方式可以设定数据库引擎: (1)修改配置文件 将安装目录下~\MySQL\mysql-5.6

  • Mysql技术内幕之InnoDB锁的深入讲解

    前言 自7月份换工作以来,期间一直在学习MySQL的相关知识,听了一些视频课,但是一直好奇那些讲师的知识是从哪里学习的.于是想着从书籍中找答案.毕竟一直 看视频也不是办法,不能形成自己的知识.于是想着看书汲取知识,看了几本MySQL的相关书籍,包括<深入浅出Mysql><高性能Mysql><Mysql技术内幕>,发现那些讲 师讲的内容确实都在书上有出现过,于是确信看书才是正确的汲取知识方式.本片主要记录了Mysql的锁机制的学习. 1.什么是锁 锁是计算机协调多个进程或

  • Mysql InnoDB和MyISAM区别原理解析

    mysql支持很多表类型的表(即存储引擎),如myisam.innodb.memory.archive.example等.每种存储引擎都有自己的优点和缺点,充分的理解每种存储引擎,有助于合理的使用它们.有人认为在同一个数据库中使用多种存储引擎很影响性能,其实这是一种十分错误的想法.实际上,除非是非常简单的数据库,否则的话,只使用一种存储引擎,对应用程序的性能来说是一个十分糟糕的行为.对数据库了解的人会根据每张表的作用不同来选择适当的存储引擎,这才是正确的做法. 前面说过mysql的存储引擎很多,

随机推荐