Как оптимизировать количество запросов и организовать хранение данных?

Question

[Лаврентий]

Если совсем вкратце. Имеется небольшая собственная CRM система (учёт товаров и т.п.) на базе этой системы был написан модуль (некое подобие для CMS), что бы контент менеджер прям из CRM управлял сайтом. Всё написано на php.

Структура БД (в mySQL) построена таким образом, что имеются страница для сайта. И у этой страниц есть группы свойств (описываю прям совсем приблизительно для примера, что бы не вдаваться в подробную структуру):

• Таблица со иерархической структурой самих страниц - pages
  
• Таблица с группами свойств - props_group (которая имеет связь с pages)
  
• Таблица со значениями свойств - props_value (которая имеет связь с props_group)
  

На выходе на сайте. Что бы обратиться к данным одной страницы - получается идет 3 запроса к БД. И система отдает все данные по одной страницы (перебирая все её группы и потом перебирая свойства этой группы). На страницы со списком товаров (к примеру выводится 24 товара), получается что каждая карточка делает свои 3 запроса к БД итого 72 запроса (3 запроса на 24 товара). И это только про страницу товаров. По другого контента на какой либо страницы, что есть на сайте (меню, баннеры, предложения, выборка какая то в футере и т.п.). Это всё условно я пишу как пример.

Что бы сократить кол-ва запросов к БД. Я решил переделать след образом. В саму таблицу pages, при её редактировании сохраняются все данные по её значениям группы и свойств (т.е. весь массив данных из таблиц props_group т props_value в виде JSON строки). Но размер данных большой и приходится использовать тип поля MEDIUMTEXT. Я в итоге получил один запрос для каждого товара, но таблица сразу увеличилась в размере и запрос выполняется соответственно дольше.

Вопрос: Как всё же правильно оптимизировать количество запросов к mySQL (или как правильно организовать хранение данных)?

Или на сколько верный вообще такой подход по хранению данных в видео json строки если данные эти статичны и меняются только при редактировании самой страницы? Есть ли смысл смотреть в сторону redis что бы хранить там этот json результат со всеми свойствами страницы?

Comments

[Дмитрий]

Это какой нужен json, чтоб в медиум текст записывать

[Лаврентий]

Дмитрий, ну вот так получается что набор свойств очень большой что в TEXT не влез.
Но мой вопрос больше в целом о подходе. На сколько он верный или нет

[Akina]

Или на сколько верный вообще такой подход по хранению данных в видео json строки если данные эти статичны и меняются только при редактированнии самой страныцы?

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

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

1) Какая точно версия MySQL?
2) Что именно Вы считаете временем выполнения запроса?
3) Как соотносятся размер данных, передаваемый всеми запросами в исходной реализации, и размер передаваемых данных в созданном одном запросе?
4) Насколько различаются накладные расходы на парсинг этого JSON на клиенте по сравнению с исходным вариантом? почему бы не получать пусть одним запросом, но отдельные JSON для каждого... ну, как было выше, из 24 товаров.
5) Почему собственно нельзя на исходной структуре собрать всё в один запрос и получить 72 записи, которые потом раскидать по карточкам?

[Лаврентий]

Akina, Отвечу на главный и основой вопрос. Потому что другой блок вопросов для меня сложен технически :).
Конечный сформированный json на стороне MYSQL (версия 5.7.26) не используется. Он только для получения из базы и дальнейшего распарсинга и формиования данных по самой страницы, т.е. по сути обработки его в php или в шаблонизаторе.

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

[d'Ivan]

MySQL 5.7 умеет работать с JSON и хранить в колонке этого типа.

[Лаврентий]

Román Mirilaczvili, хорошо это я в курсе. Если я буду хранить в типе полей JSON размер уменьшится (по идеи).
Но у меня всё же вопрос про другое. Правильно ли вообще такой подход, что бы собраться данные относящиеся к одной записи из всех связанных таблиц и хранить её в формате JSON. Что бы при обращении просто обратиться в одному полю и распарсить данные, а не делать доп запросы?

[d'Ivan]

Лаврентий, данные можно нормализовать, разделяя по таблицам, если это целесообразно. Тогда можно использовать JOIN для получения результатов в одном запросе.

[d'Ivan]

Redis - это БД, которая больше подходит для сокращения запросов в MySQL, кэшируя наиболее часто используемые данные.

[Дмитрий]

Лаврентий, этот ваш огромный json надо хранить не в мускуле, тем более выборки по нему нет, то есть достаётся весь полностью, любой кэш, хоть в файлах держите, где имя будет ид записи с базы

[Akina]

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

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

Answer 1

[Антон Антон]

Нужно разделить формирование страницы на этапы - получение списка блоков - получение данных для блоков (тут группируем по типам блоков и для каждого переделываем получение данных на массовое через where id in (...)) и возврат ассоциативного массива с ключом = id. Ну и рендеринг карточек с выводом уже полученных данных. Таким образом для формирования N карточек товара нужно будет все равно три запроса.

Answer 2

[Adamos]

1. Запрос к pages - выборка нужных данных, выделение ID результатов.
2. Запрос к props_group WHERE page_id IN (), выделение ID результатов.
3. Запрос к props_value WHERE props_group_id IN ().
4. Сбор в кучку полученной информации и передача этой кучки в отрисовку шаблоном.
0. И никакого JSON.

Comments

[Лаврентий]

Спасибо за ответ. Подитожу. Потому что только у вас вижу решения для себя.

К примеру на странице где выводится список товаров. Я не обрабатываю каждый товар по отдельности (для каждого товара свои запросы в эти таблици). А просто беру весь массив товаров (страниц) и получаю все данные по ним сразу и только потом уже на бэкенде разбираю полученный массив.

Верно я понял ход мыслей?

[Adamos]

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

[Лаврентий]

Adamos, Спасибо за "направление"

Answer 3

[rPman]

Правильно - не заводить систему entity-value где ни попадя, у вас там действительно новые типы данных появляются и меняются каждый день?
Храни значения как полагается - в таблицах и колонках

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

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