ClickHouse и MySql или как подружить две базы

Всем привет, с вами ваш писатель-программист и сегодня мы поговорим о такой штуке как "Архивация". Далеко в дебри не пойдем, предложу вам не самый эффективный в мире подход, но очень даже неплохой!

Кстати, читайте мою новую книгу: ссылка на Литнет

Зачем это нужно?

Представьте, что у вас есть таблица, в которой каждый день появляется 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:

• ORDER BY — данные на диске сортируются сначала по id, потом по дате. Это ускоряет запросы с фильтрацией по этим полям

• UInt8 вместо bool — в ClickHouse нет булева типа, используется 8-битное число

• PARTITION BY — данные физически разбиваются на папки по месяцам.

Важное уточнение о партиционировании:

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

Чтобы быстро удалить устаревшие данные, нужно использовать команду ALTER TABLE ... DROP PARTITION. Это атомарная операция, которая удаляет данные и метаданные корректно.

ALTER TABLE my_table DROP PARTITION '2023-01';

Если же стоит задача автоматической ротации данных по времени, лучше настроить TTL. Например, чтобы данные старше месяца удалялись автоматически:

CREATE TABLE ... ENGINE = MergeTree()
PARTITION BY toYYYYMM(date_column)
ORDER BY ...
TTL date_column + INTERVAL 1 MONTH;

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 — это фактически удаление + новая вставка

• При больших объемах это очень медленно и не рекомендуется

Важный нюанс, который мне подсказали в комментариях:

В статье я написала про исправление ошибок: "Удаляем ошибочные записи из ClickHouse, исправляем в MySQL, запускаем миграцию заново".

Это ошибка. И вот почему:

После того как данные уехали в ClickHouse, мы удаляем их из MySQL (см. скрипт миграции). То есть в MySQL этих строк уже нет. Если мы удалим что-то из ClickHouse, обратно вставить уже неоткуда — в MySQL данные только текущего дня, а за прошлые даты там пусто.

Что делать, если обнаружилась ошибка в исторических данных?

Вариантов, честно говоря, немного:

1. Смириться. Если ошибка не критичная для отчетности, проще оставить как есть. ClickHouse — это аналитическое хранилище, там допустима небольшая погрешность.

2. Править в ClickHouse напрямую. Использовать ALTER TABLE ... UPDATE, но это медленно и нагружает систему. Для точечных исправлений — ок, для массовых — нет.

3. Перелить партицию целиком из бекапа. Если есть бекап ClickHouse на нужную дату, можно восстановить партицию отдельно.

4. Менять подход. Например, хранить в MySQL не только сегодняшние данные, а данные за последние N дней, чтобы окно для исправлений было шире. Но тогда теряется смысл "разгрузки" MySQL.

Так что да, я тут дала нерабочий совет, спасибо комментаторам за внимательность. Исправляюсь.

4.2. Транзакционная целостность

Проблема: Миграция не транзакционна. Если скрипт упал после копирования в 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 (ссылочка на документацию)

4.3. Когда это реально нужно? (цифры и альтернативы)

В комментариях попросили добавить контекст про объемы и нагрузку, чтобы было понятно, когда такая архитектура оправдана, а когда — избыточна.

С чем работали в реальном проекте:

• Объем данных: MySQL рос на 2-3 ГБ в день (примерно 5-7 млн новых записей)

• Проблема: хранить больше месяца в MySQL стало больно — агрегатные запросы тормозили, бэкапы тяжелели, индексы раздувались

• Тип запросов: около 70% аналитики — сканирование больших диапазонов (отчеты по месяцам, суммаризация), 30% — точечные выгрузки по ID или номеру

• Ресурсы ClickHouse: хватило виртуалки с 4 vCPU и 16 GB RAM на 200 ГБ данных (ClickHouse сжал данные в 3-4 раза сильнее MySQL)

Почему не оставить всё в MySQL и не мучиться?

Альтернатива	Почему не подошло
Партиционирование в MySQL	Пробовали. Старые партиции дропать удобно, но агрегатные запросы всё равно сканировали всю таблицу целиком. Плюс блокировки при ротации партиций начинали мешать оперативной работе.
Реплика MySQL для отчетов	Просто перенесли бы тормоза на другую машину. Скорость сложных агрегатов не выросла бы — InnoDB не для того сделан.
Materialized Views в MySQL	Не спасают, когда данных много. Они не ускоряют сканирование, только предрасчет, но обновление в реальном времени тоже нагрузка.

Materialized Views в ClickHouse — вот что реально помогло. Мы льем сырые данные, а ClickHouse сам перекладывает их в агрегирующие таблицы для дашбордов. Это дает прирост скорости в разы. (Ну и как я говорила, есть варианты намного оптимальнее, но под задачу компании подошел именно такой)

Стоимость такого решения (временная и эмоциональная):

• Временная: пришлось написать и отладить конвейер синхронизации (C# + SQL)

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

Итог: такая архитектура оправдана, если:

• аналитические запросы реально душат операционную базу

• скорость агрегации критична (у нас ускорение с 40 секунд до 0.3 секунды на типовых выборках)

Если у вас 10 000 записей в день и отчеты строятся пару минут — теперь система справится без плясок с бубном.

Заключение

Что мы получили?

Производительность: MySQL не забит историческими данными
Скорость аналитики: ClickHouse летает на больших объемах
Прозрачность: приложение не знает о двух базах
Надежность: данные не теряются, всегда есть копия

Что нужно помнить?

Исторические данные неизменяемы — продумайте процесс исправления ошибок
Миграция не транзакционна — защищайтесь от дубликатов
Разные типы данных — явно указывайте маппинг в EF Core

Главный принцип: MySQL — для работы, ClickHouse — для отчетов. И они отлично дополняют друг друга!

Ну всё, всем спасибо, всем пока. Жду комментарии знатоков!