[MySQL] COUNT(DISTINCT) 임시 테이블 비용과 사전 집계 최적화 기준

COUNT(DISTINCT): 실무적 정의와 운영상 중요성

MySQL에서 COUNT(DISTINCT expression)은 조건을 만족하는 행에서 중복되지 않은 값의 개수를 반환하는 집계 함수이다. 일반 COUNT(*)와 달리 입력값의 중복 여부를 판별해야 하므로 정렬, 임시 자료구조 또는 인덱스 기반 접근이 추가될 수 있다.

고유값의 수와 집계 대상 행이 증가하면 중복 제거 상태도 커진다. 이 상태가 메모리 안에서 처리되지 못하면 내부 임시 테이블의 디스크 전환과 추가 I/O가 발생할 수 있다. 운영 환경에서는 긴 응답 시간에 그치지 않고 동시 쿼리의 버퍼 풀 경쟁과 디스크 I/O 병목으로 이어진다.

고유값 집계에서 임시 테이블 사용을 줄이는 순서는 명확하다. 먼저 WHERE 절로 읽는 행을 줄이고, 필터와 고유값 컬럼을 포함한 인덱스로 불필요한 테이블 접근을 제거한다. 이후 반복되는 중복을 사전 집계하고, 동일 집계를 자주 수행한다면 영구 요약 테이블로 계산 시점을 쓰기 경로으로 이동해야 한다.

Part 1. COUNT DISTINCT 기본 문법 및 사용법 (Basic Syntax)

일별 고유 사용자 수를 계산하는 기본 쿼리는 다음과 같다. COUNT(DISTINCT user_id)는 NULL을 제외하고 서로 다른 user_id만 계산한다.

SELECT event_date,
       COUNT(DISTINCT user_id) AS unique_users
FROM raw_events
WHERE event_date >= '2026-07-01'
  AND event_date <  '2026-08-01'
GROUP BY event_date
ORDER BY event_date;

날짜 조건은 반개방 구간으로 작성하는 것이 안전하다. DATETIME 컬럼이라면 월말의 시·분·초를 별도로 계산할 필요가 없고, 범위 검색도 유지된다. 컬럼에 DATE() 함수를 적용하면 일반 인덱스의 범위 접근이 제한될 수 있으므로 피해야 한다.

-- 가장 흔한 안티 패턴
WHERE DATE(event_time) = '2026-07-21'

-- 범위 검색으로 변경
WHERE event_time >= '2026-07-21 00:00:00'
  AND event_time <  '2026-07-22 00:00:00'

다중 컬럼 고유 조합도 계산할 수 있지만 의미를 먼저 확정해야 한다. MySQL의 다중 표현식 형식은 표현식 중 하나라도 NULL인 조합을 제외할 수 있으므로, 업무 규칙상 NULL을 하나의 값으로 취급해야 한다면 명시적인 치환 규칙과 충돌하지 않는 값이 필요하다.

SELECT COUNT(DISTINCT tenant_id, user_id)
FROM raw_events
WHERE event_date = '2026-07-21';

CONCAT(tenant_id, ':', user_id)로 조합을 만드는 방식은 구분자 충돌, 문자 변환, 추가 CPU 사용을 유발한다. 가능한 경우 다중 표현식 문법을 사용하고, 조합의 NULL 처리 규칙을 별도로 검증해야 한다.

Part 2. 내부 동작 메커니즘과 안티 패턴

옵티마이저는 통계, 인덱스, 조건 선택도와 집계 구조를 바탕으로 실행 방식을 결정한다. 따라서 모든 COUNT(DISTINCT)가 반드시 동일한 형태의 디스크 임시 테이블을 생성한다고 단정할 수 없다. 핵심은 실행 계획의 추정만으로 판단하지 않고 세션 상태값과 실제 실행을 함께 확인하는 것이다.

입력 행이 많고 고유값 수도 크면 중복 제거에 필요한 작업 영역이 증가한다. 넓은 문자열이나 복합 표현식을 고유 키로 사용하면 같은 행 수에서도 키 보관 비용과 비교 비용이 커진다. 이 과정에서 메모리 한도를 넘은 내부 임시 데이터가 디스크 기반 처리로 전환되면 지연과 I/O가 증가한다.

가장 치명적인 안티 패턴은 전체 이력에서 먼저 고유값을 계산한 뒤 외부 쿼리에서 기간이나 테넌트를 제한하는 구조이다. 필터가 내부로 안전하게 밀려 들어간다는 보장이 없거나 표현식 때문에 조건 이동이 제한되면, 필요하지 않은 행까지 중복 제거 대상이 된다.

-- 불필요한 전체 이력을 먼저 집계할 위험이 있는 구조
SELECT event_date, COUNT(*)
FROM (
    SELECT DISTINCT event_date, user_id
    FROM raw_events
) AS d
WHERE event_date >= '2026-07-01'
  AND event_date <  '2026-08-01'
