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

Question

[N]

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

spoiler

Справочник (A):

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

Справочник (B):

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

Справочник (C):

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

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

Есть данные, которые связаны с этими справочниками по такому принципу:

spoiler

Таблица с данными (D):
id | s_id | другие_поля...

Кол-во записей: ~ 3 000 000 (постоянно обновляются/добавляются/удаляются и кол-во растёт)
На каждый s_id от 1 000 до 600 000 записей (т.е. неравномерно).

Связная таблица D_B:
link_id | b_id

Кол-во записей: ~ 4 000 000 (на каждый d_id от 1 до 10 b_id...чаще от 1 до 3)

Связная таблица D_C:
link_id | c_id

Кол-во записей: ~ 3 500 000 (на каждый d_id от 1 до 3 c_id...чаще 1)

Частые выборки:

spoiler

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


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


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 некоторых запросов...

Comments

[Дмитрий]

по общепринятым практиками таблицам и полям стоит давать понятные названия. ибо погружать в мозг справочник A, d_id, b_id как то даже лениво. Количество записей у вас такое что не должно быть проблем с общепринятым подходом.

[N]

Дмитрий,
Наоборот думал легче воспринять такие обозначения)
По сути 4 справочника A, B, C и S.
Одна таблица с данными D.

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

Да, я работал с данными и более 160 000 000 записей в MySQL...но там не было таких "неравномерно размазанных" данных по связям со справочниками...Особенно по s_id, т.к. тут разбег большой по кол-ву записей в D...

[Дмитрий]

N, нет. Лучше описать задачу как есть. Сразу возникает шанс что кто то ее решал. А вот найти человека который смотрел на распределение в таблице D по справочнику A, b, c, s боюсь нулевой.
Не говоря о том что лучше бы видеть explain запросов которые у вас не оптимизируются

[N]

Если из условия задачи убрать фильтрацию по s_id (или сократить кол-во ID для фильтра в этом параметре до одного или нескольких значений), то время запроса становится терпимым...потому что на всю таблицу D примерно 100 уникальных s_id, но чаще выбираются записи по фильтру где более 40 ID...и выходит, что индекс по этому полю становится малоэффективным...т.к. большая часть таблицы сканируется...

[N]

Дмитрий, Завтра сделаю EXPLAIN и создам из реальных данных под описанные в задачи таблицы...чтобы было проще понять тогда)

[N]

Дмитрий, Изменил вопрос...добавил EXPLAIN...

[mayton2019]

N, ну что? Оптимизировал?

[N]

mayton2019, Есть предложения?

[mayton2019]

N, я свои уже сказал. Мне интересно какие ты принял решения. Буде монга или нет. Будет ли гибрид.

[N]

mayton2019, Если интересно...то был сначала MySQL...щас MySQL + MongoDB...+ решения, которые описал в комментарии к другому ответу...

[N]

mayton2019, Если ещё до сих пор интересно...монгу тоже в топку в итоге :)

Answer 1

[mayton2019]

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

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

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

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

Почитай про модель АГРЕГАТОВ в противовес реляционной модели. Это можно найти в книжках типа NoSQL и еще я находил это в доках по Cassandra.

Comments

[N]

Дружище, Так у меня каждый документ самодостаточен))
Ток эти документы надо фильтровать по полям (s_id, a_id, b_id, c_id) в документе (отбрось справочники - они в этой схеме не при чём...)

Answer 2

[ThunderCat]

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

Так, а в чем собственно проблема? Где тайминги запросов, где explain по медленным запросам?

Comments

[N]

Завтра выложу...может сегодня щас сделать успею...

Скажу сразу, я давно баловался с индексами и EXPLAIN запросами и пришёл к тому, что при фильтре по s_id, когда передаётся более 50-60% существующих ID, то начинается "затуп"...

[N]

Изменил вопрос...добавил EXPLAIN...

[ThunderCat]

N, А можете запустить запрос без группировки для сравнения?

[N]

ThunderCat, Запускал...ускорения почти нет никакого...

Answer 3

[Дмитрий]

Вымарывать названия таблиц - это конечно ржачный идиотизм. NDA, да?

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

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

Comments

[N]

Вымарывать названия таблиц - это конечно ржачный идиотизм. NDA, да?

:)

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

Чтобы совсем легче представить, вот упрощённо:

D - это товары;
A и B - категории, которые связанные между собой;
C - некая сущность товара (например "тип");
S - поставщик.

Каждый товар D может быть привязан к нескольким категориям B, а так же к нескольким "типам" C, но всегда принадлежит только одному поставщику S.
Из-за того, что у каждого поставщика "рандомное" кол-во товаров и нужно например доставать все уникальные категории A по 70 поставщикам отсюда и "скан" всей таблицы...

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

Имеете ввиду составные индексы? Пробовал...разницы не заметил...

[N]

P.S.: Ещё нужно учитывать, что обновления в таблицу D почти круглосуточные большими пачками...
Это была ещё одна причина уйти от MySQL...ибо куча связных таблиц и куча UPDATE/INSERT...

Тут задача не именно MySQL ускорить...а найти подходящее решение в виде любой БД...

[Дмитрий]

N,

Имеете ввиду составные индексы? Пробовал...разницы не заметил...

Стоит привести explain. Равно и какой порядок полей в составном индексе вы делали?

A и B - категории, которые связанные между собой;

А почему одна сущность "категории" разбита по двум таблицам?

Из-за того, что у каждого поставщика "рандомное" кол-во товаров и нужно например доставать все уникальные категории A по 70 поставщикам отсюда и "скан" всей таблицы...

Ну и если мы заранее понимаем что так или иначе нам делать scan всей таблицы - то как бы какие тут оптимизации? У вас что MySQL будет это делать, что PostgreSQL, что Mongo.

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

[Дмитрий]

N,

P.S.: Ещё нужно учитывать, что обновления в таблицу D почти круглосуточные большими пачками...
Это была ещё одна причина уйти от MySQL...ибо куча связных таблиц и куча UPDATE/INSERT...

И вы собираетесь постгрес? Ну прочитайте про то что все индексы в постгре вторичны, про оптимизации hot, warm или как они - для того что бы постгрес индексы лишний раз не мурыжил.

Тут задача не именно MySQL ускорить...а найти подходящее решение в виде любой БД...

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

[N]

Дмитрий,

Стоит привести explain. Равно и какой порядок полей в составном индексе вы делали?

Да не вижу смысла...я пробовал...про порядок индексов тоже в курсе...

А почему одна сущность "категории" разбита по двум таблицам?

Ну по сути это две разные сущности(категории для примера), которые между собой связаны как один(A) ко многим (B).

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

На таких "костылях" и едет щас :)
А данные меняются очень часто...поэтому эти все эти "хвосты" в виде "пересчётов" хотелось бы как-то сделать меньше...

[Дмитрий]

N,

Да не вижу смысла...я пробовал...про порядок индексов тоже в курсе...

Ну значит проблема решена. И это хорошо

[N]

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

Ну я это понимаю...просто миллионы записей будут расти...хотелось сразу что-то получше придумать...