MySQL: Архивирование старых данных

Архивирование старых записей в отдельные таблицы помогает сдерживать рост боевых таблиц, ускорять запросы и удешевлять хранение, не теряя историю. Ниже — краткая шпаргалка, ориентированная на MySQL/MariaDB.

Когда имеет смысл архивировать данные

• Таблица растёт бесконечно: логи, клики, события, истории операций, мониторинг.
• Для онлайн-работы нужны только последние N месяцев/лет, остальное храним на всякий случай.
• Запросы к старым данным редкие, аналитические, не участвуют в боевых сценариях.

Общая схема архивирования

• Есть основная таблица, например events.
• Создаётся архивная таблица того же формата: events_archive.
• Определяется горизонт хранения в основной: последний год, последние 2 года и т.п.
• Периодически:
  • копируем строки старше порога в архив;
  • удаляем их из основной таблицы;
  • при необходимости используем VIEW или UNION ALL для выборок за всю историю.

Готовые SQL-шаблоны

Создание архивной таблицы

CREATE TABLE 
	events_archive
LIKE 
	events;

Перелив старых данных по фиксированной дате

Пример: переносим всё, что старше 1 января 2022 года.

INSERT INTO 
	events_archive
SELECT 
	*
FROM 
	events
WHERE 
	created_at < '2022-01-01 00:00:00';

DELETE FROM 
	events
WHERE 
	created_at < '2022-01-01 00:00:00';

Перелив по подвижному горизонту (например, старше 2 лет)

SET @cutoff := DATE_SUB(CURDATE(), INTERVAL 2 YEAR);

INSERT INTO 
	events_archive
SELECT 
	*
FROM 
	events
WHERE 
	created_at < @cutoff;

# Удаляем старые записи
DELETE FROM 
	events
WHERE 
	created_at < @cutoff;

# Или удаляем партиями, пока не исчезнут все строки старше порога.
SET @cutoff := DATE_SUB(CURDATE(), INTERVAL 2 YEAR);

DELETE FROM 
	events
WHERE 
	created_at < @cutoff
LIMIT 
	50000;
-- выполнять циклом, пока ROW_COUNT() > 0

VIEW для чтения всей истории сразу

Чтобы код не знал про разделение на основную и архивную таблицы.

CREATE OR REPLACE VIEW events_all AS
SELECT * FROM events
UNION ALL
SELECT * FROM events_archive;

Архивирование по годам для более крупных проектов

Когда данных становится очень много (миллионы и десятки миллионов строк в год), удобнее делать отдельные таблицы по годам. Это упрощает обслуживание и даёт более предсказуемую производительность:

• проще переносить старые годовые таблицы на отдельный сервер или в другое хранилище;
• можно быстро дропнуть целый год, если он больше не нужен;
• запросы по конкретному году работают только с одной таблицей.

Простой пример: текущий год храним в events, прошлые — в таблицах вида events_2023, events_2022 и т.д.

# Создаём таблицу для года 2023 на основе основной структуры
CREATE TABLE 
	events_2023
LIKE 
	events;

# Переносим все записи за 2023 год в отдельную годовую таблицу
INSERT INTO 
	events_2023
SELECT 
	*
FROM 
	events
WHERE 
	YEAR(created_at) = 2023;

# Удаляем перенесённые записи из основной таблицы
DELETE FROM 
	events
WHERE 
	YEAR(created_at) = 2023;

Для выборок за несколько лет можно сделать вьюху или собирать данные через UNION ALL, например:

CREATE OR REPLACE VIEW events_last3years AS
SELECT * FROM events          -- текущий год
UNION ALL
SELECT * FROM events_2024
UNION ALL
SELECT * FROM events_2023;