Fernus
@Fernus
Техник - Механик :)

Оптимизация структуры БД. Какие варианты в данном случае?

Если делать БД по "общепринятым практикам" на MySQL, то будет выглядеть примерно так:
Есть "справочники", например:
spoiler

Справочник (A):
6336cbee092de964706945.png
Кол-во записей: ~ 120 (почти никогда не добавляются/изменяются)

Справочник (B):
6336cbfa601b4497980434.png
Кол-во записей: ~ 10 000 (добавляются/изменяются редко)

Справочник (C):
6336cc9b65b00995958651.png
Кол-во записей: ~ 5 000 (добавляются/изменяются редко)

Справочник (S):
id | name
Кол-во записей: ~ 100 (периодически добавляются/удаляются, но кол-во растёт)
Справочники A и B - это получается hasMany связь.

Есть данные, которые связаны с этими справочниками по такому принципу:
spoiler
Таблица с данными (D):
id | s_id | другие_поля...
6336ccb23cd3c285341891.png
Кол-во записей: ~ 3 000 000 (постоянно обновляются/добавляются/удаляются и кол-во растёт)
На каждый s_id от 1 000 до 600 000 записей (т.е. неравномерно).

Связная таблица D_B:
link_id | b_id
6336ccd3860f6289410999.png
Кол-во записей: ~ 4 000 000 (на каждый d_id от 1 до 10 b_id...чаще от 1 до 3)

Связная таблица D_C:
link_id | c_id
6336cce6d5e2d824445721.png
Кол-во записей: ~ 3 500 000 (на каждый d_id от 1 до 3 c_id...чаще 1)

Частые выборки:
spoiler
1. Получить записи из справочника "A" с учётом переданных ID'шников из справочника "S" и наличия в таблице D.
Кол-во ID'шников для фильтра может быть любым - чаще больше 40.
Тут логично с помощью JOIN соединить A, B и D.
6336d57a25b48552736959.png

2. Получить записи из справочника "B" с учётом переданных ID'шников из справочника "S" и справочника "A", и наличия в таблице D.
Кол-во ID'шников(S) для фильтра может быть любым - чаще больше 40.
Кол-во ID'шников(A) для фильтра может быть от 1 до 2 - чаще 1.
Тут, опять же, логично с помощью JOIN соединить A, B и D.
6336d58b06ae5162480521.png

3. Получить записи из справочника "C" с учётом переданных ID'шников из справочника "S" и справочника "B", и наличия в таблице D.
Кол-во ID'шников(S) для фильтра может быть любым - чаще больше 40.
Кол-во ID'шников(B) для фильтра может быть от 1 до 10 - чаще 1.
Тут, опять же, логично с помощью JOIN соединить B, C и D.

4. Получить записи из таблицы "D" с учётом переданных ID'шников из справочника "S", справочника "B" и справочника C.
Кол-во ID'шников(S) для фильтра может быть любым - чаще больше 40.
Кол-во ID'шников(B) для фильтра может быть от 1 до 10 - чаще 1.
Кол-во ID'шников(C) для фильтра может быть от 1 до 5 - чаще 1.
Тут, опять же, логично с помощью JOIN соединить B, C и D.

Суть проблемы:
Если это всё работает на MySQL, то не получается оптимизировать запросы "стандартными" методами...индексы выручают, но из-за "переменного" кол-ва ID'шников по фильтру s_id индексы не так эффективны...т.к. приходится сканировать много записей...

Перетащил это всё на MongoDB с такой структурой:

Справочники остались в MySQL.

Таблица(коллекция) D:
id | s_id | a_id | b_id | c_id | другие_поля...

s_id - int;
a_id - int array;
b_id - int array;
c_id - int array.

На "эвентах" пришлось замудрить сохранение в кэш уникальных ID справочников A, B и C по наличию в D по каждому S...тем самым более-менее решились пункты 1, 2, и 3.
Хотя это можно было и оставшись на MySQL сделать... :)

Думаю попробовать перенести структуру на PostgreSQL аналогично MongoDB и использовать для выборки вот это:
https://www.postgresql.org/docs/9.1/intarray.html
Так же аналогичная штука появилась в MySQL 8:
https://saveriomiroddi.github.io/Storage-and-index...

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

UPD:
Выложил EXPLAIN некоторых запросов...
  • Вопрос задан
  • 266 просмотров
Пригласить эксперта
Ответы на вопрос 3
@mayton2019
Bigdata Engineer

Перетащил это всё на MongoDB с такой структурой:

Справочники остались в MySQL.
.......
Какие есть идеи?

Думаю попробовать перенести структуру на PostgreSQL аналогично MongoDB и использовать

Дружище. Так жеж не делается в мире Документно-ориентированных БД! В монге ты делаешь не таблицы. А хранилища документов. Где каждый документ - самодостаточен и полностью хранит в себе всю информацию. Грубо говоря никаких СПРАВОЧНИКОВ и СВЯЗНЫХ таблиц у тебя быть не должно. И нельзя джойнить документы. И нельзя джойнить документы с таблицами MySQL.

Почитай про модель АГРЕГАТОВ в противовес реляционной модели. Это можно найти в книжках типа NoSQL и еще я находил это в доках по Cassandra.
Ответ написан
ThunderCat
@ThunderCat Куратор тега MySQL
{PHP, MySql, HTML, JS, CSS} developer
Суть проблемы:
Если это всё работает на MySQL, то не получается оптимизировать запросы "стандартными" методами...индексы выручают, но из-за "переменного" кол-ва ID'шников по фильтру s_id индексы не так эффективны...т.к. приходится сканировать много записей...
Так, а в чем собственно проблема? Где тайминги запросов, где explain по медленным запросам?
Ответ написан
iMedved2009
@iMedved2009
Не люблю людей
Вымарывать названия таблиц - это конечно ржачный идиотизм. NDA, да?

1. Ну а по существу - ну вы из таблицы D я так понимаю выгребаете большую часть записей. Как бы ясень фиг что индекс не будет использоваться - смысл если вам все равно практически всю таблицу выдергивать. И оптимизации то тут какие могут быть если вы требуете от БД вытащить все записи из таблицы?

2. У вас там в нескольких местах mysql вам сообщает что ключей возможных несколько - может вам попробовать композитные индексы?
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы