정렬 키 밖의 조건 — 인덱스와 프로젝션으로 건너뛰기

목표

정렬 키가 ts 인 로그 표에서 키 밖의 열로 거르는 쿼리를 건너뛰기 인덱스와 프로젝션으로 줄이고, 각각이 언제 통하고 언제 통하지 않는지, 무엇을 대가로 치르는지를 EXPLAIN·rows_read·system 표로 확인한다.

왜 중요한가

건너뛰기 인덱스는 이름과 달리 B-트리가 아니라 그래뉼 묶음의 요약입니다. 자료의 분포가 맞지 않으면 한 그래뉼도 건너뛰지 못하고, 기존 파트에는 MATERIALIZE 하기 전까지 아무 효과가 없습니다. 프로젝션은 쿼리를 고치지 않고 다른 정렬 순서를 쓰게 해 주지만 디스크를 크게 씁니다. 이 실습의 채점기는 여러분이 적은 숫자를 믿지 않습니다 — 저장한 SELECT 를 읽기 전용으로 다시 돌리되, 조건 캐시를 끄고, 인덱스를 끈 채로도 돌려 줄어든 행이 정말 그 인덱스·프로젝션 덕분인지 확인합니다.

단계

1. 데이터베이스 skp 와 표 skp.logs 를 만드세요 — 열 ts DateTime, seq UInt64, service LowCardinality(String), user_id UInt32, trace_id UInt64, latency_ms UInt32, path String (이 순서), MergeTree, ORDER BY ts. /opt/lab/fixtures/skip/logs.sql 로 100만 행을 한 번 넣고 OPTIMIZE TABLE skp.logs FINAL 로 파트를 하나로 만드세요.
2. ALTER TABLE skp.logs ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 1 을 실행하고 (MATERIALIZE 는 아직 하지 말고), EXPLAIN indexes = 1 SELECT count(), sum(latency_ms) FROM skp.logs WHERE trace_id = 12249378055674284861 의 출력을 /root/ch/skip/explain_before.txt 에 저장하세요.
3. MATERIALIZE INDEX idx_trace 를 mutations_sync = 2 로 실행하고, 위의 SELECT 를 /root/ch/skip/q_trace.sql 로 저장한 뒤, 조건 캐시를 끄고 잰 rows_read 와 그 뮤테이션의 mutation_id 를 /root/ch/skip/trace.json 에 적으세요.
4. seq 에 idx_seq, user_id 에 idx_user 라는 minmax 인덱스(GRANULARITY 1)를 추가하고 MATERIALIZE 하세요. seq BETWEEN 1001500000 AND 1001520000 인 행의 sum(latency_ms) 를 구하는 /root/ch/skip/q_seq.sql 과 user_id BETWEEN 100 AND 120 인 행의 sum(latency_ms) 를 구하는 /root/ch/skip/q_user_range.sql 을 만들고, 두 쿼리의 rows_read 를 /root/ch/skip/minmax.json 에 seq_rows_read·user_rows_read 로 적으세요.
5. 프로젝션 p_user (SELECT * ORDER BY user_id) 를 추가하고 MATERIALIZE 하세요. user_id = 4242 인 행의 count(), sum(latency_ms) 를 구하는 /root/ch/skip/q_user.sql 을 만들고, 프로젝션을 쓴 rows_read 와 --optimize_use_projections 0 으로 끈 rows_read 를 /root/ch/skip/proj.json 에 with_projection·without_projection 으로 적으세요.
6. 5단계의 쿼리를 SETTINGS log_comment = 'chs-skip-06' 을 붙여 돌린 뒤, system.query_log 의 끝(QueryFinish) 기록에서 query_id·read_rows·projections 를 /root/ch/skip/qlog.json 에 적으세요.
7. 집계 프로젝션 p_svc_hour (SELECT service, toStartOfHour(ts), count(), avg(latency_ms) GROUP BY service, toStartOfHour(ts)) 를 추가하고 MATERIALIZE 하세요. 서비스별 service, count(), round(avg(latency_ms), 3) 을 서비스순으로 내는 /root/ch/skip/q_svc.sql 을 만들고, 그 rows_read 와 system.projection_parts 의 p_svc_hour rows 를 /root/ch/skip/agg.json 에 rows_read·projection_rows 로 적으세요.
8. 활성 파트의 bytes_on_disk(part_bytes), 두 프로젝션 파트의 bytes_on_disk(p_user_bytes·p_svc_hour_bytes), 두 인덱스의 data_compressed_bytes(idx_trace_bytes·idx_seq_bytes)를 /root/ch/skip/cost.json 에 적으세요.

참고

• 읽은 행은 clickhouse-client --queries-file 파일.sql --format JSON --use_query_condition_cache 0 | jq .statistics 로 잽니다. 조건 캐시를 켜 두면 같은 쿼리의 두 번째 실행이 0행을 읽었다고 나올 수 있습니다.
• ADD INDEX·ADD PROJECTION 은 정의만 바꿉니다. 이미 있는 파트에는 MATERIALIZE INDEX·MATERIALIZE PROJECTION 이 필요하고, 이것은 뮤테이션이라 SETTINGS mutations_sync = 2 를 주면 끝날 때까지 기다립니다.
• EXPLAIN 의 Skip 칸에서 Granules: 남은/전체 를 읽습니다. 26.8 은 기본으로 나무 모양(├──)으로 찍습니다.
• 흔한 실수: 쿼리에 ts 조건을 섞어 정렬 키가 대신 걸러 주는 것(채점기는 인덱스를 끄고도 다시 잽니다), 2단계 전에 MATERIALIZE 해 버리는 것.
• 공식 문서: [Understanding data skipping indexes](https://clickhouse.com/docs/concepts/features/performance/skip-indexes/skipping-indexes) · [Use data skipping indices where appropriate](https://clickhouse.com/docs/concepts/best-practices/using-data-skipping-indices) · [Projections](https://clickhouse.com/docs/concepts/features/projections/projections) · [Materialized views versus projections](https://clickhouse.com/docs/concepts/features/projections/materialized-views-versus-projections) · [EXPLAIN](https://clickhouse.com/docs/reference/statements/explain) · [system.data_skipping_indices](https://clickhouse.com/docs/reference/system-tables/data_skipping_indices) · [system.projection_parts](https://clickhouse.com/docs/reference/system-tables/projection_parts)

8个步骤

1. 시각으로 정렬한 로그 표를 만든다
2. 인덱스를 정의만 했을 때의 EXPLAIN
3. MATERIALIZE 한 뒤 읽은 행
4. 같은 minmax, 다른 효과
5. 다른 정렬 순서의 숨은 사본
6. 어느 프로젝션을 골랐는지 기록에서 본다
7. 미리 집계한 프로젝션
8. 빠르게 해 준 것들의 디스크 값