GGR 집계 쿼리의 실행 계획(Explain Plan) 분석과 인덱스 스캔 효율성

GGR 집계 쿼리 최적화: 실행 계획 분석과 인덱스 스캔의 핵심

GGR(Gross Gaming Revenue, 총 게임 수익)은 게임 및 플랫폼 비즈니스의 핵심 성과 지표입니다. 이 데이터의 집계 쿼리는 대규모 트랜잭션 로그 테이블을 기반으로 하며, 실시간 대시보드부터 일일 정산 보고서까지 다양한 비즈니스 요구에 사용됩니다. 비효율적인 집계 쿼리는 데이터베이스 서버에 과부하를 초래하고, 보고서 생성 지연으로 인한 의사결정 장애를 일으킬 수 있습니다. 본 분석은 이러한 GGR 집계 쿼리의 성능을 결정짓는 실행 계획(Explain Plan)을 해석하고. 인덱스 스캔의 효율성을 극대화하는 실전 전략을 제시합니다. 목표는 동일한 데이터를 처리할 때, 더 낮은 CPU/IO 비용과 더 빠른 응답 시간을 얻는 것입니다.

실행 계획(Explain Plan) 해석: 비용 중심의 분석 프레임워크

데이터베이스 옵티마이저는 쿼리를 실행하기 전, 가능한 여러 실행 경로를 계산하고 예상 비용(Cost)이 가장 낮은 하나의 계획을 선택합니다. GGR 집계 쿼리의 성능 문제를 진단하려면 이 실행 계획을 비용의 관점에서 해석할 수 있어야 합니다. 계획은 트리 구조로 표현되며, 각 노드(단계)는 특정 연산(Table Scan, Index Scan, Sort, Aggregate 등)을 의미합니다. 해석의 핵심은 높은 비용을 유발하는 병목 구간을 찾는 것입니다.

집계 쿼리 실행 계획의 주요 연산자

GGR 집계 쿼리에서 자주 등장하며 주의 깊게 봐야 할 연산자들은 다음과 같습니다.

• TABLE FULL SCAN: 대상 테이블의 모든 블록을 읽습니다. 대용량 트랜잭션 테이블에서 이 연산이 나타난다면 가장 심각한 성능 저하 신호입니다. 예상 비용과 행 수(ROWS)가 매우 클 것입니다.
• INDEX RANGE SCAN / INDEX FULL SCAN: 인덱스를 순차적으로 스캔합니다. 범위 스캔은 효율적일 수 있지만, 인덱스 전체 스캔은 인덱스 크기에 따라 여전히 부담이 될 수 있습니다.
• SORT (ORDER BY) / HASH GROUP BY: 집계를 위해 데이터를 정렬하거나 해시 그룹을 생성합니다. 처리할 행 수가 많을수록 메모리와 CPU 사용량이 급증하는 구간입니다. ‘Temp Spc’ 사용 유무는 디스크 임시 공간 사용을 의미하며, 심각한 성능 저하 지표입니다.
• NESTED LOOP / HASH JOIN / MERGE JOIN: 사용자 테이블, 게임 메타 테이블 등과의 조인 시 사용됩니다. 적절한 조인 방식 선택은 옵티마이저의 핵심 결정 사항입니다.

GGR 집계 쿼리 패턴과 최적 인덱스 설계

GGR 집계는 일반적으로 시간 범위(`transaction_date`), 게임/제품 ID(`game_id`), 사용자 구분(`user_tier`) 등의 조건으로 필터링된 후, `SUM(stake – payout)` 형태로 계산됩니다. 효율적인 인덱스 설계는 쿼리 패턴에 완전히 종속적입니다.

비효율적인 인덱스 스캔의典型案例

다음과 같은 쿼리와 인덱스가 있다고 가정합니다.

쿼리: SELECT game_id, SUM(amount) as ggr FROM transaction_log WHERE transaction_date BETWEEN ‘2024-01-01’ AND ‘2024-01-31’ AND status = ‘SETTLED’ GROUP BY game_id;
인덱스 A: CREATE INDEX idx_game ON transaction_log (game_id);
인덱스 B: CREATE INDEX idx_date_status ON transaction_log (transaction_date, status);

인덱스 A(`game_id`)만 존재할 경우, 옵티마이저는 `game_id`로 범위 스캔을 하더라도 `transaction_date`와 `status` 조건을 만족하는 행을 찾기 위해 테이블 랜덤 액세스(Table Access by Index ROWID)를 매번 수행해야 합니다. 이는 수백만 건의 데이터에서 치명적인 성능 저하를 유발합니다. 반면, 인덱스 B(`transaction_date, status`)를 사용하면, 조건에 부합하는 행들을 인덱스에서 빠르게 찾고, 필요한 `game_id`와 `amount`를 함께 추출할 수 있어 훨씬 효율적입니다. 이는 커버링 인덱스(Covering Index)에 가까운 형태입니다.

