一、前言

      我们在操作大型数据表或者日志文件的时候经常会需要写入数据到数据库，那么最合适的方案就是数据库的批量插入。只是我们在执行批量操作的时候，一次插入多少数据才合适呢？假如需要插入的数据有百万条，那么一次批量插入多少条的时候，效率会高一些呢？这里博主和大家一起探讨下这个问题，应用环境为批量插入数据到临时表。

二、批量插入前准备

      博主本地原本是循环查出来的数据，然后每1000条插入一次，直至完成插入操作。但是为什么要设置1000条呢，实不相瞒，这是因为项目里的其他批量插入都是一次插1000条。。汗，博主不服，所以想要测试下。

      首先是查看当前数据库的版本，毕竟各个版本之间存在差异，脱离版本讲数据库就是耍流氓（以前没少耍啊）：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.34-log |
+------------+
1 row in set (0.00 sec)

1、插入到数据表的字段

      对于手动创建的临时表来说，字段当然是越少越好，而且字段占用的空间要尽量小一些，这样临时表不至于太大，影响表操作的性能。这里需要插入的字段是：

字段1 int(10)
字段2 int(10)
字段3 int(10)
字段4 varchar(10)

      我们一共插入四个字段，分别是3个int类型的，一个varchar类型的，整体来说这些字段都比较小，占用的内存空间会小一些。

2、计算一行字段占用的空间

      对于innodb引擎来说，int类型可以存储4个字节，里面的Int(M)并不会影响存储字节的大小，这个M只是数据的展示位数，和mysql的ZEROFILL属性有关，即在数字长度不够的数据前面填充0，以达到设定的长度。此处不多说，想要了解的朋友可以百度一下，还是很有意思的。
      varchar（10）代表可以存储10个字符，不管是英文还是中文，最多都是10个，这部分假设存储的是中文，在utf-8mb4下，10个中文占用10*4 = 40个字节那么一行数据最多占用：4+4+4+40 = 52字节

3、在数据里做插入操作的时候，整体时间的分配

链接耗时 （30%）
发送query到服务器 （20%）
解析query （20%）
插入操作 （10% * 词条数目）
插入index （10% * Index的数目）
关闭链接 （10%）

      从这里可以看出来，真正耗时的不是操作，而是链接，解析的过程。单条sql的话，会在链接，解析部分耗费大量的时间，因此速度会很慢，所以我们一般都是采用批量插入的操作，争取在一次链接里面写入尽可能多的数据，以此来提升插入的速度。但是这个尽可能多的数据是多少呢？一次到底插入多少才合适呢？

三、批量插入数据测试

      开始测试，但是一开始插入多少是合适的呢，是否有上限？查询mysql手册，我们知道sql语句是有大小限制的。

1、SQL语句的大小限制

      my.ini 里有 max_allowed_packet 这个参数控制通信的 packet 大小。mysql默认的sql语句的最大限制是1M（mysql5.7的客户端默认是16M，服务端默认是4M），可以根据设置查看。官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。

官方手册：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

2、查看服务器上的参数：

mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 33554432   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

      33554432字节 = 32M ，也就是规定大小不能超过32M。

3、计算一次能插入的最大行记录

      1M计算的话，(1024*1024)/52 ≈ 20165 ，为了防止溢出，最大可一次性插入20000条（根据自己的配置和sql语句大小计算）。那么32M的话就是：20000 *32 = 640000 也就是64W条。

4、测试插入数据比对

（1）插入11W条数据，按照每次10,600,1000，20000,80000来测试：

+---------------+
| count(c1.uin) |
+---------------+
|         110000 |
+---------------+

有个博客说一次插入10条最快，，我觉得一次插的有点少，咱们试试

参考：https://www.cnblogs.com/aicro/p/3851434.html

      这个博主测试后，认为一次插10条是性能最快的，他的每条记录是3kb，相当于我的59行数据，取个整数60，那么对于这个博主是插入10条，对我来说插入：600，这几个值都试试。

