关于数据库批量插入操作的实现方式比较

码拜

10年 ago

这里是关于批量数据入库操作的疑问。在java代码里我们一般都是用statement的executeBatch方法实现批量数据入库操作。还有一种SQL方式：insert into test(id,name) values(“”1″”,””Jerry””),(“”2″”,””Tom””),…..(“”n””,””Neo””);这样看来，只要拼接好一条SQL，通过statement的execute方法就能实现批量插入操作。但是一般平台开发都不会用这种方式。请教下大家有没有研究过，这两种方式在数据中时怎么解析的？ batch操作，是不是就是将一批SQL同时提交给数据库，然后一条条执行？而values方法，如果有多个()值，数据库又是怎么操作的？总而言之，这两者的效率差别如何？ ps：自己做了些测试，但是随着批量操作的数据量不同，得到的耗时结果不一样，比较疑惑。
30分	这一块我也没有研究过，说一下理解吧。一般的数据库服务器的结构分为sql解析器和sql执行器。请求到了服务器以后，首先要对sql做语法解析，检查有无错误，权限是否对等等等操作，然后再把编译好的指令发到执行器，由执行器来进行内存/硬盘io 对数据进行操作。 executeBatch省下的是数据库链接操作的时间，请求是批量提交到服务器的，经过解析器的解析之后，再逐条发送至执行器。 insert多个value，也不用取得很多次数据库链接（不论是创建还是池化），流程和以上一样，多个value我觉得应该是语法糖，执行器应该还是会逐条的处理。以上分析建立在，执行器不能一次执行多条指令的基础上。这一块我确实没有深究过，如果有错误，希望大牛来指正。 LZ实测不同的效率来源，我觉得是，insert的静态语句会占用大量内存，这在数据膨胀的情况下应该还是会造成很大影响，如果是prepared的batch就不会有这个问题。综上，还是batch比较好。
10分	在JDBC这，肯定是values多组值快了，都不需要控制事务，解析成sql，直接发给数据库就好了。在数据库这里，语句的时间复杂度应该是一样的，那就是比jdbc拆sql快还是数据库拆sql快，从这点看，我猜values多组值快。但不同数据库的特性是不一样的，比如oracle有共享sql，我不知道jdbc的实现能不能用的上，如果能batch会很快。
	引用 1 楼 ygycomon 的回复: 这一块我也没有研究过，说一下理解吧。一般的数据库服务器的结构分为sql解析器和sql执行器。请求到了服务器以后，首先要对sql做语法解析，检查有无错误，权限是否对等等等操作，然后再把编译好的指令发到执行器，由执行器来进行内存/硬盘io 对数据进行操作。 executeBatch省下的是数据库链接操作的时间，请求是批量提交到服务器的，经过解析器的解析之后，再逐条发送至执行器。 insert多个value，也不用取得很多次数据库链接（不论是创建还是池化），流程和以上一样，多个value我觉得应该是语法糖，执行器应该还是会逐条的处理。以上分析建立在，执行器不能一次执行多条指令的基础上。这一块我确实没有深究过，如果有错误，希望大牛来指正。 LZ实测不同的效率来源，我觉得是，insert的静态语句会占用大量内存，这在数据膨胀的情况下应该还是会造成很大影响，如果是prepared的batch就不会有这个问题。综上，还是batch比较好。感谢你的解释，“语法糖”的说法挺有意思的！感觉可以先这么理解。数据库系统比较深，论坛里的数据库版块比较冷，希望有大牛路过给大家更深入的解释。引用 2 楼 xiaopeipei2004 的回复: 在JDBC这，肯定是values多组值快了，都不需要控制事务，解析成sql，直接发给数据库就好了。在数据库这里，语句的时间复杂度应该是一样的，那就是比jdbc拆sql快还是数据库拆sql快，从这点看，我猜values多组值快。但不同数据库的特性是不一样的，比如oracle有共享sql，我不知道jdbc的实现能不能用的上，如果能batch会很快。这边测试用的db2，数据量少于1000时，多个values方式比较快，超过5000条时batch块很多。（以上数字只是个大概，可能还跟表的字段数有关）随着数据量增加，batch效率明显高于多values方式。另外，似乎不是所有的数据库都支持多values方式。
	学习了，不错啊。
	你们都是大神哦
	引用 3 楼 oh_Maxy 的回复: Quote: 引用 1 楼 ygycomon 的回复: 这一块我也没有研究过，说一下理解吧。一般的数据库服务器的结构分为sql解析器和sql执行器。请求到了服务器以后，首先要对sql做语法解析，检查有无错误，权限是否对等等等操作，然后再把编译好的指令发到执行器，由执行器来进行内存/硬盘io 对数据进行操作。 executeBatch省下的是数据库链接操作的时间，请求是批量提交到服务器的，经过解析器的解析之后，再逐条发送至执行器。 insert多个value，也不用取得很多次数据库链接（不论是创建还是池化），流程和以上一样，多个value我觉得应该是语法糖，执行器应该还是会逐条的处理。以上分析建立在，执行器不能一次执行多条指令的基础上。这一块我确实没有深究过，如果有错误，希望大牛来指正。 LZ实测不同的效率来源，我觉得是，insert的静态语句会占用大量内存，这在数据膨胀的情况下应该还是会造成很大影响，如果是prepared的batch就不会有这个问题。综上，还是batch比较好。感谢你的解释，“语法糖”的说法挺有意思的！感觉可以先这么理解。数据库系统比较深，论坛里的数据库版块比较冷，希望有大牛路过给大家更深入的解释。引用 2 楼 xiaopeipei2004 的回复: 在JDBC这，肯定是values多组值快了，都不需要控制事务，解析成sql，直接发给数据库就好了。在数据库这里，语句的时间复杂度应该是一样的，那就是比jdbc拆sql快还是数据库拆sql快，从这点看，我猜values多组值快。但不同数据库的特性是不一样的，比如oracle有共享sql，我不知道jdbc的实现能不能用的上，如果能batch会很快。这边测试用的db2，数据量少于1000时，多个values方式比较快，超过5000条时batch块很多。（以上数字只是个大概，可能还跟表的字段数有关）随着数据量增加，batch效率明显高于多values方式。另外，似乎不是所有的数据库都支持多values方式。其实我是比较赞同batch的，毕竟多values拼出来的字符串在网络传输和内存消耗方面的劣势太明显。另外觉得你5000的这种量级太小了，10w级别差别可能会更加明显。
	哦是这样知道了看看再说
	在具体的写代码工作中使用哪个方法，性能并不是唯一的追求。你这里提到了 2 种方法是常用的，当然了还有第 3、4、5 种。使用哪一种，我觉得至少考虑以下几个方面： 1.对写代码的人来说，哪个比较方便； 2.对于后期维护，哪个成本更低； 3.效率和性能； 4.项目组或公司对代码的要求；有的公司有要求统一的编码规范，有时候用一句就完成的功能，按规范就得10句以上才可以，但是为了追求统一，也必须写成 10 句。个人见解，欢迎讨论，共同进步！
	引用 8 楼 wmxcn2000 的回复: 在具体的写代码工作中使用哪个方法，性能并不是唯一的追求。你这里提到了 2 种方法是常用的，当然了还有第 3、4、5 种。使用哪一种，我觉得至少考虑以下几个方面： 1.对写代码的人来说，哪个比较方便； 2.对于后期维护，哪个成本更低； 3.效率和性能； 4.项目组或公司对代码的要求；有的公司有要求统一的编码规范，有时候用一句就完成的功能，按规范就得10句以上才可以，但是为了追求统一，也必须写成 10 句。个人见解，欢迎讨论，共同进步！你说的对！
	我只记得直接采用拼sql的方式是在数据库里是每次都要去解析的，效率不高没有仔细去研究过你说的哪两种批量提交，个人觉得用executeBatch()在处理频繁提交的时候有优势些。至于在数据库里怎么解析的请专业dba回答吧
