Хабрахабр

Гибкая схема хранения данных в MySQL (JSON)

Александр Рубин работает в компании Percona и не единожды выступал на HighLoad++, знаком участникам как эксперт в MySQL. Логично предположить, что и сегодня речь пойдет про что-то, связанное с MySQL. Это так, но лишь отчасти, потому что еще мы поговорим про интернет вещей. Рассказ будет наполовину развлекательный, особенно первая его часть, в которой посмотрим на девайс, который Александр создал, чтобы собрать урожай абрикосов. Такова уж натура настоящего инженера — хочешь фруктов, а покупаешь плату.

Предыстория

Началось все с простого желания посадить фруктовое дерево на своем участке. Сделать это, казалось бы, очень просто — приходишь в магазин и покупаешь саженец. Но в Америке первый вопрос, который задают продавцы, это сколько дерево получит солнечного света. Для Александра это оказалось гигантской загадкой — совершенно неизвестно, сколько солнечного света на участке.

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

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

Но я выбрал для этого проекта устройство, которое называется Particle Photon. Итак, чтобы не записывать наблюдения о погоде в блокнотик, есть большое количество устройств для интернет-вещей — Raspberry Pi, новый Raspberry Pi, Arduino — тысячи разных платформ. Оно очень просто в использовании, стоит 19 $ на официальном сайте.

В Particle Photon хорошо то, что это:

  1. 100% облачное решение;
  2. подходят любые датчики, например, для Arduino. Они все стоят меньше доллара.

Я сделал такой девайс и положил его в траву на участке. В нем есть Particle Device Cloud и консоль. Этот приборчик подключается через Wi-Fi hotspot и посылает данные: освещенность, температуру и влажность. Приборчик продержался 24 часа на маленькой батарейке, что достаточно неплохо.

Для этого, естественно, как ветеран MySQL, я выбрал MySQL. Дальше мне нужно не только измерять освещенность и прочее и передавать их на телефон (что на самом деле хорошо — я могу в режиме реального времени видеть, какая у меня освещенность), но и хранить данные.

Как мы записываем данные в MySQL

Я выбрал достаточно сложную схему:

  • получаю данные из Particle-консоли;
  • использую Node.js, чтобы записать их в MySQL.

Я использую Particle API JS, который можно скачать с сайта Particle. Устанавливаю соединение с MySQL и записываю, то есть просто делаю INSERT INTO values. Такой вот pipeline.

Дальше та самая схема: на виртуальной машине работает программка на Node.js, которая получает данные от Particle и записывает их в MySQL. Таким образом, девайс лежит во дворе, подсоединяется по Wi-Fi к домашнему роутеру и с помощью протокола MQTT передает данные в Particle.

Но также на графике есть шум, это типично для приборов интернета вещей. Для начала я построил графики из сырых данных в R. На графиках видно, что температура и освещенность днем поднимаются, к ночи падают, а влажность повышается — это естественно. Это будет важно при дальнейшем рассмотрении. Например, когда на устройство залез жучок и закрыл его, датчик может передать совершенно нерелевантные данные.

7 в MySQL 8. Сейчас поговорим про MySQL и JSON, что изменилось в работе с JSON с MySQL 5. Потом я покажу демо, для которого использую MySQL 8 (на момент доклада эта версия еще не была готова для продакшена, сейчас уже выпущен стабильный релиз).

Хранение данных в MySQL

Когда мы пытаемся хранить данные, полученные с датчиков, наша первая мысль — создать таблицу в MySQL:

CREATE TABLE 'sensor_wide' ( 'id' int (11) NOT NULL AUTO_INCREMENT, 'light' int (11) DEFAULT NULL, 'temp' double DEFAULT NULL, 'humidity' double DEFAULT NULL, PRIMARY KEY ('id')
) ENGINE=InnoDB

Здесь для каждого датчика и для каждого типа данных есть своя колонка: light, temperature, humidity.

Допустим, мы захотим добавить еще один датчик и измерять что-то еще. Это достаточно логично, но есть проблема — это не гибко. Что делать в этом случае? Например, некоторые люди измеряют остаток пива в кеге.

