적절한 경우 데이터 스키핑 인덱스 사용하기

데이터 스키핑 인덱스를 언제, 어떻게 사용해야 하는지 설명하는 페이지

데이터 스키핑 인덱스는 먼저 기존 모범 사례를 따랐을 때, 즉 타입이 최적화되고 적절한 프라이머리 키가 선택되었으며 구체화된 뷰(Materialized View)가 활용되었을 때 고려해야 합니다. 스킵 인덱스가 익숙하지 않다면 이 가이드부터 시작하는 것이 좋습니다.

이러한 인덱스는 동작 방식을 이해하고 신중하게 사용할 경우 쿼리 성능을 향상하는 데 도움이 됩니다.

ClickHouse는 데이터 스키핑 인덱스라는 강력한 메커니즘을 제공합니다. 이 메커니즘은 쿼리 실행 중 스캔되는 데이터 양을 크게 줄일 수 있으며, 특히 특정 필터 조건에 대해 프라이머리 키가 도움이 되지 않을 때 더욱 유용합니다. 행 기반 보조 인덱스(B-tree 등)에 의존하는 전통적인 데이터베이스와 달리, ClickHouse는 컬럼 저장소이므로 이러한 구조를 지원하는 방식으로 행 위치를 저장하지 않습니다. 대신 스킵 인덱스를 사용해 쿼리의 필터링 조건과 일치하지 않는 것이 확실한 데이터 블록을 읽지 않도록 합니다.

스킵 인덱스는 데이터 블록에 대한 메타데이터(예: 최소/최대값, 값 집합 또는 블룸 필터 표현)를 저장하고, 쿼리 실행 중 이 메타데이터를 사용해 어떤 데이터 블록을 완전히 건너뛸 수 있는지 판단하는 방식으로 동작합니다. 이러한 인덱스는 MergeTree 엔진 계열의 테이블 엔진에만 적용되며, 표현식, 인덱스 유형, 이름, 그리고 각 인덱싱된 블록의 크기를 정의하는 세분화 수준으로 정의됩니다. 이러한 인덱스는 테이블 데이터와 함께 저장되며, 쿼리 필터가 인덱스 표현식과 일치할 때 참조됩니다.

데이터 스키핑 인덱스에는 여러 유형이 있으며, 각 유형은 서로 다른 쿼리와 데이터 분포에 적합합니다:

• minmax: 블록별로 표현식의 최소값과 최대값을 추적합니다. 느슨하게 정렬된 데이터에 대한 범위 쿼리에 적합합니다.
• set(N): 각 블록에 대해 지정된 크기 N까지의 값 집합을 추적합니다. 블록별 카디널리티가 낮은 컬럼에 효과적입니다.
• text: 토큰화된 문자열 데이터에 대해 역인덱스를 구축하여 효율적이고 결정적인 전문 검색을 가능하게 합니다. 근사적인 블룸 필터 기반 접근 방식 대신, 정확한 토큰 조회와 확장 가능한 다중 용어 검색이 필요한 자연어 또는 대규모 자유 형식 텍스트 컬럼에 권장됩니다.
• bloom_filter: 값이 블록 안에 존재하는지 확률적으로 판단하여 집합 포함 여부에 대한 빠른 근사 필터링을 가능하게 합니다. 일치 항목을 반드시 찾아야 하는 “건초 더미에서 바늘 찾기”와 같은 쿼리를 최적화하는 데 효과적입니다.
• tokenbf_v1 / ngrambf_v1: (Deprecated) 문자열에서 토큰 또는 문자 시퀀스를 검색하도록 설계된 특수 블룸 필터 변형으로, 특히 로그 데이터 또는 텍스트 검색 사용 사례에 유용합니다. ClickHouse 버전 >= 26.2에서는 text 인덱스를 대신 사용하도록 지원이 중단되었습니다.

강력한 기능이지만 스킵 인덱스는 주의해서 사용해야 합니다. 의미 있는 수의 데이터 블록을 제거할 수 있을 때만 이점이 있으며, 쿼리나 데이터 구조가 맞지 않으면 오히려 오버헤드를 유발할 수 있습니다. 블록 안에 일치하는 값이 단 하나라도 있으면 해당 블록 전체를 여전히 읽어야 합니다.

효과적인 스킵 인덱스 사용은 대개 인덱싱된 컬럼과 테이블의 프라이머리 키 사이에 강한 상관관계가 있거나, 유사한 값이 함께 묶이도록 데이터를 삽입하는 방식에 달려 있습니다.

일반적으로 데이터 스키핑 인덱스는 적절한 프라이머리 키 설계와 타입 최적화를 먼저 보장한 후 적용하는 것이 가장 좋습니다. 특히 다음과 같은 경우에 유용합니다:

• 전체적으로는 카디널리티가 높지만 블록 내에서는 카디널리티가 낮은 컬럼.
• 검색에 중요하지만 드물게 나타나는 값(예: 오류 코드, 특정 ID).
• 프라이머리 키가 아닌 컬럼에 대해 국소적인 분포를 보이는 데이터에서 필터링이 발생하는 경우.

항상 다음을 수행하세요:

1. 실제 데이터와 현실적인 쿼리로 스킵 인덱스를 테스트하세요. 서로 다른 인덱스 유형과 세분화 수준 값을 시도하세요.
2. send_logs_level=‘trace’ 및 EXPLAIN indexes=1 같은 도구를 사용해 인덱스의 효과를 확인하고 영향을 평가하세요.
3. 항상 인덱스 크기와 세분화 수준이 이에 미치는 영향을 평가하세요. 세분화 수준 크기를 줄이면 더 많은 그래뉼을 필터링할 수 있어 일정 수준까지는 성능이 향상되는 경우가 많습니다. 그러나 세분화 수준이 낮아질수록 인덱스 크기도 커지므로 성능이 저하될 수도 있습니다. 다양한 세분화 수준 값에 대해 성능과 인덱스 크기를 측정하세요. 이는 특히 블룸 필터 인덱스에서 중요합니다.

적절하게 사용하면 스킵 인덱스는 상당한 성능 향상을 제공할 수 있지만, 무분별하게 사용하면 불필요한 비용만 추가할 수 있습니다.

Data Skipping Indices에 대한 더 자세한 가이드는 여기를 참조하세요.

​

예시

다음 최적화된 테이블을 살펴보십시오. 이 테이블에는 게시물마다 하나의 행으로 구성된 Stack Overflow 데이터가 포함되어 있습니다.

CREATE TABLE stackoverflow.posts
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
  `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime64(3, 'UTC'),
  `Score` Int32,
  `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  `LastActivityDate` DateTime64(3, 'UTC'),
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense` LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime64(3, 'UTC'),
  `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate))

이 테이블은 게시물 유형과 날짜를 기준으로 필터링 및 집계하는 쿼리에 최적화되어 있습니다. 2009년 이후에 게시된 게시물 중 조회수가 10,000,000 이상인 게시물의 수를 집계하는 경우를 가정해 보겠습니다.

SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─count()─┐
│     5   │
└─────────┘

1 row in set. Elapsed: 0.720 sec. Processed 59.55 million rows, 230.23 MB (82.66 million rows/s., 319.56 MB/s.)

이 쿼리는 프라이머리 인덱스를 사용하여 일부 행(및 그래뉼)을 제외할 수 있습니다. 그러나 위의 응답과 다음 EXPLAIN indexes = 1 결과에서 확인할 수 있듯이, 대부분의 행은 여전히 읽어야 합니다.

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)
LIMIT 1

┌─explain──────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                        │
│   Limit (preliminary LIMIT (without OFFSET))                     │
│     Aggregating                                                  │
│       Expression (Before GROUP BY)                               │
│         Expression                                               │
│           ReadFromMergeTree (stackoverflow.posts)                │
│           Indexes:                                               │
│             MinMax                                               │
│               Keys:                                              │
│                 CreationDate                                     │
│               Condition: (CreationDate in ('1230768000', +Inf))  │
│               Parts: 123/128                                     │
│               Granules: 8513/8545                                │
│             Partition                                            │
│               Keys:                                              │
│                 toYear(CreationDate)                             │
│               Condition: (toYear(CreationDate) in [2009, +Inf))  │
│               Parts: 123/123                                     │
│               Granules: 8513/8513                                │
│             PrimaryKey                                           │
│               Keys:                                              │
│                 toDate(CreationDate)                             │
│               Condition: (toDate(CreationDate) in [14245, +Inf)) │
│               Parts: 123/123                                     │
│               Granules: 8513/8513                                │
└──────────────────────────────────────────────────────────────────┘

25 rows in set. Elapsed: 0.070 sec.

간단한 분석을 통해 예상대로 ViewCount가 CreationDate(기본 키(primary key))와 연관되어 있음을 확인할 수 있습니다. 게시물이 오래 존재할수록 조회될 기회가 더 많아지기 때문입니다.

SELECT toDate(CreationDate) AS day, avg(ViewCount) AS view_count FROM stackoverflow.posts WHERE day > '2009-01-01'  GROUP BY day

따라서 이는 데이터 스키핑 인덱스로 적절한 선택입니다. 숫자형 데이터이므로 minmax 인덱스가 적합합니다. 다음 ALTER TABLE 명령으로 인덱스를 추가합니다. 먼저 추가한 다음 “materializing”합니다.

ALTER TABLE stackoverflow.posts
  (ADD INDEX view_count_idx ViewCount TYPE minmax GRANULARITY 1);

ALTER TABLE stackoverflow.posts MATERIALIZE INDEX view_count_idx;

이 인덱스는 테이블(table)을 처음 생성할 때도 추가할 수 있습니다. 다음은 DDL의 일부로 minmax 인덱스를 정의한 스키마(schema)입니다:

CREATE TABLE stackoverflow.posts
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
  `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime64(3, 'UTC'),
  `Score` Int32,
  `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  `LastActivityDate` DateTime64(3, 'UTC'),
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense` LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime64(3, 'UTC'),
  `ClosedDate` DateTime64(3, 'UTC'),
  INDEX view_count_idx ViewCount TYPE minmax GRANULARITY 1 --인덱스 위치
)
ENGINE = MergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate))

다음 애니메이션은 예시 테이블에서 minmax 스킵 인덱스가 어떻게 생성되는지 보여줍니다. 이 인덱스는 테이블의 각 행 블록(granule)별 최소 ViewCount 값과 최대 ViewCount 값을 기록합니다:

앞서 사용한 쿼리를 다시 실행하면 성능이 크게 향상된 것을 확인할 수 있습니다. 스캔된 행 수가 줄어든 점에 주목하십시오:

SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─count()─┐
│     5   │
└─────────┘

1 row in set. Elapsed: 0.012 sec. Processed 39.11 thousand rows, 321.39 KB (3.40 million rows/s., 27.93 MB/s.)

EXPLAIN indexes = 1을 사용하면 인덱스 사용 여부를 확인할 수 있습니다.

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─explain────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                          │
│   Aggregating                                                      │
│     Expression (Before GROUP BY)                                   │
│       Expression                                                   │
│         ReadFromMergeTree (stackoverflow.posts)                    │
│         Indexes:                                                   │
│           MinMax                                                   │
│             Keys:                                                  │
│               CreationDate                                         │
│             Condition: (CreationDate in ('1230768000', +Inf))      │
│             Parts: 123/128                                         │
│             Granules: 8513/8545                                    │
│           Partition                                                │
│             Keys:                                                  │
│               toYear(CreationDate)                                 │
│             Condition: (toYear(CreationDate) in [2009, +Inf))      │
│             Parts: 123/123                                         │
│             Granules: 8513/8513                                    │
│           PrimaryKey                                               │
│             Keys:                                                  │
│               toDate(CreationDate)                                 │
│             Condition: (toDate(CreationDate) in [14245, +Inf))     │
│             Parts: 123/123                                         │
│             Granules: 8513/8513                                    │
│           Skip                                                     │
│             Name: view_count_idx                                   │
│             Description: minmax GRANULARITY 1                      │
│             Parts: 5/123                                           │
│             Granules: 23/8513                                      │
└────────────────────────────────────────────────────────────────────┘

29 rows in set. Elapsed: 0.211 sec.

또한 예시 쿼리에서 ViewCount > 10,000,000 프레디케이트와 일치할 수 없는 모든 행 블록을 minmax 스킵 인덱스가 어떻게 걸러내는지 보여주는 애니메이션도 확인할 수 있습니다:

​

관련 문서

• 데이터 스키핑 인덱스 가이드
• 데이터 스키핑 인덱스 사용 예시
• 데이터 스키핑 인덱스 다루기
• 시스템 테이블 정보