каково реальное преимущество json внутри mysql [duplicate]

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

    DROP TABLE IF EXISTS Person

 CREATE TABLE Person 
 ( _id int identity constraint PK_JSON_ID primary key,
 value nvarchar(max)
 CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON(value)>0 )
 )

Эта простая структура похожа на стандартную коллекцию NoSQL, которую вы можно создавать в базах данных NoSQL (например, Azure DocumentDB или MongoDB), где у вас есть только ключ, представляющий идентификатор и значение, представляющее JSON.

Обратите внимание, что NVARCHAR - это не просто обычный текст. SQL Server имеет встроенный механизм сжатия текста, который может прозрачно сжимать данные, хранящиеся на диске. Сжатие зависит от языка и может достигать 50% в зависимости от ваших данных (см. Сжатие UNICODE).

Ключевое различие между SQL-сервером и другими обычными базами данных NoSQL заключается в том, что SQL Server позволяет использовать гибридную модель данных где вы можете хранить несколько объектов JSON в одной и той же «коллекции» и объединять их с регулярными реляционными столбцами.

В качестве примера представьте, что мы знаем, что каждый человек в вашей коллекции будет иметь имя FirstName и LastName, и что вы можете хранить общую информацию о человеке как о одном объекте JSON, а также о телефонных номерах / адресах электронной почты в качестве отдельных объектов. В SQL Server 2016 мы можем легко создать эту структуру без дополнительного синтаксиса:

DROP TABLE IF EXISTS Person

CREATE TABLE Person (

 PersonID int IDENTITY PRIMARY KEY,

 FirstName nvarchar(100) NOT NULL,

 LastName nvarchar(100) NOT NULL,

 AdditionalInfo nvarchar(max) NULL,

 PhoneNumbers nvarchar(max) NULL,

 EmailAddresses nvarchar(max) NULL
 CONSTRAINT [Email addresses must be formatted as JSON array]
 CHECK ( ISJSON(EmailAddresses)>0 )

 )

Вместо единого объекта JSON вы можете упорядочить свои данные в этой «коллекции». Если вы не хотите явно проверять структуру каждого столбца JSON, вам не нужно добавлять ограничение проверки JSON для каждого столбца (в этом примере я добавил ограничение CHECK только в столбец EmailAddresses).

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

Если вы сравните эту структуру со схемой таблицы Person AdventureWorks, вы можете заметить, что мы удалили много связанных таблиц.

Рядом простота схемы, ваши операции доступа к данным будут проще по сравнению с сложной реляционной структурой. Теперь вы можете читать одну таблицу вместо объединения нескольких таблиц. Когда вам нужно вставить нового человека со связанной информацией (адреса электронной почты, номера телефонов), вы можете вставить одну запись в одну таблицу вместо того, чтобы вставлять одну запись в таблицу AdventureWorks Person, используя столбец идентификатора, чтобы найти внешний ключ, который будет использоваться для хранения телефонов , адреса электронной почты и т. д. Кроме того, в этой модели вы можете легко удалить одиночную строку без каскадных удалений с использованием внешних ключей.

Базы данных NoSQL оптимизированы для простых операций чтения, вставки и удаления - SQL Сервер 2016 позволяет применять одну и ту же логику в реляционной базе данных.

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

ALTER TABLE Person
 ADD CONSTRAINT [Age should be number]
 CHECK ( ISNUMERIC(JSON_VALUE(value, '$.age'))>0 )

 ALTER TABLE Person
 ADD CONSTRAINT [Person should have skills]
 CHECK ( JSON_QUERY(value, '$.skills') IS NOT NULL)
First constraint will take the value of $.age property and check is this numeric value. Second constraint will try to find JSON object in $.skills property and verify that it exists. The following INSERT statements will fail due to the violation of constraints:



INSERT INTO Person(value)
 VALUES ('{"age": "not a number", "skills":[]}')

 INSERT INTO Person(value)
 VALUES ('{"age": 35}')

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

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

CREATE TABLE Person

 ( _id int identity constraint PK_JSON_ID primary key,

 data varbinary(max),

 value AS CAST(DECOMPRESS(data) AS nvarchar(max))

 )



 INSERT INTO Person(data)

 VALUES (COMPRESS(@json))

