Блог

У меня есть база данных MySQL с более чем 20 таблицами, но одна из них значительно большая, потому что она собирает данные измерений с разных датчиков. Ее размер составляет около 145 ГБ на диске и содержит более 1 миллиарда записей. Все эти данные также реплицируются на другой сервер MySQL.

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

Итак, ниже приведены решения, которые я не могу выбрать:

1. Использование разделения MySQL и разделение данных по годам (например, раздел1 — 2010, раздел2 — 2011 и т. Д.)
2. Создание отдельных таблиц и разделение данных по годам (например, таблицы measuring_2010, measuring_2011 и т. Д.)

Существуют ли какие-либо другие (более новые) возможные варианты, о которых я не знаю?

Я знаю, что в первом случае MySQL сам получит данные из «осколков», а во втором случае мне придется написать для него своего рода оболочку и сделать это самостоятельно. Есть ли какой-либо другой способ для второго случая, который позволил бы рассматривать все отдельные таблицы как «одну большую таблицу» для извлечения данных?

Я знаю, что этот вопрос уже задавался в прошлом, но, возможно, кто-то придумал какое-то новое решение (о котором я не знаю) или что к настоящему времени лучшее практическое решение изменилось.

Большое спасибо за вашу помощь.

Редактировать:

Схема примерно такая:

 device_id (INT)
timestamp (DATETIME)
sensor_1_temp (FLOAT)
sensor_2_temp (FLOAT)
etc. (30 more for instance)
  

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

Что ж, если вы надеетесь на новый ответ, это означает, что вы, вероятно, прочитали мои ответы, и я звучу как заезженная пластинка. См. Раздел блога для нескольких случаев использования, где разделение может повысить производительность. Ваш не похож ни на один из 4 случаев.

Сжатие device_id . INT это 4 байта; у вас действительно миллионы устройств? TINYINT UNSIGNED это 1 байт и диапазон 0 ..255. SMALLINT UNSIGNED это 2 байта и диапазон 0 ..64 КБ. Это немного уменьшит таблицу.

Если ваш реальный вопрос заключается в том, как управлять таким большим количеством данных, тогда давайте «мыслить нестандартно». Читайте дальше.

Построение графиков… Какие диапазоны дат вы отображаете?

• «Последний» час / день / неделя / месяц / год?
• Произвольный час / день / неделя / месяц / год?
• Произвольный диапазон, не привязанный к границам дня / недели / месяца / года?

Что вы рисуете?

• Среднее значение за день?
• Макс / мин за день?
• Подсвечники (и т. Д.) На день или неделю или что-то еще?

Независимо от случая, вы должны создать (и постепенно поддерживать) сводную таблицу с данными. Строка будет содержать сводную информацию за один час. Я бы предложил

 CREATE TABLE Summary (
    device_id SMALLINT UNSIGNED NOT NULL,
    sensor_id TINYINT UNSIGNED NOT NULL,
    hr TIMESTAMP NOT NULL,
    avg_val FLOAT NOT NULL,
    min_val FLOAT NOT NULL,
    max_val FLOAT NOT NULL
    PRIMARY KEY (device_id, sensor_id, hr)
) ENGINE=InnoDB;
  

Одна сводная таблица может составлять 9 ГБ (для текущего объема данных).

 SELECT hr,
       avg_val,
       min_val,
       max_val
    FROM Summary
    WHERE device_id = ?
      AND sensor_id = ?
      AND hr >= ?
      AND hr  < ?   INTERVAL 20 DAY;
  

Даст ли вам значения hi / lo / avg за 480 часов; достаточно для построения графика? Получение 480 строк из сводной таблицы намного быстрее, чем получение 60 * 480 строк из таблицы необработанных данных.

Получение аналогичных данных за год, вероятно, затруднило бы построение графического пакета, поэтому, возможно, стоит составить сводку сводки — с разрешением в день. Это будет около 0,4 ГБ.

Существует несколько разных способов построения сводных таблиц; мы можем обсудить это после того, как вы обдумаете его красоту и прочитаете блог сводных таблиц. Возможно, лучшим способом является сбор данных за один час, а затем увеличение сводной таблицы. Это было бы чем-то похоже на триггер, обсуждаемый в моем блоге Staging table.

И, если у вас были почасовые сводки, вам действительно нужны поминутные данные? Подумайте о том, чтобы выбросить ее. Или, может быть, данные, скажем, через месяц. Это приводит к использованию разбиения, но только для его пользы при удалении старых данных, как обсуждалось в «Случае 1» блога разбиения. То есть у вас будут ежедневные разделы, использующие DROP и REORGANIZE каждую ночь для сдвига времени таблицы «Фактов». Это приведет к уменьшению занимаемой площади в 145 ГБ, но без потери большого количества данных. Новый объем: около 12 ГБ (почасовая сводка поминутная информация за последние 30 дней)

PS: В блоге сводной таблицы показано, как получить стандартное отклонение.

Вы мало сказали о том, как вы используете / запрашиваете данные или как выглядит схема, но я пытаюсь что-то придумать.

1. Одна вещь, как вы можете разделить свою таблицу, основана на сущностях (разные датчики — это разные сущности). Это полезно, если для разных датчиков требуются разные столбцы. Таким образом, вам не нужно принудительно объединять их в одну схему, которая подходит для всех из них (наименьшее общее число). Хотя нехорошо, если датчики добавляются или удаляются динамически, поскольку вам придется добавлять таблицы во время выполнения.
2. Другой подход заключается в разделении таблицы по времени. Это тот случай, когда через некоторое время данные могут быть «историзированы», и они больше не используются для реальной бизнес-логики, а для статистических целей.

Оба подхода также можно комбинировать. Кроме того, убедитесь, что таблица правильно проиндексирована в соответствии с вашими потребностями запроса.

Я настоятельно не рекомендую любой подход, который часто требует добавления таблицы через некоторое время или чего-либо подобного. Как всегда, я бы ничего не разделял, пока не возникнет проблема с производительностью.

Редактировать:
я бы четко реструктурировал таблицу следующим образом и вообще не разделял ее:

 device_id (INT)
timestamp (DATETIME)
sensor_id (INT) -- could be unique or not. if sensor_id is not unique make a 
                -- composite key from device_id and sensor_id given that you 
                -- need it for queries
sensor_temp (FLOAT)
  

Если объем данных растет быстро, и вы ожидаете, что в ближайшее время будет генерироваться терабайт данных, вам лучше использовать подход NoSQL. Но это другая история.