Удаление огромных блоков данных из mysql innodb
Я должен удалить огромный блок своих данных в моей производственной базе данных, которая выполняет приблизительно 100 ГБ в размере. Если возможно, я хотел бы минимизировать свое время простоя.
Мои критерии выбора для удаления, вероятно, будут
УДАЛИТЕ * ИЗ РЕГИСТРАЦИИ ГДЕ ПОЛЬЗОВАТЕЛЬ ID=5 И UPDATED_AT <100
Что лучший способ состоит в том, чтобы удалить его?
- Создать индекс?
- Запишите последовательный сценарий, который удаляет через нумерацию страниц через строки 1000 за один раз?
4 ответа
Лучше всего удалять постепенно, используя предложение LIMIT (на 10000 элементов), но не применять упорядочивание. Это позволит MySQL чаще сбрасывать результаты, и транзакции не будут огромными. Вы можете легко сделать это с помощью любого установленного вами языка программирования, у которого есть коннектор для mysql. Обязательно фиксируйте после каждого утверждения.
Индекс определенно поможет, но его создание займет некоторое время и для таблицы размером 100 ГБ (в любом случае его стоит создать, если вы собираетесь повторно использовать индекс в будущем). Кстати, ваш текущий запрос неверен, потому что ссылка на таблицу USER не указана здесь. Вы должны быть осторожны с индексом, чтобы оптимизатор мог извлечь выгоду из его использования.
Вы можете попробовать использовать метод, упомянутый в mysql doc :
Выберите строки, которые не следует удалять, в пустую таблицу, имеющую ту же структуру, что и исходная таблица:
INSERT INTO t_copy SELECT * FROM t WHERE ...;
Используйте RENAME TABLE, чтобы атомарно переместить исходную таблицу в сторону и переименовать копию в исходное имя:
RENAME TABLE t TO t_old, t_copy TO t;
Отбросьте исходную таблицу:
DROP TABLE t_old;
Если возможно, используйте двоичное ведение журнала на уровне строк, а не двоичное ведение журнала на уровне операторов (это уменьшает количество блокировок), по крайней мере, во время этой операции. Выполняйте удаление партиями (1000 - приличный размер). Используйте первичный ключ в качестве критерия для удаления каждого пакета и упорядочения по первичному ключу (чтобы вы удаляли строки, которые физически близки друг к другу).
Некоторое время назад я хотел удалить более 99% данных из таблицы. Таблица, которую я удалял, была таблицей сессий, в которой было более 250 миллионов строк, а мне нужны были только последние 500 тысяч. Самый быстрый способ, который я придумал, заключался в том, чтобы выбрать 500 000 строк, которые мне были нужны, в другую таблицу. Удалить старую таблицу и переименовать новую таблицу вместо удаленной. Это было примерно в 100 раз быстрее, чем обычное удаление, при котором приходится выбирать записи и перестраивать таблицу.
Это также имеет дополнительное преимущество в уменьшении размера файла таблицы, если вы используете InnoDB с innodb_file_per_table = 1, потому что таблицы InnoDB никогда не уменьшаются.