Use índices de omissão de dados quando apropriado

Os índices de omissão de dados devem ser considerados quando as melhores práticas anteriores tiverem sido seguidas, ou seja, quando os tipos estiverem otimizados, uma boa chave primária tiver sido selecionada e visões materializadas tiverem sido aproveitadas. Se você ainda não conhece índices de omissão, este guia é um bom ponto de partida. Esses tipos de índices podem ser usados para acelerar o desempenho das consultas, desde que sejam usados com cuidado e com entendimento de como funcionam. O ClickHouse fornece um mecanismo poderoso chamado índices de omissão de dados que pode reduzir drasticamente a quantidade de dados examinados durante a execução de consultas — principalmente quando a chave primária não é útil para uma condição de filtro específica. Ao contrário dos bancos de dados tradicionais, que dependem de índices secundários baseados em linhas (como B-trees), o ClickHouse é um banco de dados colunar e não armazena localizações de linhas de uma forma que suporte esse tipo de estrutura. Em vez disso, ele usa índices de omissão, que ajudam a evitar a leitura de blocos de dados que certamente não correspondem às condições de filtragem de uma consulta. Os índices de omissão funcionam armazenando metadados sobre blocos de dados — como valores mínimos/máximos, conjuntos de valores ou representações de filtro de Bloom — e usando esses metadados durante a execução da consulta para determinar quais blocos de dados podem ser totalmente ignorados. Eles se aplicam apenas à família MergeTree de motores de tabela e são definidos usando uma expressão, um tipo de índice, um nome e uma granularidade que define o tamanho de cada bloco indexado. Esses índices são armazenados junto com os dados da tabela e são consultados quando o filtro da consulta corresponde à expressão do índice. Existem vários tipos de índices de omissão de dados, cada um adequado a diferentes tipos de consultas e distribuições de dados:

minmax: Rastreia o valor mínimo e máximo de uma expressão por bloco. Ideal para consultas de intervalo em dados pouco ordenados.
set(N): Rastreia um conjunto de valores até um tamanho N especificado para cada bloco. Eficaz em colunas com baixa cardinalidade por bloco.
text: Cria um índice invertido sobre dados de string tokenizados, permitindo full-text search eficiente e determinística. Recomendado para linguagem natural ou colunas grandes de texto livre, em que são necessárias busca precisa de tokens e busca escalável com múltiplos termos, em vez de abordagens aproximadas baseadas em filtro de Bloom.
bloom_filter: Determina probabilisticamente se um valor existe em um bloco, permitindo filtragem aproximada rápida para pertencimento a conjuntos. Eficaz para otimizar consultas que procuram a “agulha no palheiro”, quando é necessária uma correspondência positiva.
tokenbf_v1 / ngrambf_v1: (Obsoleto) Variantes especializadas de filtro de Bloom projetadas para pesquisar tokens ou sequências de caracteres em strings — particularmente úteis para dados de logs ou casos de uso de busca em texto. Obsoletas em versões do ClickHouse >= 26.2 em favor de text indexes.

Embora poderosos, os índices de omissão devem ser usados com cuidado. Eles só trazem benefícios quando eliminam uma quantidade significativa de blocos de dados e podem até introduzir sobrecarga se a consulta ou a estrutura dos dados não estiver alinhada. Se existir até mesmo um único valor correspondente em um bloco, esse bloco inteiro ainda precisará ser lido. O uso eficaz de índices de omissão frequentemente depende de uma forte correlação entre a coluna indexada e a chave primária da tabela, ou da inserção de dados de uma forma que agrupe valores semelhantes. Em geral, os índices de omissão de dados são mais bem aplicados depois de garantir um bom design de chave primária e a otimização dos tipos. Eles são particularmente úteis para:

Colunas com alta cardinalidade geral, mas baixa cardinalidade dentro de um bloco.
Valores raros que são críticos para busca (por exemplo, códigos de erro, IDs específicos).
Casos em que a filtragem ocorre em colunas que não fazem parte da chave primária, com distribuição localizada.

Sempre:

Teste índices de omissão em dados reais com consultas realistas. Experimente diferentes tipos de índice e valores de granularidade.
Avalie o impacto deles usando ferramentas como send_logs_level=‘trace’ e EXPLAIN indexes=1 para ver a eficácia do índice.
Sempre avalie o tamanho de um índice e como ele é impactado pela granularidade. Reduzir o tamanho da granularidade frequentemente melhora o desempenho até certo ponto, resultando em mais grânulos sendo filtrados e precisando ser examinados. No entanto, à medida que o tamanho do índice aumenta com menor granularidade, o desempenho também pode se degradar. Meça o desempenho e o tamanho do índice para vários níveis de granularidade. Isso é particularmente pertinente em índices de filtro de Bloom.

Quando usados adequadamente, os índices de omissão podem proporcionar um ganho substancial de desempenho — quando usados sem critério, podem adicionar custo desnecessário. Para um guia mais detalhado sobre Índices de Omissão de Dados, veja aqui.

Exemplo

Considere a tabela otimizada a seguir. Ela contém dados do Stack Overflow com uma linha por publicação.

CREATE TABLE stackoverflow.posts
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
  `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime64(3, 'UTC'),
  `Score` Int32,
  `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  `LastActivityDate` DateTime64(3, 'UTC'),
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense` LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime64(3, 'UTC'),
  `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate))

