Как грамотно построить sql-запросы в высоко-нагруженных базах?

Question

[sl0]

1. Есть таблица на несколько миллионов пользователей, а также есть таблица с названиями фильмов. Нужно выбрать пары пользователей, у которых есть хотя бы 7 общих фильмов. Думаю сделать отдельные таблицы для пользователей и для фильмов и связать их many-to-many. Это оптимальная архитектура или можно придумать что-нибудь лучше? И как в таком случае должен будет выглядеть оптимальный sql-запрос к базе?

2. Нужно выбрать пользователей из определенного города, со статусом active, и у которых минимум 4 фильма. Опять же, какой запрос будет оптимален?

3. Есть таблица логов, где хранятся данные о том когда пользователь был active (два timestamp - 1. стал active 2. перестал быть active). Надо подсчитать сколько было активных пользователей за определенный период времени.

Все осложняется тем, что данных много и запросов тоже может быть много, нужно с этим справиться.

Answer 1

[Александр Филиппенко]

1. Правильно думаете, только если Вы ищите такие пары среди ВСЕХ пользователей и ВСЕХ фильмов, то тут не обойдётся без полного сканирования всех 3 таблиц. Поможет кеширование, либо функциональные индексы, либо какие-то сводные таблицы управляемые триггерами или materialized view.
2. Active это bool или статус из списка? В любом случае тут нужен индекс по city_id и либо по полю active, либо частичный индекс where active = true. (надеюсь у Вас PostgreSQL)
3. Просто where с 2 условиями? Или есть подвох?

Запросы сложными тут быть не должны, но нужно предусмотреть правильные индексы чтобы это работало быстро.
Если нужна помощь именно с запросами, создайте https://www.db-fiddle.com/ с примерами данных, хотя бы строк по 10, чтобы ясна было структура

Comments

[sl0]

2. Active - bool, да.
3. Думаю что есть, но пока сам его не вижу.

[Александр Филиппенко]

1. Сейчас подумал что это идеальная задача для СУБД на графах. Правда что будет.с временем ответа сложно сказать.
2. В общем нужен триггер на таблицу связей, который будет записывать актуальное количество фильмов для каждого юзера в специальное поле в таблице users,
и индекс CREATE INDEX idx_boost_second on users (city_id, cnt_films) where active = true;

[Александр Филиппенко]

Так же можно создать поле - массив фильмов, и с помощью этого расширения https://www.postgresql.org/docs/current/intarray.html вычислять пересечения массивов у тех, у кого в принципе больше 7 фильмов, и смотреть на длину этих пересечений

Answer 2

[AlexBergal]

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

Comments

[Александр Филиппенко]

для этого и придумали частичные индексы

[AlexBergal]

Александр Филиппенко, а каким боком тут будет прикручен частичный индекс для boolean поля ?
и что остановит бд от пересчета индекса при апдейте 1й записи?

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

[Александр Филиппенко]

AlexBergal, https://postgrespro.ru/docs/postgrespro/9.6/indexe...
я про это.
Делаем индекс с условием where active = true и он во-первых меньше, во-вторых точно применится, если такое же условие будет в запросе, в-третьих он будет пересчитываться только при изменениях в строках попадающих под это условие.

[AlexBergal]

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

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

логичней сделать workaround, еще одну таблицу, где будет только 1 колонка - ид активного пользователя

Answer 3

[d'Ivan]

1-2 Попробуйте графовые СУБД. Они лучше походят для взаимосвязанных сущностей.
guides.neo4j.com/sandbox/recommendations
3. Наверное, аналитические СУБД.