Como escolher uma chave primária - ClickHouse Documentation

Usamos os termos “chave de ordenação” e “chave primária” de forma intercambiável nesta página. Em termos estritos, eles diferem no ClickHouse, mas, para os fins deste documento, os leitores podem usá-los como sinônimos, com a chave de ordenação se referindo às colunas especificadas no ORDER BY da tabela.

Observe que a chave primária no ClickHouse funciona de forma muito diferente do que em bancos de dados OLTP, como o Postgres, onde termos semelhantes são mais familiares. Escolher uma chave primária eficaz no ClickHouse é crucial para o desempenho das consultas e a eficiência de armazenamento. O ClickHouse organiza os dados em partes, cada uma contendo seu próprio índice primário esparso. Esse índice acelera significativamente as consultas ao reduzir o volume de dados varridos. Além disso, como a chave primária determina a ordem física dos dados em disco, ela afeta diretamente a eficiência da compressão. Dados ordenados de forma ideal são comprimidos com mais eficácia, o que melhora ainda mais o desempenho ao reduzir a E/S.

Ao selecionar uma chave de ordenação, priorize colunas usadas com frequência em filtros de consulta (ou seja, na cláusula WHERE), especialmente aquelas que excluem grandes quantidades de linhas.
Colunas com alta correlação com outros dados da tabela também são vantajosas, pois o armazenamento contíguo melhora as taxas de compressão e a eficiência de memória durante operações GROUP BY e ORDER BY.

Algumas regras simples podem ajudar na escolha de uma chave de ordenação. Os critérios a seguir às vezes podem entrar em conflito, então considere-os na ordem apresentada. Você pode identificar várias chaves com esse processo, mas 4 a 5 normalmente são suficientes:

ImportanteAs chaves de ordenação devem ser definidas na criação da tabela e não podem ser adicionadas depois. É possível adicionar ordenação extra a uma tabela após (ou antes da) inserção de dados por meio de um recurso conhecido como projeções. Esteja ciente de que isso resulta em duplicação de dados. Mais detalhes aqui.

Exemplo

Considere a tabela posts_unordered a seguir. Ela contém uma linha para cada post do Stack Overflow. Esta tabela não tem chave primária, como indicado por ORDER BY tuple().

CREATE TABLE posts_unordered
(
  `Id` Int32,
  `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 
  'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
  `AcceptedAnswerId` UInt32,
  `CreationDate` DateTime,
  `Score` Int32,
  `ViewCount` UInt32,
  `Body` String,
  `OwnerUserId` Int32,
  `OwnerDisplayName` String,
  `LastEditorUserId` Int32,
  `LastEditorDisplayName` String,
  `LastEditDate` DateTime,
  `LastActivityDate` DateTime,
  `Title` String,
  `Tags` String,
  `AnswerCount` UInt16,
  `CommentCount` UInt8,
  `FavoriteCount` UInt8,
  `ContentLicense`LowCardinality(String),
  `ParentId` String,
  `CommunityOwnedDate` DateTime,
  `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()

Suponha que um usuário queira calcular o número de perguntas submetidas após 2024, sendo esse o padrão de acesso mais comum.

SELECT count()
FROM stackoverflow.posts_unordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')

┌─count()─┐
│  192611 │
└─────────┘
1 row in set. Elapsed: 0.055 sec. Processed 59.82 million rows, 361.34 MB (1.09 billion rows/s., 6.61 GB/s.)

Observe o número de linhas e bytes lidos por esta consulta. Sem uma chave primária, as consultas precisam varrer todo o conjunto de dados. O uso de EXPLAIN indexes=1 confirma uma varredura completa da tabela devido à ausência de indexação.

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts_unordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')

┌─explain───────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                 │
│   Aggregating                                             │
│     Expression (Before GROUP BY)                          │
│       Expression                                          │
│         ReadFromMergeTree (stackoverflow.posts_unordered) │
└───────────────────────────────────────────────────────────┘

5 rows in set. Elapsed: 0.003 sec.

Suponha que uma tabela posts_ordered, contendo os mesmos dados, seja definida com ORDER BY como (PostTypeId, toDate(CreationDate)), ou seja:

CREATE TABLE posts_ordered
(
  `Id` Int32,
  `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 
  'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
...
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate))

PostTypeId tem cardinalidade 8 e representa a escolha lógica para a primeira entrada da nossa chave de ordenação. Como a filtragem com granularidade de data provavelmente será suficiente (e ainda beneficiará filtros de data e hora), usamos toDate(CreationDate) como o 2º componente da nossa chave. Isso também resultará em um índice menor, já que uma data pode ser representada com 16 bits, o que acelera a filtragem. A animação a seguir mostra como um índice primário esparso otimizado é criado para a tabela Posts do Stack Overflow. Em vez de indexar linhas individuais, o índice é aplicado a blocos de linhas: Se a mesma consulta for repetida em uma tabela com esta chave de ordenação:

SELECT count()
FROM stackoverflow.posts_ordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')

┌─count()─┐
│  192611 │
└─────────┘
1 row in set. Elapsed: 0.013 sec. Processed 196.53 thousand rows, 1.77 MB (14.64 million rows/s., 131.78 MB/s.)

Esta consulta agora aproveita a indexação esparsa, reduzindo significativamente a quantidade de dados lidos e tornando o tempo de execução 4x mais rápido — observe a redução no número de linhas e bytes lidos. O uso do índice pode ser confirmado com EXPLAIN indexes=1.

EXPLAIN indexes = 1
SELECT count()
FROM stackoverflow.posts_ordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')

┌─explain─────────────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection))                                                   │
│   Aggregating                                                                               │
│     Expression (Before GROUP BY)                                                            │
│       Expression                                                                            │
│         ReadFromMergeTree (stackoverflow.posts_ordered)                                     │
│         Indexes:                                                                            │
│           PrimaryKey                                                                        │
│             Keys:                                                                           │
│               PostTypeId                                                                    │
│               toDate(CreationDate)                                                          │
│             Condition: and((PostTypeId in [1, 1]), (toDate(CreationDate) in [19723, +Inf))) │
│             Parts: 14/14                                                                    │
│             Granules: 39/7578                                                               │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

13 rows in set. Elapsed: 0.004 sec.

Além disso, visualizamos como o índice esparso descarta todos os blocos de linhas que não podem conter correspondências para nossa consulta de exemplo:

Todas as colunas de uma tabela serão ordenadas com base no valor da chave de ordenação especificada, independentemente de estarem incluídas na própria chave. Por exemplo, se CreationDate for usada como chave, a ordem dos valores em todas as outras colunas corresponderá à ordem dos valores na coluna CreationDate. É possível especificar várias chaves de ordenação — nesse caso, a ordenação seguirá a mesma semântica de uma cláusula ORDER BY em uma consulta SELECT.

Um guia avançado completo sobre como escolher chaves primárias pode ser encontrado aqui. Para entender mais profundamente como as chaves de ordenação melhoram a compressão e otimizam ainda mais o armazenamento, consulte os guias oficiais sobre Compressão no ClickHouse e Codecs de compressão de colunas.

​Exemplo

Exemplo