使用Java将百万级别数据插入MySQL

1.分析是不是由主码，外码，索引形成的插入效率下降

• 主码：因为主码是每张表必须有的，不能删除。而mysql会对主码自动创建一个索引，这个索引默认是Btree索引，所以每次插入数据要额外的对Btree进行一次插入。这个额外的插入时间复杂度约为log(n)。这个索引没法删除，所以没法优化。可是每次插入的时候，因为主码约束须要检查主码是否出现，这又须要log(n)，可否减小这个开销呢？答案是确定的。咱们能够设置主码为自增id  AUTO_INCREMENT ,这样数据库里会自动记录当前的自增值，保证不会插入重复的主码，也就避免了主码的重复性检查。sql
• 外码：因为个人项目的插入表中存在外码，所以每次插入时须要在另外一张表检测外码存在性。这个约束是与业务逻辑相关的，不能随便删除。而且这个时间开销应当是与另外一张表大小成正比的常数，不该当越插入越慢才对。因此排除。数据库
• 索引：为了减小Btree插入的时间损耗，咱们能够在建表时先不建索引，先将全部的数据插入。以后咱们再向表里添加索引。该方法确实也下降了时间的开销。

2.将单条插入改成批量插入

参考：https://blog.youkuaiyun.com/frinder/article/details/38830723

对sql语句的value后面的值使用StringBuffer进行拼接，如下

INSERT INTO tb_big_data (count, create_time, random) VALUES (1, SYSDATE(), 0.8210896370852119),(2, SYSDATE(), 1.6634511491724535),(3, SYSDATE(), 2.1055467644775088),(4, SYSDATE(), 2.8879607056195793),(5, SYSDATE(), 3.961213935957325)

public static void insert() {  
        // 开时时间  
        Long begin = new Date().getTime();  
        // sql前缀  
        String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES ";  
        try {  
            // 保存sql后缀  
            StringBuffer suffix = new StringBuffer();  
            // 设置事务为非自动提交  
            conn.setAutoCommit(false);  
            // Statement st = conn.createStatement();  
            // 比起st，pst会更好些  
            PreparedStatement pst = conn.prepareStatement("");  
            // 外层循环，总提交事务次数  
            for (int i = 1; i <= 100; i++) {  
                // 第次提交步长  
                for (int j = 1; j <= 10000; j++) {  
                    // 构建sql后缀  
                    suffix.append("(" + j * i + ", SYSDATE(), " + i * j  
                            * Math.random() + "),");  
                }  
                // 构建完整sql  
                String sql = prefix + suffix.substring(0, suffix.length() - 1);  
                // 添加执行sql  
                pst.addBatch(sql);  
                // 执行操做  
                pst.executeBatch();  
                // 提交事务  
                conn.commit();  
                // 清空上一次添加的数据  
                suffix = new StringBuffer();  
            }  
            // 头等链接  
            pst.close();  
            conn.close();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        // 结束时间  
        Long end = new Date().getTime();  
        // 耗时  
        System.out.println("cast : " + (end - begin) / 1000 + " ms");  
    }  

3.及时释放查询结果

在个人数据库查询语句中，使用到了pres=con.prepareStatement(sql)来保存一个sql执行状态，使用了resultSet=pres.executeQuery来保存查询结果集。而在边查边插的过程当中，个人代码一直没有把查询的结果给释放，致使其不断的占用内存空间。当个人插入执行到50w条左右时，个人内存空间占满了，因而数据库的插入开始不之内存而以磁盘为介质了，所以插入的速度就开始变得十分的低下。所以，我在每次使用完pres和resultSet后，加入了释放其空间的语句：resultSet.close(); pres.close(); 。