워크드 쿼리 최적화 예제

워크드 쿼리 최적화 예제

이 가이드는 NYC Taxi 데이터셋에 두 가지 최적화 접근법을 적용해요. 먼저 더 정밀한 컬럼 타입을 선택해 저장·처리되는 데이터 양을 줄여요. 그 다음 선택적인 쿼리에서 ClickHouse가 데이터를 건너뛸 수 있게 하는 순서 키를 도입해요. 각 변경은 같은 기준선에 대해 측정돼요. 이 예제가 따르는 더 넓은 워크플로는 쿼리 최적화 개요를 참고하세요.

출처: 문서

본문

시작하기 전에 (Before you begin)

예제는 nyc_taxi.trips_small_inferred 테이블을 사용해요. 아직 안 했다면 만들고 로드해주세요:

예제 데이터셋 설정하기

원본 Parquet 파일은 약 5.8 GB예요. 네트워크와 사용 가능한 리소스에 따라 로드에 몇 분이 걸릴 수 있어요.

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small_inferred
ORDER BY () EMPTY
AS SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

원본 Parquet 파일에는 약 3억 2900만 행이 들어 있어요. 이 가이드의 타이밍은 한 배포에서 기록된 것으로 사용 가능한 계산 리소스에 따라 달라요. 동일한 지속 시간을 기대하기보다 단계 간 상대적 변화를 비교해요. 자신의 워크로드에 이 방법을 적용할 때는 쿼리나 스키마를 바꾸기 전에 느린 쿼리 진단을 사용해 반복되는 쿼리 패턴을 식별하고 대표 실행을 골라요.

프로세스 개요 (Process overview)

예제는 다음 세 단계를 사용해요:

1. 추론된 스키마에 대해 세 개의 독립적인 워크로드 쿼리를 실행해 기준선을 확립해요.
2. 더 정밀한 컬럼 타입을 가진 테이블을 만들고 같은 데이터를 로드한 뒤 쿼리를 다시 실행해요.
3. 같은 최적화된 스키마와 순서 키를 가진 또 다른 테이블을 만든 뒤 쿼리를 다시 실행해요.

스키마와 순서 키를 별도 단계에서 바꾸면 그 효과를 더 쉽게 구분할 수 있어요. 최적화 접근법은 언제 이 변경들을 고려하고 어떻게 검증하는지 설명해요. 비교 가능한 측정 수집에 대한 더 많은 지침은 쿼리 병목 분리를 참고하세요.

기준선 워크로드 정의하기

워크로드를 실행하는 데 사용하는 같은 클라이언트 세션에서 원격 데이터의 파일시스템 캐시, 쿼리 캐시, 쿼리 조건 캐시를 비활성화해요:

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;

이 설정은 테스트 중 반복 실행을 비교 가능하게 만드는 데 도움이 돼요. 측정을 완료한 후 이전 값으로 복원해주세요.

다음 세 개의 독립적인 쿼리가 기준선 워크로드를 이뤄요. 이후 단계에서 만든 각 테이블에 대해 세 개 모두를 실행해요. 각 쿼리를 비슷한 조건에서 여러 번 실행하고 중앙값 같은 대표 지속 시간을 읽은 행과 최고 메모리 사용량과 함께 기록해요. system.query_log에서 이 값들을 검색하는 방법을 포함해 완전한 측정 워크플로는 반복 가능한 기준선 확립을 참고하세요.

계산된 여행 속도로 필터링

이 쿼리는 시속 30마일보다 빠른 여정의 거리 분포를 찾기 전에 여행 지속 시간과 속도를 계산해요:

WITH
    dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,
    (trip_distance / trip_time) * 3600 AS speed_mph
SELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
FROM nyc_taxi.trips_small_inferred
WHERE speed_mph > 30
FORMAT JSON;

날짜 범위의 여정 집계

이 쿼리는 2009년 1분기의 여행 횟수, 거리, 평균 지불 금액을 계산해요:

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

