В чем разница между DELETE и TRUNCATE?

Разница между DELETE и TRUNCATE

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

DELETE — удаление строк

DELETE удаляет строки одну за одной на основе условия WHERE:

DELETE FROM users WHERE id > 100;
DELETE FROM orders;  -- Удалит ВСЕ строки

Характеристики DELETE:

• Удаляет построчно
• Может быть WHERE условие
• Создает записи в журнале транзакций (undo log)
• Медленно для больших таблиц
• Можно откатить (ROLLBACK)
• Срабатывают триггеры DELETE
• Счётчик AUTOINCREMENT НЕ сбрасывается

# DELETE с WHERE условием
import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute('INSERT INTO users VALUES (1, "Alice")')
cursor.execute('INSERT INTO users VALUES (2, "Bob")')
cursor.execute('INSERT INTO users VALUES (3, "Charlie")')

# Удалим только одного пользователя
cursor.execute('DELETE FROM users WHERE id = 2')
conn.commit()

# Получим оставшихся
cursor.execute('SELECT * FROM users')
print(cursor.fetchall())  # [(1, 'Alice'), (3, 'Charlie')]

# AUTOINCREMENT НЕ сбросился
cursor.execute('INSERT INTO users (name) VALUES ("David")')
print(cursor.lastrowid)  # 4 (не 1)

TRUNCATE — очистка таблицы

TRUNCATE удаляет ВСЕ строки из таблицы как одну операцию:

TRUNCATE TABLE users;
TRUNCATE TABLE orders;

Характеристики TRUNCATE:

• Удаляет все строки сразу (операция на уровне DDL, не DML)
• БЕЗ WHERE условия (нельзя выбрать, какие строки удалить)
• Минимум логирования (очень быстро)
• Сложнее откатить (в некоторых БД)
• НЕ срабатывают триггеры DELETE
• Счётчик AUTOINCREMENT СБРАСЫВАЕТСЯ (в большинстве БД)

-- TRUNCATE в PostgreSQL
TRUNCATE TABLE users;

-- В MySQL
TRUNCATE TABLE users;

-- В SQLite (нет TRUNCATE, используют DELETE или оптимизированный DELETE)
DELETE FROM users;

Таблица сравнения

Примеры на разных БД

PostgreSQL:

-- DELETE
DELETE FROM users WHERE created_at < '2020-01-01';

-- TRUNCATE
TRUNCATE TABLE users;  -- Сбросит AUTOINCREMENT
TRUNCATE TABLE users RESTART IDENTITY;  -- Явный сброс

-- Откатываемость
BEGIN;
TRUNCATE TABLE users;
ROLLBACK;  -- Можно откатить

MySQL:

-- DELETE
DELETE FROM users WHERE status = 'inactive';

-- TRUNCATE
TRUNCATE TABLE users;

-- Внимание: в MySQL TRUNCATE не срабатывает в транзакции
-- Его нельзя откатить!

SQLite:

-- SQLite не имеет TRUNCATE, но есть оптимизированный DELETE
DELETE FROM users;  -- Очень быстро в SQLite

-- Или вручную сброс autoincrement
DELETE FROM sqlite_sequence WHERE name = 'users';

Производительность: реальный пример

import time
import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)')

# Вставим 1 млн строк
print("Вставляем 1 млн строк...")
for i in range(1_000_000):
    cursor.execute('INSERT INTO test (value) VALUES (?)', (f'value_{i}',))
conn.commit()

# DELETE
conn2 = sqlite3.connect(':memory:')
cursor2 = conn2.cursor()
cursor2.execute('CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)')
for i in range(1_000_000):
    cursor2.execute('INSERT INTO test (value) VALUES (?)', (f'value_{i}',))
conn2.commit()

print("\nУдаление с DELETE FROM...")
start = time.time()
cursor2.execute('DELETE FROM test')
conn2.commit()
print(f"DELETE: {time.time() - start:.2f}сек")

# TRUNCATE (SQLite не имеет, но покажу логику)
print("\nТRUNCATE был бы быстрее (1-2мс)")

Когда использовать DELETE

# DELETE используй, когда:
# 1. Нужно удалить условные данные
cursor.execute('DELETE FROM logs WHERE created_at < ? AND level = "DEBUG"', 
               (old_date,))

# 2. Нужна откатываемость
try:
    cursor.execute('DELETE FROM important_data WHERE id = ?', (1,))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Ошибка: {e}")

# 3. Нужно срабатывать триггеры
cursor.execute('DELETE FROM users WHERE status = "deleted"')
# Триггер может логировать удаления

Когда использовать TRUNCATE

-- TRUNCATE используй, когда:
-- 1. Нужно очистить таблицу полностью и быстро
TRUNCATE TABLE audit_logs;  -- Удалит 10млн строк за 1мс

-- 2. Нужно сбросить AUTOINCREMENT
TRUNCATE TABLE sessions RESTART IDENTITY;

-- 3. Это служебная таблица, где откатываемость не критична
TRUNCATE TABLE temp_calculations;

-- 4. Нужно освободить место на диске
TRUNCATE TABLE large_table;  -- Физически освободит место

Практический пример: архивирование логов

import sqlite3
from datetime import datetime, timedelta

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

# Архивируем старые логи
archive_date = datetime.now() - timedelta(days=30)

# Сначала переместим в архив
cursor.execute('''
    INSERT INTO logs_archive
    SELECT * FROM logs WHERE created_at < ?
''', (archive_date,))
conn.commit()

# Затем удалим из основной таблицы
cursor.execute('DELETE FROM logs WHERE created_at < ?', (archive_date,))
conn.commit()

print(f"Заархивировано логов: {cursor.rowcount}")

# После этого можно очистить VACUUM для освобождения места
cursor.execute('VACUUM')

Вывод

DELETE:

• Используй для селективного удаления (с WHERE)
• Когда нужна откатываемость
• Когда должны срабатывать триггеры
• Медленнее, но безопаснее

TRUNCATE:

• Используй для полной очистки таблицы
• Когда нужна максимальная скорость
• Когда нужно сбросить AUTOINCREMENT
• Не забывай о отсутствии отката в некоторых БД