XI. Структура базы данных.
Старое решение.
Старое решение было "решение в лоб". Данные от всех датчиков хранились в одной таблице вида:
[date][sensorId][value]
У такого простого решения обнаружились недостатки:
- Большое количество записей в таблице. Даже для небольших интервалов времени.
- Низкая производительность нетривиальных запросов. Например, получить средние значения от одного датчика за несколько лет, сгруппированные по интервалам времени, например по часу. Для выполнения такого запроса СУБД придется перебрать почти всю таблицу.
Новое решение.
- Данные от датчиков разбиты по отдельным таблицам. Чтобы не плодить множество таблиц, таблица может содержать значения от группы датчиков. Датчики объединяются в группы по физическим устройствам, от которых все измерения для группы можно получить одновременно (или почти одновременно). Например измеритель качества воздуха или UPS.
- Данные каждого датчика хранятся в двух столбцах. В одном стобце - значение, в другом - сумма всех предыдущих значений. Хранение суммы позволяет вычислять среднее за интервал времени напрямую. Для этого достаточно взять разность значений суммы в конце и начале интервала и разделить её на количество измерений в интервале.
- Добавлен счетчик количества строк - автоинкрементный столбец index. Он позволяет напрямую получать количество измерений в интервале.
- В обязательном порядке в таблицу записываюся данные за все периоды измерений без пропусков. Например, если период измерения одна минута, то в таблице должны присутствовать измерения за каждую минуту. Это позволяет очень сильно упростить взятие интервалов, достаточно index поделить по модулю величину интервала.
- Чтобы обеспечить отсутствие пропусков в таблицу измерений добавляются фиктивные значения. Для этого, например можно записать нужное число раз предыдущее значение. Проверка на необходимость добавления фиктивных значений выполняется всякий раз перед записью значений группы датчиков.
- Добавлен столбец warning для храниния особых значений. Например пропадание напряжения, выход значений качества воздуха за допустимые или признак фиктивных измерений.
Проверка.
Была создана поминутная таблица на 3 млн. строк за период 5 лет.
Старое решение. Время выполнения запроса
SELECT * FROM `values` WHERE DAY(`date`)=1 AND HOUR(`date`)=0 AND MINUTE(`date`)=0
на получение значений за каждый час даже без вычисления средних составляет 2 сек.
Новое решение. Время выполнения запроса
SELECT * FROM `values` WHERE `index` % 60 = 0
SELECT * FROM `values` WHERE UNIX_TIMESTAMP(`date`) % 3600 = 0
на получение значений за каждый час с автоматическим вычислением средних составляет 0.002 сек. Разница в тысячу раз! Плюс получаем легкость выбора любого размера интервала с любого начала. Средние получаем вообще почти без вычислительных затрат.