A instrução de exclusão leve

A exclusão leve simplifica o processo de exclusão de dados do banco de dados.

A instrução de exclusão leve remove linhas da tabela [db.]table que correspondem à expressão expr. Ela está disponível apenas para a família de mecanismos de tabela *MergeTree.

DELETE FROM [db.]table [ON CLUSTER cluster] [IN PARTITION partition_expr] WHERE expr;

Ele é chamado de “exclusão leve” para diferenciá-lo do comando ALTER TABLE … DELETE, que é um processo mais pesado.

​

Exemplos

-- Exclui todas as linhas da tabela `hits` onde a coluna `Title` contém o texto `hello`
DELETE FROM hits WHERE Title LIKE '%hello%';

​

A exclusão leve não exclui dados imediatamente

A exclusão leve é implementada como uma mutação que marca linhas como excluídas, mas não as remove fisicamente de imediato.

Por padrão, as instruções DELETE aguardam até que a marcação das linhas como excluídas seja concluída antes de retornar. Isso pode levar bastante tempo se o volume de dados for grande. Como alternativa, você pode executá-lo de forma assíncrona em segundo plano usando a configuração lightweight_deletes_sync. Se ela estiver desabilitada, a instrução DELETE retornará imediatamente, mas os dados ainda poderão ficar visíveis para consultas até que a mutação em segundo plano seja concluída.

A mutação não exclui fisicamente as linhas que foram marcadas como excluídas; isso só acontece durante o próximo merge. Como resultado, é possível que, por um período indefinido, os dados não sejam de fato removidos do armazenamento e apenas fiquem marcados como excluídos.

Se você precisar garantir que seus dados sejam excluídos do armazenamento em um prazo previsível, considere usar a configuração de tabela min_age_to_force_merge_seconds. Ou você pode usar o comando ALTER TABLE … DELETE. Observe que excluir dados usando ALTER TABLE ... DELETE pode consumir recursos significativos, pois recria todas as partes afetadas.

​

Exclusão de grandes volumes de dados

Exclusões em grande volume podem afetar negativamente o desempenho do ClickHouse. Se você estiver tentando excluir todas as linhas de uma tabela, considere usar o comando TRUNCATE TABLE.

Se você espera realizar exclusões frequentes, considere usar uma chave de particionamento personalizada. Assim, você poderá usar o comando ALTER TABLE ... DROP PARTITION para remover rapidamente todas as linhas associadas a essa partição.

​

Limitações da exclusão leve

​

Exclusões leves com projeções

Por padrão, DELETE não funciona em tabelas com projeções. Isso ocorre porque linhas de uma projeção podem ser afetadas por uma operação DELETE. No entanto, há uma configuração do MergeTree lightweight_mutation_projection_mode para alterar esse comportamento.

​

Considerações de desempenho ao usar exclusão leve

Excluir grandes volumes de dados com a instrução de exclusão leve pode impactar negativamente o desempenho das consultas SELECT.

O seguinte também pode impactar negativamente o desempenho da exclusão leve:

• Uma condição WHERE complexa em uma consulta DELETE.
• Se a fila de mutações estiver cheia de muitas outras mutações, isso pode causar problemas de desempenho, já que todas as mutações em uma tabela são executadas sequencialmente.
• A tabela afetada tem um número muito grande de partes de dados.
• Ter muitos dados em partes compactas. Em uma parte Compact, todas as colunas são armazenadas em um único arquivo.

​

Permissões para exclusão

DELETE exige o privilégio ALTER DELETE. Para habilitar instruções DELETE em uma tabela específica para um determinado usuário, execute o seguinte comando:

GRANT ALTER DELETE ON db.table to username;

​

Como as exclusões leves funcionam internamente no ClickHouse

1. Uma “máscara” é aplicada às linhas afetadas

   Quando uma consulta DELETE FROM table ... é executada, o ClickHouse salva uma máscara em que cada linha é marcada como “existente” ou “excluída”. Essas linhas “excluídas” são omitidas das consultas subsequentes. No entanto, as linhas só são realmente removidas mais tarde, em merges subsequentes. Gravar essa máscara é muito mais leve do que o que é feito por uma consulta ALTER TABLE ... DELETE.

   A máscara é implementada como uma coluna de sistema oculta _row_exists que armazena True para todas as linhas visíveis e False para as excluídas. Essa coluna só está presente em uma parte se algumas linhas dessa parte tiverem sido excluídas. Essa coluna não existe quando uma parte tem todos os valores iguais a True.

2. As consultas SELECT são transformadas para incluir a máscara

   Quando uma coluna mascarada é usada em uma consulta, a consulta SELECT ... FROM table WHERE condition é internamente estendida com o predicado sobre _row_exists e transformada em:

   SELECT ... FROM table PREWHERE _row_exists WHERE condition
   

   Em tempo de execução, a coluna _row_exists é lida para determinar quais linhas não devem ser retornadas. Se houver muitas linhas excluídas, o ClickHouse pode determinar quais grânulos podem ser totalmente ignorados ao ler o restante das colunas.

3. As consultas DELETE são transformadas em consultas ALTER TABLE ... UPDATE

   O DELETE FROM table WHERE condition é traduzido em uma mutação ALTER TABLE table UPDATE _row_exists = 0 WHERE condition.

   Internamente, essa mutação é executada em duas etapas:

   1. Um comando SELECT count() FROM table WHERE condition é executado para cada parte individual para determinar se ela foi afetada.

   2. Com base nos comandos acima, as partes afetadas passam pela mutação, e hardlinks são criados para as partes não afetadas. No caso de partes wide, a coluna _row_exists de cada linha é atualizada, e os arquivos de todas as outras colunas recebem hardlinks. Para partes compact, todas as colunas são regravadas porque ficam armazenadas juntas em um único arquivo.

   Pelas etapas acima, podemos ver que a exclusão leve usando a técnica de mascaramento melhora o desempenho em relação ao ALTER TABLE ... DELETE tradicional porque ele não regrava os arquivos de todas as colunas das partes afetadas.

​

Conteúdo relacionado

• Blog: Como lidar com atualizações e exclusões no ClickHouse