MySQL優化之LOAD DATA LOCAL INFILE實作大批量插入

如今mysql普遍的插入方式有如下兩種：

1、循環單條插入

<insert id="insert" parameterType="com.chargeProject.consumer.entity.Test">
    insert into test (id, nums, name)
    values (#{id,jdbcType=INTEGER}, #{nums,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
  </insert>
           

2、拼裝批量插入

<insert id="batchInsert" parameterType="java.util.List">
    insert into test (id, nums, name)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.id,jdbcType=INTEGER}, #{item.nums,jdbcType=INTEGER}, #{item.name,jdbcType=VARCHAR})
    </foreach>
</insert>
           

一般都是通過mybatis架構進行輔助實作的，當然也可以自動拼裝。今天介紹的是mysql自帶的一種批量插入方式且效率更高，通過LOAD DATA LOCAL INFILE實作大批量插入。

MySQL使用LOAD DATA LOCAL INFILE從檔案中導入資料比insert語句要快，MySQL文檔上說要快20倍左右。

但是這個方法有個缺點，就是導入資料之前，必須要有檔案，也就是說從檔案中導入。這樣就需要去寫檔案，以及檔案删除等維護。某些情況下，比如資料源并發的話，還會出現寫檔案并發問題，很難處理。

那麼有沒有什麼辦法，可以達到同樣的效率，直接從記憶體（IO流中）中導入資料，而不需要寫檔案呢？

MySQL社群提供這樣一個方法：setLocalInfileInputStream（），此方法位于com.mysql.jdbc.PreparedStatement 類中。通過使用 MySQL JDBC 的setLocalInfileInputStream 方法實作從Java InputStream中load data local infile 到MySQL資料庫中。

代碼如下：

@Component
public class LoadDataInFileUtil {

    private Logger logger = LoggerFactory.getLogger(LoadDataInFileUtil.class);
    private Connection conn = null;
    @Resource
    private JdbcTemplate jdbcTemplate;

    /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/

    /**
     * 将資料從輸入流加載到MySQL。
     *
     * @param loadDataSql  SQL語句。
     * @param dataStream   輸入流。
     * @param jdbcTemplate JDBC。
     * @return int         成功插入的行數。
     */
    private int bulkLoadFromInputStream(String loadDataSql,
                                        InputStream dataStream,
                                        JdbcTemplate jdbcTemplate) throws SQLException {
        if (null == dataStream) {
            logger.info("輸入流為NULL，沒有資料導入。");
            return 0;
        }
        conn = jdbcTemplate.getDataSource().getConnection();
        PreparedStatement statement = conn.prepareStatement(loadDataSql);
        int result = 0;
        if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {
            com.mysql.jdbc.PreparedStatement mysqlStatement = statement.unwrap(com.mysql.jdbc.PreparedStatement.class);
            mysqlStatement.setLocalInfileInputStream(dataStream);
            result = mysqlStatement.executeUpdate();
        }
        return result;
    }

    /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/

    /**
     * 組裝 SQL 語句。
     *
     * @param dataBaseName 資料庫名。
     * @param tableName    表名。
     * @param columnName   要插入資料的列名。
     */
    public String assembleSql(String dataBaseName, String tableName, String columnName[]) {
        String insertColumnName = StringUtils.join(columnName, ",");
        String sql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE " + dataBaseName + "." + tableName + "(" + insertColumnName + ")";
        return sql;
    }

    /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/

    /**
     * 往 StringBuilder 裡追加資料。
     *
     * @param builder StringBuilder。
     * @param object  資料。
     */
    public void builderAppend(StringBuilder builder, Object object) {
        builder.append(object);
        builder.append("\t");
    }

    /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/

    /**
     * 往 StringBuilder 裡追加一條資料的最後一個字段。
     *
     * @param builder StringBuilder。
     * @param object  資料。
     */
    public void builderEnd(StringBuilder builder, Object object) {
        builder.append(object);
        builder.append("\n");
    }

    /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/

    /**
     * 通過 LOAD DATA LOCAL INFILE 大批量導入資料到 MySQL。
     *
     * @param sql     SQL語句。
     * @param builder 組裝好的資料。
     */
    public int fastInsertData(String sql, StringBuilder builder) {
        int rows = 0;
        InputStream is = null;
        try {
            byte[] bytes = builder.toString().getBytes();
            if (bytes.length > 0) {
                is = new ByteArrayInputStream(bytes);
                //批量插入資料。
                long beginTime = System.currentTimeMillis();
                rows = bulkLoadFromInputStream(sql, is, jdbcTemplate);
                long endTime = System.currentTimeMillis();
                logger.info("LOAD DATA LOCAL INFILE :【插入" + rows + "行資料至MySql中，耗時" + (endTime - beginTime) + "ms。】");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != is) {
                    is.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (IOException | SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/

}
           

調試代碼如下：

// 資料庫名。
    public static final String DATA_BASE_NAME = "charge";
    // 表名。
    public static final String TABLE_NAME = "test";
    // 要插入資料的列名。（必須與插入的資料一一對應）
    public static final String COLUMN_NAME[] = {"id", "nums", "name"};



    @Override
    public ResultContent insert(String name) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();

        StringBuilder sb = new StringBuilder();
        List<Test> list = new ArrayList<>();
        for(int i = 1; i < 100000; i++) {
            loadDataInFileUtil.builderAppend(sb, UUID.randomUUID().toString());
            loadDataInFileUtil.builderAppend(sb, i);
            loadDataInFileUtil.builderEnd(sb, name + i);
        }


        String sql = loadDataInFileUtil.assembleSql(DATA_BASE_NAME, TABLE_NAME, COLUMN_NAME);
        int insertRow = loadDataInFileUtil.fastInsertData(sql, sb);
        System.out.println("insert應收報表數量insertRow："+insertRow);


        stopWatch.stop();
        System.out.println("花費時間" + stopWatch.getTotalTimeSeconds());



        System.out.println("---------方法執行結束--------------");
        return new ResultContent(0, "success", name);
    }
           

經過測試插入1w條資料時候與拼裝批量插入語句時間差别不大，當插入數量達到10w出現了明顯的時間差：

拼裝批量插入語句花費時間：6.83s

LOAD DATA LOCAL INFILE實作大批量插入花費時間：1.23s

當表格的字段更多資料量更大出現的時間差就越大。

總結：當需要進行大批量資料插入的時候，可以優先考慮LOAD DATA LOCAL INFILE實作方式。