Проектирование ORDER BY ключа

ORDER BY ключ определяет физический порядок строк внутри каждого part. Это самый важный параметр MergeTree таблицы: он напрямую влияет на то, сколько гранул ClickHouse прочитает при выполнении запроса. Неправильный ORDER BY превращает аналитическую СУБД в full scan движок.

В предыдущем уроке о sparse index мы видели, что primary.idx хранит значения ORDER BY ключа для первой строки каждой гранулы и позволяет бинарный поиск. Теперь разберём, как спроектировать ключ, чтобы этот бинарный поиск работал максимально эффективно.

Правило кардинальности: от низкой к высокой

Основное правило проектирования ORDER BY ключа: столбцы с низкой кардинальностью первыми, с высокой — последними. Типичная длина ключа: 3-5 столбцов.

Почему это работает: ClickHouse читает столбцы ORDER BY ключа слева направо при бинарном поиске по primary.idx. Первый столбец обеспечивает самую грубую фильтрацию, каждый следующий уточняет диапазон.

Когда столбец с низкой кардинальностью стоит первым:

• Все строки одного значения (например, одного tenant) физически рядом
• Бинарный поиск по первому столбцу сразу исключает огромные блоки данных
• Данные внутри каждой группы отсортированы по следующим столбцам

Когда столбец с высокой кардинальностью стоит первым:

• Строки разных значений (например, разных tenant) перемешаны
• Фильтрация по второму столбцу ключа требует сканирования всех гранул

Пример: multi-tenant аналитика

Таблица событий аналитической платформы. Столбцы: tenant_id (100 арендаторов), event_date (365 дней), user_id (миллионы). Типичный запрос: WHERE tenant_id = 5 AND event_date = '2024-01-15'.

-- Правильно: low cardinality first
CREATE TABLE events (
    tenant_id UInt16,
    event_date Date,
    user_id UInt64,
    event_type String,
    payload String
) ENGINE = MergeTree()
ORDER BY (tenant_id, event_date, user_id)

Порядок (tenant_id, event_date, user_id):

• tenant_id (100 значений) — грубая фильтрация, исключает 99% данных
• event_date (365 значений) — уточнение до конкретного дня
• user_id (миллионы) — финальная сортировка внутри дня

Пример: click-stream данные

Таблица кликов на сайте. Столбцы: site_id (50 сайтов), timestamp (наносекунды), session_id (миллионы). Типичные запросы: фильтрация по сайту и временному диапазону.

CREATE TABLE clicks (
    site_id UInt16,
    timestamp DateTime,
    session_id String,
    url String,
    referrer String
) ENGINE = MergeTree()
ORDER BY (site_id, toStartOfHour(timestamp), session_id, timestamp)

• site_id (50) — первый фильтр, исключает данные других сайтов
• toStartOfHour(timestamp) — группировка по часам, эффективный диапазонный scan
• session_id — группировка визитов одной сессии вместе
• timestamp — точная сортировка внутри сессии

Обратите внимание: toStartOfHour(timestamp) — это выражение, а не столбец. ClickHouse вычисляет его при INSERT и хранит в primary.idx. Это позволяет группировать данные по часам без создания отдельного столбца.

Исключение: высокая кардинальность первой

Правило “низкая кардинальность первой” имеет важное исключение: если столбец с высокой кардинальностью всегда присутствует в WHERE, он может стоять первым.

Пример: таблица истории действий пользователя. Все запросы фильтруют по user_id:

-- user_id имеет миллионы значений (высокая кардинальность)
-- НО каждый запрос ВСЕГДА содержит WHERE user_id = ...
CREATE TABLE user_actions (
    user_id UInt64,
    action_date Date,
    action_type String,
    details String
) ENGINE = MergeTree()
ORDER BY (user_id, action_date)

Здесь user_id первым оправдан: каждый запрос начинается с точной фильтрации по пользователю, и бинарный поиск по primary.idx мгновенно находит нужный диапазон гранул.

Если бы user_id стоял вторым (ORDER BY (action_date, user_id)), запрос WHERE user_id = 42 потребовал бы полного сканирования всех гранул.

Влияние на сжатие

ORDER BY определяет не только эффективность запросов, но и коэффициент сжатия. Когда столбцы с низкой кардинальностью стоят первыми, одинаковые значения группируются вместе в .bin файлах. Кодеки LZ4 и ZSTD эффективнее сжимают последовательности повторяющихся значений.

Пример: столбец country (200 стран) в таблице с 1 миллиардом строк:

• ORDER BY (country, ...) — все строки одной страны рядом. LZ4 сжимает серию из миллионов одинаковых значений в минимум байт
• ORDER BY (user_id, ...) — значения country разбросаны случайным образом. Сжатие значительно хуже

Анти-паттерн: timestamp первым в multi-tenant

-- Анти-паттерн для multi-tenant систем
CREATE TABLE events (
    timestamp DateTime,
    tenant_id UInt16,
    user_id UInt64,
    data String
) ENGINE = MergeTree()
ORDER BY (timestamp, tenant_id, user_id)

Проблемы:

• Данные всех tenant перемешаны по временной оси
• WHERE tenant_id = 5 — бинарный поиск по timestamp бесполезен, tenant_id распределён по всем гранулам
• Даже WHERE tenant_id = 5 AND timestamp BETWEEN ... — сначала pruneит по timestamp (грубо), потом фильтрует по tenant_id построчно

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

1. Столбцы с низкой кардинальностью первыми — основное правило. Это обеспечивает максимальный прунинг гранул при бинарном поиске по primary.idx.
2. Исключение: высокая кардинальность первой, если столбец ВСЕГДА в WHERE. Пример: user_id в user-centric системе.
3. 3-5 столбцов — типичная длина ORDER BY ключа. Меньше — недостаточная селективность. Больше — избыточный primary.idx.
4. Сжатие зависит от ORDER BY — одинаковые значения рядом сжимаются лучше. Низкая кардинальность первой = лучший compression ratio.
5. ORDER BY нельзя переупорядочить после создания таблицы. Можно только добавить столбцы в конец через ALTER TABLE MODIFY ORDER BY. Проектируйте тщательно с первого раза.