메시지 발송 에이전트사 변경을 할 때 기존 L사에서 구현한 상태를 유지하는 것을 목표로 해 문제가 있을 경우 기존에도 문제가 있는 것이 대부분이었다.
대부분 크게 영향이 없는 문제들이라 우선순위에 밀려 유지되고 있던 것인데 전환하면서 이러한 문제들을 모두 고치기로 한 모양이다.
이러한 문제들도 제보받고 내가 고치게 되었고, 대부분 사소했지만 치명적인 부분이 하나 있었다.
메시지 발송내역을 조회할 때 월별로 조회하게 되는데, 월말이 되면 메시지가 수십만건 ~ 100만건 정도 쌓이게 된다.
조회를 할 경우 1~2분이 걸리게 된 것이다.
이 부분의 개선을 정리해본다.
구조 파악
조회 쿼리는 크게 두 종류의 테이블을 대상으로 동작한다.
M2X_MSG: 현재 처리 중인 메시지가 저장되는 테이블M2X_MSG_LOG_yyyyMM: 월별로 분리된 로그 테이블
로그 테이블은 최대 2개월 범위를 조회할 수 있도록 되어 있었고,
파라미터(sendTimeStr, sendTimeEnd)를 기반으로 동적으로 테이블명이 생성되는 구조였다.
즉, 구조 자체는 대량 데이터를 고려한 형태였지만, 실제 쿼리에서는 이를 제대로 활용하지 못하고 있었다.
문제 분석
1. 인덱스 무효화 (Full Scan 발생)
가장 큰 문제는 SUBMIT_TIME 조건절이었다.
1
WHERE TO_CHAR(TO_DATE(T.SUBMIT_TIME, 'YYYYMMDDHH24MISS'), 'YYYYMM') = #{sendTimeStr}
SUBMIT_TIME은 이미 문자열 형태로 저장되어 있었지만, 불필요한 TO_DATE → TO_CHAR 변환을 수행하고 있었다.
이로 인해 컬럼이 함수로 감싸지면서 인덱스를 사용할 수 없었고, 약 90만 건 이상의 데이터를 Full Scan 하는 상황이 발생했다.
즉, 인덱스가 존재하더라도 사용할 수 없는 구조였다.
2. 전체 정렬 후 페이징
조회 결과는 100건 단위로 페이징 처리되고 있었지만, COUNT(*) OVER()를 통해 전체 건수를 동시에 계산하고 있었다.
이 구조에서는 다음과 같은 문제가 발생한다.
100건만 조회하더라도 전체 데이터를 스캔 -> 전체 정렬 및 집계 이후 일부만 반환
결과적으로 페이징의 의미가 사라지고, 대량 데이터 처리 비용이 그대로 발생하고 있었다.
3. CTE 필터링 시점 문제
FINAL_RESULT, FINAL_RESULT_BULK CTE에서 WHERE 조건 없이 전체 데이터를 먼저 생성한 뒤, 이후 JOIN 단계에서 필터링하는 구조였다.
조건 적용 시점이 늦어질수록 불필요한 데이터 처리량이 증가하게 된다.
개선 방향
1. 인덱스 활용 가능하도록 조건 변경
함수를 제거하고, 문자열 비교가 가능한 범위 조건으로 변경했다.
| WHERE T.SUBMIT_TIME >= #{sendTimeStr} | ‘01000000’ | |
| AND T.SUBMIT_TIME < #{sendTimeEnd} | ‘01000000’ |
이 방식은 다음과 같은 장점이 있다.
- 컬럼에 함수가 적용되지 않음
- 기존 인덱스를 그대로 활용 가능
- 범위 스캔으로 빠르게 데이터 조회 가능
다만 기존 인덱스 구조는 SUBMIT_TIME 단독 인덱스가 아니었다.
기존: STATUS → SCHEDULE_TIME, MSG_TYPE → SCHEDULE_TIME
이 구조에서는 SUBMIT_TIME 조건만으로는 인덱스를 활용할 수 없었기 때문에 SUBMIT_TIME 단독 인덱스를 추가하여 해결했다.
SQL에서 ‘0100000’을 붙이는 작업이 아닌 서비스레이어에서 넘기는 값을 수정하는 메서드를 만들어 처리했다.
2. 페이징과 카운트 분리
기존 COUNT() OVER() 구조는 제거하고, 카운트 쿼리를 별도로 분리했다.
- 조회 쿼리: 필요한 범위만 조회
- 카운트 쿼리: 전체 건수만 별도로 계산
기존 API 스펙을 유지해야 했기 때문에 Mapper와 VO는 그대로 두고, Service 레이어에서 두 결과를 조합하는 방식으로 처리했다.
- 불필요한 전체 스캔 제거
- 페이징의 효과 정상화
3. 암호화 컬럼 (RCPT_DATA) 한계
수신번호(RCPT_DATA)는 암호화되어 저장되어 있었고, 조회 시 복호화 후 LIKE ‘%value%’ 조건으로 검색하는 구조였다.
이 경우 구조적으로 인덱스를 사용할 수 없기 때문에, 해당 부분은 쿼리 튜닝으로 해결할 수 있는 영역이 아니었다.
가능한 개선 방법은 다음과 같다.
- 입력값을 동일한 방식으로 암호화 후 정확 비교
- 정확히 일치하는 번호만 검색하도록 제한 (LIKE 삭제)
- 별도 검색용 컬럼 분리
컬럼을 분리하는 작업은 테이블 구조를 에이전트사 측에서 보내준 구조이기 때문에 사용할 수 없었고, 입력값을 동일한 방식으로 암호화 후 정확히 일치하는 값만 비교하도록 수정했다.
결과
조회 시간이 최대 150초 가까이 걸리던 것을 환경, 조회 건수에 따라 차이가 있지만 수 초 수준으로 단축할 수 있었다.
Full Scan을 제거하고 인덱스 기반 조회로 전환하였다.
정리
절대적인 응답 시간 기준에서는 추가적인 개선 여지가 있다.
하지만 기존 Full Scan 기반 구조에서 발생하던 비정상적인 지연을 해소했다는 점에서 의미 있는 개선이라고 생각한다.