耗时：

11W的数据，每次插入10条。耗时：2.361s
11W的数据，每次插入600条。耗时：0.523s
11W的数据，每次插入1000条。耗时：0.429s
11W的数据，每次插入20000条。耗时：0.426s
11W的数据，每次插入80000条。耗时：0.352s

      从这部分看，随着批量插入的增加，速度略有提升，最起码一次插10条应该不是最佳的。插入数据量多，减少了循环的次数，也就是在数据库链接部分的耗时有所减少，只是这个8W并不是极限数据，具体一次插入多少条，还有待参考。

（2）加大数据量到24w

+---------------+
| count(c1.uin) |
+---------------+
|        241397 |
+---------------+

耗时：

24W的数据，每次插入10条。耗时：4.445s
24W的数据，每次插入600条。耗时：1.187s
24W的数据，每次插入1000条。耗时：1.13s
24W的数据，每次插入20000条。耗时：0.933s
24W的数据，每次插入80000条。耗时：0.753s

      一次插入24W反而性能最佳，这么代表我们的测试数据量依然不够。

（3）加大测试量到42W

+---------------+
| count(c1.uin) |
+---------------+
|        418859 |

耗时：

42W的数据，每次插入1000条。耗时：2.216s
42W的数据，每次插入80000条。耗时：1.777s
42W的数据，每次插入16W条。耗时：1.523s
42W的数据，每次插入20W条。耗时：1.432s
42W的数据，每次插入30W条。耗时：1.362s
42W的数据，每次插入40W条。耗时：1.764s

      随着插入量的增加，批量插入条数多了之后，性能是有所提升的。但是在达到30W以上之后，效率反而有所下降。这部分我的理解是mysql是要分配一定的内存给传过来的数据包使用，当批量插入的数据量到达一定程度之后，一次插入操作的开销就很耗费内存了。个人感觉，最佳大小是max_allowed_packet的一半，也就是极限能插入64W，选用32W也许性能会更好一些，同时也不会对mysql的其他操作产生太大的影响。

5、如果插入的值就是sql语句限制的最大值，那么性能真的好吗？

      博主疯狂谷歌百度，都没有找到有人来具体的说一下这个问题，不过在高性能mysql里面发现一句话：
      客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

      后面通过各种百度，博主觉得最大只是代表传输数据包的最大长度，但性能是不是最佳就要从各个方面来分析了。比如下面列出的插入缓冲，以及插入索引时对于缓冲区的剩余空间需求，以及事务占有的内存等，都会影响批量插入的性能。

四、其他影响插入性能的因素

1、首先是插入的时候，要注意缓冲区的大小使用情况

      在分析源码的过程中，有一句话：如果buffer pool余量不足25%，插入失败，返回DB_LOCK_TABLE_FULL。这个错误并不是直接报错：max_allowed_packet 不够大之类的，这个错误是因为对于innodb引擎来说，一次插入是涉及到事务和锁的，在插入索引的时候，要判断缓冲区的剩余情况，所以插入并不能仅仅只考虑max_allowed_packet的问题，也要考虑到缓冲区的大小。

参考淘宝的数据库日报：http://mysql.taobao.org/monthly/2017/09/10/

2、插入缓存

      另外对于innodb引擎来说，因为存在插入缓存（Insert Buffer）这个概念，所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占用到1/2的缓冲池内存，当插入缓冲占用太多缓冲池内存的情况下，会影响到其他的操作。

      也就是说，插入缓冲受到缓冲池大小的影响，缓冲池大小为：

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

      换算后的结果为：128M，也就是说，插入缓存最多可以占用64M的缓冲区大小。这个大小要超过咱们设置的sql语句大小，所以可以忽略不计。

详细解释：

      我们都知道，在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，
但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。

      InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

参考：https://cloud.tencent.com/developer/article/1200824
参考：mysql技术内幕 Innodb篇

3、使用事务提升效率

      还有一种说法，使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。大概如下：

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('1', 'userid_1', 'content_1', 1);
...
COMMIT;

