Зачем использовать TRUNCATE и DROP?

В системе, в которой я работаю, есть много хранимых процедур и SQL-скриптов, которые используют временные таблицы. После использования этих таблиц рекомендуется удалить их.

Многие из моих коллег (почти все из которых гораздо более опытные, чем я) обычно делают это:

TRUNCATE TABLE #mytemp
ТАБЛИЦА DROP #mytemp

Обычно я использую одиночный DROP TABLE в моих сценариях.

Есть ли веская причина для выполнения TRUNCATE непосредственно перед DROP?

93 голоса | спросил user606723 27 J000000Wednesday11 2011, 21:36:39

4 ответа


122

Нет.

TRUNCATE и DROP почти идентичны поведению и скорости, поэтому TRUNCATE прямо перед DROP просто не нужно.


Примечание. Я написал этот ответ с точки зрения SQL Server и предположил, что он будет применяться в равной степени к Sybase. Похоже, что это не совсем так.

Примечание. Когда я впервые опубликовал этот ответ, было несколько других высоко оцененных ответов, включая принятый тогда ответ, - который сделал несколько ложных утверждений вроде: TRUNCATE не регистрируется ; TRUNCATE нельзя отменить; TRUNCATE быстрее, чем DROP; и др.

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


Есть пара популярных фальсификаций, которые распространены даже среди опытных администраторов баз данных, которые могли мотивировать этот шаблон TRUNCATE-then-DROP. Это:

  • Миф : TRUNCATE не регистрируется, поэтому его нельзя отменить.
  • Миф : TRUNCATE быстрее, чем DROP.

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

TRUNCATE is зарегистрирован, и он можно отменить.

  • TRUNCATE является зарегистрированной операцией, поэтому он может откат . Просто заверните его в транзакцию.

    USE [tempdb];
    SET NOCOUNT ON;
    
    CREATE TABLE truncate_demo (
        что угодно VARCHAR (10)
    );
    
    INSERT INTO truncate_demo (что угодно)
    ЦЕННОСТИ ('log this');
    
    НАЧАТЬ ПЕРЕВОД;
        TRUNCATE TABLE truncate_demo;
    СДЕЛКА ROLLBACK;
    
    ВЫБРАТЬ *
    FROM truncate_demo;
    
    DROP TABLE truncate_demo;
    

    Обратите внимание, что это не верно для Oracle , Несмотря на то, что он был зарегистрирован и защищен функциональными возможностями отладки и повторного использования Oracle, TRUNCATE и другие операторы DDL не могут отказываться от пользователя, потому что Oracle выпускает неявно фиксирует непосредственно до и после всех операторов DDL.

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

    Сравните это с DELETE. Если вы DELETE все строки в таблице и зафиксируете транзакцию, вы теоретически можете найти удаленные строки в журнале транзакций и восстановить их там. Это потому, что DELETE записывает каждую удаленную строку в журнал транзакций. Для больших таблиц это будет значительно медленнее, чем TRUNCATE.

DROP работает так же быстро, как TRUNCATE.

  • Как TRUNCATE, DROP - операция с минимальным протоколом. Это означает, что DROP также может быть откат. Это также означает он работает точно так же как TRUNCATE. Вместо того, чтобы удалять отдельные строки, DROP помещает соответствующие страницы данных как нераспределенные и дополнительно маркирует метаданные таблицы как удаленные .
  • Поскольку TRUNCATE и DROP работают точно так же, они работают так же быстро, как и один другой. Нет смысла в TRUNCATE -вставить таблицу до DROP -ing. Запустить этот демонстрационный скрипт в вашем экземпляре разработки, если вы мне не верите.

    На моей локальной машине с теплым кешем я получаю следующие результаты:

    количество строк в таблице: 134 217 728
    
    run # продолжительность транзакции (мс)
          TRUNCATE TRUNCATE, затем DROP DROP
    ==========================================
    01 0 1 4
    02 0 39 1
    03 0 1 1
    04 0 2 1
    05 0 1 1
    06 0 25 1
    07 0 1 1
    08 0 1 1
    09 0 1 1
    10 0 12 1
    ------------------------------------------
    0,08,4 1,3
    

    Итак, для таблицы строк 134 million оба DROP и TRUNCATE эффективно не занимают никакого времени. (В холодном кэше они занимают около 2-3 секунд для первого запуска или два.) Я также считаю, что более высокая средняя продолжительность для операции TRUNCATE, а затем DROP для загрузки изменений на моем локальном компьютере и not , потому что комбинация каким-то волшебным образом на порядок хуже, чем отдельные операции. В конце концов, это почти то же самое.

    Если вас интересует более подробная информация об издержках на ведение журнала этих операций, У Мартина есть прямое объяснение .

ответил Nick Chammas 9 32011vEurope/Moscow11bEurope/MoscowWed, 09 Nov 2011 01:39:19 +0400 2011, 01:39:19
49

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

Посмотрев на отдельные записи журнала, версия TRUNCATE ... DROP почти идентична версии DROP, кроме этих дополнительных записей.

+ ----------------- + --------------- + --------- ---------------- +
| Операция | Контекст | AllocUnitName |
+ ----------------- + --------------- + --------------- ---------- +
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_LOCK_XACT | LCX_NULL | NULL |
+ ----------------- + --------------- + --------------- ---------- +

Итак, первая версия TRUNCATE в конечном итоге тратит немного усилий на выполнение некоторых обновлений для различных системных таблиц следующим образом

  • Обновить rcmodified для всех столбцов таблицы в sys.sysrscols
  • Обновить rcrows в sysrowsets
  • Zero out pgfirst, pgroot, pgfirstiam, pcused, pcdata, pcreserved в sys.sysallocunits

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

Ниже приведен полный отрыв журнала, выполняемый TRUNCATE vs DROP. Я также добавил DELETE для сравнения.

+ ------------------- + ------------------- + --- ----------------- + ------------------ + ----------- + - -------------- + ------------- + ------------------ + - --------- + --------------- + ------------- +
| | | | Байты | Граф |
+ ------------------- + ------------------- + --------- ----------- + ------------------ + ----------- + ------- -------- + ------------- + ------------------ + -------- --- + --------------- + ------------- +
| Операция | Контекст | AllocUnitName | Усечение /падение | Только Drop | Усекать только | Удалить только | Усечение /падение | Только Drop | Усекать только | Удалить только |
+ ------------------- + ------------------- + --------- ----------- + ------------------ + ----------- + ------- -------- + ------------- + ------------------ + -------- --- + --------------- + ------------- +
| LOP_BEGIN_XACT | LCX_NULL | | 132 | 132 | 132 | 132 | 1 | 1 | 1 | 1 |
| LOP_COMMIT_XACT | LCX_NULL | | 52 | 52 | 52 | 52 | 1 | 1 | 1 | 1 |
| LOP_COUNT_DELTA | LCX_CLUSTERED | Системная таблица | 832 | | 832 | | 4 | | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | Системная таблица | 2864 | 2864 | | | 22 | 22 | | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | T | | | | 8108000 | | | | 1000 |
| LOP_HOBT_DDL | LCX_NULL | | 108 | 36 | 72 | | 3 | 1 | 2 | |
| LOP_LOCK_XACT | LCX_NULL | | 336 | 296 | 40 | | 8 | 7 | 1 | |
| LOP_MODIFY_HEADER | LCX_PFS | Неизвестный блок Alloc | 76 | 76 | | 76 | 1 | 1 | | 1 |
| LOP_MODIFY_ROW | LCX_CLUSTERED | Системная таблица | 644 | 348 | 296 | | 5 | 3 | 2 | |
| LOP_MODIFY_ROW | LCX_IAM | T | 800 | 800 | 800 | | 8 | 8 | 8 | |
| LOP_MODIFY_ROW | LCX_PFS | T | 11736 | 11736 | 11736 | | 133 | 133 | 133 | |
|LOP_MODIFY_ROW | LCX_PFS | Неизвестный блок Alloc | 92 | 92 | 92 | | 1 | 1 | 1 | |
| LOP_SET_BITS | LCX_GAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_IAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_PFS | Системная таблица | 896 | 896 | | | 16 | 16 | | |
| LOP_SET_BITS | LCX_PFS | T | | | | 56000 | | | | 1000 |
| LOP_SET_BITS | LCX_SGAM | Неизвестный блок Alloc | 168 | 224 | 168 | | 3 | 4 | 3 | |
+ ------------------- + ------------------- + --------- ----------- + ------------------ + ----------- + ------- -------- + ------------- + ------------------ + -------- --- + --------------- + ------------- +
| Всего | | | 36736 | 35552 | 32220 | 8164260 | 456 | 448 | 406 | 2003 |
+ ------------------- + ------------------- + --------- ----------- + ------------------ + ----------- + ------- -------- + ------------- + ------------------ + -------- --- + --------------- + ------------- +

