【MySQL】Insert buffer 漫谈

 我们知道在进行插入操作时,数据页的存放还是按主键id的执行顺序存放, 但是对于非聚集索引,叶子节点的插入不再是顺序的了。
例如,对于如下表结构进行insert操作
create table tab ( 
  id int auto_increment, 
  name varchar(30),
  primary key (id),
  key(name)
 ) engine=innodb  default charset=utf8;
nanme 为非唯一字段,这时就需要离散地访问非聚集索引页,插入性能在这里变低了。然而这并不是这个name字段上索引的错误,因为B+树的特性决定了非聚集索引插入的离散性。
为了解决非聚族索引的随机写性能差,InnoDB 存储引擎开发了 innsert-buffer pool (5.5 中做了加强,称之为 change buffer pool)

一 什么是 innsert-buffer pool
innodb使用insert buffer"欺骗"数据库:对于为非唯一索引,辅助索引的修改操作并非实时更新索引的叶子页,而是把若干对同一页面的更新缓存起来做合并为一次性更新操作,转化随机IO 为顺序IO,这样可以避免随机IO带来性能损耗,提高数据库的写性能。
1.1 原理:
   a 先判断要更新的这一页在不在内存中。
   b 如果不在,则读取index page 存入Insert Buffer,按照Master Thread的调度规则来合并非唯一索引和索引页中的叶子结点.

1.2 Master Thread的调度规则
  a 主动merger[innodb主线程定期完成,用户线程无感知]
    主动merger:
    原理:主动merge通过innodb主线程(svr_master_thread)判断:若过去1s之内发生的I/O小于系统I/O能力的5%,
        则主动进行一次insert buffer的meger操作。meger的页面数为系统I/O能力的5%,读取采用async io模式。
        每10s,必定触发一次insert buffer meger操作。meger的页面数仍旧为系统 I/O能力的5%。
    步骤:
        1.主线程发出async io请求,async读取需要被meger的索引页面
        2.I/O handler 线程,在接受到完成的async I/O之后,进行merger
  b 被动merge[用户线程完成,用户能感受到meger操作带来的性能影响]
    被动merge:
      情况一:
      insert操作,导致页面空间不足,需要分裂(split)。由于insert buffer只针对单个页面,不能buffer page split[页已经在内存里],因此引起页面的被动meger。同理,update操作导致页面空间不 足;purge导致页面为空等。总之:若 当前操作引起页面split or merge,那么就会导致被动merge。
      情况二:
      insert操作,由于其它各种原因,insert buffer优化返回false,需要真正读取page时,要进行被动merge。与一不同的是,页在disk上,需要读取到内存里。
      情况三:
      在进行insert buffer操作,发现insert buffer太大,需要压缩insert buffer,这时需要强制被动merge,不允许 insert 操作进行。

二 为什么要求是非唯一索引呢?
因为
  1 主键是行唯一的标示符,当app 写入行时,是按照主键递增的顺序进行插入的,异常插入聚族索引一般也顺序的,不需要随机IO。
  2 写唯一索引要检查记录是不是存在,所以在修改唯一索引之前,必须把修改的记录相关的索引页读出来才知道是不是唯一,这样Insert buffer就没意义了,反正要读出来(读带来随机IO),所以只对非唯一索引有效。

三 如何查看insert buffer  
我们可以通过show engine innodb status \G 来查看插入缓冲的信息
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 2920 merges
merged operations:
 insert 23858, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

seg size显示了当前插入缓冲的大小为2 *16KB,大约为32KB,free list len代表了空闲列表的长度,size代表了已经合并记录页的数量。merges 表示合并次数。
merged operations:
Inserts代表插入的记录数,delete mark delete 次数均为0.

四 insert buffer 增强之 change buffering
    change buffering 是MySQL5.5加入的新特性,change buffering是insert buffer的加强,insert buffer只针对insert有效,change buffering对insert、delete、update(delete+insert)、purge都有效。当修改一个索引块(secondary index)时的数据时,索引块在buffter pool中不存在,修改信息就会被cache在change buffer中,当通过索引扫描把需要的索引块读取到buffer pool时,会和change buffer中修改信息合并,再择机写回disk。目的还是为了减少随机IO带来性能损耗,说明白了:把随机IO尽量变成顺序IO。

