Полнотекстовый поиск с использованием текстовых индексов

Beta feature. Learn more.

Текстовые индексы в ClickHouse (также известные как "обратные индексы") обеспечивают быстрый полнотекстовый поиск по строковым данным. Индекс сопоставляет каждый токен в столбце со строками, которые содержат этот токен. Токены генерируются процессом, называемым токенизацией. Например, по умолчанию ClickHouse разбивает английское предложение "All cat like mice." на токены ["All", "cat", "like", "mice"] (обратите внимание, что завершающая точка игнорируется). Также доступны более продвинутые токенизаторы, например для данных журналов (логов).

Создание текстового индекса

Чтобы создать текстовый индекс, сначала включите соответствующую экспериментальную настройку:

SET enable_full_text_index = true;

Текстовый индекс можно определить для столбца следующих типов: String, FixedString, Array(String), Array(FixedString) и Map (через функции работы с Map mapKeys и mapValues) с помощью следующего синтаксиса:

CREATE TABLE tab
(
    `key` UInt64,
    `str` String,
    INDEX text_idx(str) TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- Optional parameters:
                                [, preprocessor = expression(str)]
                                -- Optional advanced parameters:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, max_cardinality_for_embedded_postings = M]
                                [, bloom_filter_false_positive_rate = R]
                            ) [GRANULARITY 64]
)
ENGINE = MergeTree
ORDER BY key

Аргумент tokenizer (обязательный). Аргумент tokenizer задаёт токенизатор:

• splitByNonAlpha разбивает строки по неалфавитно-цифровым ASCII-символам (см. также функцию splitByNonAlpha).
• splitByString(S) разбивает строки по определённым пользовательским строкам-разделителям S (см. также функцию splitByString). Разделители можно задать с помощью необязательного параметра, например, tokenizer = splitByString([', ', '; ', '\n', '\\']). Обратите внимание, что каждая строка может состоять из нескольких символов (в примере это ', '). Список разделителей по умолчанию, если он не задан явно (например, tokenizer = splitByString), — это один пробел [' '].
• ngrams(N) разбивает строки на равные по размеру n-граммы длиной N (см. также функцию ngrams). Длину n-граммы можно задать с помощью необязательного целочисленного параметра от 1 до 8, например, tokenizer = ngrams(3). Размер n-граммы по умолчанию, если он не задан явно (например, tokenizer = ngrams), — 3.
• sparseGrams(min_length, max_length, min_cutoff_length) разбивает строки на n-граммы переменной длины не короче min_length и не длиннее max_length (включительно) символов (см. также функцию sparseGrams). Если явно не указаны иные значения, min_length и max_length по умолчанию равны 3 и 100. Если параметр min_cutoff_length задан, в индекс сохраняются только n-граммы длиной не меньше min_cutoff_length. По сравнению с ngrams(N) токенизатор sparseGrams создаёт n-граммы переменной длины, что позволяет более гибко представлять исходный текст. Например, при tokenizer = sparseGrams(3, 5, 4) внутренне создаются 3-, 4- и 5-граммы из входной строки, но в индекс сохраняются только 4- и 5-граммы.
• array не выполняет токенизацию, т. е. каждое значение строки является токеном (см. также функцию array).

Примечание

Токенизатор splitByString применяет разделители слева направо. Это может создавать неоднозначности. Например, строки-разделители ['%21', '%'] приведут к тому, что %21abc будет токенизировано как ['abc'], тогда как при перестановке разделителей ['%', '%21'] результатом будет ['21abc']. В большинстве случаев требуется, чтобы при сопоставлении преимущество отдавалось более длинным разделителям. Обычно этого можно добиться, передавая строки-разделители в порядке убывания их длины. Если строки-разделители образуют префиксный код, их можно передавать в произвольном порядке.

Примечание

В настоящее время не рекомендуется создавать текстовые индексы для текста на незападных языках, например китайском. Поддерживаемые в данный момент токенизаторы могут привести к значительному увеличению размера индекса и длительному времени выполнения запросов. В будущем планируется добавить специализированные языково-зависимые токенизаторы, которые будут лучше обрабатывать такие случаи.

Чтобы проверить, как токенизаторы разбивают входную строку, можно использовать функцию tokens ClickHouse:

Пример:

SELECT tokens('abc def', 'ngrams', 3);

Результат:

['abc','bc ','c d',' de','def']