alter table sensor_wide
add water level double ...;

Как извратиться, чтобы в таблицу что-то добавить? Нужно сделать alter table, но если вы делали alter table в MySQL, то знаете, о чем я говорю, — это совершенно непросто. Alter table в MySQL 8 и в MariaDB реализовано намного проще, но исторически это большая проблема. Так что если нам нужно добавить колонку, например, с названием пива, то это будет не так-то просто.

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

Еще один вариант — это key/value store.

Хранение данных в MySQL: key/value

Это будет более гибко: в key/value будет название, например, temperature и соответственно данные.

CREATE TABLE 'cloud_data' ( 'id' int (11) NOT NULL AUTO_INCREMENT, 'name' varchar(255) DEFAULT NULL, 'data' text DEFAULT NULL, 'updated_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY ('id')
) ENGINE=InnoDB

В этом случае появляется другая проблема — нет типов. Мы не знаем, что мы храним в поле 'data'. Нам придётся его объявить полем text. Когда я создаю свой девайс интернета вещей, я знаю, какой там датчик и соответственно тип, но если понадобится хранить в той же таблице еще чьи-то данные, то я не буду знать, какие данные собираются.

Можно хранить много таблиц, но создавать одну целую новую таблицу на каждый датчик — не очень-то хорошо.

— Использовать JSON. Что можно сделать?

Хранение данных в MySQL: JSON

Хорошая новость в том, что в MySQL 5.7 можно хранить JSON как поле.

CREATE TABLE 'cloud_data_json' ( 'id' int (11) NOT NULL AUTO_INCREMENT, 'name' varchar(255) DEFAULT NULL, 'data' JSON, 'updated_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY ('id')
) ENGINE=InnoDB;

До того, как появился MySQL 5.7, люди тоже хранили JSON, но как поле text. Поле JSON в MySQL позволяет хранить сам JSON наиболее эффективно. Кроме того, на основе JSON можно создать виртуальные колонки и на их основе индексы.

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

Поле JSON лучше для хранения JSON, чем поле text, потому что:

  • Предоставляет автоматическую валидация документа. То есть если мы попытаемся туда записать что-то не валидное, выпадет ошибка.
  • Это оптимизированный формат хранения. JSON хранится в бинарном формате, что позволяет переходить от одного документа JSON к другому — то, что называется skip.

Чтобы хранить данные в JSON, мы можем просто использовать SQL: сделать INSERT, поместить туда 'data' и получить данные с девайса.


