INSERT 쿼리 최적화

데이터베이스의 INSERT 쿼리는 애플리케이션 성능에 중요한 영향을 미친다. 단일 레코드 삽입은 간단해 보일 수 있지만, 대규모 데이터 삽입이나 빈번한 트랜잭션 환경에서는 최적화가 필요하다.

 

INSERT 쿼리 성능에 영향을 미치는 요소

---

1. 테이블의 인덱스

• 인덱스는 데이터를 빠르게 조회하는 데 유용하지만, INSERT 시에는 추가적인 작업을 발생시킨다.
• 데이터 삽입 시마다 인덱스도 함께 업데이트되어야 하기 때문이다.

 

2. 제약 조건 (Constraints)

• FOREIGN KEY, UNIQUE 같은 제약 조건은 데이터를 검증하는 데 추가적인 시간을 소모한다.

 

3. 트랜잭션 처리

• 트랜잭션은 데이터 일관성을 보장하지만, 과도하게 사용되면 성능을 저하시킬 수 있다.

 

4. 대량 삽입 vs. 단일 삽입

• 대량의 데이터를 삽입하는 경우, 효율적인 배치(Batch) 삽입 방식이 필요하다.

 

---

 

대량 데이터 삽입 시 배치 처리 사용

---

배치 처리란?

---

배치 처리(Batch Processing)는 여러 개의 INSERT 쿼리를 하나의 쿼리로 묶어서 한 번에 실행하는 방식이다.

이는 데이터베이스와의 네트워크 왕복(Roung Trip) 횟수를 줄이고, 데이터 삽입 작업을 효율적으로 처리하도록 돕는다.

 

배치 처리의 필요성

---

1. 네트워크 오버헤드 감소

• 일반적으로 데이터베이스는 클라이언트와 서버 간의 네트워크를 통해 통신한다.
• 개별 INSERT 쿼리마다 왕복이 발생하면 불필요한 네트워크 부하가 증가한다.
• 배치 처리를 사용하면 하나의 요청으로 다수의 데이터를 처리해 네트워크 오버헤드를 줄일 수 있다.

 

2. 트랜잭션 관리 최적화

• 배치 처리를 통해 여러 데이터 삽입을 하나의 트랜잭션으로 묶으면 성능이 향상된다.
• 각 INSERT 문마다 트랜잭션을 열고 닫는 경우와 비교했을 때 로그 기록과 동기화 비용이 줄어든다.

 

3. 리소스 활용 개선

• 데이터베이스 엔진은 하나의 쿼리로 여러 데이터를 처리할 때 더 효율적으로 내부 자원을 사용할 수 있다.
• 특히 디스크 I/O와 메모리 관점에서 효율성이 높아진다.

 

배치 처리 구현 방법

---

1. SQL 다중 행 삽입

• 가장 간단하고 일반적인 방법이다.
• 여러 개의 데이터 행을 하나의 ISNERT 문으로 묶어 처리한다.

예시

INSERT INTO users (name, email)
VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com');

 

주의사항

• 배치 데이터의 크기가 너무 크면 쿼리가 실패하거나 메모리 문제가 발생할 수 있다.
  • 너무 작은 배치 크기
    • 네트워크 및 트랜잭션 오버헤드를 충분히 줄이지 못한다.
    • 처리 속도가 낮아진다.
  • 너무 큰 배치 크기
    • 메모리 부족(Memory Overflow) 문제가 발생할 수 있다.
    • 데이터베이스 내부 처리 큐가 과부하를 겪을 수 있다.
• 적절한 배치 크기(예: 1,000~10,000행)를 설정하는 것이 중요하다.

 

배치 처리의 단점

1. 에러 처리의 복잡성
   • 배치 삽입 중 한 레코드에서 에러가 발생하면 전체 배치 작업이 실패하거나, 데이터의 일부만 삽입될 수 있다.
   • 데이터가 부분적으로 삽입되면 데이터 불일치(Inconsistency)가 발생할 위험이 있다.
   • 배치 처리 중 오류가 발생할 경우, 전체 트랜잭션을 롤백할지 또는 부분적으로 진행할지 설계가 필요하다.
     
     • SAVEPOINT를 사용해 특정 시점으로 복구를 고려할 수 있다.
