MySQL - самый эффективный способ обрезки большого количества потерянных строк

Question

Реорганизовав некоторый старый код, мы обнаружили, что объекты класса X создаются слишком часто, и ~80% из них остаются без ссылки.

У меня есть ~10 таблиц, которые ссылаются на строки в таблице класса X. Я могу легко определить количество потерянных строк. Таблица содержит около 7 миллионов строк, и только около 1,5 миллионов представляют объекты, на которые есть законные ссылки.

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

Благодаря моему ограниченному опыту администратора БД, единственный способ, которым я знаю, - это выбрать все допустимые идентификаторы объектов "X-типа", а затем сделать что-то вроде: DELETE FROM x WHERE id NOT IN (valid_references), Это примерно 10 триллионов сравнений, должен быть лучший способ.

Answer 1

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

Шаг 1: Создайте новую временную таблицу с точно такой же структурой, как та, которую мы пытаемся урезать по размеру.

Шаг 2: Определите таблицы / столбцы, которые ссылаются на рассматриваемый класс (X).

SELECT * 
FROM information_schema.COLUMNS 
WHERE table_schema = 'my_db_name' 
AND column_name LIKE '%reference_column%'

Шаг 3: Для каждой строки в этих таблицах / столбцах с ненулевой ссылкой получите строку X, на которую они ссылаются, и скопируйте ее в новую таблицу, если она еще не существует в новой таблице. Я делал эту таблицу за таблицей и использовал частичный подход по 1000 записей за раз, чтобы сэкономить системную память. Я написал эту логику на своем прикладном уровне, чтобы лучше ее контролировать. я использовал INSERT IGNORE чтобы избежать создания конфликтов первичного ключа, поскольку некоторые ссылки были на один и тот же объект X.

Шаг 4: Удалите старую таблицу, переименуйте новую в то же имя, что и старая.

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE old_table; 
RENAME TABLE new_table TO old_table;
SET FOREIGN_KEY_CHECKS=1;

Все говорят, что в моей среде требуется около часа, чтобы таблица X имела ~7 миллионов строк, а у меня есть ~10 других таблиц, ссылающихся на нее.