Почему "удаление из таблицы" занимает много времени, когда "обрезать таблицу" занимает 0 раз?

Ответ 1

таблица обрезания не может быть отброшена назад, это похоже на отбрасывание и воссоздание таблицы.

Ответ 2

... просто добавим некоторые детали.

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

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

Ответ 3

Удалить из таблицы удаляет каждую строку из одной за раз и добавляет запись в журнал транзакций, чтобы можно было отменить операцию. Время, затраченное на удаление, также пропорционально количеству индексов в таблице и если есть ограничения внешнего ключа (для innodb).

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

Точные сведения о том, почему это происходит быстрее в MySql, можно найти в документации MySql: http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

Ответ 4

Ваш вопрос касался MySQL, и я почти ничего не знаю о MySQL как о продукте, но я подумал, что добавлю, что в SQL Server инструкция TRUNCATE может быть отброшена. Попробуйте сами.

create table test1 (col1 int)
go
insert test1 values(3)
begin tran
truncate table test1
select * from test1
rollback tran
select * from test1

В SQL Server TRUNCATE регистрируется, он просто не регистрируется таким подробным способом, как DELETE регистрируется. Я считаю, что это называется минимально зарегистрированной операцией. Эффективно страницы данных по-прежнему содержат данные, но их экстенты отмечены для удаления. Пока страницы данных все еще существуют, вы можете отменить усечение. Надеюсь, это полезно. Мне было бы интересно узнать результаты, если кто-то пробует это на MySQL.

Ответ 5

Для MySql 5, использующего InnoDb в качестве механизма хранения, TRUNCATE действует так же, как DELETE без предложения WHERE: т.е. для больших таблиц требуется возраст, потому что он удаляет строки один за другим. Это изменяется в версии 6.x.

см.

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

для информации 5.1 (строка за строкой с InnoDB) и

http://blogs.mysql.com/peterg/category/personal-opinion/

для изменений в 6.x

Замечание редактора

Этот ответ явно противоречит документации MySQL:

"Для таблицы InnoDB до версии 5.0.3 InnoDB обрабатывает TRUNCATE TABLE, удаляя строки один за другим. Начиная с MySQL 5.0.3, удаление строки за строкой используется только в том случае, если существуют ограничения FOREIGN KEY, которые ссылаются на таблицу Если ограничений FOREIGN KEY нет, InnoDB выполняет быструю усечку, отбрасывая исходную таблицу и создавая пустую с тем же определением, которая намного быстрее, чем удаление строк один за другим."

Ответ 6

Truncate находится на уровне таблицы, а Delete - на уровне строки. Если вы переведете это на sql в другом синтаксисе, truncate будет:

DELETE * FROM table

таким образом удаляя все строки одновременно, в то время как оператор DELETE (в PHPMyAdmin) выглядит следующим образом:

DELETE * FROM table WHERE id = 1
DELETE * FROM table WHERE id = 2

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