Поддержка родного JSON в MYSQL 5.7: каковы плюсы и минусы JSON-типа данных в MYSQL?

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

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

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

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

Удобство - дополнительный встроенный синтаксис для столбцов JSON делает его Очень естественно интегрировать запросы документов в ваш SQL. За пример (features.feature - это столбец JSON): SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

ВАУ ! они включают в себя некоторые замечательные функции. Теперь стало проще манипулировать данными. Теперь можно хранить более сложные данные в столбце. Таким образом, MySQL теперь приправлен NoSQL.

Теперь я могу представить запрос для данных JSON что-то вроде

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN 
( 
SELECT JSON_EXTRACT(data,"$.inverted") 
FROM t1 | {"series": 3, "inverted": 8} 
WHERE JSON_EXTRACT(data,"$.inverted")<4 );

Так можно ли хранить огромные маленькие отношения в нескольких столбцах? Это хорошо? Это нарушает нормализацию. Если это возможно, то я думаю, что он будет действовать как NoSQL в столбце MySQL. Я действительно хочу узнать больше об этой функции. Плюсы и минусы типа данных MySQL JSON.

Ответ 1

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

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

Заявление об "эффективном доступе" вводит в заблуждение. Это означает, что после того, как запрос исследует строку с документом JSON, он может извлечь поле, не анализируя текст синтаксиса JSON. Но для поиска строк все равно требуется сканирование таблицы. Другими словами, запрос должен проверять каждую строку.

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

MySQL 5.7 позволяет вам определять виртуальный столбец в таблице, а затем создавать индекс для виртуального столбца.

ALTER TABLE t1
  ADD COLUMN series AS (JSON_EXTRACT(data, '$.series')),
  ADD INDEX (series);

Затем, если вы запросите виртуальный столбец, он может использовать индекс и избежать сканирования таблицы.

SELECT * FROM t1
WHERE series IN ...

Или даже если вы запросите точное выражение, на котором основан виртуальный столбец (как в исходном запросе), он также может использовать индекс.

Это хорошо, но в некотором смысле упускает смысл использования JSON. Привлекательной частью использования JSON является то, что он позволяет добавлять новые атрибуты без необходимости выполнять ALTER TABLE. Но оказывается, что вы все равно должны определить дополнительный (виртуальный) столбец, если хотите искать в полях JSON с помощью индекса.

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

SELECT JSON_EXTRACT(data, '$.series') AS series FROM t1
WHERE <other conditions>

Я бы вообще сказал, что это лучший способ использовать JSON в MySQL. Только в списке выбора.

Когда вы ссылаетесь на столбцы в других предложениях (JOIN, WHERE, GROUP BY, HAVING, ORDER BY), эффективнее использовать обычные столбцы, а не поля в документах JSON.

Я выступил с докладом под названием Как использовать JSON в MySQL Wrong на конференции Percona Live в апреле 2018 года. Я обновлю и повторю доклад на Oracle Code One осенью.

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

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

Вы должны использовать JSON, когда JSON делает ваши запросы более эффективными.

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

Ответ 2

Следующее из MySQL 5.7 привносит сексуальную обратную связь с JSON, звучит хорошо для меня:

Использование JSON Data Type в MySQL имеет два преимущества: сохранение строк JSON в текстовом поле:

Проверка данных. Документы JSON будут автоматически проверены и неверные документы приведут к ошибке. Улучшено внутреннее хранилище формат. Данные JSON преобразуются в формат, который позволяет быстро читать доступ к данным в структурированном формате. Сервер способен lookob subobjects или вложенные значения с помощью ключа или индекса, что позволяет добавлять гибкость и производительность.

...

Специализированные ароматы магазинов NoSQL (Базы данных документов, хранилища ключей и графические DB), вероятно, лучше варианты их конкретных случаев использования, но добавление этого тип данных может позволить вам снизить сложность вашей технологии стек. Цена связана с MySQL (или совместимыми) базами данных. Но это не проблема для многих пользователей.

Обратите внимание на язык проверки документа, поскольку это важный фактор. Я думаю, что батарея тестов должна быть выполнена для сравнения двух подходов. Этими двумя являются:

Mysql с типами данных JSON
Mysql без

В сети есть только мелкие слайды на данный момент по теме mysql/json/performance из того, что я вижу.

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

Ответ 3

В последнее время я попал в эту проблему, и я суммирую следующие впечатления:

1, Невозможно решить все вопросы. 2, вы должны использовать JSON правильно.

Один случай:

У меня есть таблица с именем: CustomField, и она должна состоять из двух столбцов: name, fields. name - это локализованная строка, содержимое должно выглядеть следующим образом:

{
  "en":"this is English name",
  "zh":"this is Chinese name"
   ...(other languages)
}

И fields должен выглядеть следующим образом:

[
  {
    "filed1":"value",
    "filed2":"value"
    ...
  },
  {
    "filed1":"value",
    "filed2":"value"
    ...
  }
  ...
]

Как вы можете видеть, как name, так и fields можно сохранить как JSON, и он работает!

Однако, если я использую name для поиска этой таблицы очень часто, что мне делать? Используйте JSON_CONTAINS, JSON_EXTRACT...? Очевидно, что не стоит больше сохранять его как JSON, мы должны сохранить его в независимой таблице: CustomFieldName.

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

Почему MYSQL поддерживает JSON?
Почему вы хотите использовать JSON? Вам нужна была ваша бизнес-логика? Или есть что-то еще?
Никогда не ленитесь

Спасибо

Ответ 4

По моему опыту, реализация JSON по крайней мере в MySql 5.7 не очень полезна из-за ее низкой производительности. Ну, это не так плохо для чтения данных и проверки. Однако модификация JSON в MySql в 10-20 раз медленнее, чем в Python или PHP. Давайте представим очень простой JSON:

{ "name": "value" }

Предположим, нам нужно преобразовать его в нечто подобное:

{ "name": "value", "newName": "value" }

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

У меня есть таблица с 40 миллионами строк, и скрипт Python обновляет ее за 3-4 часа.

Теперь у нас есть MySql JSON, поэтому нам больше не нужен Python или PHP, мы можем сделать что-то подобное:

UPDATE 'JsonTable' SET 'JsonColumn' = JSON_SET('JsonColumn', "newName", JSON_EXTRACT('JsonColumn', "name"))

Выглядит просто и отлично. Однако его скорость в 10-20 раз ниже, чем у версии Python, и это единая транзакция, поэтому другие приложения не могут изменять данные таблицы параллельно.

Итак, если мы хотим просто дублировать ключ JSON в таблице из 40 миллионов строк, нам не нужно использовать таблицу вообще в течение 30-40 часов. Это не имеет смысла.

Что касается чтения данных, из моего опыта прямой доступ к полю JSON через JSON_EXTRACT в WHERE также чрезвычайно медленный (намного медленнее, чем TEXT с LIKE в неиндексированном столбце). Виртуальные сгенерированные столбцы работают намного быстрее, однако, если мы заранее знаем нашу структуру данных, нам не нужен JSON, вместо этого мы можем использовать традиционные столбцы. Когда мы используем JSON, где это действительно полезно, я. е. когда структура данных неизвестна или часто изменяется (например, пользовательские настройки плагина), создание виртуальных столбцов на регулярной основе для любых возможных новых столбцов не выглядит хорошей идеей.

Python и PHP делают проверку JSON как обаяние, поэтому сомнительно, нужна ли нам проверка JSON на стороне MySql. Почему бы не проверить XML, документы Microsoft Office или проверить орфографию? ;)