Что такое data skipping indexes в ClickHouse и когда их следует использовать?

Data Skipping Indexes в ClickHouse

Data skipping indexes (DSI) — дополнительные индексы поверх primary index. Они не ускоряют точечный поиск, а помогают пропускать (skip) блоки данных, которые заведомо не содержат нужных строк. Используются для колонок, не входящих в primary key.

Виды data skipping indexes

1. minmax

Хранит минимальное и максимальное значение колонки для каждого блока из N гранул. Эффективен для числовых колонок и дат с временной корреляцией.

CREATE TABLE events
(
  user_id    UInt64,
  event_date Date,
  amount     Float64,
  INDEX amount_idx amount TYPE minmax GRANULARITY 4
  -- GRANULARITY 4: один блок индекса = 4 гранулы = 4*8192 строк
)
ENGINE = MergeTree()
ORDER BY (user_id, event_date);

-- Эффективно: пропускает блоки где max(amount) < 1000
SELECT count() FROM events WHERE amount > 1000;

2. set

Хранит множество уникальных значений для каждого блока. Хорош для колонок с малым числом уникальных значений в блоке (низкая кардинальность).

ALTER TABLE events
ADD INDEX status_idx status TYPE set(100) GRANULARITY 2;
-- set(100): хранит до 100 уникальных значений на блок
-- Если значений > 100, блок не пропускается (conservative)

ALTER TABLE events MATERIALIZE INDEX status_idx;
-- Принудительно построить индекс для существующих данных

-- Эффективно: пропускает блоки, не содержащие 'failed'
SELECT count() FROM events WHERE status = 'failed';

3. bloom_filter

Probabilistic структура данных — может дать false positive (скажет «блок содержит значение» когда не содержит), но никогда false negative. Хорош для String колонок и равенства.

ALTER TABLE events
ADD INDEX email_idx email TYPE bloom_filter(0.01) GRANULARITY 1;
-- 0.01: допустимый процент false positives (1%)

-- Эффективно для точечного поиска по email
SELECT * FROM events WHERE email = 'user@example.com';

-- Также работает с LIKE (prefix):
SELECT * FROM events WHERE email LIKE 'user@%';
-- НЕ работает с: LIKE '%suffix'

4. ngrambf_v1 и tokenbf_v1

Bloom filter по n-граммам или токенам строки. Используются для full-text поиска.

-- ngrambf: разбивает строку на n-граммы
ALTER TABLE articles
ADD INDEX content_ngram content
TYPE ngrambf_v1(3, 65536, 2, 0) GRANULARITY 1;
-- Параметры: (n=3, filter_size=65536, hash_functions=2, seed=0)

-- tokenbf: токенизирует по пробелам/знакам
ALTER TABLE logs
ADD INDEX message_token message TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1;

-- Эффективно для LIKE с %substring%:
SELECT * FROM articles WHERE content LIKE '%ClickHouse%';

Когда использовать DSI

DSI имеет смысл когда:

• Колонка не входит в primary key, но часто используется в WHERE.
• Данные в колонке имеют хорошую локальность — строки с похожими значениями физически рядом.
• Фильтр имеет высокую селективность (отсеивает много блоков).

-- Проверить эффективность индекса через system.query_log
SELECT
  query,
  ProfileEvents['SelectedMarks'] AS marks_read,
  ProfileEvents['SkippedUnreadGranules'] AS granules_skipped
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query LIKE '%events%'
ORDER BY event_time DESC
LIMIT 5;

Управление индексами

-- Просмотр существующих индексов
SELECT name, type, expr, granularity
FROM system.data_skipping_indices
WHERE table = 'events';

-- Удаление индекса
ALTER TABLE events DROP INDEX amount_idx;

-- Материализация (построение для существующих данных)
ALTER TABLE events MATERIALIZE INDEX status_idx IN PARTITION '202401';

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

• DSI не помогает при случайном распределении данных: если значения колонки случайно перемешаны по блокам, каждый блок будет содержать искомое значение — skip rate = 0, только overhead.
• GRANULARITY критична для bloom_filter: слишком маленькая гранулярность → много мелких фильтров → большой overhead памяти и I/O. Слишком большая → меньше пропускается блоков.
• set(0) хранит все значения: параметр 0 означает «неограниченно» — может съесть огромное количество памяти для высококардинальных колонок.
• bloom_filter даёт false positives: маленький false_positive_p (0.001) требует больше памяти; баланс между точностью и размером индекса.
• Индекс не применяется без MATERIALIZE: добавление индекса через ALTER TABLE не применяется к существующим данным автоматически. Нужен явный MATERIALIZE INDEX.
• ngrambf медленно строится: материализация ngrambf на большой таблице может занять часы — планировать в maintenance window.
• DSI не заменяет правильный primary key: оптимизировать ORDER BY важнее, чем добавлять DSI. DSI — дополнение, а не замена основного дизайна схемы.
• Несовместимость с некоторыми функциями: bloom_filter не работает с LIKE '%suffix' (trailing wildcard) и со многими функциями над колонкой — только прямое сравнение и некоторые LIKE.

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

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