PHP трюк для быстрого DELETE на большой таблице MySQL

Приветствую! У каждого разработчика рано или поздно наступает момент, когда появляется необходимость работать с большими базами данных. В мире таблиц весом более 5 гигабайт действуют немного иные законы "физики", нежели в маленьких табличках: приходится заботиться о тех вещах, о которых раньше даже и не задумывался. Сегодня я поделюсь трюком, который поможет быстро удалить много данных с таблицы MySQL с движком InnoDB.

Проблема

У нас на руках имеется: Таблица с сообщениями `messages` за последние 10 дней. Каждый день в неё сваливается примерно миллион новых записей. По прошествии 10-ти дней у нас скрипт вызываемый кроном перетаскивает сообщения в таблицу `messages_history`. Нам поставили задачу уменьшить срок хранения сообщений в основной таблице с 10 дней до трёх.

Решение "В лоб"

//Собираем все сообщения
$oldMessages = Message::findAll(new DBWhereParamsType([
  'where' => [
    'time <=' => Carbon::now()->subDays(self::LIFETIME_OF_MESSAGES)->format(),
  ],
]));

//Собираем ID всех сообщений, которые достали
$oldMessagesIDs = [];
foreach ($oldMessages as $oldMessage) {
  $oldMessagesIDs[] = $oldMessage->id;
}

//Вставляем полученные старые сообщения в таблицу с историей сообщений
$res = MessageHistory::bathInsert($oldMessages);

//Если insert прошёл успешно, удаляем из основной таблицы старые сообщения
if ($res === true) {
      
  //Эта конструкция под капотом собирает запрос вида
  // DELETE * FROM `messages` WHERE `id` IN (1, 2, 3...)
  MessageD::deleteWhere(new DBWhereParamsType([
    'where' => [
        'id' => $idsList,
      ],
  ]));
}

Вроде бы всё логично. Запускаем с лимитом 50000, и что же мы видим?

Удаление заняло более минуты. Но почему же так? Давайте разбираться

Блокировка таблиц

При удалении из таблицы InnoDB происходит её блокировка на время выполнения транзакции. Когда мы посылаем большой запрос на удаление базе, она блокирует таблицу `messages`, и это довольно сильно затягивает процесс, так как при каждом обращении других скриптов к этой таблице будет создаваться её слепок.

Что с этим делать?

DELETE - это один из немногих случаев, когда оптимизация должна идти не на уменьшение количества запросов, а наоборот, на их увеличение и разбивку на маленькие. И поможет нам в этом php функция array_chunk. Она разобьёт наш $oldMessagesIDs на чанки, по которым будет удобно пройтись foreach'ом:

if ($res === true) {
  //Разбиваем массив на чанки
  $oldMessagesIDsChunks = array_chunk($oldMessagesIDs, 100);

  //Проходимся по каждому из них
  foreach ($oldMessagesIDsChunks as $idsList) {
    MessageD::deleteWhere(new DBWhereParamsType([
      'where' => [
        'id' => $idsList,
      ],
    ]));
  }
}

Запускаем с лимитом 50000...

Вуаля! Итак, давайте подытожим, что у нас случилось:

• Таблица не блокируется одной транзакцией на долгое время

• Среднее время выполнения скрипта уменьшилось примерно в 5 раз

Надеюсь, этот небольшой трюк поможет Вам однажды настроить скрипт удаляющий большие объёмы данных :-) Удачи!