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

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

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

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

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

CREATE TABLE id_temp_table ( temp_id int);

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

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

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

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

вставить в table_new.... где table_id НЕ ВНУТРИ (выберите отдельный (temp_id) из id_temp_table);

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

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

следующее удаляет 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

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

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

Вот рекомендуемая практика:

rows_affected = 0
do {
 rows_affected = do_query(
   "DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
   LIMIT 10000"
 )
} while rows_affected > 0

Удаление 10 000 строк за один раз обычно является достаточно большой задачей, чтобы сделать каждый запрос эффективным, и достаточно короткой задачей, чтобы свести к минимуму воздействие на сервер4 (механизмы хранения транзакций могут выиграть от меньших транзакций). Также может быть хорошей идеей добавить некоторое время ожидания между операторами DELETE, чтобы распределить нагрузку по времени и сократить время удерживания блокировок.

Ссылка на Высокая производительность MySQL

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

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

Вы можете себе представить, что при удалении записи в кластеризованном индексе (как использует MySQL) все записи выше этой записи в индексе (= таблица) должны быть перемещены вниз, чтобы избежать создания массивных дыр в индексе (ну, это то, что я помню из по крайней мере, несколько лет назад — версия 8.x возможно устранила эту проблему).

Вооружившись знаниями о вышеупомянутых «внутренних» операциях, мы обнаружили, что действительно ускоряет удаление в MySQL 5.x, выполняя удаления в обратном порядке. Это приводит к наименьшему количеству перемещений записей, потому что вы сначала удаляете записи с конца, а это означает, что при последующих удалениях остается меньше записей для перемещения - логично, верно?!

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

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

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

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

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

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

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

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

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

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

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

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

У меня был такой же случай ранее. Во время миграции базы данных было сохранено более 45 миллионов дубликатов данных. Да, это случилось. :)

Я сделал следующее:

• Создал временную таблицу, фильтрующую только уникальные
• Усечена исходная таблица
• Вставляется обратно в исходную таблицу из временной таблицы.
• Убедившись, что данные верны, я удалил временную таблицу.

В целом, я думаю, это заняло около 2,5 минут.

Пример:

CREATE TABLE mytable_temp AS SELECT * FROM my_original_table WHERE my_condition;
TRUNCATE TABLE my_original_table;
INSERT INTO my_original_table  SELECT * FROM mytable_temp;

У меня была очень загруженная база, которую нужно было постоянно удалять какие-то старые записи. Некоторые из запросов на удаление начали зависать, поэтому мне нужно было их убить, и если удалений слишком много, вся база перестает отвечать, поэтому мне нужно было ограничить параллельные прогоны. Итак, я создал cron job, запускаемый каждую минуту, запуская этот скрипт:

#!/bin/bash

#######################
#
i_size=1000
max_delete_queries=10
sleep_interval=15
min_operations=8
max_query_time=1000

USER="user"
PASS="super_secret_password"

log_max_size=1000000
log_file="/var/tmp/clean_up.log"
#
#######################

touch $log_file
log_file_size=`stat -c%s "$log_file"`
if (( $log_file_size > $log_max_size ))
then
    rm -f "$log_file"
fi 

delete_queries=`mysql -u user -p$PASS -e  "SELECT * FROM information_schema.processlist WHERE Command = 'Query' AND INFO LIKE 'DELETE FROM big.table WHERE result_timestamp %';"| grep Query|wc -l`

## -- here the hanging DELETE queries will be stopped
mysql-u $USER -p$PASS -e "SELECT ID FROM information_schema.processlist WHERE Command = 'Query' AND INFO LIKE 'DELETE FROM big.table WHERE result_timestamp %'and TIME>$max_query_time;" |grep -v ID| while read -r id ; do
    echo "delete query stopped on `date`" >>  $log_file
    mysql -u $USER -p$PASS -e "KILL $id;"
done

if (( $delete_queries > $max_delete_queries ))
then
  sleep $sleep_interval

  delete_queries=`mysql-u $USER -p$PASS -e  "SELECT * FROM information_schema.processlist WHERE Command = 'Query' AND INFO LIKE 'DELETE FROM big.table WHERE result_timestamp %';"| grep Query|wc -l`

  if (( $delete_queries > $max_delete_queries ))
  then

      sleep $sleep_interval

      delete_queries=`mysql -u $USER -p$PASS -e  "SELECT * FROM information_schema.processlist WHERE Command = 'Query' AND INFO LIKE 'DELETE FROM big.table WHERE result_timestamp %';"| grep Query|wc -l`

      # -- if there are too many delete queries after the second wait
      #  the table will be cleaned up by the next cron job
      if (( $delete_queries > $max_delete_queries ))
        then
            echo "clean-up skipped on `date`" >> $log_file
            exit 1
        fi
  fi

fi

running_operations=`mysql-u $USER -p$PASS -p -e "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';"| wc -l`

if (( $running_operations < $min_operations ))
then
    # -- if the database is not too busy this bigger batch can be processed
    batch_size=$(($i_size * 5))
else 
    batch_size=$i_size
fi

echo "starting clean-up on `date`" >>  $log_file

mysql-u $USER -p$PASS -e 'DELETE FROM big.table WHERE result_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY))*1000 limit '"$batch_size"';'

if [ $? -eq 0 ]; then
    # -- if the sql command exited normally the exit code will be 0
    echo "delete finished successfully on `date`" >>  $log_file
else
    echo "delete failed on `date`" >>  $log_file
fi

Благодаря этому я добился около 2 миллионов удалений в день, что было нормально для моего варианта использования.

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

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

Временная таблица, которую я использовал 'archive_temp' для хранения идентификаторов, созданных в памяти без каких-либо индексов.

Следовательно, при удалении записей из исходной таблицы транзакций, например. УДАЛИТЬ из tat где id (выберите id из archive_temp); запрос, используемый для возврата ошибки ПОТЕРЯНО Соединение с сервером

Я создал индекс для этой временной таблицы после ее создания следующим образом: ALTER TABLE archive_temp ADD INDEX( id);

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

Следовательно, было бы лучше проверить индексы. Надеюсь, это может помочь.

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

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

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

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

TRUNCATE TABLE table_name

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

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