Temporary Table을 활용한 Upsert 성능 개선

게시 2025/12/04 업데이트 2025/12/04

By Jeek Lee 11 분읽는 시간

I. 문제 상황

Course(강의) 엔티티와 Skill(기술) 엔티티는 다대다 관계로 연결되어 있다. 하나의 Course는 여러 Skill을 가질 수 있고, 각 Skill도 여러 Course에 연결될 수 있다. 이 관계는 course_skill이라는 매핑 테이블로 관리된다.

course 테이블        course_skill 테이블         skill 테이블
┌─────────┐      ┌──────────┬──────────┐      ┌─────────┐
│ id (PK) │──┐   │ course_id│ skill_id │   ┌──│ id (PK) │
│ url     │  └──>│          │          │<──┘  │ name    │
│ ...     │      └──────────┴──────────┘      │ ...     │
└─────────┘                                   └─────────┘

약 5000건의 크롤링 결과를 DB에 저장해야 하는 상황에서, 다음과 같은 요구사항이 있었다.

Course는 Upsert: 이미 존재하는 Course(url 기준)는 업데이트, 없으면 새로 생성
Skill 관계는 완전 교체: 기존 Course-Skill 관계를 모두 삭제하고 새로운 관계로 대체
배치 처리: 한 번에 대량의 데이터를 효율적으로 처리

II. 해결 방안

1. JPA `saveAll()`의 한계

JPA의 saveAll()은 내부적으로 다음과 같이 동작한다.

각 Course마다:
SELECT로 존재 여부 확인
INSERT 또는 UPDATE 수행
course_skill 관계 조회 (SELECT)
기존 관계 삭제 (DELETE)
새로운 관계 삽입 (INSERT × N)

만약 1,000개의 Course를 처리하고, 각 Course가 평균 5개의 Skill을 가진다면:

SELECT: 1,000번 (Course 존재 확인)
INSERT/UPDATE: 1,000번 (Course 저장)
SELECT: 1,000번 (기존 course_skill 조회)
DELETE: 1,000번 (기존 관계 삭제)
INSERT: 5,000번 (새로운 관계 삽입)

총 9,000번의 쿼리가 실행된다.

2. Temporary Table 활용 전략

이 문제를 해결하기 위해 다음과 같은 전략을 세웠다.

Batch Insert/Update: JdbcTemplate의 batchUpdate()로 Course를 한 번에 처리
Temporary Table 생성: 처리할 Course의 URL만 담은 임시 테이블 생성
JOIN을 통한 일괄 삭제: 임시 테이블과 JOIN해서 관련된 course_skill을 한 번에 삭제
ID 매핑 조회: 임시 테이블을 활용해 URL → ID 매핑 정보를 한 번에 조회
Batch Insert: 새로운 course_skill 관계를 한 번에 삽입

Course Batch Insert (1번)
  ↓
Temporary Table 생성 (1번)
  ↓
course_skill 일괄 삭제 (1번)
  ↓
URL → ID 매핑 조회 (1번)
  ↓
course_skill Batch Insert (1번)
  ↓
Temporary Table 삭제 (1번)

III. MySQL Temporary Table이란?

MySQL Temporary Table은 현재 세션에서만 사용할 수 있는 임시 테이블이다. 세션이 종료되거나 명시적으로 삭제하면 자동으로 제거되며, 다른 세션에서는 접근할 수 없다.

Temporary Table의 특징

세션 격리: 각 세션마다 독립적인 임시 테이블을 가질 수 있어 동시성 문제가 없다
자동 정리: 세션 종료 시 자동으로 삭제되어 메모리 누수 걱정이 없다
빠른 속도: 메모리에 생성되거나(작은 크기) 디스크에 생성되지만(큰 크기) 일반 테이블보다 오버헤드가 적다
인덱스 지원: 일반 테이블처럼 인덱스를 생성해 JOIN 성능을 높일 수 있다

