Удаление миллионов строк в MySQL

Я также рекомендую добавить некоторые ограничения к вашей таблице, чтобы убедиться, что это не произойдет с вами снова. Миллион строк, по 1000 за кадр, займет 1000 повторений сценария для завершения. Если скрипт запускается один раз в 3,6 секунды, вы закончите через час. Не волнуйся. Ваши клиенты вряд ли заметят.

следующая удаляет 1 000 000 записей, по одной за раз.

 for i in `seq 1 1000`; do 
     mysql  -e "select id from table_name where (condition) order by id desc limit 1000 " | sed 's;/|;;g' | awk '{if(NR>1)print "delete from table_name where id = ",,";" }' | mysql; 
 done

вы можете сгруппировать их вместе и удалить table_name,где IN (id1, id2,..ИДН) я уверен, что тоже без особых трудностей

У меня был случай использования удаления 1M+ строк в таблице 25M+ строк в MySQL. Пробовал разные подходы, такие как пакетные удаления (описанные выше).
Я узнал, что самый быстрый способ (копирование необходимых записей в новую таблицу):

1. создать временную таблицу, которая содержит только идентификаторы.

создать таблицу id_temp_table (temp_id int);

1. вставить идентификаторы, которые должны быть удалены:

вставить в id_temp_table (temp_id) выбирать.....

1. создать новую таблицу table_new

2. вставить все записи из таблицы в table_new без лишних строк, которые находятся в id_temp_table

вставить в table_new .... где table_id не в (выберите distinct (temp_id) от id_temp_table);

1. переименовать таблицы

весь процесс занял ~1час. в моем случае простое удаление пакета на 100 записей заняло 10 минут.

Я хотел бы использовать МК-архиватором от Maatkit пакет утилит (куча скриптов Perl для управления MySQL) Maatkit от Барона Шварца, автора книги O'Reilly "High Performance MySQL".

цель является низким ударом, только вперед работа, чтобы грызть старые данные из таблица без влияния на запросы OLTP много. Вы можете вставить данные в другой стол, который не обязательно должен быть на одном и том же сервер. Вы также можете написать его файл в формате, подходящем для загрузки ИНФИЛЬ ДАННЫХ. Или вы можете делать ни в в этом случае это просто инкрементный УДАЛИТЬ.

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

установка не требуется, просто возьмите http://www.maatkit.org/get/mk-archiver и запустить perldoc на нем (или читать в интернете сайт) для документации.

По словам документация mysql,TRUNCATE TABLE является быстрой альтернативой DELETE FROM. Попробуйте это:

TRUNCATE TABLE table_name

я попробовал это на 50 м строк, и это было сделано в течение двух минут.

Примечание: операции усечения не являются безопасными для транзакций; ошибка возникает при попытке сделать это в ходе активной транзакции или блокировки активной таблицы

делайте это в партиях, скажем, 2000 строк за раз. Фиксации между ними. Миллион строк-это не так много, и это будет быстро, если у вас не будет много индексов в таблице.

для нас DELETE WHERE %s ORDER BY %s LIMIT %d ответ не был вариантом, потому что критерий WHERE был медленным (неиндексированный столбец) и ударил бы master.

выберите из read-replica список первичных ключей, которые вы хотите удалить. Экспорт в таком формате:

00669163-4514-4B50-B6E9-50BA232CA5EB
00679DE5-7659-4CD4-A919-6426A2831F35

используйте следующий скрипт bash, чтобы захватить этот вход и разбить его на операторы DELETE [требуется bash ≥ 4 из-за mapfile встроенный]:

sql-chunker.sh(вспомните chmod +x me, и измените shebang, чтобы указать на ваш исполняемый файл bash 4):

#!/usr/local/Cellar/bash/4.4.12/bin/bash

# Expected input format:
: <<!
00669163-4514-4B50-B6E9-50BA232CA5EB
00669DE5-7659-4CD4-A919-6426A2831F35
!

if [ -z "" ]
  then
    echo "No chunk size supplied. Invoke: ./sql-chunker.sh 1000 ids.txt"
fi

if [ -z "" ]
  then
    echo "No file supplied. Invoke: ./sql-chunker.sh 1000 ids.txt"
fi

function join_by {
    local d=
    shift
    echo -n ""
    shift
    printf "%s" "${@/#/$d}"
}

while mapfile -t -n "" ary && ((${#ary[@]})); do
    printf "DELETE FROM my_cool_table WHERE id IN ('%s');\n" `join_by "','" "${ary[@]}"`
done < ""

Invoke следующим образом:

./sql-chunker.sh 1000 ids.txt > batch_1000.sql

это даст вам файл с выводом в таком формате (я использовал размер пакета 2):

DELETE FROM my_cool_table WHERE id IN ('006CC671-655A-432E-9164-D3C64191EDCE','006CD163-794A-4C3E-8206-D05D1A5EE01E');
DELETE FROM my_cool_table WHERE id IN ('006CD837-F1AD-4CCA-82A4-74356580CEBC','006CDA35-F132-4F2C-8054-0F1D6709388A');

затем выполните следующие инструкции:

mysql --login-path=master billing < batch_1000.sql

для тех, кто не знаком с login-path, это просто ярлык для входа в систему без ввода пароля в командной строке.