프라이머리 키 선택하기
프라이머리 키 선택하기
ClickHouse에서 효과적인 프라이머리 키를 고르는 것은 쿼리 성능과 저장 효율에 매우 중요합니다. 이 문서에서는 정렬 키(ordering key)를 어떤 규칙으로 선택해야 하는지, 희소(sparse) 프라이머리 인덱스가 쿼리 성능을 어떻게 끌어올리는지 실제 예제와 함께 설명할게요.
출처: 문서
본문
이 페이지에서는 "프라이머리 키"와 "정렬 키(ordering key)"라는 용어를 서로 바꿔 사용합니다. 엄밀히 말하면 ClickHouse에서는 둘이 다르지만, 이 문서의 목적상 독자들은 서로 바꿔 쓸 수 있으며, 정렬 키는 테이블의
ORDER BY에 지정된 컬럼을 가리킵니다.
참고로 ClickHouse의 프라이머리 키는 Postgres 같은 OLTP 데이터베이스의 비슷한 용어에 익숙한 사람들에게는 매우 다르게 동작합니다. ClickHouse에서 효과적인 프라이머리 키를 선택하는 것은 쿼리 성능과 저장 효율에 매우 중요합니다. ClickHouse는 데이터를 파츠로 구성하며, 각 파츠는 고유한 희소 프라이머리 인덱스를 가집니다. 이 인덱스는 스캔되는 데이터 양을 줄여 쿼리를 크게 가속화합니다. 또한 프라이머리 키가 디스크상 데이터의 물리적 순서를 결정하므로 압축 효율에도 직접적인 영향을 줍니다. 최적으로 정렬된 데이터는 더 효과적으로 압축되며, 이는 I/O를 줄여 성능을 더욱 향상시킵니다.
- 정렬 키를 선택할 때는 쿼리 필터(
WHERE절)에서 자주 사용되는 컬럼, 특히 많은 수의 행을 제외시키는 컬럼을 우선시하세요. - 테이블의 다른 데이터와 상관관계가 높은 컬럼도 유리합니다. 연속된 저장 공간이
GROUP BY와ORDER BY연산 중 압축률과 메모리 효율을 높여 주기 때문입니다.
정렬 키를 고르는 데 도움이 되는 몇 가지 간단한 규칙이 있습니다. 다음 규칙은 때로 충돌할 수 있으므로 순서대로 고려하세요. 이 과정을 통해 여러 개의 키를 식별할 수 있으며, 보통 4~5개면 충분합니다:
중요 정렬 키는 테이블 생성 시점에 정의해야 하며 나중에 추가할 수 없습니다. 데이터 삽입 후(또는 전에) 테이블에 추가적인 정렬을 추가하려면 프로젝션(projection)이라는 기능을 사용할 수 있습니다. 단, 이로 인해 데이터가 중복된다는 점에 유의하세요. 자세한 내용은 여기를 참고하세요.
예제
다음 posts_unordered 테이블을 고려해 보세요. 이 테이블은 Stack Overflow 게시물당 하나의 행을 담고 있습니다. 그리고 ORDER BY tuple()로 표시된 것처럼 프라이머리 키가 없습니다.
CREATE TABLE posts_unordered
(
`Id` Int32,
`PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4,
'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
`AcceptedAnswerId` UInt32,
`CreationDate` DateTime,
`Score` Int32,
`ViewCount` UInt32,
`Body` String,
`OwnerUserId` Int32,
`OwnerDisplayName` String,
`LastEditorUserId` Int32,
`LastEditorDisplayName` String,
`LastEditDate` DateTime,
`LastActivityDate` DateTime,
`Title` String,
`Tags` String,
`AnswerCount` UInt16,
`CommentCount` UInt8,
`FavoriteCount` UInt8,
`ContentLicense`LowCardinality(String),
`ParentId` String,
`CommunityOwnedDate` DateTime,
`ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
사용자가 가장 흔한 접근 패턴으로 2024년 이후 제출된 질문의 수를 계산하려 한다고 가정해 보겠습니다.
SELECT count()
FROM stackoverflow.posts_unordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
┌─count()─┐
│ 192611 │
└─────────┘
1 row in set. Elapsed: 0.055 sec. Processed 59.82 million rows, 361.34 MB (1.09 billion rows/s., 6.61 GB/s.)
이 쿼리가 읽은 행 수와 바이트 수를 주목하세요. 프라이머리 키가 없으면 쿼리는 전체 데이터셋을 스캔해야 합니다. EXPLAIN indexes=1을 사용하면 인덱스가 없어 전체 테이블 스캔이 발생함을 확인할 수 있습니다.
EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts_unordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
┌─explain───────────────────────────────────────────────────┐
│ Expression ((Project names + Projection)) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ Expression │
│ ReadFromMergeTree (stackoverflow.posts_unordered) │
└───────────────────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.003 sec.
같은 데이터를 담고 있는 posts_ordered 테이블이 ORDER BY를 (PostTypeId, toDate(CreationDate))로 정의했다고 가정해 보겠습니다. 예를 들면:
CREATE TABLE posts_ordered
(
`Id` Int32,
`PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6,
'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
...
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate))
PostTypeId는 카디널리티가 8이며 정렬 키의 첫 번째 항목으로 논리적인 선택입니다. 날짜 세분화 필터링이 충분할 것이라고 판단되어(datetime 필터에도 여전히 유용합니다) 키의 두 번째 구성 요소로 toDate(CreationDate)를 사용합니다. 날짜는 16비트로 표현할 수 있어 더 작은 인덱스를 만들어 필터링 속도를 높입니다. 다음 애니메이션은 Stack Overflow posts 테이블에 최적화된 희소 프라이머리 인덱스가 어떻게 생성되는지 보여줍니다. 개별 행을 인덱싱하는 대신 인덱스는 행 블록을 대상으로 합니다. 이 정렬 키를 가진 테이블에 동일한 쿼리를 반복하면:
SELECT count()
FROM stackoverflow.posts_ordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
┌─count()─┐
│ 192611 │
└─────────┘
1 row in set. Elapsed: 0.013 sec. Processed 196.53 thousand rows, 1.77 MB (14.64 million rows/s., 131.78 MB/s.)
이 쿼리는 이제 희소 인덱스를 활용하여 읽는 데이터 양을 크게 줄이고 실행 시간을 4배 단축합니다. 읽힌 행과 바이트 수의 감소를 확인하세요. 인덱스 사용 여부는 EXPLAIN indexes=1로 확인할 수 있습니다.
EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts_ordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
┌─explain─────────────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection)) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ Expression │
│ ReadFromMergeTree (stackoverflow.posts_ordered) │
│ Indexes: │
│ PrimaryKey │
│ Keys: │
│ PostTypeId │
│ toDate(CreationDate) │
│ Condition: and((PostTypeId in [1, 1]), (toDate(CreationDate) in [19723, +Inf))) │
│ Parts: 14/14 │
│ Granules: 39/7578 │
└─────────────────────────────────────────────────────────────────────────────────────────────┘
13 rows in set. Elapsed: 0.004 sec.
또한 희소 인덱스가 예제 쿼리의 일치 항목이 절대 포함될 수 없는 모든 행 블록을 어떻게 가지치기하는지 시각화해 보겠습니다.
테이블의 모든 컬럼은 키에 포함되었는지 여부와 관계없이 지정된 정렬 키의 값에 따라 정렬됩니다. 예를 들어 CreationDate가 키로 사용되면 다른 모든 컬럼의 값 순서는 CreationDate 컬럼의 값 순서와 일치합니다. 여러 정렬 키를 지정할 수도 있습니다. 이 경우 SELECT 쿼리의 ORDER BY 절과 동일한 의미로 정렬됩니다.
프라이머리 키 선택에 대한 완전한 고급 가이드는 여기에서 찾을 수 있습니다. 정렬 키가 압축을 개선하고 저장 공간을 더 최적화하는 방법에 대한 더 깊은 통찰을 원한다면 공식 가이드인 ClickHouse의 압축과 컬럼 압축 코덱을 살펴보세요.