Александр Рубин работает в компании Percona и не единожды выступал на HighLoad++, знаком участникам как эксперт в MySQL. Логично предположить, что и сегодня речь пойдет про что-то, связанное с MySQL. Это так, но лишь отчасти, потому что еще мы поговорим про интернет вещей. Рассказ будет наполовину развлекательный, особенно первая его часть, в которой посмотрим на девайс, который Александр создал, чтобы собрать урожай абрикосов. Такова уж натура настоящего инженера — хочешь фруктов, а покупаешь плату.
Предыстория
Началось все с простого желания посадить фруктовое дерево на своем участке. Сделать это, казалось бы, очень просто — приходишь в магазин и покупаешь саженец. Но в Америке первый вопрос, который задают продавцы, это сколько дерево получит солнечного света. Для Александра это оказалось гигантской загадкой — совершенно неизвестно, сколько солнечного света на участке.
Чтобы это узнать, школьник мог бы каждый день выходить во двор, смотреть, сколько солнечного света, и записывать это в блокнотик. Но это не дело — надо все оснастить оборудованием и автоматизировать.
В ходе выступления многие примеры запускались и воспроизводились в прямом эфире. Хотите более полную картину, чем в тексте, переключайтесь на просмотр видео.
Итак, чтобы не записывать наблюдения о погоде в блокнотик, есть большое количество устройств для интернет-вещей — Raspberry Pi, новый Raspberry Pi, Arduino — тысячи разных платформ. Но я выбрал для этого проекта устройство, которое называется Particle Photon. Оно очень просто в использовании, стоит 19 $ на официальном сайте.
В Particle Photon хорошо то, что это:
Я сделал такой девайс и положил его в траву на участке. В нем есть Particle Device Cloud и консоль. Этот приборчик подключается через Wi-Fi hotspot и посылает данные: освещенность, температуру и влажность. Приборчик продержался 24 часа на маленькой батарейке, что достаточно неплохо.
Дальше мне нужно не только измерять освещенность и прочее и передавать их на телефон (что на самом деле хорошо — я могу в режиме реального времени видеть, какая у меня освещенность), но и хранить данные. Для этого, естественно, как ветеран MySQL, я выбрал MySQL.
Как мы записываем данные в 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:
Здесь для каждого датчика и для каждого типа данных есть своя колонка: light, temperature, humidity.
Это достаточно логично, но есть проблема — это не гибко. Допустим, мы захотим добавить еще один датчик и измерять что-то еще. Например, некоторые люди измеряют остаток пива в кеге. Что делать в этом случае?
Как извратиться, чтобы в таблицу что-то добавить? Нужно сделать alter table, но если вы делали alter table в MySQL, то знаете, о чем я говорю, — это совершенно непросто. Alter table в MySQL 8 и в MariaDB реализовано намного проще, но исторически это большая проблема. Так что если нам нужно добавить колонку, например, с названием пива, то это будет не так-то просто.
Опять же датчики появляются, исчезают, что нам делать со старыми данными? Например, мы прекращаем получать информацию про освещенность. Или мы создаем новую колонку — как хранить то, чего там до этого не было? Стандартный подход — это null, но для анализа это будет не очень удобно.
Еще один вариант — это key/value store.
Хранение данных в MySQL: key/value
Это будет более гибко: в key/value будет название, например, temperature и соответственно данные.
В этом случае появляется другая проблема — нет типов. Мы не знаем, что мы храним в поле 'data'. Нам придётся его объявить полем text. Когда я создаю свой девайс интернета вещей, я знаю, какой там датчик и соответственно тип, но если понадобится хранить в той же таблице еще чьи-то данные, то я не буду знать, какие данные собираются.
Можно хранить много таблиц, но создавать одну целую новую таблицу на каждый датчик — не очень-то хорошо.
Что можно сделать? — Использовать JSON.
Хранение данных в MySQL: JSON
Хорошая новость в том, что в MySQL 5.7 можно хранить JSON как поле.
До того, как появился MySQL 5.7, люди тоже хранили JSON, но как поле text. Поле JSON в MySQL позволяет хранить сам JSON наиболее эффективно. Кроме того, на основе JSON можно создать виртуальные колонки и на их основе индексы.
Единственная небольшая проблема — при хранении таблица возрастет в размере. Но зато мы получаем намного большую гибкость.
Поле JSON лучше для хранения JSON, чем поле text, потому что:
Чтобы хранить данные в JSON, мы можем просто использовать SQL: сделать INSERT, поместить туда 'data' и получить данные с девайса.
Демо
Для демонстрации (здесь её начало на видео) примера используется виртуальная машина, в которой есть SQL.
Ниже фрагмент программы.
Я делаю
Как выяснилось, с помощью этого cloud можно получать доступ не только к данным моего устройства, но вообще ко всем данным, которые использует этот самый Particle. Кажется, это работает до сих пор. Люди, которые по всему миру используют Particle Photon, посылают какие-то данные: открыта дверь в гараже, или остаток пива такой-то, или что-то еще. Неизвестно, где эти девайсы находятся, но можно получить такие данные. Разница только в том, что, когда я получаю свои данные, я пишу что-то типа:
При запуске кода мы получаем поток каких-то данных от чьих-то девайсов, которые что-то делают.
Мы совершенно не знаем, что это за данные: 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 сбрасываю, а что там, разберемся потом.
Если хотите с этим поэкспериментировать, можно сконфигурировать 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 — это все мы таким образом можем вытащить.
В этом примере я хочу посмотреть, что у меня записалось в таблицу MySQL, и 10 последних записей. Я делаю такой запрос и пытаюсь его выполнить. К сожалению, это будет работать очень долго.
Логичным образом MySQL в данном случае не будет использовать никаких индексов. Мы вытаскиваем данные из JSON и пытаемся применить какие-то фильтры и сортировку. В этом случае у нас получится Using filesort.
Using filesort — это очень плохо, это внешняя сортировка.
Хорошая новость в том, что можно сделать 2 шага, чтобы это ускорить.
Шаг 1. Создание виртуальной колонки
Я делаю 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 проще — просто делаем виртуальную колонку.
Шаг 2. Создание индекса
В коде ниже я создаю индекс на published_at и выполняю запрос:
Видно, что на самом деле MySQL использует индекс. Это оптимизация order by. В данном примере data и name не индексируются. MySQL использует order by data, и так как у нас есть индекс на published_at, то он его и использует.
Более того, я бы мог в order by вместо published_at использовать тот же самый синтаксический сахар
С этим на самом деле есть небольшая проблемка. Допустим, я хочу отсортировать данные не только по published_at, но еще и по названию.
Если ваше устройство обрабатывает десятки тысяч событий в секунду, published_at не даст хорошей сортировки, так как будут дубликаты. Поэтому мы добавляем еще одну сортировку по data_name. Это типичный запрос не только для интернета вещей: дайте мне 10 последних событий, но отсортируйте их по дате, а потом, например, по фамилии человека по возрастанию. Для этого в примере выше есть два поля и указаны два ключа сортировки: descending и ascending.
Прежде всего в этом случае MySQL не будет использовать индексы. В данном конкретном случае MySQL решает, что полный скан таблицы будет выгоднее, чем использование индекса, и опять используется очень медленная операция filesort.
New in MySQL 8.0
descending/ascending
В MySQL 5.7 такой запрос оптимизировать нельзя, если только за счет других вещей. В MySQL 8 появилась реальная возможность указывать сортировку для каждого поля.
Самое интересное, что ключ 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 8, который сейчас, на момент публикации уже доступен и готов для использования в продакшене.
Вывод результатов
Еще есть две интересные штуки, которые я хочу показать:
1. Pretty print, то есть красивый вывод данных на экран. При обычном SELECT JSON будет не форматирован.
2. Можно сказать, чтобы MySQL вывел результат в виде JSON array или JSON object, указать поля, и тогда вывод будет форматирован в виде JSON.
Полнотекстовый поиск внутри документов JSON
Если мы используем гибкую систему хранения и не знаем, что внутри нашего JSON, то было бы логично использовать полнотекстовый поиск.
К сожалению, полнотекстовый поиск имеет свои ограничения. Первое, что я попробовал — это просто создать полнотекстовый ключ. Я попытался сделать такую штуку:
К сожалению, это не работает. Даже в MySQL 8 создать полнотекстовый индекс просто по полю JSON, к сожалению, невозможно. Я бы конечно хотел иметь такую функцию — возможность поиска хотя бы по ключам JSON была бы очень полезна.
Но если это пока невозможно, давайте создадим виртуальную колонку. В нашем случае есть поле data, и нам интересно было бы посмотреть, что там внутри.
К сожалению, это тоже не работает — на виртуальной колонке нельзя создать полнотекстовый индекс.
Раз так, давайте создадим хранимую колонку. MySQL 5.7 позволяет объявить колонку хранимым полем.
В предыдущих примерах мы создавали виртуальные колонки, которые не хранятся, но индексы создаются и хранятся. В данном случае мне пришлось сказать MySQL, что это колонка STORED, то есть она будет создана и данные в нее будут скопированы. После этого MySQL создал полнотекстовый индекс, для этого пришлось пересоздать таблицу. Но это ограничение на самом деле InnoDB и InnoDB fulltext search: приходится пересоздавать таблицу, чтобы добавить специальный идентификатор полнотекстового поиска.
Интересно, что в MySQL 8 появилась новая кодировка UTF8MB4 для смайликов. Конечно, не совсем для них, а потому что в UTF8MB3 есть некоторые проблемы с русским, китайским, японским и другими языками.
Соответственно MySQL 8 должен хранить данные JSON в UTF8MB4. Но то ли из-за того, что Node.js коннектится к Device Cloud, и там записано что-то не так, или это баг бета-версии, этого не произошло. Поэтому мне пришлось сконвертировать данные, перед тем как записать их в хранимую колонку.
После этого я смог создать полнотекстовый поиск на двух полях: на названии JSON и на данных JSON.
Not only IoT
JSON — это не только интернет вещей. Он может использоваться для других интересных штук:
Некоторые вещи могут быть намного удобнее реализованы с помощью гибкой схемы хранения данных. На Oracle OpenWorld приводился отличный пример: резервирование мест в кинотеатре. Реализовать это в реляционной модели очень сложно — получается много зависимых таблиц, джойнов и т.д. С другой стороны, мы можем хранить весь зал как структуру JSON, соответственно, записывать его в MySQL в другие таблицы и использовать обычным образом: создать индексы на основе JSON и т.д. Сложные структуры удобно хранить в формате JSON.
Это дерево, которое было успешно посажено. К сожалению, через несколько лет его съели олени, но это уже совсем другая история.
Предыстория
Началось все с простого желания посадить фруктовое дерево на своем участке. Сделать это, казалось бы, очень просто — приходишь в магазин и покупаешь саженец. Но в Америке первый вопрос, который задают продавцы, это сколько дерево получит солнечного света. Для Александра это оказалось гигантской загадкой — совершенно неизвестно, сколько солнечного света на участке.
Чтобы это узнать, школьник мог бы каждый день выходить во двор, смотреть, сколько солнечного света, и записывать это в блокнотик. Но это не дело — надо все оснастить оборудованием и автоматизировать.
В ходе выступления многие примеры запускались и воспроизводились в прямом эфире. Хотите более полную картину, чем в тексте, переключайтесь на просмотр видео.
Итак, чтобы не записывать наблюдения о погоде в блокнотик, есть большое количество устройств для интернет-вещей — Raspberry Pi, новый Raspberry Pi, Arduino — тысячи разных платформ. Но я выбрал для этого проекта устройство, которое называется Particle Photon. Оно очень просто в использовании, стоит 19 $ на официальном сайте.
В Particle Photon хорошо то, что это:
- 100% облачное решение;
- подходят любые датчики, например, для 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 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, то он его и использует.
Более того, я бы мог в 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 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 не будет использовать индексы. В данном конкретном случае 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 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, который сейчас, на момент публикации уже доступен и готов для использования в продакшене.
Вывод результатов
Еще есть две интересные штуки, которые я хочу показать:
1. Pretty print, то есть красивый вывод данных на экран. При обычном 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: приходится пересоздавать таблицу, чтобы добавить специальный идентификатор полнотекстового поиска.
Интересно, что в 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.