Тест проводился в базе данных с полной моделью восстановления против 1000 строк таблицы с одной строкой на странице. Таблица занимает всего 1 004 страницы из-за страницы индекса корневого индекса и 3 страниц индекса промежуточного уровня.

8 из этих страниц - одностраничные распределения в смешанных экстентах с остатком, распределенным по 125 равномерным экстентам. Деление на 8 одиночных страниц отображается в виде записей журнала 8 LOP_MODIFY_ROW, LCX_IAM. 125 освобождений от деления в качестве LOP_SET_BITS LCX_GAM, LCX_IAM. Обе эти операции также требуют обновления на связанной странице PFS, следовательно, объединены записи 133 LOP_MODIFY_ROW, LCX_PFS. Затем, когда таблица фактически удалена, метаданные об этом должны быть удалены из различных системных таблиц, следовательно, записи в системной таблице 22 LOP_DELETE_ROWS (учитываются, как указано ниже)

+ ---------------------- + -------------- + ----- -------------- + ------------------- +
| Объект | Удаленные строки | Количество индексов | Удалить операции |
+ ---------------------- + -------------- + ----------- -------- + ------------------- +
| sys.sysallocunits | 1 | 2 | 2 |
| sys.syscolpars | 2 | 2 | 4 |
| sys.sysidxstats | 1 | 2 | 2 |
| sys.sysiscols | 1 | 2 | 2 |
| sys.sysobjvalues ​​| 1 | 1 | 1 |
| sys.sysrowsets | 1 | 1 | 1 |
| sys.sysrscols | 2 | 1 | 2 |
| sys.sysschobjs | 2 | 4 | 8 |
+ ---------------------- + -------------- + ----------- -------- + ------------------- +
| | | | 22 |
+ ---------------------- + -------------- + ----------- -------- + ------------------- +

Полный скрипт ниже

DECLARE @Results TABLE
(
    Тестирование int NOT NULL,
    Операция nvarchar (31) NOT NULL,
    Контекст nvarchar (31) NULL,
    AllocUnitName nvarchar (1000) NULL,
    SumLen int NULL,
    Cnt int NULL
)

DECLARE @I INT = 1

WHILE @I <= 4
НАЧАТЬ
IF OBJECT_ID ('T', 'U') IS NULL
     CREATE TABLE T (N INT PRIMARY KEY, Filler char (8000) NULL)

INSERT INTO T (N)
SELECT DISTINCT TOP 1000 число
FROM master..spt_values


КОНТРОЛЬНО-ПРОПУСКНОЙ ПУНКТ

DECLARE @allocation_unit_id BIGINT

SELECT @allocation_unit_id = alloc_unit_id
FROM sys.partitions AS p
       INNER JOIN sys.allocation_units AS a
         ON p.hobt_id = a.container_id
WHERE p.object_id = object_id ('T')

DECLARE @LSN NVARCHAR (25)
DECLARE @LSN_HEX NVARCHAR (25)

SELECT @LSN = MAX ([Текущий LSN])
FROM fn_dblog (null, null)


SELECT @ LSN_HEX =
        CAST (CAST (CONVERT (varbinary, SUBSTRING (@LSN, 1, 8), 2) AS INT) AS VARCHAR) + ':' +
        CAST (CAST (CONVERT (varbinary, SUBSTRING (@LSN, 10, 8), 2) AS INT) AS VARCHAR) + ':' +
        CAST (CAST (CONVERT (varbinary, SUBSTRING (@LSN, 19, 4), 2) AS INT) AS VARCHAR)

  НАЧАТЬ TRAN
    IF @I = 1
      НАЧАТЬ
          TRUNCATE TABLE T

          ТАБЛИЦА DROP T
      КОНЕЦ
    ELSE
      IF @I = 2
        НАЧАТЬ
            ТАБЛИЦА DROP T
        КОНЕЦELSE
        IF @I = 3
          НАЧАТЬ
              TRUNCATE TABLE T
          КОНЕЦ
      ELSE
        IF @I = 4
          НАЧАТЬ
              УДАЛИТЬ ОТ Т
          КОНЕЦ
  COMMIT

