Mysql 批量插入

2022-04-26 约 2324 字预计阅读 5 分钟

假如需要插入的数据有百万条，那么一次批量插入多少条的时候，效率会高一些呢？

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


SELECT version();
-- 8.0.28

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `age` int NOT NULL,
  `name` varchar(10),
  `balance` decimal(12,2) DEFAULT '0.00',
  `create_time` datetime NOT NULL,
  `deleted` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

-- 查看字符字段大小 bit
SELECT LENGTH('我')
SELECT LENGTH('i');
SELECT LENGTH('😁');

一行占用空间 int 4 bytes(字节) * 2 + DATETIME 8字节 + 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 这里14字节 + TINYINT 1个字节 + varchar 变长按最长算是 10 * 4 字节 = 71字节

utf8mb4下，英文占用1个字节，一般汉字占3个字节，emoji表情占4个字节。应当与varchar的字符数概念区分开，使用 select length(字段),char_length(字段) from <table_name>。

单条sql插入多条数据

不是所有数据库引擎都支持这种语法。

1
2
3
4


INSERT INTO `user`(`id`, `age`, `name`, `balance`, `create_time`, `deleted`) VALUES 
(1, 18, '我我我我我我我我我我', 100.00, '2022-04-26 22:28:27', 0),
(2, 17, 'IIIIIIIIII', 1000.00, '2022-04-26 22:29:03', 0),
(3, 11, '😁😁😁😁😁😁😁😁😁😁', 1000000000.00, '2022-04-26 22:29:41', 0);

SQL语句的大小限制 my.ini 里有 max_allowed_packet 这个参数控制通信的 packet 大小。mysql默认的sql语句的最大限制是1M（mysql5.7的客户端默认是16M，服务端默认是4M），可以根据设置查看。官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。

1
2
3


show variables like '%max_allowed_packet%';
select sys.format_bytes(67108864);
-- 64MiB

可以看到默认为 64M = 64 X 1024 X 1024 / 71 ≈ 945195 也就是 90W 多条(考虑)。

插入 10 万测试数据每次插入 10 100 500 1000 5000 30000 100000 插入 100 万测试数据每次插入 10 100 500 1000 5000 30000 100000 插入 1000 万测试数据每次插入 10 100 500 1000 5000 30000 100000

个人感觉，最佳大小是 max_allowed_packet 的一半

缓冲区的大小

如果buffer pool余量不足25%，插入失败，返回DB_LOCK_TABLE_FULL。这个错误并不是直接报错：max_allowed_packet 不够大之类的，这个错误是因为对于innodb引擎来说，一次插入是涉及到事务和锁的，在插入索引的时候，要判断缓冲区的剩余情况，所以插入并不能仅仅只考虑max_allowed_packet的问题，也要考虑到缓冲区的大小。

另外对于innodb引擎来说，因为存在插入缓存（Insert Buffer）这个概念，所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占用到1/2的缓冲池内存，当插入缓冲占用太多缓冲池内存的情况下，会影响到其他的操作。

也就是说，插入缓冲受到缓冲池大小的影响，缓冲池大小为：

1

show variables like 'innodb_buffer_pool_size';

在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。

InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer中。

看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

使用事务提升效率

使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。

1
2
3
4
5


START TRANSACTION;
INSERT INTO `test`.`user`(`id`, `age`, `name`, `balance`, `create_time`, `deleted`) VALUES (1, 18, '我我我我我我我我我我', 100.00, '2022-04-26 22:28:27', 0);
INSERT INTO `test`.`user`(`id`, `age`, `name`, `balance`, `create_time`, `deleted`) VALUES (2, 17, 'IIIIIIIIII', 1000.00, '2022-04-26 22:29:03', 0);
INSERT INTO `test`.`user`(`id`, `age`, `name`, `balance`, `create_time`, `deleted`) VALUES (3, 11, '😁😁😁😁😁😁😁😁😁😁', 1000000000.00, '2022-04-26 22:29:41', 0);
COMMIT;

事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。

1

show variables like '%innodb_log_buffer_size%';

这种写法和批量写入的效果差不多，只不过sql语句还是单句的，然后统一提交。一个瓶颈是SQL语句的大小，一个瓶颈是事务的大小。当我们在提交sql的时候，首先是受到sql大小的限制，其次是受到事务大小的限制。在开启事务的情况下使用批量插入，会节省不少事务的开销，如果要追求极致的速度的话，建议是开着事务插入的。不过需要注意一下，内存是有限且共享的，如果批量插入占用太多的事务内存，那么势必会对其他的业务操作等有一定的影响。

索引影响插入性能

如果表中存在多个字段索引，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护。这样就降低了数据的插入速度。对于普通的数据表，主键索引是肯定要有的，想要加快性能的话，就是要有序插入，每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小。如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

如果是是离线同步数据，可以尝试先删除索引，插入完数据再建立索引。

MyBatis 批量插入

反复执行单条插入语句
xml foreach 拼接 sql
批处理

rewriteBatchedStatements=true 配置jdbc url时

总结

一次批量插入数据量为max_allowed_packet大小的一半，这不是最佳性能，但是是一个可行的操作方式。

目录