💡이 글은 현재 리팩토링을 진행하고 있는 전시회 기록 서비스 배치 작업의 Write 부분의 성능 개선에 대한 내용입니다. 초반부 간단한 배경 소개부터 발생하는 쿼리 수를 줄이기 위한 고민들을 적어보았습니다. Jpa를 사용하고, MySQL에서 ID 채번 전략으로 Auto_Increment를 선택했을 때, Bulk Insert 작업을 하는 방법에 대한 내용입니다.
현재 개선하고 있는 배치 작업에서는 최대 40,000건 (전시회 20,000 / 전시 공간 20,000)의 데이터를 매일 갱신하고 삽입할 수 있습니다 (현재 외부 API로는 최대 1,000건씩). 두 방향으로 나누어 작업을 진행하는데, 앞/뒤로 6개월씩, 그리고 과거 1년치를 조회하여 데이터를 불러옵니다. 과거의 데이터는 주로 DB에 없는 상태이기 때문에 초록 박스에 있는 기간의 전시회는 주로 삽입을 진행합니다.
반면, 빨간 박스에 있는 기간의 전시회는 현재를 기준으로 하루씩 움직이기 때문에 새로 추가되는 전시회 조금과 대부분의 기존 데이터 갱신 작업이 이루어집니다. 기존 데이터 갱신 역시 전시회 정보가 바뀌는 경우가 많지 않기 때문에 정말 데이터가 바뀌어서 갱신하는 경우는 적습니다. 그저 단순 확인 작업이 될 가능성이 높습니다.
정리하자면 배치는 시간 기준 앞, 뒤로 진행되며, 초록 박스의 작업은 대부분 삽입을, 빨간 박스는 조금의 삽입/수정 작업과 대부분의 경우 변경 확인 작업을 수행합니다.
사실 초록 박스의 작업은 초기 데이터 세팅과 비슷한 작업이기도 해서 단기간에 끝날 작업이라 빨간 박스가 배치 작업의 주된 목적입니다. 하지만, Insert 작업을 공부하는 측면에서 함께 알아보고자 언급했습니다!
이번 글에서는 배치 작업에서 분리한 Writer가 수행하는 DB Write 작업에서의 개선을 다뤄보겠습니다.
0. 너무 많은 쿼리
배치에서는 외부 API로부터 가져온 데이터를 가공하고, DB를 갱신하는 작업을 수행합니다.
DB에 기존 데이터가 존재하면 Update를, 새로운 데이터면 Insert를 해야하는데, 너무 많은 쿼리가 발생한 것을 확인했습니다. 어떤 쿼리가 발생하는지 확인해보고, 개선할 수 있는 방법들을 살펴보겠습니다.
public void writePlace(List<PlaceDtoToWrite> placeDtoListToWrite) {
for (PlaceDtoToWrite placeDtoToWrite : placeDtoListToWrite) {
placeRepository.findBySeq(placeDtoToWrite.getSeq())
.ifPresentOrElse(
place -> updatePlace(place, placeDtoToWrite),
() -> insertPlace(placeDtoToWrite)
);
}
}
public void writeExhibition(List<ExhibitionDtoToWrite> exhibitionDtoListToWrite) {
for (ExhibitionDtoToWrite exhibitionDtoToWrite : exhibitionDtoListToWrite) {
exhibitionRepository.findByExhibitionSeq(exhibitionDtoToWrite.getExhibitionSeq())
.ifPresentOrElse(
exhibition -> updateExhibition(exhibition, exhibitionDtoToWrite),
() -> insertExhibition(exhibitionDtoToWrite)
);
}
}전시회 / 전시 공간을 조회하고, DB에 존재하면 Update, 존재하지 않으면 Insert를 진행하는 코드입니다.
현재 상태는 save()만 사용해도 Update가 실행되지만, 우선 해당 함수에서 하는 일을 명확히 확인하기 위해 나눴습니다.
아래 SimpleJpaRepository의 save 메소드에 나타난 것처럼 해당 엔티티가 새로운 엔티티라면 (isNew) persist를 진행하고, 기존에 존재하던 엔티티라면 Merge를 진행합니다.
@Transactional
@Override
public <S extends T> S save(S entity) {
Assert.notNull(entity, "Entity must not be null.");
if (entityInformation.isNew(entity)) {
em.persist(entity);
return entity;
} else {
return em.merge(entity);
}
}하지만, 이렇게 되면 전시회 및 전시 공간 데이터 수만큼의 Select 쿼리와 Insert/Update 쿼리가 발생합니다. 갱신할 데이터가 10000개씩 존재한다면 20000번의 Select와 Insert/Update 쿼리가 발생하는 셈입니다.
실제로 로그를 확인해보면, 엔티티를 찾기 위한 Select 1회와 엔티티가 존재하지 않는 경우 Insert 1회가 발생합니다. 위 코드처럼 Dirty Checking으로 데이터를 변경하고자 할 때에는 DB에 있지만 변경 사항이 없는 경우 Update 쿼리가 날라가지 않지만, 여전히 조회를 해야 합니다.
아래 이미지는 그라파나에서 확인한 약 400건의 데이터를 Insert/Update 하는 Write 함수의 실행 시간입니다.
Write 작업에 4.4초, 1.9초 가량 소요되었습니다. 400건인 점을 감안하면, 데이터가 많아질수록 DB 작업에 걸리는 시간이 더욱 길어질 것이므로 충분히 개선의 여지가 있다고 판단했습니다.
1. Upsert
MySQL에서는 Insert + Update를 위한 Upsert 문법을 사용할 수 있습니다. 데이터가 있을 때는 Update를, 없을 때는 Insert를 수행합니다. Unique 키를 기준으로 중복 발생 여부에 따라 Insert/Update를 결정합니다.
INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...)
ON DUPLICATE KEY UPDATE
컬럼1 = 값1, 컬럼2 = 값2, ...;이렇게 쿼리를 작성 Select 쿼리가 사라지고, Insert/Update 쿼리만 발생하여 1건 당 1개의 쿼리가 발생합니다.
INSERT INTO ~ 부분은 기존 INSERT 절과 같이 사용하면 되고, ON DUPLICATE KEY UPDATE ~는 Update 시 값을 어떻게 수정할 것인지를 적어줍니다.
@Modifying
@Query(value =
"INSERT INTO place(seq, name, address, tel, home_page, created_date, modified_date, status) " +
"VALUES(:seq, :name, :address, :tel, :homePage, NOW(), NOW(), 'ACTIVE') " +
"ON DUPLICATE KEY UPDATE " +
"name=VALUES(name), " +
"address=VALUES(address), " +
"tel=VALUES(tel), " +
"home_page=VALUES(home_page)",
nativeQuery = true)
void upsert(
@Param("seq") Integer seq,
@Param("name") String name,
@Param("address") String address,
@Param("tel") String tel,
@Param("homePage") String homePage
);이렇게 코드를 작성해주었고, UK인 Seq로 Insert를 시도하는데, Seq 중복이 발생하면 ON DUPLICATE KEY UPDATE 이후의 '컬럼=값'들로 Update 쿼리를 날려줍니다.
@Transactional
public void writePlace(List<PlaceDtoToWrite> placeDtoListToWrite) {
for (PlaceDtoToWrite placeDtoToWrite : placeDtoListToWrite) {
placeRepository.upsert(
placeDtoToWrite.getSeq(),
placeDtoToWrite.getName(),
placeDtoToWrite.getAddress(),
placeDtoToWrite.getTel(),
placeDtoToWrite.getHomePage()
);
}
}호출하는 부분에서도 Select, Insert, Update 부분이 사라지고, 새로 만든 upsert 함수 하나만 호출합니다.
추가로 Upsert 구현 중 UK 관련 내용에 대해 알아보았습니다.
1) PK? Unique?
ON DUPLICATE KEY UPDATE 뒤에 오는 컬럼이 PK로 설정되어 있어야 하는가?입니다.
위에서 살펴본 것처럼 PK보단 Unique함이 중요한 것이고, 사실 구문 이름이 "ON DUPLICATE KEY UPDATE"이기 때문에 중복을 검사할 수 있어야 합니다.
++) 전시회 및 전시 공간 고유 번호(seq)에 UK 걸기
이 부분을 생각하며 전시회 / 전시 공간 고유 번호인 seq에 UK를 걸면되겠다! 라고 생각하게 되었는데, seq에 UK를 거는 것이 당연하게도? 가져오는 이점이 더 있어서 upsert와 관련없지만 이에 관해 살펴보았습니다.
UK를 걸기 위해 "정말 고유한가?"를 먼저 생각해보았습니다. 외부 API를 호출하여 수만건 이상의 데이터를 수집하고 확인했을 때 seq가 겹치는 경우는 0인 경우밖에 없었습니다. 그마저도 seq가 0인 경우는 비어있는 값을 표기하기 위한 것이었습니다. 하지만, 이런 추측성 확신으로 uk를 걸기엔 찝찝함이 컸고, 문의를 남기고 확신을 얻었습니다.
seq가 고유 번호임을 확인받았기 때문에 UK를 걸어도 됨을 확신할 수 있게 되었습니다. seq를 가진 테이블은 exhibition과 place 두 개이며, 두 테이블 모두 사용자는 Select만 사용할 수 있습니다. MySQL은 UK를 걸면 고유 인덱스가 생기고, 인덱스는 조회 속도를 높여주나 Insert/Update/Delete의 속도는 되려 느리게 만드는데, 적어도 저희 프로젝트에서는 이에 대한 걱정이 없었습니다. 배치가 돌아갈 때만 쓰기 작업이 일어납니다.
정리하자면 seq 컬럼은 카디널리티가 매우 낮고, 고유하며, Select 비중이 압도적으로 높았기 때문에 인덱스를 걸기 최적의 상태입니다. 추가로 쓰기 작업이 배치 외에 없어서 큰 문제는 없겠지만, MySQL은 PK 또는 UK가 걸린 컬럼을 조건으로 수정 쿼리를 수행할 때 갭 락이나 넥스트 키 락이 아닌 레코드 락을 걸기 때문에 이 점도 마음에 들었습니다.
하지만, 로그와 함께 문제를 발견할 수 있었습니다.
아래는 현재 Upsert 코드의 로그로, 하나의 쿼리만 발생하는 것을 확인할 수 있습니다.
Upsert는 네이티브 쿼리로 날리는 것이기 때문에 변경 사항이 없더라도 dirty checking과 다르게 무조건 쿼리가 발생했습니다. 특히, 해당 프로젝트는 데이터의 변경이 매우 적은 편으로, Update 쿼리가 필요한 경우가 적기 때문에 불필요한 Update 쿼리를 줄이는 것이 중요했습니다.
2) 격리 수준과 락
바로 앞에서 살짝 언급한 내용인데, 쿼리에서 update할 대상을 찾기 위해 where 절에 어떤 컬럼을 넣느냐에 따라 발생하는 락의 종류가 달라집니다. Unique한 컬럼을 대상으로 update 쿼리를 수행하면 레코드 락을 걸게 됩니다. 반면, Unique하지 않은 컬럼을 대상으로 update를 수행하면 갭 락이나 넥스트 키 락을 걸게 되어 단 건 레코드보다 넓은 범위에 락이 걸리게 됩니다.
Unique한 컬럼이 아닌 경우 Phantom Read 방지를 위해 범위 락을 걸게 됩니다. Unique한 경우 중복된 값이 없기 때문에 레코드 락으로 충분하지만, 중복된 값이 있는 경우에는 레코드 사이에 잠금을 걸어야 새로운 Select 시 이상 현상이 발생하지 않습니다.
하지만, Select ... For Update처럼 락이 필요한 상태에서는 Phantom Read가 발생할 수 있습니다. MySQL은 MVCC를 위해 언두 로그를 사용하는데, 언두 레코드에는 락을 걸 수 없기 때문에 변경 전 데이터가 아닌 현재 데이터를 가져오게 됩니다.
이는 기본 격리 수준인 Repeatable Read에서 락을 거는 방식이며, 갭 락은 데드락 발생 가능성이 있습니다. 하지만, 한 단계 낮은 수준인 Read Committed를 사용하면 갭 락을 걸지 않고, 레코드 락을 걸게 됩니다.
2. Bulk Upsert
따라서 Bulk 연산을 도입하고자 했습니다. Bulk Upsert문을 생성해서 여러 쿼리를 하나로 보내는 방법입니다.
이게 가능할까..? 싶었는데 잘 생각해보니 가능할 것 같았습니다.
INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES
(값1, 값2, ...),
(값1, 값2, ...),
...
ON DUPLICATE KEY UPDATE
컬럼1 = 값1, 컬럼2 = 값2, ...;Bulk Insert처럼 Values에 여러 값을 주어 한 번의 쿼리에 해결하는 것입니다.
1) multi-values Upsert SQL
예) INSERT INTO place (seq, name, address, tel, home_page, created_date, modified_date, status)
VALUES (1, 'ABC','Seoul','010-xxx','www...', NOW(), NOW(), 'ACTIVE'),
(2, 'DEF','Busan','010-yyy','site...', NOW(), NOW(),'DELETE')
ON DUPLICATE KEY UPDATE
name=VALUES(name),
address=VALUES(address),
tel=VALUES(tel),
home_page=VALUES(home_page),
modified_date=NOW(),
status=VALUES(status)이제 이 쿼리를 어떻게 내 코드로 구현할 것인가를 고민할 차례입니다.
Bulk Upsert : Stack Over Flow 글을 참고하여 구현 방법들을 탐구했습니다. 주로 아래 두 가지 방식이 사용된다고 합니다.
- EntityManager Native Query
- JdbcTemplate 사용
1) EntityManager Native Query
Auto_increment 전략으로 ID(PK)를 채번하면 JPA에서 제공하는 기능으로 Bulk Insert를 사용하기 어렵습니다. 그 이유는 Insert 후 ID를 Entity 객체에 매핑해야 하지만, Bulk Insert를 사용하면 모든 Entity의 ID를 알 수 없기 때문입니다. 따라서 Insert된 Entity의 ID를 알기 위해선 건 당 Insert 쿼리가 실행될 수밖에 없습니다.
이러한 문제 떄문에 Jpa의 save, saveAll은 Bulk Insert가 불가능합니다. 그럼에도 Bulk 연산을 할 수 있는 두 가지 대표적인 방식에 대해 살펴보겠습니다.
첫번째 방법은 EntityManager로 네이티브 쿼리를 만들어 실행시키는 방식입니다. 저장할 데이터들을 받아서 하나의 쿼리로 만들고, 이를 실행시킵니다. 물론 청크 단위로 조절할 수 있지만, 손이 좀 많이 간다는 단점이 있습니다. 다소 날것의 방식이죠 ..
@RequiredArgsConstructor
@Repository
public class PlaceRepositoryCustomImpl implements PlaceRepositoryCustom {
private final EntityManager entityManager;
public void bulkUpsert(List<PlaceUpsertRequest> requestList) {
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO place (seq, name, address, tel, home_page, created_date, modified_date, status) VALUES ");
for (int i = 0; i < requestList.size(); i++) {
PlaceUpsertRequest dto = requestList.get(i);
if (i > 0) sql.append(",");
sql.append("(").append(dto.getSeq());
sql.append(", ").append(dto.getName());
sql.append(", ").append(dto.getAddress());
sql.append(", ").append(dto.getTel());
sql.append(", ").append(dto.getHomePage());
sql.append(", NOW(), NOW(), 'ACTIVE'");
sql.append(")");
}
sql.append(" ON DUPLICATE KEY UPDATE ");
sql.append(" name=VALUES(name),");
sql.append(" address=VALUES(address),");
sql.append(" tel=VALUES(tel),");
sql.append(" home_page=VALUES(home_page),");
sql.append(" modified_date=NOW(),");
sql.append(" status=VALUES(status)");
Query query = entityManager.createNativeQuery(sql.toString());
query.executeUpdate();
}
}위 방법은 Insert된 행에 대해 id가 몇 번인지, Update가 일어났는지는 JPA 엔티티로 알 수 없습니다. 네이티브 쿼리를 날렸기 때문인데, 지금 프로젝트의 작업에서는 DB 저장하고 작업이 끝나기 때문에 엔티티 정보를 알 필요가 없으니 괜찮은 방법입니다.
이렇게 코드를 작성하고 테스트를 해보았는데, 다음과 같은 에러가 발생했습니다.
[Request processing failed: org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException:
Space is not allowed after parameter prefix ':' [INSERT INTO place (seq, name, address, tel, home_page, ... ]홈페이지 Url에 http://~~같은 값이 들어가는데, ':' 콜론을 파라미터로 해석하여 파싱에러가 발생한 것입니다. 이 경우에는 문자열로 확신할 수 있도록 따옴표로 감싸주거나 파라미터 바인딩을 하여 해결할 수 있습니다.
먼저, 문자열로 감싸는 코드를 작성해서 seq를 제외한 문자열 컬럼들을 문자열로 감싼 후 쿼리를 날려봤습니다.
아까와 같은 문제는 해결되었지만, 임시 방편인만큼 보안과 매번 감싸는 코드를 작성해야 한다는 번거로움이 생깁니다. 또한, 구조가 잡히지 않아 실수하기 정말 좋은 방법이라는 생각이 들었습니다.
따라서 저는 파라미터 바인딩 방식을 선택했습니다.
public void bulkUpsert(List<PlaceUpsertRequest> requestList) {
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO place (seq, name, address, tel, home_page, created_date, modified_date, status) VALUES ");
for (int i = 0; i < requestList.size(); i++) {
if (i > 0) sql.append(",");
sql.append("(?, ?, ?, ?, ?, NOW(), NOW(), 'ACTIVE')");
}
sql.append(" ON DUPLICATE KEY UPDATE ");
sql.append(" name=VALUES(name),");
sql.append(" address=VALUES(address),");
sql.append(" tel=VALUES(tel),");
sql.append(" home_page=VALUES(home_page),");
sql.append(" modified_date=NOW(),");
sql.append(" status=VALUES(status)");
Query query = entityManager.createNativeQuery(sql.toString());
int paramIndex = 1;
for (PlaceUpsertRequest dto : requestList) {
query.setParameter(paramIndex++, dto.getSeq());
query.setParameter(paramIndex++, dto.getName());
query.setParameter(paramIndex++, dto.getAddress());
query.setParameter(paramIndex++, dto.getTel());
query.setParameter(paramIndex++, dto.getHomePage());
}
query.executeUpdate();
}'?'로 표현하고, 인덱스에 맞게 값을 setParameter로 바인딩하여 순서를 맞춰줄 수 있습니다.
아까와 같은 파싱 에러가 발생하지도 않고, 쿼리가 잘 날라가는 것을 확인할 수 있었습니다.
다만, 파라미터 인덱스를 수동으로 관리한다는 점과 청크 관리가 어렵다는 점이 문제로 다가왔고, 조금 더 편리하게 사용할 수 있는 JdbcTemplate를 사용하기로 했습니다.
2) JdbcTemplate
많은 경우에 JdbcTemplate를 사용하여 Bulk Insert 작업을 구현합니다. 저도 마찬가지로 JdbcTemplate를 사용하여 Bulk 처리하는 것으로 결정했습니다.
public void bulkUpsertJdbcTemplate(List<PlaceUpsertRequest> requestList) {
String sql = """
INSERT INTO place (seq, name, address, tel, home_page, created_date, modified_date, status)
VALUES (?, ?, ?, ?, ?, NOW(), NOW(), 'ACTIVE')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
address = VALUES(address),
tel = VALUES(tel),
home_page = VALUES(home_page),
modified_date = NOW(),
status = VALUES(status)
""";
jdbcTemplate.batchUpdate(sql,
requestList,
requestList.size(),
(ps, dto) -> {
ps.setLong(1, dto.getSeq());
ps.setString(2, dto.getName());
ps.setString(3, dto.getAddress());
ps.setString(4, dto.getTel());
ps.setString(5, dto.getHomePage());
}
);
}PreparedStatement를 직접 다루는 방식과, 위 코드처럼 람다식을 사용하는 방식이 있는데, 저는 가독성이 좋고 간결한 람다 방식으로 구현했습니다.
이렇게 Bulk 처리를 위한 두 가지 방법을 살펴봤고, 아래 표와 같이 정리해보았습니다.
| EntityManager + NativeQuery | JdbcTemplate 사용 | |
| 파라미터 바인딩 | 수동 인덱스 관리 | 명시적 바인딩 (ps.set~~) |
| 코드 가독성 | 보통 | 좋음 |
| 유연함 | 비교적 낮음 | 비교적 높음 |
| 쿼리 개수 | 동일 (모아서 처리) | 동일 (모아서 처리) |
3) FK가 있는 Exhibition 테이블
Place : Exhibition은 1:M 관계로, Exhibition은 Place_id를 FK로 갖습니다. 그래서 배치 작업을 구성할 때에도 Place를 먼저 Insert한 후 Exhibition을 했는데, 벌크 처리를 하면서 Place ID를 가져오기 힘들어졌다는 문제가 생겼습니다.
가장 단순무식하게 findBySeq으로 일일이 Select하여 Place를 가져오고, Exhibition Insert를 위해 사용해야되나 하는 생각이 먼저 들었습니다. 하지만, 이 경우도 비슷하게 IN절로 Select 쿼리를 날려 한 번에 여러 Place를 가져온 후 Map으로 구성하여 Exhibition Insert 시 사용하면 될 것 같았습니다.
IN절을 사용한 Select 쿼리에 대해서는 아래 큰 제목 3번에서 더 자세히 알아보겠습니다!
// 1번만 select: 전시회 수만큼 select 하지 않음!
List<Place> places = placeRepository.findBySeqIn(placeSeqList);
// Map<placeSeq, placeId>
Map<Long, Long> placeIdMap = places.stream()
.collect(Collectors.toMap(Place::getSeq, Place::getId)); @Transactional
public void writeExhibition(List<ExhibitionDtoToWrite> exhibitionDtoListToWrite, List<Integer> placeSeqList) {
List<Place> placeList = placeRepository.findBySeqIn(placeSeqList);
Map<Integer, Long> placeIdMap = placeList.stream()
.collect(Collectors.toMap(Place::getSeq, Place::getId));
for (ExhibitionDtoToWrite exhibitionDtoToWrite : exhibitionDtoListToWrite) {
Long placeId = placeIdMap.get(exhibitionDtoToWrite.getPlaceSeq());
exhibitionDtoToWrite.setPlaceId(placeId);
}
exhibitionRepository.bulkUpsertJdbcTemplate(exhibitionDtoListToWrite);
}이렇게 Select 쿼리를 한 번에 날리고 FK 매핑에 사용하도록 구성했습니다.
마찬가지로 그라파나에서 확인해 본 결과 각각 0.8초, 0.2초가 걸렸습니다.
400건의 데이터 wirte 작업 시 쿼리를 줄이는 것으로
4.4 -> 0.8초
1.9초 -> 0.19초
정도의 성능 향상을 보았습니다.
4) Bulk 연산 시 주의사항
JPA 사용 시 Bulk Insert를 하게 되면 그만큼의 데이터가 영속화 된 이후 flush하게 됩니다. 메모리가 충분하다면 문제가 없겠지만 너무 많은 데이터를 영속화 시키게 된다면 out of memory가 발생하게 됩니다. 따라서 적절한 개수만큼 나눠서 영속화 시키고 flush, clear을 반복해줘야 합니다.
JdbcTemplate를 사용할 때는 영속성 컨텍스트를 사용하지 않지만, 마찬가지로 주의해야 합니다. 제가 사용하고 있는 MySQL의 경우 전달하는 패킷의 크기가 제한되어 있습니다. 따라서 패킷의 크기가 정해진 값을 넘지 않도록 조절하여 쿼리를 만들어야 합니다.
이 글에서는 보기 쉽게끔 테스트 용으로 하나의 쿼리로 묶었지만, 실제로 배치 작업을 돌리는 것과 같이 데이터가 많은 작업을 할 때는 청크 단위로 나누어 쿼리를 실행하도록 구성해야 에러 없이 작업을 완료할 수 있습니다.
3. IN절과 인덱스
앞에서 전시회 데이터 삽입을 위해 전시 공간을 조회해야 했었고, 이를 IN절로 수행한 내용을 간단히 살펴봤습니다. 실행 계획을 살펴보았더니 IN절 안에 들어가는 seq의 수에 따라 조금의 차이가 있었습니다.
IN절 안에 100개(청크 단위 처리 개수)씩 들어가며 SELECT 쿼리를 수행합니다. 따라서 이 쿼리에 대한 실행 계획을 토대로 이야기 해보겠습니다.
EXPLAIN
SELECT * FROM exhibition
WHERE exhibition_seq IN ();쿼리는 위와 같고, exhibition_seq는 Unique 인덱스가 걸려있습니다.
데이터가 208개 존재하는 상황에서 모든 데이터를 조회하는 쿼리의 실행 계획을 살펴보았더니 위와 같이나타났습니다.
100개를 조회할 때도 filter될 뿐, 마찬가지였습니다.
이렇게 되면 인덱스를 타지 못하게 테이블 스캔을 하기 때문에 인덱스를 온전히 사용하지 못하게 됩니다. 실행 계획이 예상과 다르게 나타나는 이유는 MySQL 옵티마이저는 IN절에 들어간 값의 개수와 인덱스의 커버리지, 테이블의 전체 로우 수 등을 고려하여 가장 효율적인 실행 방법(인덱스 범위 스캔 vs 테이블 스캔)을 선택하는데, 현재 쿼리에는 IN절에 들어가는 검색 대상이 너무 많기 때문입니다.
IN절에 들어가는 대상이 많아지면 옵티마이저가 테이블 스캔을 하는 것이 더 효율적이라고 판단할 수 있습니다. exhibition_seq는 고유한 값일 뿐 사실 정렬이 큰 의미는 없고, 실제로 IN절 안에서도 뒤죽박죽 섞여 있기 때문에 더욱 그렇습니다.
IN절 안에 들어가는 seq의 수를 조금씩 줄여가며 확인해보니 40개 이하는 index를 타고 있었습니다. 하지만, 하나의 청크 당 40개로 쪼개기에는 너무 단위가 작다고 느껴져 쿼리 자체를 조금 개선해보는 방향으로 고민을 해보았습니다. 마침 제가 필요한 컬럼은 pk인 place_id와 uk인 place_seq였기 때문에 프로젝션 연산을 하여 커버링 인덱스가 사용될 수 있도록 하면 더욱 효율적으로 쿼리를 수행할 수 있을 것 같았습니다.
EXPLAIN
SELECT id, exhibition_seq FROM exhibition
WHERE exhibition_seq IN ();쿼리도 간단한데, 이미 WHERE 절에 검색은 Unique 인덱스 컬럼이 사용되고 있었기 때문에 프로젝션을 적용해주었습니다.
바로 실행 계획을 살펴본 결과, 100개만 조회할 때와 208개 모두 조회할 때 동일하게 Using Index가 나타났습니다.
// SELECT id, seq
List<PlaceSeqProjection> placeList = placeRepository.findBySeqIn(placeSeqList);
Map<Integer, Long> placeIdMap = placeList.stream()
.collect(Collectors.toMap(PlaceSeqProjection::getSeq, PlaceSeqProjection::getId));
// Projection Interface
public interface PlaceSeqProjection {
Long getId();
Integer getSeq();
}
// Repository
List<PlaceSeqProjection> findBySeqIn(List<Integer> placeSeqList);간단한 필드 조회이기 때문에 위와 같이 인터페이스 기반으로 프로젝션을 적용했고, 아래와 같이 잘 적용되어 쿼리가 실행되었습니다.
사실 사용자의 요청으로 인해 발생하는 쿼리가 아닌, 배치 작업 중, 그것도 많은 데이터를 작업할 때 발생하는 문제가 아니라 유의미한 개선이라고 보기는 어렵습니다. 그럼에도 느낀 건 쿼리가 어떤 식으로 실행될지 생각하며 작업을 이어나갈 필요가 있다는 것입니다!
Writer에는 DB를 직접 사용하는 작업들이 많이 존재합니다. 쿼리가 많음으로 인해 나타나는 문제들은 쿼리를 분리해서 생각해보면 좋을 것 같다고 느꼈습니다. write 작업을 위한 select와 write 자체 쿼리들을 각각 어떻게 더 줄이고, 효율적으로 실행할지 고민해보면 좋을 것 같습니다!
참고 :
'Project' 카테고리의 다른 글
| 조회수 카운팅 시 동시성 문제 (0) | 2025.03.23 |
|---|---|
| 회원과 비회원을 고려한 복합 조회수 카운팅 정책 (0) | 2025.03.20 |
| 외부 API 호출 시 고려할 것 (0) | 2025.03.10 |
| 배치 프로세스 리팩토링 (0) | 2025.03.03 |
| Swagger 코드 분리, Http Status (0) | 2025.02.28 |