[스프링] - findByIN절, jdbc batchUpdate (bulk Insert)로 쿼리 개선

📍 기존 코드와 문제점

 

1. 문제 상황 

사용자가 학습하는 전체 리스트를 업데이트 해줘야하는데 이 때 대략 통신이 5초이상 걸린다는 것을 알게됐다.

 

2. 현재 코드 흐름

    public String updateCardWeakSound(Long userId){
        //card weaksound 테이블 해당 userId 행 전부 삭제
        cardWeakSoundRepository.deleteByUserId(userId);

        List<Card> cardList = getCardListWithoutSentence();
        List<Long> phonemeList = getPhonemeList(userId);

        cardList.forEach(card -> {
            List<Long> phonemes = card.getPhonemesMap();
            if(!Collections.disjoint(phonemes, phonemeList)){
                cardWeakSoundRepository.save(new CardWeakSound(userId ,card.getId()));
            }
        });

        return "카드 취약음 갱신 성공";
    }

    protected List<Card> getCardListWithoutSentence(){
        List<Card> cardList = new ArrayList<>();

        for(int i = 5; i <= 31; i++){
            cardList.addAll(cardRepository.findAllByCategoryId(Long.valueOf(i)));
        }

        return cardList;
    }

    protected List<Long> getPhonemeList(Long userId){
        List<UserWeakSound> userWeakSoundList = userWeakSoundRepository.findAllByUserId(userId);
        List<Long> phonemeList = new ArrayList<>();

        userWeakSoundList.forEach(userWeakSound -> {
            phonemeList.add(userWeakSound.getUserPhoneme());
        });

        return phonemeList;
    }

 

1. updateCardWeakSound가 호출

2. [deleteByUserId] : card_weaksound table에서 userId에 해당하는 행을 전부 삭제

3. [getCardListWithoutSentence] : card table에서 cardList를 불러옴

4. [getPhonemeList] : user_weaksound table에서 userId에 해당하는 userWeakSound List를 가져온다

5. [updateCardWeakList] : card_weaksound table에 조건이 맞으면 insert한다

 

모두 총 4번 DB와 통신한다.

 

3. 시간 측정

 

요청 시 응답까지 총 시간

Postman으로 측정해 보았을 때 평균적으로 5초 ~ 6초 정도 소요됐다.

 

 

 

각 DB와 통신할 때마다 걸린 시간

getCardListWithoutSentence와 updateCardWeakList에서 많은 시간을 사용하는 것을 볼 수 있다.

 

4. 결론

getCardListWithoutSentence와 updateCardWeakList에서 개선이 필요하다.

 

 

---

📍 getCardListWithoutSentence 해결

 

1. 현재 코드

    protected List<Card> getCardListWithoutSentence(){
        List<Card> cardList = new ArrayList<>();

        for(int i = 5; i <= 31; i++){
            cardList.addAll(cardRepository.findAllByCategoryId(Long.valueOf(i)));
        }

        return cardList;
    }

 

categoryId마다 card  table에 요청을 보내고 있다.

즉 거의 30번 select 요청을 하는 것이다

 

2. 해결 방안

 

DB에 한번의 요청으로 내가 원하는 값을 받아 올 수 있도록 IN을 사용했다.

IN : SELECT * FROM employee WHERE id IN (1,2,3...)

 

 

2. 변경 된 코드

service 코드

    protected List<Card> getCardListWithoutSentence(){
        List<Long> categoryIds = Arrays.asList(
                5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L,
                14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L,
                23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L
        );
        List<Card> cardList = cardRepository.findByCategoryIdIn(categoryIds);

        return cardList;
    }

 

 

repository에 추가한 코드

List<Card> findByCategoryIdIn(List<Long> categoryIds);

 

요청을 딱 한번만 보내고 내가 원하는 cardList를 받아올 수 있다

 

 

4. 시간

 

⏰ 285ms → 21ms 으로 개선된 것을 볼 수 있다.

 

 

 

---

📍updateCardWeakList 해결

1. 기존코드

        cardList.forEach(card -> {
            List<Long> phonemes = card.getPhonemesMap();
            if(!Collections.disjoint(phonemes, phonemeList)){
                cardWeakSoundRepository.save(new CardWeakSound(userId ,card.getId()));
            }
        });

 

매번 save를 보내는 걸 볼 수 있다.
한번에 몇백개씩 요청을 보내므로 여기서 시간이 가장 많이 소요된다.

 