stream.on('event', function(data) { var query = connection.query( 'INSERT INTO cloud_data_json (client_name, data) VALUES (?, ?)', ['particle', JSON.stringify(data)]
)

(demo)

Демо

Для демонстрации (здесь её начало на видео) примера используется виртуальная машина, в которой есть SQL.

Ниже фрагмент программы.

Я делаю INSERT INTO cloud_data (name, data), получаю данные уже в формате JSON, и могу их прямо записать в MySQL, как есть, совершенно не думая о том, что там внутри.

Кажется, это работает до сих пор. Как выяснилось, с помощью этого cloud можно получать доступ не только к данным моего устройства, но вообще ко всем данным, которые использует этот самый Particle. Неизвестно, где эти девайсы находятся, но можно получить такие данные. Люди, которые по всему миру используют Particle Photon, посылают какие-то данные: открыта дверь в гараже, или остаток пива такой-то, или что-то еще. Разница только в том, что, когда я получаю свои данные, я пишу что-то типа: deviceId: 'mine'.

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

Мы совершенно не знаем, что это за данные: TTL, published_at, coreid, door status (дверь открыта), relay on.

Допустим, я попытаюсь положить это в MySQL в нормальную структуру данных. Это прекрасный пример. Если у меня есть JSON, то я записываю это прямо в MySQL в виде JSON-поля. Я должен знать, что там за дверь, почему она открыта и какие вообще параметры может принимать.

Пожалуйста, все записалось.

Document store

Document store — это попытка в MySQL сделать хранилище для JSON. Я очень люблю SQL, хорошо с ним знаком, могу сделать любой SQL-запрос и т.д. Но многие не любят SQL по разным причинам, и Document store может стать для них решением, потому с его помощью можно абстрагироваться от SQL, подключиться к MySQL и прямо туда записывать JSON.

7: использовать другой протокол, другой порт, также нужен и другой драйвер. Есть еще одна возможность, которая появилась в MySQL 5. Опять же я не знаю, что у меня в этом JSON — информация про двери или что-то еще, просто данные в MySQL сбрасываю, а что там, разберемся потом. Для Node.js (на самом деле для любых языков программирования — PHP, Java и пр.) мы подключаемся к MySQL по другому протоколу и можем передавать данные в формате JSON.

const mysqlx = require('@mysql/xdevapi*);
// MySQL Connection var mySession = mysqlx.gctSession({ host: 'localhost', port: 33060, dbUser: 'photon*
});

session.getSchema("particle").getCollection("cloud_data_docstore") .add( data ) .execute(function (row) ).catch(err => { console.log(err); }) .then( -Function (notices) { console.log("Wrote to MySQL") });
...https://dev.mysql.com/doc/dev/connector-nodejs/

Если хотите с этим поэкспериментировать, можно сконфигурировать MySQL 5.7 на то, чтобы он понимал и слушал на соответствующем порту Document store или X DevAPI. Я использовал connector-nodejs.

Я совершенно не знаю, что там. Это пример того, что я туда записываю: пиво и пр. Сейчас просто запишем, а проанализируем потом.

Следующий пункт нашей программы — как посмотреть, что там?

Хранение данных в MySQL: JSON + индексы

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

Name, data, published_at — это все мы таким образом можем вытащить. Data в нашем случае — название колонки, в которой хранится JSON, а name — это наше поле.

select data->>'$.name' as data_name, data->>'$.data' as data, data->>'$.published_at' as published from cloud_data_json
order by data->'$.published_at' desc limit 10;

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

Мы вытаскиваем данные из JSON и пытаемся применить какие-то фильтры и сортировку. Логичным образом MySQL в данном случае не будет использовать никаких индексов. В этом случае у нас получится Using filesort.

EXPLAIN select data->>'$.name' as data_name ... order by data->>'$.published_at' desc limit 10 select_type: SIMPLE table: cloud_data_json possible_keys: NULL key: NULL
… rows: 101589 filtered: 100.00 Extra: Using filesort

Using filesort — это очень плохо, это внешняя сортировка.

Хорошая новость в том, что можно сделать 2 шага, чтобы это ускорить.

Шаг 1. Создание виртуальной колонки

mysql> ALTER TABLE cloud_data_json -> ADD published_at DATETIME(6) -> GENERATED ALWAYS AS (STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ")) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Я делаю EXTRACT, то есть вытаскиваю данные из JSON и на его основе создаю виртуальную колонку. Виртуальная колонка в MySQL 5.7 и в MySQL 8 не хранится — это просто возможность создать отдельную колонку.

Но здесь все не так плохо. Вы спросите, как же так, ты же говорил, что ALTER TABLE — это такая долгая операция. Там есть loсk, но на самом деле в MySQL есть lock на всех DDL-операциях. Создание виртуальной колонки происходит быстро. ALTER TABLE — достаточно быстрая операция, и она не перестраивает всю таблицу.

Мне пришлось сконвертировать дату, потому что в JSON она хранится в формате iso, а здесь MySQL использует совершенно другой формат. Мы здесь создали виртуальную колонку. Для создания колонки я назвал ее, дал ей тип и сказал, что буду туда записывать.

Published_at уже есть, name проще — просто делаем виртуальную колонку. Для оптимизации исходного запроса нужно вытащить published_at и name.

mysql> ALTER TABLE cloud_data_json -> ADD data_name VARCHAR(255) -> GENERATED ALWAYS AS (data->>'$.name') VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Шаг 2. Создание индекса

В коде ниже я создаю индекс на published_at и выполняю запрос:

mysql> alter table cloud_data_json add key (published_at);
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G table: cloud_data_json type: index
possible_keys: NULL key: published_at key_len: 9 rows: 10 filtered: 100.00 Extra: Backward index scan

Видно, что на самом деле MySQL использует индекс. Это оптимизация order by. В данном примере data и name не индексируются. MySQL использует order by data, и так как у нас есть индекс на published_at, то он его и использует.

MySQL бы все равно понял, что есть индекс на эту колонку и начал бы его использовать. Более того, я бы мог в order by вместо published_at использовать тот же самый синтаксический сахар STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ").

Допустим, я хочу отсортировать данные не только по published_at, но еще и по названию. С этим на самом деле есть небольшая проблемка.

mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc, data_name asc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 101589 filtered: 100.00 Extra: Using filesort

Если ваше устройство обрабатывает десятки тысяч событий в секунду, published_at не даст хорошей сортировки, так как будут дубликаты. Поэтому мы добавляем еще одну сортировку по data_name. Это типичный запрос не только для интернета вещей: дайте мне 10 последних событий, но отсортируйте их по дате, а потом, например, по фамилии человека по возрастанию. Для этого в примере выше есть два поля и указаны два ключа сортировки: descending и ascending.

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

New in MySQL 8.0

descending/ascending

В MySQL 5.7 такой запрос оптимизировать нельзя, если только за счет других вещей. В MySQL 8 появилась реальная возможность указывать сортировку для каждого поля.

mysql> alter table cloud_data_json add key published_at_data_name (published_at desc, data_name asc); Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0

Самое интересное, что ключ descending/ascending после названия индекса давно был в SQL. Даже в самой первой версии MySQL 3.23 можно было указать published_at descending или published_at ascending. MySQL это принимал, но ничего не делал, то есть сортировал всегда в одном направлении.

Можно создать поле с сортировкой по убыванию и с сортировкой по умолчанию. В MySQL 8 это поправили и теперь такая фича есть.

Вернемся на секунду назад и посмотрим на пример из шага 2 еще раз.

Это работает потому, что в MySQL-индексы — это B-tree, а индексы B-tree можно читать и с начала, и с конца. Почему это работает, а то — нет? Но если мы делаем descending и ascending, то прочитать нельзя. В данном случае MySQL читает индекс с конца и все хорошо. Можно прочитать в одном порядке, но совместить две сортировки нельзя — нужно пересортировать.

MySQL использует этот индекс, и все будет хорошо и быстро. Так как мы оптимизируем совершенно конкретный случай, то можем для него создать индекс и указать конкретную сортировку: здесь published_at — descending, data_name — ascending.

mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: index possible_keys: NULL key: published_at_data_name key_len: 267 ref: NULL rows: 10 filtered: 100.00 Extra: NULL

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

Вывод результатов

Еще есть две интересные штуки, которые я хочу показать:

Pretty print, то есть красивый вывод данных на экран.  1. При обычном SELECT JSON будет не форматирован.

mysql> select json_pretty(data) from cloud_data_json where data->>'$.data' like '%beer%' limit 1\G

json_pretty(data): { "ttl": 60, "data": "FvGav,tagkey=beer-store spFridge=7.00,pvFridge=7.44", "name": "LOG_DATA_DEBUG", "coreid": "3600....", "published_at": "2017-09-28T18:21:16.517Z"
}

 2. Можно сказать, чтобы MySQL вывел результат в виде JSON array или JSON object, указать поля, и тогда вывод будет форматирован в виде JSON.

Полнотекстовый поиск внутри документов JSON

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

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

mysql> alter table cloud_data_json_indexes add fulltext key (data); ERROR 3152 (42000): JSON column ’data’ supports indexing only via generated columns on a specified ISON path.

К сожалению, это не работает. Даже в MySQL 8 создать полнотекстовый индекс просто по полю JSON, к сожалению, невозможно. Я бы конечно хотел иметь такую функцию — возможность поиска хотя бы по ключам JSON была бы очень полезна.

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

mysql> ALTER TABLE cloud_data_json_indexes -> ADD data_data VARCHAR(255)
-> GENERATED ALWAYS AS (data->>'$.data') VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table cloud_data_json_indexes add fulltext key ft_json(data_name, data_data);
ERROR 3106 (HY000): 'Fulltext index on virtual generated column' is not supported for generated columns.

К сожалению, это тоже не работает — на виртуальной колонке нельзя создать полнотекстовый индекс.

MySQL 5. Раз так, давайте создадим хранимую колонку. 7 позволяет объявить колонку хранимым полем.

mysql> ALTER TABLE cloud_data_json_indexes
-> ADD data_name VARCHAR(255) CHARACTER SET UTF8MB4 -> GENERATED ALWAYS AS (data->>'$.name') STORED;
Query OK, 123518 rows affected (1.75 sec)
Records: 123518 Duplicates: 0 Warnings: 0 mysql> alter table cloud_data_json_indexes add fulltext key ft_json(data_name);
Query OK, 0 rows affected, 1 warning (3.78 sec)
Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings;
+------------+--------+---------------------------------------------------+
| Level | Code | Message |
+------------+--------+---------------------------------------------------+
| Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+------------+--------+---------------------------------------------------+

В предыдущих примерах мы создавали виртуальные колонки, которые не хранятся, но индексы создаются и хранятся. В данном случае мне пришлось сказать MySQL, что это колонка STORED, то есть она будет создана и данные в нее будут скопированы. После этого MySQL создал полнотекстовый индекс, для этого пришлось пересоздать таблицу. Но это ограничение на самом деле InnoDB и InnoDB fulltext search: приходится пересоздавать таблицу, чтобы добавить специальный идентификатор полнотекстового поиска.

Конечно, не совсем для них, а потому что в UTF8MB3 есть некоторые проблемы с русским, китайским, японским и другими языками. Интересно, что в MySQL 8 появилась новая кодировка UTF8MB4 для смайликов.

mysql> ALTER TABLE cloud_data_json_indexes -> ADD data_data TEXT CHARACTER SET UTF8MB4 -> GENERATED ALWAYS AS ( CONVERT(data->>'$.data' USING UTF8MB4) ) STORED Query OK, 123518 rows affected (3.14 sec)
Records: 123518 Duplicates: 0 Warnings: 0

Соответственно MySQL 8 должен хранить данные JSON в UTF8MB4. Но то ли из-за того, что Node.js коннектится к Device Cloud, и там записано что-то не так, или это баг бета-версии, этого не произошло. Поэтому мне пришлось сконвертировать данные, перед тем как записать их в хранимую колонку.

mysql> ALTER TABLE cloud_data_json_indexes DROP KEY ft_json, ADD FULLTEXT KEY ft_json(data_name, data_data);
Query OK, 0 rows affected (1.85 sec)
Records: 0 Duplicates: 0 Warnings: 0

После этого я смог создать полнотекстовый поиск на двух полях: на названии JSON и на данных JSON.

Not only IoT

JSON — это не только интернет вещей. Он может использоваться для других интересных штук:

  • Custom fields (CMS);
  • Complex structures и т.д.;

Некоторые вещи могут быть намного удобнее реализованы с помощью гибкой схемы хранения данных. На Oracle OpenWorld приводился отличный пример: резервирование мест в кинотеатре. Реализовать это в реляционной модели очень сложно — получается много зависимых таблиц, джойнов и т.д. С другой стороны, мы можем хранить весь зал как структуру JSON, соответственно, записывать его в MySQL в другие таблицы и использовать обычным образом: создать индексы на основе JSON и т.д. Сложные структуры удобно хранить в формате JSON.

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

В случае интернета вещей у нас получилась InoThings++ — конференция для профессионалов рынка интернета вещей, которая 4 апреля пройдет уже во второй раз. Этот доклад — отличный пример того, как из одной темы на большой конференции, вырастает целая секция, а потом и обособленное отдельное мероприятие.

Приходите, если и ваши высоконагруженные системы верно двигаются к IIoT.
Центральным событием конференции, похоже, станет круглый стол «Нужны ли нам национальные стандарты в Интернете Вещей?», который органично дополнят всесторонние прикладные доклады.

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть