Примеры индексов пропуска данных

На этой странице собраны примеры индексов пропуска данных в ClickHouse: показано, как объявлять каждый тип, когда их использовать и как проверять, что они действительно применяются. Все эти возможности поддерживаются таблицами семейства MergeTree.

Синтаксис индекса:

INDEX name expr TYPE type(...) [GRANULARITY N]

ClickHouse поддерживает шесть типов индекса пропуска данных:

В каждом разделе приведены примеры с тестовыми данными и показано, как проверить использование индекса при выполнении запроса.

Индекс minmax лучше всего подходит для диапазонных предикатов по слабо отсортированным данным или по столбцам, коррелирующим с ORDER BY.

-- Определить в CREATE TABLE
CREATE TABLE events
(
  ts DateTime,
  user_id UInt64,
  value UInt32,
  INDEX ts_minmax ts TYPE minmax GRANULARITY 1
)
ENGINE=MergeTree
ORDER BY ts;

-- Или добавить позже и материализовать
ALTER TABLE events ADD INDEX ts_minmax ts TYPE minmax GRANULARITY 1;
ALTER TABLE events MATERIALIZE INDEX ts_minmax;

-- Запрос, использующий индекс
SELECT count() FROM events WHERE ts >= now() - 3600;

-- Проверить использование
EXPLAIN indexes = 1
SELECT count() FROM events WHERE ts >= now() - 3600;

См. разобранный пример с EXPLAIN и отсечением.

Используйте индекс set, когда локальная (на уровне блока) мощность мала; он неэффективен, если в каждом блоке много различных значений.

ALTER TABLE events ADD INDEX user_set user_id TYPE set(100) GRANULARITY 1;
ALTER TABLE events MATERIALIZE INDEX user_set;

SELECT * FROM events WHERE user_id IN (101, 202);

EXPLAIN indexes = 1
SELECT * FROM events WHERE user_id IN (101, 202);

Процесс создания и материализации, а также результат до и после, показаны в руководстве по основным операциям.

text — это инвертированный индекс для токенизированных текстовых данных. Он специально разработан для задач полнотекстового поиска и обеспечивает эффективный и детерминированный поиск токенов и терминов. Рекомендуется для сценариев, связанных с обработкой естественного языка или крупномасштабным поиском по тексту.

Подробнее и примеры см. в разделе Полнотекстовый поиск с текстовыми индексами.

ALTER TABLE logs ADD INDEX msg_text msg TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE logs MATERIALIZE INDEX msg_text;

SELECT count() FROM logs WHERE hasAllTokens(msg, 'exception');

Более подробный пример обсервабилити см. в документации здесь.

Текстовый индекс полностью детерминирован и гибко настраивается с точки зрения токенизации и обработки текста, но требует несколько больше места в хранилище по сравнению с индексами на основе фильтра Блума,

Индекс bloom_filter хорошо подходит для проверки равенства и принадлежности множеству через IN в сценариях поиска "иголки в стоге сена". Он принимает необязательный параметр — уровень ложноположительных срабатываний (по умолчанию 0.025).

ALTER TABLE events ADD INDEX value_bf value TYPE bloom_filter(0.01) GRANULARITY 3;
ALTER TABLE events MATERIALIZE INDEX value_bf;

SELECT * FROM events WHERE value IN (7, 42, 99);

EXPLAIN indexes = 1
SELECT * FROM events WHERE value IN (7, 42, 99);

Индекс ngrambf_v1 разбивает строки на n-граммы. Он хорошо подходит для запросов LIKE '%...%'. Поддерживаются String/FixedString/Map (через mapKeys/mapValues), а также настраиваемые размер, количество хеш-функций и seed. Подробнее см. в документации по N-граммному bloom-фильтру.

-- Создание индекса для поиска подстрок
ALTER TABLE logs ADD INDEX msg_ngram msg TYPE ngrambf_v1(3, 10000, 3, 7) GRANULARITY 1;
ALTER TABLE logs MATERIALIZE INDEX msg_ngram;

-- Поиск подстрок
SELECT count() FROM logs WHERE msg LIKE '%timeout%';

EXPLAIN indexes = 1
SELECT count() FROM logs WHERE msg LIKE '%timeout%';

В этом руководстве приведены практические примеры и объясняется, когда использовать token, а когда ngram.

Вспомогательные функции для оптимизации параметров:

