소개
- ClickHouse의 인덱싱이 기존 관계형 데이터베이스 관리 시스템과 어떻게 다른지
- ClickHouse가 테이블의 희소 프라이머리 인덱스를 구축하고 활용하는 방법
- ClickHouse 인덱싱 모범 사례
이 가이드는 ClickHouse 희소 프라이머리 인덱스에 중점을 둡니다.ClickHouse의 보조 데이터 스키핑 인덱스는 Tutorial을 참조하십시오.
데이터 세트
- 샘플 데이터 세트에서 887만 개 행(이벤트)으로 이루어진 부분 집합을 사용합니다.
- 비압축 데이터 크기는 887만 개 이벤트, 약 700 MB입니다. 이를 ClickHouse에 저장하면 200 MB로 압축됩니다.
- 이 부분 집합에서 각 행에는 특정 시점에 URL(
URL컬럼)을 클릭한 인터넷 사용자(UserID컬럼)를 나타내는 3개의 컬럼과 시간 정보(EventTime컬럼)가 포함됩니다.
- “특정 사용자가 가장 많이 클릭한 URL 상위 10개는 무엇입니까?”
- “특정 URL을 가장 자주 클릭한 사용자 상위 10명은 누구입니까?”
- “사용자가 특정 URL을 클릭하는 가장 빈도가 높은 시간대(예: 요일)는 언제입니까?”
테스트 머신
전체 테이블 스캔
일반적으로는 테이블에 데이터를 적재한 직후 바로 최적화할 필요는 없으며, 그렇게 하는 것도 권장되지 않습니다. 이 예시에서 왜 이것이 필요한지는 곧 분명해집니다.
ClickHouse 인덱스 설계
대규모 데이터 처리를 위한 인덱스 설계
B(+)-Tree 데이터 구조에서 인덱스 항목을 검색하는 평균 시간 복잡도는 O(log n)입니다. 더 정확히는 log_b n = log_2 n / log_2 b이며, 여기서 b는 B(+)-Tree의 branching factor이고 n은 인덱싱된 행 수입니다. b는 일반적으로 수백에서 수천 사이이므로 B(+)-Trees는 매우 얕은 구조이며, 레코드를 찾는 데 필요한 디스크 탐색 횟수도 적습니다. 887만 개의 행과 branching factor 1000을 가정하면 평균적으로 2.3회의 디스크 탐색이 필요합니다. 하지만 이러한 기능에는 비용이 따릅니다. 추가적인 디스크 및 메모리 오버헤드가 발생하고, 테이블에 새 행을 추가하고 인덱스에 인덱스 항목을 추가할 때 삽입 비용이 더 커지며, 경우에 따라 B-Tree 리밸런싱도 필요합니다.
B-Tree 인덱스의 이러한 한계를 고려해 ClickHouse의 테이블 엔진은 다른 접근 방식을 사용합니다. ClickHouse의 MergeTree 엔진 계열은 대규모 데이터 볼륨을 처리하도록 설계되고 최적화되었습니다. 이 테이블은 초당 수백만 개의 행 삽입을 수용하고 매우 큰 규모(수백 페타바이트)의 데이터를 저장하도록 설계되었습니다. 데이터는 파트 단위로 테이블에 빠르게 기록되며, 백그라운드에서는 파트를 머지하는 규칙이 적용됩니다. ClickHouse에서는 각 파트가 자체 프라이머리 인덱스를 가집니다. 파트가 머지되면 병합된 파트의 프라이머리 인덱스도 함께 머지됩니다. ClickHouse가 대상으로 하는 초대규모 환경에서는 디스크와 메모리를 매우 효율적으로 사용하는 것이 무엇보다 중요합니다. 따라서 모든 행을 인덱싱하는 대신, 파트의 프라이머리 인덱스는 각 행 그룹(「그래뉼」이라고 함)마다 하나의 인덱스 항목(「mark」라고 함)만 가집니다. 이 기법을 희소 인덱스라고 합니다.
희소 인덱싱이 가능한 이유는 ClickHouse가 파트의 행을 기본 키 컬럼을 기준으로 정렬해 디스크에 저장하기 때문입니다. 희소 프라이머리 인덱스(sparse primary index)는 단일 행을 직접 찾는 방식(B-Tree 기반 인덱스처럼) 대신, 인덱스 항목에 대한 이진 검색을 통해 쿼리와 일치할 가능성이 있는 행 그룹을 빠르게 식별할 수 있게 해줍니다. 이렇게 찾아낸 일치 가능성이 있는 행 그룹(그래뉼)은 실제 일치 항목을 찾기 위해 병렬로 ClickHouse 엔진에 스트리밍됩니다. 이러한 인덱스 설계 덕분에 프라이머리 인덱스를 작게 유지할 수 있으며(메인 메모리에 완전히 들어가야 하며, 실제로도 그렇게 되어야 합니다), 동시에 쿼리 실행 시간도 크게 단축할 수 있습니다. 특히 데이터 분석 사용 사례에서 일반적인 범위 쿼리에서 효과적입니다.
아래에서는 ClickHouse가 희소 프라이머리 인덱스를 어떻게 구축하고 사용하는지 자세히 설명합니다. 이 글의 뒷부분에서는 인덱스 구축에 사용되는 테이블 컬럼(기본 키 컬럼)을 선택하고, 제거하고, 정렬할 때의 모범 사례도 살펴보겠습니다.
기본 키가 있는 테이블
DDL 문 세부 정보
DDL 문 세부 정보
이 가이드의 뒷부분 설명을 단순화하고 다이어그램과 결과를 재현할 수 있도록, 다음 DDL 문을 사용합니다:
ORDER BY절을 통해 테이블의 복합 정렬 키를 지정합니다.- 다음 설정을 통해 프라이머리 인덱스(primary index)의 인덱스 항목 수를 명시적으로 제어합니다:
index_granularity: 기본값인 8192로 명시적으로 설정합니다. 즉, 8192행마다 프라이머리 인덱스에 인덱스 항목이 1개 생성됩니다. 예를 들어 테이블에 16384행이 있으면 인덱스에는 인덱스 항목이 2개 생성됩니다.index_granularity_bytes: 적응형 인덱스 세분화 수준을 비활성화하기 위해 0으로 설정합니다. 적응형 인덱스 세분화 수준이란, 다음 조건 중 하나가 참일 때 ClickHouse가n개의 행으로 이루어진 그룹마다 인덱스 항목 1개를 자동으로 생성하는 것을 의미합니다:n이 8192보다 작고, 해당n개 행의 결합된 데이터 크기가 10 MB 이상인 경우(index_granularity_bytes의 기본값).n개 행의 결합된 데이터 크기가 10 MB보다 작지만n이 8192인 경우.
compress_primary_key: 프라이머리 인덱스 압축을 비활성화하기 위해 0으로 설정합니다. 이렇게 하면 나중에 필요에 따라 그 내용을 확인할 수 있습니다.
다음으로 데이터를 삽입합니다:
그런 다음 테이블을 최적화합니다:
다음 쿼리를 사용하여 테이블의 메타데이터를 확인할 수 있습니다:
- 테이블 데이터는 디스크의 특정 디렉터리에 wide format으로 저장되며, 이는 해당 디렉터리 안에 테이블의 각 컬럼마다 데이터 파일 1개(및 마크 파일 1개)가 있음을 의미합니다.
- 테이블에는 887만 개의 행이 있습니다.
- 모든 행의 비압축 데이터 크기를 합하면 733.28 MB입니다.
- 모든 행의 디스크상 압축된 크기를 합하면 206.94 MB입니다.
- 테이블에는 1083개의 항목(‘마크’라고 함)으로 구성된 프라이머리 인덱스가 있으며, 인덱스 크기는 96.93 KB입니다.
- 전체적으로 테이블의 데이터 파일, 마크 파일, 프라이머리 인덱스 파일이 디스크에서 차지하는 크기는 207.07 MB입니다.
데이터는 프라이머리 키 컬럼 순으로 정렬되어 디스크에 저장됩니다
- 복합 프라이머리 키(primary key)
(UserID, URL)와 - 복합 정렬 키(sorting key)
(UserID, URL, EventTime)입니다.
- 정렬 키만 지정한 경우, 프라이머리 키는 암묵적으로 정렬 키와 동일하게 정의됩니다.
- 메모리 효율을 높이기 위해 쿼리에서 필터링하는 컬럼만 포함하는 프라이머리 키를 명시적으로 지정했습니다. 프라이머리 키를 기반으로 하는 프라이머리 인덱스는 메인 메모리(main memory)에 전체가 로드됩니다.
- 가이드의 다이어그램 전반에서 일관성을 유지하고 압축률을 최대화하기 위해 테이블의 모든 컬럼을 포함하는 별도의 정렬 키를 정의했습니다(예를 들어 정렬을 통해 한 컬럼에서 유사한 데이터가 서로 가까이 배치되면 해당 데이터는 더 잘 압축됩니다).
- 둘 다 지정한 경우 프라이머리 키는 정렬 키의 접두사(prefix)여야 합니다.
EventTime 컬럼)을 기준으로 사전식 오름차순으로 디스크에 저장됩니다.
ClickHouse에서는 프라이머리 키 컬럼 값이 동일한 여러 행을 삽입할 수 있습니다. 이 경우(아래 다이어그램의 행 1과 행 2 참조) 최종 순서는 지정된 정렬 키, 즉
EventTime 컬럼의 값에 따라 결정됩니다.- 디스크상 표현에서는 테이블 컬럼마다 하나의 데이터 파일(
*.bin)이 있으며, 해당 컬럼의 모든 값은 압축된 포맷으로 저장되고, - 887만 개의 행은 프라이머리 키 컬럼(그리고 추가 정렬 키 컬럼)을 기준으로 사전식 오름차순으로 디스크에 저장됩니다. 즉, 이 경우에는
- 먼저
UserID, - 그다음
URL, - 마지막으로
EventTime순입니다.
- 먼저
UserID.bin, URL.bin, EventTime.bin은 UserID, URL, EventTime 컬럼의 값이 저장되는 디스크상의 데이터 파일입니다.
- 프라이머리 키가 디스크상의 행의 사전식 순서를 정의하므로 테이블에는 프라이머리 키를 하나만 둘 수 있습니다.
- ClickHouse 내부 행 번호 체계와 로깅 메시지에서 사용하는 체계에 맞추기 위해 행 번호는 0부터 시작합니다.
데이터는 병렬 처리를 위해 그래뉼로 구성됩니다
컬럼 값이 그래뉼 내부에 물리적으로 저장되는 것은 아닙니다. 그래뉼은 쿼리 처리를 위해 컬럼 값을 논리적으로 구성한 단위일 뿐입니다.
index_granularity 설정이 포함된 결과, 1083개의 그래뉼로 구성되는 방식을 보여줍니다(기본값인 8192로 설정됨).
처음 8192개 행(디스크상의 물리적 순서 기준, 해당 컬럼 값)은 논리적으로 그래뉼 0에 속하고, 그다음 8192개 행(해당 컬럼 값)은 그래뉼 1에 속하는 식입니다.
- 마지막 그래뉼(그래뉼 1082)은 8192개보다 적은 행을 “포함”합니다.
- 이 가이드의 앞부분 “DDL Statement Details”에서 adaptive index granularity를 비활성화했다고 언급했습니다(이 가이드의 설명을 단순화하고 다이어그램과 결과를 재현 가능하게 하기 위해서입니다). 따라서 예시 테이블의 모든 그래뉼(마지막 그래뉼 제외)은 크기가 동일합니다.
- adaptive index granularity가 있는 테이블의 경우(index granularity는 default로 adaptive임) 행 데이터 크기에 따라 일부 그래뉼의 크기가 8192개 행보다 작을 수 있습니다.
-
프라이머리 키 컬럼(
UserID,URL)의 일부 컬럼 값은 주황색으로 표시했습니다. 이렇게 주황색으로 표시된 컬럼 값은 각 그래뉼의 첫 번째 행에 있는 프라이머리 키 컬럼 값입니다. 아래에서 보겠지만, 이렇게 주황색으로 표시된 컬럼 값이 테이블의 프라이머리 인덱스 항목이 됩니다. - ClickHouse 내부 번호 체계에 맞추기 위해 그래뉼 번호는 0부터 시작합니다. 이 번호 체계는 로깅 메시지에도 사용됩니다.
프라이머리 인덱스는 그래뉼당 하나의 항목을 가집니다
- 첫 번째 인덱스 항목(아래 다이어그램의 ‘mark 0’)은 위 다이어그램의 그래뉼 0의 첫 번째 행에 있는 키 컬럼 값을 저장합니다.
- 두 번째 인덱스 항목(아래 다이어그램의 ‘mark 1’)은 위 다이어그램의 그래뉼 1의 첫 번째 행에 있는 키 컬럼 값을 저장하며, 이런 방식으로 계속됩니다.
- adaptive index granularity을 사용하는 테이블의 경우, 마지막 테이블 행의 프라이머리 키 컬럼 값을 기록하는 추가 “final” 마크 1개도 프라이머리 인덱스에 저장됩니다. 하지만 이 가이드의 설명을 단순화하고 다이어그램과 결과를 재현 가능하게 만들기 위해 adaptive index granularity를 비활성화했으므로, 이 예시 테이블의 인덱스에는 해당 final 마크가 포함되지 않습니다.
- 프라이머리 인덱스 파일은 전체가 주 메모리(main memory)에 로드됩니다. 파일이 사용 가능한 여유 메모리 공간보다 크면 ClickHouse에서 오류가 발생합니다.
프라이머리 인덱스 내용 확인
프라이머리 인덱스 내용 확인
자가 관리형 ClickHouse 클러스터에서는 예시 테이블의 프라이머리 인덱스 내용을 확인하기 위해 file 테이블 함수를 사용할 수 있습니다.이를 위해 먼저 실행 중인 클러스터의 노드 하나에서 프라이머리 인덱스 파일을 user_files_path로 복사해야 합니다:
- 1단계: 프라이머리 인덱스 파일이 들어 있는 part 경로 확인
- 2단계: user_files_path 확인 Linux의 기본 user_files_path는
- 3단계: 프라이머리 인덱스 파일을 user_files_path로 복사하기
SELECT path FROM system.parts WHERE table = 'hits_UserID_URL' AND active = 1테스트 머신에서는 /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4가 반환됩니다./var/lib/clickhouse/user_files/이며, Linux에서는 다음과 같이 변경 여부를 확인할 수 있습니다: $ grep user_files_path /etc/clickhouse-server/config.xml테스트 머신에서 이 경로는 /Users/tomschreiber/Clickhouse/user_files/입니다.cp /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4/primary.idx /Users/tomschreiber/Clickhouse/user_files/primary-hits_UserID_URL.idx이제 SQL을 통해 프라이머리 인덱스의 내용을 확인할 수 있습니다:
- 항목 수 확인
- 처음 두 개의 인덱스 마크 확인
- 마지막 인덱스 마크 확인
SELECT count( )<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String');
1083이 반환됩니다.SELECT UserID, URL<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 0, 2;다음이 반환됩니다.240923, http://showtopics.html%3...<br/> 4073710, http://mk.ru&pos=3_0SELECT UserID, URL FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 1082, 1;
다음이 반환됩니다.
4292714039 │ http://sosyal-mansetleri...이는 예시 테이블의 프라이머리 인덱스 내용 다이어그램과 정확히 일치합니다:
-
UserID 인덱스 마크:
프라이머리 인덱스에 저장된
UserID값은 오름차순으로 정렬되어 있습니다.
따라서 위 다이어그램의 ‘mark 1’은 그래뉼 1의 모든 테이블 행과 그 뒤의 모든 그래뉼에서UserID값이 4,073,710 이상임이 보장된다는 뜻입니다.
-
URL 인덱스 마크:
프라이머리 키(primary key) 컬럼
UserID와URL의 카디널리티가 상당히 비슷하므로, 일반적으로 첫 번째 컬럼 다음에 오는 모든 키 컬럼의 인덱스 마크는 현재 그래뉼 내의 모든 테이블 행에서 바로 앞선 키 컬럼의 값이 동일하게 유지될 때에만 데이터 범위를 나타냅니다.
예를 들어, 위 그림에서 mark 0과 mark 1의 UserID 값이 서로 다르므로 ClickHouse는 그래뉼 0에 있는 모든 테이블 행의 URL 값이'http://showtopics.html%3...'보다 크거나 같다고 가정할 수 없습니다. 하지만 위 그림에서 mark 0과 mark 1의 UserID 값이 같다면(즉, 그래뉼 0 내의 모든 테이블 행에서 UserID 값이 동일하게 유지된다면), ClickHouse는 그래뉼 0에 있는 모든 테이블 행의 URL 값이'http://showtopics.html%3...'보다 크거나 같다고 가정할 수 있습니다. 이것이 쿼리 실행 성능에 어떤 영향을 미치는지는 뒤에서 더 자세히 설명하겠습니다.
프라이머리 인덱스는 그래뉼을 선택하는 데 사용됩니다
749927693 값을 가진 UserID 컬럼의 행을 포함할 가능성이 있는 그래뉼을 식별하기 위해 1083개의 UserID 인덱스 마크를 대상으로 이진 검색을 수행했음이 표시됩니다. 여기에는 평균 시간 복잡도 O(log2 n)으로 19단계가 필요합니다:
Trace Log 세부 정보
Trace Log 세부 정보
마크 176이 식별되었으며(‘found left boundary mark’는 포함, ‘found right boundary mark’는 제외), 따라서 그래뉼 176의 8192개 행 전체가(이 가이드의 뒷부분에서 살펴보겠지만 이 그래뉼은 1,441,792번째 행에서 시작합니다) 749927693인 UserID 컬럼 값을 가진 실제 행을 찾기 위해 ClickHouse로 스트리밍됩니다.
결론쿼리가 복합 키의 일부이자 첫 번째 키 컬럼인 컬럼을 기준으로 필터링하면, ClickHouse는 해당 키 컬럼의 인덱스 마크에서 이진 검색 알고리즘을 수행합니다.
앞서 설명했듯이 ClickHouse는 쿼리와 일치하는 행을 포함할 가능성이 있는 그래뉼을 빠르게 선택하기 위해 희소 프라이머리 인덱스를 사용합니다(이진 검색 사용). 이것이 ClickHouse 쿼리 실행의 **첫 번째 단계(그래뉼 선택)**입니다. **두 번째 단계(데이터 읽기)**에서는 ClickHouse가 실제로 쿼리와 일치하는 행을 찾기 위해 선택된 그래뉼의 위치를 찾아, 그 안의 모든 행을 ClickHouse 엔진으로 스트리밍합니다. 다음 섹션에서는 이 두 번째 단계를 더 자세히 설명합니다.
그래뉼 위치를 찾는 데 마크 파일을 사용합니다
그래뉼 선택 세부 정보
그래뉼 선택 세부 정보
위 다이어그램은 마크 176이 첫 번째 인덱스 엔트리임을 보여줍니다. 즉, 연결된 그래뉼 176의 최소 UserID 값은 749.927.693보다 작고, 다음 마크(마크 177)에 해당하는 그래뉼 177의 최소 UserID 값은 이 값보다 큽니다. 따라서 UserID 컬럼 값이 749.927.693인 행을 포함할 가능성이 있는 것은 마크 176에 해당하는 그래뉼 176뿐입니다.
UserID, URL, EventTime 컬럼에 대한 그래뉼의 물리적 위치를 저장하는 세 개의 마크 파일 UserID.mrk, URL.mrk, EventTime.mrk를 보여줍니다.
앞서 프라이머리 인덱스는 0부터 번호가 매겨진 인덱스 마크를 포함하는 단순한 비압축 배열 파일(primary.idx)이라고 설명했습니다.
마찬가지로 마크 파일도 0부터 번호가 매겨진 마크를 포함하는 단순한 비압축 배열 파일(*.mrk)입니다.
ClickHouse가 쿼리에 일치하는 행을 포함할 가능성이 있는 그래뉼의 인덱스 마크를 식별하고 선택하면, 그래뉼의 물리적 위치를 얻기 위해 마크 파일에서 위치 기반 배열 조회를 수행할 수 있습니다.
특정 컬럼에 대한 각 마크 파일 엔트리에는 오프셋 형태로 두 개의 위치가 저장됩니다.
- 첫 번째 오프셋(위 다이어그램의 ‘block_offset’)은 선택한 그래뉼의 압축된 버전을 포함하는 압축된 컬럼 data file 내의 block 위치를 가리킵니다. 이 압축 block에는 몇 개의 압축된 그래뉼이 들어 있을 수 있습니다. 찾은 압축 파일 block은 읽는 시점에 메인 메모리로 압축 해제됩니다.
- 두 번째 오프셋(위 다이어그램의 ‘granule_offset’)은 마크 파일에서 제공되며, 압축 해제된 블록 데이터 내에서 그래뉼의 위치를 나타냅니다.
- wide format을 사용하고 adaptive index granularity를 사용하지 않는 테이블의 경우, ClickHouse는 위에 표시된
.mrk마크 파일을 사용합니다. 이 파일에는 엔트리마다 길이 8바이트인 주소 두 개가 들어 있습니다. 이 엔트리들은 모두 같은 크기를 가진 그래뉼의 물리적 위치입니다.
-
wide format을 사용하고 adaptive index granularity를 사용하는 테이블의 경우, ClickHouse는
.mrk2마크 파일을 사용합니다. 이 파일은.mrk마크 파일과 유사한 엔트리를 포함하지만, 엔트리마다 현재 엔트리가 연결된 그래뉼의 행 수를 나타내는 세 번째 값이 추가됩니다. -
compact format을 사용하는 테이블의 경우, ClickHouse는
.mrk3마크 파일을 사용합니다.
왜 마크 파일이 필요한가프라이머리 인덱스에는 인덱스 마크에 해당하는 그래뉼의 물리적 위치가 왜 직접 포함되지 않을까요?그 이유는 ClickHouse가 설계된 초대규모 환경에서는 디스크와 메모리를 매우 효율적으로 사용하는 것이 중요하기 때문입니다.프라이머리 인덱스 파일은 메인 메모리에 들어가야 합니다.예시 쿼리에서 ClickHouse는 프라이머리 인덱스를 사용해 쿼리와 일치하는 행을 포함할 가능성이 있는 단 하나의 그래뉼을 선택했습니다. 그리고 ClickHouse는 그 하나의 그래뉼에 대해서만, 해당 행을 추가 처리하기 위해 스트리밍하는 데 필요한 물리적 위치를 알면 됩니다.또한 이 오프셋 정보는 UserID 및 URL 컬럼에 대해서만 필요합니다.쿼리에서 사용되지 않는 컬럼(예:
EventTime)에는 오프셋 정보가 필요하지 않습니다.예시 쿼리의 경우 ClickHouse에는 UserID 데이터 파일(UserID.bin)의 그래뉼 176에 대한 2개의 물리적 위치 오프셋과 URL 데이터 파일(URL.bin)의 그래뉼 176에 대한 2개의 물리적 위치 오프셋만 필요합니다.마크 파일이 제공하는 간접 참조를 사용하면 프라이머리 인덱스 안에 3개 컬럼 전체의 1083개 그래뉼에 대한 물리적 위치 엔트리를 직접 저장하지 않아도 되므로, 메인 메모리에 불필요한(잠재적으로 사용되지 않을 수도 있는) 데이터를 두지 않아도 됩니다.여러 프라이머리 인덱스 사용
보조 키 컬럼은 비효율적일 수도, 아닐 수도 있습니다
여기서는 쿼리가 첫 번째 키 컬럼으로는 명시적으로 필터링하지 않고, 보조 키 컬럼으로 필터링하는 시나리오를 다룹니다.쿼리가 첫 번째 키 컬럼과 그 뒤의 다른 키 컬럼을 모두 기준으로 필터링하는 경우, ClickHouse는 첫 번째 키 컬럼의 인덱스 마크를 대상으로 이진 검색을 실행합니다.
“http://public_search” URL을 가장 자주 클릭한 상위 10명의 사용자를 계산하는 쿼리를 사용합니다:
일반 제외 검색 알고리즘
- URL 값이 “W3”인 행을 찾는 쿼리
- UserID와 URL 값을 단순화한 hits 테이블의 추상화된 버전
- 인덱스에 동일한 복합 기본 키 (UserID, URL)를 사용합니다. 즉, 행은 먼저 UserID 값 기준으로 정렬되며, 같은 UserID 값을 가진 행은 그다음 URL 기준으로 정렬됩니다.
- 그래뉼 크기는 2이며, 즉 각 그래뉼에는 2개의 행이 포함됩니다.
- URL 값이 W3보다 작고, 바로 다음 인덱스 마크의 URL 값도 W3보다 작은 인덱스 마크 0은 제외할 수 있습니다. 마크 0과 1이 동일한 UserID 값을 가지기 때문입니다. 이 제외 전제조건은 그래뉼 0이 전부 U1 UserID 값으로만 구성되어 있음을 보장하므로, ClickHouse는 그래뉼 0의 최대 URL 값 역시 W3보다 작다고 가정하고 해당 그래뉼을 제외할 수 있습니다.
- URL 값이 W3보다 작거나 같고, 바로 다음 인덱스 마크의 URL 값이 W3보다 크거나 같은 인덱스 마크 1은 선택됩니다. 이는 그래뉼 1에 URL이 W3인 행이 포함되어 있을 수 있음을 의미하기 때문입니다.
- URL 값이 W3보다 큰 인덱스 마크 2와 3은 제외할 수 있습니다. 프라이머리 인덱스의 인덱스 마크는 각 그래뉼의 첫 번째 테이블 행에 대한 키 컬럼 값을 저장하고, 테이블 행은 디스크에서 키 컬럼 값 기준으로 정렬되어 있으므로 그래뉼 2와 3에는 URL 값 W3가 포함될 수 없습니다.
결론ClickHouse는 쿼리가 복합 키에 포함되어 있지만 첫 번째 키 컬럼은 아닌 컬럼을 필터링할 때 이진 검색 알고리즘 대신 일반 제외 검색 알고리즘을 사용하며, 이 알고리즘은 앞선 키 컬럼의 카디널리티가 낮을수록 가장 효과적입니다.
데이터 스키핑 인덱스에 대한 참고 사항
ALTER TABLE 문의 GRANULARITY 4 절에 유의하십시오) URL의 최소값과 최대값이 저장됩니다:
첫 번째 인덱스 항목(위 다이어그램의 ‘mark 0’)에는 테이블의 처음 4개 그래뉼에 속한 행에 대한 URL의 최소값과 최대값이 저장됩니다.
두 번째 인덱스 항목(‘mark 1’)에는 테이블의 다음 4개 그래뉼에 속한 행에 대한 URL의 최소값과 최대값이 저장되며, 이후에도 같은 방식으로 계속됩니다.
(ClickHouse는 또한 인덱스 마크와 연결된 그래뉼 그룹을 찾기 위한 데이터 스키핑 인덱스용 특수한 마크 파일도 생성했습니다.)
UserID와 URL의 카디널리티가 비슷하게 높기 때문에, 이 보조 데이터 스키핑 인덱스는 URL로 필터링하는 쿼리가 실행될 때 선택 대상에서 그래뉼을 제외하는 데 도움이 되지 않습니다.
쿼리가 찾는 특정 URL 값(즉, ‘http://public_search')은 각 그래뉼 그룹에 대해 인덱스에 저장된 최소값과 최대값 사이에 있을 가능성이 매우 높습니다. 따라서 ClickHouse는 해당 그래뉼 그룹을 선택할 수밖에 없습니다(쿼리와 일치하는 행이 포함되어 있을 수 있기 때문입니다).
여러 프라이머리 인덱스를 사용해야 하는 이유
추가 프라이머리 인덱스를 생성하는 옵션
- 서로 다른 프라이머리 키를 가진 두 번째 테이블 생성
- 기존 테이블에 materialized view 생성
- 기존 테이블에 PROJECTION 추가
옵션 1: 보조 테이블
UserIDs로 필터링하는 예시 쿼리도 새로 추가된 테이블에서는 그다지 효율적으로 실행되지 않습니다. 이제 UserID가 해당 테이블의 프라이머리 인덱스에서 두 번째 키 컬럼이므로, ClickHouse는 그래뉼 선택에 일반 제외 검색을 사용합니다. 그런데 UserID와 URL은 카디널리티가 비슷하게 높아 이 방식이 그다지 효과적이지 않습니다.
자세한 내용은 세부 정보 상자를 열어 확인하십시오.
이제 UserIDs로 필터링하는 쿼리의 성능이 좋지 않습니다
이제 UserIDs로 필터링하는 쿼리의 성능이 좋지 않습니다
UserIDs로 필터링하는 쿼리와 URL로 필터링하는 쿼리의 속도를 높이도록 최적화되어 있습니다:
옵션 2: Materialized Views
- 뷰의 프라이머리 키에서는 원본 테이블과 비교해 키 컬럼의 순서를 바꿉니다
- materialized view는 암시적으로 생성된 테이블을 기반으로 하며, 그 테이블의 행 순서와 프라이머리 인덱스는 지정된 프라이머리 키 정의를 따릅니다
- 암시적으로 생성된 테이블은
SHOW TABLES쿼리 결과에 표시되며, 이름은.inner로 시작합니다 - 먼저 materialized view의 기반 테이블을 명시적으로 생성한 다음,
TO [db].[table]절을 통해 뷰가 해당 테이블을 대상으로 하도록 할 수도 있습니다 - 원본 테이블 hits_UserID_URL의 887만 개 전체 행을 암시적으로 생성된 테이블에 즉시 채우기 위해
POPULATE키워드를 사용합니다 - 원본 테이블 hits_UserID_URL에 새 행이 삽입되면, 해당 행도 암시적으로 생성된 테이블에 자동으로 삽입됩니다
- 결과적으로 암시적으로 생성된 테이블은 명시적으로 생성한 보조 테이블과 동일한 행 순서와 프라이머리 인덱스를 갖습니다:
옵션 3: PROJECTION
- PROJECTION은 PROJECTION에 지정된
ORDER BY절을 기준으로 행 순서와 프라이머리 인덱스를 갖는 숨겨진 테이블을 생성합니다 - 숨겨진 테이블은
SHOW TABLES쿼리 결과에 표시되지 않습니다 MATERIALIZE키워드를 사용해 원본 테이블 hits_UserID_URL의 총 887만 행 전체를 숨겨진 테이블에 즉시 채웁니다- 원본 테이블 hits_UserID_URL에 새 행이 삽입되면 해당 행도 자동으로 숨겨진 테이블에 삽입됩니다
- 쿼리는 항상 (구문상으로는) 원본 테이블 hits_UserID_URL을 대상으로 하지만, 숨겨진 테이블의 행 순서와 프라이머리 인덱스를 사용하는 편이 더 효율적으로 실행될 수 있다면 대신 그 숨겨진 테이블이 사용됩니다
- PROJECTION은
ORDER BY를 사용하는 쿼리를 더 효율적으로 만들지는 않는다는 점에 유의하십시오.ORDER BY가 PROJECTION의ORDER BY절과 일치하더라도 마찬가지입니다(https://github.com/ClickHouse/ClickHouse/issues/47333 참조) - 결과적으로, 암묵적으로 생성된 숨겨진 테이블은 명시적으로 생성한 보조 테이블과 동일한 행 순서와 프라이머리 인덱스를 가집니다:
요약
정렬 키 컬럼을 효율적으로 배치하기
- 쿼리에서 보조 키 컬럼에 대한 필터링 효율성과
- 테이블 데이터 파일의 압축률입니다.
UserID 컬럼)의 URL(URL 컬럼) 접근이 봇 트래픽으로 표시되었는지 여부를 나타내는 세 개의 컬럼(IsRobot 컬럼 포함)이 있습니다.
앞서 언급한 세 개의 컬럼을 모두 포함하는 복합 기본 키를 사용하겠습니다. 이 키는 다음과 같은 일반적인 웹 분석 쿼리의 속도를 높이는 데 활용할 수 있습니다.
- 특정 URL에 대한 트래픽 중 몇 퍼센트가 봇에서 발생했는지 또는
- 특정 사용자가 봇인지 아닌지를 얼마나 확신할 수 있는지(해당 사용자의 트래픽 중 몇 퍼센트가 봇 트래픽으로 간주되거나 간주되지 않는지)
clickhouse client에서 실행하세요:
URL 컬럼과 IsRobot 컬럼의 차이가 큽니다. 따라서 복합 기본 키에서 이들 컬럼의 순서는, 해당 컬럼을 기준으로 필터링하는 쿼리의 성능 향상과 테이블의 컬럼 데이터 파일에 대한 최적의 압축률 달성에 모두 중요합니다.
이를 보여주기 위해 봇 트래픽 분석 데이터용으로 두 가지 테이블 버전을 생성합니다:
- 카디널리티가 높은 순서로 키 컬럼을 정렬한 복합 기본 키
(URL, UserID, IsRobot)를 사용하는 테이블hits_URL_UserID_IsRobot - 카디널리티가 낮은 순서로 키 컬럼을 정렬한 복합 기본 키
(IsRobot, UserID, URL)를 사용하는 테이블hits_IsRobot_UserID_URL
(URL, UserID, IsRobot)를 사용하는 테이블 hits_URL_UserID_IsRobot를 생성합니다:
(IsRobot, UserID, URL)를 사용하는 hits_IsRobot_UserID_URL 테이블을 생성합니다:
보조 키 컬럼에 대한 효율적인 필터링
(URL, UserID, IsRobot)을 정렬한 테이블에서 UserID 컬럼으로 필터링하는 쿼리입니다:
(IsRobot, UserID, URL)을 카디널리티 오름차순으로 정렬한 테이블에서 실행한 동일한 쿼리입니다:
데이터 파일의 최적 압축률
UserID 컬럼의 압축률을 비교합니다:
UserID 컬럼의 압축률은 키 컬럼 (IsRobot, UserID, URL)을 카디널리티 기준 오름차순으로 정렬한 테이블에서 훨씬 더 높다는 것을 확인할 수 있습니다.
두 테이블에는 정확히 동일한 데이터가 저장되어 있지만(두 테이블 모두에 동일한 887만 개의 행을 삽입했습니다), 복합 기본 키(compound primary key)에서 키 컬럼의 순서는 테이블의 압축된 데이터가 테이블의 컬럼 데이터 파일에서 얼마나 많은 디스크 공간을 필요로 하는지에 큰 영향을 미칩니다:
- 카디널리티 기준 내림차순으로 키 컬럼을 정렬한 복합 기본 키
(URL, UserID, IsRobot)를 사용하는 테이블hits_URL_UserID_IsRobot에서는UserID.bin데이터 파일이 디스크 공간 11.24 MiB를 차지합니다 - 카디널리티 기준 오름차순으로 키 컬럼을 정렬한 복합 기본 키
(IsRobot, UserID, URL)를 사용하는 테이블hits_IsRobot_UserID_URL에서는UserID.bin데이터 파일이 디스크 공간 877.47 KiB만 차지합니다
cl 값으로 정렬되고, 같은 cl 값을 가진 행들은 ch 값으로 정렬됩니다. 그리고 첫 번째 키 컬럼 cl은 낮은 카디널리티를 가지므로 같은 cl 값을 가진 행이 있을 가능성이 높습니다. 그 결과 ch 값도 정렬된 상태가 될 가능성이 높습니다(국소적으로, 즉 같은 cl 값을 가진 행들 내에서).
컬럼에서 비슷한 데이터가 예를 들어 정렬을 통해 서로 가깝게 배치되면, 그 데이터는 더 잘 압축됩니다.
일반적으로 압축 알고리즘은 데이터의 연속 구간 길이(run length)가 길수록(더 많은 데이터를 연속해서 볼수록 압축에 유리함)
그리고 지역성(locality)이 높을수록(데이터가 더 유사할수록 압축률이 더 높아짐) 이점을 얻습니다.
반대로 아래 다이어그램은 키 컬럼이 카디널리티 기준 내림차순으로 정렬된 기본 키의 디스크상 행 순서를 개략적으로 보여줍니다:
이제 테이블의 행은 먼저 ch 값 기준으로 정렬되며, 동일한 ch 값을 가진 행들은 cl 값 기준으로 정렬됩니다.
하지만 첫 번째 키 컬럼인 ch의 카디널리티가 높기 때문에, 같은 ch 값을 가진 행이 존재할 가능성은 낮습니다. 따라서 cl 값도 정렬되어 있을 가능성이 낮습니다(로컬하게는, 즉 같은 ch 값을 가진 행 내에서만 해당).
따라서 cl 값은 대부분 무작위 순서로 배치될 가능성이 높고, 그 결과 데이터 지역성(locality)과 압축률이 모두 좋지 않습니다.
요약
단일 행을 효율적으로 식별하기
구체적인 예시
- 내용이 변경될 때 행의 삽입 순서(예를 들어 텍스트 영역에 텍스트를 입력하는 키 입력으로 인해 변경되는 경우)와
PRIMARY KEY (hash)를 사용할 때 삽입된 행의 데이터가 디스크에 저장되는 순서:
hash 컬럼이 기본 키(primary key) 컬럼으로 사용되기 때문에
- 특정 행은 매우 빠르게 조회할 수 있지만,
- 테이블의 행(해당 컬럼 데이터)은 (고유하고 무작위적인) hash 값의 오름차순으로 정렬되어 디스크에 저장됩니다. 따라서 content 컬럼의 값도 데이터 지역성 없이 무작위 순서로 저장되어 content 컬럼 데이터 파일의 압축률이 최적 수준에 미치지 못합니다.
- 앞서 설명한 대로 서로 다른 데이터에 대해 서로 다른 값을 갖는 내용의 hash와
- 데이터가 조금만 바뀌어도 변하지 않는 로컬리티 민감 해시(fingerprint)입니다.
- 내용이 변경될 때 행의 삽입 순서(예를 들어 텍스트 영역에 텍스트를 입력하는 키 입력으로 인해 변경되는 경우)와
- 복합
PRIMARY KEY (fingerprint, hash)를 사용할 때 삽입된 행의 데이터가 디스크에 저장되는 순서:
fingerprint를 기준으로 정렬되고, fingerprint 값이 같은 행들 사이에서는 hash 값이 최종 순서를 결정합니다.
작은 변경만 있는 데이터는 동일한 fingerprint 값을 갖게 되므로, 이제 유사한 데이터가 content 컬럼에서 디스크상 서로 가까이 저장됩니다. 이는 content 컬럼의 압축률에 매우 유리합니다. 일반적으로 압축 알고리즘은 데이터 지역성의 이점을 활용하기 때문입니다(데이터가 더 비슷할수록 압축률도 더 좋아집니다).
이 방식에서 감수해야 하는 점은 복합 PRIMARY KEY (fingerprint, hash)로 생성되는 프라이머리 인덱스(primary index)를 최적으로 활용하려면 특정 행을 조회할 때 두 개의 필드(fingerprint와 hash)가 모두 필요하다는 것입니다.