Повысить производительность в большой таблице MySQL

Я хотел бы задать вопрос о том, как повысить производительность в большой таблице MySQL с помощью механизма innodb:

В настоящее время в моей базе данных находится таблица с 200 миллионами строк. В этой таблице периодически хранятся данные, собранные различными датчиками. Структура таблицы выглядит следующим образом:

CREATE TABLE sns_value ( value_id int(11) NOT NULL AUTO_INCREMENT, sensor_id int(11) NOT NULL, type_id int(11) NOT NULL, date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, value int(11) NOT NULL, PRIMARY KEY (value_id), KEY idx_sensor id (sensor_id), KEY idx_date (date), KEY idx_type_id (type_id) ); 

Сначала я думал о разделении таблицы в месяцах, но из-за постоянного добавления новых датчиков он достигнет текущего размера примерно через месяц.

Другим решением, которое я придумал, было разбиение таблицы на датчики. Однако из-за предела 1024 разделов MySQL это не вариант.

Я считаю, что правильным решением будет использование таблицы с одинаковой структурой для каждого из датчиков:

sns_value_XXXXX

Таким образом, было бы более 1000 таблиц с предполагаемым размером 30 миллионов строк в год. В то же время эти таблицы могут быть разделены на несколько месяцев для обеспечения быстрого доступа к данным.

Какие проблемы могут возникнуть в результате этого решения? Существует ли более нормализованное решение?

Редактирование с дополнительной информацией

Я считаю таблицу большой по отношению к моему серверу:

  • Облако 2xCPU и память 8 ГБ
  • LAMP (CentOS 6.5 и MySQL 5.1.73)

Каждый датчик может иметь более одного типа переменных (CO, CO2 и т. Д.).

У меня в основном есть два медленных запроса:

1) Ежедневная сводка для каждого датчика и типа (сред., Макс., Мин.):

 SELECT round(avg(value)) as mean, min(value) as min, max(value) as max, type_id FROM sns_value WHERE sensor_id=1 AND date BETWEEN '2014-10-29 00:00:00' AND '2014-10-29 12:00:00' GROUP BY type_id limit 2000; 

Это занимает более 5 минут.

2) Вертикальный и горизонтальный вид и экспорт:

 SELECT sns_value.date AS date, sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 101)))))) AS one, sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 141)))))) AS two, sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 151)))))) AS three FROM sns_value WHERE sns_value.sensor_id=1 AND sns_value.date BETWEEN '2014-10-28 12:28:29' AND '2014-10-29 12:28:29' GROUP BY sns_value.sensor_id,sns_value.date LIMIT 4500; 

Это также занимает более 5 минут.

Другие соображения

  1. Временные метки могут повторяться из-за характеристик вставок.
  2. Периодические вставки должны сосуществовать с выборами.
  3. Никакие обновления и удаления не выполняются в таблице.

Предположения, сделанные для подхода «один стол для каждого датчика»

  1. Таблицы для каждого датчика будут намного меньше, поэтому доступ будет быстрее.
  2. Выборы будут выполняться только по одной таблице для каждого датчика.
  3. Выбор данных смешивания с разных датчиков не критичен по времени.

Обновление 02/02/2015

Мы создали новую таблицу для каждого года данных, которую мы также разделяли ежедневно. Каждая таблица имеет около 250 миллионов строк с 365 разделами. Новый индекс используется, как предложил Олли (sensor_id, date, type_id, value), но запрос все еще занимает от 30 секунд до 2 минут. Мы не используем первый запрос (ежедневное резюме), а только второе (вертикальное и горизонтальное).

Чтобы иметь возможность разбивать таблицу, первичный индекс нужно было удалить.

Мы что-то упускаем? Есть ли способ улучшить производительность?

Большое спасибо!

Отредактировано на основе изменений в вопросе

Одна таблица на датчик, с уважением, действительно очень плохая идея. Для этого есть несколько причин:

  1. Серверы MySQL на обычных операционных системах имеют трудное время с тысячами таблиц. Большинство ОС не могут одновременно обрабатывать многие одновременные обращения к файлам.
  2. Вам придется создавать таблицы каждый раз, когда вы добавляете (или удаляете) датчики.
  3. Запросы, содержащие данные с нескольких датчиков, будут медленными и запутанными.

Моя предыдущая версия этого ответа предложила разбиение диапазонов по метке времени. Но это не будет работать с вашим основным ключом value_id . Однако, с запрошенными вами запросами и правильной индексацией вашей таблицы, разметка, вероятно, не понадобится.

