Какие есть способы оптимизации тяжелых запросов к базе данных

Что проверяют

Умеете ли вы найти причину медленного запроса, а не перечислять общие советы. Для Python-разработчика это почти всегда связка ORM и базы, поэтому следом просят показать, как вы заметите N+1 в Django или SQLAlchemy.

Как отвечать

Каркас простой: сначала измерить, потом чинить.

• найти запрос: pg_stat_statements в PostgreSQL, лог медленных запросов, в Django django-debug-toolbar или QuerySet.explain(), в SQLAlchemy параметр echo=True;
• прочитать план: EXPLAIN (ANALYZE, BUFFERS) покажет Seq Scan по большой таблице, расхождение оценки строк с фактом и дорогую сортировку;
• N+1: цикл по объектам с обращением к связи порождает запрос на каждую строку. В Django это лечат select_related для ForeignKey и prefetch_related для обратных связей и many-to-many, в SQLAlchemy joinedload и selectinload;
• лишние данные: only(), values_list() или выбор конкретных колонок вместо целых объектов. Создание тысяч моделей само по себе заметно стоит в Python;
• индексы: составной под фильтр и сортировку, частичный под частый WHERE, индекс по выражению, если в условии стоит функция;
• пагинация: глубокий OFFSET читает и выбрасывает все пропущенные строки, keyset-пагинация через WHERE id > last_id работает одинаково быстро на любой странице;
• пакетная работа: bulk_create, bulk_update, iterator() для выгрузок, чтобы не держать весь результат в памяти;
• кеш и реплики: Redis для горячих агрегатов, чтение отчётов с реплики. Это следующий шаг, когда сам запрос уже быстрый.

Где спотыкаются

Советуют включить query cache в MySQL. В MySQL 8.0 его удалили, кеширование результатов делают на стороне приложения.

Думают, что async ускорит тяжёлый запрос. Асинхронный драйвер освобождает воркер, пока база думает, но план и время выполнения самого запроса не меняются.

Оптимизируют вслепую: вешают индекс, не посмотрев план, и не обновляют статистику после массовой загрузки. Без ANALYZE планировщик продолжает выбирать старый путь.