Аргумент preprocessor (необязательный). Аргумент preprocessor — это выражение, которое применяется к входной строке перед токенизацией.

Типичные сценарии использования аргумента препроцессора включают

1. Приведение к нижнему или верхнему регистру для обеспечения сопоставления без учета регистра, например lower, lowerUTF8, см. первый пример ниже.
2. Нормализация в UTF-8, например normalizeUTF8NFC, normalizeUTF8NFD, normalizeUTF8NFKC, normalizeUTF8NFKD, toValidUTF8.
3. Удаление или преобразование нежелательных символов или подстрок, например extractTextFromHTML, substring, idnaEncode.

Выражение препроцессора должно преобразовывать входное значение типа String или FixedString в значение того же типа.

Примеры:

• INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(col))
• INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = substringIndex(col, '\n', 1))
• INDEX idx(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(extractTextFromHTML(col))

Кроме того, выражение препроцессора должно ссылаться только на столбец, на основе которого определён текстовый индекс. Использование недетерминированных функций не допускается.

Функции hasToken, hasAllTokens и hasAnyTokens используют препроцессор для преобразования поискового термина перед его токенизацией.

Например,

CREATE TABLE tab
(
    key UInt64,
    str String,
    INDEX idx(str) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(str))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasToken(str, 'Foo');

эквивалентно:

CREATE TABLE tab
(
    key UInt64,
    str String,
    INDEX idx(lower(str)) TYPE text(tokenizer = 'splitByNonAlpha')
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasToken(str, lower('Foo'));

Прочие аргументы (необязательные). Текстовые индексы в ClickHouse реализованы как вторичные индексы. Однако, в отличие от других индексов с пропуском данных, текстовые индексы имеют значение GRANULARITY по умолчанию равное 64. Это значение было выбрано эмпирически и обеспечивает хороший баланс между скоростью и размером индекса для большинства случаев использования. Опытные пользователи могут указать другую гранулярность индекса (мы не рекомендуем этого делать).

Необязательные расширенные параметры

Значения по умолчанию для следующих расширенных параметров подойдут практически во всех случаях. Мы не рекомендуем их изменять.

Необязательный параметр dictionary_block_size (по умолчанию: 128) задаёт размер блоков словаря в строках.

Необязательный параметр dictionary_block_frontcoding_compression (по умолчанию: 1) определяет, используют ли блоки словаря front coding в качестве метода сжатия.

Необязательный параметр max_cardinality_for_embedded_postings (по умолчанию: 16) задаёт порог кардинальности, ниже которого списки постингов должны встраиваться в блоки словаря.

Необязательный параметр bloom_filter_false_positive_rate (по умолчанию: 0.1) задаёт вероятность ложных срабатываний фильтра Блума словаря.

Текстовые индексы можно добавлять в столбец или удалять из него уже после создания таблицы:

ALTER TABLE tab DROP INDEX text_idx;
ALTER TABLE tab ADD INDEX text_idx(s) TYPE text(tokenizer = splitByNonAlpha);

Использование текстового индекса

Использовать текстовый индекс в запросах SELECT достаточно просто, так как стандартные строковые функции поиска автоматически задействуют индекс. Если индекс отсутствует, приведённые ниже строковые функции поиска будут выполнять медленное полное (brute-force) сканирование.

Поддерживаемые функции

Текстовый индекс можно использовать, если в условии WHERE оператора SELECT используются текстовые функции:

SELECT [...]
FROM [...]
WHERE string_search_function(column_with_text_index)

= и !=

= (equals) и != (notEquals) проверяют полное совпадение с указанным поисковым термином.

Пример:

SELECT * from tab WHERE str = 'Hello';

Текстовый индекс поддерживает = и !=, однако поиск по условиям равенства и неравенства имеет смысл только с токенизатором array (он приводит к тому, что индекс хранит значения целых строк).

IN и NOT IN

IN (in) и NOT IN (notIn) аналогичны функциям equals и notEquals, но проверяют соответствие всем (IN) или ни одному (NOT IN) из искомых значений.

Пример:

SELECT * from tab WHERE str IN ('Hello', 'World');

Применимы те же ограничения, что и для = и !=, то есть IN и NOT IN имеют смысл только при использовании токенизатора array.

LIKE, NOT LIKE и match

Примечание

В настоящее время эти функции используют текстовый индекс для фильтрации только в том случае, если токенизатор индекса — splitByNonAlpha или ngrams.

Чтобы использовать LIKE (like), NOT LIKE (notLike) и функцию match с текстовыми индексами, ClickHouse должен иметь возможность извлечь полные токены из поискового запроса.

Пример:

SELECT count() FROM tab WHERE comment LIKE 'support%';

support в этом примере может соответствовать support, supports, supporting и т. д. Такой тип запроса является запросом на поиск подстроки, и его нельзя ускорить с помощью текстового индекса.

Чтобы использовать текстовый индекс для запросов с LIKE, шаблон LIKE необходимо переписать следующим образом:

SELECT count() FROM tab WHERE comment LIKE ' support %'; -- or `% support %`

Пробелы слева и справа от support обеспечивают извлечение этого термина как отдельного токена.

startsWith и endsWith

По аналогии с LIKE, функции startsWith и endsWith могут использовать текстовый индекс только в том случае, если из поискового термина можно извлечь целые токены.

Пример:

SELECT count() FROM tab WHERE startsWith(comment, 'clickhouse support');

В этом примере только clickhouse считается отдельным токеном. support не считается токеном, потому что ему могут соответствовать support, supports, supporting и т.д.

Чтобы найти все строки, которые начинаются с clickhouse supports, завершите шаблон поиска пробелом на конце:

startsWith(comment, 'clickhouse supports ')`

Аналогично, функцию endsWith следует использовать с пробелом в начале:

SELECT count() FROM tab WHERE endsWith(comment, ' olap engine');

hasToken и hasTokenOrNull

Функции hasToken и hasTokenOrNull выполняют поиск по одному заданному токену.

В отличие от ранее упомянутых функций, они не выполняют токенизацию искомого значения (предполагается, что на вход передаётся один токен).

Пример:

SELECT count() FROM tab WHERE hasToken(comment, 'clickhouse');

Функции hasToken и hasTokenOrNull являются наиболее производительными при использовании с индексом text.

hasAnyTokens и hasAllTokens

Функции hasAnyTokens и hasAllTokens используются для сопоставления с одним или со всеми из указанных токенов.

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

Пример:

-- Search tokens passed as string argument
SELECT count() FROM tab WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM tab WHERE hasAllTokens(comment, 'clickhouse olap');

-- Search tokens passed as Array(String)
SELECT count() FROM tab WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM tab WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

has

Функция работы с массивами has выполняет сопоставление с отдельным токеном в массиве строк.

Пример:

SELECT count() FROM tab WHERE has(array, 'clickhouse');

mapContains

Функция mapContains (псевдоним mapContainsKey) сопоставляет отдельный токен с ключами map.

Пример:

SELECT count() FROM tab WHERE mapContainsKey(map, 'clickhouse');
-- OR
SELECT count() FROM tab WHERE mapContains(map, 'clickhouse');

operator[]

Оператор доступа operator[] можно использовать с текстовым индексом для фильтрации по ключам и значениям.

Пример:

SELECT count() FROM tab WHERE map['engine'] = 'clickhouse';

См. следующие примеры использования столбцов типа Array(T) и Map(K, V) с текстовым индексом.

Примеры использования столбцов Array и Map с текстовыми индексами

Индексация столбцов Array(String)

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

Рассмотрим следующее определение таблицы:

CREATE TABLE posts (
    post_id UInt64,
    title String,
    content String,
    keywords Array(String)
)
ENGINE = MergeTree
ORDER BY (post_id);

Без текстового индекса, чтобы найти посты с определённым ключевым словом (например, clickhouse), приходится просматривать все записи:

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- slow full-table scan - checks every keyword in every post

По мере роста платформы выполнение запроса становится всё более медленным, потому что ему приходится просматривать каждый массив keywords в каждой строке. Чтобы решить эту проблему с производительностью, мы определяем текстовый индекс для столбца keywords:

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- Don't forget to rebuild the index for existing data

Индексирование столбцов типа Map

Во многих сценариях обсервабилити сообщения логов разбиваются на «компоненты» и сохраняются с соответствующими типами данных, например дата-время для временной метки, enum для уровня логирования и т. д. Поля метрик оптимально хранить в виде пар ключ-значение. Командам, отвечающим за эксплуатацию, необходимо эффективно искать по логам для отладки, расследования инцидентов информационной безопасности и мониторинга.

Рассмотрим следующую таблицу логов:

CREATE TABLE logs (
    id UInt64,
    timestamp DateTime,
    message String,
    attributes Map(String, String)
)
ENGINE = MergeTree
ORDER BY (timestamp);

Без текстового индекса поиск по данным типа Map требует полного сканирования таблицы:

-- Finds all logs with rate limiting data:
SELECT count() FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- slow full-table scan

-- Finds all logs from a specific IP:
SELECT count() FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- slow full-table scan

По мере увеличения объёма логов такие запросы начинают работать медленно.

Решение — создать текстовый индекс для ключей и значений Map. Используйте mapKeys, чтобы создать текстовый индекс, когда нужно находить логи по именам полей или типам атрибутов:

ALTER TABLE logs ADD INDEX attributes_keys_idx mapKeys(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx;

Используйте mapValues, чтобы создать текстовый индекс, когда вам нужно выполнять поиск по собственному содержимому атрибутов:

ALTER TABLE logs ADD INDEX attributes_vals_idx mapValues(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx;

Примеры запросов:

-- Find all rate-limited requests:
SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast

-- Finds all logs from a specific IP:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast

Настройка производительности

Прямое чтение

Некоторые типы текстовых запросов могут быть значительно ускорены с помощью оптимизации, называемой «прямое чтение». Более конкретно, эта оптимизация может быть применена, если в запросе SELECT текстовый столбец не включён в список выбираемых столбцов.

Пример:

SELECT column_a, column_b, ... -- not: column_with_text_index
FROM [...]
WHERE string_search_function(column_with_text_index)

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

Прямое чтение управляется двумя настройками:

• Настройка query_plan_direct_read_from_text_index, которая определяет, включено ли прямое чтение в целом.
• Настройка use_skip_indexes_on_data_read, которая является дополнительным требованием для прямого чтения. Обратите внимание, что в базах данных ClickHouse с compatibility < 25.10 use_skip_indexes_on_data_read отключена, поэтому вам либо нужно повысить значение настройки compatibility, либо явно выполнить SET use_skip_indexes_on_data_read = 1.

Также текстовый индекс должен быть полностью материализован, чтобы использовать прямое чтение (для этого используйте ALTER TABLE ... MATERIALIZE INDEX).

Поддерживаемые функции Оптимизация прямого чтения поддерживает функции hasToken, hasAllTokens и hasAnyTokens. Эти функции также могут комбинироваться операторами AND, OR и NOT. Условие WHERE также может содержать дополнительные фильтры, не связанные с функциями полнотекстового поиска (для текстовых столбцов или других столбцов) — в этом случае оптимизация прямого чтения всё равно будет использоваться, но менее эффективно (она применяется только к поддерживаемым функциям текстового поиска).

Чтобы проверить, использует ли запрос прямое чтение, выполните его с EXPLAIN PLAN actions = 1. В качестве примера, запрос с отключённым прямым чтением

EXPLAIN PLAN actions = 1
SELECT count()
FROM tab
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 0, -- disable direct read
         use_skip_indexes_on_data_read = 1;

возвращает

[...]
Filter ((WHERE + Change column names to column identifiers))
Filter column: hasToken(__table1.col, 'some_token'_String) (removed)
Actions: INPUT : 0 -> col String : 0
         COLUMN Const(String) -> 'some_token'_String String : 1
         FUNCTION hasToken(col :: 0, 'some_token'_String :: 1) -> hasToken(__table1.col, 'some_token'_String) UInt8 : 2
[...]

тогда как тот же запрос, выполняемый с параметром query_plan_direct_read_from_text_index = 1

EXPLAIN PLAN actions = 1
SELECT count()
FROM tab
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 1, -- enable direct read
         use_skip_indexes_on_data_read = 1;

возвращает

[...]
Expression (Before GROUP BY)
Positions:
  Filter
  Filter column: __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 (removed)
  Actions: INPUT :: 0 -> __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 UInt8 : 0
[...]

Второй результат EXPLAIN PLAN содержит виртуальный столбец __text_index_<index_name>_<function_name>_<id>. Если этот столбец присутствует, используется прямое чтение.

Кэширование

Для буферизации частей текстового индекса в памяти доступны различные кэши (см. раздел Implementation Details). В настоящее время существуют кэши для десериализованных блоков словаря, заголовков и списков вхождений (posting lists) текстового индекса, позволяющие сократить количество операций ввода-вывода (I/O). Эти кэши включаются с помощью настроек use_text_index_dictionary_cache, use_text_index_header_cache и use_text_index_postings_cache. По умолчанию все кэши отключены.

Для их настройки воспользуйтесь следующими параметрами сервера.

Настройки кэша блоков словаря

Параметр	Описание
text_index_dictionary_block_cache_policy	Имя политики кэша блоков словаря текстового индекса.
text_index_dictionary_block_cache_size	Максимальный размер кэша в байтах.
text_index_dictionary_block_cache_max_entries	Максимальное число десериализованных блоков словаря в кэше.
text_index_dictionary_block_cache_size_ratio	Размер защищённой очереди в кэше блоков словаря текстового индекса относительно общего размера кэша.

Настройки кэша заголовков

Параметр	Описание
text_index_header_cache_policy	Имя политики кэширования заголовков текстового индекса.
text_index_header_cache_size	Максимальный размер кэша в байтах.
text_index_header_cache_max_entries	Максимальное количество десериализованных заголовков в кэше.
text_index_header_cache_size_ratio	Размер защищённой очереди в кэше заголовков текстового индекса по отношению к общему размеру кэша.

Настройки кэша списков вхождений

Настройка	Описание
text_index_postings_cache_policy	Имя политики кэша списков вхождений текстового индекса.
text_index_postings_cache_size	Максимальный размер кэша в байтах.
text_index_postings_cache_max_entries	Максимальное количество десериализованных списков вхождений в кэше.
text_index_postings_cache_size_ratio	Размер защищённой очереди в кэше списков вхождений текстового индекса относительно общего размера кэша.

Подробности реализации

Каждый текстовый индекс состоит из двух (абстрактных) структур данных:

• словаря, который отображает каждый токен на список вхождений, и
• набора списков вхождений, каждый из которых представляет собой набор номеров строк.

Поскольку текстовый индекс является skip-индексом, эти структуры данных логически существуют для каждого гранула индекса.

Во время создания индекса создаются три файла (на каждую часть):

Файл блоков словаря (.dct)

Токены в грануле индекса сортируются и сохраняются в блоках словаря по 128 токенов в каждом (размер блока настраивается параметром dictionary_block_size). Файл блоков словаря (.dct) содержит все блоки словаря для всех гранул индекса в части.

Файл гранул индекса (.idx)

Файл гранул индекса содержит для каждого блока словаря первый токен блока, его относительное смещение в файле блоков словаря и bloom-фильтр для всех токенов в блоке. Эта разреженная структура индекса аналогична разреженному индексу первичного ключа ClickHouse (sparse primary key index). Bloom-фильтр позволяет на раннем этапе отбрасывать блоки словаря, если искомый токен не содержится в блоке словаря.

Файл списков вхождений (.pst)

Списки вхождений для всех токенов размещаются последовательно в файле списков вхождений. Чтобы экономить место и при этом обеспечивать быстрые операции пересечения и объединения, списки вхождений хранятся как roaring bitmaps. Если кардинальность списка вхождений меньше 16 (настраивается параметром max_cardinality_for_embedded_postings), он встраивается в словарь.

Пример: датасет Hacker News

Рассмотрим, как текстовые индексы повышают производительность на большом наборе данных с большим объёмом текстов. Мы будем использовать 28,7 млн строк комментариев с популярного сайта Hacker News. Вот таблица без текстового индекса:

CREATE TABLE hackernews (
    id UInt64,
    deleted UInt8,
    type String,
    author String,
    timestamp DateTime,
    comment String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    children Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32
)
ENGINE = MergeTree
ORDER BY (type, author);

28,7 млн строк хранятся в файле Parquet в S3 — давайте вставим их в таблицу hackernews:

INSERT INTO hackernews
    SELECT * FROM s3Cluster(
        'default',
        'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet',
        'Parquet',
        '
    id UInt64,
    deleted UInt8,
    type String,
    by String,
    time DateTime,
    text String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    kids Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32');

Мы используем ALTER TABLE, добавим текстовый индекс по столбцу comment, а затем материализуем его:

-- Add the index
ALTER TABLE hackernews ADD INDEX comment_idx(comment) TYPE text(tokenizer = splitByNonAlpha);

-- Materialize the index for existing data
ALTER TABLE hackernews MATERIALIZE INDEX comment_idx SETTINGS mutations_sync = 2;

Теперь выполним запросы с использованием функций hasToken, hasAnyTokens и hasAllTokens. Следующие примеры покажут резкую разницу в производительности между стандартным сканированием индекса и оптимизацией прямого чтения.

1. Использование hasToken

hasToken проверяет, содержит ли текст конкретный отдельный токен. Мы будем искать чувствительный к регистру токен «ClickHouse».

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

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0, use_skip_indexes_on_data_read = 0;

┌─count()─┐
│     516 │
└─────────┘

1 row in set. Elapsed: 0.362 sec. Processed 24.90 million rows, 9.51 GB

Прямое чтение включено (быстрое чтение индекса) Теперь запустим тот же запрос с включённым прямым чтением (это поведение по умолчанию).

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 1;

┌─count()─┐
│     516 │
└─────────┘

1 row in set. Elapsed: 0.008 sec. Processed 3.15 million rows, 3.15 MB

Запрос с прямым чтением более чем в 45 раз быстрее (0,362 с против 0,008 с) и обрабатывает значительно меньше данных (9,51 ГБ против 3,15 МБ), считывая данные только из индекса.

2. Использование hasAnyTokens

hasAnyTokens проверяет, содержит ли текст хотя бы один из переданных токенов. Будем искать комментарии, содержащие «love» или «ClickHouse».

Прямое чтение отключено (стандартное сканирование)

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0, use_skip_indexes_on_data_read = 0;

┌─count()─┐
│  408426 │
└─────────┘

1 row in set. Elapsed: 1.329 sec. Processed 28.74 million rows, 9.72 GB

Включено прямое чтение (быстрое чтение по индексу)

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 1;

┌─count()─┐
│  408426 │
└─────────┘

1 row in set. Elapsed: 0.015 sec. Processed 27.99 million rows, 27.99 MB

Ускорение ещё более заметно для этого распространённого поиска по условию «OR». Запрос выполняется почти в 89 раз быстрее (1.329s против 0.015s), так как удаётся избежать полного сканирования столбца.

3. Использование hasAllTokens

hasAllTokens проверяет, содержит ли текст все заданные токены. Будем искать комментарии, содержащие и 'love', и 'ClickHouse'.

Прямое чтение отключено (стандартное сканирование) Даже при отключённом прямом чтении стандартный пропускающий индекс остаётся эффективным. Он сокращает выборку с 28.7M строк до всего 147.46K строк, но при этом всё равно должен прочитать 57.03 MB из столбца.

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0, use_skip_indexes_on_data_read = 0;

┌─count()─┐
│      11 │
└─────────┘

1 row in set. Elapsed: 0.184 sec. Processed 147.46 thousand rows, 57.03 MB

Прямое чтение включено (быстрое чтение индекса) Прямое чтение отвечает на запрос, используя данные индекса и считывая только 147,46 КБ.

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 1;

┌─count()─┐
│      11 │
└─────────┘

1 row in set. Elapsed: 0.007 sec. Processed 147.46 thousand rows, 147.46 KB

Для такого поиска по условию AND оптимизация прямого чтения более чем в 26 раз быстрее (0,184 с против 0,007 с), чем стандартное сканирование индекса-пропуска.

4. Составной поиск: OR, AND, NOT, ...

Оптимизация прямого чтения также применяется к составным логическим выражениям. Здесь мы выполним поиск без учета регистра для 'ClickHouse' ИЛИ 'clickhouse'.

Прямое чтение отключено (стандартное сканирование)

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 0, use_skip_indexes_on_data_read = 0;

┌─count()─┐
│     769 │
└─────────┘

1 row in set. Elapsed: 0.450 sec. Processed 25.87 million rows, 9.58 GB

Включено прямое чтение (быстрое чтение по индексу)

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 1;

┌─count()─┐
│     769 │
└─────────┘

1 row in set. Elapsed: 0.013 sec. Processed 25.87 million rows, 51.73 MB

Комбинируя результаты работы индекса, прямой запрос на чтение выполняется в 34 раза быстрее (0,450 с против 0,013 с) и позволяет избежать чтения 9,58 ГБ данных столбца. Для этого конкретного случая hasAnyTokens(comment, ['ClickHouse', 'clickhouse']) будет предпочтительным, более эффективным синтаксисом.

Связанные материалы

• Блог: Introducing Inverted Indices in ClickHouse
• Блог: Inside ClickHouse full-text search: fast, native, and columnar
• Видео: Full-Text Indices: Design and Experiments