배경

‘테이블에 1억 건 데이터가 있으면 조회가 느리다’는 말은 항상 참이 아님. 성능 저하는 데이터 양 자체보다 ‘어떤 쿼리를 어떤 인덱스로 어떻게 읽는가’의 문제일 때가 많음. 이 글에서는 대용량 테이블에서 조회 성능이 저하되는 주된 원인을 진단하고, 각 상황에 맞는 해결 전략을 정리함.

느린 쿼리의 대표적인 원인

대용량 테이블에서 쿼리가 느리다면 대부분 아래 원인 중 하나에 해당함.

  1. Full Table Scan: WHERE나 ORDER BY 절이 인덱스를 효과적으로 사용하지 못해 테이블 전체를 스캔
  2. 낮은 인덱스 선택도: 인덱스를 사용하더라도, status='ACTIVE'처럼 대부분의 행이 해당하는 조건이라 읽어야 할 데이터가 너무 많음
  3. 메모리 외부 정렬/그룹화: 정렬이나 그룹화할 데이터가 메모리 용량을 초과해 디스크 I/O가 발생하는 경우 (External Sort, Hash Aggregate)
  4. 비효율적인 조인: 조인 순서가 잘못되었거나 조인 키에 인덱스가 없어 비효율적으로 동작
  5. OFFSET 기반 페이지네이션: OFFSET 1000000처럼 앞부분의 데이터를 모두 읽고 버리는 비효율적인 방식
  6. 기타 운영 이슈: 특정 행에 대한 동시 접근으로 인한 핫스팟(Hotspot), 잠금(Lock) 경합, 디스크 IOPS 부족, 캐시 미스 등

결론적으로 1억 건 테이블이라도 쿼리가 인덱스를 통해 소수의 행만 접근(Index Seek)하고 짧은 범위만 스캔(Range Scan)한다면 응답 속도는 충분히 빠를 수 있음.

성능 진단 시 핵심 확인 사항

옵티마이저 통계의 함정

인덱스를 잘 설계했더라도 데이터베이스 옵티마이저가 풀 스캔을 선택하는 경우가 있음. 옵티마이저는 통계 정보(Statistics)를 바탕으로 데이터 분포를 예측하고 실행 계획을 수립함. 만약 테이블의 데이터 분포가 크게 변경되었는데 통계 정보가 갱신되지 않았다면, 옵티마이저는 인덱스 사용이 오히려 비효율적이라고 잘못 판단할 수 있음. 실행 계획이 예상과 다르다면 인덱스 설계를 의심하기 전에 통계 정보가 최신 상태인지 먼저 확인해야 함.

랜덤 I/O와 커버링 인덱스

보조 인덱스를 사용해 조건을 만족하는 데이터를 찾은 뒤, 실제 행 데이터를 가져오기 위해 테이블 페이지에 접근하는 과정은 디스크의 여러 위치를 읽는 랜덤 I/O를 유발함. 이 랜덤 I/O 비용이 특정 임계점을 넘으면, 옵티마이저는 차라리 테이블 전체를 순차적으로 읽는 풀 스캔이 더 빠르다고 판단할 수 있음. 이런 이유로 조회하려는 모든 컬럼이 인덱스에 포함되어 테이블 접근이 필요 없는 ‘커버링 인덱스(Covering Index)’ 전략이 대용량 테이블에서 매우 효과적임.

파티셔닝의 양면성

테이블 파티셔닝은 ‘파티션 키’가 쿼리의 WHERE 절에 포함되어 특정 파티션만 읽도록 제한(Partition Pruning)될 때 가장 효과적임. 만약 쿼리에 파티션 키 조건이 없다면 모든 파티션을 스캔해야 하므로 오히려 성능이 저하될 수 있음. 파티셔닝은 ‘데이터를 나눴으니 빨라지겠지’라는 막연한 기대가 아니라, 명확한 쿼리 패턴과 프루닝 전략 하에 도입해야 함.

조회 유형별 최적화 전략

PK 단건 조회: WHERE id = ?

B-Tree 인덱스 덕분에 데이터양과 상관없이 O(logN)의 시간 복잡도로 매우 빠름. 성능 저하가 발생한다면 DB 자체보다 디스크, 캐시, 네트워크 등 외부 요인이 원인일 가능성이 높음.

  • 전략: PK를 클러스터드 인덱스로 유지하고, 필요한 컬럼만 조회하며, 커넥션 풀을 적절히 튜닝

조건 필터 + 최신순 정렬: WHERE user_id=? ORDER BY created_at DESC LIMIT 20

