Как оптимизировать таблицу с JSON строкой в mysql?

Question

[psiklop]

Задам абстрактный вопрос покороче, хотя такое часто встречается.

1 вариант создать таблицу с двух столбцов: Login и Data - в поле Data json массив к примеру около 50 элементов на 5-10KB
2 вариант создать таблицу с трех столбцов: Login и JSON массив развернуть на ID и Value

Как сделать оптимальней, нужно читать и писать данные? замечу искать по ID и Value нет надобности, только по Login

Comments

[Akina]

нужно читать и писать данные

Если читать - то все сразу, а не некоторые?
Если писать - то полностью уничтожая предыдущее значение?

[Adamos]

Вообще-то второй вариант - это ДВЕ таблицы.
Оптимальней и правильней - определяется тем, как с этими данными нужно (будет) работать.
Высасывать что-то из пальца только по дилемме "два или три столбца" - просто нелепо.

[psiklop]

Akina, читать по разному, но скорее всего все сразу. Писать и добавлять. А еще удалять по одному. Разные варианты, иначе вопрос бы не имел смысла. Как раз в том, что JSON нужно кодировать раскодировать всегда, даже если я используя JSON функции mysql - я так понимаю они делают практически тоже самое что json_decode.

[psiklop]

Adamos, ну почему же две таблицы ? Нелепо ?

[Akina]

читать по разному, но скорее всего все сразу. Писать и добавлять. А еще удалять по одному. Разные варианты, иначе вопрос бы не имел смысла.

Вопрос СЕЙЧАС не имеет смысла - потому что не сказано вообще ничего вменяемого (в том числе и в комментариях к данным уже ответам). Даже точной версии MySQL - и то не указано. А потому любые советы в нынешнем состоянии вопроса - не более чем тыкание пальцем в небо. И рассчитывать на то, что любой совет из данных на основании имеющегося никакого описания окажется оптимальным - это верх оптимизма.
Впрочем, нам-то как раз пофиг - это у Вас потом возможны проблемы из-за неверно выбранной стратегии.

[psiklop]

Akina, тогда снова повторюсь ответ не имеет смысла, тем более уточняющие вопросы хрень, я уже понял максимум толковых советов, чем от Lastor я тут не получу, сейчас тебе версию MYSQL подавай - потом снова будет недоволен, а где версия убунты и тп
P.S. я же не олень северный - логика есть у тебя ? Весь софт на сервере обновляется периодически - поэтому догадывайся, что все свежее - если тебе это важно для решения.

[Akina]

psiklop, а чего ты, мил друг, вздрючился-то ни с того ни с сего? Видать, и сам понимаешь, что вопрос ни о чём...

Или ты из тех, кто всегда прав? ну и так бывает...

[psiklop]

Akina, вопрос решен - понимаешь ? Mysql стоит 8 или даже MariaDB на другом компе, доволен ? Вопроса тут не понял ты и потому "вздрючился" но я в том не виноват, ты уточнил я ответил - но ты остался недоволен уточнением, я не могу тебе в этом помочь.

[psiklop]

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

Answer 1

[SymphoGraph]

Если вы уверены в том, что в обозримом будущем вам не понадобится поиск по другим полям или вы точно не захотите сделать join с их участием, то json будет работать быстрее. И сохранять его проще.
А если потом захотите, то придётся создать дополнительные таблицы, и сохранить как нормализованные данные.
Но эта задача не сложна и всего лишь потенциальна (может и не понадобится).

Comments

[psiklop]

Интересно! Читать скорее всего нужно будет все.
Даже если нужно часто добавлять по строке и удалять по строке - все равно лучше JSON ?
P.S. Немного сам себе противоречу - ведь удаление по ID и есть в какой-то мере поиск.

[psiklop]

JOIN по json точно не нужен, он скорее был бы удобнее по Login и данные одной строкой в столбце Data бы пригодились. Но если JSON не оптимален можно этим пожертвовать и написать второй SELECT

[SymphoGraph]

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

удаление по ID и есть в какой-то мере поиск

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

[psiklop]

SymphoGraph, понял, сделаю вначале без JSON - я так и собирался на 80%

[Ипатьев]

Для этого действительно нужно забирать и сохранять весь объект полностью.

может быть, стоит подучить матчасть? :)

[psiklop]

Ипатьев, да подучи

Answer 2

[Ипатьев]

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

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

Кроме того, никакого "login" в качестве идентификатора быть не должно, а должен быть первичный ключ.

Comments

[psiklop]

Сам по себе ответ бессмысленный, ибо выше уже решен насколько это возможно. Структура данных описана это массив из 50 элементов длиной 10КБ, нужны самые простые функции массива добавить и удалить, искать сортировать и тп не нужно. Первичный ключ в этой таблице не нужен.

[Ипатьев]

Массив из 50 элементов - это таблица из 50 колонок, а самые простые функции добавить и удалить - это запрос UPDATE

Первичный ключ в этой таблице не нужен.

Сразу видно крупного специалиста по базам данных :)

[psiklop]

Ипатьев, сразу видно говоруна, важно говорить умеем, делать не факт. Я могу сделать первичный ключ одним движением - но он мне тут не нужен. Я не знаю как мне его тут использовать и что он тут сможет оптимизировать. Я все равно буду делать выборку по логину и дал полю логин index mysql для скорости.

Вот в этом и был вопрос хранить как JSON и кодировать всю строку при каждом изменении или разложить на столбцы.

P.S. не 50 колонок - будет всего одна лишняя колонка, я описал выше.

[Ипатьев]

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

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

[psiklop]

Ипатьев, Дурацкий вопрос уже решен. Отчаливай, спец. Догадливым тоже быть полезно, а придираться к каждому слову и дурачок может. Будет нужен сделаю - я не буду редактировать эти строки по ключу. Я понимаю с ним может быстрее удалять и делать UPDATE, или чтобы не было дедлоков, но в данном случае придется обойтись.

[Ипатьев]

Да-да, надо обязательно вставить умное слово "дедлоки", чтобы казаться себе умнее :)

[psiklop]

Ипатьев, господи вот дурак, просто дедлоки в mysql реально достают и я пока не разобрался в них даже на 25% Чего пришел не одного полезного слова не написал в теме, бывшие заслуги что-ли требуют гадить в сети.

[Ипатьев]

Я обновил ответ.
Когда наберетесь достаточно опыта, перечитаете.

[psiklop]

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

[psiklop]

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

[Ипатьев]

Я же рекомендовал перечитать. Там написан ответ на этот вопрос. Превращать JSON в EAV глупо.

[psiklop]

Ипатьев, твое мнение по этому вопросу понял.

Answer 3

[psiklop]

Написал на коленке тест, JSON в таблицу на 10кб и скрипт который гоняет 1000 раз select + decode + encode + update.
Результат хреновый, слабый сервер грузит, даже 100 человек на сайте обновляющие страницу раз в 10 сек сделают нагрузку.

Заодно потестил встроенные JSON функции с ними результат как будто лучше.

Нагнал по второму варианту в таблицу 40.000 строк и 400 логинов результат лучше ощутимо скорость x10 (при условии, что полю login дать индекс)