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

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

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

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

SET allow_experimental_full_text_index = true;

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

CREATE TABLE tab
(
    `key` UInt64,
    `str` String,
    INDEX text_idx(str) TYPE text(
                                -- Обязательные параметры:
                                tokenizer = splitByNonAlpha|splitByString(S)|ngrams(N)|array
                                -- Необязательные параметры:
                                [, preprocessor = expression(str)]
                                -- Необязательные дополнительные параметры:
                                [, 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-граммы одинаковой длины (см. также функцию ngrams). Длину n-граммы можно указать с помощью необязательного целочисленного параметра от 2 до 8, например tokenizer = ngrams(3). Длина n-граммы по умолчанию, если она не указана явно (например, tokenizer = ngrams), равна 3.
array не выполняет токенизацию, то есть каждое значение строки является токеном (см. также функцию array).
sparseGrams(min_length, max_length, min_cutoff_length) — использует тот же алгоритм, что и функция sparseGrams, чтобы разбить строку на все n-граммы длины min_length и несколько n-грамм большей длины вплоть до max_length включительно. Если указан min_cutoff_length, в индекс сохраняются только N-граммы длиной не меньше min_cutoff_length. В отличие от ngrams(N), который генерирует только N-граммы фиксированной длины, sparseGrams создаёт набор N-грамм переменной длины в указанном диапазоне, что позволяет более гибко представлять текстовый контекст. Например, tokenizer = sparseGrams(3, 5, 4) сгенерирует из входной строки 3-, 4- и 5-граммы и сохранит в индексе только 4- и 5-граммы.

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

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

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

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

возвращает

+-tokens--------------------------+
| ['abc','bc ','c d',' de','def'] |
+---------------------------------+

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

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

Выражение preprocessor должно преобразовывать входное значение типа 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))

Кроме того, выражение preprocessor должно ссылаться только на столбец, для которого определён текстовый индекс. Использование недетерминированных функций не допускается. Функции hasToken, hasAllTokens и hasAnyTokens используют preprocessor, чтобы сначала преобразовать поисковый запрос перед его токенизацией. Например:

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) указывает, используют ли блоки словаря фронт-кодирование для сжатия.Необязательный параметр 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 несложно: распространённые функции поиска по строкам автоматически задействуют индекс. Если индекс отсутствует, указанные ниже функции поиска по строкам будут выполнять медленный полный перебор.

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

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

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

`=` and `!=`

= (equals) and != (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 %'; -- или `% 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` and `hasTokenOrNull`

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

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

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

`hasAnyTokens` and `hasAllTokens`

Функции hasAnyTokens и hasAllTokens проверяют совпадение с любым из указанных токенов или со всеми указанными токенами. Эти две функции принимают поисковые токены либо в виде строки, которая будет разбита на токены с помощью того же токенизатора, что используется для индексного столбца, либо в виде массива уже обработанных токенов, к которому перед поиском токенизация применяться не будет. Дополнительные сведения см. в документации по функциям. Пример:

-- Токены поиска переданы в виде строкового аргумента
SELECT count() FROM tab WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM tab WHERE hasAllTokens(comment, 'clickhouse olap');

-- Токены поиска переданы в виде 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'; -- will use the text index if defined

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

Примеры поддержки `Array` и `Map` в текстовом индексе.

Индексация Array(String)

На простой блог-платформе авторы присваивают своим постам ключевые слова, чтобы распределять контент по категориям. Обычно пользователи могут находить связанные материалы, нажимая на ключевые слова или выполняя поиск по темам. Рассмотрим следующее определение таблицы:

CREATE TABLE posts (
    post_id UInt64,
    title String,
    content String,
    keywords Array(String) COMMENT 'Author-defined keywords'
)
ENGINE = MergeTree
ORDER BY (post_id);

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

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- медленное полное сканирование таблицы — проверяет каждое ключевое слово в каждом посте

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

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);

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

ALTER TABLE posts MATERIALIZE INDEX keywords_idx;

Индексация Map

В системе сбора журналов запросы к серверу часто сохраняют метаданные в виде пар ключ-значение. Командам эксплуатации нужно эффективно искать по журналам при отладке, расследовании инцидентов безопасности и мониторинге. Рассмотрим эту таблицу журналов:

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

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

Находит все записи журнала, связанные с ограничением частоты:

SELECT count() FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- медленное полное сканирование таблицы

Находит все записи журнала с определённого IP:

SELECT count() FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- медленное полное сканирование таблицы

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

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

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

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

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

ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx;
ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx;

Найдите все запросы, попавшие под ограничение частоты:

SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- быстро

Находит все записи журнала с определённого IP-адреса:

SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- быстро

Реализация

Структура индекса

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

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