시도해 본 해결 방법

• jpa의 saveAll
• jpa의 batch insert 
• jdbc의 bulkUpdate

 

2.  JPA의 saveAll

saveAll은 save와 다르게 1개의 Trasaction으로 save()를 여러번 호출한다.
save를 여러번 호출하는 것을 동일하다.

 

변경 된 코드

    public String updateCardWeakSound(Long userId){
        //card weaksound 테이블 해당 userId 행 전부 삭제
        cardWeakSoundRepository.deleteByUserId(userId);
        
        List<Card> cardList = getCardListWithoutSentence();
        List<Long> phonemeList = getPhonemeList(userId);

        List<CardWeakSound> cardWeakSoundList = new ArrayList<>();
        cardList.forEach(card -> {
            List<Long> phonemes = card.getPhonemesMap();
            if(!Collections.disjoint(phonemes, phonemeList)){
                cardWeakSoundList.add(new CardWeakSound(userId ,card.getId()));
            }
        });

        cardWeakSoundRepository.saveAll(cardWeakSoundList);

        return "카드 취약음 갱신 성공";
    }

 

saveAll 을 사용해도 실행시간에 큰 차이가 있지 않았다.

 

실행시간

 

⏰ saveAll에서 여전히 3초 이상 소요되는 모습

 

 

3. Jpa의 batchInsert

bulkInsert란 ? 

 insert를 각각 보내는 것이 아니라 위의 예시처럼 한개의 쿼리로 여러 insert 요청을 한방에 보내는 것

 

insert into table (attribute1, attribute2) values 
					(1, 2), 
                                        (1, 2), 
                                        (1, 2), 
                                        (1, 2)

 

 

단건으로 요청을 너무 많이 보내는 게 시간의 원인이라고 생각하여 한번에 삽입하는 bulk insert를 사용하고자 했으나

jpa + mysql은 id 생성을 @GeneratedValue(strategy = GenerationType.IDENTITY)을 사용하기 때문에

insert을 해야 id를 알 수 있으므로 jpa의 batch insert는 사용할 수 없다

 

batch insert는 반드시 삽입 전에 id를 알아야하기 때문

 

 

4. JDBC의 batchUpdate

Jdbc template에서 사용하는 batchUpdate사용하여 bulk Insert를 해보자

 

application.properties에 ?rewriteBatchedStatements=true를 추가해줘야한다

spring.datasource.url=jdbc:mysql://{URL}/test?rewriteBatchedStatements=true

 

변경 후 코드

jdbcTemplate 을 이용한 repository

@Repository
@Slf4j
@RequiredArgsConstructor
public class BulkRepository {
    private final JdbcTemplate jdbcTemplate;
    @PersistenceContext
    private EntityManager em;

    public void saveAll(List<CardWeakSound> cardWeakSoundList){
        String sql = "INSERT INTO card_weaksound (user_id, card_id) VALUES (?, ?)";

        jdbcTemplate.batchUpdate(sql,
                cardWeakSoundList,
                cardWeakSoundList.size(),
                (PreparedStatement ps, CardWeakSound cardWeakSound) -> {
                    ps.setLong(1, cardWeakSound.getUserId());
                    ps.setLong(2, cardWeakSound.getCardId());
                });

        em.clear();
    }
}

 

시간

 

⏰  3434ms -> 11ms로 매우 시간이 줄었다.

 

그런데 이 때 delete하는 부분의 시간이 매우 증가했다.

영속성 문제인 듯하다..(확실하지않음)

계속 찾아보고 공부해봤는데 아직 명확한 이유는 모르겠다

 

그래서 delete까지 jdbc를 이용해서 진행하였다.

@Repository
@Slf4j
@RequiredArgsConstructor
public class BulkRepository {
    private final JdbcTemplate jdbcTemplate;

    public void deleteAllByUserId(Long userId){
        String sql = "DELETE FROM card_weaksound WHERE user_id = ?";

        int count = jdbcTemplate.update(sql, userId);
        log.info("[Delete] : " + count);
    }
}

 

추가로 jdbc로 넣었을 때 영속성을 초기화 해주는 코드도 추가하였다.

@Repository
@Slf4j
@RequiredArgsConstructor
public class BulkRepository {
    private final JdbcTemplate jdbcTemplate;
    @PersistenceContext
    private EntityManager em;