GROUP BY event_date;

필터는 가능한 한 원본 테이블을 읽는 단계에 배치해야 한다. 이렇게 하면 파생 테이블로 전달되는 행이 감소하고 중복 제거 대상도 함께 줄어든다.

SELECT event_date, COUNT(*) AS unique_users
FROM (
    SELECT event_date, user_id
    FROM raw_events
    WHERE event_date >= '2026-07-01'
      AND event_date <  '2026-08-01'
    GROUP BY event_date, user_id
) AS deduplicated
GROUP BY event_date
ORDER BY event_date;

위 쿼리는 원본 이벤트를 (event_date, user_id) 단위로 먼저 축소한다. 다만 단일 문장 안에서 파생 테이블을 추가했다고 해서 임시 테이블이 자동으로 감소하는 것은 아니다. 원본에 중복이 적거나 옵티마이저가 기존 COUNT(DISTINCT)를 효율적으로 처리한다면 단계만 늘어날 수 있으므로 실측이 필요하다.

EXPLAIN의 Using temporary 표시만으로 내부 임시 테이블의 전체 사용량을 확정해서도 안 된다. 실행 계획은 접근 방식 확인에 사용하고, 실제 디스크 임시 테이블 변화는 동일 세션의 Created_tmp_disk_tables 전후 차이로 확인한다. 다른 쿼리를 같은 세션에서 실행하면 상태값이 함께 누적되므로 측정 구간을 분리해야 한다.

Part 3. 성능 최적화 및 튜닝 전략

1. 필터 컬럼과 고유값 컬럼을 인덱스에 배치한다

일별 범위 검색과 사용자 중복 제거가 핵심이라면 (event_date, user_id) 인덱스가 우선 검토 대상이다. 날짜 범위에서 필요한 user_id를 인덱스만으로 읽을 가능성이 생기며, 넓은 payload 페이지 접근을 줄일 수 있다.

CREATE INDEX ix_raw_events_date_user
    ON raw_events (event_date, user_id);

인덱스가 있다고 해서 집계용 작업 영역이 항상 사라지는 것은 아니다. 그러나 읽는 페이지와 중간 입력량을 줄이는 기반이 된다. 커버링 인덱스의 원리와 과도한 인덱스 비용은 MySQL 커버링 인덱스 설계에서 함께 확인할 수 있다.

테넌트 등가 조건과 날짜 범위 조건을 함께 사용한다면 (tenant_id, event_date, user_id)처럼 등가 조건을 선행 컬럼에 두는 방식을 검토한다. 컬럼 순서는 쿼리 패턴마다 달라지므로 MySQL 복합 인덱스 컬럼 순서 원칙에 따라 실제 조건과 정렬 요구를 확인해야 한다.

2. 사전 집계는 중복률과 재사용 횟수로 결정한다

사전 집계의 효과는 원본 행 수 대비 고유 조합 수가 충분히 작을 때 커진다. 한 사용자가 하루에 여러 이벤트를 생성한다면 (event_date, user_id) 단계에서 입력이 크게 축소될 수 있다. 반대로 사용자당 이벤트가 거의 하나라면 사전 집계 결과의 행 수도 원본과 비슷해져 추가 집계 비용만 발생할 수 있다.

동일한 고유값 집계를 여러 보고서가 반복한다면 영구 요약 테이블이 더 적합하다. 조회 시마다 중복 제거를 반복하지 않고 적재 과정에서 유일성을 보장한다.

CREATE TABLE daily_unique_users (
    event_date DATE NOT NULL,
    user_id BIGINT NOT NULL,
    PRIMARY KEY (event_date, user_id)
) ENGINE=InnoDB;

INSERT IGNORE INTO daily_unique_users (event_date, user_id)
SELECT event_date, user_id
FROM raw_events
WHERE event_date >= ?
  AND event_date <  ?;

SELECT event_date, COUNT(*) AS unique_users
FROM daily_unique_users
WHERE event_date >= ?
  AND event_date <  ?
GROUP BY event_date;

이 방식은 읽기 비용을 줄이는 대신 쓰기 증폭, 추가 저장 공간, 재처리 절차가 필요하다. 지연 도착 데이터와 수정 이벤트가 존재하면 해당 날짜 파티션을 다시 적재하거나 멱등성 있는 증분 반영 절차를 설계해야 한다. 원본과 요약 테이블의 집계 결과를 정기적으로 비교하지 않으면 데이터 정합성 문제가 장기간 숨을 수 있다.

3. 임시 테이블 한도 증설은 마지막 수단으로 사용한다