2. 메모리 사용량 증가
   • 배치 처리가 너무 크면 메모리에 데이터가 과도하게 적재되어 성능이 저하되거나 시스템이 불안정해질 수 있다.
   • 특히 클라이언트 측의 메모리가 제한적일 경우, Out of Memory(OOM) 오류가 발생할 수 있다.
3. 잠금(Locking) 문제
   • 배치 처리 중 데이터베이스의 특정 리소스(테이블, 인덱스, 페이지)가 잠길 수 있다.
   • 예를 들어, 인덱스가 있는 테이블에 대량 데이터를 삽입하면, 테이블이 잠기면서 다른 트랜잭션이 대기 상태에 빠질 수 있다.
4. 동시성 문제
   • 여러 클라이언트가 동시에 배치 작업을 수행하면 경쟁 상태(Race Condition)가 발생할 수 있다.
   • 동일한 자원(테이블, 행 등)에 대해 동시 삽입 시 충돌이 발생할 가능성이 높아진다.
   • 여러 클라이언트가 동시에 대량 데이터를 삽입하는 경우, 동시성 문제가 발생하지 않도록 잠금(lock) 메커니즘을 설계해야 한다.
5. 입력 데이터의 유효성 검증 부족
   • 배치 처리에서는 데이터의 유효성 검증이 모든 레코드에 대해 동일하게 처리된다.
   • 개별 레코드마다 복잡한 검증이 필요한 경우, 배치 처리로 인해 세부 검증 로직을 적용하기 어려울 수 있다.

 

참고: max_allowd_packet
max_allowd_packet은 MySQL에서 클라이언트가 서버로 보낼 수 있는 최대 패킷 크기를 정의하는 서버 설정 변수이다.
이는 INSERT, UPDATE, LOAD DATA INFILE 같은 쿼리에서 클라이언트가 서버로 보내는 데이터 크기 제한에 영향을 미친다.

max_allowd_packet: 단일 요청(쿼리)이 포함할 수 있는 데이터 크기의 상하선
- 기본값: 일반적으로 MySQL의 기본값은 4MB이다.
- 최대값: MySQL 버전에 따라 최대 1GB까지 설정 가능
- 값은 바이트 단위로 설정된다. 

 

 

2. PreparedStatemet를 활용한 배치 (Java JDBC)

• PreparedStatement를 활용한 배치 작업은 쿼리 컴파일, 네트워크 통신, 트랜잭션 관리 등 여러 측면에서 최적화되어 INSERT 쿼리 성능을 향상시킨다.
• Java에서 JDBC를 사용할 경우, PreparedStatement의 배치 기능을 활용할 수 있다.

예시

Connection connection = DriverManager.getConnection(DB_URL, USER, PASSWORD);
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);

connection.setAutoCommit(false); // 트랜잭션 시작

for (int i = 0; i < dataList.size(); i++) {
    pstmt.setString(1, dataList.get(i).getName());
    pstmt.setString(2, dataList.get(i).getEmail());
    pstmt.addBatch();

    if (i % 1000 == 0) { // 배치 크기 설정 (예: 1000건마다 실행)
        pstmt.executeBatch();
    }
}
pstmt.executeBatch(); // 남은 데이터 실행
connection.commit(); // 트랜잭션 종료
connection.close();

 

쿼리 재사용으로 인한 컴파일 비용 절감

• 일반적인 SQL 실행 과정
  1. SQL 파싱
  2. 쿼리 계획 생성
  3. 컴파일
  4. 실행
• PreparedStatement는 SQL 파싱과 쿼리 계획 생성을 한 번만 수행하고, 동일한 쿼리를 반복해서 실행한다.
  • 컴파일 비용이 감소하므로 CPU 사용량이 줄고, 데이터 삽입 속도가 빨라진다.

예시

INSERT INTO users (name, email) VALUES (?, ?);

• 위 쿼리는 한 번 컴파일된 후 여러 번 실행될 때 매번 파싱이나 쿼리 계획 생성이 필요 없다.

 

네트워크 왕복 횟수 감소

• 일반적인 단일 INSERT 실행
  • 매 INSERT마다 클라이언트와 데이터베이스 간의 요청 및 응답(Round Trip)이 발생한다.
  • 네트워크 왕복 횟수가 많아질수록 대기 시간이 증가한다.
• PreparedStatement의 배치를 사용하면 여러 INSERT 요청을 한 번에 묶어 전송하므로 네트워크 왕복 횟수를 크게 줄일 수 있다.
  • 네트워크 통신 부하가 줄어들어 전송 속도가 빨라진다.

