최좌측 매칭 원칙 개요

이 원칙은 복합 인덱스 활용 시 중요한 개념이다:

• 인덱스의 가장 왼쪽 컬럼부터 연속적으로 조건이 있어야 인덱스를 효과적으로 사용할 수 있다.
• 범위 조건(>, <, between, like 등)이 나타나면 그 이후 컬럼은 인덱스를 이용하지 못한다.
• 예를 들어 (a,b) 순서로 인덱스가 구성되었을 때, b=2 조건만으로는 인덱스를 사용할 수 없다.
• a=1 AND b=2 형태라면 a,b 순서가 바뀌어도 옵티마이저가 자동으로 조정하여 인덱스를 활용한다.
• (a,b,c,d) 인덱스에서 c>3 조건이 있으면 d 컬럼은 인덱스를 사용할 수 없다.

테스트 환경 및 데이터 구조

테이블 생성 구문 (약 3.5억 건 데이터)

-- 첫 번째 테이블
CREATE TABLE test.test_event_combine (
    id_project Int32,
    user_id Int64,
    group_id Int32,
    flag_status Int8,
    platform String,
    platform_name String,
    server_identifier Int32,
    operating_system String,
    device_uuid String,
    game_name String,
    status_info String,
    client_ip String,
    timestamp_log Int64,
    type_logging String,
    event_type String,
    key_value_data String
) ENGINE = MergeTree
ORDER BY (id_project, timestamp_log)
SETTINGS index_granularity = 8192;

-- 두 번째 테이블
CREATE TABLE test.test_logtime_index (
    id_project Int32,
    user_id Int64,
    group_id Int32,
    flag_status Int8,
    platform String,
    platform_name String,
    server_identifier Int32,
    operating_system String,
    device_uuid String,
    game_name String,
    status_info String,
    client_ip String,
    timestamp_log Int64,
    type_logging String,
    event_type String,
    key_value_data String
) ENGINE = MergeTree
ORDER BY (timestamp_log, id_project)
SETTINGS index_granularity = 8192;

쿼리 성능 비교 결과

SELECT count(id_project) AS cnt
FROM test.test_event_combine
WHERE id_project IN (
    6609291, 182776, 7016351, 336626, 
    121096, 522472, 6997365, 197394, 
    4979687, 6898037, 2543060, 7011696, 
    6687321
);
결과: 1 row. 소요시간: 0.014초
처리된 행: 425,980개 (3002만 rows/sec)

SELECT count(id_project) AS cnt
FROM test.test_logtime_index
WHERE id_project IN (
    6609291, 182776, 7016351, 336626, 
    121096, 522472, 6997365, 197394, 
    4979687, 6898037, 2543060, 7011696, 
    6687321
);
결과: 1 row. 소요시간: 0.989초
처리된 행: 355,820,000개 (35972만 rows/sec)

실행 계획 분석

두 실행계획 모두 id_project 키를 사용했지만 선택된 그라뉼 수치 차이가 큼을 확인할 수 있으며, 두 번째 경우 전체 데이터 스캔에 가까운 성능 저하가 발생했다.

ClickHouse 프로젝션(Projection) 이해

개념 설명

프로젝션은 파트(part) 레벨에서 정의되는 특수한 구조로, ClickHouse 21.8 버전부터 지원하는 실험적 기능이다. 일반적인 머터ialized view와 유사하지만 다음과 같은 특징을 갖는다:

• 원본 테이블 파티션 내부에 저장되어 일관성 보장
• 자동으로 쿼리 최적화에 적용됨
• 최대 20배 이상의 쿼리 성능 향상 가능

핵심 특징

• 파트 레벨 저장: 프로젝션 데이터는 원본 테이블 파티션 디렉토리에 저장되어 세부 데이터와 집계형 프로젝션 모두 지원
• 무감지 자동 적용: 하나의 MergeTree 테이블에 여러 프로젝션 생성 가능하며, SELECT 문 수행 시 자동으로 최적의 프로젝션 선택
• 데이터 동기화 보장: 원본 테이블과 동일한 생명주기를 가지므로 데이터 불일치 문제 없음

매칭 규칙

• WHERE 절은 프로젝션 정의의 GROUP BY 항목의 하위 집합이어야 함
• GROUP BY 절은 프로젝션 정의의 GROUP BY 항목의 하위 집합이어야 함
• SELECT 절은 프로젝션 정의의 SELECT 항목의 하위 집합이어야 함
• 여러 프로젝션이 매칭될 경우 파트 읽기가 가장 적은 것을 선택

주의사항

• 실험적 기능으로 allow_experimental_projection_optimization=1 설정 필요
• FINAL 키워드가 포함된 SELECT 문에서는 프로젝션 미지원

프로젝션 최적화 적용 예시

SET allow_experimental_projection_optimization = 1;

ALTER TABLE test.test_logtime_index 
ADD PROJECTION project_by_pid (
    SELECT * ORDER BY id_project
);

ALTER TABLE test.test_logtime_index 
MATERIALIZE PROJECTION project_by_pid;

-- 처리 결과 --
1 row. 소요시간: 0.057초
처리된 행: 483,260개 (841만 rows/sec)

프로젝션 적용 후 실행 계획 변화

"Plan": {
  "Node Type": "Expression",
  "Description": "(Projection + Before ORDER BY)",
  "Plans": [
    {
      "Node Type": "Aggregating",
      "Plans": [
        {
          "Node Type": "Expression",
          "Description": "Before GROUP BY",
          "Plans": [
            {
              "Node Type": "SettingQuotaAndLimits",
              "Description": "Set limits and quota after reading from storage",
              "Plans": [
                {
                  "Node Type": "ReadFromStorage",
                  "Description": "MergeTree(with Normal projection project_by_pid)"

결론

ClickHouse의 기본키 인덱스는 희소(sparse) 인덱스로 구성되어 있으며 각 8192행마다 하나의 인덱스 값을 저장한다. 이러한 구조는 메모리 효율성을 높이지만 탐색 시 이진 검색(binary search) 방식(log n 복잡도)을 사용해야 한다.

복합 인덱스의 경우 MySQL과 유사하게 최좌측 매칭 원칙을 따라야 하며, 중간 컬럼이 생략되면 해당 인덱스는 효과적으로 활용되지 않는다. 본 테스트를 통해 ORDER BY 절에서 주요 필터링 컬럼을 앞에 배치하는 것이 쿼리 성능에 큰 영향을 미친다는 것을 확인할 수 있었다.

또한 프로젝션 기능을 활용하면 공간을 희생하여 시간적 성능을 획기적으로 개선할 수 있으며, 자동으로 최적의 프로젝션이 선택되기 때문에 운영 환경에서도 유용하게 활용할 수 있다.