MySQL: обрезать таблицу в транзакции?

У меня есть таблица InnoDB, которую нужно обновлять каждые десять минут в пределах от 60 к до 200 тыс. записей. Наш подход к этому моменту был следующим:

Отключить автосообщение
Обрезать таблицу
Выполнить выбор запросов и дополнительных вычислений (используя PHP)
Вставить новые записи
Фиксировать

После выполнения операции Truncate данные немедленно удаляются и больше не доступны из пользовательского интерфейса. Для наших пользователей это было довольно неприятно, хотя в течение примерно 30 секунд или около того script встречается с операцией Commit, и таблица повторно заселена.

Я подумал, что, возможно, я мог бы обернуть всю операцию, включая Truncate, в транзакцию, и что это может сократить время, в течение которого таблица становится пустой для пользователей. Поэтому я изменил "SET AUTOCOMMIT = 0" на "START TRANSCATION".

Yikes! Это было противоположно желаемому эффекту! Теперь операция TRUNCATE по-прежнему выполняется в начале script, но для выполнения операций INSERT в транзакции требуется MUCH, чтобы к тому времени, когда операция COMMIT и данные в таблице снова будут доступны, это было почти десять минут!

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

Ответ 1

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

Ответ 2

http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html

В соответствии с этим URL, с MySQL 5.1.32, TRUNCATE TABLE является DDL и NOT DML, как DELETE. Это означает, что TRUNCATE TABLE вызовет неявный COMMIT в середине транзакционного блока. Итак, используйте DELETE FROM в таблице, которую нужно очистить вместо TRUNCATE TABLE.

Даже DELETE FROM tblname; можно отбросить назад. Для отката потребуется некоторое время, поэтому убедитесь, что InnoDB правильно настроен для обработки времени транзакции для таких возможностей отката.

Ответ 3

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

Ключевое отличие от SET AUTOCOMMIT = 0 состоит в том, что если он уже 0, он ничего не сделает, где, как и в случае с START TRANSACTION, вы инициируете суб транзакцию в текущей транзакции.