승객 수로 필터링

이 쿼리는 승객이 1명 또는 2명인 여정의 평균 지속 시간을 계산해요:

SELECT avg(dateDiff('s', pickup_datetime, dropoff_datetime))
FROM nyc_taxi.trips_small_inferred
WHERE passenger_count = 1 OR passenger_count = 2
FORMAT JSON;

원래 측정값은 다음과 같았어요:

워크로드	지속 시간	읽은 행	최고 메모리
계산된 속도 필터	1.699 sec	3억 2904만	440.24 MiB
날짜 범위 집계	1.419 sec	3억 2904만	546.75 MiB
승객 수 필터	1.414 sec	3억 2904만	451.53 MiB

세 쿼리 모두 약 3억 2900만 행을 읽는데, 이는 테이블의 행 수에 가까워요. 이는 워크로드의 두 가지 다른 측면을 개선할 기회를 확립해요: 선택된 컬럼을 처리하기 더 저렴하게 만들고, 필터가 허용할 때 선택된 행 수를 줄이기.

스키마 최적화하기

스키마 추론은 데이터셋을 탐구하기 시작하는 실용적인 방법이지만, 추론된 타입은 워크로드가 요구하는 것보다 더 넓거나 관대할 수 있어요. 스키마를 바꾸기 전에 데이터를 검사하지, 추론된 타입이 불필요하다고 가정하지 마세요.

불필요한 Nullable 컬럼 피하기

Nullable 컬럼은 값 외에 null 마스크도 저장해요. null 값과 타입 기본값의 구분이 의미가 있을 때 Nullable을 유지하지만, 값을 포함하는 것이 보장되는 컬럼에서는 피해요. 예제 스키마에서 사용하는 컬럼의 null 값을 세어요:

SELECT
    countIf(vendor_id IS NULL) AS vendor_id_nulls,
    countIf(pickup_datetime IS NULL) AS pickup_datetime_nulls,
    countIf(dropoff_datetime IS NULL) AS dropoff_datetime_nulls,
    countIf(passenger_count IS NULL) AS passenger_count_nulls,
    countIf(trip_distance IS NULL) AS trip_distance_nulls,
    countIf(ratecode_id IS NULL) AS ratecode_id_nulls,
    countIf(fare_amount IS NULL) AS fare_amount_nulls,
    countIf(extra IS NULL) AS extra_nulls,
    countIf(mta_tax IS NULL) AS mta_tax_nulls,
    countIf(tip_amount IS NULL) AS tip_amount_nulls,
    countIf(tolls_amount IS NULL) AS tolls_amount_nulls,
    countIf(total_amount IS NULL) AS total_amount_nulls,
    countIf(payment_type IS NULL) AS payment_type_nulls,
    countIf(pickup_location_id IS NULL) AS pickup_location_id_nulls,
    countIf(dropoff_location_id IS NULL) AS dropoff_location_id_nulls
FROM nyc_taxi.trips_small_inferred
FORMAT VERTICAL;

Row 1:
──────
vendor_id_nulls:           0
pickup_datetime_nulls:     0
dropoff_datetime_nulls:    0
passenger_count_nulls:     0
trip_distance_nulls:       0
ratecode_id_nulls:          167200929
fare_amount_nulls:         0
extra_nulls:               0
mta_tax_nulls:             137946731
tip_amount_nulls:          0
tolls_amount_nulls:        0
total_amount_nulls:        0
payment_type_nulls:        69305
pickup_location_id_nulls:  0
dropoff_location_id_nulls: 0

이 데이터셋에서는 ratecode_id, mta_tax, payment_type만 null 값을 포함해요. 최적화된 스키마는 그 컬럼들에 대해 Nullable을 유지하고 나머지에서는 제거해요.

반복 값에 LowCardinality 사용

