MySQL批量SQL插入性能优化

本文介绍了几种有效的SQL批量插入优化方法,包括合并多条插入语句、利用事务处理及按主键顺序插入数据等手段,旨在减少日志量、提高执行效率及减少磁盘I/O操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1.一条SQL语句插入多条数据----适用于少量数据

通常:

INSERTINTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERTINTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);

优化:

INSERTINTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0), ('1','userid_1', 'content_1', 1);

提升原因:这里第二种SQL执行效率高的主要原因是合并后日志量(MySQLbinloginnodb的事务让日志)减少了,降低日志刷盘的数据量和频率,从而提高效率。通过合并SQL语句,同时也能减少SQL语句解析的次数,减少网络传输的IO

2.  在事务中进行插入处理

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`,`uid`, `content`, `type`)
   VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`,`uid`, `content`, `type`)
   VALUES ('1', 'userid_1', 'content_1', 1);
...
COMMIT;

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

3.将数据有序插入,主键有序

提升原因:由于数据库插入时,需要维护索引数据,无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+Tree 索引,如果每次插入记录都在索引的最后面,索引的定位效率很高,并且对索引调整较小;如果插入的记录在索引中间,需要B+tree进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作。

综合分析:

合并数据+事务的方法在较小数据量时,性能提高是很明显的,数据量较大时(1千万以上),性能会急剧下降,这是由于此时数据量超过了innodb_buffer的容量,每次定位索引涉及较多的磁盘读写操作,性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好,在数据量较大时,有序数据索引定位较为方便,不需要频繁对磁盘进行读写操作,所以可以维持较高的性能。

注意:

  • SQL语句是有长度限制, max_allowed_packet配置可以修改,默认是1M
  • 事务需要控制大小,事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以比较好的做法是,在数据达到这个这个值前进行事务提交。

 

 

对大量数据的更新100

在jdbc中 开启allowmultiqueries true选项:允许把多条用;分割的语句当成一条statement来执行,默认命令行是true,但是mysql连接的时候默认是false

 

3)使用procedure

 

不能一下把一百万条一起提交,一起提交上万条很容易出错,而且出错损失会比较大,(因该是语句执行时间过长导致的吧)

补充: 

事务、存储过程、function区别

事务:是多条SQL可以同时执行、回滚

存储过程:是一组完成特定功能的sql语句集,经编译后存储在数据库中,用户通过制定名字和参数完成功能,存储过程可以接收和输出参数、返回执行存储过程的状态值

函数:功能同存储过程,只是函数需要返回确定的一个值

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值