Оператор Lightweight DELETE

Легковесное удаление упрощает процесс удаления данных из базы данных.

Оператор легковесного DELETE удаляет строки из таблицы [db.]table, которые соответствуют выражению expr. Он доступен только для таблиц семейства *MergeTree.

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

Это называется “легковесным DELETE” в отличие от команды ALTER TABLE … DELETE, которая является тяжеловесной операцией.

​

Примеры

-- Удаляет все строки из таблицы `hits`, где столбец `Title` содержит текст `hello`
DELETE FROM hits WHERE Title LIKE '%hello%';

​

Легковесный DELETE не удаляет данные сразу

Легковесный DELETE реализован как мутация: он помечает строки как удалённые, но не удаляет их физически сразу.

По умолчанию команды DELETE дожидаются завершения пометки строк как удалённых перед тем, как вернуть результат. Если данных много, это может занять продолжительное время. В качестве альтернативы можно запустить операцию асинхронно в фоновом режиме с помощью настройки lightweight_deletes_sync. Если она отключена, оператор DELETE вернётся сразу, но данные могут оставаться видимыми для запросов, пока не завершится фоновая мутация.

Мутация не удаляет физически строки, помеченные как удалённые: это произойдёт только во время следующего слияния. В результате в течение неопределённого времени данные могут фактически оставаться в хранилище и быть лишь помеченными как удалённые.

Если вам нужно гарантировать удаление данных из хранилища в предсказуемые сроки, рассмотрите возможность использования настройки таблицы min_age_to_force_merge_seconds. Либо можно использовать команду ALTER TABLE … DELETE. Обратите внимание, что удаление данных с помощью ALTER TABLE ... DELETE может потребовать значительных ресурсов, поскольку при этом пересоздаются все затронутые части.

​

Удаление больших объёмов данных

Удаление больших объёмов данных может отрицательно сказаться на производительности ClickHouse. Если вам нужно удалить из таблицы все строки, рассмотрите возможность использования команды TRUNCATE TABLE.

Если вы предполагаете, что удаления будут выполняться часто, рассмотрите возможность использования пользовательского ключа партиционирования. Затем можно использовать команду ALTER TABLE ... DROP PARTITION, чтобы быстро удалить все строки, относящиеся к этой партиции.

​

Ограничения легковесного DELETE

​

Облегчённые DELETE с проекциями

По умолчанию DELETE не работает для таблиц с проекциями. Это связано с тем, что операция DELETE может затрагивать строки в проекции. Однако это поведение можно изменить с помощью настройки MergeTree lightweight_mutation_projection_mode.

​

Особенности производительности при использовании облегчённого DELETE

Удаление больших объёмов данных с помощью облегчённого оператора DELETE может негативно сказаться на производительности запросов SELECT.

На производительность облегчённого DELETE также могут негативно влиять следующие факторы:

• Сложное условие WHERE в запросе DELETE.
• Если очередь мутаций заполнена большим количеством других мутаций, это может привести к проблемам с производительностью, так как все мутации таблицы выполняются последовательно.
• Затронутая таблица содержит очень большое число частей данных.
• Большой объём данных в компактных частях. В компактной части все столбцы хранятся в одном файле.

​

Разрешения на DELETE

Для DELETE требуется привилегия ALTER DELETE. Чтобы разрешить пользователю выполнять команды DELETE для конкретной таблицы, выполните следующую команду:

GRANT ALTER DELETE ON db.table to username;

​

Как внутри ClickHouse работают легковесные DELETE

1. К затронутым строкам применяется “маска”

   Когда выполняется запрос DELETE FROM table ..., ClickHouse сохраняет маску, в которой каждая строка помечается как “существующая” или “удалённая”. Эти “удалённые” строки исключаются из последующих запросов. Однако физически строки удаляются только позже, в ходе последующих слияний. Запись этой маски значительно менее затратна, чем выполнение запроса ALTER TABLE ... DELETE.

   Маска реализована в виде скрытого системного столбца _row_exists, который хранит True для всех видимых строк и False для удалённых. Этот столбец присутствует в части только в том случае, если из неё были удалены некоторые строки. Если в части все значения равны True, этого столбца нет.

2. Запросы SELECT преобразуются так, чтобы учитывать маску

   Когда в запросе используется столбец с маской, запрос SELECT ... FROM table WHERE condition внутри ClickHouse дополняется предикатом по _row_exists и преобразуется в:

   SELECT ... FROM table PREWHERE _row_exists WHERE condition
   

   Во время выполнения столбец _row_exists считывается, чтобы определить, какие строки не нужно возвращать. Если удалённых строк много, ClickHouse может определить, какие гранулы можно полностью пропустить при чтении остальных столбцов.

3. Запросы DELETE преобразуются в запросы ALTER TABLE ... UPDATE

   DELETE FROM table WHERE condition преобразуется в мутацию ALTER TABLE table UPDATE _row_exists = 0 WHERE condition.

   Внутри ClickHouse эта мутация выполняется в два шага:

   1. Для каждой отдельной части выполняется команда SELECT count() FROM table WHERE condition, чтобы определить, затронута ли эта часть.

   2. На основе результатов этих команд затем мутируются затронутые части, а для незатронутых создаются жёсткие ссылки. В случае широких частей столбец _row_exists обновляется для каждой строки, а файлы всех остальных столбцов связываются жёсткими ссылками. Для компактных частей все столбцы перезаписываются, поскольку они хранятся вместе в одном файле.

   Из описанных выше шагов видно, что легковесный DELETE, использующий технику маскирования, работает быстрее традиционного ALTER TABLE ... DELETE, потому что не требует перезаписывать файлы всех столбцов для затронутых частей.

​

См. также

• Блог: Обновления и удаления в ClickHouse