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

У меня довольно большая таблица InnoDB, которая содержит около 10 миллионов строк (и подсчет, ожидается, что он станет в 20 раз больше). Каждая строка не такая большая (в среднем 131 B), но время от времени мне приходится удалять кусок из них, и это занимает много времени. Это структура таблицы:

 CREATE TABLE `problematic_table` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `taxid` int(10) unsigned NOT NULL,
    `blastdb_path` varchar(255) NOT NULL,
    `query` char(32) NOT NULL,
    `target` int(10) unsigned NOT NULL,
    `score` double NOT NULL,
    `evalue` varchar(100) NOT NULL,
    `log_evalue` double NOT NULL DEFAULT '-999',
    `start` int(10) unsigned DEFAULT NULL,
    `end` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `taxid` (`taxid`),
    KEY `query` (`query`),
    KEY `target` (`target`),
    KEY `log_evalue` (`log_evalue`)
) ENGINE=InnoDB AUTO_INCREMENT=7888676 DEFAULT CHARSET=latin1;

Запросы, которые удаляют большие куски из таблицы, выглядят следующим образом:

DELETE FROM problematic_table WHERE problematic_table.taxid = '57';

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

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

Я попытался INSERT ... SELECT те строки, которые я не хочу удалять во временную таблицу, и просто отбрасываю остальное, но поскольку отношение to-delete vs. to-keep shifts to-keep, это уже не является полезным решением.

Это таблица, которая может видеть частые INSERT и SELECT в будущем, но не UPDATE s. В принципе, это журнал и справочная таблица, которая время от времени должна отбрасывать часть своего контента.

Могу ли я улучшить свои индексы на этой таблице, ограничив их длину? Переключение на MyISAM-справку, которая поддерживает DISABLE KEYS во время транзакций? Что еще я мог бы улучшить производительность DELETE?

Изменить: Одно такое удаление будет порядка порядка миллиона строк.

Ответ 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 строк менее чем за минуту.