LabHub
배우기 러닝패스 코스

ClickHouse — A Columnar Analytics Database from the Inside

Count skipped granules while changing sort-key order

LabHub 에서 이어서 보기

한국어 원문으로 표시합니다.

목표

같은 200만 행을 정렬 키가 다른 여러 표에 넣고, EXPLAIN indexes = 1 의 그래뉼 수와 rows_read 로 성긴 기본 인덱스가 무엇을 건너뛰는지 확인한다. 마지막에 주어진 쿼리에 맞는 정렬 키를 직접 고른다.

왜 중요한가

ClickHouse 표의 정렬 키는 만든 뒤에 바꾸기 어렵고, 잘못 고르면 인덱스가 있어도 매번 전부 읽는다. 어느 열을 앞에 둘지는 감이 아니라 "이 쿼리는 몇 그래뉼을 고르나" 로 정한다. 이 실습의 채점기는 여러분이 적은 숫자를 믿지 않는다 — 여러분이 저장한 SELECT 에 EXPLAIN 을 붙여 읽기 전용으로 다시 돌리고, 같은 SELECT 를 다시 실행해 읽은 행을 재서 대조한다.

단계

  1. 데이터베이스 sparse 와 표 sparse.hits 를 만드세요 — 열 site LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32 (이 순서), 엔진 MergeTree, 정렬 키 ORDER BY (site, user_id).
  2. /opt/lab/fixtures/sparse/hits.sql 을 한 번 실행해 200만 행을 넣고 OPTIMIZE TABLE sparse.hits FINAL 로 파트를 하나로 합치세요.
  3. site = 'docs.example' 인 행의 dur_ms 합을 내는 /root/ch/sparse/q_site.sql 을 만들고, 그 SELECT 에 EXPLAIN indexes = 1 을 붙인 출력을 /root/ch/sparse/explain_site.txt 에 저장한 뒤, PrimaryKey 단계의 고른/전체 그래뉼과 검색 방식을 /root/ch/sparse/granules.json 에 selected·total·search 로 적으세요.
  4. user_id = 4242 인 행의 dur_ms 합을 내는 /root/ch/sparse/q_user.sql 을 만들고, EXPLAIN 의 고른 그래뉼·검색 방식과 이 쿼리의 rows_read 를 /root/ch/sparse/user.json 에 selected·search·rows_read 로 적으세요.
  5. 같은 열에 정렬 키만 ORDER BY (user_id, site) 인 sparse.hits_us 를 만들어 sparse.hits 의 행을 옮기고 파트를 하나로 합치세요. 그 표에서 user_id = 4242 의 합을 내는 /root/ch/sparse/q_user_us.sql, site = 'docs.example' 의 합을 내는 /root/ch/sparse/q_site_us.sql 을 만들고 두 쿼리의 rows_read 를 /root/ch/sparse/order.json 에 user_rows_read·site_rows_read 로 적으세요.
  6. sparse.hits 와 같은 열·정렬 키에 SETTINGS index_granularity = 1024 인 sparse.hits_g1k 를 만들어 행을 옮기고 합치세요. 그 표의 marks·primary_key_size(system.parts)와, user_id = 4242 의 합을 내는 /root/ch/sparse/q_user_g1k.sql 의 rows_read 를 /root/ch/sparse/granularity.json 에 marks·primary_key_size·user_rows_read 로 적으세요.
  7. 정렬 키 ORDER BY (site, user_id, ts) 에 기본 키 PRIMARY KEY (site, user_id) 를 따로 둔 sparse.hits_pk 와, 같은 정렬 키에 PRIMARY KEY 를 적지 않은 sparse.hits_full 을 만들어 행을 옮기고 합치세요. 두 표의 primary_key_size 를 /root/ch/sparse/pk.json 에 hits_pk·hits_full 로 적으세요.
  8. 하루치 사이트 보고서 WHERE site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00' 의 dur_ms 합을 sparse.hits 에서 내는 /root/ch/sparse/q_day_hits.sql 과, 정렬 키를 여러분이 골라 만든 sparse.hits_day(같은 열·같은 행, 파트 하나)에서 내는 /root/ch/sparse/q_day.sql 을 만드세요. q_day.sql 의 rows_read 가 q_day_hits.sql 의 4분의 1 이하여야 합니다. 두 값을 /root/ch/sparse/day.json 에 hits_rows_read·day_rows_read 로 적으세요.

참고

종류가 적은 열을 앞에 둔 표

데이터베이스 sparse 와 표 sparse.hits 를 만드세요. 열은 site LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32 순서, 엔진은 MergeTree, 정렬 키는 ORDER BY (site, user_id) 입니다.

CREATE DATABASE 와 CREATE TABLE 두 문장입니다. site 는 값이 다섯 가지, user_id 는 5만 가지입니다 — 종류가 적은 열이 앞에 오는 순서입니다. 열 이름·타입·순서가 다르면 다음 단계의 원본 스크립트가 들어가지 않습니다.

200만 행을 넣고 파트를 하나로

/opt/lab/fixtures/sparse/hits.sql 을 한 번 실행해 sparse.hits 에 2,000,000행을 넣고, OPTIMIZE TABLE sparse.hits FINAL 로 활성 파트를 하나로 만드세요.

INSERT 한 번이 블록 크기에 따라 파트를 여러 개 만들 수 있습니다. 파트가 여럿이면 EXPLAIN 이 파트마다 그래뉼을 따로 세므로, 비교하기 전에 하나로 합칩니다. 행이 400만이면 두 번 넣은 것입니다 — TRUNCATE 뒤 다시.

첫 키 열로 거르면 — 이진 탐색

