阅读 1982

后端开发:【批量插入海量数据之Java插入MySql】解决方案

一、解析问题。

Java向MySql数据库插入万级记录时,采用的方案不同时执行速度会有所不同,数据量越大则优劣越明显。所以选取最优方案尤其重要,本文目前提供如下两种解决方案(不借用第三方框架或工具)。

二、解决问题。

1、方案一:循环逐条插入。

关键代码:

//DataModel 为自定义的数据模型类,dataList 即传入的即将要插入的数据集合;
public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException{

//开始计时;
Long begin = new Date().getTime();

//创建要执行的sql语句;
String sql = "insert into tb_ncdc values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

/* 创建并获取JDBC连接类"Connection"的实例对象。(DBUtil类内为数据库访问的配置信息,需要自定义)*/
Connection connection = new DBUtil().getDbCon();

//PrepareStatement类存放每条记录对应的字段值;
PreparedStatement preparedStatement= connection.prepareStatement(sql);

    for (int i = 0; i < dataList.size(); i ++) {

        preparedStatement.clearParameters();

        preparedStatement.setString(1, dataList.get(i).getSTN());

        preparedStatement.setString(2, dataList.get(i).getWBAN());

        preparedStatement.setString(3, dataList.get(i).getYEARMODA());

        preparedStatement.setString(4, dataList.get(i).getTEMP());

        preparedStatement.setString(5, dataList.get(i).getDEWP());

        preparedStatement.setString(6, dataList.get(i).getSLP());

        preparedStatement.setString(7, dataList.get(i).getSTP());

        preparedStatement.setString(8, dataList.get(i).getVISIB());

        preparedStatement.setString(9, dataList.get(i).getWDSP());

        preparedStatement.setString(10, dataList.get(i).getMXSPD());

        preparedStatement.setString(11, dataList.get(i).getGUST());

        preparedStatement.setString(12, dataList.get(i).getMAX());

        preparedStatement.setString(13, dataList.get(i).getMIN());

        preparedStatement.setString(14, dataList.get(i).getPRCP());

        preparedStatement.setString(15, dataList.get(i).getSNDP());

        preparedStatement.setString(16, dataList.get(i).getFRSHTT());

        preparedStatement.execute();
     }

    /*如果autocommit=false时(默认为true,即自动提交事务)记得将本次事务提交,否则数据库里没有数据的;*/
    //connection.commit();

    //所有数据库操作结束后记得关闭连接,减少内存的占用;
    preparedStatement.close();
    connection.close();

    // 结束时间
    Long end = new Date().getTime();

    //总耗时
    System.out.println("插入"+dataList.size()+"条数据的总时间为 : " + (end - begin)  + " ms");

    return 1;

}复制代码

2、方案二:分批事务插入。

//DataModel 为自定义的数据模型类,dataList 即传入的即将要插入的数据集合;
public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException {

        //设定每批、每次事务插入多少条数据;
        int itemNum = 1000;

        //开始时间;
        Long begin = new Date().getTime();

        // 创建sql前缀
        String prefix = "INSERT INTO tb_ncdc VALUES ";

        /* 创建并获取JDBC连接类"Connection"的实例对象。(DBUtil类内为数据库访问的配置信息,需要自定义) */
        Connection connection = new DBUtil().getDbCon();

        // PrepareStatement类存放每条记录对应的字段值;
        PreparedStatement preparedStatement= connection.prepareStatement("");

        // 创建sql后缀
        StringBuffer suffix = new StringBuffer();

        // 设置事务为非自动提交
        connection.setAutoCommit(false);

        //根据总的数据量计算需要分多少次事务插入;
        int numTrans = dataList.size() / itemNum + 1;

        //设定首次事务中的数据在集合中的索引为0;
        int numData = 0;

        //  外层循环,j代表提交事务次序;
        for (int j = 1; j <= numTrans; j++) {

        // 从索引numData开始查找总数为itemNum个数据,即为本批要插入的数据量;
        for (int i = numData; i < numData + itemNum; i++) {

            //判定如果是最后一批,可能会不足itemNum数量,则够数结束,防止数组越界;
            if (i == dataList.size()) {
                break;
            }

            // 构建sql后缀
            suffix.append("('" + dataList.get(i).getSTN() + "','" + dataList.get(i).getWBAN() + "','"
            + dataList.get(i).getYEARMODA() + "','" + dataList.get(i).getTEMP() + "','"
            + dataList.get(i).getDEWP() + "','" + dataList.get(i).getSLP() + "','"
            + dataList.get(i).getSTP() + "','" + dataList.get(i).getVISIB() + "','"
            + dataList.get(i).getWDSP() + "','" + dataList.get(i).getMXSPD() + "','"
            + dataList.get(i).getGUST() + "','" + dataList.get(i).getMAX() + "','"
            + dataList.get(i).getMIN() + "','" + dataList.get(i).getPRCP() + "','"
            + dataList.get(i).getSNDP() + "','" + dataList.get(i).getFRSHTT() + "'),");
        }

        // 构建完整sql
        String sql = prefix + suffix.substring(0, suffix.length() - 1);

        // 添加sql批;
        preparedStatement.addBatch(sql);

        // 执行sql批;
        preparedStatement.executeBatch();

        // 提交本次事务
        connection.commit();

        // 清空上一次的sql后缀;
        suffix = new StringBuffer();

        numData += itemNum;

        }

        // 所有数据库操作结束后记得关闭连接,减少内存的占用;
        preparedStatement.close();
        connection.close();

        // 结束时间
        Long end = new Date().getTime();

        // 耗时
        System.out.println("插入" + dataList.size() + "条数据的总时间为 : "+ (end - begin) + " ms");

        return 1;

}复制代码

三 、总结问题。

1.两种方案的主要区别在于,sql语句的不同、batch批和事务的使用。

单条插入sql语句:insert into Table (col1,col2...) values (val11,val12...);

多条批插入sql语句:insert intoTable (col1,col2...) values (val11,val12...),(val11,val12...),...;

2.本次测试的实例中,插入69万条数据左右,方案二要比方案一的速度快上10倍左右。具体测试得到的具体毫秒数可能不同。影响因素个人认为有如下几条:

(1)数据模型,每条数据记录的字段越多,就需要调整itemNum(每批插入的数据量,可以采用二分法找到最合适的数值),或者调整MySql数据库对每次执行sql语句的字节长度限制(网上自行搜索)。itemNum值找到最合适的,速度才可能在其他条件同等的条件下是最快的;

(2)主机配置。包括处理器性能、硬盘性能 ,mysql数据库可能也会影响到速度;

本节Demo源码附带测试数据包及数据库脚本GitHub下载地址:

github.com/Breaker-93/…

若有任何疑问,请留言。