Primary Index 와 ORDER BY 설계

ClickHouse 분류

Core Architecture

Type

Introduction

작성자

Ken

OLTP 데이터베이스에서 넘어온 사용자들은 인덱스에 대한 몇 가지 직관을 갖고 옵니다. "인덱스는 row를 정확히 찾아주는 것", "primary key는 유니크한 것", "인덱스를 더 만들면 빨라지는 것". ClickHouse에서는 셋 다 통하지 않습니다. ClickHouse의 primary index는 row가 아니라 granule(행 묶음)을 건너뛰기 위한 sparse index이고, 유니크 제약이 없으며, 그 설계는 인덱스 추가가 아니라 ORDER BY 절 하나로 결정됩니다. [지난 글](파티션 편)에서 "조회 성능은 파티션이 아니라 ORDER BY가 담당한다"고 했는데, 이번 글에서는 그 ORDER BY를 어떻게 설계해야 하는지를 다룹니다.

Part 내부 구조: granule과 sparse index

MergeTree의 각 Part는 컬럼별 파일로 저장되고, 데이터는 ORDER BY 키 순서로 정렬되어 있습니다. 정렬된 데이터는 index_granularity(기본 8,192행) 단위의 granule로 나뉘며, primary index(primary.idx)는 **granule당 1개의 엔트리(mark)**만 갖습니다. row마다 엔트리를 갖는 B-tree와의 근본적 차이가 여기 있습니다.

이 구조에서 파생되는 특성들:

인덱스가 극단적으로 작습니다. 1억 행이면 마크가 약 12,000개(1억 ÷ 8,192)뿐이라, 수십억 행 테이블도 primary index가 MB 단위로 메모리에 통째로 올라갑니다.
조회의 최소 단위는 row가 아니라 granule입니다. 조건에 맞는 row가 1개여도 그 row가 속한 granule 전체(8,192행)를 읽고 필터링합니다. ClickHouse가 point lookup보다 범위 스캔·집계에 최적화된 이유입니다.
정렬이 곧 압축입니다. ORDER BY 순서로 유사한 값이 물리적으로 인접하므로 압축률이 크게 올라갑니다. ORDER BY는 인덱스 설계이자 스토리지 비용 설계이기도 합니다.
유니크 제약이 없습니다. PRIMARY KEY는 정렬과 스킵의 기준일 뿐, 중복 방지는 별개 문제(ReplacingMergeTree 등)입니다.

쿼리가 들어오면 ClickHouse는 WHERE 조건을 마크 배열에 대한 이진 탐색으로 변환해 읽어야 할 granule 범위를 정하고, 나머지는 건너뜁니다. 즉 성능의 관건은 "내 쿼리의 WHERE 조건이 정렬 순서의 앞부분과 얼마나 일치하는가"입니다.

ORDER BY 설계 원칙

원칙 1: 자주 쓰는 필터 컬럼을, 왼쪽 접두사(prefix)가 활용되도록

primary index는 복합 키의 왼쪽 접두사 순서로 동작합니다. ORDER BY (a, b, c)일 때 WHERE a = ..., WHERE a = ... AND b = ...는 인덱스를 잘 타지만, WHERE c = ...만 있는 쿼리는 사실상 풀스캔에 가깝습니다(뒤 컬럼 단독으로는 제한적인 generic exclusion만 가능). 따라서 첫 번째 질문은 "이 테이블의 쿼리들이 거의 항상 거는 필터가 무엇인가"입니다.

원칙 2: 카디널리티 낮은 컬럼을 앞에

같은 필터 컬럼들이라도 순서는 카디널리티 오름차순이 기본입니다.

-- 권장: 저카디널리티 → 고카디널리티
ORDER BY (tenant_id, event_type, event_time)
--        수백 개      수십 개      수억 개

-- 비권장: 고카디널리티가 앞
ORDER BY (event_time, tenant_id, event_type)

이유는 두 가지입니다. 첫째, 앞 컬럼의 카디널리티가 낮아야 뒤 컬럼들이 granule 안에서 정렬 상태를 유지합니다. 앞 컬럼이 초 단위 timestamp면 같은 granule 안에서 tenant_id가 뒤죽박죽이 되어, tenant_id 필터가 granule을 거의 배제하지 못합니다. 둘째, 뒤 컬럼일수록 유사 값이 뭉치지 않아 압축률도 떨어집니다.

원칙 3: 시간 컬럼은 (대개) 뒤에

시계열 테이블에서 가장 흔한 실수가 ORDER BY (event_time, ...)입니다. 시간이 맨 앞이면 다른 모든 필터가 무력화되는데, 정작 시간 범위 필터는 ORDER BY 앞자리가 아니어도 이미 두 겹의 안전망이 있습니다 — 파티션 pruning(월 파티션), 그리고 Part 단위 min/max(시계열은 시간 순으로 적재되므로 Part 자체가 시간 구간별로 나뉨). 그래서 (tenant_id, event_type, event_time)처럼 시간을 마지막에 두면, 디멘전 필터는 인덱스 앞자리로, 시간 범위는 파티션/minmax/키 후반부로 각각 처리되어 모든 축이 삽니다.

예외는 있습니다. 쿼리의 대부분이 디멘전 필터 없이 "최근 N분 전체" 스캔이라면(모니터링 대시보드 등) 시간이 앞에 오는 것이 맞을 수 있습니다. 원칙은 "규칙 암기"가 아니라 지배적인 쿼리 패턴이 정렬 순서를 결정한다는 것입니다.

원칙 4: 키는 3~5개 안에서, 고카디널리티 유니크 값은 넣지 말 것

