NOT IN 대신 JOIN을 통한 쿼리 최적화 방법 (쿼리 시간 96% 절감 후기)
들어가는 글
저희 기업은 B2B BI 서비스인 위핀 워크스페이스 사용자 통계를 제공하기 위해 엔터프라이즈 데이터 웨어하우스(Enterprise Data Warehouse) 환경을 운영하고 있습니다. 이 운영 과정에서 ELT 파이프라인과 오케스트레이션 작업을 진행하면서, 예상보다 많은 시간이 소요되는 부분을 발견하게 되었습니다. 현재 트래픽 수준에서는 치명적인 문제가 되지는 않았지만, 더 빠르게 데이터 변환(Transformation)을 할 수 있는 방법을 찾기 위해 고민을 했습니다. 결국, 기존 50분에서 약 2분 내외로 처리 시간을 줄일 수 있었는데요. 이번 아티클에서는 쿼리 실행 시간이 지나치게 오래 걸린 부분을 어떻게 발견하고 개선했는지, 그 과정을 공유드리겠습니다.
배경
데이터 웨어하우스는 아래 그림과 같이 Kimball’s Dimensional Modeling 이론을 적용하여 Source-Core-Mart-Access의 4단계로 구성되어 있습니다. 매일 자정 무렵에 오케스트레이션이 진행되며, BI 서비스에 데이터를 업데이트하는 방식으로 운영됩니다.
문제의 발견
Core Layer에는 core_fct_events 라는 전형적인 이벤트 테이블이 있습니다. 이 테이블은 각 Mart Layer의 중심 역할을 하는 가장 중요한 Fact Table로, 모든 이벤트 로그 데이터를 담고 있어 가장 큰 사이즈를 가지고 있습니다. 이 테이블을 증분 전략(Incremental Strategy)을 통해 업데이트하고 있는데, 이 과정이 전체 오케스트레이션 시간의 대부분을 차지했는데요. 그 이유는 크게 세 가지였습니다.
(1) 소스 테이블 자체가 사용자 이벤트 데이터를 지니고 있으므로, 애초에 사이즈가 매우 크기 때문입니다.
(2) 모종의 이유로 인해 소스 테이블 자체에서 동일한 이벤트가 중복 생성되는 경우가 많아, 데이터를 읽어올 때 DISTINCT를 적용할 수밖에 없었기 때문입니다.
(3) core_fct_events 테이블의 기존 데이터와 새로운 데이터를 비교하여 중복 여부를 확인하는 작업에 시간이 많이 소요되기 때문입니다.
문제 해결 목표 설정
우선, 문제 (3)을 개선하는 것에 집중하기로 했습니다. 문제 (1)은 불가피한 상황이며, 문제 (2)의 경우 애널리틱스 엔지니어링 업무를 담당하고 있는 저뿐만 아니라, 백엔드 개발 등 조직적인 차원의 시간과 비용이 수반되므로 가성비가 좋지 않다고 생각했기 때문입니다.
또한, 기존 오케스트레이션 시간을 10% 수준으로 줄이는 것을 목표로 삼았습니다. 향후 서비스의 트래픽 규모가 급격하게 증가하는 추후 상황을 고려한다면, 사실 언젠가는 꼭 필요한 목표였기 때문입니다.
구체적인 문제 해결 전략
core_fct_events.sql 파일의 기존 코드는 다음과 같았습니다.
WITH
CTE_src_events AS (
SELECT
DISTINCT
datetime,
app_id,
user_id,
event_name
FROM
src_events
-- Incremental Strategy: Read rows with a datetime greater than the maximum datetime currently stored in the table.
{% if is_incremental() %}
WHERE
(SELECT MAX(datetime) FROM {{ this }}) < datetime
{% endif %}
)
SELECT
*
FROM
CTE_src_events
-- Incremental Strategy: Exclude data that already exists in the table. Do not insert those rows.
{% if is_incremental() %}
WHERE
(datetime, app_id, user_id, event_name) NOT IN (SELECT datetime, app_id, user_id, event_name FROM {{ this }})
{% endif %}🔼 실제 코드를 상당 부분 생략하고 각색한 것입니다.
위 코드의 증분 전략 작업 흐름을 요약하면 다음과 같았습니다.
(1) 소스 테이블 src_events 에서 core_fct_events 테이블의 datetime 최댓값을 초과하는 행들을 읽어옵니다. (중복 행도 함께 제거)
(2) core_fct_events 테이블에 아직 존재하지 않는 행들만 신규 데이터로 간주하여 Insert합니다.
이 중, 성능 병목을 유발하는 NOT IN 구문을 최적화할 필요가 있었습니다. NOT IN은 Nested Loop 검색을 유발해 core_fct_events 테이블의 사이즈가 커질수록 지속적인 성능 저하를 야기하기 때문입니다.
SELECT
*
FROM
CTE_src_events
-- Incremental Strategy: Exclude data that already exists in the table. Do not insert those rows.
{% if is_incremental() %}
WHERE
(datetime, app_id, user_id, event_name) NOT IN (SELECT datetime, app_id, user_id, event_name FROM {{ this }})
{% endif %}🔼 정확히 이 지점이 성능 저하를 야기하고 있었습니다.
🔼 Nested Loop 검색이 발생하는 이유를 시각화해봤습니다.
이에 따라 기존의 NOT IN 구문을 아래의 LEFT JOIN 방법으로 수정했습니다.
SELECT
MAIN.*
FROM
CTE_src_events MAIN
-- Incremental Strategy: Exclude data that already exists in the table. Do not insert those rows.
{% if is_incremental() %}
LEFT JOIN
{{ this }} THIS
ON MAIN.datetime = THIS.datetime
AND MAIN.app_id = THIS.app_id
AND MAIN.user_id = THIS.user_id
AND MAIN.event_name = THIS.event_name
WHERE
THIS.datetime IS NULL
{% endif %}🔼 LEFT JOIN을 통해 기존 데이터와 신규 데이터를 연결한 후, 기존 데이터가 NULL인 것들만 출력하는 구조입니다.
LEFT JOIN 방법이 NOT IN 보다 성능이 뛰어난 이유는 전체 테이블 스캔을 줄이고, 불필요한 데이터를 건너뛰어 검색 속도를 높일 수 있기 때문입니다.
문제 해결 후 결과
아래 그림과 같이, 위 사례를 포함한 전체 오케스트레이션 과정의 시간이 기존 50분에서 2분으로 대폭 줄어들었습니다. core_fct_events 테이블의 증분 전략 배치 실행 시간이 주요한 이슈였기 때문에 문제 해결의 임팩트가 엄청 컸던 것입니다.
🔼 저희 기업의 데이터 천재 고양이 슬랙 봇 “데냥이”
나가는 글
SQL을 객체 지향 프로그래밍 언어인 Python이나 절차적 프로그래밍 언어인 VBA처럼 바라보면 안 된다는 점을 다시금 깨달았습니다. SQL은 항상 집합의 개념으로 접근해야 하며, 이 과정에서 발생하는 검색-집계-연산의 부담을 고려해야 하는 것입니다. 논리적인 절차에 따라 쿼리문을 작성하는 것도 괜찮지만, 실행 속도와 비용을 염두에 둔 최적화를 위해서는 종종 논리 순서가 아니라, 집합의 개념으로 작성할 필요가 있습니다.
이번 사례를 통해 쿼리 최적화에 대한 중요한 교훈을 얻었으며, 이를 통해 가성비가 더 좋은 엔터프라이즈 데이터 웨어하우스를 구축할 수 있었다는 점에서 보람을 느낍니다. 읽어주셔서 감사합니다.
