Оптимизация базы данных IoT-панели: от дублей к стабильности

При работе с IoT-устройствами через Zigbee2MQTT и MySQL часто возникает соблазн просто сохранять каждое поле JSON-сообщения как отдельную строку. Это удобно для отображения, но ломает управление — особенно для многоканальных устройств вроде fish_power с каналами state_l1…state_l5.

Проблема

• Каждое обновление устройства добавляло новую строку в таблицу sensor_data.
• Накапливались дубликаты по топику (например, 50 строк для zigbee2mqtt/fish_power/state_l1).
• Невозможно было сделать UNIQUE KEY(topic) — MariaDB ругалась на дубли.
• Возникала ошибка: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations — из-за несовместимых кодировок между таблицами.

Решение

1. Чистка дублей

Использовал оконную функцию ROW_NUMBER() в MariaDB 10.11:

CREATE TABLE sensor_data_clean AS
SELECT topic, value, timestamp
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY topic ORDER BY timestamp DESC) AS rn
    FROM sensor_data
) t
WHERE rn = 1;

Это оставило только последнее значение для каждого топика.

2. Первичный ключ по топику

После чистки — сделал topic первичным ключом:

ALTER TABLE sensor_data ADD PRIMARY KEY (topic);

Теперь каждое устройство представлено ровно одной строкой — актуальное состояние всегда под рукой.

3. Единая кодировка

Оказалось, часть таблиц использовала utf8mb4_general_ci, а часть — utf8mb4_unicode_ci. Это ломало JOIN-запросы.

Привёл всё к единому стандарту:

ALTER TABLE automations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE sensor_history CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- и другие проблемные таблицы

Теперь все текстовые поля совместимы.

Итог

• ✅ Нет дублей в sensor_data
• ✅ Быстрые UPDATE/INSERT через ON DUPLICATE KEY UPDATE
• ✅ Стабильные JOIN-запросы без ошибок collation
• ✅ Возможность управлять многоканальными устройствами через один топик

Система стала быстрее, надёжнее и проще в поддержке. А главное — теперь можно строить адекватную веб-панель, где одно устройство = одна карточка, а не десяток разрозненных полей.

P.S. Всегда делайте бэкап перед ALTER TABLE — даже на Raspberry Pi!