Оптимизация хранения MQTT-данных - хранить только последние значения без истории

Чтобы снизить нагрузку на CPU при работе с базой данных я изменил логику записи в таблицу. Чтобы не сохранять все подряд, я просто обновляю последние данные топиков. Исторические данные для графиков и анализа тенденций сохраняю в другую таблицу и указываю какие топики хранить чтобы не перегружать таблицу.

Решение: уникальный ключ + ON DUPLICATE KEY UPDATE

Идея проста: для каждого топика MQTT должно существовать ровно одна строка с последним значением. При получении нового значения — обновляем существующую запись, а не создаём новую.

Шаг 1: Подготовка таблицы

Структура таблицы должна включать уникальный ключ по полю topic:

CREATE TABLE sensor_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    topic VARCHAR(255) NOT NULL,
    value VARCHAR(255) NOT NULL,
    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_topic (topic)
) ENGINE=InnoDB;

Шаг 2: SQL-запрос для вставки/обновления

INSERT INTO sensor_data (topic, value, timestamp)
VALUES ('zigbee2mqtt/sensor/temperature', '23.5', NOW())
ON DUPLICATE KEY UPDATE
    value = VALUES(value),
    timestamp = VALUES(timestamp);

Как это работает:

• Если topic ещё не существует → создаётся новая строка
• Если topic уже есть → обновляются поля value и timestamp

Шаг 3: Python-код (MQTT → MySQL)

def save_to_sensor_data(topic, value):
    try:
        with connection.cursor() as cursor:
            sql = """
                INSERT INTO sensor_data (topic, value, timestamp)
                VALUES (%s, %s, NOW())
                ON DUPLICATE KEY UPDATE
                    value = VALUES(value),
                    timestamp = VALUES(timestamp)
            """
            cursor.execute(sql, (topic, str(value)))
        connection.commit()
    except Exception as e:
        logging.error(f"Ошибка записи: {e}")

Миграция существующей таблицы

Если у вас уже есть таблица с тысячами строк — её можно быстро привести к нужному виду:

-- 1. Оставляем только последние значения для каждого топика
CREATE TABLE sensor_data_clean AS
SELECT 
    topic, value, timestamp
FROM (
    SELECT 
        topic, value, timestamp,
        ROW_NUMBER() OVER (PARTITION BY topic ORDER BY timestamp DESC) as rn
    FROM sensor_data
) ranked
WHERE rn = 1;

-- 2. Очищаем основную таблицу
TRUNCATE TABLE sensor_data;

-- 3. Вставляем только актуальные данные
INSERT INTO sensor_data (topic, value, timestamp)
SELECT topic, value, timestamp FROM sensor_data_clean;

-- 4. Удаляем временную таблицу
DROP TABLE sensor_data_clean;

-- 5. Добавляем уникальный ключ
ALTER TABLE sensor_data ADD UNIQUE KEY uniq_topic (topic);

Результат

Теперь таблица сама себя поддерживает в компактном состоянии:

• Новые топики → автоматически добавляются
• Существующие топики → автоматически обновляются
• История не накапливается → нет необходимости в cron-очистке

Когда использовать такое решение?

Этот подход идеален для:

• Панелей управления (Home Assistant, Grafana)
• Отображения текущих показаний датчиков
• Систем мониторинга в реальном времени
• Устройств с ограниченными ресурсами (Raspberry Pi, Orange Pi)

Если вам всё же нужна история — храните её в отдельной таблице с регулярной архивацией или ограничением количества записей.

Заключение

Простое добавление уникального ключа и использование ON DUPLICATE KEY UPDATE превращает хаотично растущую таблицу в элегантное хранилище актуальных данных. Это решение:

• Экономит место на диске
• Снижает нагрузку на CPU
• Ускоряет все запросы
• Упрощает логику приложения
• Работает стабильно годами без обслуживания

Идеально для IoT-проектов на Raspberry Pi!

Посетителей сегодня: 0

о блоге