site = 'docs.example' 인 행의 dur_ms 합을 내는 /root/ch/sparse/q_site.sql 을 만들고, 그 SELECT 에 EXPLAIN indexes = 1 을 붙인 출력을 /root/ch/sparse/explain_site.txt 에 저장하세요. PrimaryKey 단계의 Granules: 고른/전체 와 Search Algorithm 을 /root/ch/sparse/granules.json 에 selected·total·search 로 적으세요.

EXPLAIN 출력에는 Granules 가 두 번 나옵니다 — ReadFromMergeTree 바로 아래 줄은 최종 결과이고, 이 단계가 묻는 것은 Indexes 아래 PrimaryKey 블록의 숫자입니다. 검색 방식 문자열은 공백까지 그대로 옮깁니다. 첫 키 열은 정렬돼 있으니 범위의 시작과 끝을 바로 찾을 수 있습니다.

두 번째 키 열로만 거르면

user_id = 4242 인 행의 dur_ms 합을 내는 /root/ch/sparse/q_user.sql 을 만드세요. EXPLAIN 의 PrimaryKey 단계에서 고른 그래뉼 수와 검색 방식을, --use_query_condition_cache 0 으로 돌린 이 쿼리의 statistics.rows_read 와 함께 /root/ch/sparse/user.json 에 selected·search·rows_read 로 적으세요.

user_id 는 사이트 덩어리마다 다시 처음부터 정렬돼 있어 전체로는 정렬돼 있지 않습니다. 그래서 이진 탐색 대신 이웃한 마크 사이에서 앞 열 값이 바뀌지 않는 구간만 제외하는 방식을 씁니다. 사이트 덩어리가 다섯 개라는 것과 고른 그래뉼 수를 이어 생각해 보세요. rows_read 는 고른 그래뉼 × 8192 근처로 나옵니다.

키 순서를 뒤집은 표

같은 열에 정렬 키만 ORDER BY (user_id, site) 인 sparse.hits_us 를 만들어 sparse.hits 의 행을 옮기고 OPTIMIZE ... FINAL 로 합치세요. 그 표에서 user_id = 4242 의 dur_ms 합을 내는 /root/ch/sparse/q_user_us.sql 과 site = 'docs.example' 의 합을 내는 /root/ch/sparse/q_site_us.sql 을 만들고, 두 쿼리의 rows_read 를 /root/ch/sparse/order.json 에 user_rows_read·site_rows_read 로 적으세요.

CREATE TABLE ... AS sparse.hits ENGINE = MergeTree ORDER BY (...) 로 열을 복사하며 정렬 키만 새로 줄 수 있습니다. 한 그래뉼 8192행 안에 사용자가 약 200명 들어 있다면, 이웃한 두 마크 사이에서 첫 열 user_id 가 같은 경우가 있을까요? 없다면 뒤 열 site 로는 어떤 구간도 제외할 수 없습니다.

그래뉼을 1024행으로 줄이면

sparse.hits 와 같은 열·정렬 키에 SETTINGS index_granularity = 1024 인 sparse.hits_g1k 를 만들어 행을 옮기고 합치세요. system.parts 의 marks·primary_key_size 와, user_id = 4242 의 합을 내는 /root/ch/sparse/q_user_g1k.sql 의 rows_read 를 /root/ch/sparse/granularity.json 에 marks·primary_key_size·user_rows_read 로 적으세요.

SETTINGS 절은 ORDER BY 뒤에 붙습니다. 그래뉼이 작아지면 같은 조건이 고르는 그래뉼 수는 비슷해도 그래뉼마다 읽는 행이 줄어듭니다. 대신 마크가 늘고 인덱스 파일(primary_key_size)이 커집니다 — 인덱스를 메모리에 두는 설계라 이것이 비용입니다. marks 는 그래뉼 수보다 하나 많습니다.

PRIMARY KEY 를 정렬 키의 앞부분으로

정렬 키 ORDER BY (site, user_id, ts) 에 PRIMARY KEY (site, user_id) 를 따로 적은 sparse.hits_pk 와, 같은 정렬 키에 PRIMARY KEY 를 적지 않은 sparse.hits_full 을 만들어 각각 행을 옮기고 합치세요. 두 표의 system.parts primary_key_size 를 /root/ch/sparse/pk.json 에 hits_pk·hits_full 로 적으세요.

PRIMARY KEY 를 적지 않으면 정렬 키 전체가 기본 키가 됩니다. 따로 적을 때는 정렬 키의 앞부분이어야 합니다 — (user_id) 처럼 앞부분이 아닌 것을 적으면 표가 만들어지지 않습니다. 정렬 순서는 같으니 파일 크기의 차이는 인덱스에 적힌 열 수에서 옵니다.

쿼리에 맞는 정렬 키를 고른다

하루치 사이트 보고서 조건 site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00' 으로 dur_ms 합을 내는 쿼리를 두 개 만드세요 — sparse.hits 를 읽는 /root/ch/sparse/q_day_hits.sql, 여러분이 정렬 키를 골라 만든 sparse.hits_day(같은 열, sparse.hits 의 행 전부, 파트 하나)를 읽는 /root/ch/sparse/q_day.sql. q_day.sql 의 rows_read 가 q_day_hits.sql 의 4분의 1 이하가 되게 키를 고르고, 두 값을 /root/ch/sparse/day.json 에 hits_rows_read·day_rows_read 로 적으세요.

sparse.hits 는 site 로는 좁혀지지만 그 안이 user_id 순이라 ts 범위가 인덱스에 걸리지 않습니다. 조건에 등호로 오는 열과 범위로 오는 열이 무엇인지, 그리고 두 번째 열이 인덱스에 걸리려면 앞 열이 어때야 하는지(4단계) 떠올려 보세요. 고른 뒤에는 EXPLAIN 으로 먼저 확인합니다.