베팅 패턴 분석 쿼리의 실행 시간 초과(Timeout) 방지 요약 테이블 설계

증상 확인: 쿼리가 30초 이상 걸리거나 타임아웃이 발생하나요?

대량의 베팅 로그 데이터를 JOIN하고, 복잡한 집계 함수(SUM, COUNT, AVG)를 실행하며, 실시간 분석을 요구하는 패턴 분석 쿼리는 데이터베이스에 치명적인 부하를 줍니다. “쿼리 실행 중” 상태에서 멈추거나, 애플리케이션 로그에 “CommandTimeout” 에러가 기록되는 것이 대표적인 증상입니다. 이는 단순한 성능 저하가 아닌, 시스템 가용성 자체를 위협하는 문제입니다.

원인 분석: 왜 분석 쿼리는 느릴 수밖에 없나

근본 원인은 OLTP(온라인 트랜잭션 처리) 데이터베이스를 OLAP(온라인 분석 처리) 용도로 사용하려는 데 있습니다. 베팅 트랜잭션을 기록하는 테이블은 INSERT 위주로 최적화되어 있지만, 분석 쿼리는 과거 데이터 전체를 스캔하고 복잡한 계산을 수행합니다. 특히, 비정규화되지 않은 테이블 간의 다중 JOIN, 부적절한 인덱스, 실시간으로 수행되는 전체 기간 분석은 실행 계획을 비효율적으로 만들고 타임아웃을 유발합니다.

주의사항: 본 가이드에서 제안하는 테이블 설계 및 쿼리 변경은 기존 운영 데이터베이스의 스키마와 성능에 직접적인 영향을 미칩니다. 모든 변경 사항은 반드시 스테이징(Staging) 환경에서 철저한 성능 테스트를 거친 후 운영 환경에 적용해야 합니다. 주요 트랜잭션 시간대를 피해 작업하는 것이 기본 원칙입니다.

해결 방법 1: 즉시 적용 가능한 쿼리 및 인덱스 최적화

애플리케이션 코드를 수정할 시간이 없다면, 현재 데이터베이스에서 실행 속도를 개선할 수 있는 최소한의 조치부터 시작합니다.

1. 
   
   실행 계획 분석: 느린 쿼리 앞에 EXPLAIN (MySQL) 또는 EXPLAIN ANALYZE (PostgreSQL), SQL Server의 경우 실행 계획 보기 기능을 사용합니다. Full Table Scan이 발생하는 지점을 찾는 것이 핵심입니다.
   
2. 
   
   전략적 인덱스 추가: WHERE 절의 조건(예: user_id, bet_time), JOIN 조건 컬럼, ORDER BY 또는 GROUP BY에 사용된 컬럼에 복합 인덱스를 생성합니다. 실제로. create index idx_bet_pattern on bet_logs(user_id, sport_type, bet_time); 과 같이 설계합니다.
   
3. 쿼리 재작성:
   
   • select * 대신 필요한 컬럼만 명시합니다.
   • 서브쿼리보다는 join을, 가능하다면 inner join을 우선 사용합니다.
   • 데이터 양이 많다면, limit 또는 top으로 샘플 데이터를 먼저 확인합니다.

해결 방법 2: 근본적 해결 – 요약 테이블(Summary Table) 설계

실시간 분석 부하를 분리하는 가장 효과적인 방법입니다. 원본 트랜잭션 테이블(bet_logs)을 직접 조회하지 않고, 미리 집계된 데이터를 담은 요약 테이블을 생성하여 쿼리합니다.

요약 테이블 설계 예시 (핵심 필드)

분석 요구사항에 따라 세분화 수준(Granularity)을 결정합니다. 아래는 일별 사용자별 스포츠 종목별 요약 테이블의 필수 컬럼 예시입니다.

1. 기본 키 및 그룹 기준:
   
   • summary_date (DATE): 집계 일자. (예: 2023-10-27)
   • user_id (INT/BIGINT): 사용자 식별자.
   • sport_type (VARCHAR): 스포츠 종목 (예: football, basketball).
   • 이 세 컬럼의 조합으로 복합 기본 키(Primary Key)를 구성합니다.
2. 집계 지표 (메트릭스):
   
   • total_bets (INT): 총 베팅 횟수.
   • total_stake (DECIMAL): 총 베팅 금액.
   • total_payout (DECIMAL): 총 지급 금액.
   • net_profit (DECIMAL): 순이익 (total_stake – total_payout). 물리적으로 저장하거나 뷰에서 계산.
   • favorite_bet_type (VARCHAR): 해당 기간 내 가장 많이 선택한 배팅 유형 (예: moneyline, over/under).
   • first_bet_time, last_bet_time (DATETIME): 첫/마지막 베팅 시간 (추가 분석용).