실무에서 가장 흔한 유형의 쿼리. (user_id, created_at) 순서의 복합 인덱스가 핵심임. user_id로 범위를 좁힌 뒤 created_at으로 정렬된 순서를 따라 필요한 만큼만 읽을 수 있기 때문. 인덱스 순서가 (created_at, user_id)로 바뀌면 의도대로 동작하지 않음.

  • 전략: (필터링 컬럼, 정렬 컬럼) 순서로 복합 인덱스를 생성하고, 조회하는 컬럼 수가 적다면 커버링 인덱스까지 고려

범위 조회: WHERE created_at BETWEEN ...

created_at 컬럼에 인덱스가 있다면 Range Scan으로 효율적인 조회가 가능함. 하지만 조회 기간이 너무 길어 읽어야 할 데이터 양이 많아지면 성능이 저하될 수 있음.

  • 전략: 시간 컬럼에 인덱스를 생성하고, 조회 범위가 항상 크고 예측 가능하다면 시간 단위 파티셔닝을 고려

페이지네이션

OFFSET 기반 페이지네이션은 뒤 페이지로 갈수록 불필요한 데이터를 반복적으로 읽고 버리므로 대용량 테이블에서는 절대적으로 피해야 함. 대신 마지막으로 조회된 데이터의 키를 다음 페이지 요청 시 넘겨주는 Keyset(또는 Seek) 방식을 사용해야 함.

  • 전략: WHERE (created_at, id) < (?, ?)와 같이 마지막 조회 키를 조건으로 사용하는 Keyset 페이지네이션으로 전환하고, 관련 복합 인덱스를 설계

집계 및 리포팅

COUNT(*), GROUP BY, DISTINCT 같은 집계 쿼리는 대용량 테이블 전체를 스캔할 수 있어 매우 비용이 큼. 실시간으로 이런 쿼리를 실행하는 것은 피해야 함.

  • 전략: 일별/시간별로 미리 집계해두는 요약 테이블이나 Materialized View를 활용. 또는 분석 목적의 쿼리는 ClickHouse, BigQuery 같은 OLAP 시스템으로 분리

구조적 해법: 인덱스만으로 부족할 때

인덱스 튜닝만으로 한계에 부딪혔을 때 고려할 수 있는 구조적인 해결책임.

  1. 파티셔닝(Partitioning): 특정 기준(시간, 테넌트 ID 등)에 따라 테이블을 물리적으로 분할. 쿼리가 파티션 키를 포함하면 해당 파티션만 읽어 성능 향상
  2. 샤딩(Sharding): 단일 DB 인스턴스의 한계를 넘어섰을 때 여러 DB 서버로 데이터를 수평 분할. 운영 복잡도가 크게 증가하므로 신중한 접근 필요
  3. 캐싱(Caching): 동일한 쿼리가 반복적으로 요청될 때 Redis 등 외부 캐시를 활용하여 DB 부하 감소
  4. 읽기 전용 복제(Read Replica): 읽기 트래픽을 복제된 DB 인스턴스로 분산. 단, 복제본 역시 무거운 집계 쿼리에는 취약할 수 있음

문제 해결을 위한 의사결정 순서

대용량 테이블의 쿼리 성능을 개선할 때 다음 순서로 접근하는 것이 합리적임.

  1. 필터링 결과가 소량이고 선택도가 높은가? (OLTP성 조회)

    • 해결: 복합 인덱스 설계가 1순위. 대부분의 문제가 여기서 해결됨

  2. 쿼리 대부분이 시간/테넌트 등 특정 범위를 포함하는가?

    • 해결: 파티셔닝을 적용해 스캔 범위를 줄이는 전략 고려

  3. 단일 DB의 물리적 한계(용량, IOPS, 쓰기 부하)에 도달했는가?

    • 해결: 읽기 복제본으로 시작해, 최종적으로 샤딩을 통한 수평 확장 고려

  4. 필터링의 목적이 사실상 대량 데이터 분석/집계인가? (OLAP성 조회)

    • 해결: RDB에서 처리하려 하지 말고, 별도의 OLAP 시스템으로 데이터를 이관하여 처리하는 것이 정답

마무리

인덱스를 추가하면 쓰기 성능이 저하되고 저장 공간이 늘어나는 트레이드오프가 존재함. 따라서 모든 쿼리를 만족시키는 인덱스를 만들기보다, 가장 빈번하고 중요한 쿼리 3~5개를 기준으로 최적의 복합 인덱스 최소 세트를 설계하는 것이 중요함. ‘빠른 필터링’을 위해 무작정 샤딩부터 고려하는 것은 과잉 대응일 수 있음. 대부분의 성능 문제는 쿼리 패턴 분석을 통한 인덱스 최적화와 필요시 파티셔닝 적용으로 해결할 수 있음.