Planilha 2: projeto da chave de ordenação (ORDER BY)

Tempo estimado: 20–25 minutos Referência: Mecanismos MergeTree — seção sobre projeto do ORDER BY

Conceito

A cláusula ORDER BY do ClickHouse não é decorativa. Ela define o índice primário — um índice esparso no nível dos blocos que permite ao ClickHouse ignorar blocos irrelevantes de dados ao avaliar cláusulas WHERE. Também determina a ordem física dos dados dentro das partes, o que afeta a compressão.

Um ORDER BY incorreto = consultas lentas + armazenamento desperdiçado. Um ORDER BY que começa por UUID impede o descarte de blocos em qualquer consulta analítica (UUIDs são aleatórios; não há prefixo ordenável). Um ORDER BY que começa pela data permite que consultas filtradas por data ignorem a maior parte da tabela.

Três regras para projetar o ORDER BY

Regra 1: derive a partir dos filtros das consultas, não do esquema de origem. Examine as colunas em WHERE, GROUP BY e JOIN nas consultas mais frequentes. As colunas mais filtradas provavelmente devem aparecer em ORDER BY (desde que a cardinalidade não seja alta demais). A chave primária da tabela de origem, caso exista, normalmente é irrelevante.

Regra 2: baixa cardinalidade primeiro, alta cardinalidade por último. O índice primário do ClickHouse tem uma entrada a cada aproximadamente 8192 linhas (um grânulo). Colunas de baixa cardinalidade (por exemplo, toStartOfMonth(date) = aproximadamente 48 valores distintos em 4 anos) agrupam muitas linhas; o índice pode ignorar grânulos inteiros. Colunas de alta cardinalidade (por exemplo, trip_id = 50 milhões de valores distintos) são únicas por linha; colocá-las primeiro impede o índice de descartar qualquer coisa. Essa ordenação é um padrão, não uma substituição da regra 1: uma coluna filtrada por intervalo que elimine a maioria das linhas ainda pode merecer a primeira posição, mesmo diante de outra coluna de cardinalidade menor filtrada apenas por igualdade.

Regra 3: em ReplacingMergeTree, termine com o identificador exclusivo da linha. A chave de deduplicação é a tupla ORDER BY completa. Se trip_id estiver ausente de ORDER BY, duas corridas diferentes com o mesmo pickup_at e nenhuma outra coluna serão tratadas como duplicatas. Coloque trip_id por último para garantir exclusividade sem prejudicar o desempenho do índice.

Exercício: análise da carga de consultas

Antes de projetar as chaves de ordenação, descubra quais colunas as consultas realmente filtram. O laboratório de táxis de NYC tem 7 consultas representativas; para cada uma, escolha a coluna de filtro que elimina mais linhas.

Exercício: estimativa de cardinalidade

Para cada coluna candidata a ORDER BY, estime a cardinalidade no conjunto de 50 milhões de linhas ao longo de 4 anos. A maior parte da tabela abaixo contém dados de referência. As duas células abertas correspondem à estimativa de valores distintos do próprio pickup_at e à classe de cardinalidade. Quatro anos representam aproximadamente 126 milhões de segundos (e apenas cerca de 2,1 milhões de minutos), o produtor registra cada corrida pelo relógio real e PICKUP_AT é armazenado como DateTime64(3, 'UTC'). Calcule quantos desses intervalos podem ser ocupados por 50 milhões de corridas antes de escolher uma faixa.

Exercício: projeto da chave de ordenação

Com base na análise da carga de consultas e nas estimativas de cardinalidade, proponha um ORDER BY para trips_raw, fact_trips e agg_hourly_zone_trips. Lembretes:

• Baixa cardinalidade primeiro → maior descarte de blocos.
• Colunas que aparecem em WHERE/GROUP BY em várias consultas → inclua-as.
• Para tabelas ReplacingMergeTree → termine com o identificador exclusivo da linha.
• Não inclua colunas que nunca são filtradas.

Depois que todas as tabelas estiverem preenchidas, responda às questões de raciocínio e reflexão.

Loading worksheet...

Transferência para migration-plan.md

Depois de preencher esta planilha, copie suas decisões de ORDER BY para a seção 4 de migration-plan.md e marque:

- [ ] Sort key design: completed