Четыре параметра ngrambf_v1 (размер n-граммы, размер битмапа, хеш-функции, seed) существенно влияют на производительность и использование памяти. Используйте эти функции, чтобы рассчитать оптимальный размер битмапа и количество хеш-функций на основе ожидаемого объёма n-грамм и требуемой частоты ложноположительных срабатываний:

CREATE FUNCTION bfEstimateFunctions AS
(total_grams, bits) -> round((bits / total_grams) * log(2));

CREATE FUNCTION bfEstimateBmSize AS
(total_grams, p_false) -> ceil((total_grams * log(p_false)) / log(1 / pow(2, log(2))));

-- Пример расчёта для 4300 n-грамм, p_false = 0.0001
SELECT bfEstimateBmSize(4300, 0.0001) / 8 AS size_bytes;  -- ~10304
SELECT bfEstimateFunctions(4300, bfEstimateBmSize(4300, 0.0001)) AS k; -- ~13

См. документацию по параметрам для получения подробных рекомендаций по настройке.

Индексы tokenbf_v1 индексируют токены, разделённые небуквенно-цифровыми символами. Их следует использовать с hasToken, шаблонами слов для LIKE или операторами =/IN. Поддерживаются типы String/FixedString/Map.

Подробнее см. на страницах Token bloom-фильтр и Типы bloom-фильтра.

ALTER TABLE logs ADD INDEX msg_token lower(msg) TYPE tokenbf_v1(10000, 7, 7) GRANULARITY 1;
ALTER TABLE logs MATERIALIZE INDEX msg_token;

-- Поиск по слову (регистронезависимый, с помощью lower)
SELECT count() FROM logs WHERE hasToken(lower(msg), 'exception');

EXPLAIN indexes = 1
SELECT count() FROM logs WHERE hasToken(lower(msg), 'exception');

См. примеры по обсервабилити и рекомендации по выбору между токенами и n-граммами здесь.

Индексы пропуска данных также поддерживают составные выражения и типы Map/Tuple/Nested. Это показано в примере ниже:

CREATE TABLE t
(
  u64 UInt64,
  s String,
  m Map(String, String),

  INDEX idx_bf u64 TYPE bloom_filter(0.01) GRANULARITY 3,
  INDEX idx_minmax u64 TYPE minmax GRANULARITY 1,
  INDEX idx_set u64 * length(s) TYPE set(1000) GRANULARITY 4,
  INDEX idx_ngram s TYPE ngrambf_v1(3, 10000, 3, 7) GRANULARITY 1,
  INDEX idx_token mapKeys(m) TYPE tokenbf_v1(10000, 7, 7) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY u64;

Вы можете добавить индекс к существующим частям данных с помощью MATERIALIZE, а отсечение проверить с помощью EXPLAIN или трассировочных логов, как показано ниже:

ALTER TABLE t MATERIALIZE INDEX idx_bf;

EXPLAIN indexes = 1
SELECT count() FROM t WHERE u64 IN (123, 456);

-- Необязательно: подробная информация об отсечении данных
SET send_logs_level = 'trace';

Этот разобранный пример minmax демонстрирует структуру вывода EXPLAIN и количество отсечений.

Используйте индексы пропуска данных, когда:

• Значения, по которым выполняется фильтрация, разреженно распределены внутри блоков данных
• Есть сильная корреляция со столбцами ORDER BY, либо шаблоны ингестии данных группируют схожие значения
• Выполняется текстовый поиск по большим наборам логов (типы ngrambf_v1/tokenbf_v1)

Избегайте индексов пропуска данных, когда:

• Большинство блоков, скорее всего, содержит хотя бы одно подходящее значение (поэтому блоки всё равно будут прочитаны)
• Фильтрация выполняется по столбцам с высокой мощностью без корреляции с порядком данных

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

-- Ignore an index by name
SELECT * FROM logs
WHERE hasToken(lower(msg), 'exception')
SETTINGS ignore_data_skipping_indices = 'msg_token';

• Индекс пропуска данных поддерживается только для таблиц семейства MergeTree; отсечение данных происходит на уровне гранулы/блока.
• Индексы на основе bloom-фильтров являются вероятностными (ложноположительные срабатывания приводят к дополнительным чтениям, но не к пропуску корректных данных).
• Bloom-фильтры и другие индексы пропуска данных следует проверять с помощью EXPLAIN и трассировки; настраивайте гранулярность, чтобы сбалансировать степень отсечения и размер индекса.

• Руководство по индексам пропуска данных
• Руководство по рекомендациям
• Управление индексами пропуска данных
• Информация о системной таблице