Удалите огромные объемы данных из огромной таблицы

Ответ 1

Как насчет этого ограничения?

delete x 
  from `VALUE` x
  join (select key_id, value_id
          from `VALUE` v
          left join `KEY` k using (key_id)
         where k.key_id is null
         limit 1000) y
    on x.key_id = y.key_id AND x.value_id = y.value_id;

Ответ 2

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

Хорошим инструментом для помощи является pt-archiver. Он выполняет инкрементные операции над партиями рядов с умеренным размером, насколько это возможно. pt-archiver может копировать, перемещать или удалять строки в зависимости от параметров.

В документации приведен пример удаления сиротских строк, что в точности соответствует вашему сценарию:

pt-archiver --source h=host,D=db,t=VALUE --purge \
  --where 'NOT EXISTS(SELECT * FROM `KEY` WHERE key_id=`VALUE`.key_id)' \
  --limit 1000 --commit-each

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

pt-archiver является частью Percona Toolkit for MySQL, бесплатного (GPL) набора сценариев, которые помогают общим задачам с MySQL и совместимыми базы данных.

Ответ 3

Непосредственно из Документация по MySQL

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

Выберите строки, которые не должны быть удалены в пустую таблицу, которая имеет ту же структуру, что и исходная таблица:

INSERT INTO t_copy SELECT * FROM t WHERE ... ;

Используйте RENAME TABLE для автоматического перемещения исходной таблицы и переименования копии в исходное имя:

RENAME TABLE t TO t_old, t_copy TO t;

Отбросьте исходную таблицу:

DROP TABLE t_old;

Никакие другие сеансы не могут получить доступ к таблицам, участвующим в то время как RENAME TABLE выполняется, поэтому операция переименования не подлежит concurrencyпроблемы. См. Раздел 12.1.9, "Синтаксис RENAME TABLE".

Итак, в вашем случае вы можете сделать

INSERT INTO value_copy SELECT * FROM VALUE WHERE key_id IN
    (SELECT key_id FROM `KEY`);

RENAME TABLE value TO value_old, value_copy TO value;

DROP TABLE value_old;

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

Ответ 4

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

Ответ 5

Может быть вместо предела делить весь набор строк на мелкие части key_id:

delete v 
  from VALUE v
  left join KEY k using (key_id)
 where k.key_id is null and v.key_id > 0 and v.key_id < 100000;

затем удалите строки с ключом в 100000..200000 и т.д.

Ответ 6

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

declare @i INT
declare @step INT
set @i = 0
set @step = 100000

while (@i< (select max(VALUE.key_id) from VALUE))
BEGIN
  BEGIN TRANSACTION
  delete from VALUE where
    VALUE.key_id between @i and @[email protected] and
    not exists(select 1 from KEY where KEY.key_id = VALUE.key_id and KEY.key_id between @i and @[email protected])

  set @i = (@[email protected])
  COMMIT TRANSACTION
END

Ответ 7

Создайте временную таблицу!

drop table if exists batch_to_delete;
create temporary table batch_to_delete as
select v.* from `VALUE` v
left join `KEY` k on k.key_id = v.key_id
where k.key_id is null
limit 10000; -- tailor batch size to your taste

-- optional but may help for large batch size
create index batch_to_delete_ix_key on batch_to_delete(key_id); 
create index batch_to_delete_ix_value on batch_to_delete(value_id);

-- do the actual delete
delete v from `VALUE` v
join batch_to_delete d on d.key_id = v.key_id and d.value_id = v.value_id;

Ответ 8

Для меня это задача, которую я хотел бы увидеть в файле журнала. И я бы не стал решать это в чистом SQL, я бы использовал некоторые скрипты на Python или на другом подобном языке. Еще одна вещь, которая меня беспокоит, заключается в том, что большое количество ЛЕВЫХ ПРИСОЕДИНЕНИЙ с ГДЕ НЕ НУЛЛ между таблицами может привести к нежелательным блокировкам, поэтому я также избегаю JOINs.

Вот несколько псевдокодов:

max_key = select_db('SELECT MAX(key) FROM VALUE')
while max_key > 0:
    cur_range = range(max_key, max_key-100, -1)
    good_keys = select_db('SELECT key FROM KEY WHERE key IN (%s)' % cur_range)
    keys_to_del = set(cur_range) - set(good_keys)
    while 1:
        deleted_count = update_db('DELETE FROM VALUE WHERE key IN (%s) LIMIT 1000' % keys_to_del)
        db_commit
        log_something
        if not deleted_count:
            break
    max_key -= 100

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

Ответ 9

Если целевые столбцы правильно проиндексированы, это должно идти быстро,

DELETE FROM `VALUE`
WHERE NOT EXISTS(SELECT 1 FROM `key` k WHERE k.key_id = `VALUE`.key_id)
-- ORDER BY key_id, value_id -- order by PK is good idea, but check the performance first.
LIMIT 1000

Измените предел от 10 до 10000, чтобы получить приемлемую производительность, и повторите его несколько раз.

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

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

Ответ 10

Есть ли у вас среда SLAVE или Dev/Test с одинаковыми данными?

Первый шаг - узнать ваше распределение данных, если вы беспокоитесь о том, что определенный ключ имеет 1 миллион value_ids

SELECT v.key_id, COUNT(IFNULL(k.key_id,1)) AS cnt 
FROM `value` v  LEFT JOIN `key` k USING (key_id) 
WHERE k.key_id IS NULL 
GROUP BY v.key_id ;

EXPLAIN PLAN для вышеуказанного запроса намного лучше, чем добавление

ORDER BY COUNT(IFNULL(k.key_id,1)) DESC ;

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

Самый быстрый способ:

• Остановить приложение, чтобы данные не были изменены.

• Дамп key_id и value_id из таблицы VALUE с использованием только соответствия key_id в таблице KEY с помощью

  Значение mysqldump YOUR_DATABASE_NAME - where = "key_id in (выберите key_id из YOUR_DATABASE_NAME.key)" --lock-all --opt --quick --quote-names --skip-extended-insert > VALUE_DATA.txt

• Обрезать таблицу VALUE

• Загрузка данных, экспортированных на шаге 2
• Запустить приложение

Как всегда, попробуйте это в среде Dev/Test с данными Prod и той же инфраструктурой, чтобы вы могли рассчитать время простоя.

Надеюсь, что это поможет.

Ответ 11

Мне просто интересно, какой эффект будет заключаться в добавлении неидеального индекса в key_id в таблицу VALUE. Селективность невелика (~ 0,001), но мне любопытно, как это повлияет на производительность соединения.

Ответ 12

Почему бы вам не разделить таблицу VALUE на несколько из них в соответствии с некоторым правилом, например, с модулем key_id, с мощностью 2 (например, 256)?