ORDER BY 컬럼이 많아질수록 뒤 컬럼의 실효성은 급감하고 INSERT 시 정렬 비용만 늘어납니다. 실무 적정선은 3~5개입니다.
UUID, trace_id 같은 사실상 유니크한 컬럼을 키 앞쪽에 두면 sparse index의 전제(유사 값의 물리적 인접)가 무너집니다. 이런 컬럼의 point lookup이 필요하면 ORDER BY가 아니라 뒤에 나올 skipping index(bloom_filter)의 영역입니다.

원칙 5: PRIMARY KEY와 ORDER BY의 분리

기본적으로 PRIMARY KEY = ORDER BY지만, 둘을 분리할 수 있습니다. PRIMARY KEY는 ORDER BY의 접두사여야 하며, 인덱스 메모리에는 PRIMARY KEY 컬럼만 올라갑니다.

CREATE TABLE events (...)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time, session_id)  -- 정렬(압축·중복제거 목적)은 넓게
PRIMARY KEY (tenant_id, event_type);                       -- 인덱스 메모리는 좁게

필터로 쓰지 않는 컬럼이 정렬 목적(압축, SummingMergeTree/ReplacingMergeTree의 키)으로만 ORDER BY에 들어가 있다면, PRIMARY KEY를 앞부분으로 줄여 인덱스 메모리를 절감할 수 있습니다.

설계 검증하기

감이 아니라 숫자로 확인합니다. 핵심 도구는 EXPLAIN indexes = 1입니다.

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE tenant_id = 42
  AND event_time >= now() - INTERVAL 1 DAY;

Partition
  Parts: 2/48
MinMax
  Parts: 2/2
PrimaryKey
  Keys: tenant_id
  Granules: 118/24576     -- 24,576개 중 118개만 읽음

Granules 비율이 곧 인덱스 설계의 성적표입니다. 필터가 충분히 선택적인데도 granule이 거의 걸러지지 않는다면 ORDER BY 순서가 쿼리 패턴과 어긋나 있다는 뜻입니다. 함께 볼 것들:

-- 인덱스 메모리 사용량 (PRIMARY KEY 분리 효과 확인)
SELECT table,
       formatReadableSize(sum(primary_key_bytes_in_memory)) AS pk_mem
FROM system.parts
WHERE active
GROUP BY table
ORDER BY sum(primary_key_bytes_in_memory) DESC;

-- 실제 쿼리들의 읽은 행 수 (설계 변경 전후 비교)
SELECT normalized_query_hash,
       count() AS cnt,
       avg(read_rows) AS avg_read_rows,
       avg(query_duration_ms) AS avg_ms
FROM system.query_log
WHERE type = 'QueryFinish' AND query_kind = 'Select'
  AND event_date >= today() - 7
GROUP BY normalized_query_hash
ORDER BY avg_read_rows DESC
LIMIT 20;

ORDER BY는 테이블 생성 후 변경할 수 없으므로(신규 테이블 + 재적재 필요), 설계 단계에서 대표 쿼리 목록을 놓고 위 검증을 거치는 것이 가장 저렴합니다.

ORDER BY로 못 잡는 컬럼: data skipping index

ORDER BY에 넣지 못한 컬럼의 필터가 자주 쓰인다면 보조 수단으로 data skipping index가 있습니다.

-- 고카디널리티 ID의 point lookup
ALTER TABLE events ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 4;

-- 값 범위가 지역적으로 뭉치는 수치 컬럼
ALTER TABLE events ADD INDEX idx_amount amount TYPE minmax GRANULARITY 4;

단, skipping index는 B-tree 보조 인덱스처럼 "찾아가는" 것이 아니라 여전히 "granule을 건너뛰는" 장치라서, 해당 컬럼 값이 물리적 배치와 상관관계가 있을 때만 효과가 있습니다. 정렬과 무관하게 흩어진 컬럼에 minmax를 걸면 모든 granule의 min/max 범위에 값이 걸려 아무것도 스킵하지 못합니다. "인덱스 추가 = 성능 향상"이라는 OLTP 직관이 가장 크게 배신당하는 지점이므로, 추가 후 반드시 EXPLAIN indexes = 1로 실제 스킵 효과를 확인해야 합니다.

핵심 체크리스트

✅ 설계

대표 쿼리의 WHERE 패턴부터 수집 — ORDER BY는 쿼리가 결정
저카디널리티 → 고카디널리티 순서, 시간 컬럼은 대개 마지막
키는 3~5개, UUID류 유니크 컬럼은 키에서 배제
정렬 전용 컬럼이 있으면 PRIMARY KEY 분리로 인덱스 메모리 절감

✅ 검증

EXPLAIN indexes = 1로 granule 스킵 비율 확인
system.query_log의 read_rows로 설계 변경 전후 비교
skipping index는 추가 후 실효성 검증 필수

✅ 운영

ORDER BY 변경 = 테이블 재생성이므로 설계 단계 검증에 투자
쿼리 패턴이 크게 달라졌다면 신규 테이블 + 파티션 단위 재적재로 마이그레이션

결론

ClickHouse에서 조회 성능의 8할은 ORDER BY 설계에서 결정됩니다. primary index는 row를 찾는 장치가 아니라 granule을 건너뛰는 장치이고, 그 효율은 "쿼리의 필터 조건이 물리적 정렬 순서와 얼마나 일치하는가"에 달려 있습니다. 저카디널리티 디멘전을 앞에, 시간을 뒤에, 유니크 값은 배제 — 이 기본형에서 출발하되, 최종 판단은 항상 대표 쿼리와 EXPLAIN indexes = 1의 granule 숫자로 하시기 바랍니다.