예시 (JDBC Batch)

PreparedStatement pstmt = connection.prepareStatement(
    "INSERT INTO users (name, email) VALUES (?, ?)"
);
for (User user : userList) {
    pstmt.setString(1, user.getName());
    pstmt.setString(2, user.getEmail());
    pstmt.addBatch();
}
pstmt.executeBatch(); // 네트워크 요청은 한 번만 발생

 

트랜잭션 관리 효율화

• 일반적인 단일 INSERT 작업
  • 각 INSERT 쿼리는 트랜잭션을 시작하고 커밋하는 과정을 반복한다.
  • 이 과정에서 디스크 I/O 및 로그 작성이 빈번하게 발생하여 성능이 저하된다.
• PreparedStatement의 배치는 하나의 트랜잭션으로 여러 INSERT를 묶어 처리할 수 있다.
  • 트랜잭션이 하나로 줄어들면 디스크 I/O와 로그 기록 횟수도 감소한다.

예시

connection.setAutoCommit(false); // 트랜잭션 시작
for (User user : userList) {
    pstmt.setString(1, user.getName());
    pstmt.setString(2, user.getEmail());
    pstmt.addBatch();
}
pstmt.executeBatch(); // 모든 INSERT를 한 트랜잭션으로 처리
connection.commit();  // 트랜잭션 종료

 

PreparedStatement의 단점

1. 구현 복잡성
   • SQL 다중 행 삽입보다 프로그래밍 코드가 더 복잡하다.
2. 드라이버 의존성
   • JDBC와 같은 드라이버 설정이 필요하며, 환경에 따라 최적화 수준이 다를 수 있다.

 

 

SQL 다중 행 삽입 vs. PreparedStatement

두 방식 모두 대량의 데이터를 효율적으로 삽입할 수 있는 방식이지만, 각각 장단점이 명확하며, 사용하는 환경이나 요구사항에 따라 최적의 선택은 달라질 수 있다.

특징	SQL 다중 행 삽입	PreparedStatement 배치
복잡한 데이터 처리	어려움	유연함
SQL 길이 제한	제한 존재	없음
네트워크 왕복 횟수	한 번	한 번
쿼리 실행 계획 재사용	불가능	가능
가독성	단순, 간결	구현 코드가 다소 복잡
환경 의존성	데이터베이스 SQL 표준에 따라 다름	JDBC 또는 드라이버 환경에 따라 다름
동적 데이터 삽입	적합하지 않음	적합

 

일반적으로 데이터베이스는 Connection에 대한 오버헤드가 많이 발생하며, 성능에 중요한 영향을 미친다.

이러한 관점에서 봤을 때, 일반적으로 SQL 다중 행 삽입 방식이 PreparedStatement에 비해 더 효율적이다.

하지만, 1만건의 데이터를 넘지 않는 경우에는 두 방식의 차이는 거의 없다.

 

SQL 다중 행 삽입 방식이 더 적합한 경우

1. 정적이고 간단한 데이터 삽입
   • 삽입할 데이터가 미리 준비되어 있고, 동적으로 변경되지 않는 경우
   • 예: 초기 데이터 로드, 정적 참조 데이터 삽입
2. 단순한 대량 데이터 처리
   • 데이터 구조가 단순하고, SQL 문 길이 제한 내에서 대량 데이터를 처리해야 할 때
   • 예: CSV 데이터를 변환 없이 그대로 삽입
3. 한 번에 끝나는 작업
   • 한 번의 실행으로 끝나는 단순한 작업이며, 데이터 크기가 클 필요는 없는 경우

 

PreparedStatement 배치 처리가 적합한 경우

1. 동적 데이터 삽입
   • 삽입 데이터가 실행 중에 생성되거나, 입력 데이터에 따라 변경되는 경우
   • 예: 사용자 입력 데이터 처리, API 요청 데이터를 데이터베이스에 저장
2. 복잡한 데이터 처리
   • 삽입 전 데이터 검증, 변환, 조건 처리 등 복잡한 로직이 필요한 경우
   • 예: 데이터 검증 후 유효한 데이터만 삽입
3. 다양한 환경 지원
   • 다른 데이터베이스 환경에서도 동일한 방식으로 실행해야 하거나, 코드 재사용을 높이고자 하는 경우