Что такое index в ClickHouse и что такое sparse indexes (primary index)?

Индексы в ClickHouse: sparse primary index

ClickHouse принципиально отличается от реляционных СУБД в подходе к индексированию. Здесь нет B-tree индексов по отдельным строкам. Вместо этого используется sparse primary index — разреженный индекс, который хранит одну запись на каждые N строк.

Как устроен sparse primary index

При создании таблицы MergeTree определяется ORDER BY (col1, col2, ...). Данные на диске хранятся отсортированными по этим колонкам. Индекс создаётся автоматически: одна запись в индексе на каждые granule строк.

Granule — минимальная единица чтения в ClickHouse. По умолчанию это 8192 строк (настраивается через index_granularity в CREATE TABLE). Индекс хранит первое значение ключа каждой гранулы.

CREATE TABLE events
(
  user_id   UInt64,
  event_date Date,
  event_type LowCardinality(String),
  value     Float64
)
ENGINE = MergeTree()
ORDER BY (user_id, event_date)
SETTINGS index_granularity = 8192;  -- по умолчанию

Для таблицы с 1 000 000 строк индекс будет содержать примерно 122 записи (1000000 / 8192). Файл индекса называется primary.idx и хранится в директории part.

Как работает поиск через sparse index

При выполнении запроса с фильтром по ключевым колонкам ClickHouse:

• Читает primary.idx (он целиком помещается в память).
• Бинарным поиском находит первую и последнюю гранулу, которые могут содержать нужные строки.
• Читает только эти гранулы с диска (пропуская остальные).
• Применяет фильтр уже в памяти к прочитанным строкам.

-- Этот запрос использует primary index эффективно:
-- user_id является первым ключом
SELECT count(), sum(value)
FROM events
WHERE user_id = 42;

-- EXPLAIN покажет количество гранул
EXPLAIN indexes = 1
SELECT count() FROM events WHERE user_id = 42;
-- Output: Granules: 2/122  <- читает только 2 из 122 гранул

Когда sparse index не помогает

-- Фильтр только по второму ключу — нет prefix match
-- ClickHouse прочитает ВСЕ гранулы
SELECT count() FROM events WHERE event_date = '2024-01-01';
-- Granules: 122/122  <- полный скан!

-- Фильтр по не-ключевой колонке
SELECT count() FROM events WHERE event_type = 'click';
-- Granules: 122/122

Marks и соответствие индекса колонкам

Рядом с primary.idx хранятся файлы *.mrk2 (marks) для каждой колонки. Mark — это пара (offset в .bin файле, offset внутри гранулы). Именно marks позволяют ClickHouse читать конкретные гранулы конкретных колонок без чтения всего файла.

-- Просмотреть информацию о parts и их индексах
SELECT
  name,
  marks,
  rows,
  marks_bytes,
  primary_key_bytes_in_memory
FROM system.parts
WHERE table = 'events' AND active = 1;

Adaptive index granularity

В современных версиях ClickHouse по умолчанию включена adaptive_index_granularity. Гранулы адаптируются под реальный размер строк (целевой размер ~10 MB), чтобы избежать слишком маленьких или слишком больших гранул при переменной длине строк.

Подводные камни

• Sparse index не уникальный: в отличие от реляционных БД, primary key в ClickHouse не гарантирует уникальность строк — дубликаты физически возможны.
• Порядок колонок в ORDER BY критичен: только prefix используется для range scan. Колонка на второй позиции эффективна только если первая тоже в фильтре.
• Слишком маленький index_granularity: уменьшение гранулы увеличивает размер индекса и количество marks — индекс перестаёт помещаться в кэш.
• Кардинальность первого ключа: если первый ключ имеет низкую кардинальность (например, status с 3 значениями), index плохо сужает диапазон. Первым ставить колонку с высокой кардинальностью.
• primary.idx хранится в памяти целиком: при огромных таблицах (миллиарды строк, тысячи parts) суммарный размер всех primary.idx может занять гигабайты RAM.
• Mark cache: marks кэшируются в mark_cache (по умолчанию 5 GB). После рестарта сервера первые запросы медленнее из-за холодного кэша.

• Объяснять sparse primary index как OLTP-механику row-store базы вместо аналитической колоночной модели ClickHouse.
• Путать primary key ClickHouse с уникальным constraint из PostgreSQL или MySQL.
• Игнорировать parts, merges, ORDER BY, sparse index и стоимость маленьких вставок.
• Предлагать синтаксис или транзакционное поведение, которого в ClickHouse нет.

• Кандидат объясняет sparse primary index через реальный механизм ClickHouse, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.