ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • Delete 배치 처리 (feat. deleteAllInbatch() vs batchUpdate() + rewriteBatchedStatements=true vs in 절)
      Spring 2025. 4. 8. 20:58

       

      이전에 bulk insert에 대해서 찾아보았다. 

      ↓

      https://dodop-blog.tistory.com/498

       

      Spring JPA의 save() vs saveAll() vs bulk insert() (feat. db client)

      이번에 면허 재검증 프로세스를 구성하면서 면허 검증 로깅에 관한 작업을 구성하게 되었다. 면허 검증에는 배치로 검증하는 과정이 있기 떄문에, 로깅도 한번에 저장되어 DB 팀에게 어느정도

      dodop-blog.tistory.com

       

      그렇다면 다량의 데이터를 delete 처리할 때는 어떨까? 

       

       

       

       

       

      1. jpa의 delete() 메서드 활용 

       

      가장 먼저 for문을 이용해서 delete()메서드를 연속호출하여 인서트를 수행해보자.

       

          @Test
    fun delete() {
        val users = userRepository.findAllByOrderByIdAsc(Pageable.ofSize(1000))
        val start = System.currentTimeMillis()
        for (user in users) {
            // SimpleJpaRepository -> SessionImpl -> DefaultMergeEventListener
            userRepository.delete(user)
        }
        val end = System.currentTimeMillis()
        println("Time taken to delete 1000 users: ${end - start} ms")
    }

       

      실행시간 경과는 다음과 같다. 

      Time taken to delete 1000 users: 12606 ms

       

      실행된 쿼리는 다음과 같이 개별로 나간다. 

      autoCommit을 0으로 만들고 commit 후 autocommit을 1로 바꾸는 작업을 반복하는 것을 확인할 수 있다. 

      Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] SET autocommit=0 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] select ue1_0.id,ue1_0.created_at,ue1_0.email,ue1_0.name,ue1_0.phone,ue1_0.status,ue1_0.updated_at from user ue1_0 where ue1_0.id=7860 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] delete from user where id=7860 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] COMMIT [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 3, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] SET autocommit=1 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] SET autocommit=0 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] select ue1_0.id,ue1_0.created_at,ue1_0.email,ue1_0.name,ue1_0.phone,ue1_0.status,ue1_0.updated_at from user ue1_0 where ue1_0.id=7861 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] delete from user where id=7861 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] COMMIT [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 3, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 19:42:24 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 0, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 19:42:24 KST 2025 INFO: [QUERY] SET autocommit=1 [Created on: Tue Apr 08 19:42:24 KST 2025, duration: 1, connection-id: 191, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
...

       

      실제 호출 로직을 보면다음과 같다. 

      @Repository
@Transactional(readOnly = true)
public class SimpleJpaRepository<T, ID> implements JpaRepositoryImplementation<T, ID> {

	@Override
	@Transactional
	@SuppressWarnings("unchecked")
	public void delete(T entity) {

		Assert.notNull(entity, ENTITY_MUST_NOT_BE_NULL);

		if (entityInformation.isNew(entity)) {
			return;
		}

		if (entityManager.contains(entity)) {
			entityManager.remove(entity);
			return;
		}

		Class<?> type = ProxyUtils.getUserClass(entity);

		// if the entity to be deleted doesn't exist, delete is a NOOP
		T existing = (T) entityManager.find(type, entityInformation.getId(entity));
		if (existing != null) {
			entityManager.remove(entityManager.merge(entity));
		}
	}
}

       

       

      save()와 동일하게 내부에서 트랜잭션을 잡는다.

       

       

       

      2. jpa의 deleteAll() 메서드 활용 

       

      이번엔 deleteAll() 메서드를 활용해보자. 

          @Test
    fun deleteAll() {
        val users = userRepository.findAllByOrderByIdAsc(Pageable.ofSize(1000))
        val start = System.currentTimeMillis()
        // SimpleJpaRepository -> SessionImpl -> DefaultMergeEventListener
        userRepository.deleteAll(users)
        val end = System.currentTimeMillis()
        println("Time taken to delete 1000 users: ${end - start} ms")
    }

       

      실행결과는 다음과 같다. 

      Time taken to delete 1000 users: 5099 ms

       

       

       

      실행된 쿼리는 다음과 같다. 

      Tue Apr 08 19:50:23 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 0, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 19:50:23 KST 2025 INFO: [QUERY] delete from user where id=8260 [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 1, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 0, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [QUERY] delete from user where id=8261 [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 2, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 0, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [QUERY] delete from user where id=8262 [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 2, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 0, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [QUERY] delete from user where id=8263 [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 1, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 0, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 19:50:23 KST 2025 INFO: [QUERY] delete from user where id=8264 [Created on: Tue Apr 08 19:50:23 KST 2025, duration: 2, connection-id: 311, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
...
Tue Apr 08 19:50:26 KST 2025 INFO: [QUERY] COMMIT [Created on: Tue Apr 08 19:50:26 KST 2025, duration: 7, connection-id: 311, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 19:50:26 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:50:26 KST 2025, duration: 0, connection-id: 311, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 19:50:26 KST 2025 INFO: [QUERY] SET autocommit=1 [Created on: Tue Apr 08 19:50:26 KST 2025, duration: 1, connection-id: 311, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 19:50:26 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 19:50:26 KST 2025, duration: 0, connection-id: 311, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]

       

       

      deleteAll의 메서드는 다음과 같이 내부에서 transactional을 생성하여 커넥션 안에서 반복 요청 하기 때문에 개별 delete() 보다 빠른 결과를 낸다.

      	@Override
	@Transactional
	public void deleteAll() {

		for (T element : findAll()) {
			delete(element);
		}
	}

       

       

       

      3. jpa의 deleteAllInBatch()

       

      이번엔 jpa의 deleteAllInBatch()기능을 사용해보자. 

      Spring Data JPA의 JpaRepository에 있는 메서드로, 내부적으로는 JPQL 대신 직접 SQL을 사용해서 한 번에 삭제한다. 

      • JPA가 엔티티들을 식별하고, ID 기준으로 delete SQL을 생성
        • JPA 컨텍스트를 사용하지 않고 직접 delete SQL 생성
        • 따라서 JPA 컨텍스트에 영향이 없음 (flush, clear 불필요) 
      • 다음과 같이 단일 쿼리로 처리 (Hibernate 기준)
      • 파라미터 바인딩이 엔티티 기반으로 자동으로 됨 
      delete from user where id in (1, 2, 3, 4, 5);
       

       

          @Test
    fun deleteAllInBatch() {
        val users = userRepository.findAllByOrderByIdAsc(Pageable.ofSize(1000))
        val start = System.currentTimeMillis()
        // SimpleJpaRepository -> SessionImpl -> DefaultMergeEventListener
        userRepository.deleteAllInBatch(users)
        val end = System.currentTimeMillis()
        println("Time taken to delete 1000 users: ${end - start} ms")
    }

       

      실행 결과는 다음과 같다. 

      Time taken to delete 1000 users: 384 ms

       

       

      실행된 쿼리를 보면 where 절의 조건문으로 한번에 날라가는 것을 확인할 수 있다. 

      WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
Tue Apr 08 20:44:55 KST 2025 INFO: [QUERY] select ue1_0.id,ue1_0.created_at,ue1_0.email,ue1_0.name,ue1_0.phone,ue1_0.status,ue1_0.updated_at from user ue1_0 order by ue1_0.id limit 1000 [Created on: Tue Apr 08 20:44:55 KST 2025, duration: 3, connection-id: 511, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:44:55 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:44:55 KST 2025, duration: 4, connection-id: 511, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:44:55 KST 2025 INFO: [QUERY] SET autocommit=0 [Created on: Tue Apr 08 20:44:55 KST 2025, duration: 2, connection-id: 511, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:44:55 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:44:55 KST 2025, duration: 0, connection-id: 511, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:44:56 KST 2025 INFO: [QUERY] delete ue1_0 from user ue1_0 where ue1_0.id=12260 or ue1_0.id=12261 or ue1_0.id=12262 or ue1_0.id=12263 or ue1_0.id=12264 or ue1_0.id=12265 or ue1_0.id=12266 or ue1_0.id=12267 or ue1_0.id=12268 or ue1_0.id=12269 or ue1_0.id=12270 or ue1_0.id=12271 or ue1_0.id=12272 or ue1_0.id=12273 or ue1_0.id=12274 or ue1_0.id=12275 or ue1_0.id=12276 or ue1_0.id=12277 or ue1_0.id=12278 or ue1_0.id=12279 or ue1_0.id=12280 or ue1_0.id=12281 or ue1_0.id=12282 or ue1_0.id=12283 or ue1_0.id=12284 or ue1_0.id=12285 or ue1_0.id=12286 or ue1_0.id=12287 or ue1_0.id=12288 or ue1_0.id=12289 or ue1_0.id=12290 or ue1_0.id=12291 or ue1_0.id=12292 or ue1_0.id=12293 or ue1_0.id=12294 or ue1_0.id=12295 or ue1_0.id=12296 or ue1_0.id=12297 or ue1_0.id=12298 or ue1_0.id=12299 or ue1_0.id=12300 or ue1_0.id=12301 or ue1_0.id=12302 or ue1_0.id=12303 or ue1_0.id=12304 or ue1_0.id=12305 or ue1_0.id=12306 or ue1_0.id=12307 or ue1_0.id=12308 or ue1_0.id=12309 or ue1_0.id=12310 or ue1_0.id=12311 or ue1_0.id=12312 or ue1_0.id=12313 or ue1_0.id=12314 or ue1_0.id=12315 or ue1_0.id=12316 or ue1_0.id=12317 or ue1_0.id=12318 or ue1_0.id=12319 or ue1_0.id=12320 or ue1_0.id=12321 or ue1_0.id=12322 or ue1_0.id=12323 or ue1_0.id=12324 or ue1_0.id=12325 or ue1_0.id=12326 or ue1_0.id=12327 or ue1_0.id=12328 or ue1_0.id=12329 or ue1_0.id=12330 or ue1_0.id=12331 or ue1_0.id=12332 or ue1_0.id=12333 or ue1_0.id=12334 or ue1_0.id=12335 or ue1_0.id=12336 or ue1_0.id=12337 or ue1_0.id=12338 or ue1_0.id=12339 or ue1_0.id=12340 or ue1_0.id=12341 or ue1_0.id=12342 or ue1_0.id=12343 or ue1_0.id=12344 or ue1_0.id=12345 or ue1_0.id=12346 or ue1_0.id=12347 or ue1_0.id=12348 or ue1_0.id=12349 or ue1_0.id=12350 or ue1_0.id=12351 or ue1_0.id=12352 or ue1_0.id=12353 or ue1_0.id=12354 or ue1_0.id=12355 or ue1_0.id=12356 or ue1_0.id=12357 or ue1_0.id=12358 or ue1_0.id=12359 or ue1_0.id=12360 or ue1_0.id=12361 or ue1_0.id=12362 or ue1_0.id=12363 or ue1_0.id=12364 or ue1_0.id=12365 or ue1_0.id=12366 or ue1_0.id=12367 or ue1_0.id=12368 or ue1_0.id=12369 or ue1_0.id=12370 or ue1_0.id=12371  ... (truncated) [Created on: Tue Apr 08 20:44:56 KST 2025, duration: 47, connection-id: 511, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 20:44:56 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:44:56 KST 2025, duration: 0, connection-id: 511, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 20:44:56 KST 2025 INFO: [QUERY] COMMIT [Created on: Tue Apr 08 20:44:56 KST 2025, duration: 4, connection-id: 511, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:44:56 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:44:56 KST 2025, duration: 0, connection-id: 511, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:44:56 KST 2025 INFO: [QUERY] SET autocommit=1 [Created on: Tue Apr 08 20:44:56 KST 2025, duration: 1, connection-id: 511, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:44:56 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:44:56 KST 2025, duration: 0, connection-id: 511, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]

       

       

      실제 구현부는 다음과 같다. 

      @Repository
@Transactional(readOnly = true)
public class SimpleJpaRepository<T, ID> implements JpaRepositoryImplementation<T, ID> {
	
    
	@Override
	@Transactional
	public void deleteAllInBatch() {

		Query query = entityManager.createQuery(getDeleteAllQueryString());

		applyQueryHints(query);

		query.executeUpdate();
	}
    
    private void applyQueryHints(Query query) {

		if (metadata == null) {
			return;
		}

		getQueryHints().withFetchGraphs(entityManager).forEach(query::setHint);
		applyComment(metadata, query::setHint);
	}
    
    @Override
	@Transactional
	public void deleteAllInBatch(Iterable<T> entities) {

		Assert.notNull(entities, ENTITIES_MUST_NOT_BE_NULL);

		if (!entities.iterator().hasNext()) {
			return;
		}

		applyAndBind(getQueryString(DELETE_ALL_QUERY_STRING, entityInformation.getEntityName()), entities, entityManager)
				.executeUpdate();
	}
    
}


public abstract class QueryUtils {

    
	public static <T> Query applyAndBind(String queryString, Iterable<T> entities, EntityManager entityManager) {

		Assert.notNull(queryString, "Querystring must not be null");
		Assert.notNull(entities, "Iterable of entities must not be null");
		Assert.notNull(entityManager, "EntityManager must not be null");

		Iterator<T> iterator = entities.iterator();

		if (!iterator.hasNext()) {
			return entityManager.createQuery(queryString);
		}

		String alias = detectAlias(queryString);
		StringBuilder builder = new StringBuilder(queryString);
		builder.append(" where");

		int i = 0;

		while (iterator.hasNext()) {

			iterator.next();

			builder.append(String.format(" %s = ?%d", alias, ++i));

			if (iterator.hasNext()) {
				builder.append(" or");
			}
		}

		Query query = entityManager.createQuery(builder.toString());

		iterator = entities.iterator();
		i = 0;

		while (iterator.hasNext()) {
			query.setParameter(++i, iterator.next());
		}

		return query;
	}

}

       

       

       

       

      4. JdbcTemplate의 batchUpdate 활용 

       

      이번엔 jdbcTemplate을 사용해서 직접 delete 구문을 구현해보자.

          @Transactional
    override fun deleteAllWithJdbcTemplate(users: List<UserEntity>) {
        if (users.isEmpty()) return

        // 방법 1. BatchPreparedStatementSetter 사용 (타입 안정성 높음)
        val sql = "DELETE FROM user WHERE id = ?"
        jdbcTemplate.batchUpdate(sql, object : BatchPreparedStatementSetter {
            override fun setValues(ps: PreparedStatement, i: Int) {
                ps.setLong(1, users[i].id)
            }
            override fun getBatchSize(): Int = users.size
        })

        // 방법 2. 파라미터가 간단한 경우
//        val ids = users.map { arrayOf<Any>(it) }
//        jdbcTemplate.batchUpdate(sql, ids)

    }

       

       

          @Test
    fun deleteAllByJdbcTemplate() {
        val users = userRepository.findAllByOrderByIdAsc(Pageable.ofSize(1000))
        val start = System.currentTimeMillis()
        userRepository.deleteAllWithJdbcTemplate(users)
        val end = System.currentTimeMillis()
        println("Time taken to delete 1000 users: ${end - start} ms")
    }

       

       

      실행 결과는 다음과 같다. 

      Time taken to delete 1000 users: 2258 ms
      WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
Tue Apr 08 20:01:53 KST 2025 INFO: [QUERY] select ue1_0.id,ue1_0.created_at,ue1_0.email,ue1_0.name,ue1_0.phone,ue1_0.status,ue1_0.updated_at from user ue1_0 order by ue1_0.id limit 1000 [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 2, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:01:53 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 4, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:01:53 KST 2025 INFO: [QUERY] SET autocommit=0 [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 1, connection-id: 351, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:01:53 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 0, connection-id: 351, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
2025-04-08T20:01:53.581+09:00 DEBUG 4876 --- [    Test worker] o.s.jdbc.core.JdbcTemplate               : Executing SQL batch update [DELETE FROM user WHERE id = ?]
2025-04-08T20:01:53.582+09:00 DEBUG 4876 --- [    Test worker] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [DELETE FROM user WHERE id = ?]
2025-04-08T20:01:53.585+09:00 DEBUG 4876 --- [    Test worker] o.s.jdbc.support.JdbcUtils               : JDBC driver supports batch updates
Tue Apr 08 20:01:53 KST 2025 INFO: [QUERY] DELETE FROM user WHERE id = 9260 [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 2, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:53 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 0, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:53 KST 2025 INFO: [QUERY] DELETE FROM user WHERE id = 9261 [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 2, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:53 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 0, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:53 KST 2025 INFO: [QUERY] DELETE FROM user WHERE id = 9262 [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 1, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:53 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 0, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:53 KST 2025 INFO: [QUERY] DELETE FROM user WHERE id = 9263 [Created on: Tue Apr 08 20:01:53 KST 2025, duration: 1, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
...
Tue Apr 08 20:01:55 KST 2025 INFO: [QUERY] DELETE FROM user WHERE id = 10259 [Created on: Tue Apr 08 20:01:55 KST 2025, duration: 1, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:55 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:55 KST 2025, duration: 0, connection-id: 351, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:01:55 KST 2025 INFO: [QUERY] COMMIT [Created on: Tue Apr 08 20:01:55 KST 2025, duration: 6, connection-id: 351, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:01:55 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:55 KST 2025, duration: 0, connection-id: 351, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:01:55 KST 2025 INFO: [QUERY] SET autocommit=1 [Created on: Tue Apr 08 20:01:55 KST 2025, duration: 1, connection-id: 351, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:01:55 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:01:55 KST 2025, duration: 0, connection-id: 351, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]

       

      영속성 컨텍스트에서 삭제하는 로직이 생략되기 떄문에 더 빠른 속도가 나왔다. 

       

       

       

      5. JdbcTemplate의 batchUpdate와 rewriteBatchedStatements=true을 함께 사용 

       

      이번엔 rewriteBatchedStatements 옵션을 활용하여 배치 삭제 기능을 구현해보자. 

      rewriteBatchedStatements=true는 MySQL JDBC 드라이버의 성능 최적화 옵션 중 하나로, JdbcTemplate.batchUpdate() 사용 시 실제 배치 insert 성능에 큰 영향을 미칠 수 있다. 

       

      rewriteBatchedStatements 옵션은 MySQL JDBC 드라이버가 addBatch()로 쌓은 SQL들을 하나의 multi-row로 재작성해주는 기능이다. 즉, DB로 전송되는 쿼리 수 자체가 줄어들어 성능이 폭발적으로 향상될 수 있다. 

       

      다음과 같이 jdbc url에 옵션을 설정해준다. 

          url: jdbc:mysql://localhost:33306/bulk_insert?profileSQL=true&rewriteBatchedStatements=true

       

       

          @Test
    fun deleteAllByJdbcTemplateWithRewriteBatchedStatementsOption() {
        val users = userRepository.findAllByOrderByIdAsc(Pageable.ofSize(1000))
        val start = System.currentTimeMillis()
        userRepository.deleteAllWithJdbcTemplate(users)
        val end = System.currentTimeMillis()
        println("Time taken to delete 1000 users: ${end - start} ms")
    }

       

       

      실행 결과는 다음과 같다. 

      Time taken to delete 1000 users: 230 ms

       

       

      시간이 정말 많이 줄었다! 

      실행된 쿼리를 보자. 

      WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
Tue Apr 08 20:07:18 KST 2025 INFO: [QUERY] select ue1_0.id,ue1_0.created_at,ue1_0.email,ue1_0.name,ue1_0.phone,ue1_0.status,ue1_0.updated_at from user ue1_0 order by ue1_0.id limit 1000 [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 2, connection-id: 391, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:07:18 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 9, connection-id: 391, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:07:18 KST 2025 INFO: [QUERY] SET autocommit=0 [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 1, connection-id: 391, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:07:18 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 0, connection-id: 391, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
2025-04-08T20:07:18.449+09:00 DEBUG 4994 --- [    Test worker] o.s.jdbc.core.JdbcTemplate               : Executing SQL batch update [DELETE FROM user WHERE id = ?]
2025-04-08T20:07:18.451+09:00 DEBUG 4994 --- [    Test worker] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [DELETE FROM user WHERE id = ?]
2025-04-08T20:07:18.454+09:00 DEBUG 4994 --- [    Test worker] o.s.jdbc.support.JdbcUtils               : JDBC driver supports batch updates
Tue Apr 08 20:07:18 KST 2025 INFO: [QUERY] DELETE FROM user WHERE id = 10260;DELETE FROM user WHERE id = 10261;DELETE FROM user WHERE id = 10262;DELETE FROM user WHERE id = 10263;DELETE FROM user WHERE id = 10264;DELETE FROM user WHERE id = 10265;DELETE FROM user WHERE id = 10266;DELETE FROM user WHERE id = 10267;DELETE FROM user WHERE id = 10268;DELETE FROM user WHERE id = 10269;DELETE FROM user WHERE id = 10270;DELETE FROM user WHERE id = 10271;DELETE FROM user WHERE id = 10272;DELETE FROM user WHERE id = 10273;DELETE FROM user WHERE id = 10274;DELETE FROM user WHERE id = 10275;DELETE FROM user WHERE id = 10276;DELETE FROM user WHERE id = 10277;DELETE FROM user WHERE id = 10278;DELETE FROM user WHERE id = 10279;DELETE FROM user WHERE id = 10280;DELETE FROM user WHERE id = 10281;DELETE FROM user WHERE id = 10282;DELETE FROM user WHERE id = 10283;DELETE FROM user WHERE id = 10284;DELETE FROM user WHERE id = 10285;DELETE FROM user WHERE id = 10286;DELETE FROM user WHERE id = 10287;DELETE FROM user WHERE id = 10288;DELETE FROM user WHERE id = 10289;DELETE FROM user WHERE id = 10290;DELETE FROM user WHERE id = 10291;DELETE FROM user WHERE id = 10292;DELETE FROM user WHERE id = 10293;DELETE FROM user WHERE id = 10294;DELETE FROM user WHERE id = 10295;DELETE FROM user WHERE id = 10296;DELETE FROM user WHERE id = 10297;DELETE FROM user WHERE id = 10298;DELETE FROM user WHERE id = 10299;DELETE FROM user WHERE id = 10300;DELETE FROM user WHERE id = 10301;DELETE FROM user WHERE id = 10302;DELETE FROM user WHERE id = 10303;DELETE FROM user WHERE id = 10304;DELETE FROM user WHERE id = 10305;DELETE FROM user WHERE id = 10306;DELETE FROM user WHERE id = 10307;DELETE FROM user WHERE id = 10308;DELETE FROM user WHERE id = 10309;DELETE FROM user WHERE id = 10310;DELETE FROM user WHERE id = 10311;DELETE FROM user WHERE id = 10312;DELETE FROM user WHERE id = 10313;DELETE FROM user WHERE id = 10314;DELETE FROM user WHERE id = 10315;DELETE FROM user WHERE id = 10316;DELETE FROM user WHERE id = 10317;DELETE FROM user WHERE id = 10318;DELETE FROM user WHERE id = 10319;DELETE F ... (truncated) [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 5, connection-id: 391, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:07:18 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 163, connection-id: 391, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
Tue Apr 08 20:07:18 KST 2025 INFO: [QUERY] COMMIT [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 12, connection-id: 391, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:07:18 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 0, connection-id: 391, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:07:18 KST 2025 INFO: [QUERY] SET autocommit=1 [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 1, connection-id: 391, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:07:18 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:07:18 KST 2025, duration: 0, connection-id: 391, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]

       

       

      mysql 에서 배치 쿼리를 드라이버가 내부적으로 단일 쿼리로 리라이트해서 성능을 올린다. 

       

       

      단건 쿼리 요청 시  rewriteBatchedStatements = true 인 경우 
      • SQL이 개별 실행됨
      • 네트워크 왕복이 많아 성능 저하 가능성
      • 트랜잭션 처리 비용이 증가할 수 있음
      • 인덱스를 많이 타는 경우, 개별 DELETE가 성능 이슈를 일으킬 수도 있음
      • 네트워크 트래픽 절감 (SQL이 하나만 나감)
      • 트랜잭션 비용 감소

       

       

       

       

      6. in 절을 사용하여 삭제 처리

      이번엔 in 절을 사용하는 경우와 비교해보자. 

       

       

          @Transactional
    override fun deleteAllWithInClause(users: List<UserEntity>) {
        if (users.isEmpty()) return

        val ids = users.map { it.id }
        val placeholders = ids.joinToString(",") { "?" }
        val sql = "DELETE FROM user WHERE id IN ($placeholders)"
        jdbcTemplate.update(sql, *ids.toTypedArray())
    }
          @Test
    fun deleteAllWithInClause() {
        val users = userRepository.findAllByOrderByIdAsc(Pageable.ofSize(1000))
        val start = System.currentTimeMillis()
        userRepository.deleteAllWithInClause(users)
        val end = System.currentTimeMillis()
        println("Time taken to delete 1000 users: ${end - start} ms")
    }

       

       

      실행 결과는 다음과 같다. 

      Time taken to delete 1000 users: 43 ms

       

       

      실행된 쿼리는 다음과 같다. 

      in 절을 이용해서 쿼리가 한번에 수행된 것을 확인할 수 있다. 

      WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
Tue Apr 08 20:13:42 KST 2025 INFO: [QUERY] select ue1_0.id,ue1_0.created_at,ue1_0.email,ue1_0.name,ue1_0.phone,ue1_0.status,ue1_0.updated_at from user ue1_0 order by ue1_0.id limit 1000 [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 2, connection-id: 471, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:13:42 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 3, connection-id: 471, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)]
Tue Apr 08 20:13:42 KST 2025 INFO: [QUERY] SET autocommit=0 [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 1, connection-id: 471, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:13:42 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 0, connection-id: 471, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
2025-04-08T20:13:42.211+09:00 DEBUG 5131 --- [    Test worker] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
2025-04-08T20:13:42.211+09:00 DEBUG 5131 --- [    Test worker] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [DELETE FROM user WHERE id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]
Tue Apr 08 20:13:42 KST 2025 INFO: [QUERY] DELETE FROM user WHERE id IN (11260,11261,11262,11263,11264,11265,11266,11267,11268,11269,11270,11271,11272,11273,11274,11275,11276,11277,11278,11279,11280,11281,11282,11283,11284,11285,11286,11287,11288,11289,11290,11291,11292,11293,11294,11295,11296,11297,11298,11299,11300,11301,11302,11303,11304,11305,11306,11307,11308,11309,11310,11311,11312,11313,11314,11315,11316,11317,11318,11319,11320,11321,11322,11323,11324,11325,11326,11327,11328,11329,11330,11331,11332,11333,11334,11335,11336,11337,11338,11339,11340,11341,11342,11343,11344,11345,11346,11347,11348,11349,11350,11351,11352,11353,11354,11355,11356,11357,11358,11359,11360,11361,11362,11363,11364,11365,11366,11367,11368,11369,11370,11371,11372,11373,11374,11375,11376,11377,11378,11379,11380,11381,11382,11383,11384,11385,11386,11387,11388,11389,11390,11391,11392,11393,11394,11395,11396,11397,11398,11399,11400,11401,11402,11403,11404,11405,11406,11407,11408,11409,11410,11411,11412,11413,11414,11415,11416,11417,11418,11419,11420,11421,11422,11423,11424,11425,11426,11427,11428,11429,11430,11431,11432,11433,11434,11435,11436,11437,11438,11439,11440,11441,11442,11443,11444,11445,11446,11447,11448,11449,11450,11451,11452,11453,11454,11455,11456,11457,11458,11459,11460,11461,11462,11463,11464,11465,11466,11467,11468,11469,11470,11471,11472,11473,11474,11475,11476,11477,11478,11479,11480,11481,11482,11483,11484,11485,11486,11487,11488,11489,11490,11491,11492,11493,11494,11495,11496,11497,11498,11499,11500,11501,11502,11503,11504,11505,11506,11507,11508,11509,11510,11511,11512,11513,11514,11515,11516,11517,11518,11519,11520,11521,11522,11523,11524,11525,11526,11527,11528,11529,11530,11531,11532,11533,11534,11535,11536,11537,11538,11539,11540,11541,11542,11543,11544,11545,11546,11547,11548,11549,11550,11551,11552,11553,11554,11555,11556,11557,11558,11559,11560,11561,11562,11563,11564,11565,11566,11567,11568,11569,11570,11571,11572,11573,11574,11575,11576,11577,11578,11579,11580,11581,11582,11583,11584,11585,11586,11587,11588,11589,11590,11591,11592,11593,11594,11595,11 ... (truncated) [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 11, connection-id: 471, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 20:13:42 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 0, connection-id: 471, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)]
Tue Apr 08 20:13:42 KST 2025 INFO: [QUERY] COMMIT [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 4, connection-id: 471, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:13:42 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 0, connection-id: 471, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:378)]
Tue Apr 08 20:13:42 KST 2025 INFO: [QUERY] SET autocommit=1 [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 1, connection-id: 471, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]
Tue Apr 08 20:13:42 KST 2025 INFO: [FETCH]  [Created on: Tue Apr 08 20:13:42 KST 2025, duration: 0, connection-id: 471, statement-id: -1, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:402)]

       

       

      그렇다면 rewrteBatchedStatements를 사용했을 때와 어떻게 다를까?

       

      방식 설명 예시
      IN 절 여러 ID를 한 쿼리에 넣는 방식 DELETE FROM user WHERE id IN (1,2,3,...,N)
      rewriteBatchedStatements 여러 개의 DELETE FROM user WHERE id = ?를 한 번에 연결해서 서버로 전송 DELETE FROM user WHERE id=1; DELETE FROM user WHERE id=2; ...

       

      하지만 in절에 너무 많은 데이터를 넣으면 성능이 저하된다는 DB 팀의 피드백을 받았다. 

      왜일까? 

      IN 절에 너무 많은 데이터를 넣으면 다음의 문제점이 있다.

      • SQL 파싱 시간이 증가
        • 문자열 자체가 커짐 
      • 쿼리 로그 처리 비용이 증가
        • 쿼리 기록에 부하를 줌 
      • 쿼리 파싱 비용이 증가 
        • Mysql 등은 SQL 쿼리를 파싱하고 계획을 수립해야 하는데, 값이 많을수록 파싱 트리 구조가 커지고 느려짐 
        • 쿼리가 달라지면 매번 새로운 계획을 수립하므로 실행 계획 캐시에도 불리함 
      • 메모리 사용 증가 
        • Mysql은 in 절의 항목을 임시 배열이나 리스트로 메모리에 올리는데, 값이 수천개 이상이라면 서버 메모리 소비가 증가하며, GC를 유발할 가능성이 커짐 
      • 인덱스 효율 저하 
        • in 절은 일반적으로 인덱스를 잘타지만, 너무 많은 id 가 있는 경우 쿼리 옵티마이저가 인덱스 사용을 포기하거나 비효율 적인 방식으로 처리할 가능성이 있음 
      • 실행 계획 비효율 
        • 일부 DB는 in 절이 매우 큰 경우 중첩 루프 조인 방식처럼 처리하여 느려질 수 있음 
        • 복잡한 JOIN이나 서브 쿼리가 함께 있는 경우, in 조건으로 인해 Nested Loop Join 최적화가 깨짐 
      • 네트워크 / 드라이버의 한계 
        • JDBC 드라이버 자체에도 쿼리 길이나 파라미터 수 제한이 있어 너무 많은 in 절은 드라이버에서 예외가 발생할 수 있음 

      성능비교를 해보면 다음과 같다. 

      항목 IN 절 rewriteBatchedStatements=true
      쿼리 수 1 N (하지만 한 번에 전송됨)
      네트워크 전송량 작음 큼 (쿼리 텍스트가 N개)
      DB 엔진 실행 계획 수 1개 N개 (단순 반복)
      쿼리 파싱/컴파일 부담 낮음 높음 (내부적으로는 여러 개로 처리됨)
      JDBC 처리 속도 빠름 (1회 실행) 빠름 (네트워크 I/O 줄어듦)
      파라미터 개수 한계 있음 (MySQL은 약 65,535개까지 가능, 하지만 실무선 1000~3000 정도 권장) 없음 (나눠서 보냄)
      쿼리 캐싱 재사용 불리함 (파라미터 수 달라지면 다른 쿼리로 취급) 유리함 (PreparedStatement 반복)
      단순 삭제 100~1000건 정도일 때 빠름 빠름
      대규모 삭제 (수천~수만 건) 쿼리 사이즈 너무 커질 수 있음 → 나눠야 함 안정적이고 성능 좋음

       

       

      사용 조건을 비교해보면 다음과 같다. 

      상황 추천 방식
      1,000건 이하 삭제 IN 절 (간단하고 빠름)
      ID 수가 유동적이거나 많음 (5,000 이상) rewriteBatchedStatements (안정적이고 scalable)
      개별 실패 추적이 필요함 rewriteBatchedStatements
      파라미터 수가 동적으로 계속 바뀜 rewriteBatchedStatements
      쿼리 캐시를 잘 활용하고 싶음 rewriteBatchedStatements

       

       

       

       

      JPA의 deleteAllInBatch와도 함꼐 비교하면 다음과 같다. 

       

      항목 deleteAllInBatch() (JPA) IN 절 직접 작성 (JDBC) batchUpdate() + rewriteBatchedStatements=true
      실행 SQL DELETE FROM table WHERE id IN (...) (JPA가 생성) 직접 구성: DELETE FROM table WHERE id IN (...) 여러 DELETE FROM table WHERE id = ?;
      쿼리 수 1 1 N (한 요청에 N 쿼리)
      파라미터 바인딩 자동 (엔티티 기반) 수동 (? 바인딩) 수동 (PreparedStatement 여러 개)
      JDBC 네트워크 전송량 낮음 낮음 보통~많음 (쿼리 텍스트가 큼)
      JDBC 최적화 적용 가능 여부 ❌ 불가 ✅ 가능 ✅ 가능 (rewriteBatchedStatements)
      성능 (소량) ✅ 빠름 ✅ 빠름 ✅ 빠름
      성능 (대량) ⚠️ 느려짐 (IN 절 길이 한계) ⚠️ 느려짐 (같음) ✅ 상대적으로 안정적
      JPA 컨텍스트 영향 ❌ 영향 없음 (flush, clear 불필요) ❌ 없음 ❌ 없음
      트랜잭션 처리 ✅ JPA @Transactional ✅ JDBC transaction ✅ JDBC transaction
      개별 row 에러 추적 ❌ 불가 ❌ 불가 ✅ 가능 (batch index 기준 추적 가능)
      복잡 쿼리 대응력 ❌ 제한적 (JPA 메서드 한정) ✅ 유연 ✅ 유연
      쿼리 캐시 재사용 ❌ 불리 (파라미터 수 달라지면 캐시 안 됨) ❌ 불리 ✅ 유리 (PreparedStatement 재사용)

       

       

      상황 추천 방식
      500건 이하 삭제 deleteAllInBatch() 또는 IN 절 직접
      1,000 ~ 3,000건 IN 절 또는 batchUpdate() 가능. 성능 비슷
      3,000건 이상 or 유동적 수량 ✅ batchUpdate() + rewriteBatchedStatements
      고급 튜닝/에러 추적/로그 구분 필요 ✅ batchUpdate()
      "그냥 빠르게 지우고 싶다" deleteAllInBatch() (간단한 JPA 코드로 끝)

       

       

       

      끝 ✨✨✨✨✨✨✨✨✨

       

      참고한 사이트 

      - https://monny.tistory.com/198

       

      Batch Update 2편

      고민을 너무 많이 했나봅니다. 글이 너무 길어져서 지난 글에 이어 2편으로 다시 돌아왔습니다. 지난 글에서는 메모리와 JPA에 대한 고민을 위주로 글을 썼었더라면 이번 편에서는 데이터베이스

      monny.tistory.com

      - https://twosky.tistory.com/62

       

      [Spring] JdbcTemplate을 통해 Bulk Insert 하기

      Bulk Insert란? 아래와 같이 여러 개의 데이터를 하나의 Insert 쿼리로 삽입하는 것은 Bulk Insert라고 한다. 개별 Insert의 경우 쿼리를 던지고 응답을 받은 후에야 다음 쿼리를 전달하기 때문에 지연이

      twosky.tistory.com

      - https://hyos-dev-log.tistory.com/1

       

      JdbcTemplate의 Batch Insert 구현시, rewriteBatchedStatements 옵션을 true로 설정하여 성능 문제 해결

      Spring Batch Application과 JPA, JDBC를 함께 사용하여 Batch 작업을 처리해야 하는 상황이 있었습니다. 이때 MySQL Connector/J (JDBC Reference) - Configuration Properties for Connector/J 에서 제공하는 기능들을 몰랐기에,

      hyos-dev-log.tistory.com

      - https://7357.tistory.com/316

       

      삽질 기록 (15) 한 번에 여러 Entity를 저장해야한다면? 벌크벌크 BulkInsert

      보통 삽질 기록은 한 시간 이상 고민한 것만 올리는데 이건 그냥 시간 남고 심심해서 올림 ㅎ 이번 프로젝트에서 크롤링 시 데이터를 150~300개 가량 한 번에 저장하게 되는데, 엄청나게 대단한 용

      7357.tistory.com

      - https://hyos-dev-log.tistory.com/1

       

      JdbcTemplate의 Batch Insert 구현시, rewriteBatchedStatements 옵션을 true로 설정하여 성능 문제 해결

      Spring Batch Application과 JPA, JDBC를 함께 사용하여 Batch 작업을 처리해야 하는 상황이 있었습니다. 이때 MySQL Connector/J (JDBC Reference) - Configuration Properties for Connector/J 에서 제공하는 기능들을 몰랐기에,

      hyos-dev-log.tistory.com

      - https://dkswnkk.tistory.com/757

       

      [JPA] deleteAll(), deleteAllInBatch(), deleteInBatch() 정리

      개요Hibernate(JPA)에서는 레코드를 삭제할 수 있는 아래의 다양한 메서드들을 지원한다.delete(),deleteById()deleteAll(), deleteAllById()deleteInBatch()deleteAllInBatch(), deleteAllByIdInBatch()다양한 메서드를 제공하는

      dkswnkk.tistory.com

       

       

      'Spring' 카테고리의 다른 글

      WebClientResponseException의 responseBodyAsString에서 한글이 깨지는 문제 해결  (0) 2025.05.04
      Spring JPA의 save() vs saveAll() vs bulk insert() (feat. db client)  (0) 2025.04.08
      SpringData JPA를 사용하는 환경에서 multi-database (feat. master/slave구분, querydsl) 구성하기  (1) 2025.01.21
      Grpc Service의 호출오류가 Grpc Client 헬스체크 실패를 야기하는 문제 해결  (0) 2024.11.19
      동시 삭제 요청으로 인한 StaleObjectStateException 해결 - redisson lock 적용기 (feat. Spring AOP, applicationEventListener)  (2) 2024.10.01

      관련글 관련글 더보기

    Designed by Tistory.

    티스토리툴바