LowCardinality는 딕셔너리 인코딩을 사용하며 반복 값이 많은 컬럼의 저장·처리를 줄일 수 있어요. 적용하기 전에 고유 값의 수를 확인해요:

SELECT
    uniq(ratecode_id),
    uniq(pickup_location_id),
    uniq(dropoff_location_id),
    uniq(vendor_id)
FROM nyc_taxi.trips_small_inferred
FORMAT VERTICAL;

Row 1:
──────
uniq(ratecode_id):         6
uniq(pickup_location_id):  260
uniq(dropoff_location_id): 260
uniq(vendor_id):           3

이 네 컬럼은 행보다 고유 값이 훨씬 적어요. LowCardinality에 대한 적절한 후보지만, 효과는 여전히 워크로드에 대해 측정해야 해요. 약 10,000개의 고유 값이 후보를 식별하는 유용한 시작점이지 고정된 한계는 아니에요.

더 정밀한 데이터 타입 선택

요구되는 범위와 정밀도를 안전하게 보존하는 가장 좁은 타입을 사용해요. 예를 들어 추론된 Int64나 Float64를 교체하기 전에 숫자 컬럼의 최소·최대 값을 검사해요:

SELECT
    min(payment_type),
    max(payment_type),
    min(passenger_count),
    max(passenger_count)
FROM nyc_taxi.trips_small_inferred;

   ┌─min(payment_type)─┬─max(payment_type)─┬─min(passenger_count)─┬─max(passenger_count)─┐
1. │                 1 │                 4 │                    0 │                  255 │
   └───────────────────┴───────────────────┴──────────────────────┴──────────────────────┘

두 정수 컬럼 모두 UInt8에 들어맞지만, passenger_count는 최대값 255에 도달해요. 예제는 trip_distance에 Float32를, 금액 값에 Decimal32를 사용해요. 이 데이터셋의 모든 값은 대상 범위에 들어맞고, 예제는 그 워크로드가 집계 결과를 비교하기 때문에 줄어든 부동소수점 정밀도와 센트 단위 통화 정밀도를 수용해요. 정확한 원본 값이 필요하면 더 넓은 원본 타입을 유지해요. 예제는 쿼리가 분수 초 정밀도를 요구하지 않으므로 같은 UTC 시간대에서 추론된 DateTime64 컬럼을 DateTime으로 교체해요. 이러한 선택은 이 데이터셋에 특화된 것이에요. 같은 변경을 적용하기 전에 프로덕션 데이터의 범위, 정밀도, nullability 요구를 확인해주세요.

스키마 변경 적용하기

이 단계가 스키마 변경을 독립적으로 측정하도록 순서 키 없이 테이블을 만들어요:

CREATE TABLE nyc_taxi.trips_small_no_pk
(
    vendor_id LowCardinality(String),
    pickup_datetime DateTime('UTC'),
    dropoff_datetime DateTime('UTC'),
    passenger_count UInt8,
    trip_distance Float32,
    ratecode_id LowCardinality(Nullable(String)),
    pickup_location_id LowCardinality(String),
    dropoff_location_id LowCardinality(String),
    payment_type Nullable(UInt8),
    fare_amount Decimal32(2),
    extra Decimal32(2),
    mta_tax Nullable(Decimal32(2)),
    tip_amount Decimal32(2),
    tolls_amount Decimal32(2),
    total_amount Decimal32(2)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO nyc_taxi.trips_small_no_pk
SELECT *
FROM nyc_taxi.trips_small_inferred;

각 워크로드 쿼리에서 nyc_taxi.trips_small_inferred를 nyc_taxi.trips_small_no_pk로 바꾼 뒤 세 쿼리를 모두 다시 실행해요. 원래 예제는 다음 대표 결과를 기록했어요:

워크로드	추론된 스키마	최적화된 스키마	읽은 행	최적화된 최고 메모리
계산된 속도 필터	1.699 sec	1.353 sec	3억 2904만	337.12 MiB
날짜 범위 집계	1.419 sec	1.171 sec	3억 2904만	531.09 MiB
승객 수 필터	1.414 sec	1.188 sec	3억 2904만	265.05 MiB

쿼리는 여전히 같은 수의 행을 읽지만, 최적화된 스키마는 그 행들이 나타내는 데이터 양을 줄여요. 따라서 데이터 선택을 바꾸지 않고도 쿼리 지속 시간과 최고 메모리가 개선돼요. 두 테이블의 디스크 크기를 비교해요:

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    sum(rows) AS rows
FROM system.parts
WHERE active = 1
  AND database = 'nyc_taxi'
  AND table IN ('trips_small_inferred', 'trips_small_no_pk')
GROUP BY database, table
ORDER BY sum(data_compressed_bytes) DESC;

   ┌─table────────────────┬─compressed─┬─uncompressed─┬──────rows─┐
1. │ trips_small_inferred │ 7.38 GiB   │ 37.41 GiB    │ 329044175 │
2. │ trips_small_no_pk    │ 4.89 GiB   │ 15.31 GiB    │ 329044175 │
   └──────────────────────┴────────────┴──────────────┴───────────┘

이 데이터셋에서 최적화된 스키마는 압축 저장 공간을 약 34% 줄여 7.38 GiB에서 4.89 GiB로 줄어요.

순서 키 최적화하기

MergeTree 계열에서 순서 키는 행이 디스크에 어떻게 배열되는지 결정해요. ClickHouse는 그 순서 위에 희소 기본 인덱스를 구축해서 쿼리의 필터를 충족할 수 없는 granule을 건너뛸 수 있어요. 많은 트랜잭션 데이터베이스의 기본 키와 달리 이것은 유일성을 강제하지 않아요. 순서 키는 중요한 반복 쿼리가 사용하는 필터를 반영해야 해요. 컬럼 순서가 중요해요: 쿼리가 유용한 접두사로 필터링할 때 키가 가장 효과적이에요. 카디널리티가 낮은 컬럼은 흔히 필터링될 때 효과적인 선두 항목이 될 수 있고, 시간 기반 워크로드에는 시간 구성 요소가 종종 유용해요. 자세한 선택 지침은 기본 키 선택을 참고하세요. 이 예제에서는 (passenger_count, pickup_datetime, dropoff_datetime)을 사용해요. passenger_count는 고유 값이 적고 승객 수 필터에 나타나며, pickup_datetime은 날짜 범위 집계에 나타나요. pickup_datetime이 첫 번째 컬럼은 아니지만, ClickHouse는 선두 컬럼이 제약되지 않을 때 이후 키 컬럼의 값으로 데이터를 제외할 수 있어요. 순서 키의 유용한 접두사로 필터링하면 일반적으로 더 강한 프루닝을 제공해요.

순서 키 변경 적용하기

이전 단계에서 사용한 것과 같은 최적화된 스키마로 테이블을 만들어요. 순서 키만 바꿔요:

CREATE TABLE nyc_taxi.trips_small_pk
(
    vendor_id LowCardinality(String),
    pickup_datetime DateTime('UTC'),
    dropoff_datetime DateTime('UTC'),
    passenger_count UInt8,
    trip_distance Float32,
    ratecode_id LowCardinality(Nullable(String)),
    pickup_location_id LowCardinality(String),
    dropoff_location_id LowCardinality(String),
    payment_type Nullable(UInt8),
    fare_amount Decimal32(2),
    extra Decimal32(2),
    mta_tax Nullable(Decimal32(2)),
    tip_amount Decimal32(2),
    tolls_amount Decimal32(2),
    total_amount Decimal32(2)
)
ENGINE = MergeTree
ORDER BY (passenger_count, pickup_datetime, dropoff_datetime);

INSERT INTO nyc_taxi.trips_small_pk
SELECT *
FROM nyc_taxi.trips_small_no_pk;

각 워크로드 쿼리에서 테이블 이름을 nyc_taxi.trips_small_pk로 바꾼 뒤 세 쿼리를 모두 다시 실행해요.

결과 비교하기

원래 가이드는 세 단계에 걸쳐 다음 측정값을 기록했어요:

워크로드	측정값	추론된 스키마	최적화된 스키마	최적화된 스키마 + 순서 키
계산된 속도 필터	지속 시간	1.699 sec	1.353 sec	0.765 sec
	읽은 행	3억 2904만	3억 2904만	3억 2904만
	최고 메모리	440.24 MiB	337.12 MiB	444.19 MiB
날짜 범위 집계	지속 시간	1.419 sec	1.171 sec	0.248 sec
	읽은 행	3억 2904만	3억 2904만	4146만
	최고 메모리	546.75 MiB	531.09 MiB	173.50 MiB
승객 수 필터	지속 시간	1.414 sec	1.188 sec	0.431 sec
	읽은 행	3억 2904만	3억 2904만	2억 7699만
	최고 메모리	451.53 MiB	265.05 MiB	197.38 MiB

스키마 최적화는 저장을 줄이고 선택된 값을 처리하기 더 저렴하게 만들어요. 순서 키는 날짜 범위 집계에 가장 큰 추가 개선을 제공하는데, ClickHouse가 그 날짜 범위 밖의 granule을 건너뛸 수 있기 때문이에요. 승객 수 필터도 첫 번째 키 컬럼으로 필터링하므로 더 적은 행을 읽어요. 계산된 속도 필터는 여전히 전체 테이블을 읽는데, 그 필터가 순서 키의 유용한 접두사가 아닌 pickup_datetime, dropoff_datetime, trip_distance에서 파생되기 때문이에요. EXPLAIN indexes = 1로 날짜 범위 집계를 검사해요:

EXPLAIN indexes = 1
SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_pk
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC
SETTINGS
    use_query_condition_cache = 0,
    use_skip_indexes_on_data_read = 0;

ClickHouse 25.9 이상에서 이 설정은 EXPLAIN이 사용된 인덱스와 그것들이 제거하는 part·granule을 보고하도록 보장해요.

ReadFromMergeTree (nyc_taxi.trips_small_pk)
Indexes:
  PrimaryKey
    Keys:
      pickup_datetime
    Condition: and((pickup_datetime in (-Inf, 1238543999]), (pickup_datetime in [1230768000, +Inf)))
    Parts: 9/9
    Granules: 5061/40167

기본 인덱스는 40,167개의 granule 중 5,061개를 선택해요. 이 감소는 날짜 범위 집계가 전체 3억 2904만 대신 4,146만 행을 처리하는 것과 일치해요.

자신의 워크로드에 방법 적용하기

자신의 워크로드에도 같은 순서를 사용해요:

1. 기준선 지속 시간, 읽은 행·바이트, 최고 메모리를 기록해요.
2. 선택된 컬럼이 불필요하게 넓거나 관대한 타입을 사용하는지 검사해요.
3. 데이터 레이아웃을 바꾸지 않고 스키마 변경을 적용·측정해요.
4. 중요한 반복 쿼리가 사용하는 필터를 기반으로 순서 키를 테스트해요.
5. EXPLAIN indexes = 1로 선택된 데이터를 비교한 다음 비슷한 조건에서 기준선 쿼리를 다시 실행해요.

이 예제의 타입이나 순서 키가 다른 데이터셋에 맞을 것이라고 가정하지 마세요. 그러한 결정에는 관찰된 값과 쿼리 필터를 사용해요.

다음 단계 (Next steps)

스키마와 순서 키 변경이 측정된 병목을 해결하지 못할 때 프로젝션, materialized views, 데이터 스킵 인덱스, 미리 계산을 평가하려면 최적화 접근법으로 돌아가요.

더 알아보기 (Learn more)

• Choose an optimization approach
• Isolate query bottlenecks
• Query optimization