Exemplo prático de otimização de consultas

Este guia aplica duas abordagens de otimização ao conjunto de dados NYC Taxi. Primeiro, reduz a quantidade de dados armazenados e processados ao escolher tipos de coluna mais precisos. Em seguida, introduz uma chave de ordenação que permite ao ClickHouse ignorar dados em consultas seletivas. Cada alteração é medida em relação à mesma referência. Consulte a visão geral da otimização de consultas para conhecer o fluxo de trabalho mais abrangente que este exemplo segue.

Os exemplos usam a tabela nyc_taxi.trips_small_inferred. Crie-a e carregue-a caso ainda não tenha feito isso:

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small_inferred
ORDER BY () EMPTY
AS SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

O arquivo Parquet de origem contém aproximadamente 329 milhões de linhas. Os tempos apresentados neste guia foram registrados em uma implantação e variam conforme os recursos de computação disponíveis. Compare a variação relativa entre as etapas, em vez de esperar durações idênticas.

Ao aplicar este método à sua própria carga de trabalho, use Diagnosticar consultas lentas para identificar um padrão de consulta recorrente e selecionar uma execução representativa antes de alterar a consulta ou o esquema.

O exemplo usa as três etapas a seguir:

1. Execute três consultas independentes de carga de trabalho no esquema inferido para estabelecer uma referência.
2. Crie uma tabela com tipos de coluna mais precisos, carregue os mesmos dados e execute as consultas novamente.
3. Crie outra tabela com o mesmo esquema otimizado e uma chave de ordenação e execute as consultas novamente.

Alterar o esquema e a chave de ordenação em etapas separadas facilita a distinção entre seus efeitos. Abordagens de otimização explica quando considerar essas alterações e como validá-las. Para mais orientações sobre a coleta de medições comparáveis, consulte Isole os gargalos de consultas.

Na mesma sessão do cliente usada para executar a carga de trabalho, desative o cache do sistema de arquivos para dados remotos, o cache de consultas e o cache de condições de consulta:

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;

As três consultas independentes a seguir compõem a carga de trabalho de referência. Execute as três em cada tabela criada nas etapas a seguir. Execute cada consulta várias vezes em condições comparáveis e registre uma duração representativa, como a mediana, além do número de linhas lidas e do pico de uso de memória. Consulte Estabeleça uma referência repetível para conhecer o fluxo de trabalho completo de medição, incluindo como recuperar esses valores de system.query_log.

Esta consulta calcula a duração e a velocidade da viagem antes de obter a distribuição das distâncias das viagens com velocidade superior a 30 milhas por hora:

WITH
    dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,
    (trip_distance / trip_time) * 3600 AS speed_mph
SELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
FROM nyc_taxi.trips_small_inferred
WHERE speed_mph > 30
FORMAT JSON;

Esta consulta calcula o número de corridas, a distância e os valores médios de pagamento do primeiro trimestre de 2009:

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

Esta consulta calcula a duração média das corridas com um ou dois passageiros:

SELECT avg(dateDiff('s', pickup_datetime, dropoff_datetime))
FROM nyc_taxi.trips_small_inferred
WHERE passenger_count = 1 OR passenger_count = 2
FORMAT JSON;

As medições originais foram:

As três consultas leram aproximadamente 329 milhões de linhas, um número próximo ao total de linhas da tabela. Isso indica a possibilidade de melhorar dois aspectos distintos da carga de trabalho: reduzir o custo de processamento das colunas selecionadas e, quando os filtros permitirem, reduzir o número de linhas selecionadas.

A inferência de esquema é uma maneira prática de começar a explorar um conjunto de dados, mas os tipos inferidos podem ser mais amplos ou permissivos do que o necessário para a carga de trabalho. Inspecione os dados antes de alterar o esquema, em vez de presumir que um tipo inferido seja desnecessário.

Uma coluna Nullable armazena uma máscara de valores nulos além de seus valores. Mantenha Nullable quando a distinção entre um valor nulo e o valor padrão do tipo for relevante, mas evite-o em colunas que têm garantia de sempre conter um valor.

Conte os valores nulos nas colunas usadas no esquema de exemplo:

SELECT
    countIf(vendor_id IS NULL) AS vendor_id_nulls,
    countIf(pickup_datetime IS NULL) AS pickup_datetime_nulls,
    countIf(dropoff_datetime IS NULL) AS dropoff_datetime_nulls,
    countIf(passenger_count IS NULL) AS passenger_count_nulls,
    countIf(trip_distance IS NULL) AS trip_distance_nulls,
    countIf(ratecode_id IS NULL) AS ratecode_id_nulls,
    countIf(fare_amount IS NULL) AS fare_amount_nulls,
    countIf(extra IS NULL) AS extra_nulls,
    countIf(mta_tax IS NULL) AS mta_tax_nulls,
    countIf(tip_amount IS NULL) AS tip_amount_nulls,
    countIf(tolls_amount IS NULL) AS tolls_amount_nulls,
    countIf(total_amount IS NULL) AS total_amount_nulls,
    countIf(payment_type IS NULL) AS payment_type_nulls,
    countIf(pickup_location_id IS NULL) AS pickup_location_id_nulls,
    countIf(dropoff_location_id IS NULL) AS dropoff_location_id_nulls