요약 테이블 생성 및 유지 관리

테이블을 설계했으면 데이터를 채우고 최신 상태로 유지하는 메커니즘이 필요합니다. 초기 데이터 적재 시에는 과거 데이터를 일괄 집계하여 요약 테이블에 INSERT하며, 부하가 크므로 시스템 사용량이 낮은 시간에 수행하는 것이 적절합니다.

이후 지속적인 관리를 위한 운영 방식 검토 결과에 따르면, 매일 새벽 전일 데이터만 집계하여 갱신하는 증분 전략은 실시간 트랜잭션에 영향을 주지 않으면서 데이터 최신성을 보장하는 효율적인 대안으로 확인됩니다. 이에 따라 애플리케이션의 분석 쿼리가 원본 테이블인 bet_logs 대신 bet_logs_daily_summary와 같은 요약 테이블을 조회하도록 변경하면 쿼리 복잡도와 실행 시간을 극적으로 감소시킬 수 있습니다.

해결 방법 3: 고급 아키텍처 – 분석 스택 분리

데이터 규모가 매우 크고 분석 요구가 복잡해지면, 운영 데이터베이스(OLTP)와 별개의 전문 분석 데이터베이스(OLAP)를 도입하는 것이 장기적인 해결책입니다.

• 읽기 전용 복제본(Read Replica) 활용: 운영 DB의 복제본을 생성하여 분석 쿼리를 모두 복제본으로 라우팅함으로써 운영 시스템의 부하를 원천적으로 분리합니다.
• 컬럼형 데이터베이스(Columnar DB) 도입: 대량 집계에 최적화된 ClickHouse, Amazon Redshift 같은 데이터베이스를 도입하면, 행 기반 DB보다 수십 배에서 수백 배 빠른 성능을 체감할 수 있습니다.
• ELT/ETL 파이프라인 구축: Apache Airflow나 AWS Glue를 통해 데이터를 분석용 웨어하우스로 정기적으로 적재하여 분석 효율을 극대화합니다.

이러한 고성능 분석 아키텍처는 실시간 리스크 관리의 핵심인 비정상적인 고수익 파트너 발생 시 자동 정산 보류(Hold) 트리거 조건을 구현할 때 진가를 발휘합니다. 운영 DB에서 모든 거래를 직접 계산하면 시스템 마비가 올 수 있지만, 분리된 분석 스택에서는 수백만 건의 데이터를 실시간으로 스캔하여 특정 파트너의 수익률이 비정상적으로 급증하는 패턴을 즉각 탐지할 수 있습니다.

트리거 조건이 충족되는 순간, 분석 시스템은 운영 시스템에 즉시 ‘정산 보류’ 신호를 보냅니다. 이는 대량의 데이터 분석을 통한 정밀한 탐지와 운영 시스템의 가용성 확보라는 두 마리 토끼를 모두 잡는 전략입니다. 결국 현대적인 인프라는 데이터를 단순히 저장하는 것을 넘어, 분석 전용 계층을 통해 리스크에 즉각 반응할 수 있는 ‘방어적 아키텍처’로 진화해야 합니다.

주의사항 및 예방 조치

성능 개선은 한 번의 작업이 아닌 지속적인 프로세스입니다.

• 모니터링 필수: 쿼리 응답 시간, 데이터베이스 CPU/메모리 사용량을 상시 모니터링합니다. 타임아웃이 발생하기 전에 성능 저하 추세를 파악할 수 있습니다.
• 데이터 보존 정책: 요약 테이블이 있다고 해도 원본 bet_logs 테이블의 데이터는 무한정 증가합니다. 사용하지 않는 오래된 데이터는 아카이빙하거나 삭제하는 정책을 수립하여 테이블 스캔 부하를 근본적으로 줄입니다.
• 개발자 교육: 모든 개발자가 효율적인 쿼리 작성법과 새로운 요약 테이블 스키마를 이해하도록 합니다, n+1 쿼리 문제나 무분별한 실시간 전체 조회 요청이 발생하지 않도록 합니다.

전문가 팁: 요약 테이블의 세분화 수준을 결정할 때, “상세함 vs 성능”의 트레이드오프를 명심하십시오. 시간대별. 10분별로 집계하면 더 세밀한 분석이 가능한편 테이블 크기가 폭발적으로 증가하고 갱신 부하가 커집니다. 처음에는 일별 집계로 시작하여, 정말 필요한 경우에만 더 세분화된 테이블을 별도로 구축하는 접근법이 안전합니다. 또한, 요약 테이블에 자주 사용되는 차원(예: 사용자 등급, 지역)을 미리 JOIN하여 포함시켜두면, 분석 쿼리 시 추가 JOIN을 완전히 제거할 수 있어 성능 향상에 결정적입니다.