参考：https://my.oschina.net/songhongxu/blog/163063

      事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。

查看： show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 67108864 |
+------------------------+----------+

大概是：64M

      这种写法和批量写入的效果差不多，只不过sql语句还是单句的，然后统一提交。一个瓶颈是SQL语句的大小，一个瓶颈是事务的大小。当我们在提交sql的时候，首先是受到sql大小的限制，其次是受到事务大小的限制。在开启事务的情况下使用批量插入，会节省不少事务的开销，如果要追求极致的速度的话，建议是开着事务插入的。不过需要注意一下，内存是有限且共享的，如果批量插入占用太多的事务内存，那么势必会对其他的业务操作等有一定的影响。

4、通过配置提升读写性能

      也可以通过增大innodb_buffer_pool_size 缓冲区来提升读写性能，只是缓冲区是要占用内存空间的，内存很珍贵，所以这个方案在内存富裕，而性能瓶颈的时候，可以考虑下。

参考：https://my.oschina.net/anuodog/blog/3002941

5、索引影响插入性能

      如果表中存在多个字段索引，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护。这样就降低了数据的插入速度。对于普通的数据表，主键索引是肯定要有的，想要加快性能的话，就是要有序插入，每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小。如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

五、总结

      博主经过测试+谷歌，最终是选用的一次批量插入数据量为max_allowed_packet大小的一半。只是在不断的搜索中，发现影响插入性能的地方挺多的，如果仅仅是拿max_allowed_packet这个参数作为分析，其实是没有意义的，这个参数只是设置最大值，但并不是最佳性能。不过需要注意，由于sql语句比较大，所以才执行完插入操作之后，一定要释放变量，不要造成无谓的内存损耗，影响程序性能。

      对于我们的mysql来说也是一样的，mysql的最佳性能是建立在各个参数的合理设置上，这样协同干活儿的效果最佳。如果其他设置不到位的话，就像是木桶原理一样，哪怕内存缓冲区设置的很大，但是性能取决的反而是设置最差的那个配置。关于mysql的配置调优，我们都在路上，加油！

end

在此还记录了一些mysql基础知识，可以用作参考

优化方案

插入语句

普通使用的插入语句：
insert into table (col1，col2，col3) values (’ 1’，’ John ‘，’ stu ‘)；
insert into table (col1，col2，col3) values (’ 2’，’ Jan ‘，’ stu ‘)；
insert into table (col1，col2，col3) values (’ 3 ‘，’ Billy ‘，’ stu ')；

插入操作时间分配：

链接耗时 （30%）
发送query到服务器 （20%）
解析query （20%）
插入操作 （10% * 词条数目）
插入index （10% * Index的数目）
关闭链接 （10%）

从这里可以看出单条语句，会在链接、解析部分耗费大量时间，因此我们大多时候会采用批量插入来提升插入速度，争取在一次链接中尽可能多的写入数据（下面通过几个参数来说明一次到底插入多少数据量合适）。

使用语句拼接

insert into table (col1，col2，col3) values (’ 1’，’ John ‘，’ stu ‘)，(’ 2’，’ Jan ‘，’ stu ‘)，(’ 3 ‘，’ Billy ‘，’ stu ')；
使用拼接sql语句的优势：
降低了日志（MYSQL的binlog和innodb的事务日志）刷盘的数据量和频率、较少了SQL语句的解析次数、减少了网络传输IO（远程客户端插入数据）等；

对于拼接语句sql有一个长度限制（官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。）：
查看限制最大值：show variables like ‘%max_allowed_packet%’;

mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 33554432   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

设置限制最大值：mysql一般可以在配置文件中通过设置变量max_allowed_packet 的值来更改sql长度限制最大值；
在使用python的pymysql包提供的executemany函数，默认使用sql拼接插入数据；

设置完限制值后，那么我们在使用拼接时，一次性拼接多少条语句合适呢？首先我们需要计算出一条语句大概占用多少内存，然后通过我们设置的max_allowed_packet的50%-75%去除单条语句的大小，最后计算出每次拼接的数量；（这里的被除数设置为max_allowed_packet的50%-75%，是本人在自己测试时，得到效率最大的区间，每台机器可能不一致，个人猜想与内存等硬件相关）；总之我们应该遵循一句话：减小通信间数据包的大小和数量是一个非常好的习惯。

使用事务

在一条insert语句中默认会开启一个事务，当执行多个insert语句时，事务的开销就会影响到插入数据的性能；因此最常用的优化手段就是在执行一块插入语句前手动开启事务（START TRANSACTION），执行完插入语句后使用COMMIT提交；
事务也需要控制大小，事务太大可能会影响执行的效率；MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。

innodb_log_buffer_size大小查询：

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 67108864 |
+------------------------+----------+

总结：从以上来看，在我们提交插入数据时主要受到两个限制，一个是sql语句本身的大小，其次是事务大小限制；在最大限制下，我们可以开启事务使用sql拼接批量插入，可以节省事务开销，但是需要注意，内存是有限且共享的，如果批量插入占用太多的事务内存，那么势必会对其他的业务操作等有一定的影响。

注：事务和sql拼接从宏观上是差不多的，都是使用了类似缓存的原理，将整块数据一起处理，微观上处理的方式还是有本质区别的；

个人猜测：通过开启事务，会将插入的数据临时放到缓冲池中，等到COMMIT提交时，再落盘到磁盘中；当使用拼接sql
时，其实也是将大量数据放到缓冲池中，单个拼接后sql执行过程内也会建立事务，当完成后落盘；因此使用拼接和使用事务，或者同时使用其实都是差不多，只是使用时的参数设置不同；比如单独使用事务时innodb_log_buffer_size设置大点，单数使用拼接max_allowed_packet设置大点，两者同时使用时两个配置值都适量设置；当然目前只是猜测，等日后研究后再来完善，或者大佬们提点下。

数据插入影响

索引的影响

数据库插入时，需要维护索引的数据，当插入的记录无序时会增大维护索引（聚簇）的成本；我们可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作（无序，且缓冲池不够用）。

因此有序插入在一定程度上可以优化插入的效率。

插入缓存的影响（存在非聚簇索引时）

对于innodb引擎来说，一次插入是涉及到事务和锁的，所以插入并不能仅仅只考虑max_allowed_packet的问题，也要考虑服务器缓冲池的大小；
在innodb中存在一个插入缓存（insert buffer）的概念，所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占用到1/2的缓冲池内存，当插入缓冲占用太多缓冲池内存的情况下，会影响到其他的操作。

缓冲池的大小查询：

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

插入缓冲的详细解释：
我们都知道，在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。
 InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。
 
具体可以参考：https://cloud.tencent.com/developer/article/1200824
缓存池可以大致类比cpu将物理内存当作磁盘的缓存，innodb将缓存池用作磁盘的缓存

日志的影响

这里主要是介绍二进制日志BinLog：
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复，比如MySQL主从复制、数据恢复等；

启动或关闭binlog：通过配置文件中的log-bin配置项来启动及关闭；
查看是否启动了binlog：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

根据项目需求，可以选择关闭binlog来优化插入数据的效率。

服务器配置参数

mysql配置文件：my.cnf（linux）、my.ini（win）；

• innodb_buffer_pool_size（缓冲池）
  这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲；
• innodb_log_file_size
  指定日志文件大小；这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系；
  当一个日志文件写满后，innodb会自动切换到另一个日志文件，而且会触发数据库检查点，这会导致缓存脏页小批量刷新，会明显降低innodb性能；
• innodb_log_buffer_size
  事务在内存中的缓冲大小，我也看到说这是将日志写入磁盘日志文件前的缓冲大小；不过确实会影响到事务；
  一个事务能否成功提交的关键是日志是否成功落盘，与数据没有太大的关系
• max_allowed_packet
  最大的sql语句大小限制；
  想要了解具体的参数可以参考：https://blog.csdn.net/wjc19911118/article/details/51784783

mysql常用的引擎：InnoDB(上面的介绍都是以该引擎为基础)、Myisam

利用JAVA向Mysql插入一亿数量级数据—效率测评

这几天研究mysql优化中查询效率时，发现测试的数据太少（10万级别），利用 EXPLAIN 比较不同的 SQL 语句，不能够得到比较有效的测评数据，大多模棱两可，不敢通过这些数据下定论。

所以通过随机生成人的姓名、年龄、性别、电话、email、地址 ，向mysql数据库大量插入数据，便于用大量的数据测试 SQL 语句优化效率。、在生成过程中发现使用不同的方法，效率天差万别。

1、先上Mysql数据库，随机生成的人员数据图。分别是ID、姓名、性别、年龄、Email、电话、住址。

下图一共三千三百万数据：

在数据量在亿级别时，别点下面按钮，会导致Navicat持续加载这亿级别的数据，导致电脑死机。~觉着自己电脑配置不错的可以去试试，可能会有惊喜

2、本次测评一共通过三种策略，五种情况，进行大批量数据插入测试

策略分别是：

• Mybatis 轻量级框架插入（无事务）

• 采用JDBC直接处理（开启事务、无事务）

• 采用JDBC批处理（开启事务、无事务）

测试结果：

Mybatis轻量级插入 -> JDBC直接处理 -> JDBC 批处理。

JDBC 批处理，效率最高

第一种策略测试：

2.1 Mybatis 轻量级框架插入（无事务）

Mybatis是一个轻量级框架，它比hibernate轻便、效率高。

但是处理大批量的数据插入操作时，需要过程中实现一个ORM的转换，本次测试存在实例，以及未开启事务，导致mybatis效率很一般。

这里实验内容是：

• 利用Spring框架生成mapper实例、创建人物实例对象

• 循环更改该实例对象属性、并插入。

//代码内无事务

 private long begin = 33112001;//起始id
    private long end = begin+100000;//每次循环插入的数据量
    private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
    private String user = "root";
    private String password = "0203";
    
    
@org.junit.Test
    public void insertBigData2()
    {
        //加载Spring，以及得到PersonMapper实例对象。这里创建的时间并不对最后结果产生很大的影响
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        PersonMapper pMapper = (PersonMapper) context.getBean("personMapper");
        //创建一个人实例
        Person person = new Person();
        //计开始时间
        long bTime = System.currentTimeMillis();
        //开始循环，循环次数500W次。
        for(int i=0;i<5000000;i++)
        {
            //为person赋值
            person.setId(i);
            person.setName(RandomValue.getChineseName());
            person.setSex(RandomValue.name_sex);
            person.setAge(RandomValue.getNum(1, 100));
            person.setEmail(RandomValue.getEmail(4,15));
            person.setTel(RandomValue.getTel());
            person.setAddress(RandomValue.getRoad());
            //执行插入语句
            pMapper.insert(person);
            begin++;
        }
        //计结束时间
        long eTime = System.currentTimeMillis();
        System.out.println("插入500W条数据耗时："+(eTime-bTime));
    }

本想测试插入五百万条数据，但是实际运行过程中太慢，中途不得不终止程序。最后得到52W数据，大约耗时两首歌的时间（7~9分钟）。随后，利用mybatis向mysql插入10000数据。

结果如下：

利用mybatis插入 一万 条数据耗时：28613，即28.6秒

第二种策略测试：

2.2 采用JDBC直接处理（开启事务、关闭事务）

采用JDBC直接处理的策略，这里的实验内容分为开启事务、未开启事务是两种，过程均如下：

• 利用PreparedStatment预编译

• 循环，插入对应数据，并存入

事务对于插入数据有多大的影响呢？ 看下面的实验结果:

//该代码为开启事务
 private long begin = 33112001;//起始id
    private long end = begin+100000;//每次循环插入的数据量
    private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
    private String user = "root";
    private String password = "0203";
 
 
@org.junit.Test
    public void insertBigData3() {
        //定义连接、statement对象
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            //加载jdbc驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接mysql
            conn = DriverManager.getConnection(url, user, password);
             //将自动提交关闭
             conn.setAutoCommit(false);
            //编写sql
            String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
            //预编译sql
            pstm = conn.prepareStatement(sql);
            //开始总计时
            long bTime1 = System.currentTimeMillis();
            
            //循环10次，每次一万数据，一共10万
            for(int i=0;i<10;i++) {
                //开启分段计时，计1W数据耗时
                long bTime = System.currentTimeMillis();
                //开始循环
                while (begin < end) {
                    //赋值
                    pstm.setLong(1, begin);
                    pstm.setString(2, RandomValue.getChineseName());
                    pstm.setString(3, RandomValue.name_sex);
                    pstm.setInt(4, RandomValue.getNum(1, 100));
                    pstm.setString(5, RandomValue.getEmail(4, 15));
                    pstm.setString(6, RandomValue.getTel());
                    pstm.setString(7, RandomValue.getRoad());
                    //执行sql
                    pstm.execute();
                    begin++;
                }
                //提交事务
                conn.commit();
                //边界值自增10W
                end += 10000;
                //关闭分段计时
                long eTime = System.currentTimeMillis();
                //输出
                System.out.println("成功插入1W条数据耗时："+(eTime-bTime));
            }
            //关闭总计时
            long eTime1 = System.currentTimeMillis();
            //输出
            System.out.println("插入10W数据共耗时："+(eTime1-bTime1));
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
    }

1、我们首先利用上述代码测试无事务状态下，插入10W条数据需要耗时多少。

如图：

成功插入1W条数据耗时：21603
成功插入1W条数据耗时：20537
成功插入1W条数据耗时：20470
成功插入1W条数据耗时：21160
成功插入1W条数据耗时：23270
成功插入1W条数据耗时：21230
成功插入1W条数据耗时：20372
成功插入1W条数据耗时：22608
成功插入1W条数据耗时：20361
成功插入1W条数据耗时：20494
插入10W数据共耗时：212106

实验结论如下：

在未开启事务的情况下，平均每 21.2 秒插入 一万 数据。

接着我们测试开启事务后，插入十万条数据耗时，如图：

成功插入1W条数据耗时：4938
成功插入1W条数据耗时：3518
成功插入1W条数据耗时：3713
成功插入1W条数据耗时：3883
成功插入1W条数据耗时：3872
成功插入1W条数据耗时：3873
成功插入1W条数据耗时：3863
成功插入1W条数据耗时：3819
成功插入1W条数据耗时：3933
成功插入1W条数据耗时：3811
插入10W数据共耗时：39255

实验结论如下：

开启事务后，平均每 3.9 秒插入 一万 数据

第三种策略测试：

2.3 采用JDBC批处理（开启事务、无事务）

采用JDBC批处理时需要注意一下几点：

1、在URL连接时需要开启批处理、以及预编译

String url = “jdbc:mysql://localhost:3306/User?rewriteBatched
-Statements=true&useServerPrepStmts=false”;

2、PreparedStatement预处理sql语句必须放在循环体外

代码如下：

private long begin = 33112001;//起始id
private long end = begin+100000;//每次循环插入的数据量
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";


@org.junit.Test
public void insertBigData() {
    //定义连接、statement对象
    Connection conn = null;
    PreparedStatement pstm = null;
    try {
        //加载jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接mysql
        conn = DriverManager.getConnection(url, user, password);
  //将自动提交关闭
  // conn.setAutoCommit(false);
        //编写sql
        String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
        //预编译sql
        pstm = conn.prepareStatement(sql);
        //开始总计时
        long bTime1 = System.currentTimeMillis();

        //循环10次，每次十万数据，一共1000万
        for(int i=0;i<10;i++) {

            //开启分段计时，计1W数据耗时
            long bTime = System.currentTimeMillis();
            //开始循环
            while (begin < end) {
                //赋值
                pstm.setLong(1, begin);
                pstm.setString(2, RandomValue.getChineseName());
                pstm.setString(3, RandomValue.name_sex);
                pstm.setInt(4, RandomValue.getNum(1, 100));
                pstm.setString(5, RandomValue.getEmail(4, 15));
                pstm.setString(6, RandomValue.getTel());
                pstm.setString(7, RandomValue.getRoad());
                //添加到同一个批处理中
                pstm.addBatch();
                begin++;
            }
            //执行批处理
            pstm.executeBatch();
           //提交事务
  //        conn.commit();
            //边界值自增10W
            end += 100000;
            //关闭分段计时
            long eTime = System.currentTimeMillis();
            //输出
            System.out.println("成功插入10W条数据耗时："+(eTime-bTime));
        }
        //关闭总计时
        long eTime1 = System.currentTimeMillis();
        //输出
        System.out.println("插入100W数据共耗时："+(eTime1-bTime1));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    }
}

首先开始测试

无事务，每次循环插入10W条数据，循环10次，一共100W条数据。

结果如下图：

成功插入10W条数据耗时：3832
成功插入10W条数据耗时：1770
成功插入10W条数据耗时：2628
成功插入10W条数据耗时：2140
成功插入10W条数据耗时：2148
成功插入10W条数据耗时：1757
成功插入10W条数据耗时：1767
成功插入10W条数据耗时：1832
成功插入10W条数据耗时：1830
成功插入10W条数据耗时：2031
插入100W数据共耗时：21737

实验结果：

使用JDBC批处理，未开启事务下，平均每 2.1 秒插入 十万 条数据

接着测试

开启事务，每次循环插入10W条数据，循环10次，一共100W条数据。

结果如下图：

成功插入10W条数据耗时：3482
成功插入10W条数据耗时：1776
成功插入10W条数据耗时：1979
成功插入10W条数据耗时：1730
成功插入10W条数据耗时：1643
成功插入10W条数据耗时：1665
成功插入10W条数据耗时：1622
成功插入10W条数据耗时：1624
成功插入10W条数据耗时：1779
成功插入10W条数据耗时：1698
插入100W数据共耗时：19003

实验结果：

使用JDBC批处理，开启事务，平均每 1.9 秒插入 十万 条数据

3 总结

能够看到，在开启事务下 JDBC直接处理 和 JDBC批处理 均耗时更短。

• Mybatis 轻量级框架插入 , mybatis在我这次实验被黑的可惨了，哈哈。实际开启事务以后，差距不会这么大（差距10倍）。大家有兴趣的可以接着去测试

• JDBC直接处理，在本次实验，开启事务和关闭事务，耗时差距5倍左右，并且这个倍数会随着数据量的增大而增大。因为在未开启事务时，更新10000条数据，就得访问数据库10000次。导致每次操作都需要操作一次数据库。

• JDBC批处理，在本次实验，开启事务与关闭事务，耗时差距很微小（后面会增加测试，加大这个数值的差距）。但是能够看到开启事务以后，速度还是有提升。

结论：设计到大量单条数据的插入，使用JDBC批处理和事务混合速度最快

实测使用批处理+事务混合插入1亿条数据耗时：174756毫秒

4 补充

JDBC批处理事务，开启和关闭事务，测评插入20次，一次50W数据，一共一千万数据耗时：

1、开启事务(数据太长不全贴了)

插入1000W数据共耗时：197654

2、关闭事务(数据太长不全贴了)

插入1000W数据共耗时：200540

还是没很大的差距~

借用：

分别是：

• 不用批处理，不用事务；

• 只用批处理，不用事务；

• 只用事务，不用批处理；

• 既用事务，也用批处理；（很明显，这个最快，所以建议在处理大批量的数据时，同时使用批处理和事务）

PS：防止找不到本篇文章，可以收藏点赞，方便翻阅查找哦。