Mysql批量插入分析【面试+工作】

前言

最近发现几个项目中都有批次插入数据库的功能,每个项目中批次插入的写法有一些差别,所以本文打算对Mysql的批次插入做一个详细的分析。

准备

1.jdk1.7,mysql5.6.38

2.准备库和表

测试与分析

下面准备几种插入的方式来分析优劣:

1.Statement插入方式

准备数据,然后通过Statement方式插入数据,插入10000条数据大概在6秒多左右,同时可以监控服务器数据包;

监控命令:

日志如下:

以上截取了其中一条插入语句的数据包日志,详细的数据包可以通过如下命令监控:

详细日志:

可以发现每个sql语句包前面都有一个select.@@session.tx_read_only包,这是因为mysql jdbc驱动设置useLocalSessionState=false,每一次都需要检测目标数据库isReadOnly的状态,

所以每次都发送select.@@session.tx_read_only包,可以设置useLocalSessionState=true使用连接对象本地的状态,可以修改url如下:

再次运行,观察日志:

日志中省掉了select.@@session.tx_read_only的过程,提升插入的性能,具体代码可以参考ConnectionImpl的isReadOnly方法:

2.PreparedStatement方式

PreparedStatement比起Statement有很多优势,其中一条就是PreparedStatement比Statement更快,SQL语句会预编译在数据库系统中,执行计划同样会被缓存起来,它允许数据库做参数化查询。同样插入10000条数据,时间大概在5秒多左右,比起Statement有一定优势,但是不明显;PreparedStatement使用的是批次提交,速度不应该这么查,同样观察日志:

发现和Statement没有区别,一条语句对应了一个包,没有批次的效果,查看PreparedStatement的executeBatch方法,部分代码如下:

其中大致逻辑就是如果canRewriteAsMultiValueInsertAtSqlLevel()为true,那么执行批次插入(executeBatchedInserts),否则执行串联插入(executeBatchSerially);具体可以通过url上添加参数rewriteBatchedStatements

再次运行,插入10000条数据只需要100ms左右,观察日志:

可以发现数据包不是原来的92个字节了,每个包的大小大幅度提升,具体分多少次提交,每次提交多少数据量,可以查看PreparedStatement的computeBatchSize方法:

此方法计算每次提交批量数据中的多少条数据,其中一个maxAllowedPacket参数,此参数在服务器端配置用来限制客户端每个包的最大字节数;

查询maxAllowedPacket:

设置maxAllowedPacket:

此方式可以很好的执行批量数据的插入,但是如果数据量很大,一下执行所有数据的批次插入,很容易造成客户端内存的溢出,所以也可以使用第三种方式;

3.PreparedStatement分批次方式

部分代码如下:

同样是插入10000条数据,但是这种方式是,分10次批次插入数据,有效的控制了内存的消耗,可以做一个简单的实验;

设置启动参数

然后分别使用第二种方式和第三种方式插入10w条数据,第二种方式直接内存溢出,而第三种方式可以完整的将数据插入;当然分批次插入肯定比一次性插入速度慢,所以可以在内存和速度方面做一个简单的权衡。

总结

本文通过三种方式来插入数据,从而了解Mysql批次插入的过程,了解到useLocalSessionState和rewriteBatchedStatements参数对性能的影响,以及maxAllowedPacket对数据包的大小限制;最后建议要在内存和速度方面做一个权衡。

原文发布于微信公众号 - Java帮帮(javahelp)

原文发表时间:2018-10-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏码生

ReactNative loading toast hint alert alertSheet

1472
来自专栏Seebug漏洞平台

CVE-2015-1641 Word 利用样本分析

00 引 子 本文我们将通过一个恶意文档的分析来理解漏洞 CVE-2015-1641(MS15-033)的具体利用过程,以此还原它在现实攻击中的应用。就目前来...

3438
来自专栏伪君子的梦呓

用 Python 生成彩色动态二维码

4867
来自专栏AI研习社

AI-Blocks:可以让任何人创建机器学习模型的所见即所得交互界面 | Github 项目推荐

AI-Blocks 是一个强大且直观的所见即所得交互界面,可以让任何人都创建机器学习模型。 ? AI-Block 通过可拖动的对象来创建简单的场景,该模型可以直...

38616
来自专栏北京马哥教育

用Linux命令行生成随机密码的十种方法

Linux操作系统的一大优点是对于同样一件事情,你可以使用高达数百种方法来实现它。例如,你可以通过数十种方法来生成随机密码。本文将介绍生成随机密码的十种方法。 ...

3326
来自专栏流柯技术学院

JMeter专题系列(四)参数化

参数化:录制脚本中有登录操作,需要输入用户名和密码,假如系统不允许相同的用户名和密码同时登录,或者想更好的模拟多个用户来登录系统。

952
来自专栏转载gongluck的CSDN博客

文件操作(偏移量)

通过函数lseek可以改变文件当前的读写位置。 函数原型:        #include <sys/types.h>        #include <un...

42710
来自专栏蜉蝣禅修之道

fs学习笔记之输出格式

1953
来自专栏MasiMaro 的技术博文

Windows程序设计学习笔记(一)Windows内存管理初步

学习Windows程序设计也有一些时间了,为了记录自己的学习成果,以便以后查看,我希望自己能够坚持写下一系列的学习心得,对自己学习的内容进行总结,同时与大家交流...

731
来自专栏瓜大三哥

综合后的资源分析——资源与扇出分析

Get_timing_path Report_timing 第一条语句:起点 第二条语句:终点 第三条路径:设置为时序路径的起点和终点 时序分析步骤: N...

2215

扫码关注云+社区

领取腾讯云代金券