MySQL快速插入100W条数据

最近研究了一下hibernate,想试一下hibernate的性能,于是想使用hibernate的批处理插入一百万条数据,先插入一万条试一下:

        Session session = entityManager.unwrap(Session.class);
        Transaction tx = session.beginTransaction();
        Long startTime = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setUsername("girl" + i);
            user.setPassword("000000");
            user.setBirthday(LocalDate.parse("19"+Math.round(Math.random()*2 + 7) + Math.round(Math.random()*9) +
                    "-" + StringUtils.leftPad(Math.round(Math.random()*11 + 1) + "", 2, '0') + "-" +
                    StringUtils.leftPad(Math.round(Math.random() * 28 + 1) + "", 2, '0')));
            user.setEnabled(true);
            user.setCreateDate(LocalDateTime.now());
            session.save(user);
            if(i % 50 == 0)
            {
                session.flush();
                session.clear();
            }
        }
        tx.commit();
        session.close();
        Long endTime = System.currentTimeMillis();
        System.out.println("OK,用时:" + (endTime - startTime));

用时25分钟,性能惨不忍睹,不知道网上的大神们是怎么用hibernate秒级插入一百万条的。
电脑配置:Intel® Core™ i5-4210U CPU @ 1.7GHz 2.4GHz
内存8GB
Win10企业版
后来试了一下jdbc插入,速度快了很多,一百万条大概40s左右,链接如下:
这里有详细的代码
我改写的代码如下:

Connection conn = null;
            PreparedStatement pstm =null;
            ResultSet rt = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(url, user, password);
                String sql = "INSERT INTO l_user(username,password,birthday, is_enabled, realname, create_date) " +
                        "VALUES(CONCAT('girl',?),'000000',?,?,?,?)";
                pstm = conn.prepareStatement(sql);
                Long startTime = System.currentTimeMillis();
                Random rand = new Random();
                int a,b,c,d;
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                for (int i = 4000001; i <= 5000000; i++) {
                    pstm.setLong(1, i);
                    pstm.setDate(2, new Date( format.parse("19"+Math.round(Math.random()*2 + 7) + Math.round(Math.random()*9) +
                    "-" + StringUtils.leftPad(Math.round(Math.random()*11 + 1) + "", 2, '0') + "-" +
                    StringUtils.leftPad(Math.round(Math.random() * 28 + 1) + "", 2, '0') +
                            " 00:00:00").getTime()));
                    pstm.setBoolean(3, true);
                    pstm.setString(4, "王" + i);
                    pstm.setDate(5, new Date(new java.util.Date().getTime()));
                    pstm.addBatch();
                }
                pstm.executeBatch();
                Long endTime = System.currentTimeMillis();
                System.out.println("OK,用时:" + (endTime - startTime));
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }finally{
                if(pstm!=null){
                    try {
                        pstm.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                        throw new RuntimeException(e);
                    }
                }
                if(conn!=null){
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                        throw new RuntimeException(e);
                    }
                }
        }

版权声明:本文为qq_36221219原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接和本声明。