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对数据包的大小限制；最后建议要在内存和速度方面做一个权衡。