Esta tabela é otimizada para consultas que filtram e agregam por tipo de post e data. Suponha que quiséssemos contar o número de posts com mais de 10.000.000 de visualizações publicados após 2009.

SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

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

1 row in set. Elapsed: 0.720 sec. Processed 59.55 million rows, 230.23 MB (82.66 million rows/s., 319.56 MB/s.)

Esta consulta consegue excluir algumas das linhas (e grânulos) usando o índice primário. No entanto, a maioria das linhas ainda precisa ser lida, conforme indicado pela resposta acima e pelo seguinte EXPLAIN indexes = 1:

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)
LIMIT 1

┌─explain──────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                        │
│   Limit (preliminary LIMIT (without OFFSET))                     │
│     Aggregating                                                  │
│       Expression (Before GROUP BY)                               │
│         Expression                                               │
│           ReadFromMergeTree (stackoverflow.posts)                │
│           Indexes:                                               │
│             MinMax                                               │
│               Keys:                                              │
│                 CreationDate                                     │
│               Condition: (CreationDate in ('1230768000', +Inf))  │
│               Parts: 123/128                                     │
│               Granules: 8513/8545                                │
│             Partition                                            │
│               Keys:                                              │
│                 toYear(CreationDate)                             │
│               Condition: (toYear(CreationDate) in [2009, +Inf))  │
│               Parts: 123/123                                     │
│               Granules: 8513/8513                                │
│             PrimaryKey                                           │
│               Keys:                                              │
│                 toDate(CreationDate)                             │
│               Condition: (toDate(CreationDate) in [14245, +Inf)) │
│               Parts: 123/123                                     │
│               Granules: 8513/8513                                │
└──────────────────────────────────────────────────────────────────┘

25 rows in set. Elapsed: 0.070 sec.

Uma análise simples mostra que ViewCount é correlacionado com CreationDate (uma chave primária), como era de se esperar — quanto mais tempo uma postagem existe, mais tempo ela tem para ser visualizada.

SELECT toDate(CreationDate) AS day, avg(ViewCount) AS view_count FROM stackoverflow.posts WHERE day > '2009-01-01'  GROUP BY day

Isso faz dele, portanto, uma escolha lógica para um índice de omissão de dados. Dado o tipo numérico, um índice minmax faz sentido. Adicionamos um índice com os seguintes comandos ALTER TABLE — primeiro o adicionamos e, em seguida, “o materializamos”.

ALTER TABLE stackoverflow.posts
  (ADD INDEX view_count_idx ViewCount TYPE minmax GRANULARITY 1);

ALTER TABLE stackoverflow.posts MATERIALIZE INDEX view_count_idx;

Este índice também poderia ter sido adicionado durante a criação inicial da tabela. O esquema com o índice minmax definido como parte do DDL:

CREATE TABLE stackoverflow.posts
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
  `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime64(3, 'UTC'),
  `Score` Int32,
  `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  `LastActivityDate` DateTime64(3, 'UTC'),
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense` LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime64(3, 'UTC'),
  `ClosedDate` DateTime64(3, 'UTC'),
  INDEX view_count_idx ViewCount TYPE minmax GRANULARITY 1 --índice aqui
)
ENGINE = MergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate))

A animação a seguir ilustra como nosso índice minmax de omissão é criado para a tabela de exemplo, registrando os valores mínimo e máximo de ViewCount para cada bloco de linhas (grânulo) da tabela: Ao repetir a consulta anterior, vemos melhorias significativas no desempenho. Observe o número reduzido de linhas analisadas:

SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

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

1 row in set. Elapsed: 0.012 sec. Processed 39.11 thousand rows, 321.39 KB (3.40 million rows/s., 27.93 MB/s.)

Um EXPLAIN indexes = 1 confirma que o índice está sendo usado.

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts
WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000)

┌─explain────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                          │
│   Aggregating                                                      │
│     Expression (Before GROUP BY)                                   │
│       Expression                                                   │
│         ReadFromMergeTree (stackoverflow.posts)                    │
│         Indexes:                                                   │
│           MinMax                                                   │
│             Keys:                                                  │
│               CreationDate                                         │
│             Condition: (CreationDate in ('1230768000', +Inf))      │
│             Parts: 123/128                                         │
│             Granules: 8513/8545                                    │
│           Partition                                                │
│             Keys:                                                  │
│               toYear(CreationDate)                                 │
│             Condition: (toYear(CreationDate) in [2009, +Inf))      │
│             Parts: 123/123                                         │
│             Granules: 8513/8513                                    │
│           PrimaryKey                                               │
│             Keys:                                                  │
│               toDate(CreationDate)                                 │
│             Condition: (toDate(CreationDate) in [14245, +Inf))     │
│             Parts: 123/123                                         │
│             Granules: 8513/8513                                    │
│           Skip                                                     │
│             Name: view_count_idx                                   │
│             Description: minmax GRANULARITY 1                      │
│             Parts: 5/123                                           │
│             Granules: 23/8513                                      │
└────────────────────────────────────────────────────────────────────┘

29 rows in set. Elapsed: 0.211 sec.

Também mostramos uma animação de como o índice minmax de omissão descarta todos os blocos de linhas que não podem conter correspondências para o predicado ViewCount > 10,000,000 em nossa consulta de exemplo:

​Exemplo

​Documentação relacionada

Exemplo

Documentação relacionada