최적의 복합 인덱스 설계 전략

GGR 집계를 위한 인덱스는 쿼리의 WHERE 절, GROUP BY 절, 그리고 선택적으로 SELECT 절까지 고려해야 합니다. 이상적인 복합 인덱스의 컬럼 순서는 다음과 같은 원칙을 따릅니다.

• 선택도(Selectivity)가 높은 컬럼을 선두에: `transaction_date`와 `status` 중, 특정 일자의 ‘SETTLED’ 상태 건수는 전체 데이터의 극히 일부일 것입니다. `transaction_date`를 선두로 하는 것이 효율적입니다.
• 동등 조건(=) 컬럼은 범위 조건(BETWEEN, >, <) 컬럼보다 앞에: `WHERE status = ‘SETTLED’ AND transaction_date BETWEEN …` 라면, 인덱스를 `(status, transaction_date)` 순으로 생성하는 것이 더 유리할 수 있습니다.
• GROUP BY 또는 ORDER BY 절의 컬럼을 포함: `GROUP BY game_id`를 수행한다면, 인덱스에 `game_id`를 포함시켜 정렬 작업(SORT)을 생략할 수 있습니다. 인덱스가 `(transaction_date, status, game_id)` 순이라면, 조건 필터링 후 이미 `game_id`로 정렬된 데이터를 얻어 추가 정렬 없이 해시 그룹 생성만으로 집계가 가능합니다.
• 커버링 인덱스 지향: 가장 효율적인 스캔은 인덱스만 읽고 쿼리를 완료하는 것입니다. 위 쿼리의 경우 `(transaction_date, status, game_id, amount)` 인덱스를 생성하면, 테이블 액세스 없이 인덱스 스캔만으로 모든 필요한 데이터(`transaction_date`, `status`, `game_id`, `amount`)를 얻을 수 있습니다. 이는 테이블이 매우 클 경우 성능 향상 효과가 절대적입니다.

실행 계획 비교: 인덱스 설계에 따른 성능 차이

다양한 인덱스 전략이 동일한 GGR 집계 쿼리의 실행 계획과 성능에 미치는 영향을 비교합니다. 분석 대상은 1억 건의 트랜잭션 로그 테이블이며, 특정 일자 100만 건을 집계한다고 가정합니다.

인덱스 전략	예상 실행 계획 핵심 연산	예상 비용 & 효율성 분석	주요 리스크
전략 1: 인덱스 없음	TABLE FULL SCAN -> SORT (GROUP BY)	가장 높은 비용. 전체 1억 건 테이블 스캔 및 디스크 정렬 발생 가능성이 큼. 실행 시간이 수 분에서 수십 분 소요될 수 있음.	시스템 자원(CPU, I/O) 과다 사용으로 다른 쿼리 성능 저하 유발.
전략 2: 단일 컬럼 인덱스 (transaction_date)	INDEX RANGE SCAN (date) -> TABLE ACCESS BY ROWID -> HASH GROUP BY	비용 중간. 100만 건에 대한 테이블 랜덤 액세스가 부하 요인. 인덱스 리프 블록과 데이터 블록을 모두 읽어야 함.	집계 컬럼(amount)이 인덱스에 없어 테이블 접근은 필수적이며, I/O 부하가 여전히 존재.
전략 3: 복합 인덱스 (transaction_date, status)	INDEX RANGE SCAN -> TABLE ACCESS BY ROWID -> HASH GROUP BY	비용 중간-낮음. 전략2 대비 스캔 범위가 ‘status’ 조건으로 더 정제됨. 그러나 테이블 랜덤 액세스는 동일하게 발생.	GROUP BY game_id를 위한 정렬 작업이 추가로 필요할 수 있어 임시 영역 사용 가능성 있음.
전략 4: 커버링 인덱스 (transaction_date, status, game_id, amount)	INDEX RANGE SCAN -> HASH GROUP BY (또는 인덱스 정렬 활용)	가장 낮은 비용. 인덱스만 스캔하여 모든 데이터 취득. 테이블 액세스 제거로 I/O 비용 대폭 절감. 실행 시간은 초 단위로 단축될 수 있음.	인덱스 크기가 증가하여 DML(INSERT/UPDATE/DELETE) 성능에 미세한 영향을 줄 수 있음. 그러나 읽기 성능 향상 폭이 일반적으로 이를 상쇄함.

