Как я могу улучшить DELETE FROM performance на больших таблицах InnoDB?

Ответ 1

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

Можно добавить новый столбец BIT и по умолчанию TRUE для "active" и FALSE для "inactive". Если этого недостаточно, вы можете использовать TINYINT с 256 возможными значениями.

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

Причина, по которой InnoDB занимает столько времени на DELETE на такой массивной таблице, как ваша, из-за индекса кластера. Он физически заказывает вашу таблицу на основе PRIMARY, сначала UNIQUE, которую он находит, или что бы он ни мог определить как адекватную замену, если он не может найти PRIMARY или UNIQUE, поэтому, когда одна строка удаляется, теперь физически переупорядочивает всю вашу таблицу на диске для скорости и дефрагментации. Так что это не DELETE, что так долго; это физическое переупорядочение после удаления этой строки.

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

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

Ответ 2

У меня был похожий сценарий с таблицей с 2 ​​миллионами строк и оператором delete, который должен удалять около 100 тысяч строк - для этого потребовалось около 10 минут.

После проверки конфигурации я обнаружил, что MySQL Server работает со значением по умолчанию innodb_buffer_pool_size= 8 МБ (!).

После перезапуска с innodb_buffer_pool_size= 1,5 ГБ, тот же сценарий занял 10 секунд.

Итак, похоже, что существует зависимость, если "переупорядочение таблицы" может поместиться в buffer_pool или нет.

Ответ 3

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

В моей таблице было 33M строк и несколько индексов, и я хотел удалить строки 10K. Моя БД была в Azure без контроля над innodb_buffer_pool_size.

Для простоты я создал таблицу tmp_id только с основным полем id:

CREATE TABLE `tmp_id` (
    `id` bigint(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

Я выбрал набор идентификаторов, которые я хотел удалить, в tmp_id и запустил delete from my_table where id in (select id from tmp_id);. Это не завершилось через 12 часов, поэтому я пробовал только с одним id в tmp_id, и это заняло 25 минут. Выполнение delete from my_table where id = 1234 завершено за несколько миллисекунд, поэтому я решил попробовать сделать это вместо процедуры:

CREATE PROCEDURE `delete_ids_in_tmp`()
BEGIN
    declare finished integer default 0;
    declare v_id bigint(20);
    declare cur1 cursor for select id from tmp_id;
    declare continue handler for not found set finished=1;    
    open cur1;
    igmLoop: loop
        fetch cur1 into v_id;
        if finished = 1 then leave igmLoop; end if;
        delete from problematic_table where id = v_id;
    end loop igmLoop;
    close cur1;
END

Теперь call delete_ids_in_tmp(); удалил все 10K строк менее чем за минуту.