[Spring JPA] 대량의 데이터를 삭제할 때 batch delete query

JPA에서 대량의 데이터를 삭제할 때 주의할 점이 있습니다. 결론은 @Query 어노테이션을 사용하여 직접 삭제 쿼리를 작성한다.

 

예제

의존 관리는 Gradle를 사용하고 코드 간결성을 위해 lombok을, 테스트 프레임워크로 Spock을 사용하였다.

 

build.gradle

apply plugin: 'java'
apply plugin: 'groovy'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'

dependencies {
    compile('org.springframework.boot:spring-boot-starter-data-jpa')
    compile('org.springframework.boot:spring-boot-starter-web')
    runtime('com.h2database:h2')
    compileOnly('org.projectlombok:lombok')
    testCompile('org.springframework.boot:spring-boot-starter-test')
    testCompile('org.spockframework:spock-core:1.1-groovy-2.4')
    testCompile('org.spockframework:spock-spring:1.1-groovy-2.4')
}

application.yml 에 설정값을 추가한다.

spring:
  jpa:
    show-sql: true

사용할 엔티티 클래스는 Customer, Shop, Item이다.

// Customer.java
@Entity
@Getter
@NoArgsConstructor
public class Customer {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    public Customer(String name) {
        this.name = name;
    }
}

// Shop.java
@Getter
@NoArgsConstructor
@Entity
public class Shop {

    @Id
    @GeneratedValue
    private Long id;

    private String name;
    private String address;

    @OneToMany(mappedBy = "shop", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Item> items = new ArrayList<>();

    public Shop(String name, String address) {
        this.name = name;
        this.address = address;
    }

    public void addItem(Item item){
        if(this.items == null){
            this.items = new ArrayList<>();
        }

        this.items.add(item);
        item.updateShop(this);
    }
}

// Item.java
@Getter
@NoArgsConstructor
@Entity
public class Item {

    @Id
    @GeneratedValue
    private Long id;

    private String name;
    private long price;

    @ManyToOne
    private Shop shop;

    public Item(String name, long price) {
        this.name = name;
        this.price = price;
    }

    public void updateShop(Shop shop){
        this.shop = shop;
    }
}

이 엔티티 클래스를 다룬 JpaRepository를 구현한 repository를 생성하겠습니다.

// CustomerRepository.java
public interface CustomerRepository extends JpaRepository<Customer, Long>{

    @Modifying
    @Transactional
    long deleteByIdIn(List<Long> ids);

    @Transactional
    @Modifying
    @Query("delete from Customer c where c.id in :ids")
    void deleteAllByIdInQuery(@Param("ids") List<Long> ids);
}

// ShopRepository.java
public interface ShopRepository extends JpaRepository<Shop, Long> {

    @Transactional
    @Modifying
    long deleteAllByIdIn(List<Long> ids);

    @Transactional
    @Modifying
    @Query("delete from Shop s where s.id in :ids")
    void deleteAllByIdInQuery(@Param("ids") List<Long> ids);
}

// ItemRepository.java
public interface ItemRepository extends JpaRepository<Item, Long> {

    @Transactional
    @Modifying
    @Query("delete from Item i where i.shop.id in :ids")
    void deleteAllByIdInQuery(@Param("ids") List<Long> ids);
}

첫번째 메서드인 deleteAllByIdIn은 JpaRepository에서 제공하는 delete 메서드를 활용한 것이다.

두번째 메서드인 deleteAllByIdInQuery는 @Query를 사용하여 직접 delete 쿼리를 사용한 것이다.

 

1. 다른 엔티티와 관계가 없는 엔티티 삭제

다른 엔티티와 관계가 없는 Customer 엔티티 삭제 기능을 테스트한 예제이다.

@SpringBootTest
class CustomerRepositoryTest extends Specification {

    @Autowired
    private CustomerRepository customerRepository;
    
    def "Customer in delete" () {
        given: // 100개의 데이터를 DB에 insert
        for (int i = 0; i < 100; i++) {
            customerRepository.save(new Customer(i + "님"))
        }
        
        when: // 3개의 ID 조건으로 delete
        customerRepository.deleteByIdIn(Arrays.asList(1L, 2L, 3L))
        
        then:
        println "======THEN====="
        customerRepository.findAll().size() == 97
    }
}

Hibernate: insert into customer (id, name) values (null, ?)
Hibernate: insert into customer (id, name) values (null, ?)
Hibernate: insert into customer (id, name) values (null, ?)
...
Hibernate: insert into customer (id, name) values (null, ?)
Hibernate: select customer0_.id as id1_0_, customer0_.name as name2_0_ from customer customer0_ where customer0_.id in (?, ?, ?)
Hibernate: delete from customer where id = ?
Hibernate: delete from customer where id = ?
Hibernate: delete from customer where id = ?
=====THEN=====

in 으로 조회하는 쿼리가 처음으로 실행된다.

id별로 각각 delete가 실행된다.

 

public abstract class AbstractJpaQuery implements RepositoryQuery {
    // ... 생략
    
    @Nullable
    public Object execute(Object[] parameters) {
        return this.doExecute(this.getExecution(), parameters);
    }

    @Nullable
    private Object doExecute(JpaQueryExecution execution, Object[] values) {
        JpaParametersParameterAccessor accessor = new JpaParametersParameterAccessor(this.method.getParameters(), values);
        Object result = execution.execute(this, accessor);
        ResultProcessor withDynamicProjection = this.method.getResultProcessor().withDynamicProjection(accessor);
        return withDynamicProjection.processResult(result, new AbstractJpaQuery.TupleConverter(withDynamicProjection.getReturnedType()));
    }
    // ... 생략
}

public abstract class JpaQueryExecution {
    private static final ConversionService CONVERSION_SERVICE;

    public JpaQueryExecution() {
    }
    
    // ...생략
    
    static class DeleteExecution extends JpaQueryExecution {
        private final EntityManager em;

        public DeleteExecution(EntityManager em) {
            this.em = em;
        }

        protected Object doExecute(AbstractJpaQuery jpaQuery, JpaParametersParameterAccessor accessor) {
            Query query = jpaQuery.createQuery(accessor);
            List<?> resultList = query.getResultList();
            Iterator var5 = resultList.iterator();

            while(var5.hasNext()) {
                Object o = var5.next();
                this.em.remove(o);
            }

            return jpaQuery.getQueryMethod().isCollectionQuery() ? resultList : resultList.size();
        }
    }
    // ...생략
}

• jpaQuery.createQuery(values)의 결과로 select ~ from Customer where ~ 쿼리가 생성된다.
• for loop를 돌면서 1건씩 삭제한다.

 

JpaRepository에서 제공하는 deleteByXXX 등의 메서드는 먼저 조회하고 그 결과로 얻은 엔티티를 1건씩 삭제한다.

2. 관계가 있는 엔티티 삭제

@SpringBootTest
class ShopRepositoryTest extends Specification {

    @Autowired
    private ShopRepository shopRepository;
    
    @Autowired
    private ItemRepository itemRepository;
    
    def setup() {
        for (long i = 0; i <= 2; i++) {
             SHOP_ID_LIST.add(i)
        }
    }
    
    def cleanup() {
        println "======== Clean All ========="
        itemRepository.deleteAll()
        shopRepository.deleteAll()
    }
    
    def "SpringDataJPA에서 제공하는 예약어를 통해 삭제한다 - 부모&자식" () {
        given:
        createShopAndItem()

        when:
        shopRepository.deleteAllByIdIn(SHOP_ID_LIST)

        then:
        shopRepository.findAll().size() == 8
    }

    private void createShop() {
        for (int i = 0; i < 10; i++) { 
            shopRepository.save(new Shop("우아한서점" + i, "우아한 동네" + i))
        }

        println "=======End Create Shop======="
    }

    private void createShopAndItem() {
        for (int i = 0; i < 10; i++) {
            Shop shop = new Shop("우아한서점" + i, "우아한 동네" + i)

            for (int j = 0; j < 3; j++) {
                shop.addItem(new Item("IT책" + j, j * 10000))
            }

            shopRepository.save(shop)
        }

        println "=======End Create Shop & Item======="
    }
}

 

Hibernate: select shop0_.id as id1_2_, shop0_.address as address2_2_, shop0_.name as name3_2_ from shop shop0_ where shop0_.id in (?, ?)
Hibernate: select item0_.shop_id as shop_id4_1_0_, item0_.id as id1_1_0, ... from item item0_ where item0_.shop_id = ?
Hibernate: select item0_.shop_id as shop_id4_1_0_, item0_.id as id1_1_0, ... from item item0_ where item0_.shop_id = ?
Hibernate: delete from item where id = ?
Hibernate: delete from item where id = ?
Hibernate: delete from item where id = ?
Hibernate: delete from shop where id = ?
Hibernate: delete from item where id = ?
Hibernate: delete from item where id = ?
Hibernate: delete from item where id = ?
Hibernate: delete from shop where id = ?

• in 으로 조회하는 쿼리가 처음 실행된다.
• shop id 별로 item을 조회한다.
• 조회된 item을 1건씩 삭제한다.
• 조회된 shop을 1건씩 삭제한다.

 

SpringDataJpa에서 deleteByXXX 등의 메소드 사용시

• 삭제 대상들을 전부 조회하는 쿼리가 1번 발생한다.
• 삭제 대상들은 1건씩 삭제 된다.
• cascade = CascadeType.DELETE으로 하위 엔티티와 관계가 맺어진 경우 하위 엔티티들도 1건씩 삭제가 진행된다.

해결책

직접 범위 조건의 삭제 쿼리를 작성하면 된다.

    @Transactional
    @Modifying
    @Query("delete from Customer c where c.id in :ids")
    void deleteAllByIdInQuery(@Param("ids") List<Long> ids);

이를 실행하면 

Hibernate: delete from customer where id in (?, ?, ?)

만약 Shop과 Item 같이 서로 연관관계가 있는 경우에는 Shop만 삭제시 에러가 발생할 수 있습니다.

Item을 먼저 삭제 후, Shop을 삭제하면된다.

itemRepository.deleteAllByIdInQuery(SHOP_ID_LIST)
shopRepository.deleteAllByIdInQuery(SHOP_ID_LIST)

Hibernate: delete from item where shop_id in (?, ?, ?)
Hibernate: delete from shop where id in (?, ?, ?)

 

 출처 

https://jojoldu.tistory.com/235

JPA에서 대량의 데이터를 삭제할때 주의해야할 점