분석 결과, 전략 4의 커버링 인덱스가 예상 비용 측면에서 가장 효율적입니다. 이는 순수한 인덱스 스캔으로 데이터를 완결 지을 수 있어, 디스크 I/O라는 가장 비싼 연산을 최소화하기 때문입니다.

고급 최적화 기법 및 주의사항

기본적인 인덱스 설계 외에도, 대용량 GGR 집계 환경에서 고려해야 할 사항들이 있습니다.

파티셔닝(Partitioning)과의 연동

트랜잭션 로그 테이블이 transaction_date 기준으로 범위 파티셔닝(Range Partitioning)되어 있다면, 쿼리 성능은 더욱 극적으로 향상됩니다. 옵티마이저는 불필요한 파티션을 완전히 배제할 수 있습니다(Partition Pruning). 대규모 데이터 집합을 논리적으로 분할하여 관리하는 데이터베이스 파티셔닝(Database Partitioning)의 아키텍처적 정의를 참조해 보면, 특정 범위에 해당하지 않는 데이터 블록은 아예 접근하지 않음으로써 검색 속도를 물리적으로 단축할 수 있음을 확인할 수 있습니다.

이 경우, 파티션 키와 동일한 컬럼에 대한 인덱스는 로컬 인덱스(Local Index)로 생성하는 것이 관리와 성능 측면에서 유리합니다. 파티션 프루닝과 커버링 인덱스가 결합되면, 처리해야 할 물리적 데이터 블록의 수가 최소화됩니다.

집계 함수와 데이터 정합성

GGR 계산 공식 `SUM(stake – payout)`을 데이터베이스에서 직접 수행하는 것과, 애플리케이션 레벨에서 `stake`와 `payout`을 따로 집계한 후 차이를 계산하는 것은 결과가 다를 수 있습니다. 이는 NULL 값 처리나 정밀도 문제에서 기인합니다. 실행 계획 분석 시, 집계 함수의 정확성도 중요한 고려 대상입니다. 가능하다면, 트랜잭션 시점에 네트웨이브 GGR 금액을 계산하여 컬럼 하나(`ggr_amount`)로 저장하는 전처리 방식을 고려할 수 있으며, 이는 집계 쿼리의 복잡성과 부하를 줄여줍니다.

실행 계획의 불안정성과 통계 정보

옵티마이저의 선택은 테이블 및 인덱스의 통계 정보(데이터 분포, 행 수, 높이 등)에 크게 의존합니다. 통계 정보가 오래되었거나 부정확하면, 최적의 인덱스 스캔 경로를 선택하지 못하고 성능이 저하된 실행 계획을 수립할 수 있습니다. 대용량 데이터가 빠르게 쌓이는 트랜잭션 테이블의 경우, 통계 정보 수집 주기를 적절히 관리하는 것이 필수적입니다.

리스크 관리: 최적화의 함정과 모니터링

주의: 인덱스는 만능 해결책이 아닙니다. 각 인덱스는 쓰기 작업(INSERT, UPDATE, DELETE) 시 오버헤드를 발생시킵니다. 과도한 인덱스는 전반적인 시스템 처리 속도를 저하시킬 수 있습니다. 실제로 트랜잭션 발생 빈도가 매우 높은 실시간 테이블에서는 인덱스 개수를 최소한으로 유지하면서, 가장 빈번한 읽기 쿼리에 최적화된 1~2개의 복합 커버링 인덱스를 설계하는 전략이 효과적입니다.

이러한 최적화 과정은 운영 비용 절감과도 맥을 같이 합니다. 시스템 리소스를 효율적으로 관리하여 인프라 비용을 아끼는 것이나, 실생활에서 북앤라이프 도서문화상품권 캐시 전환 수수료 최저가 비교를 통해 불필요한 지출을 줄이는 것은 모두 한정된 자원 내에서 최대의 가치를 끌어내기 위한 노력입니다.

더욱이 실행 계획이 변경되어 성능이 갑자기 저하되는 경우(Plan Regression)를 대비해, 중요한 집계 쿼리에 대한 안정적인 실행 계획을 고정시키는 힌트(Hint) 사용이나 SQL 플랜 베이스라인(SQL Plan Baseline) 관리도 프로덕션 환경에서는 필수적인 절차입니다. 결론적으로 GGR 집계 쿼리의 성능은 실행 계획에 대한 정확한 해석과 쿼리 패턴에 꼭 맞는 인덱스 설계에 달려 있습니다. ‘커버링 인덱스’를 지향하는 설계는 테이블 액세스라는 높은 비용의 연산을 제거함으로써 가장 확실한 성능 향상을 보장하며, 이는 쿼리 자체와 데이터 접근 패턴을 심층적으로 분석한 결과물이어야 합니다.