인덱스 없이 50초 → 3초
이커머스 운영 프로젝트에서 마케팅 시스템을 교체하게 되었다. 새로운 CRM과 연동해야 하는데, 그 중 1시간 단위로 주문/취소/반품 데이터를 뽑아서 보내는 작업이 있었다.
2018년부터 쌓인 주문 850만 건, 주문 상품 1240만 건, 클레임 145만 건… 일단 테이블도 너무 무거운데, 우리 데이터 구조와 연동 스펙이 달라서 주문 + 클레임을 조합해야 했다.
인덱스가 없다
시간 단위 “증분” 데이터를 걸러내려면 업데이트일시 컬럼이 필수였다. 주문 상태 변경 시간, 클레임 상태 변경 시간 모두 조건에 들어가야 했으니까.
그런데 업데이트일시에 관한 인덱스가 없었다. 운영 역사에서 이 컬럼으로 조회할 일이 없었나보다.
이미 오래된 운영 테이블이라 인덱스는 너무 많았고, 오히려 안 쓰이는 것들 정리해야 할 판이었다. 데이터량, 기존 인덱스 개수, 테이블 무게, 메모리… 이런 것들을 고려해서 인덱스 추가는 최후의 보루로 두기로 했다.
한방 쿼리의 한계
처음에는 온갖 기교를 넣어서 한방 쿼리로 원하는 조합의 데이터를 뽑아보았다. CTE, OUTER APPLY, UNION… 이것저것 조합해서.
처음 실행했을 때 2분이 넘어가길래 중지해버렸다. 최적화에 최적화를 거듭해도 47초.
한 시간마다 도는 배치인데 주문 테이블 조회에만 47초? 절대 안 될 일이었다.
수 번의 최적화에 용쓰던 나는 슬슬 지쳐가고 있었다. 아 인덱스 넣으면 해결될 텐데… 인덱스를 애타게 그리워하고 있었다.
쪼개야 살겠구나
하지만 잠시 숨을 돌리고, 쿼리 그 자체에서 한 발 물러나 보았다.
대체 병목이 어디지? 쿼리를 쪼개서 각각의 실행 시간을 확인해보았다. 그리고, 각각의 작은 단위의 쿼리를 최적화해보았다.
5초짜리가 0.6초가 되고, 7.8초짜리가 0.09초가 되더라.
한방 쿼리를 고집할 이유가 없었다. 쪼개야 살겠구나! 바로 쿼리 분리에 들어갔다.
분리 구조
핵심 원칙은 “PK 먼저 수집 → IN절로 개별 조회”였다.
주문 데이터 (5개 분리)
| 순서 | 쿼리 | 역할 |
|---|---|---|
| 1 | selectTargetOrderNos | 대상 OrderNo 수집 |
| 2 | selectOrderMstByOrderNos | 주문 마스터 정보 |
| 3 | selectOrderPayByOrderNos | 결제 정보 |
| 4 | selectOrderDtlAggByOrderNos | 주문 상태 집계 |
| 5 | selectClaimAmtByOrderNos | 환불 금액 |
주문 상품 데이터 (8개 분리)
| 순서 | 쿼리 | 역할 |
|---|---|---|
| 1-1 | selectNewOrderNos | 신규 주문 OrderNo |
| 1-2 | selectOrderSeqsByOrderNos | OrderNo → OrderSeq 변환 |
| 1-3 | selectStatusChangedItemPKs | 상태 변경된 아이템 PK |
| 1-4 | selectPartialClaimItemPKs | 부분클레임 원주문 PK |
| 2 | selectOrderItemDtlByPKs | 상세 데이터 조회 |
| 3-1 | selectPartialCancelClaims | 부분취소 건 |
| 3-2 | selectOriginalOrderStatusByPKs | 원주문 상태 |
| 3-3 | selectMaxOrderSeqByOrderNos | order_idx 계산용 |
조합은 Java에서
N+1을 내 손으로 만들지 않기 위해 IN절을 사용했고, 그렇다고 쿼리를 너무 잘게 쪼개지도 않았다. 쿼리 수가 많아지면 DB 커넥션 획득/반환 비용이 늘어나고, Java 서비스 단에서도 병목이 생길 수 있기 때문이다.
조회한 데이터들은 Java에서 HashMap으로 조합했다. order_id를 key로 해서 O(1)로 매칭하는 방식이다.
결과
하나의 쿼리를 5~8개로 분리했고, 같은 범위 조건으로 테스트해본 결과 총 실행 시간은 3초가 되었다.
아직 좀 길어보이시나요..? 사실 클레임 테이블 조회에 2.5초가 걸리는데, 이건 진짜 업데이트일시 인덱스 없이는 답이 없다. 하지만 나머지 조회, 조합 로직은 0.5초로 끝난 것이다.
일단 운영에는 무리가 없다고 판단하였지만, 운영하면서 더 최적화가 필요해지면 그때 인덱스 추가를 다시 검토해볼 생각이다.
배운 점
1. 한방 쿼리가 항상 좋은 건 아니다
한방 쿼리가 좋을 때도 있다. 데이터가 적고, JOIN이 단순하고, 인덱스가 잘 잡혀있을 때.
하지만 이번처럼 데이터가 많고, 인덱스도 없고, 조합 로직이 복잡하면 오히려 분리하는 게 낫다. JOIN이 많아지면 실행 계획이 꼬이고, OUTER APPLY 같은 게 들어가면 더 느려진다.
2. 병목 지점을 먼저 파악하자
무작정 최적화하지 말고, 어디서 느린지부터 확인해야 한다. 쿼리를 쪼개서 각각 실행 시간을 보면 답이 보인다.
3. 인덱스가 답이 아닐 수도 있다
인덱스 추가가 어려운 상황도 있다. 그럴 땐 구조를 바꾸는 것도 방법이다.