五 小结
对于廉价的机械硬盘,这个参数还是能帮助提高性能的。在SSD盛行的今天,在SSD上随机访问和顺序访问性能几乎差不多的情况下,insert buffer/change buffering特性不会带来多大的性能提升。

时间: 2023-01-05

【MySQL】Insert buffer 漫谈的相关文章

php中mysql操作buffer用法详解

 这篇文章主要介绍了php中mysql操作buffer用法,以实例形式较为详细的分析了mysql操作buffer的技巧,具有一定参考借鉴价值,需要的朋友可以参考下     本文实例讲述了php中mysql操作buffer用法.分享给大家供大家参考.具体分析如下: php与mysql的连接有三种方式,mysql,mysqli,pdo.不管使用哪种方式进行连接,都有使用buffer和不使用buffer的区别. 什么叫使用buffer和不使用buffer呢? 客户端与mysql服务端进行查询操作,查询

mysql insert into select

问题描述 mysql insert into select insert into a(server_id) select server_id from b where server_id = 6 ; a表中 设置id 为自增长 在插入的时候 有时候 select 的查询结果是空 则insert 到 a表中的数据是空 : 有两个问题请教大家 1, 如何在 select返回结果是空的时候 给server_id赋值 ? 2 , 或者如何在select 有结果非空数据的时候插入到a表中 ? 求解救 求

php mysql insert into 结合详解及实例代码_Mysql

php mysql insert into 结合详解 ySQL INSERT INTO语句在实际应用中是经常使用到的语句,所以对其相关的内容还是多多掌握为好. 向数据库表插入数据 INSERT INTO 语句用于向数据库表添加新记录. 语法 INSERT INTO table_name VALUES (value1, value2,....) 您还可以规定希望在其中插入数据的列: INSERT INTO table_name (column1, column2,...) VALUES (valu

mysql insert的几点操作(DELAYED,IGNORE,ON DUPLICATE KEY UPDATE )_Mysql

INSERT语法 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 或: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INT

如何优化MySQL insert性能

对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间长.我们就有一个业务系统,每天的数据导入需要4-5个钟.这种费时的操作其实是很有风险的,假设程序出了问题,想重跑操作那是一件痛苦的事情.因此,提高大数据量系统的MySQL insert效率是很有必要的. 经过对MySQL的测试,发现一些可以提高insert效率的方法,供大家参考参考. 1.一条SQL语句插入多条数据. 常用的插入语句如: INSERT INTO `insert_table` (`datetim

正确使用MySQL INSERT INTO语句_Mysql

以下的文章主要介绍的是MySQL INSERT INTO语句的实际用法以及MySQL INSERT INTO语句中的相关语句的介绍,MySQL INSERT INTO语句在实际应用中是经常使用到的语句,所以对其相关的内容还是多多掌握为好. INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES (expression,...),(...),... MySQLINSERT INTO SELEC

mysql insert into一次插入多条记录sql语句

例如,你能这样:  代码如下 复制代码 MySQL> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); 插入多条数据库记录时,我们常这么写:  代码如下 复制代码 $name='soulcy'; for($i = 0; $i <= 5; $i++) {      $sql = "INSERT INTO users (uid, name) VALUES(".$i.", '".$name."')

Mysql insert 数据性能测试

前些时候对Mysql做了些测试,得到了一些测试数据.网上看到一些Mysql相关总结,并不能一味相信,还是需要看数据说话. 下面的测试用例都是插入1千万条数据,分别对三种类型的数据表类型进行测试(MYISAM,INNODB,NDB),测试用例是: NDB是官方的集群部署版本,测试数据是基于6.x.Mysql proxy也是官方的,用的好像是0.8.x版本,做过负载均衡,读写分离测试,负载均衡比较稳定,但是读写分离在多线程下则非常不稳定. 1.MYISAM类型数据表的单线程 2.INNODB类型数据

mysql insert into用法详解

MySQL INSERT INTO语句在实际应用中是经常使用到的语句,所以对其相关的内容还是多多掌握为好. 向数据库表插入数据 INSERT INTO 语句用于向数据库表添加新记录. 语法 INSERT INTO table_name VALUES (value1, value2,....)您还可以规定希望在其中插入数据的列:  代码如下 复制代码 INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)