利用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;//起始idprivate 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.Testpublic 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条数据耗时：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
还是没很大的差距~
借用：
别离是：
- 不必批处理，不必事务；
- 只用批处理，不必事务；
- 只用事务，不必批处理；
- 既用事务，也用批处理；（很显著，这个最快，所以倡议在解决大批量的数据时，同时应用批处理和事务）