Как строить архитектуру БД, где у юзеров много полей?
Здравствуйте,
Специфика проекта такова, что у каждого юзера есть достаточно обширный профиль из 50-100 вопросов, при этом некоторые вопросы поддерживают один ответ, а некоторые несколько. Мне не составило больших проблем привести все варианты ответов к уникальным integer в рамках вопросов, обладающих между собой так же уникальными числовыми идентификаторами. То есть мы имеем дело с целыми числами.
По ряду причин мне очень импонирует такая структура данных (здесь и ниже вариант "А"):
При такой модели на одного юзера всегда приходится одна строка. Эта модель очень удобна для поиска строк по user_id, при том что id добавляются последовательно, и не будут расщеплять индексы, при обновлении я так же контролирую логику, чтобы такого не могло происходить. Это понятно.
Но, как я отметил, некоторые вопросы профиля поддерживают несколько ответов, то есть простые массивы из нескольких integer значений. Можно использовать тип данных столбца json или blob для столбцов, отвечающих за такие вопросы.
Если делать архитектуру "по книжке" (здесь и ниже вариант "Б"):
user_id | answer_variable ,
где для одного user_id будет очень много строк, я не вижу, где бы это вообще выигрывало, кроме ухода от массивов.
Вот и думаю, что теоретически быстрее, скажем, при 100 000 юзеров:
А. Очень быстро найти строку юзера, но затем есть необходимость провести с ней некоторые дополнительные манипуляции, или
Б. В таблице-куче искать все строки для user_id, но по завершению поиска получим очень приятный аккуратный список значений.
Тогда в таблице "А" будет 100 000 строк и 50 столбцов, в то время как в таблице "Б" будет 5 000 000 строк и 2 столбца. Но в первом варианте я всегда сначала выбираю одну строку и быстро получаю выборку в 50 полей.
Можете ли вы что-то посоветовать, как оптимизировать работу с таблицей типа "А", чтобы сгладить/минимизировать издержки работы со строкой, имеющей помимо integer ещё json (serialized)? Или вообще второй / свой вариант?
Евгений Самсонов, я сам по специальности не из IT, я владелец проекта, но вынужден в такое вникать, потому что жизнь показывает, что никому неинтересно, кроме меня, как сделать лучше. Сделают на подряде так, чтобы работало. Потом окажется, что могло в 20 раз быстрее работать.
Я стараюсь рассматривать все варианты, про это тоже уже читал. Пока я не очень понял, какие именно преимущества я получу от такой миграции. Я соотнёс преимущества NoSQL и не вижу, чтобы они были мне нужны именно в моей ситуации. У меня кроме описываемой таблицы ещё очень много таблиц, но те пока полностью укладываются в реляционную модель.
Анатолий, просто решение А больше подходит к NoSQL, как мне кажется, хотя можно и в SQL сделать. И мигрировать все не обязательно, а использовать конкретный инструмент там, где он больше всего подходит
Все по чему нет поиска, то выносится в json.
Если же нужно искать по json например поле парни из 7 бара, для этого создается новая таблица.
И уже оттуда берется список для поиска по Id из основной таблицы
Пока что поиска по этим полям не планируется. Достаточно просто извлекать массив из нужной ячейки целиком.
Я думал над таким вариантом тоже. Всё отлично, но никак не придумаю, как лучше всего избегать скачков в весе строк. К примеру:
uid | json_array
1
2
3
4
5
6
...
26
Если создавать ячейку пустой при создании юзера, то позже, если юзер будет заполнять профиль содержательнее и его поле с массивом будет увеличиваться, это будет происходить уже в глубине таблицы. Значит, будут постоянные смещения индексов по страницам.
Есть вариант подсчитывать, сколько в среднем весит массив в таблице и набивать его изначально при создании юзера, к примеру ключами с кодами 999, за которыми будет стоять "Нет ответа". Но как-то это всё коряво и странно, должно быть что-то удобнее...
Роман, я изучил эту функцию (правда документация ссылается на её доступность только с MySQL 8.0.4 и поздних версий), и это пока видится очень даже удобным решением в некоторых сценариях.
Только вот я всё перерыл и нигде не нашёл, можно ли делать join разных таблиц, где одна из используемых в join-е как раз создаётся через json_table. В документации указано:
An inner join can be emulated by applying a suitable condition in the WHERE clause, as shown here
Вы не знаете, можно ли делать такие join-ы, то есть вместо "tbl_name" подставляя json_table функцию?
Анатолий, не имея схемы БД и данных, не могу знать можно или нет.
Стоит создать тестовый набор данных при помощи сервиса https://www.db-fiddle.com/ (или подобным ему) и задать новый вопрос.
Порой проще раскодировать JSON на стороне клиента СУБД, в самой программе, занеся в соответствующие модели. Особенно когда для пользователя требуется вернуть список вопросов и ответов.
Попробуйте сначала свой вариант