Temporary Table의 활용 시나리오

  
-- 1. 임시 테이블 생성
CREATE TEMPORARY TABLE temp_ids (id BIGINT PRIMARY KEY);

-- 2. 데이터 삽입
INSERT INTO temp_ids VALUES (1), (2), (3);

-- 3. JOIN을 통한 대량 작업
DELETE FROM orders 
WHERE user_id IN (SELECT id FROM temp_ids);

-- 4. 세션 종료 또는 명시적 삭제
DROP TEMPORARY TABLE IF EXISTS temp_ids;

배치 처리에서 Temporary Table이 유용한 이유는 “처리할 대상 집합”을 테이블로 만들어 SQL의 집합 연산을 활용할 수 있기 때문이다. 개별 레코드를 반복문으로 처리하는 대신, 한 번의 JOIN으로 대량의 데이터를 처리할 수 있다.

IV. 구현 상세

1. 전체 흐름

  
@Override
public List<Course> saveAll(List<Course> courses) {
    if (courses.isEmpty()) return List.of();

    bulkInsertCourses(courses);                        // ① Course Upsert
    creatTemporaryTableAndInsertUrls(courses);         // ② Temporary Table 생성
    deleteCourseSkillsByTemporaryTable();              // ③ 기존 관계 삭제
    Map<String, Long> urlToIdMap = getUrlToIdMapFromTemporaryTable();  // ④ ID 매핑
    Map<Course, Long> courseIdMap = courses.stream()
            .collect(Collectors.toMap(
                    course -> course,
                    course -> urlToIdMap.get(course.url().toString())
            ));
    bulkInsertCourseSkills(courses, courseIdMap);      // ⑤ 매핑 테이블 삽입
    dropTemporaryTable();                              // ⑥ Temporary Table 삭제
    
    return courses.stream()
            .map(course -> course.withId(new CourseId(courseIdMap.get(course))))
            .toList();
}

이 메서드는 크게 여섯 단계로 나뉜다. 각 단계를 상세히 살펴보자.

2. Course Upsert (①)

  
private void bulkInsertCourses(List<Course> courses) {
    String sql = """
        INSERT INTO content.course
        (url, platform, title, instructor, description, difficulty, rating, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            platform = VALUES(platform),
            title = VALUES(title),
            instructor = VALUES(instructor),
            description = VALUES(description),
            difficulty = VALUES(difficulty),
            rating = VALUES(rating),
            updated_at = VALUES(updated_at)
    """;

    jdbcTemplate.batchUpdate(sql, courses, 1000, (ps, course) -> {
        ps.setString(1, course.url().toString());
        ps.setString(2, course.platform().name());
        ps.setString(3, course.title());
        ps.setString(4, course.instructor());
        ps.setString(5, course.description());
        ps.setString(6, course.difficulty().name());
        ps.setFloat(7, course.rating());
        ps.setTimestamp(8, Timestamp.valueOf(course.createdAt()));
        ps.setTimestamp(9, Timestamp.valueOf(course.updatedAt()));
    });
}

ON DUPLICATE KEY UPDATE는 MySQL의 Upsert 구문이다. url 컬럼에 UNIQUE 제약이 걸려있어, 중복되는 url이 들어오면 INSERT 대신 UPDATE를 수행한다.

3. Temporary Table 생성 및 데이터 삽입 (②)

  
private void creatTemporaryTableAndInsertUrls(List<Course> courses) {
    jdbcTemplate.execute("CREATE TEMPORARY TABLE temp_course_urls (url VARCHAR(500) PRIMARY KEY)");
    
    String sql = "INSERT INTO temp_course_urls (url) VALUES (?)";
    jdbcTemplate.batchUpdate(sql, courses, 1000, 
        (ps, course) -> ps.setString(1, course.url().toString())
    );
}

처리할 Course의 URL만 담은 임시 테이블을 생성한다. url을 PRIMARY KEY로 지정해 중복을 방지하고, 이후 JOIN 시 인덱스를 활용할 수 있도록 한다.