    public void saveAll(List<CardWeakSound> cardWeakSoundList){
        String sql = "INSERT INTO card_weaksound (user_id, card_id) VALUES (?, ?)";

        jdbcTemplate.batchUpdate(sql,
                cardWeakSoundList,
                cardWeakSoundList.size(),
                (PreparedStatement ps, CardWeakSound cardWeakSound) -> {
                    ps.setLong(1, cardWeakSound.getUserId());
                    ps.setLong(2, cardWeakSound.getCardId());
                });
		
        //영속성 초기화
        em.clear();
    }

    public void deleteAllByUserId(Long userId){
        String sql = "DELETE FROM card_weaksound WHERE user_id = ?";

        jdbcTemplate.update(sql, userId);
    }
}

 

 

---

📍 변경 후 시간

 

각 DB와 통신할 때마다 걸린 시간

실행 시간이 모두 매우 감소한 것을 볼 수 있다.

 

 

요청 시 응답까지 총 시간

⏰ 5000ms -> 141ms로 감소하였다.

 

 

 

---

📍 요약

전체 코드

service 코드

@Service
@RequiredArgsConstructor
@Slf4j
public class CardWeakSoundService {
    private final BulkRepository bulkRepository;
    private final CardRepository cardRepository;
    private final UserWeakSoundRepository userWeakSoundRepository;
    /**
     * 취약음 갱신 시 user weaksound table update
     * @param userId
     * @return
     */
    @Transactional
    public String updateCardWeakSound(Long userId){
        //card weaksound 테이블 해당 userId 행 전부 삭제
        bulkRepository.deleteAllByUserId(userId);

        List<Card> cardList = getCardListWithoutSentence();

        List<Long> phonemeList = getPhonemeList(userId);

        List<CardWeakSound> cardWeakSoundList = new ArrayList<>();
        cardList.forEach(card -> {
            List<Long> phonemes = card.getPhonemesMap();
            if(!Collections.disjoint(phonemes, phonemeList)){
                cardWeakSoundList.add(new CardWeakSound(userId ,card.getId()));
            }
        });
        bulkRepository.saveAll(cardWeakSoundList);

        return "카드 취약음 갱신 성공";
    }

    protected List<Card> getCardListWithoutSentence(){
        List<Long> categoryIds = Arrays.asList(
                5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L,
                14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L,
                23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L
        );
        List<Card> cardList = cardRepository.findByCategoryIdIn(categoryIds);

        return cardList;
    }

    protected List<Long> getPhonemeList(Long userId){
        List<UserWeakSound> userWeakSoundList = userWeakSoundRepository.findAllByUserId(userId);
        List<Long> phonemeList = new ArrayList<>();

        userWeakSoundList.forEach(userWeakSound -> {
            phonemeList.add(userWeakSound.getUserPhoneme());
        });

        return phonemeList;
    }
}

 

repository 코드

@Repository
@Slf4j
@RequiredArgsConstructor
public class BulkRepository {
    private final JdbcTemplate jdbcTemplate;
    @PersistenceContext
    private EntityManager em;

    public void saveAll(List<CardWeakSound> cardWeakSoundList){
        String sql = "INSERT INTO card_weaksound (user_id, card_id) VALUES (?, ?)";

        jdbcTemplate.batchUpdate(sql,
                cardWeakSoundList,
                cardWeakSoundList.size(),
                (PreparedStatement ps, CardWeakSound cardWeakSound) -> {
                    ps.setLong(1, cardWeakSound.getUserId());
                    ps.setLong(2, cardWeakSound.getCardId());
                });

        em.clear();
    }

    public void deleteAllByUserId(Long userId){
        String sql = "DELETE FROM card_weaksound WHERE user_id = ?";

        jdbcTemplate.update(sql, userId);
    }
}

 

1. 여러 개를 검색해서 하나의 리스트를 받고자 할 때는 findByIn을 사용하면 좋다 (단 검색할 In이 너무 많아도 효율이 떨어짐)

2. mysql + jpa를 사용하면 jpa의 batch insert를 사용할 수 없으므로 (@id generate 방식을 바꾸지 않는 이상) jdbc의 batchUpdate를 사용하면 bulkInsert를 할 수 있다. 이 때 영속성 문제를 유의해두어야함

 

 

 

---

📍 느낀점

통신을 너무 많이하는 게 항상 시간을 많이 소요하는 것 같다.

그래도 항상 찾아보면 개선할 방법이 있는게 개발의 즐거운 점인 것 중 하나인 것 같다.