Desempenho de consultas - séries temporais

Após otimizar o armazenamento, o próximo passo é melhorar o desempenho das consultas. Esta seção aborda duas técnicas principais: otimizar as chaves ORDER BY e usar visões materializadas. Veremos como essas abordagens podem reduzir o tempo das consultas de segundos para milissegundos.

Otimize as chaves `ORDER BY`

Antes de tentar outras otimizações, você deve otimizar as chaves ORDER BY para garantir que o ClickHouse produza os resultados mais rápidos possíveis. A escolha da chave ORDER BY certa depende em grande parte das consultas que você vai executar. Suponha que a maioria das nossas consultas filtre pelas colunas project e subproject. Nesse caso, é uma boa ideia adicioná-las à chave ORDER BY — assim como a coluna time, já que também fazemos consultas com base no tempo. Vamos criar outra versão da tabela que tenha os mesmos tipos de coluna de wikistat, mas seja ordenada por (project, subproject, time).

CREATE TABLE wikistat_project_subproject
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (project, subproject, time);

Vamos agora comparar várias consultas para ter uma ideia de quanto a expressão da nossa chave ORDER BY é essencial para o desempenho. Observe que não aplicamos nossas otimizações anteriores de tipos de dados e codec, portanto quaisquer diferenças no desempenho das consultas se devem apenas à ordem de ordenação.

Consulta	`(time)`	`(project, subproject, time)`
`SELECT project, sum(hits) AS h FROM wikistat GROUP BY project ORDER BY h DESC LIMIT 10;`	2.381 sec	1.660 sec
`SELECT subproject, sum(hits) AS h FROM wikistat WHERE project = 'it' GROUP BY subproject ORDER BY h DESC LIMIT 10;`	2.148 sec	0.058 sec
`SELECT toStartOfMonth(time) AS m, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY m ORDER BY m DESC LIMIT 10;`	2.192 sec	0.012 sec
`SELECT path, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY path ORDER BY h DESC LIMIT 10;`	2.968 sec	0.010 sec

Visões materializadas

Outra opção é usar visões materializadas para agregar e armazenar os resultados de consultas usadas com frequência. Esses resultados podem ser consultados em vez dos da tabela original. Suponha que, no nosso caso, a consulta a seguir seja executada com bastante frequência:

SELECT path, SUM(hits) AS v
FROM wikistat
WHERE toStartOfMonth(time) = '2015-05-01'
GROUP BY path
ORDER BY v DESC
LIMIT 10

┌─path──────────────────┬────────v─┐
│ -                     │ 89650862 │
│ Angelsberg            │ 19165753 │
│ Ana_Sayfa             │  6368793 │
│ Academy_Awards        │  4901276 │
│ Accueil_(homonymie)   │  3805097 │
│ Adolf_Hitler          │  2549835 │
│ 2015_in_spaceflight   │  2077164 │
│ Albert_Einstein       │  1619320 │
│ 19_Kids_and_Counting  │  1430968 │
│ 2015_Nepal_earthquake │  1406422 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 2.285 sec. Processed 231.41 million rows, 9.22 GB (101.26 million rows/s., 4.03 GB/s.)
Peak memory usage: 1.50 GiB.

Criar visão materializada

Podemos criar a seguinte visão materializada:

CREATE TABLE wikistat_top
(
    `path` String,
    `month` Date,
    hits UInt64
)
ENGINE = SummingMergeTree
ORDER BY (month, hits);

CREATE MATERIALIZED VIEW wikistat_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

Carga retroativa da tabela de destino

Esta tabela de destino só será populada quando novos registros forem inseridos na tabela wikistat, portanto precisamos fazer uma carga retroativa. A maneira mais fácil de fazer isso é usar uma instrução INSERT INTO SELECT para inserir diretamente na tabela de destino da visão materializada usando a consulta SELECT da visão (transformação):

INSERT INTO wikistat_top
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

Dependendo da cardinalidade do conjunto de dados brutos (temos 1 bilhão de linhas!), essa pode ser uma abordagem que consome muita memória. Como alternativa, você pode usar uma variante que requer o mínimo de memória:

Criar uma tabela temporária com o engine de tabela Null
Conectar uma cópia da visão materializada normalmente usada a essa tabela temporária
Usar uma consulta INSERT INTO SELECT para copiar todos os dados do conjunto de dados brutos para essa tabela temporária
Excluir a tabela temporária e a visão materializada temporária.

Com essa abordagem, as linhas do conjunto de dados brutos são copiadas bloco a bloco para a tabela temporária (que não armazena nenhuma dessas linhas) e, para cada bloco de linhas, um estado parcial é calculado e gravado na tabela de destino, onde esses estados são mesclados incrementalmente em segundo plano.

CREATE TABLE wikistat_backfill
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = Null;

Em seguida, criaremos uma visão materializada para ler a partir de wikistat_backfill e gravar em wikistat_top

CREATE MATERIALIZED VIEW wikistat_backfill_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat_backfill
GROUP BY path, month;

E, por fim, vamos popular wikistat_backfill a partir da tabela wikistat inicial:

INSERT INTO wikistat_backfill
SELECT * 
FROM wikistat;

Quando essa consulta for concluída, podemos excluir a tabela de backfill e a visão materializada:

DROP VIEW wikistat_backfill_top_mv;
DROP TABLE wikistat_backfill;

Agora podemos fazer consultas na visão materializada em vez da tabela original:

SELECT path, sum(hits) AS hits
FROM wikistat_top
WHERE month = '2015-05-01'
GROUP BY ALL
ORDER BY hits DESC
LIMIT 10;

┌─path──────────────────┬─────hits─┐
│ -                     │ 89543168 │
│ Angelsberg            │  7047863 │
│ Ana_Sayfa             │  5923985 │
│ Academy_Awards        │  4497264 │
│ Accueil_(homonymie)   │  2522074 │
│ 2015_in_spaceflight   │  2050098 │
│ Adolf_Hitler          │  1559520 │
│ 19_Kids_and_Counting  │   813275 │
│ Andrzej_Duda          │   796156 │
│ 2015_Nepal_earthquake │   726327 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 0.004 sec.

A melhora de desempenho aqui é impressionante. Antes, levava pouco mais de 2 segundos para calcular a resposta dessa consulta, e agora leva apenas 4 milissegundos.

​Otimize as chaves ORDER BY

​Visões materializadas

​Criar visão materializada

​Carga retroativa da tabela de destino

Otimize as chaves `ORDER BY`

Visões materializadas

Criar visão materializada

Carga retroativa da tabela de destino