메모리 관련 한도를 높이면 일부 작업의 디스크 전환을 늦출 수 있지만 근본적인 입력량은 줄지 않는다. 연결 수가 많은 운영 환경에서 세션별 작업 메모리가 동시에 증가하면 전체 메모리 고갈을 유발할 수 있다. DBA는 서버 전역 값을 먼저 높이지 말고 쿼리 구조와 인덱스를 개선한 뒤 동시 실행량을 포함해 판단해야 한다.

특히 넓은 문자열을 그대로 고유값으로 계산하는 쿼리는 식별자 정규화를 우선 검토한다. 안정적인 숫자 키로 치환할 수 있다면 비교 키가 좁아지고 인덱스 설계도 단순해진다. 해시값만으로 정확한 고유 개수를 계산하면 충돌 가능성이 생기므로 원본 값과의 정합성 보장 없이 대체해서는 안 된다.

4. 같은 세션에서 상태값과 결과를 함께 검증한다

1. 측정 전 별도의 연결을 열고 테스트 대상 쿼리 외의 문장을 실행하지 않는다.
2. SHOW SESSION STATUS LIKE 'Created_tmp%'; 결과를 기록한다.
3. 원본 COUNT(DISTINCT) 쿼리를 실행하고 실행 시간과 결과 집합을 저장한다.
4. 동일 세션 상태값을 다시 조회해 Created_tmp_disk_tables와 Created_tmp_tables의 증가분을 계산한다.
5. 새 연결에서 커버링 인덱스 쿼리와 사전 집계 쿼리를 각각 같은 방법으로 측정한다.
6. 날짜별 결과 행 수와 고유 사용자 수가 모두 같은지 검증한다.
7. EXPLAIN ANALYZE로 실제 행 수, 반복 횟수와 단계별 시간을 확인한다.

캐시 상태와 동시 부하에 따라 실행 시간은 달라질 수 있다. 각 방식을 여러 차례 교대로 실행하고 중앙값을 비교하되, 운영 서버에서 버퍼 풀을 비우거나 전역 캐시를 조작해서는 안 된다. 실행 계획의 추정치와 실제 행 수 차이를 읽는 절차는 MySQL EXPLAIN ANALYZE 카디널리티 오차 진단를 참고할 수 있다.

비교 방식	인덱스 및 집계 구조	판단 기준
원본 COUNT DISTINCT	보조 인덱스 없이 직접 집계	기준 쿼리이며 전체 테이블 스캔과 디스크 전환 여부를 확인한다.
커버링 인덱스 COUNT DISTINCT	(event_date, user_id) 인덱스에서 직접 집계	테이블 접근 감소 효과와 인덱스 유지 비용을 함께 판단한다.
커버링 인덱스 사전 GROUP BY	기간 필터 후 (event_date, user_id) 사전 중복 제거	중복률이 높고 축소 결과가 재사용될 때 적용한다.

COUNT DISTINCT 최적화 판단 기준

일회성 쿼리라면 먼저 조건절을 SARGable하게 작성하고 커버링 인덱스를 검토한다. 반복 보고서이면서 원본 중복률이 높다면 파생 테이블 사전 집계를 비교한다. 같은 결과를 여러 서비스가 지속적으로 조회한다면 영구 요약 테이블과 정합성 검증 절차가 필요하다.

성능 최적화의 핵심은 임시 테이블 한도를 무조건 높이는 것이 아니라 중복 제거 단계로 들어가는 행과 키의 폭을 줄이는 것이다. 컬럼 가공을 피하고, 범위 검색과 인덱스를 활용하며, Created_tmp_disk_tables, 실행 시간, 결과 동일성을 같은 조건에서 검증해야 한다. 마지막으로 EXPLAIN ANALYZE를 통해 실제 처리 행을 확인하는 것이 운영 환경의 장애 위험을 줄이는 원칙이다.

FAQ

Created_tmp_disk_tables가 0이면 COUNT DISTINCT 비용이 없는가?

그렇지 않다. 메모리 내부의 중복 제거, 인덱스 스캔, 정렬과 CPU 비교 비용은 남을 수 있다. 디스크 임시 테이블 증가분과 함께 실행 시간, 읽은 행, 실행 계획을 확인해야 한다.

사전 GROUP BY가 항상 COUNT DISTINCT보다 빠른가?

항상 빠르지 않다. 파생 결과를 생성하고 다시 집계하는 단계가 추가되므로 중복률이 낮으면 오히려 불리할 수 있다. 동일 데이터와 인덱스 조건에서 실제 측정으로 결정해야 한다.

메모리 한도만 높이면 문제가 해결되는가?

단일 쿼리의 디스크 전환은 줄어들 수 있지만 동시 실행 시 전체 메모리 사용량이 증가한다. 필터 축소, 적절한 인덱스, 사전 집계와 요약 테이블을 먼저 검토해야 한다.