php mysql json array

Продвинутая работа с JSON в MySQL

У MySQL нет возможности напрямую индексировать документы JSON, но есть альтернатива: генерируемые столбцы.

С момента введения поддержки типа данных JSON в MySQL 5.7.8 не хватает одной вещи: способности индексировать значения JSON. Для того, чтобы обойти это ограничение, можно использовать генерируемые столбцы. Эта возможность, представленная в MySQL 5.7.5, позволяет разработчикам создавать столбцы, содержащие информацию, полученную из других столбцов, предопределенных выражений или вычислений. Генерируя столбец из значений JSON, а затем индексируя его, можно практически индексировать поле с JSON.

Набор данных в формате JSON, используемый в данной статье, можно скачать на Гитхабе. Он содержит список игроков со следующими элементами: идентификатор игрока, его имя и игры, в которые он играл (Battlefield, Crazy Tennis и Puzzler).

Генерация столбцов

Для создания генерируемых столбцов в операторе CREATE TABLE используется следующий синтаксис:

Этот код означает, что мы берём поле c JSON player_and_games и извлекаем значение из JSON по ключу «name» — дочернее по отношению к корню.

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

Уникальные для генерируемых столбцов ключевые слова VIRTUAL и STORED указывают на то, будут ли значения сохраняться в таблице.

Ключевое слово VIRTUAL используется по умолчанию. Оно означает, что значения столбца не сохраняются и не занимают место для хранения. Они вычисляются при каждом чтении строки. Если вы создаете индекс с виртуальным столбцом, значение всё же сохраняется — в индексе.

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

Запрос, создающий таблицу:

Заполнение таблицы тестовыми данными:

Содержимое таблицы players на Гисте или…

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

Индексирование генерируемых столбцов

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

Давайте сделаем простой запрос к генерируемому столбцу, чтобы увидеть, как он выглядит, прежде чем индексировать его. Изучив детали запроса при выборе names_virtual и имени «Sally», получим следующее:

Для этого запроса MySQL просматривает каждую строку, чтобы найти «Sally». Однако, можно получить совершенно другой результат, добавив индекс к столбцу:

Теперь, выполняя тот же запрос, получаем:

Можно проверить, были ли проиндексированы все наши столбцы, запустив:
Код Гисте или…

Теперь, когда созданы несколько индексов в генерируемых столбцах, давайте усложним поиск. В этом примере выбираются идентификаторы, имена, выигранные теннисные игры, уровень Battlefield и время Puzzler для игроков, которые имеют уровень выше 50, а также выигравших 50 теннисных игр. Все результаты будут упорядочены по возрастанию в соответствии с временем в Puzzler. Команда SQL и результаты будут выглядеть так:

Давайте посмотрим, как MySQL выполнял этот запрос:

При использовании индексов win_idx и level_idx MySQL приходилось обращаться к двум столбцам, чтобы вернуть желаемый результат. Если запрос должен выполнить полный просмотр таблицы с миллионом записей, это займёт очень много времени. Однако, с помощью генерируемых столбцов и их индексированием, MySQL показал очень быстрый результат и удобный способ поиска элементов в JSON-данных.

Тем не менее остается один вопрос: для чего нужны STORED генерируемые столбцц? Как их использовать и как они работают?

Хранение значений в генерируемых столбцах

Использование ключевого слова STORED при настройке генерируемого столбца обычно не предпочтительно, поскольку в основном значения в таблице сохраняются дважды: поле с JSON и в STORED столбце. Тем не менее, существует три сценария, когда в MySQL нужно использовать столбец STORED :

В то же время, если не устанавливать индекс первичного ключа и попытаться вставить данные, MySQL выдает сообщение об ошибке:

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

Вывод

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

Источник

Использование JSON в MySQL

Обратите внимание, что любая база данных будет принимать документы JSON как одностроковый BLOB-объект. Однако MySQL и PostgreSQL поддерживают проверенные данные JSON в реальных парах ключ / значение, а не в базовой строке.

Нормализация — это метод, используемый для оптимизации структуры базы данных. Правило первой нормальной формы (1NF) гласит, что каждый столбец должен содержать одно значение, что явно нарушается при хранении многозначных документов JSON.

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

Тем не менее, есть хорошие варианты использования JSON для редко заполненных данных или настраиваемых атрибутов.

Создать таблицу с полем JSON

Рассмотрим магазин по продаже книг. У всех книг есть идентификатор, ISBN, название, издатель, количество страниц и другие четкие данные о взаимоотношениях. Предположим, вы хотите добавить к каждой книге любое количество тегов категорий. Вы можете добиться этого в SQL, используя:

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

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

Добавление данных JSON

Целые документы JSON можно передавать в операторах INSERT или UPDATE. Например, наши книжные теги могут быть переданы в виде массива (внутри строки):

JSON также можно создать с помощью этих:

Функция JSON_TYPE () позволяет проверять типы значений JSON. Он должен возвращать OBJECT, ARRAY, скалярный тип (INTEGER, BOOLEAN и т. Д.), NULL или ошибку. Например:

Функция JSON_VALID () возвращает 1, если JSON действителен, или 0 в противном случае:

Попытка вставить недопустимый документ JSON вызовет ошибку, и вся запись не будет вставлена ​​/ обновлена.

Поиск данных JSON

Функция JSON_CONTAINS () принимает документ JSON, в котором выполняется поиск, и другой документ для сравнения. Он возвращает 1, когда найдено совпадение. Например:

Аналогичная функция JSON_SEARCH () возвращает путь к заданному совпадению или NULL, если совпадения нет. Он передал документ JSON, в котором выполняется поиск, ’one’чтобы найти первое совпадение или ’all’найти все совпадения, и строку поиска (где %соответствует любому количеству символов и _соответствует одному символу идентично LIKE). Например:

Пути JSON

Путь JSON нацелен на значения и может использоваться для извлечения или изменения частей документа JSON. Функция JSON_EXTRACT () демонстрирует это, извлекая одно или несколько значений:

Следующие примеры относятся к следующему документу JSON:

Извлечение путей JSON в запросах

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

Для более сложного примера предположим, что у вас есть таблица пользователей с данными профиля JSON. Например:

Вы можете извлечь имя Twitter, используя путь JSON. Например:

Вы можете использовать путь JSON в предложении WHERE, чтобы возвращать только пользователей с учетной записью Twitter:

Изменение части документа JSON

Есть несколько функций MySQL для изменения частей документа JSON с использованием записи пути. Они включают:

Поэтому вы можете добавить «технический» тег к любой книге, в которой уже есть тег «JavaScript»:

Дальнейшая информация

Руководство MySQL предоставляет дополнительную информацию о типе данных JSON и связанных функциях JSON.

Опять же, я призываю вас не использовать JSON, если в этом нет крайней необходимости. Вы можете эмулировать всю документно-ориентированную базу данных NoSQL в MySQL, но это сведет на нет многие преимущества SQL, и вы также можете переключиться на настоящую систему NoSQL! Тем не менее, типы данных JSON могут сэкономить усилия для более неясных требований к данным в приложении SQL.

Источник

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

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

Предыстория

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

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

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

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

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

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

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

Таким образом, девайс лежит во дворе, подсоединяется по 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, потому что:

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

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

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

Мы совершенно не знаем, что это за данные: 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, то он его и использует.

С этим на самом деле есть небольшая проблемка. Допустим, я хочу отсортировать данные не только по 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 — это не только интернет вещей. Он может использоваться для других интересных штук:

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

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

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

Источник