Здравствуйте, уважаемые подписчики и гости канала!
Привет! Не знаю как вам, но мне посчастливилось работать в рекламной тематике - данных тут уже не совсем мало и часто по работе надо писать запросы на таблицах, включающих например 10млн. записей, при этом в том же запросе используется JOIN с двумя - четырьмя другими таблицами, а одной из которых 2-3 млн. записей, а в остальных по 30 тысяч примерно. Сетапы бывают разные - у нас БД шардированные, шард очено много и все зависит от конкретных данных в конкретной шарде. Бывают шарды значительно меньше, где трюк, о котором я расскажу дальше не сильно нужен.
Так вот, например, у нас есть такие таблицы:
- campaigns (10 тыс. записей)
- ads (1 млн. записей) ссылается на campaigns по campaign_id (индексное поле)
- keywords (10 млн. !!! записей) ссылается на ads по ad_id (индексное поле). Получается, что на каждый ad в среднем приходится 10 keywords
Задача
И нам надо быстро уметь считать для каждой из campaigns с status=enabled: сколько keywords с status=enabled есть в ads с status=enabled.
Тут можно сказать - так просто же! Сделать JOIN, отфильтровать и сгруппировать. Нормаз, если у вас всего до 1 млн. записей еще как-то будет работать. А если нет, го читать ниже и учиться.
Медленное решение "в лоб"
Итак, мы уже поняли, что что-то вроде такого просто не отработает на таких объемах под нагрузкой:
Не буду мучать вас планом запроса, но там вы увидите JOIN-ы всех таблиц меж собой с фильтрацией и потом группировку. Индекс по ad_id в keywords скорее всего использоваться не будет, так как БД решит, что ей проще обойти всю таблицу (seq scan) - все равно же потом группировать. Это не прикольно, если у вас как у нас это более менее частые операции и кешы PG постоянно такими вот делами будут наполняться. Добавьте сюда рабочие нагрузки на БД и увидите дикие чтения с диска, тормоза приложения и т.д.
"Правильное" решение
Сразу стоит отметить, что не стоит сейчас бросать все как студент второкурсник и мчаться переписывать все запросы подряд. Эта техника не для всего подходит!
Перво-наперво - "думай как база, будь базой". Это значит, что все оптимизации надо продумывать головой, ведь БД писали ваши коллеги программисты, пусть даже из другой области.
Итак, для любых (!) оптимизаций надо понять - что конкретно вы хотите получить. В нашем случае это количество активных ключевых фраз (keywords) в активных объявлениях (ads) для каждой активной кампании (campaigns). Что именно вас должно напрячь в верхнем запросе? Правильно - зачем JOIN c ads ? =) Ведь keywords много и именно JOIN задача от нас не требует. Но как добыть данные для каждого объявления не делая JOIN или подзапрос? Потом сами проверите, но простой подзапрос прямо в секции select оптимизатор обычно разворачивает в JOIN, но может вам на ваших данных повезет. Опять же в примере ниже в lateral join можно много полей выбирать.
Так вот - суть lateral join в том, что БД как бы отключает оптимизатор на границе общего запроса и того, что в блоке lateral join и делает столько запросов, сколько строк пытается сджойнится с инструкцией lateral join.
Для секции LATERAL JOIN просто ставьте "ON true", пока не поймете зачем оно там и что там еще может быть.
Вот конкретно на этом примере БД сработает так: сделает join - campaigns + ads, а потом для каждого ad_id, уже используя индекс, сделает запрос в keywords. Кажется - не круто же делать 1млн. запросов в keywords, отнюдь, по индексному полю это очень очень быстро. Повторюсь, все зависит от вашего случая. Такой трюк спасал наш проект реально много раз.
Еще одна фича LATERAL JOIN
Поскольку, мы узнали, что на каждую строку для join-а выполнится подзапос, то логично продолжить, что вы можете вызывать хранимые процедуры по сути в секции join-ов. А это значит, что вы можете сортировать, фильтровать и join-ить с этими данными что-то ниже по секции джойнов.
Кстати, некоторые оптимизации правильнее делать на этапе хранения данных, например у меня есть статейка про триггерные функции, которые могут помочь тебе в трудной ситуации.
---
А на этом всё!
Не согласен? Оставляй коммент, обсудим!
Подписывайтесь в Telegram: https://t.me/lets_goto_it
Быстрых вам запросов
#sql #postresql #java #php #python #программирование #оптимизация сайта #оптимизация запросов