Очистка MySQL таблицы: удалить все кроме последних N записей

Быстрый ответ

Для очистки таблицы в базе данных с сохранением N последних строк можно воспользоваться операцией DELETE, применяя при этом конструкцию ORDER BY для выбора наиболее свежих записей и LIMIT N, чтобы указать количество строк, которые нужно сохранить. Пример запроса представлен ниже:

DELETE FROM my_table
WHERE id NOT IN (
  SELECT id FROM (
    SELECT id FROM my_table
    ORDER BY updated_at DESC
    LIMIT N
  ) as fresh_data
);

Замените my_table наименованием вашей таблицы, updated_at — на имя колонки с датой и временем, которые вы хотите отслеживать, а N — на число последних строк, которые вы хотели бы сохранить.

Для выполнения более сложных сценариев или учета ограничений производительности можно применить другие методы, такие как комбинацию LEFT JOIN/IS NULL или метод двойного подзапроса.

Настройка метода двойного подзапроса

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

DELETE FROM my_table WHERE id <= (
  SELECT id FROM (
    SELECT id FROM my_table
    ORDER BY updated_at DESC
    LIMIT 1 OFFSET N-1
  ) as inner_table
);

Этот метод основывается на идентификации и удалении записей, которые старше N-й по свежести, таким образом сохраняются последние N записей. Предполагается, что id — это последовательный ключ.

Осторожное удаление данных

Перед выполнением операции DELETE рекомендуется проверить, какие строки будут удалены, с помощью оператора SELECT:

SELECT * FROM my_table WHERE id NOT IN (
  SELECT id FROM (
    SELECT id FROM my_table
    ORDER BY updated_at DESC
    LIMIT N
  ) as draft_table
);

Убедитесь, что результатом запроса SELECT будет удаление именно тех строк, которые вы планировали удалить.

Визуализация

Можно представить таблицу как коллекцию воздушных шариков. Ваша задача — оставить только N самых ярких шариков:

До:  🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈
Оставить N:     🎈🎈🎈🎈🎈 (N = 5)

SQL-запрос:

DELETE FROM BalloonInventory
WHERE id NOT IN (
  SELECT id FROM (
    SELECT id FROM BalloonInventory
    ORDER BY 'date' DESC
    LIMIT 5
  ) as mine
);

Результат:

После:                       🎈🎈🎈🎈🎈

Таким образом, в небесах остаются только самые яркие шарики! 🎈🎈🎈

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

Профессиональные рекомендации для оптимизации обработки и обеспечения безопасности

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

Использование LEFT JOIN для аккуратного удаления

Этот метод очень полезен при работе со сложными отношениями внешних ключей:

DELETE a FROM my_table a
LEFT JOIN (
  SELECT id FROM my_table
  ORDER BY updated_at DESC
  LIMIT N
) b ON a.id = b.id
WHERE b.id IS NULL;

Подробнее об этом расскажет наш спикер на видео

Создание временных таблиц с использованием подзапроса

Иногда целесообразнее использовать временные таблицы для оптимизации удаления, особенно когда вы работаете с MySQL:

DELETE FROM my_table WHERE id IN (
  SELECT id FROM (
    SELECT id FROM my_table
    ORDER BY updated_at DESC
    LIMIT N, 18446744073709551615
  ) as temp_table
);

Обращайте внимание на огромное число, используемое для выбора всех записей, кроме N последних. Адаптируйте это число в соответствии с возможностями вашей СУБД.

Полезные материалы

1. SQL: DELETE all except some rows — Обсуждение на Stackoverflow с решениями и идеями по теме SQL-запроса.
2. ROW_NUMBER (Transact-SQL) – SQL Server | Microsoft Learn — Подробное описание функции ROW_NUMBER() от Microsoft.
3. More indexes, slower DELETE — Разъяснение влияния индексации на производительность удаления в SQL.
4. SQL Server Common Table Expression (CTE) Basics – Simple Talk — Руководство по использованию CTE в SQL Server, особенно полезное при сложных операциях удаления.