30分	这个基本依赖jdbc的驱动的没用过db2，我的猜测是，类似mysql jdbc的方式的话，addBatch因为提交了N次insert语句，所以在行数少的时候，会慢。而multiple rows的方式： 1 客户端（如果用到?或者其他形式的参数的话）：由于jdbc驱动要维护相关参数的位置及对应关系等，太多参数会影响效率 2 服务端，解析那么长一句SQL，也是要花时间的（尤其是你解析部分的缓冲没开大的话）。无论这个驱动的方式是在客户端拼接好参数（比如mysql）还是在服务端再绑定参数值。另外，如果从可维护的角度来说，建议还是用addBatch。
30分	第一个方法，驱动器拿到sql连接后，在一次连接执行了多条sql语句而已，省略了获取连接的时间第二种方法，可能楼主你没注意到这种sql写法是mysql特有的，db2能否支持不知道，但是mssql肯定不支持，也就是说基于sql方言的批量插入是要考虑将来移植和兼容性问题的，但是论速度来说，绝逼第二种快，这种方式是一次向数据库批量提交数据，而且是数据库级别的优化
20分	个人理解，第二种方式执行前会转换成第一种方式再执行。对于第一个方法，感觉是批量拷贝内存实现的。省掉了一些单条语句执行时的一些验证及相关事务处理。
20分	之前做过一个项目，jdbc+mysql 4万条数据，批量比单条执行快50%左右。 addBatch提交给数据库的缓冲区，excuteBatch的时候一次性执行。这个缓存区大小是可以配置的，配置大一点会快一点，类似空间换时间的概念。
	不懂帮顶
	引用 15 楼 momoaiyanzi 的回复: 之前做过一个项目，jdbc+mysql 4万条数据，批量比单条执行快50%左右。 addBatch提交给数据库的缓冲区，excuteBatch的时候一次性执行。这个缓存区大小是可以配置的，配置大一点会快一点，类似空间换时间的概念。这个有2个疑问：addBatch操作就会和数据库交互了么？缓冲区大小，指的是数据库的么，应该怎么配置？
	引用 12 楼 shine333 的回复: 这个基本依赖jdbc的驱动的没用过db2，我的猜测是，类似mysql jdbc的方式的话，addBatch因为提交了N次insert语句，所以在行数少的时候，会慢。而multiple rows的方式： 1 客户端（如果用到?或者其他形式的参数的话）：由于jdbc驱动要维护相关参数的位置及对应关系等，太多参数会影响效率 2 服务端，解析那么长一句SQL，也是要花时间的（尤其是你解析部分的缓冲没开大的话）。无论这个驱动的方式是在客户端拼接好参数（比如mysql）还是在服务端再绑定参数值。另外，如果从可维护的角度来说，建议还是用addBatch。渐渐地更清晰了。第二点，本来打算通过freemarker来循环，构造超长SQL的，这个过程应该会比较慢。
50分	我想当然了，查了一下资料，关于语法糖的说法不正确。多values性能测试，这里有多values的性能测试。其实按照我们之前的讨论，多values在性能上是不错的，开销在于网络、数据库服务器的内存占用。仔细想了一下，网络方面的开销在内网时其实可以忽略，一般这种导数据的操作都是在内网，内网的带宽一般都足，如果是跨数据中心的请求这方面开销可能要大一些，这个另说。数据库服务器的内存占用这个其实有办法解决，可以通过调大的方式来解决。可以参考上面那篇文章。关于batch的语法糖，不正确。事实上，仍然可以看做是语法糖，只不过处理不同理解不同，数据库服务器不存在语法糖，批量处理是存在的，如同上述的多values，但是batch却不是用的这种。以前的处理是语法糖，用事务包裹所有的扯臊做，add一次就和数据库交互一次，5.1.17以后的驱动，是将所有的操作一次型提交到数据库，这种处理方式应该就和values比较相近了。参考：mysql批量提交的优化 batch的包我没抓过，以上的处理是我的推测，希望有抓包的继续来探讨。
20分	原来还可以批量插入的，我以前都不知道
30分	恩，学些了~ http://blog.csdn.net/moneyshi/article/details/22807239 这是我的博客，里面有我对批量插入的三种方法的见解…
30分	我个人觉得小批量数据还是 insert速率最高多条数据就多条insert ；如果很大批量的话可以利用框架hibernate 或者mybatis吧 addBatch拼接适用于大批量数据的，执行效率肯定会比较低，但是相反可维护性和可移植性高。一般我还是建议尽量用addBatch，为了后续的维护不然你会哭、、、、菜鸟，个人见解别喷我、、、
	谢谢分享。。。
	谢谢分享。。。
	引用 20 楼 ygycomon 的回复: 我想当然了，查了一下资料，关于语法糖的说法不正确。多values性能测试，这里有多values的性能测试。其实按照我们之前的讨论，多values在性能上是不错的，开销在于网络、数据库服务器的内存占用。仔细想了一下，网络方面的开销在内网时其实可以忽略，一般这种导数据的操作都是在内网，内网的带宽一般都足，如果是跨数据中心的请求这方面开销可能要大一些，这个另说。数据库服务器的内存占用这个其实有办法解决，可以通过调大的方式来解决。可以参考上面那篇文章。关于batch的语法糖，不正确。事实上，仍然可以看做是语法糖，只不过处理不同理解不同，数据库服务器不存在语法糖，批量处理是存在的，如同上述的多values，但是batch却不是用的这种。以前的处理是语法糖，用事务包裹所有的扯臊做，add一次就和数据库交互一次，5.1.17以后的驱动，是将所有的操作一次型提交到数据库，这种处理方式应该就和values比较相近了。参考：mysql批量提交的优化 batch的包我没抓过，以上的处理是我的推测，希望有抓包的继续来探讨。引用 20 楼 ygycomon 的回复: 我想当然了，查了一下资料，关于语法糖的说法不正确。多values性能测试，这里有多values的性能测试。其实按照我们之前的讨论，多values在性能上是不错的，开销在于网络、数据库服务器的内存占用。仔细想了一下，网络方面的开销在内网时其实可以忽略，一般这种导数据的操作都是在内网，内网的带宽一般都足，如果是跨数据中心的请求这方面开销可能要大一些，这个另说。数据库服务器的内存占用这个其实有办法解决，可以通过调大的方式来解决。可以参考上面那篇文章。关于batch的语法糖，不正确。事实上，仍然可以看做是语法糖，只不过处理不同理解不同，数据库服务器不存在语法糖，批量处理是存在的，如同上述的多values，但是batch却不是用的这种。以前的处理是语法糖，用事务包裹所有的扯臊做，add一次就和数据库交互一次，5.1.17以后的驱动，是将所有的操作一次型提交到数据库，这种处理方式应该就和values比较相近了。参考：mysql批量提交的优化 batch的包我没抓过，以上的处理是我的推测，希望有抓包的继续来探讨。感谢分享！网络和内存消耗，同数据量情况下，个人认为两种方式都差不多。感觉12L讲的解析消耗可能算一个原因吧。
	最近又使用mysql做同样的验证，发现mysql对多values方式优化很多，随着批量数据的增多，多values方式时间消耗几乎没有太多的变化，而batch方式的时间消耗数量级式的增加。对比测试得出结论：DB2虽然支持多values的语法，却没有做优化，Mysql（InnoDB引擎）对多values做了优化。优化的内容，可能是在数据库操作的日志上面做文章了，具体还是不太清楚。
	mark！感谢大神们的交流
30分	mysqldump 生成的备份.sql文件就是拼接values的，想必性能更好些