设置
代码语言：javascript复制autocommit = off
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 134217728
innodb_change_buffer_max_size = 25
innodb_change_buffering = all建表
代码语言：javascript复制create table t1 (id int auto_increment not null primary key, name varchar(30), key(name)) engine=innodb;插入数据
代码语言：javascript复制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)
查看插入缓冲状态
代码语言：javascript复制show engine innodb statusG
......
-------------------------------------
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
对比测试：
设置
代码语言：javascript复制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) 查看插入缓冲状态
代码语言：javascript复制-------------------------------------
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
