쿼리 최적화와 실행계획 읽기

EXPLAIN 변형 일람

ClickHouse의 EXPLAIN은 단일 명령이 아니라 목적이 다른 여러 모드의 묶음이다.

실무에서 가장 많이 쓰는 조합은 두 가지다.

-- 인덱스가 얼마나 데이터를 줄이는지 확인
EXPLAIN indexes = 1 SELECT ...;

-- 프로세서 병렬도와 병목 위치 확인
EXPLAIN PIPELINE SELECT ...;

EXPLAIN indexes=1 읽기

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE event_date >= '2024-01-15' AND user_id = 42;

출력 예시:

ReadFromMergeTree (default.events)
  Indexes:
    PrimaryKey
      Keys: event_date, user_id
      Condition: and((event_date >= '2024-01-15'), (user_id = 42))
      Parts: 3/10
      Granules: 45/2000

핵심 숫자 해석:

• Parts 3/10: 전체 10개 파트 중 3개만 읽는다. 파티션 프루닝 결과다.
• Granules 45/2000: 스파스 인덱스가 2,000개 그래뉼을 45개(2.25%)로 좁혔다. 작을수록 인덱스가 효과적이다.
• 비율이 100%에 가까우면 사실상 전체 스캔. PRIMARY KEY 설계를 재검토해야 한다.

Skip Index가 있으면 같은 출력에 추가로 나타난다.

    SkipIndex (idx_type)
      Parts: 3/3
      Granules: 12/45

Skip Index가 PrimaryKey 이후에도 그래뉼을 추가로 줄이고 있음을 보여준다.

PREWHERE 자동 최적화

WHERE 절 필터가 일부 컬럼만 참조하고 SELECT는 많은 컬럼을 읽는 경우, ClickHouse는 자동으로 PREWHERE를 적용한다.

PREWHERE 동작 순서:

1. 필터 컬럼만 먼저 읽어 적합한 그래뉼을 선별한다.
2. 선별된 그래뉼에서만 나머지 SELECT 컬럼을 읽는다.

-- ClickHouse가 내부적으로 PREWHERE로 최적화
SELECT event_type, value, extra_col1, extra_col2
FROM events
WHERE user_id = 42;

optimize_move_to_prewhere = 1(기본값)이 활성화되어 있으면 ClickHouse가 WHERE 조건 중 PREWHERE로 옮길 후보를 자동 선택한다. 명시적으로 제어하고 싶을 때만 직접 쓴다.

SELECT event_type, value
FROM events
PREWHERE user_id = 42    -- 이 필터 먼저 평가, 그래뉼 추려낸 뒤
WHERE event_date >= '2024-01-01';  -- 이후 나머지 조건 평가

SELECT 컬럼 최소화

ClickHouse는 열 지향 저장소다. SELECT *는 모든 컬럼 파일을 디스크에서 읽는다.

-- 나쁨: 컬럼이 30개라면 30개 파일 모두 읽음
SELECT * FROM events WHERE user_id = 42;

-- 좋음: 필요한 2개 컬럼만 읽음
SELECT event_type, value FROM events WHERE user_id = 42;

컬럼이 많은 넓은 테이블일수록 효과가 크다. 실제 I/O 감소량은 (선택 컬럼 수 / 전체 컬럼 수)에 비례한다.

인덱스 컬럼에 함수 적용 금지

스파스 인덱스는 정렬된 원본 값과 비교한다. 컬럼에 함수를 적용하면 인덱스를 사용하지 못하고 전체 스캔이 된다.

-- 나쁨: toStartOfMonth가 인덱스 탐색을 막음
WHERE toStartOfMonth(event_date) = '2024-01-01'

-- 좋음: 범위 조건으로 변환
WHERE event_date >= '2024-01-01' AND event_date < '2024-02-01'

같은 이유로 toString(numeric_col) = '42'보다 numeric_col = 42가, lower(name) = 'alice'보다 전처리된 소문자 컬럼을 따로 두는 것이 빠르다.

Projection으로 다른 정렬 키 지원

ORDER BY를 바꾸지 않고도 특정 쿼리 패턴에 맞는 정렬이나 미리 집계된 데이터를 같은 파트 안에 보조로 저장할 수 있다.

-- 기본 테이블은 (event_date, user_id) 정렬
-- event_type 기준 집계를 자주 쿼리한다면:
ALTER TABLE events
  ADD PROJECTION proj_by_type (
    SELECT event_type, count(), sum(value)
    GROUP BY event_type
  );

ALTER TABLE events MATERIALIZE PROJECTION proj_by_type;

쿼리가 Projection과 일치하면 ClickHouse가 자동으로 Projection 데이터를 읽는다. 쿼리 변경 없이 속도가 올라간다.

system.query_log로 느린 쿼리 찾기

SELECT
    query,
    read_rows,
    read_bytes,
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
  AND event_date = today()
ORDER BY query_duration_ms DESC
LIMIT 20;

read_bytes가 크면서 result_rows가 작으면 인덱스 효율이 낮다는 신호다. 해당 쿼리를 EXPLAIN indexes = 1로 분석해 구체적인 Granules 통계를 확인한다.

최적화 체크리스트

1. EXPLAIN indexes=1 — Granules 비율 확인. 10% 미만이 목표.
2. ORDER BY 앞쪽 컬럼부터 쿼리 필터가 매칭되는지 확인.
3. 필터 컬럼에 함수 적용 제거.
4. SELECT * 대신 필요한 컬럼만 명시.
5. 반복 집계 패턴은 Projection 또는 Materialized View 적용.
6. system.query_log로 정기 슬로우 쿼리 리뷰.

한 줄 정리

ClickHouse 쿼리 최적화의 출발점은 EXPLAIN indexes=1로 Granules 비율을 보는 것이고, PRIMARY KEY 설계와 PREWHERE·Projection이 실제 I/O를 줄이는 세 가지 주요 레버다.

References

• https://clickhouse.com/docs/sql-reference/statements/explain
• https://clickhouse.com/blog/a-simple-guide-to-clickhouse-query-optimization-part-1
• https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes
• https://www.glassflow.dev/blog/clickhouse-query-optimization
• https://girff.medium.com/clickhouse-performance-optimization-best-practices-for-ultra-fast-analytics-567afb976db1
• https://clickhouse.com/docs/sql-reference/statements/alter/projection
• https://kb.altinity.com/altinity-kb-queries-and-syntax/explain-query/
• https://www.e6data.com/query-and-cost-optimization-hub/how-to-optimize-clickhouse-query-performance