Большие удаления

Проблема

Как удалить много строк из большой таблицы? Вот пример очистки записей, старше 30 дней:

DELETE FROM tbl WHERE 
  ts < CURRENT_DATE() - INTERVAL 30 DAY

Если в таблице миллионы строк, это операция может занять минуты, а может и часы.

Есть ли предложения о том, как ускорить этот процесс?

Почему это проблема

• MyISAM заблокирует таблицу на всё время операции, что не позволит выполнить никакие другие действия с таблицей.
• InnoDB не блокирует таблицу, но потребует много ресурсов, что приведёт к замедлению работы.
• InnoDB должен записать информацию о развороте в журналы транзакций; это значительно увеличивает требования к вводу-выводу.
• Репликация, являясь асинхронной, будет фактически задерживаться (на серверах-следопытах) во время выполнения удаления.

InnoDB и разворот

Для готовности к сбоям, транзакционный движок, такой как InnoDB, записывает свои действия в файл журнала. Для того, чтобы это было несколько менее дорогостоящим, файл журнала записывается последовательно. Если журналы заполняются (обычно их 2) из-за большого удаления, то информация о развороте переносится в сами блоки данных, что ведёт к ещё большему вводу-выводу.

Удаление по частям избегает некоторых из этих избыточных накладных расходов.

Ограниченные тесты общей длительности удаления показывают две закономерности:

• Общее время удаления примерно удваивается при превышении определённого размера "пакета" (по сравнению со значениями ниже этого порога). У меня нет формулы, связывающей размер файла журнала с пороговым значением.
• Размер пакета ниже нескольких сотен строк медленнее. Вероятно, это связано с тем, что накладные расходы на запуск/окончание каждого пакета доминируют во времени.

Решения

• PARTITION — Требует некоторой тщательной настройки, но отлично подходит для очистки временных рядов.
• Удаление частями — Аккуратно проходим по таблице N строк за раз.

PARTITION

Идея здесь состоит в том, чтобы иметь скользящее окно разделов. Допустим, вам нужно очистить новости старше 30 дней. "Ключ разбиения" будет датой (или временной меткой), которая используется для очистки, а разбиения будут "диапазонными". Каждую ночь задача cron будет создавать новый раздел для следующего дня и удалять самый старый раздел.

Удаление раздела происходит практически мгновенно, гораздо быстрее, чем удаление такого количества строк. Однако вы должны спроектировать таблицу так, чтобы весь раздел можно было удалить. То есть, вы не можете иметь некоторые элементы, живущие дольше других.

Таблицы PARTITION имеют много ограничений, некоторые из них довольно странные. У вас либо нет уникального (или первичного) ключа в таблице, либо каждый уникальный ключ должен включать ключ разбиения. В этом случае ключом разбиения является дата. Он не должен быть первой частью первичного ключа (если у вас есть первичный ключ).

Вы можете разбить таблицы InnoDB или MyISAM.

Поскольку две новости могут иметь одинаковую временную метку, вы не можете считать, что ключ разбиения достаточен для уникальности первичного ключа, поэтому вам нужно найти что-то ещё, что поможет в этом.

Реализация примера для обслуживания раздела

Документация MariaDB по PARTITION

Удаление частями

Хотя в этом разделе речь идёт об удалении, его можно использовать и для других операций по "пакетированию", например, UPDATE или SELECT с последующей сложной обработкой.

(Это обсуждение относится как к MyISAM, так и к InnoDB.)

При удалении частями убедитесь, что не выполняете сканирование всей таблицы. Представленный ниже код хорошо справляется с этим; он сканирует не более 1001 строки в любом запросе. (1000 — настраиваемое значение.)

Предполагая, что у вас есть новости, которые нужно очистить, и у вас есть структура данных примерно следующего вида

   CREATE TABLE tbl
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      ts TIMESTAMP,
      ...
      PRIMARY KEY(id)

Тогда этот псевдокод — хороший способ удалить строки, старше 30 дней:

   @a = 0
   LOOP
      DELETE FROM tbl
         WHERE id BETWEEN @a AND @a+999
           AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      SET @a = @a + 1000
      sleep 1  -- be a nice guy
   UNTIL end of table

Примечания (большинство этих замечаний будет рассмотрено позже):