INSERT INTO @Results
SELECT @I,
       ДЕЛО
         КОГДА GROUPING (Operation) = 1 THEN 'Total'
         Операция ELSE
       КОНЕЦ,
       Контекст,
       ДЕЛО
         WHEN AllocUnitId = @allocation_unit_id THEN 'T'
         WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
         ELSE AllocUnitName
       КОНЕЦ,
       COALESCE (SUM ([Длина записи журнала]), 0) AS [Размер в байтах],
       COUNT (*) AS Cnt
FROM fn_dblog (@LSN_HEX, null) AS D
ГДЕ [Текущий LSN]> @LSN
GROUP BY GROUPING SETS ((Операция, Контекст,
       ДЕЛО
         WHEN AllocUnitId = @allocation_unit_id THEN 'T'
         WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
         ELSE AllocUnitName
       КОНЕЦ),())


SET @ I + = 1
КОНЕЦ

Операция SELECT,
       Контекст,
       AllocUnitName,
       AVG (CASE WHEN Testing = 1 THEN SumLen END) AS [Truncate /Drop Bytes],
       AVG (CASE WHEN Testing = 2 THEN SumLen END) AS [Drop Bytes],
       AVG (CASE WHEN Testing = 3 THEN SumLen END) AS [Truncate Bytes],
       AVG (CASE WHEN Testing = 4 THEN SumLen END) AS [Удалить байты],
       AVG (CASE WHEN Testing = 1 THEN Cnt END) AS [Truncate /Drop Count],
       AVG (CASE WHEN Testing = 2 THEN Cnt END) AS [Drop Count],
       AVG (CASE WHEN Testing = 3 THEN Cnt END) AS [Truncate Count],
       AVG (CASE WHEN Testing = 4 THEN Cnt END) AS [Удалить счет]
FROM @Results
Операция GROUP BY,
          Контекст,
          AllocUnitName
ORDER BY Operation, Context, AllocUnitName

ТАБЛИЦА DROP T
ответил Martin Smith 9 32011vEurope/Moscow11bEurope/MoscowWed, 09 Nov 2011 15:23:39 +0400 2011, 15:23:39
1

OK подумал, что я попытаюсь выполнить некоторые тесты, которые не полагались бы на «теплую кешировку», так что, надеюсь, они были бы более реалистичным тестом (также используя Postgres, чтобы увидеть, совпадает ли он с теми же характеристиками других ответы):

Мои тесты, использующие postgres 9.3.4 с большой-ой базой данных (надеюсь, достаточно большой, чтобы не вписываться в кеш RAM):

Использование этого сценария тестовой БД: https://gist.github.com/rdp/8af84fbb54a430df8fc0

с 10M строками:

truncate: 1763ms
падение: 2091 мс
усечение + падение: 1763 мс (усечение) + 300 мс (падение) (всего 2063 мс)
drop + recreate: 2063ms (drop) + 242ms (воссоздать)

с 100M строками:

truncate: 5516ms
усекать + падение: 5592 мс
падение: 5680 мс (в основном, тот же самый стадион)

Итак, из этого я предполагаю следующее: падение происходит примерно так же быстро (или быстрее), как усечение + падение (по крайней мере, для современных версий Postgres), однако, если вы планируете также поворачивать и воссоздавать таблицу, вы можете также придерживаться прямого усечения, которое быстрее, чем drop + rereate (имеет смысл). FWIW.

note 1: https://stackoverflow.com/questions/11419536/postgresql-truncation- speed /11423886 # 11423886 (говорит, что postgres 9.2 могут иметь более быструю усечку, чем предыдущие версии). Как всегда, сравните с вашей собственной системой, чтобы увидеть ее характеристики.

note 2: truncate можно откат в postgres, если в транзакции: http://www.postgresql.org/docs/8.4/static/SQL-truncate.html

note 3: truncate can, с небольшими таблицами, иногда медленнее, чем удаление: https: //stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886

ответил rogerdpack 21 J000000Monday14 2014, 20:20:51
-7

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

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

truncate может быть заключен в транзакцию (что было бы самым безопасным вариантом), что, конечно же, позволит вам отменить операцию.

ответил IAbstract 27 J000000Wednesday11 2011, 21:46:25

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132