ORDER BY, PRIMARY KEY, PARTITION BY: три ключа, три задачи

MergeTree таблица принимает три ключевых выражения: ORDER BY, PRIMARY KEY и PARTITION BY. Каждое управляет отдельным аспектом хранения данных. Путаница между ними — одна из самых частых ошибок при проектировании ClickHouse таблиц.

Три ключа: что каждый контролирует

ORDER BY: фундамент хранения

ORDER BY — единственное обязательное выражение. Оно определяет:

• Порядок строк в каждом .bin файле
• Какие значения записываются в primary.idx (если PRIMARY KEY не указан отдельно)
• Эффективность гранулярного прунинга запросов

-- Минимальное определение таблицы
CREATE TABLE events (
    event_date Date,
    user_id UInt64,
    data String
) ENGINE = MergeTree()
ORDER BY (event_date, user_id)
-- PRIMARY KEY = (event_date, user_id) автоматически
-- PARTITION BY не задан = одна партиция

PRIMARY KEY: когда и зачем отличать от ORDER BY

По умолчанию PRIMARY KEY совпадает с ORDER BY. В большинстве случаев этого достаточно. Но есть важный сценарий, когда их разделение полезно: ReplacingMergeTree (и другие движки с дедупликацией).

В ReplacingMergeTree ORDER BY определяет ключ дедупликации: строки с одинаковыми значениями ORDER BY столбцов считаются дубликатами, и при merge остаётся строка с максимальной версией. Но для sparse index не все столбцы ORDER BY одинаково полезны.

CREATE TABLE user_profiles (
    user_id UInt64,
    event_time DateTime,
    name String,
    email String
) ENGINE = ReplacingMergeTree(event_time)
ORDER BY (user_id, event_time)    -- полный ключ дедупликации
PRIMARY KEY (user_id)             -- короткий sparse index

Разделение означает:

• ORDER BY (user_id, event_time) — определяет порядок строк на диске и ключ дедупликации. Строки с одинаковыми (user_id, event_time) считаются дубликатами
• PRIMARY KEY (user_id) — в primary.idx записывается только user_id. Sparse index занимает меньше памяти (1 столбец вместо 2), а бинарный поиск по user_id работает так же эффективно

Ограничение: PRIMARY KEY обязан быть префиксом ORDER BY. Если ORDER BY = (a, b, c), PRIMARY KEY может быть (a), (a, b) или (a, b, c). Нельзя задать PRIMARY KEY = (b) или (a, c).

-- Ошибка: PRIMARY KEY не является префиксом ORDER BY
CREATE TABLE t (
    a UInt32, b UInt32, c UInt32
) ENGINE = MergeTree()
ORDER BY (a, b, c)
PRIMARY KEY (b)  -- ERROR: Primary key must be a prefix of the sorting key

PARTITION BY: независимость от ORDER BY

PARTITION BY полностью независим от ORDER BY. Можно партиционировать по месяцу, а сортировать по user_id:

CREATE TABLE events (
    user_id UInt64,
    event_date Date,
    data String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)   -- группировка по месяцам
ORDER BY (user_id, event_date)       -- сортировка по user_id внутри каждого part

Каждая партиция (месяц) содержит свои parts. Внутри каждого part строки отсортированы по (user_id, event_date). Parts из разных партиций никогда не мержатся.

Полный пример: все три ключа

CREATE TABLE user_actions (
    user_id UInt64,
    action_time DateTime,
    action_type String,
    details String
) ENGINE = ReplacingMergeTree(action_time)
PARTITION BY toYYYYMM(action_time)            -- lifecycle: TTL по месяцам
ORDER BY (user_id, action_time)               -- dedup: (user_id, action_time) = unique action
PRIMARY KEY (user_id)                         -- sparse index: только user_id для экономии RAM

• PARTITION BY toYYYYMM — партиции по месяцам, TTL удаляет старые месяцы
• ORDER BY (user_id, action_time) — строки отсортированы для дедупликации: одинаковые (user_id, action_time) = дубликат
• PRIMARY KEY (user_id) — в primary.idx только user_id. Запрос WHERE user_id = 42 эффективно pruneит гранулы. Запрос WHERE action_time BETWEEN ... без user_id — full scan внутри партиции

Ключевые выводы

1. ORDER BY — физический порядок строк. Обязателен. Определяет эффективность прунинга гранул и ключ дедупликации для *MergeTree-вариантов.
2. PRIMARY KEY — столбцы sparse index (primary.idx). По умолчанию = ORDER BY. Отдельно задаётся только для экономии RAM в сценариях, где ORDER BY длиннее, чем нужно для прунинга.
3. PARTITION BY — группировка данных для lifecycle. Независим от ORDER BY. Parts из разных партиций не мержатся.
4. PRIMARY KEY = префикс ORDER BY — нарушение этого правила вызывает ошибку. Нельзя задать PRIMARY KEY из столбцов, не входящих в ORDER BY или входящих в другом порядке.
5. Разделение ORDER BY и PRIMARY KEY оправдано в ReplacingMergeTree: ORDER BY включает полный ключ дедупликации, PRIMARY KEY — короткий префикс для экономии sparse index.