jdbc插入百万级数据

素颜马尾好姑娘i 2021-10-15 11:03 349阅读 0赞

1.背景介绍

快速插入百万级数据

我们一般用JDBC向数据库插入数据时，是用statement或preparedstatment中的update或execute方法来实现，但当要插入的数据为百万级别甚至千万级别时，这种方法显然无法满足我们的需求，可以尝试写一个循环,循环以百万次statement.excute(sql)方法，我这边测试是半个小时还没有插完。

缺点:一条一条的sql执行，大部分时间都是消耗在打开数据库，关闭数据库上了。  
是否可以只发一句sql而实现批量插入呢  
  
为此jdbc也提供了相应的方法来应对这种场景。今天就来介绍jdbc批处理的相关api，当然如果今天仅仅是介绍api,那大家大可百度就好了  
这次分享会除了介绍api，更重要的是介绍些我踩过的坑以及对此相关的一些见解。  
  
  
2.知识剖析  
          
批量处理  
             
|-- Statement  
    -- PreparedStatement  子接口，建议使用，会对sql语句先进行编译再给数据库  
PreparedStatement对象不仅包含了SQL语句，而且大多数情况下这个语句已经被预编译过，因而当其执行时，只需DBMS运行SQL语句，而不必先编译。  
当你需要执行Statement对象多次的时候，PreparedStatement对象将会大大降低运行时间，当然也加快了访问数据库的速度。  
                 
批处理相关方法  
void addBatch(String sql)  添加批处理     
void clearBatch()      清空批处理  
int\[\] executeBatch()    执行批处理     
  
事务提交  
con.setAutoCommit(false);     默认自动提交，在这里需要设置为false,手动提交  
con.commit();    
con.rollback();  
          
3.常见问题  
批量处理无法生效          
1、rewriteBatchedStatements=true  
2、mysql 5.1.13  
  
4.编码实战

@Test
    public void testForRemote() throws Exception {
        try {
            //准白sql语句
            String sql = "INSERT INTO table1(NAME,gender,age,qq) VALUES(?,?,?,?)";
            Connection con = null;
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
           //获取到远程服务器的连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Jnshu1?useUnicode=true&"+
                            "characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true",
                    "root","root");
            //设置非自动提交事务
            con.setAutoCommit(false);
     
            PreparedStatement pstat = con.prepareStatement(sql);
    
            //获得模拟数据
            list2 = getPersonWithArg();
            System.out.println(list2.size());
            long start = System.currentTimeMillis();
            for (int i=0; i<list2.size(); i++) {
     
                Person person = list2.get(i);
              pstat.setString(1, person.getNAME());
             pstat.setString(2, person.getGender());
             pstat.setString(3, person.getAge());
                pstat.setString(4, person.getQq());
     
                //10w提交一次
                pstat.addBatch();
                if(i % 100000 == 0){
                    pstat.executeBatch();
                    pstat.clearBatch();
                }
          }
              pstat.executeBatch(); //执行批处理
              pstat.clearBatch();  //清空批处理
              con.commit();
              long end = System.currentTimeMillis();
            pstat.close();
            con.close();
            System.out.print((end-start)/1000+"秒。");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
     
    // 模拟数据
    //1000万数据执行后会内存溢出
    public List<Person> getPersonWithArg(){
     
        //由一个StringBuffer来代替之前4个String的反复字符串操作。
        StringBuffer sb = new StringBuffer("");// String = "";
        for (int r = 0; r < 1000000 ; r++) {
            Person person = new Person();
            //随机产生姓名
            for (int i = 0; i < 3; i++) {
                sb.append((char) (0x4e00 + (Math.random() * (0x9fa5 - 0x4e00 + 1))));
            }
            person.setNAME(sb.toString());
            //用完后清楚里面的内容，以供下一次使用
            sb.delete(0,sb.length());
     
            //随机产生性别
            int sexf = (int)(Math.random() * 2);//[0,2)
            if(sexf==0){
                sb.append("男");
            }else if( sexf == 1){
                sb.append("女");
            }else {
                sb.append("Unknow");
            }
            person.setGender(sb.toString());
            sb.delete(0,sb.length());
            //随机产生年龄
            int d;
            for (int i = 0; i < 2; i++) {
                d = (int)(Math.random()*10);
                sb.append(d);
            }
            person.setAge(sb.toString());
            sb.delete(0,sb.length());
     
            // 随机产生qq
            int j;
            for (int i = 0; i < 10; i++) {
                d = (int)(Math.random()*10);
                sb.append(d);
            }
            person.setQq(sb.toString());
            sb.delete(0,sb.length());
            list.add(person);
        }
        return list;
    }

5.扩展思考  
1.  批处理addbatch为什么比一般插入效率高，难道仅仅因为名字叫批处理？  
答案：当然不是，如果这样的话我们大可再搞一个超级批处理叫addsuperbatch,  
其实我们大可做一个这样的实验，在sql语句后面加一个 ; ,看程序是否可以正常运行  
我们知道，如果不是批处理加一个分号是很正常的，可以正常执行的。那么现在我这里做个实验  
可以看到，他这里说你的SQL语句存在语法问题，在几几行附近我们检查把它复制到命令行去执行，发现是没有任何问题的。没错，问题就出现在分号上，关于分号问题  
一般的sql语句是要分号结尾的，平时的一般插入加与不加都可，你不加系统会自动给你加上，  
但批处理不一样，它会对你的sql语句进行加工然后发送给mysql服务端执行加工后的语句，  
可以看到我这里写的是“INSERT INTO table1(NAME,gender,age,qq) VALUES(?,?,?,?)”  
如果把这条语句如实的发给数据库，即便把里面的问号传好了相关参数，也是不可能执行上百万条语句的，  
要知道原因，这里先给介绍一个sql语句，(打开navicat展示)，可以看到这条语句后面还可以  
随便加，加几十万条都可以。那么答案自然出来了，它会把我们的“INSERT INTO table1(NAME,gender,age,qq) VALUES(?,?,?,?)”  
封装成“INSERT INTO table1(NAME,gender,age,qq) VALUES(?,?,?,?),( ∞ ，∞ )”，  
即括号内是参数组，多个参数组用逗号隔开，  
这是符合mysql语法的，  
如果你加了分号，就变成了“INSERT INTO table1(NAME,gender,age,qq) VALUES(?,?,?,?)；,( ∞ ，∞ )；”  
那么综上我们可以知道，批处理比一般的插入快，原因就在于批处理只发送了一条sql过去，数据库只开关一次  
即可，而一般插入如果插100万要开关数据库100万次，大大的开销就在这里。而一条大体量的批处理sql语句，  
尽管文本长，但数据库对文本处理的性能差异是毫秒级的，而不停的开关数据库是秒级别的，所以一般方式插入百万数据可以  
可以插一个小时，有时候甚至中间出个错会卡机。  
  
2.批处理时，每批不同数量sql是否对性能有影响(上数据。5w/10w最佳)

**进入正式测试，100万数据插入**

**100万整个一批   27s**

**50万                 26s**

**20万                28**

**10万                19s**

**5万一批处理    18s**

**1万                  28s**

**5000               48s**

**由上面可知，批处理的速度跟每批处理数据量有关，每次处理太少会慢，但太高也不好，经过上面的测试对于100万的插入每批10万或5万最佳。**

3.插入1000w时会怎么样？

很可能会内存溢出，这边测试如果使用直接插入属性方法没事，如果采用我这里的产生  
1000w个对象的方式，测试过几次溢出率100%，  
java.lang.OutOfMemoryError: Java heap space