Всем привет, с вами ваш писатель-программист и сегодня мы поговорим о такой штуке как "Архивация". Далеко в дебри не пойдем, предложу вам не самый эффективный в мире подход, но очень даже неплохой!
Кстати, читайте мою новую книгу: ссылка на Литнет
Зачем это нужно?
Представьте, что у вас есть таблица, в которой каждый день появляется 100 000 новых записей. Через год в ней будет 36 миллионов записей. MySQL начнет "тормозить" на сложных аналитических запросах, а удалять старые данные нельзя — они нужны для отчетности.
Решение: разделить данные по времени:
MySQL — только сегодняшние и будущие записи (мало, быстро)
ClickHouse — все остальные записи (много, но ClickHouse создан для больших объемов) Лекцию про ClickHouse читать не буду, у них обширная документация, можете почитать ее (ссылка на документацию)
Как это работает?
Время: Прошлое Сегодня Будущее ↓ ↓ ↓ Хранилище: ClickHouse ←→ MySQL ←→ MySQL (только чтение) (чтение/запись) (чтение/запись) ↑_______________________↑ Ежедневная миграция
Часть 1. Структура данных
1.1. Почему таблицы отличаются?
MySQL — транзакционная база. Здесь важны: целостность, блокировки, внешние ключи, точечные обновления.
ClickHouse — аналитическая база. Здесь важны: скорость сканирования больших данных, сжатие, колоночное хранение.
Поэтому структуры немного отличаются:
-- MySQL: с индексами для быстрого поиска по ID и дате CREATE TABLE `records` ( `id` char(32) NOT NULL PRIMARY KEY, -- Быстрый поиск по ключу `code` varchar(50) NOT NULL, `number` varchar(20) NOT NULL, `record_date` date NOT NULL, -- Отдельно дата и время `record_time` datetime NOT NULL, -- для удобства фильтрации `hour` tinyint(3) unsigned NOT NULL, -- Денормализация для частых запросов `is_completed` tinyint(1) NOT NULL, `type_id` int(11) DEFAULT NULL, `value` int(11) DEFAULT NULL, INDEX idx_date (`record_date`), -- Индекс для фильтрации по дате INDEX idx_number (`number`) -- Индекс для поиска по номеру ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ClickHouse: колоночное хранение, сортировка по дате CREATE TABLE records ( `id` String, `code` String, `number` String, `record_date` Date, `record_time` DateTime, `hour` UInt8, `is_completed` UInt8, -- В ClickHouse нет bool, используется UInt8 `type_id` Nullable(Int32), -- Nullable для опциональных полей `value` Nullable(Int32) ) ENGINE = MergeTree -- Основной движок ClickHouse PARTITION BY toYYYYMM(record_date) -- Разбиваем по месяцам для быстрого удаления старых партиций PRIMARY KEY (id) -- Первичный ключ для сортировки ORDER BY (id, record_date, record_time); -- Порядок хранения на диске
Объяснение полей ClickHouse:
PARTITION BY— данные физически разбиваются на папки по месяцам. Это позволяет быстро удалять старые данные (просто удалить папку)ORDER BY— данные на диске сортируются сначала поid, потом по дате. Это ускоряет запросы с фильтрацией по этим полямUInt8вместоbool— в ClickHouse нет булева типа, используется 8-битное число
1.2. Таблица-источник для миграции
ClickHouse умеет подключаться к MySQL напрямую через специальный движок таблиц:
CREATE TABLE records_source ENGINE = MySQL('host:3306', -- Адрес MySQL 'database', -- Имя базы 'records', -- Имя таблицы 'user', -- Пользователь 'password'); -- Пароль
Это не копия данных, а "окно" в MySQL. Когда вы делаете SELECT из этой таблицы, ClickHouse реально ходит в MySQL и тянет данные оттуда. Удобно для миграции, но медленно для частых запросов.
Часть 2. Работа с сервером
2.1. Устанавливаем все, что нужно
Шаг 1. Установка MySQL клиента
# Установить MySQL клиент sudo apt install -y mysql-client # Проверить, что установилось which mysql # Должны увидеть: /usr/bin/mysql # Проверить версию mysql --version # Должны увидеть что-то вроде: mysql Ver 8.0.xx
Шаг 2. Подготовка к установке ClickHouse клиента
# Установить вспомогательные пакеты sudo apt install -y apt-transport-https ca-certificates curl gnupg
Шаг 3. Добавление репозитория ClickHouse
# Скачать и добавить ключ репозитория curl -fsSL https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/clickhouse.gpg # Добавить репозиторий в список источников echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
Шаг 4. Установка ClickHouse клиента
# Обновить список пакетов (теперь с новым репозиторием) sudo apt update # Установить ClickHouse клиент sudo apt install -y clickhouse-client # Проверить, что установилось which clickhouse-client # Должны увидеть: /usr/bin/clickhouse-client # Проверить версию clickhouse-client --version # Должны увидеть: ClickHouse client version 24.xx.x.x
Шаг 5. Проверка установленных программ
# Убедиться, что оба клиента доступны echo "MySQL: $(mysql --version)" echo "ClickHouse: $(clickhouse-client --version)"
Супер, полдела сделано!
2.2. Скрипт миграции
Миграцию будем делать через CRONу и баш скрипт, чтобы сервер сам вызывал процедуру, а мы и быть забыли, что оно вот так у нас там работает.
1. Создаем скрипт
nano /path/to/migrate.sh
2. Что пишем в файл:
#!/bin/bash # Параметры подключения MYSQL_HOST="localhost" MYSQL_USER="user" MYSQL_PASSWORD="password" MYSQL_DB="database" CLICKHOUSE_USER="default" CLICKHOUSE_PASSWORD="password" CLICKHOUSE_DB="database" echo "=== НАЧАЛО МИГРАЦИИ $(date) ===" # Копируем данные за вчера из MySQL в ClickHouse /usr/bin/clickhouse-client -u $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --query " INSERT INTO $CLICKHOUSE_DB.records SELECT * FROM $CLICKHOUSE_DB.records_source WHERE record_date < CURDATE()" # <-- Вчерашние и более старые данные # Почему именно так? # CURDATE() в MySQL возвращает сегодняшнюю дату (2024-01-15 00:00:00) # Значит, мы берем всё, что строго меньше сегодня — т.е. вчера и раньше # Сегодняшние данные остаются в MySQL для оперативной работы if [ $? -eq 0 ]; then # Проверяем, успешно ли выполнился предыдущий шаг echo "Миграция в ClickHouse выполнена успешно" # Удаляем перенесенные данные из MySQL /usr/bin/mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST $MYSQL_DB -e " DELETE FROM records WHERE record_date < CURDATE();" # Тот же принцип: удаляем всё, что строго меньше сегодня echo "Очистка MySQL выполнена успешно" else echo "ОШИБКА при миграции" exit 1 # Выходим с ошибкой, чтобы cron знал о проблеме fi echo "=== МИГРАЦИЯ ЗАВЕРШЕНА $(date) ==="
3. Делаем скрипт исполняемым
chmod +x /path/to/migrate.sh
4. Проверим, что наша миграция работает
./path/to/migrate.sh
5. Если всё без ошибок, то радуемся!
2.3. Запуск по расписанию (cron)
Как открыть кроны
crontab -e
В открывшемся файле пишем нечто такое:
# Запуск каждый день в 3 часа ночи 0 3 * * * /path/to/migrate.sh >> /var/log/migration.log 2>&1
Объяснение cron:
0— минута (0)3— час (3 ночи)*— каждый день месяца*— каждый месяц*— каждый день недели>> /var/log/migration.log 2>&1— перенаправляем весь вывод (и ошибки) в лог-файл
Часть 3. Работа с данными в приложении
Теперь попробуем поработать с двумя разными бд одновременно. Пример на c#
3.1. Модель данных
public class Record { public string Id { get; set; } = null!; public string Code { get; set; } = null!; public string Number { get; set; } = null!; public DateTime RecordDate { get; set; } public DateTime RecordTime { get; set; } public byte Hour { get; set; } public bool IsCompleted { get; set; } public int? TypeId { get; set; } public int? Value { get; set; } }
Что важно?
Помните, у вас одна модель на две базы данных, а значит типы полей должны подходить и там и тут.
3.2. Контексты Entity Framework Core
MySQL контекст:
public class MySqlContext : DbContext { public DbSet<Record> Records { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseMySql( "server=localhost;database=database;user=user;password=password", new MySqlServerVersion(new Version(8, 0, 0)) ); } }
ClickHouse контекст:
public class ClickHouseContext : DbContext { public DbSet<Record> Records { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseClickHouse( "Host=localhost;Port=8123;Database=database;User=default;Password=password" ); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Record>(entity => { // Указываем точные типы ClickHouse entity.Property(e => e.IsCompleted) .HasColumnType("UInt8"); // bool в C# будет UInt8 в ClickHouse entity.Property(e => e.TypeId) .HasColumnType("Nullable(Int32)"); entity.Property(e => e.Value) .HasColumnType("Nullable(Int32)"); }); } }
3.3. Репозиторий (самое важное место)
Чтобы сервис не знал, откуда мы берем данные, давайте вынесем всё в репозиторий.
public interface IRecordRepository { Task<List<Record>> GetByFilter(RecordFilter filter); Task<Record?> GetById(string id); Task Add(Record record); Task Update(Record record); } public class RecordRepository : IRecordRepository { private readonly MySqlContext _mysql; private readonly ClickHouseContext _clickHouse; private readonly DateTime _today; // Кэшируем сегодняшнюю дату public RecordRepository(MySqlContext mysql, ClickHouseContext clickHouse) { _mysql = mysql; _clickHouse = clickHouse; _today = DateTime.Today; // Вычисляем один раз при создании репозитория } /// <summary> /// Получение записей по фильтру из соответствующих источников /// </summary> public async Task<List<Record>> GetByFilter(RecordFilter filter) { var result = new List<Record>(); // Шаг 1: Определяем, какие источники данных нужны для этого периода var (needMysql, needClickHouse) = GetDataSources(filter.DateFrom, filter.DateTo); // Шаг 2: Получаем данные из MySQL (сегодня и будущее) if (needMysql) { // Важно: всегда фильтруем по дате на уровне базы данных! var mysqlQuery = _mysql.Records .Where(r => r.RecordDate >= _today) // Только сегодня+ .ApplyFilter(filter); // Остальные фильтры result.AddRange(await mysqlQuery.ToListAsync()); } // Шаг 3: Получаем данные из ClickHouse (прошлое) if (needClickHouse) { var clickHouseQuery = _clickHouse.Records .Where(r => r.RecordDate < _today) // Только прошлое .ApplyFilter(filter); result.AddRange(await clickHouseQuery.ToListAsync()); } // Шаг 4: Возвращаем объединенный результат return result; } /// <summary> /// Получение записи по ID (ищем в MySQL, потом в ClickHouse) /// </summary> public async Task<Record?> GetById(string id) { // Сначала в MySQL (более вероятно для часто запрашиваемых записей) var record = await _mysql.Records .FirstOrDefaultAsync(r => r.Id == id); if (record != null) return record; // Если не нашли, ищем в ClickHouse return await _clickHouse.Records .FirstOrDefaultAsync(r => r.Id == id); } /// <summary> /// Добавление записи (только в MySQL) /// </summary> public async Task Add(Record record) { // Автоматически заполняем вычисляемые поля record.RecordDate = record.RecordTime.Date; record.Hour = (byte)record.RecordTime.Hour; await _mysql.Records.AddAsync(record); await _mysql.SaveChangesAsync(); } /// <summary> /// Обновление записи (только в MySQL, данные в ClickHouse неизменяемы) /// </summary> public async Task Update(Record record) { // Проверяем, что запись не в прошлом if (record.RecordDate < _today) throw new InvalidOperationException("Cannot update historical records"); _mysql.Records.Update(record); await _mysql.SaveChangesAsync(); } /// <summary> /// Определение нужных источников по периоду /// </summary> private (bool needMysql, bool needClickHouse) GetDataSources(DateTime? dateFrom, DateTime? dateTo) { // Если даты не указаны - берем все данные из обоих источников if (!dateFrom.HasValue && !dateTo.HasValue) return (true, true); // Определяем границы периода var minDate = dateFrom ?? DateTime.MinValue; // Если нет начала, берем "начало времен" var maxDate = dateTo ?? DateTime.MaxValue; // Если нет конца, берем "конец времен" // Логика: // MySQL нужен, если период заканчивается сегодня или позже // ClickHouse нужен, если период начинается до сегодня return ( needMysql: maxDate.Date >= _today, needClickHouse: minDate.Date < _today ); } } /// <summary> /// Extension methods для фильтрации (чтобы не дублировать код) /// </summary> public static class RecordQueryExtensions { public static IQueryable<Record> ApplyFilter( this IQueryable<Record> query, RecordFilter filter) { if (!string.IsNullOrEmpty(filter.Number)) query = query.Where(r => r.Number == filter.Number); if (filter.DateFrom.HasValue) query = query.Where(r => r.RecordDate >= filter.DateFrom.Value.Date); if (filter.DateTo.HasValue) { // ВАЖНО: используем строгое неравенство для корректной обработки времени var endDate = filter.DateTo.Value.Date.AddDays(1); query = query.Where(r => r.RecordDate < endDate); } if (filter.TimeFrom.HasValue) query = query.Where(r => r.RecordTime.TimeOfDay >= filter.TimeFrom.Value); if (filter.TimeTo.HasValue) query = query.Where(r => r.RecordTime.TimeOfDay <= filter.TimeTo.Value); if (filter.TypeId.HasValue) query = query.Where(r => r.TypeId == filter.TypeId); if (filter.IsCompleted.HasValue) query = query.Where(r => r.IsCompleted == filter.IsCompleted.Value); return query; } } /// <summary> /// Фильтр для запросов /// </summary> public class RecordFilter { public string? Number { get; set; } public DateTime? DateFrom { get; set; } public DateTime? DateTo { get; set; } public TimeSpan? TimeFrom { get; set; } public TimeSpan? TimeTo { get; set; } public int? TypeId { get; set; } public bool? IsCompleted { get; set; } }
Часть 4. Особенности и подводные камни
4.1. Неизменяемость исторических данных
Правило: Данные в ClickHouse считаются неизменяемыми.
Почему?
ClickHouse оптимизирован для вставки, а не для обновления
Обновление в ClickHouse — это фактически удаление + новая вставка
При больших объемах это очень медленно
Что делать, если нужно исправить ошибку в исторических данных?
Вариант А: Если данные еще не перенесены (сегодняшние) — исправляем в MySQL
Вариант Б: Если данные уже в ClickHouse:
Удаляем ошибочные записи из ClickHouse
Исправляем в MySQL (если там еще есть)
Запускаем миграцию заново для этих дат
-- Удаление из ClickHouse ALTER TABLE records DELETE WHERE record_date = '2024-01-10'; -- Исправление в MySQL (если данные еще там) UPDATE records SET value = 100 WHERE id = '...'; -- Повторная миграция (придется временно разрешить вставку в прошлое) INSERT INTO records SELECT * FROM records_source WHERE record_date = '2024-01-10';
4.4. Транзакционная целостность
Проблема: Миграция не транзакционна. Если скрипт упал после копирования в ClickHouse, но до удаления из MySQL:
До миграции: MySQL: [вчера, сегодня] ClickHouse: [] После копирования: MySQL: [вчера, сегодня] ClickHouse: [вчера] <-- СКОПИРОВАЛИ После падения: MySQL: [вчера, сегодня] ClickHouse: [вчера] Скрипт не дошел до удаления из MySQL
Результат: при следующем запуске данные за вчера скопируются снова -> дубликаты.
Решение: Добавить проверку на существование
INSERT INTO records SELECT * FROM records_source s WHERE s.record_date < CURDATE() AND NOT EXISTS ( -- Проверяем, что записи еще нет SELECT 1 FROM records r WHERE r.id = s.id )
Или можете поиграть с семейством движков MergeTree (ссылочка на документацию)
Заключение
Что мы получили?
Производительность: MySQL не забит историческими данными
Скорость аналитики: ClickHouse летает на больших объемах
Прозрачность: приложение не знает о двух базах
Надежность: данные не теряются, всегда есть копия
Что нужно помнить?
Исторические данные неизменяемы — продумайте процесс исправления ошибок
Миграция не транзакционна — защищайтесь от дубликатов
Разные типы данных — явно указывайте маппинг в EF Core
Главный принцип: MySQL — для работы, ClickHouse — для отчетов. И они отлично дополняют друг друга!
Ну всё, всем спасибо, всем пока. Жду комментарии знатоков!
