MySQL 5.6 插入缓冲测试

设置

autocommit = off
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 134217728
innodb_change_buffer_max_size = 25
innodb_change_buffering = all

建表

create table t1 (id int auto_increment not null primary key, name varchar(30), key(name)) engine=innodb;

插入数据

delimiter //
CREATE PROCEDURE p1 (n int)
BEGIN
  SET @x = 0;
  WHILE @x < n DO
    INSERT INTO t1 (NAME)
      VALUES (FLOOR(1 + RAND() * (n - 1)));
    SET @x = @x + 1;
  END WHILE;
  COMMIT;
END;
//

delimiter ;

call p1 (10000000);

Query OK, 0 rows affected (8 min 23.79 sec)

查看插入缓冲状态

show engine innodb status\G
......
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3081, seg size 3083, 50425 merges
merged operations:
 insert 2481177, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 1 buffer(s)
0.00 hash searches/s, 2902.60 non-hash searches/s
......

说明：

插入1000万条记录
执行时间：8 min 23.79 sec
当前插入缓冲的大小为：3083 * 16K
合并次数：50425
执行insert操作数：2481177

对比测试：

设置

autocommit = off
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 134217728
innodb_change_buffer_max_size = 25
innodb_change_buffering = none

call p1 (10000000);

Query OK, 0 rows affected (22 min 18.85 sec)

查看插入缓冲状态

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3081, seg size 3083, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s