Хранение JSON в базе данных и наличие нового столбца для каждого ключа

Ответ 1

Обновлено 4 июня 2017 года

Учитывая, что этот вопрос/ответ приобрели некоторую популярность, я решил, что стоит обновить.

Когда этот вопрос был первоначально опубликован, MySQL не поддерживал типы данных JSON, и поддержка в PostgreSQL была в зачаточном состоянии. Начиная с 5.7, MySQL теперь поддерживает тип данных JSON (в двоичном формате хранения) и PostgreSQL JSONB значительно вырос. Оба продукта предоставляют исполняемые JSON-типы, которые могут хранить произвольные документы, включая поддержку индексирования определенных ключей объекта JSON.

Тем не менее, я по-прежнему придерживаюсь своего первоначального утверждения о том, что предпочтение по умолчанию при использовании реляционной базы данных должно быть равно столбцу. Реляционные базы данных по-прежнему построены в предположении, что данные в них будут достаточно хорошо нормализованы. Планировщик запросов имеет лучшую информацию по оптимизации при просмотре столбцов, чем при просмотре ключей в документе JSON. Внешние ключи могут быть созданы между столбцами (но не между ключами в документах JSON). Важно: если большинство вашей схемы достаточно волатильны, чтобы оправдывать использование JSON, вам может потребоваться, по крайней мере, рассмотреть возможность выбора реляционной базы данных.

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

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

• Сохранение пользовательских настроек произвольного ключа/значения (где значение может быть логическим, текстовым или числовым, и вы не хотите иметь отдельные столбцы для разных типов данных)

• Сохранение данных конфигурации, не имеющих определенной схемы (если вы создаете Zapier или IFTTT и должны хранить данные конфигурации для каждой интеграции)

Я уверен, что есть и другие, но это всего лишь несколько простых примеров.

Оригинальный ответ

Если вы действительно хотите добавить столько полей, сколько хотите, без ограничений (кроме ограничения размера произвольного документа), рассмотрите решение NoSQL, такое как MongoDB.

Для реляционных баз данных: используйте один столбец за значение. Помещение капли JSON в столбце делает практически невозможным запрос (и очень медленно, когда вы действительно находите запрос, который работает).

Реляционные базы данных используют преимущества типов данных при индексировании и предназначены для реализации с нормализованной структурой.

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

Ответ 2

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

Другие люди хорошо ответили, что такое технический компромисс.

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

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

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

Сверхурочная работа, добавление новых функций, наличие данных в JSON привело к более сложным поисковым запросам, чем то, что могло бы быть добавлено, если мы придерживались традиционных столбцов. Итак, мы начали промывать некоторые ключевые значения обратно в столбцы, чтобы мы могли создавать соединения и проводить сравнения между значениями. Плохая идея. Теперь у нас было дублирование. Новый разработчик появится на борту и будет смущен? Какую ценность мне нужно вернуть? JSON один или столбец?

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

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

Подумайте, долго и упорно о том, что природа ваших данных. Это основа вашего приложения. Каким образом данные будут использоваться с течением времени. И как это возможно ИЗМЕНИТЬ?

Ответ 3

Просто отбросьте его там, но WordPress имеет структуру для такого рода вещей (по крайней мере WordPress был первым местом, где я его наблюдал, вероятно, возникла в другом месте).

Он позволяет безграничные ключи и быстрее выполняет поиск, чем с использованием JSON-blob, но не так быстро, как некоторые из решений NoSQL.

uid   |   meta_key    |   meta_val
----------------------------------
1         name            Frank
1         age             12
2         name            Jeremiah
3         fav_food        pizza
.................

ИЗМЕНИТЬ

Для хранения истории/нескольких клавиш

uid   | meta_id    |   meta_key    |   meta_val
----------------------------------------------------
1        1             name            Frank
1        2             name            John
1        3             age             12
2        4             name            Jeremiah
3        5             fav_food        pizza
.................

и запрос через что-то вроде этого:

select meta_val from `table` where meta_key = 'name' and uid = 1 order by meta_id desc

Ответ 4

Недостатком подхода является то, о чем вы говорили:

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

значение за столбец вместо этого соответствует всей строке.

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

Изменить:. Чтобы прояснить, вышесказанное относится к классическим реляционным базам данных. NoSQL использует JSON внутренне и, вероятно, является лучшим вариантом, если это желаемое поведение.

Ответ 5

В принципе, первая используемая вами модель называется хранилищем на основе документов. Вы должны взглянуть на популярную базу данных на основе NoSQL, такую ​​как MongoDB и CouchDB. В основном, в db на основе документа вы храните данные в json файлах, а затем можете запрашивать эти json файлы.

Вторая модель - популярная структура реляционной базы данных.

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

Чтобы ответить на ваш второй вопрос, , вы не можете запросить имя типа "foo", если вы используете первую модель.

Ответ 6

Похоже, вы в основном сомневаетесь, использовать ли реляционную модель или нет.

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

Если у вас есть только один (или несколько заранее определенных) уровней атрибутов для вашего основного объекта (пользователя), вы все равно можете использовать модель атрибута сущности (EAV) в реляционной базе данных. (Это также имеет свои плюсы и минусы.)

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

Если вы использовали PostgreSQL, вы могли бы получить лучшее из обоих миров. (Это действительно зависит от фактической структуры данных здесь... MySQL не обязательно является неправильным выбором, и опции NoSQL могут представлять интерес, я просто предлагаю альтернативы.)

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

EDIT:

Поскольку не будет слишком большого количества столбцов, на которых мне нужно выполнить поиск, целесообразно ли использовать обе модели? Ключ за столбец для данных Мне нужно искать и JSON для других (в той же базе данных MySQL)?

Смешивание двух моделей не обязательно неверно (при условии, что дополнительное пространство является незначительным), но это может вызвать проблемы, если вы не убедитесь, что два набора данных синхронизированы: ваше приложение никогда не должно меняться без использования обновление другого.

Хорошим способом добиться этого было бы заставить триггер выполнить автоматическое обновление, запустив хранимую процедуру на сервере базы данных всякий раз, когда производится обновление или вставка. Насколько мне известно, язык хранимой процедуры MySQL, вероятно, не имеет поддержки для какой-либо обработки JSON. Опять же, PostgreSQL с поддержкой PLV8 (и, возможно, с другими RDBMS с более гибкими языками хранимых процедур) должен быть более полезным (обновление вашего реляционного столбца автоматически с помощью триггера очень похоже на обновление индекса таким же образом).

Ответ 7

Вы пытаетесь установить нереляционную модель в реляционную базу данных, я думаю, вам будет лучше работать с использованием базы данных NoSQL, такой как MongoDB. Нет предопределенной схемы, которая соответствует вашему требованию не иметь ограничения на количество полей (см. Типичный пример коллекции MongoDB). Ознакомьтесь с документацией MongoDB , чтобы получить представление о том, как вы запрашиваете свои документы, например.

db.mycollection.find(
    {
      name: 'sann'
    }
)

Ответ 8

Некоторое время присоединение к таблице будет накладным. скажем, для OLAP. если у меня две таблицы, одна из них - таблица ORDERS, а другая - ORDER_DETAILS. Для получения всех деталей заказа мы должны присоединиться к двум таблицам, это сделает запрос медленнее, если ни один из строк в таблицах не увеличится, скажем, в миллионах или около того. Соединение слева/справа слишком медленнее, чем внутреннее соединение. Думаю, если мы добавим строку JSON/Object в соответствующую запись ORDERS, JOIN будет избегать. добавить генерацию отчета будет быстрее...

Ответ 9

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