MySQL百万级数据的插入，哪些方面对效率有影响？jdbc相对mybatis又能快多少？

0. start

MySQL百万级数据的插入，哪些方面对效率有影响。
从事务、数据插入方式、连接、mysql配置等方面看一看。

一条数据，允许的最大值是多少？

1. 事务的影响

• 事务提交对效率是有影响的。

// 10W条数据，循环单条插入，自动提交事务（10W次事务提交）与 只提交一次事务的差别
- 自动提交事务：执行test1，耗时 5869423 。
- 提交 10次事务：执行test2（改），耗时  37053 。
- 提交一次事务：执行test2，耗时   32990 。

// 时间相差不少，事务提交对效率是有影响的。

2. 数据插入方式

• 批量插入是能极大提高数据插入效率的。

// 10W条数据，循环单条插入（自动提交事务），批量插入（自动提交事务）的差别
- 单条插入（自动提交事务）：执行test1，耗时 5869423 。 
- 批量插入（自动提交事务）：执行test3，耗时 5715297 。
- 单条插入（提交一次事务）：执行test2，耗时   35555 。
- 批量插入（提交一次事务）：执行test4，耗时   36272 。

// 时间相差不多，批量处理提高不了效率吗？ 当然不是，批量处理语句是真的。
// 只是做了单条插入的事，一条一条地发给MySQL数据库，批处理没起作用。批处理还需要配置连接参数才能生效。

3. 连接参数

连接参数也对插入效率有很大影响，url上加 ‘rewriteBatchedStatements=true’ 批量处理才会生效。

// url上加个允许批量处理的参数：rewriteBatchedStatements=true
 - 批量处理（加参数前，提交一次事务）：执行test4，10W条数据，耗时 36272 。
 - 批量处理（加参数后，提交一次事务）：执行test4，10W条数据，耗时 10466 。

// 很明显加上允许批量处理的参数后，批量处理的效率提高了不少。

代码中已经用了预编译，再个允许预编译的参数‘useServerPrepStmts=true’会不会更快。

// url上加个允许批量处理的参数：useServerPrepStmts=true
 - 批量处理（加预编译参数前，提交一次事务）：执行test4，200W条数据，耗时 104032，94735，94111 。
 - 批量处理（加预编译参数后，提交一次事务）：执行test4，200W条数据，耗时 163642，158585，161939 。

// 从数据上看，加了预编译参数后，效率反而降低了。

4. MySQL配置

MySQL配置调整是肯定能提高效率的。

// 默认4M一些，听说上限1G。（最大允许数据包:pstm.executeUpdate()或pstm.executeBatch()的数据量）
max_allowed_packet=16M
// 默认值0，有0、1、2三个值。（事务提交，日志缓冲的策略）此参数更详细一些的说明，可以看第5点。
innodb_flush_log_at_trx_commit=0
// 默认8M。 （表空间大小，需要MySQL系统需要自动扩展，每次tablespace 扩展都会让各个SQL 处于等待状态。）
innodb_autoextend_increment=128M
// 默认1M。（日志缓存区大小，增大缓冲区，可以减少innodb数据库引擎，写数据文件次数。）
innodb_log_buffer_size=16M
// 默认8M。（日志文件大小，增大值，可以减少数据库checkpoint的次数，提高数据插入效率；但是，MySQL崩溃等灾难情况会让MySQL服务器花更长时间来恢复。）
innodb_log_file_size=128M

执行test4：

200W数据	第一次（毫秒）	第二次（毫秒）	第三次（毫秒）
默认值（10W执行一次，提交一次事务）	104032	94735	94111
调整后（10W执行一次，提交一次事务）	96648	93899	95065

虽然看起来配置调整后的效率更高了，但执行时间也相差无几。
主要是因为数据量虽然有200W，但其实数据大小也就90M的样子，所以消耗的时间表现得不怎么明显。

5. 一些说明

innodb_flush_log_at_trx_commit： 如果不在乎事务丢失，0和2能获得更高的性能。
参数说明：https://www.cnblogs.com/klvchen/p/10861850.html

• 0：Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit（默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。）.
• 1：the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file（事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。）.
• n：the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it（每写N次操作系统缓冲就执行一次刷新操作。）.

jdbc批量插入，一次提交10W条，直接异常了。
根据异常可以看出max_allowed_packet（最大允许数据包）大小是4M，我的每条数据比较小，10W条的大小刚好超过4M一些。听说上限1G，设置值超出了也等同于上限值。

参考：
https://www.cnblogs.com/fnz0/p/5713102.html
https://www.cnblogs.com/yins/p/9908824.html