• Используется PK вместо вторичного ключа. Это обеспечивает лучшую локальность попаданий в диск, особенно для InnoDB.
• Можно (и нужно?) сделать что-то, чтобы не проходить по дням, для которых ничего не нужно делать. Осторожно — код для этого может быть дорогим.
• 1000 следует настроить так, чтобы удаление обычно занимало менее, скажем, одной секунды.
• Индекс на ts не нужен. (Это немного помогает INSERTs.)
• Если ваш первичный ключ составной, код становится более запутанным.
• Этот код не будет работать без числового первичного или уникального ключа.
• Продолжайте читать, мы разработаем более сложный код, чтобы справиться с большинством этих замечаний.

Если в значениях `id` есть большие пробелы (и они будут после первой очистки), то

   @a = SELECT MIN(id) FROM tbl
   LOOP
      SELECT @z := id FROM tbl WHERE id >= @a ORDER BY id LIMIT 1000,1
      If @z is null
         exit LOOP  -- last chunk
      DELETE FROM tbl
         WHERE id >= @a
           AND id <  @z
           AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      SET @a = @z
      sleep 1  -- be a nice guy, especially in replication
   ENDLOOP
   # Last chunk:
   DELETE FROM tbl
      WHERE id >= @a
        AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

Этот код работает, независимо от того, является ли `id` числовым или символьным, и он в основном работает, даже если `id` не является уникальным. При не уникальном ключе риск заключается в том, что вы можете попасть в цикл, когда @z == @a. Это можно обнаружить и исправить, например:

   ...
      SELECT @z := id FROM tbl WHERE id >= @a ORDER BY id LIMIT 1000,1
      If @z == @a
         SELECT @z := id FROM tbl WHERE id > @a ORDER BY id LIMIT 1
   ...

Недостатком является то, что может быть более 1000 элементов с одним id. В большинстве практических случаев это маловероятно.

Если у вас нет первичного (или уникального) ключа в таблице, а есть индекс на ts, то рассмотрите

   LOOP
      DELETE FROM tbl
         WHERE ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
         ORDER BY ts   -- to use the index, and to make it deterministic
         LIMIT 1000
   UNTIL no rows deleted

Этот метод НЕ рекомендуется, так как LIMIT приводит к предупреждению о не детерминированности при репликации (см. ниже).

Рекомендации по чанкингу InnoDB

• Установите «разумный» размер для innodb_log_file_size.
• Используйте AUTOCOMMIT=1 для сеанса, выполняющего удаления.
• Выберите приблизительно 1000 строк для размера пакета.
• Уменьшите количество строк, если асинхронная репликация (на основе операторов) вызывает слишком большую задержку на серверах-следопытах или слишком сильно загружает таблицу.

Итерация по составному ключу

Для выполнения рекомендуемого выше чанкинга удаления вам нужен способ пройти по первичному ключу. Это может быть сложно, если PK содержит более одной колонки.

Для эффективного выполнения составных «больше чем»:

Предположим, вы остановились на ($g, $s) (и обработаны все строки):

   INDEX(Genus, species)
   SELECT/DELETE ...
      WHERE Genus >= '$g' AND ( species  > '$s' OR Genus > '$g' )
      ORDER BY Genus, species
      LIMIT ...

Дополнение: вышеупомянутое AND/OR хорошо работает в старых версиях MySQL; в MariaDB и более новых версиях MySQL это работает лучше:

      WHERE ( Genus = '$g' AND species  > '$s' ) OR Genus > '$g' )

Предупреждение об использовании переменных @ для строк. Если вместо '$g' вы используете @g, нужно быть внимательным, чтобы убедиться, что @g имеет тот же CHARACTER SET и COLLATION, что и `Genus`, иначе может быть преобразование кодировки/сортировки на лету, что предотвратит использование индекса. Использование индекса имеет важное значение для производительности. Это может потребовать COLLATE-заказ в SET NAMES и/или @g в SELECT.

Освобождение дискового пространства

Это дорогостояще. (Переключитесь на решение PARTITION, если это возможно.)

MyISAM оставляет пробелы в таблице (.MYD-файл); OPTIMIZE TABLE восстановит освобождённое место после большого удаления. Но это может занять много времени и заблокировать таблицу.

InnoDB имеет блочную структуру, организованную в B-дереве по первичному ключу. Изолированная удалённая строка делает блок менее заполненным. Много удалённых строк может привести к слиянию смежных блоков. (Блоки обычно имеют размер 16 КБ — см. innodb_page_size.)

В InnoDB нет практического способа вернуть освобождённое пространство из ibdata1, кроме как переиспользовать освобождённые блоки в будущем.

