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

Недавно я обнаружил и исправил ошибку на сайте, над которым я работал, что привело к миллионам повторяющихся строк данных в таблице, которые будут довольно большими даже без них (все еще в миллионах). Я могу легко найти эти повторяющиеся строки и выполнить один запрос на удаление, чтобы убить их всех. Проблема в том, что попытка удалить это много строк за один выстрел блокирует таблицу в течение длительного времени, чего я бы хотел избежать, если это возможно. Единственные способы, с помощью которых я могу избавиться от этих строк, без снятия сайта (путем блокировки таблицы):

Напишите script, который будет выполнять тысячи небольших запросов на удаление в цикле. Это теоретически обойдет проблему заблокированной таблицы, потому что другие запросы смогут попасть в очередь и работать между удалениями. Но он все равно будет сильно загружать базу данных в базу данных и займет много времени.
Переименуйте таблицу и заново создайте существующую таблицу (теперь она будет пустой). Затем выполните очистку на переименованной таблице. Переименуйте новую таблицу, назовите ее прежнюю и объедините новые строки в переименованную таблицу. Для этого требуется значительно больше шагов, но нужно выполнить работу с минимальным прерыванием. Единственная сложная часть здесь состоит в том, что таблица, о которой идет речь, представляет собой таблицу отчетов, поэтому, как только она переименована в другую сторону, а пустая, на ее место, все исторические отчеты уходят, пока я не верну ее на место. Кроме того, процесс слияния может быть немного больно из-за типа данных, которые хранятся. В целом это мой вероятный выбор прямо сейчас.

Мне просто интересно, есть ли у кого-либо еще эта проблема до этого, и если да, то как вы справлялись с этим, не снимая сайт и, надеюсь, с минимальным прерыванием для пользователей? Если я пойду с номером 2 или другим, похожим подходом, я могу запланировать, чтобы материал работал поздно вечером, а на следующий день слияние началось и просто сообщило пользователям заранее, чтобы это не было огромным делом. Я просто хочу посмотреть, есть ли у кого-нибудь идеи для лучшего или простого способа очистки.

Ответ 1

DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000

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

Ответ 2

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

Ответ 3

следующее удаляет 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 = ",$1,";" }' | mysql; 
 done

вы можете сгруппировать их вместе и удалить имя_таблицы, где IN (id1, id2,.. idN) im sure too w/o much problems

Ответ 4

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

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

CREATE TABLE id_temp_table (temp_id int);

Вставьте идентификаторы, которые следует удалить:

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

Создать новую таблицу table_new
Вставьте все записи из таблицы в таблицу_new без лишних строк, которые находятся в id_temp_table

Вставить в table_new.... где table_id NOT IN (выберите (temp_id) из id_temp_table);

Переименовать таблицы

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

Ответ 5

Я бы использовал mk-archiver из отличного Maatkit (куча скриптов Perl для управления MySQL) Maatkit принадлежит барону Шварцу, автору книги O'Reilly "Высокая производительность MySQL".

Цель - низкая отдача, только вперед работу по получению старых данных из таблица без влияния на запросы OLTP много. Вы можете вставить данные в другой таблицы, которые не обязательно должны быть на одном сервер. Вы также можете записать его на файл в формате, подходящем для LOAD DATA INFILE. Или вы не можете ни в этом случае это просто инкрементный DELETE.

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

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

Ответ 6

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

TRUNCATE TABLE table_name

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

Примечание. Операции Truncate не являются безопасными для транзакций; ошибка возникает при попытке одного в ходе активной транзакции или блокировки активной таблицы.

Ответ 7

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

Ответ 8

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

ВЫБЕРИТЕ из read-replica список первичных ключей, которые вы хотите удалить. Экспорт с таким форматом:

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

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

sql-chunker.sh (запомните chmod +x меня и измените 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 "$1" ]
  then
    echo "No chunk size supplied. Invoke: ./sql-chunker.sh 1000 ids.txt"
fi

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

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

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

Вызвать так:

./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, это просто ярлык для входа в систему без ввода пароля в командной строке.

Ответ 9

Я столкнулся с подобной проблемой. У нас была действительно большая таблица, размером около 500 ГБ без разделов и один только один индекс для столбца primary_key. Наш мастер был огромным компьютером, 128 ядрами и 512 гигабайтами оперативной памяти, и у нас было несколько рабов. Мы попробовали несколько методов, чтобы заняться масштабным удалением строк. Я перечислю их всех здесь от худшего к лучшему, что мы found-

Загрузка и удаление по одному ряду за раз. Это самое худшее, что вы могли бы сделать. Итак, мы даже не попробовали это.
Извлечение первых "X" строк из базы данных с использованием запроса предела для столбца primary_key, затем проверка идентификаторов строк для удаления в приложении и запуск одного запроса на удаление со списком идентификаторов primary_key. Итак, 2 запроса на "X" строк. Теперь, этот подход был хорош, но при использовании пакетного задания было удалено около 5 миллионов строк за 10 минут или около того, из-за чего ведомые устройства нашей базы данных MySQL отставали на 105 секунд. 105-секундное отставание в 10-минутной активности. Итак, нам пришлось остановиться.
В этом методе мы вводили задержку в 50 мс между нашей последующей пакетной выборкой и удалением размером 'X' каждая. Это решило проблему задержки, но теперь мы удаляли 1,2-1,3 миллиона строк за 10 минут по сравнению с 5 миллионами в методике №2.
Разделение таблицы базы данных, а затем удаление целых разделов, когда они не нужны. Это лучшее решение, которое у нас есть, но оно требует предварительно разделенной таблицы. Мы выполнили шаг 3, потому что у нас была очень старая многораздельная таблица с индексированием только по столбцу primary_key. Создание раздела заняло бы слишком много времени, и мы оказались в кризисном режиме. Вот несколько ссылок, связанных с разделением, которые я нашел helpful- Официальный справочник по MySQL, ежедневное разбиение базы данных Oracle.

Итак, IMO, если вы можете позволить себе роскошь создать раздел в своей таблице, перейдите к варианту № 4, в противном случае вы застряли с вариантом № 3.

Ответ 10

Я думаю, что медлительность происходит из-за MySQl "кластеризованного индекса", где фактические записи хранятся в индексе первичного ключа - в порядке индекса первичного ключа. Это означает, что доступ к записи через первичный ключ является чрезвычайно быстрым, поскольку для него требуется только одна выборка диска, поскольку запись на диске находится там, где он нашел правильный первичный ключ в индексе.

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

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

Зная выше, то, что мы обнаружили, что действительно ускорило удаление в MySQL, должно было выполнить удаление в обратном порядке. Это создает наименьшее количество перемещений записей, поскольку вы удаляете записи с конца вначале, что означает, что при последующих удалениях будет меньше объектов для перемещения.

Ответ 11

Я ничего не написал для этого, и для его правильного выполнения абсолютно необходим сценарий, но другой вариант - создать новую дублирующуюся таблицу и выбрать все строки, которые вы хотите сохранить в ней. Используйте триггер, чтобы поддерживать его в актуальном состоянии, пока этот процесс завершается. Когда он синхронизирован (за исключением строк, которые вы хотите удалить), переименуйте обе таблицы в транзакции, чтобы новая заменила старую. Брось старый стол и вуаля!

Это (очевидно) требует много дополнительного дискового пространства и может обложить ваши ресурсы ввода-вывода, но в противном случае может быть гораздо быстрее.

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