FROM nyc_taxi.trips_small_inferred
FORMAT VERTICAL;

Row 1:
──────
vendor_id_nulls:           0
pickup_datetime_nulls:     0
dropoff_datetime_nulls:    0
passenger_count_nulls:     0
trip_distance_nulls:       0
ratecode_id_nulls:          167200929
fare_amount_nulls:         0
extra_nulls:               0
mta_tax_nulls:             137946731
tip_amount_nulls:          0
tolls_amount_nulls:        0
total_amount_nulls:        0
payment_type_nulls:        69305
pickup_location_id_nulls:  0
dropoff_location_id_nulls: 0

Apenas ratecode_id, mta_tax e payment_type contêm valores nulos neste conjunto de dados. O esquema otimizado mantém Nullable nessas colunas e o remove das demais.

LowCardinality usa codificação de dicionário e pode reduzir o armazenamento e o processamento de colunas com muitos valores repetidos. Verifique o número de valores distintos antes de aplicá-la:

SELECT
    uniq(ratecode_id),
    uniq(pickup_location_id),
    uniq(dropoff_location_id),
    uniq(vendor_id)
FROM nyc_taxi.trips_small_inferred
FORMAT VERTICAL;

Row 1:
──────
uniq(ratecode_id):         6
uniq(pickup_location_id):  260
uniq(dropoff_location_id): 260
uniq(vendor_id):           3

Essas quatro colunas contêm significativamente menos valores distintos do que linhas. São candidatas adequadas para LowCardinality, embora o impacto ainda deva ser medido para a carga de trabalho. Cerca de 10.000 valores distintos é um ponto de partida útil para identificar candidatas, não um limite fixo.

Use o tipo mais específico que preserve com segurança o intervalo e a precisão necessários. Por exemplo, verifique os valores mínimo e máximo das colunas numéricas antes de substituir um Int64 ou Float64 inferido:

SELECT
    min(payment_type),
    max(payment_type),
    min(passenger_count),
    max(passenger_count)
FROM nyc_taxi.trips_small_inferred;

   ┌─min(payment_type)─┬─max(payment_type)─┬─min(passenger_count)─┬─max(passenger_count)─┐
1. │                 1 │                 4 │                    0 │                  255 │
   └───────────────────┴───────────────────┴──────────────────────┴──────────────────────┘

Ambas as colunas inteiras cabem em UInt8, embora passenger_count atinja o valor máximo de 255. O exemplo também usa Float32 para trip_distance e Decimal32 para valores monetários. Todos os valores deste conjunto de dados cabem nos intervalos de destino, e o exemplo aceita a menor precisão de ponto flutuante e a precisão monetária em centavos porque a carga de trabalho compara resultados agregados. Mantenha os tipos de origem mais abrangentes quando forem necessários valores exatos da origem. O exemplo substitui as colunas DateTime64 inferidas por DateTime no mesmo fuso horário UTC, pois as consultas do exemplo não exigem precisão de frações de segundo.

Essas escolhas são específicas deste conjunto de dados. Confirme os requisitos de intervalo, precisão e capacidade de aceitar valores nulos dos dados de produção antes de aplicar as mesmas alterações.

Crie uma tabela sem chave de ordenação para que esta etapa avalie as alterações de esquema de forma independente:

