[Java] MySQL Batch 처리 방법

자바로 MySQL(혹은 MariaDB)에 대량의 데이터를 넣는 방법이 2가지 존재한다. 하나는 Batch 기능을 활용하여 Insert를 하는 방법, 나머지 하나는 Load 명령어를 활용하여 넣는 방법. 본 포스팅은 2가지 방법에 대해서 설명해 보고 장단점에 대해 말해보도록 하겠다.

---

add Batch 방법

Java에서 DB 데이터를 핸들링 할 수 있는 PreparedStatement 같은 라이브러리에 Batch 하는 방식을 제공하는 기능이 있다. 

Connection conn = null;
PreparedStatement pstmt = null;
int total = 0;
int row = 0;
 
try {
    conn = getConnection();
    pstmt = conn.prepareStatement(sql);
    conn.setAutoCommit(false);
    
    for(HashMap<String, Object> map : list) {
        int seq = 1;
        for(String key : fields.keySet()) {
            if(fields.get(key).equals("str")) {
                pstmt.setString(seq, (String)map.get(key));
            } else if (fields.get(key).equals("int")) {
                pstmt.setInt(seq, Integer.parseInt((String)map.get(key)));
            } else if (fields.get(key).equals("double")) {
                pstmt.setDouble(seq, Double.parseDouble((String)map.get(key)));
            }
            seq++;
        }
            
        pstmt.addBatch();
        pstmt.clearParameters();
        
        total++;
        row++;
        
        if(total > 1 && total % commitCut == 0) {
            pstmt.executeBatch();
            conn.setAutoCommit(true);
            pstmt.clearBatch();
            conn.setAutoCommit(false);
            
            LOGGER.info("TASK: [" + SERVICE + "] " + total + " insert DB");
            row = 0;
        }
    }
    
    if(row > 0) {
        pstmt.executeBatch();
        conn.setAutoCommit(true);
        pstmt.clearBatch();
        
        LOGGER.info("TASK: [" + SERVICE + "] " + total + " insert DB");
    }
    
} catch (Exception e) {
    LOGGER.error("insertBatch : " + e.getMessage());
    e.printStackTrace();
    total = -1;
} finally {
    if (pstmt != null) {
        try { pstmt.close(); } 
        catch (SQLException e) {}
    }
 
    if (conn != null) {
        try { conn.close(); } 
        catch (SQLException e) {}
    }
}

위 소스에서 핵심만 설명하자면, addBatch를 하기 전에 autocommit을 false로 설정하고, 원하는 만큼 addBatch가 되었을 경우, executeBatch를 실행하고, autocommit을 하는 개념인데 문제는 이렇게 Batch 작업을 하는 것이 문제가 있다.

DB 사용량이 적거나, 간단한 통계성 DB에서 작업을 하면 크게 문제가 없지만, 실시간으로 핸들링 해야 하는 DB에 저런식으로 데이터를 넣게 되면 DB 부하가 좀 심한 것으로 사료 된다.

위와 같은 방식을 로컬 MariaDB로 했을 경우 DB 부하가 심하지 않았던 것 같았는데 운영 MySQL DB에서는 성능이 심각하게 떨어졌다. MySQL 버전을 타는건지 소스에 이상이 있는건지 정확하게는 모르겠으나, addBatch전까지는 빠르게 세팅하다가 executeBatch 단계에서 속도가 심각하게 떨어졌다.

결국 Load하는 방식으로 수정하게 되었는데 사실 저 방식을 사용했던 이유는 PK가 Duplicate가 되었을 경우 update를 실행하기 위함이었다. 

Load 방식

MySQL에는 데이터를 Load하는 방식을 제공한다. 

Connection conn = null;
PreparedStatement pstmt = null;
int total = 0;
 
String sql = "LOAD DATA LOCAL INFILE \"" + filePath.replaceAll("\\\\", "/") + "\" INTO TABLE "
        + tableNm + " FIELDS TERMINATED BY \',\' "
        + " LINES TERMINATED BY \''\\n\' "
        + " IGNORE 1 LINES"
        + " (" + fields + ");";                
 
System.out.println(sql);
try {
    LOGGER.info(filePath + " load ...[BEGIN]");
    conn = getConnection();
    pstmt = conn.prepareStatement(sql);
    total = pstmt.executeUpdate();            
    
    pstmt.close();
    conn.close();
    LOGGER.info(filePath + " load...[END]");
} catch (Exception e) {
    e.printStackTrace();
    return 0;
}
 
return total;

위 소스는 내가 사용하는 메소드를 그대로 옮긴 것인데, fields는 DB에서 알아서 생성해줘서 csv에는 없을 경우 csv의 데이터가 꼬일 수 있는데 이럴 경우 csv의 컬럼 순서는 어떤 것이다를 미리 지정을 하는 것이다.

fields에는 "컬럼1,컬럼2,컬럼3...컬럼N" 이런식으로 설정을 하면 되고, filePath를 replace하는 경우는 MySQL이 인식할 수 있게 역슬래시 부분을 슬래시로 치환을 하는 것이다.

이런식으로 처리를 하니, DB쪽에서도 부하가 매우 적으며 속도가 10만건당 7초에서 5초로 줄어들었다. 사실 이 방식이 BEST라는 것은 누구나 알 수 있으나, Duplicate 처리에 대한 어려움으로 Batch 방식도 고려해볼수는 있다. 소량의 데이터를 자주 Batch 하는 경우(약 1만건 정도)라면 첫번째 방식도 나쁘지 않다고 생각한다.

어쨌거나, DB가 부하가 있느냐 없느냐의 문제이기 때문에, 사이트에 맞게 적절한 방식을 채택하는 것이 좋을테고 필자 같은 경우는 Duplicate 부분에 대한 문제로, 별도의 컬럼을 신규 생성하여 PK를 auto increment 컬럼으로 변경하였다.