ORDER BY и первичный ключ: ключ к скорости

Самое важное решение при создании таблицы — порядок ключа сортировки.

ORDER BY в MergeTree задаёт, по каким колонкам и в каком порядке сортируются данные внутри куска; на нём же строится разрежённый первичный индекс.

Не индекс по строке, а индекс по гранулам

В OLTP-базах индекс указывает на каждую строку. В ClickHouse индекс разрежённый: данные отсортированы по ORDER BY, разбиты на гранулы по ~8192 строки, и индекс хранит лишь значение ключа в начале каждой гранулы (mark). Схема:

granule 0:  ключ начинается с 2024-01-01
granule 1:  ключ начинается с 2024-01-05
granule 2:  ключ начинается с 2024-01-10
...

Запрос WHERE event_date = '2024-01-05' по индексу понимает: нужная дата может быть только в гранулах 1–2. Остальные гранулы движок пропускает, не читая с диска. Это и есть отсечение (pruning) — главный источник скорости.

Порядок колонок в ключе решает всё

Правило: от часто фильтруемого и «грубого» к редкому и «точному». Сравните два варианта для таблицы событий:

ORDER BY	Когда хорош
`(event_date, user_id)`	фильтры по дате (диапазоны дней) — самый частый случай аналитики
`(user_id, event_date)`	выборки по конкретному пользователю

Если ваши запросы почти всегда фильтруют по дате, первый вариант отсечёт огромную часть данных; второй — почти ничего, ведь данные «перемешаны» по дате.

Первичный ключ ≠ уникальность

Важная мысль: первичный ключ ClickHouse — это про порядок и индекс, а не про уникальность. В MergeTree он по умолчанию совпадает с ORDER BY (можно задать PRIMARY KEY как префикс ORDER BY). Никто не запрещает дубликаты ключа — это сделано ради скорости вставки.

Маленький расчёт отсечения

Покажем, как растёт экономия чтения при удачном ключе (чистый Python):

total_granules = 10000      # всего гранул в таблице
matching_granules = 50      # подходят под условие WHERE
read_share = matching_granules / total_granules
print("Читаем гранул:", matching_granules, "из", total_granules)
print("Это", round(read_share * 100, 2), "% данных")
print("Ускорение примерно в", total_granules // matching_granules, "раз")

Вывод:

Читаем гранул: 50 из 10000
Это 0.5 % данных
Ускорение примерно в 200 раз

Как работает под капотом

Индекс (засечки) обычно умещается в памяти. По условию WHERE на ключевых колонках ClickHouse бинарным поиском находит диапазон подходящих гранул и читает только их сжатые блоки. Чем лучше условие совпадает с префиксом ORDER BY, тем больше гранул отсекается.

Частые ошибки

ORDER BY не под запросы. Если ключ не совпадает с тем, по чему вы фильтруете, отсечения не будет и запрос прочитает всё.
Случайная колонка первой в ключе. Высококардинальная «перемешивающая» колонка (например, UUID) первой в ORDER BY убивает отсечение по другим полям.
Ждать уникальности по ключу. MergeTree допускает дубликаты ключа; для дедупликации есть специальные движки.

Итоги

ORDER BY задаёт сортировку и разрежённый индекс по гранулам.
Подходящий ключ отсекает ненужные гранулы — отсюда скорость.
Порядок колонок выбирают под реальные фильтры запросов.
Первичный ключ — про порядок и индекс, а не про уникальность.