首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql批量插入分析【面试+工作】

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

作者头像
Java帮帮
发布2018-11-22 17:33:23
1.8K0
发布2018-11-22 17:33:23
举报

前言

最近发现几个项目中都有批次插入数据库的功能,每个项目中批次插入的写法有一些差别,所以本文打算对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对数据包的大小限制;最后建议要在内存和速度方面做一个权衡。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2018-10-24,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 Java帮帮 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档