Pesquisa de texto completo usando índices de texto

Os índices de texto no ClickHouse (também conhecidos como “índices invertidos”) oferecem recursos rápidos de busca por texto completo em dados do tipo string. O índice mapeia cada token da coluna para as linhas que contêm esse token. Os tokens são gerados por um processo chamado tokenização. Por exemplo, por padrão, o ClickHouse tokeniza a frase em inglês “All cat like mice.” como [“All”, “cat”, “like”, “mice”] (observe que o ponto final é ignorado). Tokenizers mais avançados estão disponíveis, por exemplo, para dados de log.

Criando um índice de texto

Para criar um índice de texto, primeiro ative a configuração experimental correspondente:

SET allow_experimental_full_text_index = true;

Um índice de texto pode ser definido em uma coluna do tipo String, FixedString, Array(String), Array(FixedString) e Map (por meio das funções de Map mapKeys e mapValues), usando a seguinte sintaxe:

CREATE TABLE tab
(
    `key` UInt64,
    `str` String,
    INDEX text_idx(str) TYPE text(
                                -- Parâmetros obrigatórios:
                                tokenizer = splitByNonAlpha|splitByString(S)|ngrams(N)|array
                                -- Parâmetros opcionais:
                                [, preprocessor = expression(str)]
                                -- Parâmetros avançados opcionais:
                                [, 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

Argumento tokenizer. O argumento tokenizer especifica o tokenizer:

splitByNonAlpha divide strings em caracteres ASCII não alfanuméricos (veja também a função splitByNonAlpha).
splitByString(S) divide strings com base em determinadas strings separadoras S definidas pelo usuário (veja também a função splitByString). Os separadores podem ser especificados usando um parâmetro opcional, por exemplo, tokenizer = splitByString([', ', '; ', '\n', '\\']). Observe que cada string pode ser composta por vários caracteres (', ' no exemplo). A lista padrão de separadores, se não for especificada explicitamente (por exemplo, tokenizer = splitByString), é um único espaço em branco [' '].
ngrams(N) divide strings em N-gramas de mesmo tamanho (veja também a função ngrams). O comprimento do ngram pode ser especificado usando um parâmetro inteiro opcional entre 2 e 8, por exemplo, tokenizer = ngrams(3). O tamanho padrão do ngram, se não for especificado explicitamente (por exemplo, tokenizer = ngrams), é 3.
array não realiza tokenização, ou seja, cada valor de uma linha é um token (veja também a função array).
sparseGrams(min_length, max_length, min_cutoff_length) — usa o mesmo algoritmo da função sparseGrams para dividir uma string em todos os ngrams de min_length e em vários ngrams maiores, até max_length, inclusive. Se min_cutoff_length for especificado, somente N-gramas com comprimento maior ou igual a min_cutoff_length serão salvos no índice. Diferentemente de ngrams(N), que gera apenas N-gramas de comprimento fixo, sparseGrams produz um conjunto de N-gramas de comprimento variável dentro do intervalo especificado, permitindo uma representação mais flexível do contexto do texto. Por exemplo, tokenizer = sparseGrams(3, 5, 4) gerará 3-, 4- e 5-gramas a partir da string de entrada e salvará apenas os 4- e 5-gramas no índice.

O tokenizer splitByString aplica os separadores de divisão da esquerda para a direita. Isso pode criar ambiguidades. Por exemplo, as strings separadoras ['%21', '%'] farão com que %21abc seja tokenizado como ['abc'], enquanto inverter a ordem dessas duas strings separadoras para ['%', '%21'] produzirá ['21abc']. Na maioria dos casos, o ideal é que a correspondência priorize primeiro os separadores mais longos. Em geral, isso pode ser feito passando as strings separadoras em ordem decrescente de comprimento. Se as strings separadoras formarem um código de prefixo, elas podem ser passadas em qualquer ordem.

No momento, não é recomendável criar índices de texto sobre textos em idiomas não ocidentais, por exemplo, chinês. Os tokenizers atualmente compatíveis podem levar a tamanhos de índice enormes e tempos de consulta elevados. Planejamos adicionar no futuro tokenizers especializados por idioma, que lidarão melhor com esses casos.

Para testar como os tokenizers dividem o texto de entrada, você pode usar a função tokens do ClickHouse: Por exemplo,

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

retorna

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

Argumento preprocessor. O argumento opcional preprocessor é uma expression que transforma a string de entrada antes da tokenização. Os casos de uso típicos do argumento preprocessor incluem

Converter as strings de entrada em minúsculas (ou maiúsculas) para permitir correspondência sem diferenciar maiúsculas de minúsculas, por exemplo, lower, lowerUTF8; veja o primeiro exemplo abaixo.
Normalização UTF-8, por exemplo, normalizeUTF8NFC, normalizeUTF8NFD, normalizeUTF8NFKC, normalizeUTF8NFKD, toValidUTF8.
Remover ou transformar caracteres ou substrings indesejados, por exemplo, extractTextFromHTML, substring, idnaEncode.

A expressão do preprocessor deve transformar um valor de entrada do tipo String ou FixedString em um valor do mesmo tipo. Exemplos:

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))

Além disso, a expressão do preprocessor deve referenciar apenas a coluna sobre a qual o índice de texto foi definido. Não é permitido usar funções não determinísticas. As funções hasToken, hasAllTokens e hasAnyTokens usam o preprocessor para primeiro transformar o termo de busca antes de tokenizá-lo. Por exemplo:

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');

equivale a:

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'));

Outros argumentos. Os índices de texto no ClickHouse são implementados como índices secundários. No entanto, ao contrário de outros índices de skipping, os índices de texto têm uma GRANULARITY padrão de 64. Esse valor foi definido empiricamente e oferece um bom equilíbrio entre velocidade e tamanho do índice para a maioria dos casos de uso. Usuários avançados podem especificar uma granularidade de índice diferente (não recomendamos isso).

Parâmetros avançados opcionais

Os valores padrão dos parâmetros avançados a seguir funcionam bem em praticamente todas as situações. Não recomendamos alterá-los.O parâmetro opcional dictionary_block_size (padrão: 128) especifica o tamanho dos blocos do dicionário em linhas.O parâmetro opcional dictionary_block_frontcoding_compression (padrão: 1) especifica se os blocos do dicionário usam front coding para compressão.O parâmetro opcional max_cardinality_for_embedded_postings (padrão: 16) especifica o limite de cardinalidade abaixo do qual as posting lists devem ser incorporadas aos blocos do dicionário.O parâmetro opcional bloom_filter_false_positive_rate (padrão: 0.1) especifica a taxa de falso positivo do filtro de Bloom do dicionário.

Índices de texto podem ser adicionados a uma coluna ou removidos dela depois que a tabela for criada:

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

Usando um índice de texto

Usar um índice de texto em consultas SELECT é simples, pois funções comuns de pesquisa em strings usarão o índice automaticamente. Se não existir nenhum índice, as funções de pesquisa em strings abaixo recorrerão a varreduras lentas por força bruta.

Funções suportadas

O índice de texto pode ser usado quando funções de texto são usadas na cláusula WHERE de uma consulta SELECT:

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

`=` and `!=`

= (equals) and != (notEquals ) correspondem exatamente ao termo de busca fornecido. Exemplo:

SELECT * from tab WHERE str = 'Hello';

O índice de texto oferece suporte a = e !=, mas a busca por igualdade e desigualdade só faz sentido com o tokenizer array (o que faz com que o índice armazene os valores completos da linha).

`IN` and `NOT IN`

IN (in) e NOT IN (notIn) são semelhantes às funções equals e notEquals, mas correspondem a todos (IN) ou a nenhum (NOT IN) dos termos de busca. Exemplo:

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

Aplicam-se as mesmas restrições de = e !=; ou seja, IN e NOT IN só fazem sentido em conjunto com o tokenizer array.

`LIKE`, `NOT LIKE` e `match`

Atualmente, essas funções usam o índice de texto para filtragem somente se o tokenizer do índice for splitByNonAlpha ou ngrams.

Para usar LIKE like, NOT LIKE (notLike) e a função match com índices de texto, o ClickHouse precisa conseguir extrair tokens completos do termo de pesquisa. Exemplo:

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

support no exemplo pode corresponder a support, supports, supporting etc. Esse tipo de consulta é uma consulta de substring e não pode ser acelerada por um índice de texto. Para usar um índice de texto em consultas LIKE, o padrão do LIKE deve ser reescrito da seguinte forma:

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

Os espaços à esquerda e à direita de support garantem que o termo possa ser extraído como um token.

`startsWith` and `endsWith`

Assim como LIKE, as funções startsWith e endsWith só podem usar um índice de texto se for possível extrair tokens completos do termo de pesquisa. Exemplo:

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

No exemplo, apenas clickhouse é considerado um token. support não é considerado um token porque pode corresponder a support, supports, supporting etc. Para encontrar todas as linhas que começam com clickhouse supports, termine o padrão de pesquisa com um espaço no final:

startsWith(comment, 'clickhouse supports ')`

Da mesma forma, endsWith deve ser usado com um espaço à esquerda:

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

`hasToken` and `hasTokenOrNull`

As funções hasToken e hasTokenOrNull fazem a correspondência com um único token fornecido. Ao contrário das funções mencionadas anteriormente, elas não tokenizam o termo de busca (presumem que a entrada seja um único token). Exemplo:

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

As funções hasToken e hasTokenOrNull oferecem o melhor desempenho para uso com o índice text.

`hasAnyTokens` and `hasAllTokens`

As funções hasAnyTokens e hasAllTokens fazem correspondência com um ou com todos os tokens fornecidos. Essas duas funções aceitam os tokens de busca como uma string, que será tokenizada usando o mesmo tokenizer usado na coluna indexada, ou como um array de tokens já processados, aos quais não será aplicada nenhuma tokenização antes da busca. Consulte a documentação da função para mais informações. Exemplo:

-- Tokens de busca passados como argumento string
SELECT count() FROM tab WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM tab WHERE hasAllTokens(comment, 'clickhouse olap');

-- Tokens de busca passados como Array(String)
SELECT count() FROM tab WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM tab WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

`has`

A função de Array has faz a correspondência com um único token em um array de strings. Exemplo:

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

`mapContains`

A função mapContains(sinônimo de: mapContainsKey) faz correspondência com um único token nas chaves de um map. Exemplo:

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

`operator[]`

O operator[] de acesso pode ser usado com o índice de texto para filtrar chaves e valores. Exemplo:

SELECT count() FROM tab WHERE map['engine'] = 'clickhouse'; -- will use the text index if defined

Veja os exemplos a seguir de uso de Array(T) e Map(K, V) com o índice de texto.

Exemplos de suporte a `Array` e `Map` no índice de texto.

Indexação de Array(String)

Em uma plataforma simples de blogs, os autores atribuem palavras-chave às suas postagens para categorizar o conteúdo. Um recurso comum permite que os usuários descubram conteúdo relacionado clicando em palavras-chave ou pesquisando tópicos. Considere esta definição de tabela:

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

Sem um índice de texto, encontrar posts com uma palavra-chave específica (por exemplo, clickhouse) exige varrer todas as entradas:

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- varredura lenta da tabela inteira - verifica cada palavra-chave em cada post

À medida que a plataforma cresce, isso se torna cada vez mais lento, porque a consulta precisa examinar cada array de palavras-chave em cada linha. Para contornar esse problema de desempenho, podemos definir um índice de texto para keywords, que cria uma estrutura otimizada para pesquisa, pré-processando todas as palavras-chave e permitindo buscas instantâneas:

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

Importante: depois de adicionar o índice de texto, você precisa reconstruí-lo para os dados existentes:

ALTER TABLE posts MATERIALIZE INDEX keywords_idx;

Indexação de map

Em um sistema de logging, as solicitações do servidor frequentemente armazenam metadados em pares chave-valor. As equipes de operações precisam pesquisar com eficiência nos logs para depuração, incidentes de segurança e monitoramento. Considere esta tabela de logs:

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

Sem um índice de texto, pesquisar em dados Map exige varreduras completas da tabela:

Encontra todos os logs com limitação de taxa:

SELECT count() FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- varredura lenta em toda a tabela

Encontra todos os logs de um IP específico:

SELECT count() FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- varredura lenta na tabela inteira

À medida que o volume de logs aumenta, essas consultas ficam lentas. A solução é criar um índice de texto para as chaves e os valores do Map. Use mapKeys para criar um índice de texto quando precisar localizar logs por nomes de campos ou tipos de atributos:

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

Use mapValues para criar um índice de texto quando precisar pesquisar no conteúdo em si dos atributos:

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

Importante: após adicionar o índice de texto, você precisa recriá-lo para os dados existentes:

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

Encontre todas as solicitações com taxa limitada:

SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- rápido

Encontre todos os logs de um IP específico:

SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- rápido

Implementação

Layout do índice

Cada índice de texto consiste em duas estruturas de dados (abstratas):

um dicionário que associa cada token a uma lista de postings; e
um conjunto de listas de postings, cada uma representando um conjunto de números de linha.

Como um índice de texto é um skip index, essas estruturas de dados existem logicamente por grânulo de índice. Durante a criação do índice, três arquivos são criados (por part): Arquivo de blocos do dicionário (.dct) Os tokens em um grânulo de índice são ordenados e armazenados em blocos de dicionário de 128 tokens cada (o tamanho do bloco é configurável pelo parâmetro dictionary_block_size). Um arquivo de blocos do dicionário (.dct) contém todos os blocos de dicionário de todos os grânulos de índice em uma part. Arquivo de grânulos de índice (.idx) O arquivo de grânulos de índice contém, para cada bloco de dicionário, o primeiro token do bloco, seu deslocamento relativo no arquivo de blocos do dicionário e um filtro de Bloom para todos os tokens do bloco. Essa estrutura de índice esparso é semelhante ao índice esparso de chave primária) do ClickHouse. O filtro de Bloom permite ignorar blocos de dicionário logo no início se o token procurado não estiver presente em um bloco de dicionário. Arquivo de listas de postings (.pst) As listas de postings de todos os tokens são organizadas sequencialmente no arquivo de listas de postings. Para economizar espaço e ainda permitir operações rápidas de interseção e união, as listas de postings são armazenadas como bitmaps Roaring. Se a cardinalidade de uma lista de postings for menor que 16 (configurável pelo parâmetro max_cardinality_for_embedded_postings), ela é incorporada ao dicionário.

Leitura direta

Certos tipos de consultas de texto podem ser acelerados significativamente por uma otimização chamada “leitura direta”. Mais especificamente, a otimização pode ser aplicada se a consulta SELECT não incluir a coluna de texto na projeção. Exemplo:

SELECT column_a, column_b, ... -- não: column_with_text_index
FROM [...]
WHERE string_search_function(column_with_text_index)

A otimização de leitura direta no ClickHouse responde à consulta exclusivamente usando o índice de texto (isto é, consultas ao índice de texto), sem acessar a coluna de texto subjacente. As consultas ao índice de texto leem relativamente poucos dados e, por isso, são muito mais rápidas do que os skip indexes usuais no ClickHouse (que fazem uma consulta ao skip index, seguida do carregamento e da filtragem dos grânulos restantes). A leitura direta é controlada por duas configurações:

Configuração query_plan_direct_read_from_text_index (padrão: 1), que especifica se a leitura direta está habilitada de modo geral.
Configuração use_skip_indexes_on_data_read (padrão: 1), que é outro pré-requisito para a leitura direta. Observe que, em bancos de dados ClickHouse com compatibility < 25.10, use_skip_indexes_on_data_read fica desabilitada, portanto você precisa aumentar o valor da configuração de compatibility ou definir SET use_skip_indexes_on_data_read = 1 explicitamente.

Além disso, o índice de texto deve estar totalmente materializado para usar a leitura direta (use ALTER TABLE ... MATERIALIZE INDEX para isso). Funções suportadas A otimização de leitura direta oferece suporte às funções hasToken, hasAllTokens e hasAnyTokens. Essas funções também podem ser combinadas com os operadores AND, OR e NOT. A cláusula WHERE também pode conter filtros adicionais que não sejam funções de pesquisa de texto (para colunas de texto ou outras colunas) — nesse caso, a otimização de leitura direta ainda será usada, mas será menos eficaz (ela se aplica apenas às funções de pesquisa de texto compatíveis). Para verificar se uma consulta usa leitura direta, execute a consulta com EXPLAIN PLAN actions = 1. Como exemplo, uma consulta com a leitura direta desabilitada

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

retorna

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

enquanto a mesma consulta é executada com 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;

retorna

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

A segunda saída de EXPLAIN PLAN contém uma coluna virtual __text_index_<index_name>_<function_name>_<id>. Se essa coluna estiver presente, a leitura direta estará sendo usada.

Exemplo: conjunto de dados do Hacker News

Vamos analisar os ganhos de desempenho dos índices de texto em um grande conjunto de dados com muito conteúdo textual. Usaremos 28,7 milhões de linhas de comentários do popular site Hacker News. Aqui está a tabela sem índice de texto:

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);

As 28,7 milhões de linhas estão em um arquivo Parquet no S3 — vamos inseri-las na tabela 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');

Usaremos ALTER TABLE para adicionar um índice de texto à coluna comment e, em seguida, materializá-lo:

-- Adicionar o índice
ALTER TABLE hackernews ADD INDEX comment_idx(comment) TYPE text(tokenizer = splitByNonAlpha);

-- Materializar o índice para dados existentes
ALTER TABLE hackernews MATERIALIZE INDEX comment_idx SETTINGS mutations_sync = 2;

Agora, vamos executar consultas usando as funções hasToken, hasAnyTokens e hasAllTokens. Os exemplos a seguir mostrarão a grande diferença de desempenho entre uma varredura de índice padrão e a otimização de leitura direta.

1. Usando `hasToken`

hasToken verifica se o texto contém um token específico. Vamos procurar pelo token sensível a maiúsculas e minúsculas ‘ClickHouse’. Leitura direta desabilitada (varredura padrão) Por padrão, o ClickHouse usa o skip index para filtrar grânulos e, em seguida, lê os dados da coluna desses grânulos. Podemos simular esse comportamento desabilitando a leitura direta.

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

Leitura direta ativada (leitura rápida do índice) Agora executamos a mesma consulta com a leitura direta ativada (comportamento padrão).

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

A consulta usando leitura direta é mais de 45 vezes mais rápida (0,362s vs 0,008s) e processa significativamente menos dados (9,51 GB vs 3,15 MB) ao ler apenas o índice.

2. Usando `hasAnyTokens`

hasAnyTokens verifica se o texto contém pelo menos um dos tokens informados. Vamos procurar comentários que contenham ‘love’ ou ‘ClickHouse’. Leitura direta desativada (varredura padrão)

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

Leitura direta habilitada (leitura rápida pelo índice)

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

O ganho de velocidade é ainda mais expressivo para esta busca comum com “OR”. A consulta é quase 89 vezes mais rápida (1.329s vs 0.015s) ao evitar a varredura completa da coluna.

3. Usando `hasAllTokens`

hasAllTokens verifica se o texto contém todos os tokens informados. Vamos buscar comentários que contenham tanto ‘love’ quanto ‘ClickHouse’. Leitura direta desativada (varredura padrão) Mesmo com a leitura direta desativada, o skip index padrão continua eficaz. Ele reduz as 28.7M linhas para apenas 147.46K, mas ainda precisa ler 57.03 MB da coluna.

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

Leitura direta ativada (Leitura rápida do índice) A leitura direta responde à consulta com base nos dados do índice, lendo apenas 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

Para esta pesquisa “AND”, a otimização de leitura direta é mais de 26 vezes mais rápida (0.184s vs 0.007s) do que a varredura padrão com skip index.

4. Busca composta: OR, AND, NOT, …

A otimização de leitura direta também se aplica a expressões booleanas compostas. Aqui, faremos uma busca sem diferenciar maiúsculas de minúsculas por ‘ClickHouse’ OR ‘clickhouse’. Leitura direta desabilitada (varredura padrão)

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

Leitura direta ativada (Leitura rápida do índice)

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

Ao combinar os resultados do índice, a consulta com leitura direta fica 34 vezes mais rápida (0,450s vs 0,013s) e evita a leitura de 9,58 GB de dados das colunas. Para este caso específico, hasAnyTokens(comment, ['ClickHouse', 'clickhouse']) seria a sintaxe preferida e mais eficiente.

Ajuste do índice de texto

Atualmente, há caches para os blocos de dicionário desserializados, os cabeçalhos e as listas de postings do índice de texto, para reduzir a E/S. Eles podem ser habilitados por meio das configurações use_text_index_dictionary_cache, use_text_index_header_cache e use_text_index_postings_cache, respectivamente. Por padrão, eles ficam desabilitados. Consulte as configurações de servidor a seguir para configurar o cache.

Configurações do servidor

Configurações de cache de blocos do dicionário do índice de texto

Configuração	Descrição	Padrão
text_index_dictionary_block_cache_policy	Nome da política de cache de blocos do dicionário do índice de texto.	`SLRU`
text_index_dictionary_block_cache_size	Tamanho máximo do cache em bytes.	`1073741824`
text_index_dictionary_block_cache_max_entries	Número máximo de blocos do dicionário desserializados no cache.	`1'000'000`
text_index_dictionary_block_cache_size_ratio	Tamanho da fila protegida no cache de blocos do dicionário do índice de texto em relação ao tamanho total do cache.	`0.5`

Configurações do cache de cabeçalhos

Configuração	Descrição	Padrão
text_index_header_cache_policy	Nome da política do cache de cabeçalhos do índice de texto.	`SLRU`
text_index_header_cache_size	Tamanho máximo do cache em bytes.	`1073741824`
text_index_header_cache_max_entries	Número máximo de cabeçalhos desserializados no cache.	`100'000`
text_index_header_cache_size_ratio	Tamanho da fila protegida no cache de cabeçalhos do índice de texto em relação ao tamanho total do cache.	`0.5`

Configurações do cache das listas de postings

Configuração	Descrição	Padrão
text_index_postings_cache_policy	Nome da política do cache de postings do índice de texto.	`SLRU`
text_index_postings_cache_size	Tamanho máximo do cache em bytes.	`2147483648`
text_index_postings_cache_max_entries	Número máximo de postings desserializados no cache.	`1'000'000`
text_index_postings_cache_size_ratio	Tamanho da fila protegida no cache de postings do índice de texto em relação ao tamanho total do cache.	`0.5`

​Criando um índice de texto

​Usando um índice de texto

​Funções suportadas

​= and !=

​IN and NOT IN

​LIKE, NOT LIKE e match

​startsWith and endsWith

​hasToken and hasTokenOrNull

​hasAnyTokens and hasAllTokens

​has

​mapContains

​operator[]

​Exemplos de suporte a Array e Map no índice de texto.

​Indexação de Array(String)

​Indexação de map

​Implementação

​Layout do índice

​Leitura direta

​Exemplo: conjunto de dados do Hacker News

​1. Usando hasToken

​2. Usando hasAnyTokens

​3. Usando hasAllTokens

​4. Busca composta: OR, AND, NOT, …

​Ajuste do índice de texto

​Configurações do servidor

​Configurações de cache de blocos do dicionário do índice de texto

​Configurações do cache de cabeçalhos

​Configurações do cache das listas de postings

​Conteúdo relacionado

Criando um índice de texto

Usando um índice de texto

Funções suportadas

`=` and `!=`

`IN` and `NOT IN`

`LIKE`, `NOT LIKE` e `match`

`startsWith` and `endsWith`

`hasToken` and `hasTokenOrNull`

`hasAnyTokens` and `hasAllTokens`

`has`

`mapContains`

`operator[]`

Exemplos de suporte a `Array` e `Map` no índice de texto.

Indexação de Array(String)

Indexação de map

Implementação

Layout do índice

Leitura direta

Exemplo: conjunto de dados do Hacker News

1. Usando `hasToken`

2. Usando `hasAnyTokens`

3. Usando `hasAllTokens`

4. Busca composta: OR, AND, NOT, …

Ajuste do índice de texto

Configurações do servidor

Configurações de cache de blocos do dicionário do índice de texto

Configurações do cache de cabeçalhos

Configurações do cache das listas de postings

Conteúdo relacionado