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

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

В data и ML тяжёлый запрос обычно аналитический: сбор датасета или признаков по миллионам строк в хранилище. Проверяют, понимаете ли вы, как работает колоночное хранилище и почему JOIN может размножить строки. Следом часто просят разобрать конкретный медленный запрос.

Как отвечать

Сначала определите, где выполняется запрос: OLTP-база, колоночное хранилище вроде ClickHouse или облачного DWH, Spark. Оптимизации у них разные.

• меньше читать: в колоночных системах стоимость пропорциональна прочитанным колонкам, поэтому SELECT * по широкой таблице особенно дорог;
• отсечение партиций: фильтр по ключу партиционирования, обычно дате, позволяет не трогать лишние данные. Функция над этой колонкой в WHERE может отсечение сломать;
• фильтровать до соединения: сужайте обе стороны в подзапросе или CTE, прежде чем соединять;
• контроль кардинальности: соединение «многие ко многим» раздувает таблицу, и DISTINCT сверху только маскирует проблему;
• оконные функции вместо самосоединений: LAG и ROW_NUMBER для «последнего события пользователя» дешевле, чем соединение таблицы с самой собой;
• приближённые агрегаты: uniq в ClickHouse или APPROX_COUNT_DISTINCT вместо точного COUNT(DISTINCT), когда погрешность в доли процента не важна;
• предагрегация: материализованные представления и витрины признаков, которые считаются по расписанию, а не в каждом эксперименте;
• перекос данных в Spark: один горячий ключ грузит одну задачу. Помогают salting и broadcast join для маленькой таблицы;
• план: EXPLAIN в SQL, Spark UI для стадий и shuffle.

Со стороны Python: агрегируйте в базе, а не в pandas, а большие выгрузки читайте частями через chunksize в read_sql или сохраняйте в Parquet.

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

Тянут сырые строки в ноутбук и считают агрегаты в pandas. Упираются в память и сеть, хотя база посчитала бы это за секунды.

Переносят приёмы OLTP на колоночное хранилище и ищут, какой бы B-tree индекс добавить. В ClickHouse скорость в первую очередь определяет ключ сортировки ORDER BY таблицы.

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