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

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

Предыстория

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

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

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

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

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

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

Сейчас поговорим про MySQL и JSON, что изменилось в работе с JSON с MySQL 5.7 в MySQL 8. Потом я покажу демо, для которого использую 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.

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

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. Мне кажется, это очень удобно.

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

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 последних записей. Я делаю такой запрос и пытаюсь его выполнить. К сожалению, это будет работать очень долго.

Логичным образом MySQL в данном случае не будет использовать никаких индексов. Мы вытаскиваем данные из JSON и пытаемся применить какие-то фильтры и сортировку. В этом случае у нас получится 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 10G

        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, то он его и использует.

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

С этим на самом деле есть небольшая проблемка. Допустим, я хочу отсортировать данные не только по 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 10G

    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 не будет использовать индексы. В данном конкретном случае MySQL решает, что полный скан таблицы будет выгоднее, чем использование индекса, и опять используется очень медленная операция filesort.

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 можно читать и с начала, и с конца. В данном случае MySQL читает индекс с конца и все хорошо. Но если мы делаем descending и ascending, то прочитать нельзя. Можно прочитать в одном порядке, но совместить две сортировки нельзя — нужно пересортировать.

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

mysql> explain select data_name, published_at, data->>'$.data' as data from 
cloud_data_json order by published_at desc  limit 10G

      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, который сейчас, на момент публикации уже доступен и готов для использования в продакшене.

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

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

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

mysql> select json_pretty(data) from cloud_data_json 
where data->>'$.data' like '%beer%' limit 1G
…
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: приходится пересоздавать таблицу, чтобы добавить специальный идентификатор полнотекстового поиска.

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

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.

Автор: olegbunin

Источник