67,513
社区成员
发帖
与我相关
我的任务
分享关于数据库批量插入操作的实现方式比较
这里是关于批量数据入库操作的疑问。
在java代码里我们一般都是用statement的executeBatch方法实现批量数据入库操作。
还有一种SQL方式：insert into test(id,name) values('1','Jerry'),('2','Tom'),.....('n','Neo');这样看来，只要拼接好一条SQL，通过statement的execute方法就能实现批量插入操作。但是一般平台开发都不会用这种方式。
请教下大家有没有研究过，这两种方式在数据中时怎么解析的？
batch操作，是不是就是将一批SQL同时提交给数据库，然后一条条执行？
而values方法，如果有多个()值，数据库又是怎么操作的？
总而言之，这两者的效率差别如何？
ps：自己做了些测试，但是随着批量操作的数据量不同，得到的耗时结果不一样，比较疑惑。
在java代码里我们一般都是用statement的executeBatch方法实现批量数据入库操作。
还有一种SQL方式：insert into test(id,name) values('1','Jerry'),('2','Tom'),.....('n','Neo');这样看来，只要拼接好一条SQL，通过statement的execute方法就能实现批量插入操作。但是一般平台开发都不会用这种方式。
请教下大家有没有研究过，这两种方式在数据中时怎么解析的？
batch操作，是不是就是将一批SQL同时提交给数据库，然后一条条执行？
而values方法，如果有多个()值，数据库又是怎么操作的？
总而言之，这两者的效率差别如何？
ps：自己做了些测试，但是随着批量操作的数据量不同，得到的耗时结果不一样，比较疑惑。
...全文
请发表友善的回复…
发表回复
eeight 2014-10-02
- 打赏
- 举报
mysqldump 生成的备份.sql文件就是拼接values的，想必性能更好些
不喝咖啡的程序猿 2014-09-30
- 打赏
- 举报
mark！感谢大神们的交流
oh_Maxy 2014-09-29
- 打赏
- 举报
最近又使用mysql做同样的验证，发现mysql对多values方式优化很多，随着批量数据的增多，多values方式时间消耗几乎没有太多的变化，而batch方式的时间消耗数量级式的增加。
对比测试得出结论：DB2虽然支持多values的语法，却没有做优化，Mysql（InnoDB引擎）对多values做了优化。
优化的内容，可能是在数据库操作的日志上面做文章了，具体还是不太清楚。
致知Fighting 2014-07-21
- 打赏
- 举报
我想当然了，查了一下资料，关于语法糖的说法不正确。
多values性能测试， 这里有多values的性能测试。其实按照我们之前的讨论，多values在性能上是不错的，开销在于网络、数据库服务器的内存占用。
仔细想了一下，网络方面的开销在内网时其实可以忽略，一般这种导数据的操作都是在内网，内网的带宽一般都足，如果是跨数据中心的请求这方面开销可能要大一些，这个另说。数据库服务器的内存占用这个其实有办法解决，可以通过调大的方式来解决。可以参考上面那篇文章。
关于batch的语法糖，不正确。事实上，仍然可以看做是语法糖，只不过处理不同理解不同，数据库服务器不存在语法糖，批量处理是存在的，如同上述的多values，但是batch却不是用的这种。以前的处理是语法糖，用事务包裹所有的扯臊做，add一次就和数据库交互一次，5.1.17以后的驱动，是将所有的操作一次型提交到数据库，这种处理方式应该就和values比较相近了。参考：mysql批量提交的优化
batch的包我没抓过，以上的处理是我的推测，希望有抓包的继续来探讨。
oh_Maxy 2014-07-21
- 打赏
- 举报
渐渐地更清晰了。
第二点，本来打算通过freemarker来循环，构造超长SQL的，这个过程应该会比较慢。
oh_Maxy 2014-07-21
- 打赏
- 举报
这个有2个疑问：addBatch操作就会和数据库交互了么？缓冲区大小，指的是数据库的么，应该怎么配置？
放纵的青春 2014-07-21
- 打赏
- 举报
不懂 帮顶
liyingju001 2014-07-21
- 打赏
- 举报
momoaiyanzi 2014-07-21
- 打赏
- 举报
之前做过一个项目，jdbc+mysql 4万条数据，批量比单条执行快50%左右。
addBatch提交给数据库的缓冲区，excuteBatch的时候一次性执行。这个缓存区大小是可以配置的，配置大一点会快一点，类似空间换时间的概念。
oh_Maxy 2014-07-21
- 打赏
- 举报
感谢分享！
网络和内存消耗，同数据量情况下，个人认为两种方式都差不多。
感觉12L讲的解析消耗可能算一个原因吧。
eeight 2014-07-21
- 打赏
- 举报
谢谢分享。。。
走在四季 2014-07-21
- 打赏
- 举报
谢谢分享。。。
1028405943 2014-07-21
- 打赏
- 举报
我个人觉得小批量数据还是 insert速率最高 多条数据就多条insert ； 如果很大批量的话 可以利用框架hibernate 或者mybatis吧
addBatch拼接 适用于大批量数据的，执行效率肯定会比较低，但是相反可维护性和可移植性高。 一般我还是建议尽量用addBatch，为了后续的维护 不然你会哭 、、、、菜鸟，个人见解 别喷我、、、
执笔记忆的空白 2014-07-21
- 打赏
- 举报
恩，学些了~ http://blog.csdn.net/moneyshi/article/details/22807239 这是我的博客，里面有我对批量插入的三种方法的见解...
繁华终归落尽 2014-07-21
- 打赏
- 举报
原来还可以批量插入的，我以前都不知道
马少华 2014-07-20
- 打赏
- 举报
个人理解，第二种方式执行前会转换成第一种方式再执行。
对于第一个方法，感觉是批量拷贝内存实现的。省掉了一些单条语句执行时的一些验证及相关事务处理。
abcbuzhiming 2014-07-19
- 打赏
- 举报
第一个方法，驱动器拿到sql连接后，在一次连接执行了多条sql语句而已，省略了获取连接的时间
第二种方法，可能楼主你没注意到这种sql写法是mysql特有的，db2能否支持不知道，但是mssql肯定不支持，也就是说基于sql方言的批量插入是要考虑将来移植和兼容性问题的，但是论速度来说，绝逼第二种快，这种方式是一次向数据库批量提交数据，而且是数据库级别的优化
卖水果的net 2014-07-18
- 打赏
- 举报
在具体的写代码工作中使用哪个方法，性能并不是唯一的追求。
你这里提到了 2 种方法是常用的，当然了还有第 3、4、5 种。
使用哪一种，我觉得至少考虑以下几个方面：
1.对写代码的人来说，哪个比较方便；
2.对于后期维护，哪个成本更低；
3.效率和性能；
4.项目组或公司对代码的要求；有的公司有要求统一的编码规范，有时候用一句就完成的功能，按规范就得10句以上才可以，但是为了追求统一，也必须写成 10 句。
个人见解，欢迎讨论，共同进步！
liyingju001 2014-07-18
- 打赏
- 举报
哦 是这样 知道了 看看再说
致知Fighting 2014-07-18
- 打赏
- 举报
感谢你的解释，“语法糖”的说法挺有意思的！感觉可以先这么理解。
数据库系统比较深，论坛里的数据库版块比较冷，希望有大牛路过给大家更深入的解释。
这边测试用的db2，数据量少于1000时，多个values方式比较快，超过5000条时batch块很多。（以上数字只是个大概，可能还跟表的字段数有关）随着数据量增加，batch效率明显高于多values方式。
另外，似乎不是所有的数据库都支持多values方式。[/quote]
其实我是比较赞同batch的，毕竟多values拼出来的字符串在网络传输和内存消耗方面的劣势太明显。
另外觉得你5000的这种量级太小了，10w级别差别可能会更加明显。
加载更多回复（9）