(Измените date имени столбца, если можете: это зарезервированное слово, и у вас будет много проблем с написанием запросов. Вместо этого я предлагаю вам использовать ts , что означает временную метку.)

Остерегайтесь : значения int(11) не достаточно велики для вашего столбца value_id . У вас кончились иды. Для этого столбца используйте bigint(20) .

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

 SELECT round(avg(value)) as mean, min(value) as min, max(value) as max, type_id FROM sns_value WHERE sensor_id=1 AND date BETWEEN '2014-10-29 00:00:00' AND '2014-10-29 12:00:00' GROUP BY type_id limit 2000; 

Для этого запроса вы сначала просматриваете sensor_id с помощью константы, затем вы просматриваете диапазон значений date , затем вы агрегируете по type_id . Наконец, вы извлекаете столбец value . Поэтому так называемый составной индекс покрытия (sensor_id, date, type_id, value) сможет удовлетворить ваш запрос непосредственно при сканировании индекса. Это должно быть очень быстро для вас – конечно, быстрее, чем за 5 минут даже при большом столе.

В вашем втором запросе будет работать аналогичная стратегия индексирования.

 SELECT sns_value.date AS date, sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 101)))))) AS one, sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 141)))))) AS two, sum((sns_value.value * (1 - abs(sign((sns_value.type_id - 151)))))) AS three FROM sns_value WHERE sns_value.sensor_id=1 AND sns_value.date BETWEEN '2014-10-28 12:28:29' AND '2014-10-29 12:28:29' GROUP BY sns_value.sensor_id,sns_value.date LIMIT 4500; 

Опять же, вы начинаете с постоянного значения sensor_id а затем используете диапазон date . Затем вы извлекаете как type_id и value . Это означает, что один и тот же индекс из четырех столбцов, который я упомянул, должен работать на вас.

 CREATE TABLE sns_value ( value_id bigint(20) NOT NULL AUTO_INCREMENT, sensor_id int(11) NOT NULL, type_id int(11) NOT NULL, ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, value int(11) NOT NULL, PRIMARY KEY (value_id), INDEX query_opt (sensor_id, ts, type_id, value) ); 

Идея создания отдельной таблицы для ряда датчиков.

Не используйте auto_increment для первичного ключа, если вам не нужно. Обычно механизм БД кластеризует данные по его первичному ключу.

Вместо этого используйте составной ключ, зависящий от вашего usecase, последовательность столбцов может быть разной.

EDIT: Также добавлен тип в PK. Учитывая запросы, я бы сделал это так. Выбор имен полей преднамерен, они должны быть описательными и всегда учитывать слова reserverd.

 CREATE TABLE snsXX_readings ( sensor_id int(11) NOT NULL, reading int(11) NOT NULL, reading_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, type_id int(11) NOT NULL, PRIMARY KEY (reading_time, sensor_id, type_id), KEY idx date_idx (date), KEY idx type_id (type_id) ); 

Кроме того, рассмотрите обобщение показаний или их группировку в одно поле.

Вы можете попробовать получить рандомизированные сводные данные

У меня есть аналогичная таблица. таблица двигателя myisam (наименьший размер таблицы), запись 10 м, индекс на мой стол, потому что бесполезен (проверен). Получите весь диапазон для всех данных. результат: 10sn этот запрос.

 SELECT * FROM ( SELECT sensor_id, value, date FROM sns_value l WHERE l.sensor_id= 123 AND (l.date BETWEEN '2013-10-29 12:28:29' AND '2015-10-29 12:28:29') ORDER BY RAND() LIMIT 2000 ) as tmp ORDER BY tmp.date; 

Этот запрос на первом шаге попадает между датами и сортировкой, чтобы рандомизировать первые 2k данных, на данных сортировки второго шага. запрос каждый раз получает результат 2k для разных данных.

  • Как скрыть пароль к базе данных MySQL от людей, использующих программу
  • mysql: выберите несколько значений строк в формате, разделенном запятыми, используя COALESCE
  • Мощный инструмент управления для MySQL с аналогичными функциями для студии SQL Server Management
  • Удаление повторяющихся строк из пользовательского запроса выбора SQL
  • Как фильтровать записи на основе состояния из двух столбцов, которые зависят от
  • server link - ссылка сервера mysql и sql 2008
  • Невозможно вставить значение как есть - нужен ответ для любого БД - Oracle, Sql Server, MySql и т. Д.
  • Помощник по миграции SQL Server (SSMA): ERROR
  • почему varbinary вместо varchar
  • использовать переменную в select query
  • Как базы данных физически хранят данные в файловой системе?
  • Давайте будем гением компьютера.