Единственный вариант с innodb_file_per_table = 0 — это создать дамп ВСЕХ таблиц, удалить ibdata*, перезапустить и загрузить. Это редко стоит усилий и времени.

InnoDB, даже с innodb_file_per_table = 1, не вернёт место операционной системе, но, по крайней мере, это только одна таблица, которую нужно перестроить. В этом случае что-то вроде этого должно сработать:

   CREATE TABLE new LIKE main;
   INSERT INTO new SELECT * FROM main;  -- This could take a long time
   RENAME TABLE main TO old, new TO main;   -- Atomic swap
   DROP TABLE old;   -- Space freed up here

Вам нужно достаточно места на диске для обеих копий. Во время процесса нельзя записывать в таблицу.

Удаление более половины таблицы

Следующий метод можно использовать для любой комбинации

• Более эффективного удаления большой части таблицы
• Добавления PARTITION
• Преобразования в innodb_file_per_table = ON
• Дефрагментации

Это можно сделать частями или (если возможно) сразу:

   -- Optional:  SET GLOBAL innodb_file_per_table = ON;
   CREATE TABLE New LIKE Main;
   -- Optional:  ALTER TABLE New ADD PARTITION BY RANGE ...;
   -- Do this INSERT..SELECT all at once, or with chunking:
   INSERT INTO New
      SELECT * FROM Main
         WHERE ...;  -- just the rows you want to keep
   RENAME TABLE main TO Old, New TO Main;
   DROP TABLE Old;   -- Space freed up here

Примечания:

• Вам нужно достаточно места на диске для обеих копий.
• Во время процесса нельзя записывать в таблицу. (Изменения в Основной не отображаются в Новой.)

Недетерминированная репликация

Любое UPDATE, DELETE и т. д. с LIMIT, которое реплицируется на сервера-следопыты (через репликацию на основе операторов), может привести к несоответствиям между мастер- и следопытными серверами. Это связано с тем, что фактический порядок строк, обнаруженных для обновления/удаления, может отличаться на сервере-следопыте, что приводит к изменению подмножества. Для безопасности добавьте ORDER BY в такие операторы. Кроме того, убедитесь, что ORDER BY является детерминированным — то есть поля/выражения в ORDER BY являются уникальными.

Пример ORDER BY, который не совсем работает: предположим, что есть несколько строк для каждой «даты»:

   DELETE * FROM tbl ORDER BY date LIMIT 111

Учитывая, что `id` — первичный ключ (или уникальный), это будет безопасно:

   DELETE * FROM tbl ORDER BY date, id LIMIT 111

К сожалению, даже с ORDER BY MySQL имеет недостаток, который приводит к ложному предупреждению в mysqld.err. См. предупреждения "Statement is not safe to log in statement format"

Некоторые из представленных выше кодов избегают этого ложного предупреждения, выполняя

   SELECT @z := ... LIMIT 1000,1;  -- not replicated
   DELETE ... BETWEEN @a AND @z;   -- deterministic

Эта пара операторов гарантирует, что не более 1000 строк будут затронуты, а не вся таблица.

Репликация и KILL

Если вы KILL удаление (или любой? запрос) на мастер-сервере в середине его выполнения, что будет реплицировано?

Если это InnoDB, запрос должен быть отменён. (Исключения??)

В MyISAM строки удаляются по мере выполнения оператора, и нет возможности отменить операцию. Некоторые строки будут удалены, некоторые — нет. У вас, вероятно, нет представления о том, сколько было удалено. На отдельном сервере просто выполните удаление ещё раз. Удаление помещается в binlog, но с ошибкой 1317. Поскольку репликация должна поддерживать синхронизацию мастер- и следопытных серверов, а у неё нет представления о том, как это сделать, репликация останавливается и ожидает ручного вмешательства. В HA-системе (высокая доступность) с репликацией это небольшая катастрофа. Тем временем вам нужно перейти на каждый сервер-следопыт(ы) и проверить, что он застрял по этой причине, затем выполнить

   SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
   START SLAVE;

Затем (предположительно) повторное выполнение удаления завершит прерванную задачу.

(Это ещё одна причина переместить все ваши таблицы из MyISAM в InnoDB.)

SBR против RBR; Galera

TBD — «Репликация на основе строк» может повлиять на это обсуждение.

Postlog

Советы в этом документе относятся к MySQL, MariaDB и Percona.

См. также

Рик Джеймс любезно разрешил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, руководства, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/deletebig