> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Быстро находите нужные термины в тексте. # Полнотекстовый поиск с помощью текстовых индексов export const PrivatePreviewBadge = () => { return
{'Закрытая предварительная версия в ClickHouse Cloud'}
; }; Текстовые индексы в ClickHouse (также известные как ["инвертированные индексы"](https://en.wikipedia.org/wiki/Inverted_index)) обеспечивают быстрый полнотекстовый поиск по строковым данным. Индекс сопоставляет каждый токен в столбце со строками, содержащими этот токен. Токены создаются в процессе, называемом токенизацией. Например, по умолчанию ClickHouse токенизирует английское предложение "All cat like mice." как \["All", "cat", "like", "mice"] (обратите внимание, что точка в конце игнорируется). Доступны и более продвинутые токенизаторы, например для лог-данных.
## Создание текстового индекса
Чтобы создать текстовый индекс, сначала включите соответствующую экспериментальную настройку: ```sql theme={null} SET allow_experimental_full_text_index = true; ``` Текстовый индекс можно определить для столбца типа [String](/ru/reference/data-types/string), [FixedString](/ru/reference/data-types/fixedstring), [Array(String)](/ru/reference/data-types/array), [Array(FixedString)](/ru/reference/data-types/array) и [Map](/ru/reference/data-types/map) (с помощью функций [mapKeys](/ru/reference/functions/regular-functions/tuple-map-functions#mapkeys) и [mapValues](/ru/reference/functions/regular-functions/tuple-map-functions#mapvalues)), используя следующий синтаксис: ```sql theme={null} 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](/ru/reference/functions/regular-functions/splitting-merging-functions#splitByNonAlpha)). * `splitByString(S)` разбивает строки по заданным пользователем строкам-разделителям `S` (см. также функцию [splitByString](/ru/reference/functions/regular-functions/splitting-merging-functions#splitByString)). Разделители можно указать с помощью необязательного параметра, например `tokenizer = splitByString([', ', '; ', '\n', '\\'])`. Обратите внимание, что каждый разделитель может состоять из нескольких символов (`', '` в примере). Список разделителей по умолчанию, если он не указан явно (например, `tokenizer = splitByString`), — это один пробел `[' ']`. * `ngrams(N)` разбивает строки на `N`-граммы одинаковой длины (см. также функцию [ngrams](/ru/reference/functions/regular-functions/splitting-merging-functions#ngrams)). Длину n-граммы можно указать с помощью необязательного целочисленного параметра от 2 до 8, например `tokenizer = ngrams(3)`. Длина n-граммы по умолчанию, если она не указана явно (например, `tokenizer = ngrams`), равна 3. * `array` не выполняет токенизацию, то есть каждое значение строки является токеном (см. также функцию [array](/ru/reference/functions/regular-functions/array-functions#array)). * `sparseGrams(min_length, max_length, min_cutoff_length)` — использует тот же алгоритм, что и функция [sparseGrams](/ru/reference/functions/regular-functions/string-functions#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']`. В большинстве случаев желательно, чтобы при сопоставлении сначала выбирались более длинные разделители. Обычно этого можно добиться, передавая строки-разделители в порядке убывания длины. Если строки-разделители образуют [префиксный код](https://en.wikipedia.org/wiki/Prefix_code), их можно передавать в произвольном порядке. В настоящее время не рекомендуется строить текстовые индексы для текста на незападных языках, например китайском. Поддерживаемые сейчас токенизаторы могут приводить к очень большим размерам индекса и долгому времени выполнения запросов. В будущем мы планируем добавить специализированные токенизаторы для отдельных языков, которые будут лучше обрабатывать такие случаи. Чтобы проверить, как токенизаторы разбивают входную строку, можно воспользоваться функцией [tokens](/ru/reference/functions/regular-functions/splitting-merging-functions#tokens) в ClickHouse: Например, ```sql theme={null} SELECT tokens('abc def', 'ngrams', 3) AS tokens; ``` возвращает ```result theme={null} +-tokens--------------------------+ | ['abc','bc ','c d',' de','def'] | +---------------------------------+ ``` **Аргумент preprocessor**. Необязательный аргумент `preprocessor` — это выражение, которое преобразует входную строку перед токенизацией. Типичные сценарии использования аргумента preprocessor: 1. Приведение входных строк к нижнему (или верхнему) регистру для регистронезависимого сопоставления, например [lower](/ru/reference/functions/regular-functions/string-functions#lower), [lowerUTF8](/ru/reference/functions/regular-functions/string-functions#lowerUTF8), см. первый пример ниже. 2. Нормализация UTF-8, например [normalizeUTF8NFC](/ru/reference/functions/regular-functions/string-functions#normalizeUTF8NFC), [normalizeUTF8NFD](/ru/reference/functions/regular-functions/string-functions#normalizeUTF8NFD), [normalizeUTF8NFKC](/ru/reference/functions/regular-functions/string-functions#normalizeUTF8NFKC), [normalizeUTF8NFKD](/ru/reference/functions/regular-functions/string-functions#normalizeUTF8NFKD), [toValidUTF8](/ru/reference/functions/regular-functions/string-functions#toValidUTF8). 3. Удаление или преобразование нежелательных символов или подстрок, например [extractTextFromHTML](/ru/reference/functions/regular-functions/string-functions#extractTextFromHTML), [substring](/ru/reference/functions/regular-functions/string-functions#substring), [idnaEncode](/ru/reference/functions/regular-functions/string-functions#idnaEncode). Выражение preprocessor должно преобразовывать входное значение типа [String](/ru/reference/data-types/string) или [FixedString](/ru/reference/data-types/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](/ru/reference/functions/regular-functions/string-search-functions#hasToken), [hasAllTokens](/ru/reference/functions/regular-functions/string-search-functions#hasAllTokens) и [hasAnyTokens](/ru/reference/functions/regular-functions/string-search-functions#hasAnyTokens) используют preprocessor, чтобы сначала преобразовать поисковый запрос перед его токенизацией. Например: ```sql theme={null} 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'); ``` эквивалентно: ```sql theme={null} 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 реализованы как [вторичные индексы](/ru/reference/engines/table-engines/mergetree-family/mergetree#skip-index-types). Однако, в отличие от других индексов пропуска данных, для текстовых индексов значение GRANULARITY по умолчанию равно 64. Это значение было выбрано эмпирически и обеспечивает хороший компромисс между скоростью и размером индекса для большинства сценариев использования. Опытные пользователи могут указать другое значение гранулярности индекса (мы не рекомендуем этого делать). Значения по умолчанию для следующих расширенных параметров хорошо подходят практически для всех ситуаций. Мы не рекомендуем их изменять. Необязательный параметр `dictionary_block_size` (по умолчанию: 128) задаёт размер блоков словаря в строках. Необязательный параметр `dictionary_block_frontcoding_compression` (по умолчанию: 1) указывает, используют ли блоки словаря фронт-кодирование для сжатия. Необязательный параметр `max_cardinality_for_embedded_postings` (по умолчанию: 16) задаёт порог мощности, ниже которого списки вхождений следует встраивать в блоки словаря. Необязательный параметр `bloom_filter_false_positive_rate` (по умолчанию: 0.1) задаёт уровень ложноположительных срабатываний фильтра Блума словаря. Текстовые индексы можно добавлять в столбец или удалять из него после создания таблицы: ```sql theme={null} ALTER TABLE tab DROP INDEX text_idx; ALTER TABLE tab ADD INDEX text_idx(s) TYPE text(tokenizer = splitByNonAlpha); ```
## Использование текстового индекса
Использовать текстовый индекс в запросах SELECT несложно: распространённые функции поиска по строкам автоматически задействуют индекс. Если индекс отсутствует, указанные ниже функции поиска по строкам будут выполнять медленный полный перебор.
### Поддерживаемые функции
Текстовый индекс можно использовать, если в условии `WHERE` запроса SELECT используются текстовые функции: ```sql theme={null} SELECT [...] FROM [...] WHERE string_search_function(column_with_text_index) ```
#### `=` and `!=`
`=` ([equals](/ru/reference/functions/regular-functions/comparison-functions#equals)) and `!=` ([notEquals](/ru/reference/functions/regular-functions/comparison-functions#notEquals) ) совпадают со всем указанным поисковым выражением. Пример: ```sql theme={null} SELECT * from tab WHERE str = 'Hello'; ``` Текстовый индекс поддерживает `=` и `!=`, однако поиск по равенству и неравенству имеет смысл только с токенизатором `array` (при котором индекс сохраняет значения строки целиком).
#### `IN` и `NOT IN`
`IN` ([in](/ru/reference/functions/regular-functions/in-functions)) и `NOT IN` ([notIn](/ru/reference/functions/regular-functions/in-functions)) похожи на функции `equals` и `notEquals`, но позволяют проверить наличие всех (`IN`) или отсутствие всех (`NOT IN`) поисковых терминов. Пример: ```sql theme={null} SELECT * from tab WHERE str IN ('Hello', 'World'); ``` Действуют те же ограничения, что и для `=` и `!=`, то есть `IN` и `NOT IN` имеют смысл только при использовании токенизатора `array`.
#### `LIKE`, `NOT LIKE` и `match`
В настоящее время эти функции используют текстовый индекс для фильтрации только в том случае, если в качестве токенизатора индекса используется `splitByNonAlpha` или `ngrams`. Чтобы использовать `LIKE` [like](/ru/reference/functions/regular-functions/string-search-functions#like), `NOT LIKE` ([notLike](/ru/reference/functions/regular-functions/string-search-functions#notLike)) и функцию [match](/ru/reference/functions/regular-functions/string-search-functions#match) с текстовыми индексами, ClickHouse должен иметь возможность извлекать полные токены из поискового выражения. Пример: ```sql theme={null} SELECT count() FROM tab WHERE comment LIKE 'support%'; ``` `support` в примере может соответствовать `support`, `supports`, `supporting` и т. д. Такой запрос является запросом на поиск подстроки, и его нельзя ускорить с помощью текстового индекса. Чтобы задействовать текстовый индекс для запросов LIKE, шаблон LIKE нужно переписать следующим образом: ```sql theme={null} SELECT count() FROM tab WHERE comment LIKE ' support %'; -- или `% support %` ``` Пробелы слева и справа от `support` гарантируют, что этот термин можно выделить как токен.
#### `startsWith` и `endsWith`
Как и в случае с `LIKE`, функции [startsWith](/ru/reference/functions/regular-functions/string-functions#startsWith) и [endsWith](/ru/reference/functions/regular-functions/string-functions#endsWith) могут использовать текстовый индекс только в том случае, если из поискового выражения можно выделить полные токены. Пример: ```sql theme={null} SELECT count() FROM tab WHERE startsWith(comment, 'clickhouse support'); ``` В этом примере только `clickhouse` считается токеном. `support` не считается токеном, потому что может соответствовать `support`, `supports`, `supporting` и т. д. Чтобы найти все строки, начинающиеся с `clickhouse supports`, завершите шаблон поиска пробелом в конце: ```sql theme={null} startsWith(comment, 'clickhouse supports ')` ``` Аналогично, `endsWith` следует использовать с пробелом в начале: ```sql theme={null} SELECT count() FROM tab WHERE endsWith(comment, ' olap engine'); ```
#### `hasToken` and `hasTokenOrNull`
Функции [hasToken](/ru/reference/functions/regular-functions/string-search-functions#hasToken) и [hasTokenOrNull](/ru/reference/functions/regular-functions/string-search-functions#hasTokenOrNull) выполняют поиск по одному заданному токену. В отличие от ранее упомянутых функций, они не токенизируют поисковый запрос (предполагается, что входное значение — один токен). Пример: ```sql theme={null} SELECT count() FROM tab WHERE hasToken(comment, 'clickhouse'); ``` Функции `hasToken` и `hasTokenOrNull` обеспечивают максимальную производительность при использовании с индексом `text`.
#### `hasAnyTokens` and `hasAllTokens`
Функции [hasAnyTokens](/ru/reference/functions/regular-functions/string-search-functions#hasAnyTokens) и [hasAllTokens](/ru/reference/functions/regular-functions/string-search-functions#hasAllTokens) проверяют совпадение с любым из указанных токенов или со всеми указанными токенами. Эти две функции принимают поисковые токены либо в виде строки, которая будет разбита на токены с помощью того же токенизатора, что используется для индексного столбца, либо в виде массива уже обработанных токенов, к которому перед поиском токенизация применяться не будет. Дополнительные сведения см. в документации по функциям. Пример: ```sql theme={null} -- Токены поиска переданы в виде строкового аргумента 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](/ru/reference/functions/regular-functions/array-functions#has) выполняет поиск одного токена в массиве строк. Пример: ```sql theme={null} SELECT count() FROM tab WHERE has(array, 'clickhouse'); ```
#### `mapContains`
Функция [mapContains](/ru/reference/functions/regular-functions/tuple-map-functions#mapcontainskey)(псевдоним: `mapContainsKey`) проверяет наличие одного токена в ключах Map. Пример: ```sql theme={null} SELECT count() FROM tab WHERE mapContainsKey(map, 'clickhouse'); -- OR SELECT count() FROM tab WHERE mapContains(map, 'clickhouse'); ```
#### `operator[]`
Оператор доступа [operator\[\]](/ru/reference/operators/index#access-operators) можно использовать с текстовым индексом для фильтрации ключей и значений. Пример: ```sql theme={null} SELECT count() FROM tab WHERE map['engine'] = 'clickhouse'; -- will use the text index if defined ``` См. приведённые ниже примеры использования `Array(T)` и `Map(K, V)` с текстовым индексом.
### Примеры поддержки `Array` и `Map` в текстовом индексе.
#### Индексация Array(String)
На простой блог-платформе авторы присваивают своим постам ключевые слова, чтобы распределять контент по категориям. Обычно пользователи могут находить связанные материалы, нажимая на ключевые слова или выполняя поиск по темам. Рассмотрим следующее определение таблицы: ```sql theme={null} CREATE TABLE posts ( post_id UInt64, title String, content String, keywords Array(String) COMMENT 'Author-defined keywords' ) ENGINE = MergeTree ORDER BY (post_id); ``` Без текстового индекса для поиска постов по определённому ключевому слову (например, `clickhouse`) приходится сканировать все записи: ```sql theme={null} SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- медленное полное сканирование таблицы — проверяет каждое ключевое слово в каждом посте ``` По мере роста платформы это работает всё медленнее, поскольку запросу приходится проверять каждый массив keywords в каждой строке. Чтобы устранить эту проблему с производительностью, можно создать текстовый индекс для `keywords`, который формирует оптимизированную для поиска структуру, заранее обрабатывающую все ключевые слова и обеспечивающую мгновенный lookup: ```sql theme={null} ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha); ``` Важно: после добавления текстового индекса его нужно перестроить для уже существующих данных: ```sql theme={null} ALTER TABLE posts MATERIALIZE INDEX keywords_idx; ```
#### Индексация Map
В системе сбора журналов запросы к серверу часто сохраняют метаданные в виде пар ключ-значение. Командам эксплуатации нужно эффективно искать по журналам при отладке, расследовании инцидентов безопасности и мониторинге. Рассмотрим эту таблицу журналов: ```sql theme={null} CREATE TABLE logs ( id UInt64, timestamp DateTime, message String, attributes Map(String, String) ) ENGINE = MergeTree ORDER BY (timestamp); ``` Без текстового индекса поиск по данным [Map](/ru/reference/data-types/map) требует полного сканирования таблицы: 1. Находит все записи журнала, связанные с ограничением частоты: ```sql theme={null} SELECT count() FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- медленное полное сканирование таблицы ``` 2. Находит все записи журнала с определённого IP: ```sql theme={null} SELECT count() FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- медленное полное сканирование таблицы ``` По мере роста объема журналов эти запросы замедляются. Решение — создать текстовый индекс для ключей и значений [Map](/ru/reference/data-types/map). Используйте [mapKeys](/ru/reference/functions/regular-functions/tuple-map-functions#mapkeys), чтобы создать текстовый индекс, если вам нужно искать журналы по именам полей или типам атрибутов: ```sql theme={null} ALTER TABLE logs ADD INDEX attributes_keys_idx mapKeys(attributes) TYPE text(tokenizer = array); ``` Используйте [mapValues](/ru/reference/functions/regular-functions/tuple-map-functions#mapvalues), чтобы создать текстовый индекс, если нужно искать по фактическому содержимому атрибутов: ```sql theme={null} ALTER TABLE logs ADD INDEX attributes_vals_idx mapValues(attributes) TYPE text(tokenizer = array); ``` Важно: после добавления текстового индекса его нужно перестроить для уже существующих данных: ```sql theme={null} ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx; ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx; ``` 1. Найдите все запросы, попавшие под ограничение частоты: ```sql theme={null} SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- быстро ``` 2. Находит все записи журнала с определённого IP-адреса: ```sql theme={null} SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- быстро ```
## Реализация
### Структура индекса
Каждый текстовый индекс состоит из двух (абстрактных) структур данных: * словаря, который сопоставляет каждому токену список вхождений, и * набора списков вхождений, каждый из которых представляет собой множество номеров строк. Поскольку текстовый индекс является индексом пропуска данных, эти структуры данных логически существуют для каждой гранулы индекса. При создании индекса создаются три файла (для каждой части): **Файл блоков словаря (.dct)** Токены в грануле индекса сортируются и сохраняются в блоках словаря по 128 токенов в каждом (размер блока настраивается параметром `dictionary_block_size`). Файл блоков словаря (.dct) содержит все блоки словаря для всех гранул индекса в части. **Файл гранул индекса (.idx)** Файл гранул индекса содержит для каждого блока словаря первый токен блока, его относительное смещение в файле блоков словаря и фильтр Блума для всех токенов в блоке. Эта структура разреженного индекса похожа на [разреженный индекс первичного ключа](/ru/guides/clickhouse/data-modelling/sparse-primary-indexes)). Фильтр Блума позволяет заранее пропускать блоки словаря, если искомый токен отсутствует в блоке. **Файл списков вхождений (.pst)** Списки вхождений для всех токенов располагаются последовательно в файле списков вхождений. Чтобы экономить место и при этом обеспечивать быстрые операции пересечения и объединения, списки вхождений хранятся в виде [битмапов Roaring](https://roaringbitmap.org/). Если мощность списка вхождений меньше 16 (настраивается параметром `max_cardinality_for_embedded_postings`), он встраивается в словарь.
### Прямое чтение
Некоторые типы текстовых запросов можно значительно ускорить с помощью оптимизации, называемой "прямым чтением". Точнее, эту оптимизацию можно применять, если запрос SELECT *не* выбирает данные из текстового столбца. Пример: ```sql theme={null} 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](/ru/reference/settings/session-settings#query_plan_direct_read_from_text_index) (по умолчанию: 1), которая определяет, включено ли прямое чтение в целом. * Настройка [use\_skip\_indexes\_on\_data\_read](/ru/reference/settings/session-settings#use_skip_indexes_on_data_read) (по умолчанию: 1), которая является еще одним обязательным условием для прямого чтения. Обратите внимание: в базах данных ClickHouse с [compatibility](/ru/reference/settings/session-settings#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`. Например, запрос с отключенным прямым чтением ```sql theme={null} EXPLAIN PLAN actions = 1 SELECT count() FROM tab WHERE hasToken(col, 'some_token') SETTINGS query_plan_direct_read_from_text_index = 0; ``` возвращает ```text theme={null} [...] 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` ```sql theme={null} EXPLAIN PLAN actions = 1 SELECT count() FROM tab WHERE hasToken(col, 'some_token') SETTINGS query_plan_direct_read_from_text_index = 1; ``` возвращает ```text theme={null} [...] 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___`. Если этот столбец есть, значит используется прямое чтение.
## Пример: набор данных Hacker News
Давайте посмотрим, как текстовые индексы улучшают производительность на большом наборе данных с большим объёмом текста. Мы будем использовать 28,7 млн строк комментариев с популярного сайта Hacker News. Вот таблица без текстового индекса: ```sql theme={null} 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`: ```sql theme={null} 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, а затем материализуем его: ```sql theme={null} -- Добавить индекс 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 использует индекс пропуска данных для фильтрации гранул, а затем считывает данные столбца для этих гранул. Мы можем смоделировать такое поведение, отключив прямое чтение. ```sql theme={null} 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 ``` **Прямое чтение включено (быстрое чтение по индексу)** Теперь выполним тот же запрос с включенным прямым чтением (это значение используется по умолчанию). ```sql theme={null} 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'. **Прямое чтение отключено (стандартное сканирование)** ```sql theme={null} 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 ``` **Прямое чтение включено (быстрое чтение по индексу)** ```sql theme={null} 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 из столбца. ```sql theme={null} 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. ```sql theme={null} 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'. **Прямое чтение отключено (стандартное сканирование)** ```sql theme={null} 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 ``` **Прямое чтение включено (Быстрое чтение по индексу)** ```sql theme={null} 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](/ru/reference/settings/session-settings#use_text_index_dictionary_cache), [use\_text\_index\_header\_cache](/ru/reference/settings/session-settings#use_text_index_header_cache) и [use\_text\_index\_postings\_cache](/ru/reference/settings/session-settings#use_text_index_postings_cache) соответственно. По умолчанию они отключены. Чтобы настроить кэш, обратитесь к следующим настройкам сервера.
### Настройки сервера
#### Настройки кэша блоков словаря
| Setting | Description | Default | | ---------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------- | ------------ | | [text\_index\_dictionary\_block\_cache\_policy](/ru/reference/settings/server-settings/settings#text_index_dictionary_block_cache_policy) | Имя политики кэширования блоков словаря текстового индекса. | `SLRU` | | [text\_index\_dictionary\_block\_cache\_size](/ru/reference/settings/server-settings/settings#text_index_dictionary_block_cache_size) | Максимальный размер кэша в байтах. | `1073741824` | | [text\_index\_dictionary\_block\_cache\_max\_entries](/ru/reference/settings/server-settings/settings#text_index_dictionary_block_cache_max_entries) | Максимальное количество десериализованных блоков словаря в кэше. | `1'000'000` | | [text\_index\_dictionary\_block\_cache\_size\_ratio](/ru/reference/settings/server-settings/settings#text_index_dictionary_block_cache_size_ratio) | Размер защищённой очереди в кэше блоков словаря текстового индекса относительно общего размера кэша. | `0.5` |
#### Настройки кэша заголовков текстового индекса
| Настройка | Описание | По умолчанию | | ------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------ | ------------ | | [text\_index\_header\_cache\_policy](/ru/reference/settings/server-settings/settings#text_index_header_cache_policy) | Название политики кэша заголовков текстового индекса. | `SLRU` | | [text\_index\_header\_cache\_size](/ru/reference/settings/server-settings/settings#text_index_header_cache_size) | Максимальный размер кэша в байтах. | `1073741824` | | [text\_index\_header\_cache\_max\_entries](/ru/reference/settings/server-settings/settings#text_index_header_cache_max_entries) | Максимальное количество десериализованных заголовков в кэше. | `100'000` | | [text\_index\_header\_cache\_size\_ratio](/ru/reference/settings/server-settings/settings#text_index_header_cache_size_ratio) | Размер защищённой очереди в кэше заголовков текстового индекса относительно общего размера кэша. | `0.5` |
#### Настройки кэша списков вхождений
| Настройка | Описание | По умолчанию | | ----------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------- | ------------ | | [text\_index\_postings\_cache\_policy](/ru/reference/settings/server-settings/settings#text_index_postings_cache_policy) | Имя политики кэша списков вхождений текстового индекса. | `SLRU` | | [text\_index\_postings\_cache\_size](/ru/reference/settings/server-settings/settings#text_index_postings_cache_size) | Максимальный размер кэша в байтах. | `2147483648` | | [text\_index\_postings\_cache\_max\_entries](/ru/reference/settings/server-settings/settings#text_index_postings_cache_max_entries) | Максимальное количество десериализованных списков вхождений в кэше. | `1'000'000` | | [text\_index\_postings\_cache\_size\_ratio](/ru/reference/settings/server-settings/settings#text_index_postings_cache_size_ratio) | Размер защищённой очереди в кэше списков вхождений текстового индекса относительно общего размера кэша. | `0.5` |
## Материалы по теме
* Блог: [Представляем инвертированные индексы в ClickHouse](https://clickhouse.com/blog/clickhouse-search-with-inverted-indices) * Блог: [Как устроен полнотекстовый поиск в ClickHouse: быстрый, нативный и столбцовый](https://clickhouse.com/blog/clickhouse-full-text-search) * Видео: [Полнотекстовые индексы: проектирование и эксперименты](https://www.youtube.com/watch?v=O_MnyUkrIq8)