4. 기존 Skill 관계 일괄 삭제 (③)

  
private void deleteCourseSkillsByTemporaryTable() {
    String sql = """
        DELETE cs FROM content.course_skill cs
        INNER JOIN content.course c ON cs.course_id = c.id
        INNER JOIN temp_course_urls t ON c.url = t.url
    """;
    jdbcTemplate.update(sql);
}

이 쿼리는 세 개의 테이블을 JOIN한다.

course_skill (삭제 대상)
course (연결 매개)
temp_course_urls (필터 조건)

임시 테이블에 있는 URL에 해당하는 Course와 Skill의 매핑 테이블을 한 번의 쿼리로 삭제한다.

5. URL → ID 매핑 조회 (④)

  
private Map<String, Long> getUrlToIdMapFromTemporaryTable() {
    String sql = """
        SELECT c.url, c.id
        FROM content.course c
        INNER JOIN temp_course_urls t ON c.url = t.url
    """;

    return jdbcTemplate.query(sql, rs -> {
        Map<String, Long> map = new HashMap<>();
        while (rs.next()) {
            map.put(rs.getString("url"), rs.getLong("id"));
        }
        return map;
    });
}

Upsert가 완료된 후, 각 Course의 ID를 알아야 course_skill 테이블에 삽입할 수 있다. 임시 테이블과 JOIN해서 필요한 Course의 URL과 ID를 한 번에 가져온다.

이 매핑 정보는 다음 단계에서 course_skill 관계를 생성할 때 사용된다.

6. 새로운 Skill 관계 일괄 삽입 (⑤)

  
private record CourseSkillId(Long courseId, Long skillId) { }

private void bulkInsertCourseSkills(List<Course> courses, Map<Course, Long> courseIdMap) {
    List<CourseSkillId> courseSkillIds = new ArrayList<>();
    for (Course course : courses) {
        Long courseId = courseIdMap.get(course);
        for (var skill : course.skills()) {
            courseSkillIds.add(new CourseSkillId(courseId, skill.id().id()));
        }
    }

    if (courseSkillIds.isEmpty()) return;

    String sql = "INSERT IGNORE INTO content.course_skill (course_id, skill_id) VALUES (?, ?)";

    jdbcTemplate.batchUpdate(sql, courseSkillIds, 1000, (ps, courseSkillId) -> {
        ps.setLong(1, courseSkillId.courseId());
        ps.setLong(2, courseSkillId.skillId());
    });
}

모든 Course-Skill 조합을 CourseSkillId 리스트로 만든 후, 한 번에 삽입한다. INSERT IGNORE를 통해 중복된 조합이 있을 경우 무시하고 진행한다.

7. Temporary Table 정리 (⑥)

  
private void dropTemporaryTable() {
    jdbcTemplate.execute("DROP TEMPORARY TABLE IF EXISTS temp_course_urls");
}

작업이 완료되면 임시 테이블을 명시적으로 삭제한다.

V. 성능 비교

Before: JPA saveAll()

1,000개 Course × 5개 Skill 기준

SELECT (존재 확인): 1,000번
INSERT/UPDATE (Course): 1,000번
SELECT (기존 관계 조회): 1,000번
DELETE (기존 관계 삭제): 1,000번
INSERT (새로운 관계): 5,000번

총 쿼리 수: 9,000번

After: Temporary Table + Batch

1,000개 Course × 5개 Skill 기준

Batch INSERT/UPDATE (Course): 1번 (1,000개 묶음)
CREATE TEMPORARY TABLE: 1번
Batch INSERT (임시 테이블): 1번 (1,000개 묶음)
DELETE (JOIN 활용): 1번
SELECT (ID 매핑): 1번
Batch INSERT (course_skill): 1번 (5,000개 묶음)
DROP TEMPORARY TABLE: 1번

총 쿼리 수: 7번

VI. 참고

Framework, Spring