ClickHouse의 ORDER BY와 PRIMARY KEY: 인덱스를 올바르게 설정하는 방법
1. ORDER BY가 테이블에서 가장 중요한 설정인 이유
기존 데이터베이스(PostgreSQL, MySQL)에는 클러스터형 인덱스(디스크에서 데이터를 물리적으로 정렬하는 기본 키)와 보조 인덱스(별도의 B-트리)라는 두 가지 개념이 있습니다. 테이블을 다시 만들지 않고도 언제든지 인덱스를 추가하거나 삭제할 수 있습니다.
ClickHouse는 다릅니다. 여기서는 디스크에 데이터의 물리적 순서가 하나만 있습니다. 바로 ORDER BY에 지정한 순서입니다. 그리고 테이블을 다시 만들지 않고는 변경할 수 없습니다. 전혀요. 마치 콘크리트를 부은 후 철근을 잘못 배치한 것을 깨닫는 것과 같습니다. 모든 것을 부수고 다시 시작해야 합니다.
왜 이렇게 엄격할까요? ClickHouse는 컬럼 기반 형식으로 데이터를 저장하며, 높은 압축률을 자랑합니다. 행 순서를 변경하려면 모든 컬럼을 처음부터 다시 써야 합니다. 테라바이트 크기의 테이블을 재구성하는 데 몇 시간 또는 며칠을 기다리고 싶은 사람은 없습니다.
따라서 ORDER BY를 선택하는 것은 전략적 결정입니다. 가장 빈번한 쿼리를 예측하고 키를 설계하여 쿼리가 번개처럼 빠르게 실행되도록 해야 합니다. 실수는 큰 비용을 초래합니다.
실생활 비유: 당신이 사서이고 모든 책을 특정 순서로 선반에 배열해야 한다고 상상해보세요. 장르별로, 그 안에서 저자의 성 순서로 정렬할 수 있습니다. 그러면 해당 기준으로 검색할 때 책을 빠르게 찾을 수 있습니다. 하지만 발행일 순서로 정렬하는 것이 더 편리하다고 결정하면 모든 책을 다시 선반에서 옮겨야 합니다. 몇 시간 동안요.
2. PRIMARY KEY ⊆ ORDER BY — 드문 규칙
ClickHouse에는 두 가지 매개변수가 있습니다:
ORDER BY— 디스크에서 행의 물리적 순서를 정의합니다(필수).PRIMARY KEY— 인덱스를 정의합니다(선택 사항).
그리고 엄격한 규칙이 있습니다: PRIMARY KEY에 나열된 컬럼은 ORDER BY의 첫 번째 컬럼이어야 합니다. 즉, PRIMARY KEY는 ORDER BY의 접두사입니다.
-- ✅ 올바름: PRIMARY KEY는 ORDER BY의 처음 두 컬럼
CREATE TABLE bets
(
user_id UInt64,
created_at DateTime,
amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount) -- 전체 순서
PRIMARY KEY (user_id, created_at); -- 접두사: 처음 두 개-- ❌ 오류: PRIMARY KEY가 접두사가 아님
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id); -- 순서가 다름 — ClickHouse에서 오류 발생-- ⚠️ PRIMARY KEY를 완전히 생략할 수 있음
-- 그러면 자동으로 ORDER BY와 일치함
CREATE TABLE bets
(
user_id UInt64,
created_at DateTime,
amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at); -- PRIMARY KEY = (user_id, created_at)그렇다면 PRIMARY KEY가 단지 접두사라면 왜 필요한가요? 이유는 다음과 같습니다: ClickHouse 인덱스(희소 인덱스)는 PRIMARY KEY의 컬럼에 대해서만 구축됩니다. ORDER BY보다 짧은 PRIMARY KEY를 지정하면 인덱스 메모리를 절약할 수 있지만, 행 순서는 여전히 전체(ORDER BY의 모든 컬럼)입니다. 이는 물리적 순서에 영향을 주는 컬럼이 인덱스에 필요하지 않을 때 유용합니다.
예시: ORDER BY (user_id, created_at, amount)에서 행은 먼저 user_id, 그 다음 created_at, 마지막으로 amount 순서로 정렬됩니다. 하지만 amount로 검색할 필요가 없으므로 PRIMARY KEY (user_id, created_at)이 더 짧고 인덱스가 더 작으며, 물리적 배치가 압축에 도움이 됩니다(동일한 amount가 함께 저장됨).
3. 희소 인덱스: 8192행당 하나의 항목(그래뉼)
ClickHouse의 인덱스를 희소 인덱스라고 합니다. PostgreSQL의 B-트리처럼 각 행에 대한 포인터를 저장하지 않습니다. 대신 8192행마다 하나의 항목을 저장합니다(이 그룹을 그래뉼이라고 함).
내부적으로 어떻게 보이는가:
| 그래뉼 (행 1–8192) | 그래뉼의 첫 번째 행에 대한 PRIMARY KEY 값 |
|---|---|
| 그래뉼 1 | user_id=100, created_at=2025-01-01 00:00:01 |
| 그래뉼 2 | user_id=100, created_at=2025-01-01 10:15:23 |
| 그래뉼 3 | user_id=200, created_at=2025-01-01 00:00:05 |
| ... | ... |
ClickHouse가 데이터를 검색하는 방법:
WHERE user_id = 100 AND created_at >= '2025-01-01'쿼리가 있습니다.- ClickHouse는 희소 인덱스를 보고 그래뉼을 확인합니다.
user_id=100이 그래뉼 1, 2, 그리고 아마도 3 이상에 나타난다는 것을 찾습니다.- 하지만 그래뉼 내에서 원하는 행이 정확히 어디에 있는지 알지 못합니다 — 인덱스는 그래뉼의 시작만 가리키기 때문입니다.
- 따라서 ClickHouse는 필요한 행이 포함될 수 있는 모든 그래뉼을 읽습니다(때로는 필요한 것보다 더 많이 읽기도 함 — 이를 인덱스 필터링이라고 함).
비유: 희소 인덱스는 각 장이 100페이지인 책의 목차와 같습니다. 목차에는 "3장은 201페이지부터 시작"이라고 나와 있습니다. 210페이지에 있는 특정 구절이 필요하면 정확한 위치를 모르기 때문에 여전히 201~300페이지를 모두 읽어야 합니다. PostgreSQL에서는 B-트리 인덱스가 210페이지를 알려줄 것입니다.
ClickHouse에서 이것이 빠른 이유는 무엇인가요? 왜냐하면:
- ClickHouse는 컬럼을 선택적으로 읽습니다. WHERE에
user_id가 필요하고 SELECT에amount가 필요하면 해당 두 컬럼만 읽습니다. - 그래뉼 내의 데이터는 압축되어 있으며, 한 번에 8192행을 읽는 것은 매우 효율적입니다(최소 볼륨 ~64KB, 그래뉼 크기는
index_granularity로 설정 가능). - 분석 쿼리(수백만 행을 읽는)의 경우 이러한 세분성으로 충분합니다.
4. 카디널리티 규칙: 낮은 것 먼저, 그 다음 높은 것
카디널리티는 컬럼의 고유 값 수입니다. 예를 들어:
sport_id(스포츠 유형: 축구, 하키, 테니스) — 카디널리티 20 (낮음)market_id(베팅 시장: 결과, 총점, 핸디캡) — 카디널리티 1000 (중간)created_at(초 단위 시간) — 카디널리티 수십억 (높음)
ClickHouse의 황금률: ORDER BY에서 카디널리티가 낮은 컬럼은 카디널리티가 높은 컬럼 앞에 와야 합니다.
왜 그럴까요? 희소 인덱스가 그래뉼을 더 효과적으로 걸러낼 수 있기 때문입니다.
나쁜 키: ORDER BY (created_at, sport_id)
- 데이터가 먼저 시간 순으로 정렬됩니다. 인접한 행의
sport_id는 축구, 하키, 테니스, 다시 축구...로 이리저리 바뀝니다. WHERE sport_id = 1쿼리는sport_id=1이 테이블 전체에 흩어져 있기 때문에 ClickHouse가 모든 그래뉼을 읽도록 강제합니다.
좋은 키: ORDER BY (sport_id, created_at)
- 먼저 모든 축구 행(
sport_id=1)이 시간 순으로 정렬됩니다. 그 다음 모든 하키 행(sport_id=2)이 모여 있습니다. WHERE sport_id = 1쿼리는 인덱스 수준에서 축구와 관련 없는 모든 그래뉼을 걸러냅니다. ClickHouse는sport_id=1인 그래뉼만 읽습니다.
비유: 카드 덱을 정렬한다고 상상해보세요. 먼저 무늬(낮은 카디널리티 — 4개 값)로 정렬한 다음 숫자(높은 카디널리티 — 13개 값)로 정렬하면 모든 스페이드가 함께 모입니다. 반대로 먼저 숫자로 정렬하면 모든 무늬의 에이스가 덱 전체에 흩어집니다. 모든 스페이드를 찾는 것이 어려워집니다.
5. 베팅 예시: 올바른 ORDER BY 선택 방법
북메이커의 베팅 테이블에 대한 두 가지 옵션을 비교해 보겠습니다.
옵션 A (나쁨): ORDER BY (created_at, sport_id)
CREATE TABLE bets_bad
(
sport_id UInt8, -- 1 = 축구, 2 = 하키, 3 = 테니스
market_id UInt32, -- 베팅 시장 ID
user_id UInt64,
amount Decimal(18,2),
created_at DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);일반적인 쿼리 성능:
-- 쿼리: 지난 1시간 동안의 모든 축구 베팅
SELECT sum(amount) FROM bets_bad
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;
-- EXPLAIN 결과: sport_id=1이 시간대에 걸쳐 흩어져 있어 거의 모든 그래뉼을 읽음인덱스 (created_at, sport_id)는 sport_id가 두 번째 컬럼이기 때문에 도움이 거의 되지 않습니다. ClickHouse는 접두사 created_at을 사용할 수 있지만, sport_id 필터링은 그래뉼 수준에서 수행되어 추가 데이터를 읽어야 합니다.
옵션 B (좋음): ORDER BY (sport_id, market_id, created_at)
CREATE TABLE bets_good
(
sport_id UInt8,
market_id UInt32,
user_id UInt64,
amount Decimal(18,2),
created_at DateTime
)
ENGINE = MergeTree()
ORDER BY (sport_id, market_id, created_at);동일한 쿼리:
-- 쿼리: 지난 1시간 동안의 축구 베팅
SELECT sum(amount) FROM bets_good
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;
-- EXPLAIN 결과: sport_id = 1인 그래뉼만 읽음, 훨씬 적음왜 더 나은가요? ClickHouse는 인덱스를 통해 sport_id = 1인 블록을 즉시 찾을 수 있으며, 해당 블록 내에서 데이터는 market_id와 created_at 순으로 정렬됩니다. 시간 필터 created_at >= ...는 해당 블록 내의 그래뉼 수준에서 적용됩니다.
6. 동등 조건 vs 범위 조건: 어느 것이 더 효율적인가
ORDER BY의 컬럼에는 효율성 계층이 있습니다:
- 동등 조건 (
=) — 가장 효율적입니다. 정확한 값을 검색하면 ClickHouse가 전체 그래뉼 블록을 건너뛸 수 있습니다. - 부등 조건 (
>=,<=,BETWEEN) — 덜 효율적이지만, 키의 마지막 컬럼이면 작동할 수 있습니다. LIKE또는 기타 함수 — 일반적으로 인덱스를 사용하지 않습니다(범위로 변환되지 않는 한).
규칙: ORDER BY에서 동등 조건이 있는 컬럼은 범위 조건이 있는 컬럼 앞에 와야 합니다.
키 (user_id, created_at) 예시:
-- ✅ 좋음: user_id = 동등 조건 (첫 번째 컬럼), created_at >= 범위 (두 번째)
SELECT * FROM bets WHERE user_id = 123 AND created_at >= '2025-06-01';
-- ❌ 나쁨: created_at 범위 (첫 번째 컬럼), user_id = 동등 조건 (두 번째)
-- 인덱스는 created_at으로만 걸러낼 수 있으며, user_id는 그래뉼 내에서 필터링해야 함
SELECT * FROM bets WHERE created_at >= '2025-06-01' AND user_id = 123;왜 그럴까요? 데이터가 (user_id, created_at) 순으로 물리적으로 정렬되어 있기 때문입니다. 한 user_id의 모든 레코드는 압축되어 저장되고, 그 안에서 시간 순으로 정렬됩니다. 시간 범위로 검색하면 쉽습니다. 하지만 먼저 시간으로 검색하면 한 user_id의 레코드가 테이블 전체에 흩어져 있어 인덱스로 걸러낼 수 없습니다.
비유: 먼저 성, 그 다음 이름 순으로 정렬된 전화번호부를 상상해보세요. "모든 김씨"를 찾는 것은 쉽습니다(성이 첫 번째 컬럼). "1990년 이후 출생한 모든 사람"을 찾으려면 전체 전화번호부를 읽어야 합니다.
7. UInt8+UInt32+DateTime 복합 키 vs 단일 DateTime
때로는 "왜 그냥 ORDER BY created_at을 사용하지 않을까? 간단하고 명확한데?"라고 생각할 수 있습니다. 도박 예시로 분석해 보겠습니다.
대시보드가 실제로 필요한 쿼리:
- 지난주 특정 사용자의 베팅:
WHERE user_id = 123 AND created_at >= today() - 7 - 하루 동안의 스포츠별 통계:
WHERE sport_id = 1 AND created_at = yesterday() - 한 시간 동안의 시장별 집계:
WHERE market_id = 100 AND created_at >= now() - 1 hour
옵션 1: ORDER BY (created_at)
CREATE TABLE bets_simple
(
user_id UInt64,
sport_id UInt8,
market_id UInt32,
created_at DateTime
)
ORDER BY created_at;문제점:
user_id로 쿼리하면 느립니다 — 모든 것을 스캔해야 합니다.sport_id로 쿼리해도 마찬가지입니다.
옵션 2: ORDER BY (user_id, sport_id, market_id, created_at)
CREATE TABLE bets_composite
(
user_id UInt64,
sport_id UInt8,
market_id UInt32,
created_at DateTime
)
ORDER BY (user_id, sport_id, market_id, created_at);이제:
WHERE user_id = 123 AND created_at >= ...쿼리 — 훌륭함(접두사user_id사용).WHERE sport_id = 1 AND created_at = ...쿼리 — 나쁨,sport_id가 첫 번째 컬럼이 아니기 때문입니다. ClickHouse는 인덱스에서sport_id로 걸러낼 수 없습니다.
타협: 가장 빈번한 필터 패턴을 선택하고 해당 컬럼을 ORDER BY의 시작 부분에 배치하세요. user_id로 가장 자주 검색한다면 user_id를 먼저 배치하세요. brand_id로 더 자주 검색한다면 그것을 먼저 배치하세요.
경험 법칙: ORDER BY에는 최소 2~4개의 컬럼이 있어야 합니다. 하나의 컬럼은 거의 최적이 아닙니다.
8. EXPLAIN으로 키 효율성 확인 방법
ClickHouse는 인덱스 사용 방식을 분석할 수 있는 강력한 도구를 제공합니다.
EXPLAIN indexes = 1
-- 인덱스 사용 정보 출력 활성화
EXPLAIN indexes = 1
SELECT sum(amount) FROM bets
WHERE user_id = 123 AND created_at >= '2025-06-01';결과는 다음과 같이 표시됩니다:
Expression
...
ReadFromMergeTree
Indexes:
PrimaryKey
Condition: (user_id = 123) AND (created_at >= '2025-06-01')
Used keys: (user_id, created_at)
Granules: 15 / 1280숫자의 의미: 15 / 1280 — 테이블의 1280개 그래뉼 중 15개만 읽었습니다. 훌륭한 결과입니다. 1200 / 1280이 표시되면 인덱스가 거의 도움이 되지 않은 것입니다.
system.query_log
시스템 테이블 query_log는 각 쿼리의 통계를 저장합니다. 인덱스 분석에 가장 유용한 컬럼:
-- 느린 쿼리를 찾고 읽은 행 수 확인
SELECT
query,
read_rows, -- 읽은 행 수
result_rows, -- 반환된 행 수
read_rows / result_rows AS efficiency, -- 1에 가까울수록 좋음
query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish'
AND query LIKE '%bets%'
AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC;해석 방법:
read_rows / result_rows≈ 1..10 — 인덱스가 잘 작동함read_rows / result_rows> 1000 — 하나의 결과를 위해 수천 행을 읽고 있음 — 인덱스 나쁨read_rows가 테이블의 총 행 수에 가까움 — 전체 스캔
system.query_log의 columns_read
SELECT
query,
read_rows,
written_rows,
result_rows,
columns_read, -- 읽은 컬럼 목록
columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;columns_read에 SELECT나 WHERE에 없는 컬럼이 보이면 ClickHouse가 추가 데이터를 읽고 있는 것입니다(아마도 잘못된 ORDER BY 때문).
9. 도박 산업을 위한 패턴
패턴 1: 특정 플레이어별 쿼리
가장 빈번한 쿼리가 "사용자의 베팅 내역 보기"라면 키 (user_id, created_at)이 이상적입니다.
CREATE TABLE bets_by_user
(
user_id UInt64,
created_at DateTime,
sport_id UInt8,
amount Decimal(18,2)
)
ORDER BY (user_id, created_at); -- 한 사용자의 모든 베팅이 압축되어 시간 순으로 정렬됨WHERE user_id = 123 AND created_at BETWEEN ... 쿼리는 해당 사용자의 그래뉼만 읽으며, 그래뉼 수가 적습니다.
패턴 2: 멀티 브랜드 플랫폼
여러 브랜드(casino_A, casino_B)가 있고 쿼리에 거의 항상 brand_id가 포함됩니다. 그렇다면:
CREATE TABLE bets_multi_brand
(
brand_id UInt8, -- 낮은 카디널리티 (5개 브랜드)
user_id UInt64,
created_at DateTime,
amount Decimal(18,2)
)
ORDER BY (brand_id, created_at);WHERE brand_id = 1 AND created_at >= ... 쿼리는 인덱스 수준에서 다른 브랜드의 모든 데이터를 걸러냅니다.
패턴 3: 스포츠별 대시보드
보고서가 sport_id(축구, 하키)로 그룹화되고 시간으로 필터링되는 경우:
CREATE TABLE bets_by_sport
(
sport_id UInt8,
created_at DateTime,
user_id UInt64,
amount Decimal(18,2)
)
ORDER BY (sport_id, created_at);보편적인 키는 없습니다. 가장 빈번한 쿼리 패턴 중 하나 또는 두 개를 선택하고 이에 맞게 최적화해야 합니다. 다른 쿼리는 느려집니다 — 이는 불가피한 트레이드오프입니다.
10. 테이블 생성 후 ORDER BY 변경 — 불가능
이것은 가장 슬프지만 가장 중요한 지식입니다. ALTER와 같은 명령으로 기존 테이블의 ORDER BY나 PRIMARY KEY를 변경할 수 없습니다.
-- ❌ 이런 것은 존재하지 않음
ALTER TABLE bets MODIFY ORDER BY (new_column, created_at); -- ERROR!왜 그럴까요? 행의 물리적 순서가 이미 결정되었기 때문입니다. 변경하려면 테이블을 다시 만들어야 합니다.
실수를 깨달았을 때 어떻게 해야 할까요?
방법 1: 새 테이블 생성, 데이터 마이그레이션, 이름 변경
-- 1. 올바른 ORDER BY로 새 테이블 생성
CREATE TABLE bets_new
(
user_id UInt64,
created_at DateTime,
amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at); -- 새 키
-- 2. 데이터 마이그레이션 (테이블이 크면 비동기 가능)
INSERT INTO bets_new SELECT * FROM bets;
-- 3. 테이블 교체 (원자적 연산)
RENAME TABLE bets TO bets_old, bets_new TO bets;
-- 4. 모든 것이 잘 작동하는지 확인한 후 이전 테이블 삭제
DROP TABLE bets_old;방법 2: 구체화된 뷰 사용 (데이터를 두 가지 순서로 동시에 저장할 수 있는 경우)
-- 일부 쿼리를 위해 이전 테이블 유지
-- 다른 쿼리를 위해 다른 ORDER BY로 구체화된 뷰 생성
CREATE MATERIALIZED VIEW bets_by_sport_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM bets; -- 데이터가 중복됨방법 3: 받아들이고 나쁜 키와 함께 살기 (때로는 테라바이트를 마이그레이션하는 것보다 리소스를 늘리는 것이 더 저렴함)
조언: 대량의 데이터(수십억 행)가 있는 테이블을 생성하기 전에 항상 샘플에서 ORDER BY를 테스트하세요. 1000만 행으로 복사본을 만들고 EXPLAIN indexes=1을 실행하고 다양한 쿼리를 테스트하세요. 이렇게 하면 나중에 몇 주간의 고통을 덜 수 있습니다.
다음 단계
이제 ClickHouse에서 ORDER BY가 단순한 정렬이 아니라 전략적 인덱스라는 것을 이해했습니다. 다음 주제:
- index_granularity 구성 방법 — 그래뉼 크기를 8192에서 다른 값으로 변경(거의 필요 없음).
- 파티셔닝 vs ORDER BY — 파티션이 도움이 되는 경우와 인덱스가 도움이 되는 경우.
- 건너뛰기 인덱스(블룸 필터 인덱스) — ORDER BY에 없는 컬럼을 위한 보조 인덱스.
- system.query_log를 통한 느린 쿼리 분석 — 심층 프로파일링.
결론: ClickHouse에서 이상적인 ORDER BY의 공식: 낮은 카디널리티와 동등 조건이 있는 컬럼이 먼저 오고, 그 다음 높은 카디널리티와 범위 조건이 있는 컬럼이 옵니다. 모든 것을 다루려고 하지 말고 가장 빈번한 쿼리를 선택하고 나머지는 무시하세요. 그리고 EXPLAIN indexes=1을 절대 잊지 마세요 — ClickHouse 개발자의 가장 친한 친구입니다.
← 이전 글: ClickHouse 파티셔닝: 폴더 수준에서 데이터 관리하기
→ 다음 글: ClickHouse의 TTL: 자동 데이터 수명 주기 관리
— Editorial Team
아직 댓글이 없습니다.