Поскольку текстовый индекс является индексом пропуска данных, эти структуры данных логически существуют для каждой гранулы индекса. При создании индекса создаются три файла (для каждой части): Файл блоков словаря (.dct) Токены в грануле индекса сортируются и сохраняются в блоках словаря по 128 токенов в каждом (размер блока настраивается параметром dictionary_block_size). Файл блоков словаря (.dct) содержит все блоки словаря для всех гранул индекса в части. Файл гранул индекса (.idx) Файл гранул индекса содержит для каждого блока словаря первый токен блока, его относительное смещение в файле блоков словаря и фильтр Блума для всех токенов в блоке. Эта структура разреженного индекса похожа на разреженный индекс первичного ключа). Фильтр Блума позволяет заранее пропускать блоки словаря, если искомый токен отсутствует в блоке. Файл списков вхождений (.pst) Списки вхождений для всех токенов располагаются последовательно в файле списков вхождений. Чтобы экономить место и при этом обеспечивать быстрые операции пересечения и объединения, списки вхождений хранятся в виде битмапов Roaring. Если мощность списка вхождений меньше 16 (настраивается параметром max_cardinality_for_embedded_postings), он встраивается в словарь.

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

Некоторые типы текстовых запросов можно значительно ускорить с помощью оптимизации, называемой “прямым чтением”. Точнее, эту оптимизацию можно применять, если запрос SELECT не выбирает данные из текстового столбца. Пример:

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

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

Настройка query_plan_direct_read_from_text_index (по умолчанию: 1), которая определяет, включено ли прямое чтение в целом.
Настройка use_skip_indexes_on_data_read (по умолчанию: 1), которая является еще одним обязательным условием для прямого чтения. Обратите внимание: в базах данных 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;

возвращает

[...]
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;

возвращает

[...]
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>. Если этот столбец есть, значит используется прямое чтение.

Пример: набор данных 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, а затем материализуем его:

-- Добавить индекс
ALTER TABLE hackernews ADD INDEX comment_idx(comment) TYPE text(tokenizer = splitByNonAlpha);

-- Материализовать индекс для существующих данных
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.362s против 0.008s) и обрабатывает значительно меньше данных (9.51 GB против 3.15 MB), поскольку считывает данные только из индекса.

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 KB.

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.184s против 0.007s), чем стандартное сканирование индекса пропуска данных.

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

Оптимизация прямого чтения также применяется к составным логическим выражениям. Здесь мы выполним регистронезависимый поиск по ‘ClickHouse’ OR ‘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.450s против 0.013s) и позволяет не читать 9.58 GB данных столбца. В этом конкретном случае предпочтительнее и эффективнее использовать синтаксис hasAnyTokens(comment, ['ClickHouse', 'clickhouse']).

Настройка текстового индекса

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

Настройки сервера

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

Setting	Description	Default
text_index_dictionary_block_cache_policy	Имя политики кэширования блоков словаря текстового индекса.	`SLRU`
text_index_dictionary_block_cache_size	Максимальный размер кэша в байтах.	`1073741824`
text_index_dictionary_block_cache_max_entries	Максимальное количество десериализованных блоков словаря в кэше.	`1'000'000`
text_index_dictionary_block_cache_size_ratio	Размер защищённой очереди в кэше блоков словаря текстового индекса относительно общего размера кэша.	`0.5`

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

Настройка	Описание	По умолчанию
text_index_header_cache_policy	Название политики кэша заголовков текстового индекса.	`SLRU`
text_index_header_cache_size	Максимальный размер кэша в байтах.	`1073741824`
text_index_header_cache_max_entries	Максимальное количество десериализованных заголовков в кэше.	`100'000`
text_index_header_cache_size_ratio	Размер защищённой очереди в кэше заголовков текстового индекса относительно общего размера кэша.	`0.5`

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

Настройка	Описание	По умолчанию
text_index_postings_cache_policy	Имя политики кэша списков вхождений текстового индекса.	`SLRU`
text_index_postings_cache_size	Максимальный размер кэша в байтах.	`2147483648`
text_index_postings_cache_max_entries	Максимальное количество десериализованных списков вхождений в кэше.	`1'000'000`
text_index_postings_cache_size_ratio	Размер защищённой очереди в кэше списков вхождений текстового индекса относительно общего размера кэша.	`0.5`

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

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

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

​= and !=

​IN и NOT IN

​LIKE, NOT LIKE и match

​startsWith и endsWith

​hasToken and hasTokenOrNull

​hasAnyTokens and hasAllTokens

​has

​mapContains

​operator[]

​Примеры поддержки Array и Map в текстовом индексе.

​Индексация Array(String)

​Индексация Map

​Реализация

​Структура индекса

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

​Пример: набор данных Hacker News

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

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

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

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

​Настройка текстового индекса

​Настройки сервера

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

​Настройки кэша заголовков текстового индекса

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

​Материалы по теме

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

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

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

`=` and `!=`

`IN` и `NOT IN`

`LIKE`, `NOT LIKE` и `match`

`startsWith` и `endsWith`

`hasToken` and `hasTokenOrNull`

`hasAnyTokens` and `hasAllTokens`

`has`

`mapContains`

`operator[]`

Примеры поддержки `Array` и `Map` в текстовом индексе.

Индексация Array(String)

Индексация Map

Реализация

Структура индекса

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

Пример: набор данных Hacker News

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

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

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

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

Настройка текстового индекса

Настройки сервера

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

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

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

Материалы по теме