Функции COMPRESS и DECOMPRESS используют стандартное сжатие GZip. Если ваш клиент может обрабатывать сжатие GZip (например, браузер, который понимает содержимое gzip), вы можете напрямую возвращать сжатый контент. Обратите внимание, что это компромисс производительности / хранения. Если вы часто запрашиваете сжатые данные, у вас есть более медленная производительность, потому что текст должен каждый раз распаковываться.

Примечание. Функции JSON доступны только в SQL Server 2016+ и Azure SQL Database.

Подробнее можно прочитать из источника этой статьи

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/

Вопрос, который вы должны задать, это:

Я привязан к использованию только этой базы данных?

DO

1. Если вы можете использовать другую базу данных для хранения JSON, используйте решение для хранения документов, такое как CouchDB, DynamoDB или MongoDB.
2. Используйте эти возможности DB хранилища документов для индексирования и поиска иерархических данных.
3. Используйте реляционную базу данных для ваших реляционных данных.
4. Используйте реляционную базу данных для отчетов, хранилищ данных и интеллектуального анализа данных.

НЕ НЕ

1. Храните JSON в качестве строки, если это возможно.
2. Попробуйте указать максимальную длину данных JSON.
3. Используйте varchar для хранения JSON (используйте текст / blob, если вы must /.
4. Попробуйте выполнить поиск в сохраненных JSON для значений.
5. Беспокойство об экранировании JSON для хранения в виде строки.

Это слишком долго для комментария.

Если это было «абсолютно неправильно», большинство баз данных не поддержали бы его. Хорошо, большинство баз данных поддерживают запятые в предложении FROM, и я считаю, что это «абсолютно неправильно». Но поддержка JSON - это новая разработка, а не обратная совместимость «функции».

. Один очевидный случай: структура JSON - это просто BLOB, который передается обратно в приложение. Тогда нет дебатов, кроме того, накладные расходы на хранение JSON, что излишне многословно для структурированных данных с общими полями в каждой записи.

Другим случаем является случай «редких» столбцов. У вас есть строки со многими возможными столбцами, но они варьируются от строки к строке.

Другой случай - это когда вы хотите хранить «вложенные» записи в записи. JSON является мощным.

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

«Золотое правило», которое я использую в ручном режиме, заключается в том, что если мне нужен JSON в его необработанном формате, это нормально хранить. Если я должен сделать особую точку разбора, то это не так.

Например, если я создаю API, который отправляет raw JSON, и по какой-либо причине это значение не будет измените, тогда okay сохранит его как сырой JSON. Если мне нужно разобрать его, измените его, обновите и т. Д. ... тогда не так много.

Я буду махать волшебной палочкой. Пуф! Золотые правила использования JSON:

• Если MySQL не нужно искать внутри JSON, а для приложения просто нужен набор материалов, тогда JSON в порядке, возможно, даже лучше.
• Если вы будете искать данные, находящиеся внутри и , у вас есть MariaDB 10.0.1 или MySQL 5.7 (с типом и функциями JSON), тогда JSON может быть практичным. «Dynamic» столбца MariaDB 5.3 является вариантом.
• Если вы делаете вещи «Entity-Attribute-Value», то JSON не очень хорош, но это наименьшее из нескольких зол. http://mysql.rjweb.org/doc.php/eav
• Для поиска по индексированному столбцу, не имея значения, заложенного внутри JSON, большой плюс.
• Для поиска по диапазону в индексированном столбце или в поиске FULLTEXT или SPATIAL JSON не представляется возможным.
• Для WHERE a=1 AND b=2 «составной» индекс INDEX(a,b) отлично; вероятно, не может приблизиться к JSON.
• JSON хорошо работает с «разреженными» данными; INDEXing работает, но не так, с такими. (Я имею в виду значения, которые «отсутствуют» или NULL для многих строк.)
• JSON может дать вам «массивы» и «деревья», не прибегая к дополнительным таблицам. Но выкачайте в такие массивы / деревья только в приложении, , а не в SQL.
• JSON - это мир лучше, чем XML. (Мое мнение)
• Если вы не хотите попасть в строку JSON, кроме приложения, я рекомендую сжать (в клиенте) его сохранение в BLOB. Подумайте об этом как .jpg - там есть материал, но SQL все равно.

Укажите ваше приложение; возможно, мы можем быть более конкретными.