Практика разделения таблицы базы данных MySql

MySQL

1. Предыстория - проект компании IoT

Большое количество устройств подключено к облаку через сервисы IoT, и устройства сообщают свои собственные данные (далее — динамические данные) каждые 30 с. Служба IoT перенаправляет данные, сообщаемые устройством, в шлюз обработки данных, а шлюз хранилища данных выполняет операции пакетного хранения и вставляет их в базу данных. Общая техническая структура проекта выглядит следующим образом:

2. Вопросы

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

Если предположить, что имеется 10 000 устройств и каждое из них передает динамические данные каждые 30 секунд, то каждую минуту будет генерироваться 20 000 единиц данных, каждый день будет генерироваться 28,8 миллиона единиц данных, а в среднем будет создано более 10 миллиардов единиц данных. год.

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

3. Технический фон

3.1 Подтаблица

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

3.2 Разделение

Данные в базе данных MySql хранятся на диске в виде файлов и по умолчанию размещаются в /mysql/data (вы можете просмотреть их через каталог данных в my.cnf).

Таблица в основном соответствует трем файлам: один — frm для хранения структуры таблицы, один — myd для хранения данных таблицы, а другой — myi для хранения индекса таблицы. Если объем данных в таблице слишком велик, mydmyi станет очень большим, и поиск данных станет очень медленным.

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

MySql5.1 и выше поддерживает функцию разделения. Методы разбиения MySql в основном включают:

3.2.1 диапазонный раздел:

Раздел диапазона означает непрерывный раздел, основанный на определенном поле, например, раздел с идентификатором менее 3, раздел с идентификатором менее 6 и раздел с идентификатором менее 100.

3.2.2 список разделов:

Раздел списка основан на поле, принадлежащем разделу в диапазоне списка, например разделу с идентификатором 1, 3, 5, 7 и разделу с 2, 4, 6 и 8.

3.2.3 хеш-раздел:

Хэш-разделы используются для обеспечения равномерного распределения данных по предварительно заданному количеству разделов.Например, если количество разделов установлено равным 3, то все данные будут равномерно распределены по 3 разделам.

3.2.4 ключевой раздел:

Разбиение по КЛЮЧУ похоже на разбиение по HASH, за исключением того, что при разбиении HASH используется определяемое пользователем выражение, а хеш-функция для разбиения по KEY предоставляется сервером MySQL.

3.2.5 Подраздел:

Подраздел — это подраздел каждого раздела в таблице разделов.Подраздел может использовать либо раздел HASH, либо раздел KEY. Это также известно как составное разбиение. Подразделы подчиняются следующим правилам:

  • Если в одном разделе создается подраздел, в других разделах также должны быть подразделы.
  • Если создаются разделы, количество подразделов в каждом разделе должно быть одинаковым.
  • Подразделы в одном разделе имеют разные имена, а подразделы в разных разделах могут иметь одинаковые имена (5.1.50 не применяется).

4. Решения

4.1 Дизайн подтаблицы

Он разработан как одна таблица для каждой 1000 устройств, а имя таблицы — t_data_serial number.

Предполагая, что имеется 10 000 устройств, данные разбросаны и хранятся в десяти таблицах t_data_1 ~ t_data_10 в соответствии с серийными номерами устройств.

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

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

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

4.2 Дизайн разделов

Поскольку данные устройства передаются постоянно, рассмотрите возможность использования раздела Range.

Структура секций основана на времени сбора данных, одна секция в неделю, и каждая таблица предварительно настроена на 10-летнюю секцию. Рассчитано на основе того, что каждое устройство передает один фрагмент данных каждые 30 секунд, каждый раздел содержит около 10002460*2 = 2880000 единиц данных.

Оператор создания таблицы выглядит следующим образом:

CREATE TABLE `t_data_1` (
  `i_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `i_status` bit(1) DEFAULT NULL,
  `c_device_sequence` varchar(32) DEFAULT NULL COMMENT '设备序列号',
  `t_collect_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '数据采集时间',
  ...
  PRIMARY KEY (`i_id`,`t_collect_time`),
  KEY `index_c_device_sequence` (`c_device_sequence`,`t_collect_time`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=398404 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(t_collect_time)
(PARTITION p20171224 VALUES LESS THAN ('2017-12-24 00:00:00') ENGINE = MyISAM,
 PARTITION p20171231 VALUES LESS THAN ('2017-12-31 00:00:00') ENGINE = MyISAM,
 PARTITION p20180107 VALUES LESS THAN ('2018-01-07 00:00:00') ENGINE = MyISAM,
 ...
 PARTITION p20271212 VALUES LESS THAN ('2027-12-12 00:00:00') ENGINE = MyISAM,
 PARTITION p20271219 VALUES LESS THAN ('2027-12-19 00:00:00') ENGINE = MyISAM) */;
/*!40101 SET character_set_client = @saved_cs_client */;

5. Тест

Протестировано с 1,2 миллионами фрагментов данных, время запроса подтаблиц (10 фрагментов) составляет около 0,1 секунды, как показано на следующем рисунке:

Таблицы или раздела нет, а время запроса занимает более 1 секунды, как показано на следующем рисунке:

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

6. Думай

Чем больше разделов, тем лучше? Конечно нет.

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

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

Категории