CREATE TABLE nyc_taxi.trips_small_no_pk
(
    vendor_id LowCardinality(String),
    pickup_datetime DateTime('UTC'),
    dropoff_datetime DateTime('UTC'),
    passenger_count UInt8,
    trip_distance Float32,
    ratecode_id LowCardinality(Nullable(String)),
    pickup_location_id LowCardinality(String),
    dropoff_location_id LowCardinality(String),
    payment_type Nullable(UInt8),
    fare_amount Decimal32(2),
    extra Decimal32(2),
    mta_tax Nullable(Decimal32(2)),
    tip_amount Decimal32(2),
    tolls_amount Decimal32(2),
    total_amount Decimal32(2)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO nyc_taxi.trips_small_no_pk
SELECT *
FROM nyc_taxi.trips_small_inferred;

Em cada consulta da carga de trabalho, substitua nyc_taxi.trips_small_inferred por nyc_taxi.trips_small_no_pk e execute novamente as três consultas. O exemplo original registrou os seguintes resultados representativos:

As consultas continuam lendo o mesmo número de linhas, mas o esquema otimizado reduz a quantidade de dados representada por essas linhas. Assim, a duração da consulta e o pico de memória melhoram sem alterar a seleção de dados.

Compare o tamanho em disco das duas tabelas:

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    sum(rows) AS rows
FROM system.parts
WHERE active = 1
  AND database = 'nyc_taxi'
  AND table IN ('trips_small_inferred', 'trips_small_no_pk')
GROUP BY database, table
ORDER BY sum(data_compressed_bytes) DESC;

   ┌─table────────────────┬─compressed─┬─uncompressed─┬──────rows─┐
1. │ trips_small_inferred │ 7.38 GiB   │ 37.41 GiB    │ 329044175 │
2. │ trips_small_no_pk    │ 4.89 GiB   │ 15.31 GiB    │ 329044175 │
   └──────────────────────┴────────────┴──────────────┴───────────┘

Para este conjunto de dados, o esquema otimizado reduz o armazenamento compactado em aproximadamente 34%, de 7,38 GiB para 4,89 GiB.

Na família MergeTree, a chave de ordenação determina como as linhas são organizadas em disco. O ClickHouse cria um índice primário esparso com base nessa ordenação, o que permite ignorar grânulos que não podem atender aos filtros de uma consulta. Diferentemente de uma chave primária em muitos bancos de dados transacionais, ela não impõe exclusividade.

A chave de ordenação deve refletir os filtros usados em consultas recorrentes importantes. A ordem das colunas é importante: uma chave é mais eficaz quando a consulta filtra por um prefixo útil. Colunas com menor cardinalidade às vezes são boas opções para as primeiras posições da chave quando são filtradas com frequência, e um componente de tempo costuma ser útil para cargas de trabalho baseadas em tempo. Para orientações detalhadas sobre a seleção, consulte Escolhendo uma chave primária.

Neste exemplo, use (passenger_count, pickup_datetime, dropoff_datetime). passenger_count tem poucos valores distintos e é usado no filtro de contagem de passageiros, enquanto pickup_datetime é usado na agregação por intervalo de datas. Embora pickup_datetime não seja a primeira coluna, o ClickHouse ainda pode usar valores de colunas-chave posteriores para excluir dados quando a coluna inicial não é restringida. Em geral, filtrar por um prefixo útil da chave de ordenação proporciona uma poda mais eficiente.

Crie uma tabela com o mesmo esquema otimizado usado na etapa anterior. Altere apenas a chave de ordenação:

CREATE TABLE nyc_taxi.trips_small_pk
(
    vendor_id LowCardinality(String),
    pickup_datetime DateTime('UTC'),
    dropoff_datetime DateTime('UTC'),
    passenger_count UInt8,
    trip_distance Float32,
    ratecode_id LowCardinality(Nullable(String)),
    pickup_location_id LowCardinality(String),
    dropoff_location_id LowCardinality(String),
    payment_type Nullable(UInt8),
    fare_amount Decimal32(2),
    extra Decimal32(2),
    mta_tax Nullable(Decimal32(2)),
    tip_amount Decimal32(2),
    tolls_amount Decimal32(2),
    total_amount Decimal32(2)
)
ENGINE = MergeTree
ORDER BY (passenger_count, pickup_datetime, dropoff_datetime);

INSERT INTO nyc_taxi.trips_small_pk
SELECT *
FROM nyc_taxi.trips_small_no_pk;

Em cada consulta da carga de trabalho, substitua o nome da tabela por nyc_taxi.trips_small_pk e execute novamente as três consultas.

O guia original registrou as seguintes medições nas três etapas:

A otimização do esquema reduz o armazenamento e torna os valores selecionados mais eficientes de processar. A chave de ordenação proporciona a maior melhoria adicional para a agregação por intervalo de datas, pois o ClickHouse pode ignorar grânulos fora desse intervalo. O filtro por número de passageiros também lê menos linhas porque filtra pela primeira coluna-chave. O filtro de velocidade calculada ainda lê a tabela inteira porque sua condição de filtragem é derivada de pickup_datetime, dropoff_datetime e trip_distance, e não de um prefixo útil da chave de ordenação.

Inspecione a agregação por intervalo de datas com EXPLAIN indexes = 1:

EXPLAIN indexes = 1
SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_pk
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC
SETTINGS
    use_query_condition_cache = 0,
    use_skip_indexes_on_data_read = 0;

ReadFromMergeTree (nyc_taxi.trips_small_pk)
Indexes:
  PrimaryKey
    Keys:
      pickup_datetime
    Condition: and((pickup_datetime in (-Inf, 1238543999]), (pickup_datetime in [1230768000, +Inf)))
    Parts: 9/9
    Granules: 5061/40167

O índice primário seleciona 5.061 dos 40.167 grânulos. Essa redução faz com que a agregação por intervalo de datas processe 41,46 milhões de linhas, em vez do total de 329,04 milhões.

Use a mesma sequência para sua própria carga de trabalho:

1. Registre a duração de referência, as linhas e os bytes lidos e o pico de memória.
2. Verifique se as colunas selecionadas usam tipos desnecessariamente amplos ou permissivos.
3. Aplique e meça alterações de esquema sem alterar o layout dos dados.
4. Teste uma chave de ordenação com base nos filtros usados por consultas recorrentes importantes.
5. Compare os dados selecionados com EXPLAIN indexes = 1 e execute novamente as consultas de referência em condições comparáveis.

Não presuma que os tipos ou a chave de ordenação deste exemplo serão adequados para outro conjunto de dados. Use os valores observados e os filtros das consultas para tomar essas decisões.

Volte para Abordagens de otimização para avaliar projeções, visões materializadas, índices de salto de dados ou pré-computação quando alterações no esquema e na chave de ordenação não resolverem o gargalo identificado.