jdbc插入百万级数据

62 阅读 0 评论 41 点赞

我是靠谱客的博主稳重茉莉，最近开发中收集的这篇文章主要介绍jdbc插入百万级数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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最佳)