Как удалить миллионы записей из таблицы MySQL без замедления
Есть ли хороший способ удалить много записей, не замедляя работу сайта?
Мне нужно удалить миллионы записей из таблицы MySQL, которая не имеет индексов и первичного ключа. Я читал в SO и различных учебных пособиях в Интернете, что основная стратегия заключается в том, чтобы ограничить запрос на удаление, переждать одну или две секунды между удалениями и повторять процесс до завершения. Я также (используя PDO) запускаю коммит после завершения всех циклов.
На прошлой неделе это работало нормально, но каждый раз, когда я запускал сценарий, база данных замедлялась, и мы получали много жалоб о том, что сайт работает медленно и т. Д. Это на столе в корзине Miva Merchant, но это не так важно.
Я почти закончила обрезку стола, чтобы я могла просто пережить это и закончить. Но должен быть лучший способ …?
Вот соответствующий код:
$database->beginTransaction();
$selectLimit = 4900; // mysql will lock the entire table at 5000+.....
$loopLimit = 10;
$date = "1456272001"; // 2016-02-24
for( $i = 0; $i < $loopLimit; $i++ ) {
$startTime = time();
$oldBaskets = $database->prepare("DELETE FROM s01_Baskets WHERE CAST(lastupdate AS UNSIGNED) < '" . $date . "' LIMIT " . $selectLimit . "");
if ( $oldBaskets->execute() ) {
$deletes = $oldBaskets->rowCount();
$totalDeletes += $deletes;
$duration = time() - $startTime;
echo "\ndeleted '" . $deletes . "' entries";
echo "\n-- took '" . $duration . "' seconds";
}
sleep(2);
}
$database->commit();
Решение
Создать индекс на lastupdate и немного измените ваш запрос:
DELETE
FROM s01_Baskets
WHERE lastupdate < :date
ORDER BY
lastupdate
LIMIT :limit
Наличие индекса на lastupdate позволит MySQL использовать его как для упорядочения, так и для фильтрации, поэтому механизм будет посещать только те записи, которые должны быть удалены.
Без индекса, MySQL должен проверить все записи в вашей базе данных, как он читает их, прежде чем он достигнет предела.
С помощью CAST в индексируемом поле в MySQL выражение становится неразборчивым (невозможно использовать индекс для фильтрации), поэтому следует преобразовать выражение, с которым вы сравниваете ($date), а не наоборот.
Другие решения
Поскольку у вас нет индексов и идентификаторов с автоинкрементным увеличением, я бы лично выбрал прямой SQL следующим образом:
Примечание: вы, вероятно, должны делать это, когда в системе минимальная активность
RENAME TABLE s01_Baskets TO s01_Baskets_to_be_deleted;
CREATE TABLE s01_Baskets LIKE s01_Baskets_to_be_deleted;
INSERT INTO s01_Baskets (col1, col2, ..., coln)
SELECT *
FROM s01_Baskets_to_be_deleted
WHERE lastupdate >= '2016-02-24 00:00:00';
DROP TABLE s01_Baskets_to_be_deleted;
Первые два должны выполняться относительно быстро, и ваши пользователи не заметят замедления. Все их взаимодействие будет просто направлено на вашу новую пустую таблицу.
Третья команда повторно вставит записи, которые вы хотите сохранить.
Для DROP команда может немного замедлить работу БД с точки зрения дискового ввода-вывода, но, поскольку ни одна из записей не взаимодействует, ваши пользователи почти не будут испытывать замедления.
Кроме того, еще одна причина, по которой удаление происходит так медленно и интенсивно, заключается в том, что MySQL будет регистрировать каждую строку, и если у вас есть какие-либо активные триггеры, они должны быть выполнены до того, как удаление может быть выполнено.