Как аналитику оптимизировать свои запросы?

Ранее я рассказывал, почему оконные функции могут тормозить, и дал 4 совета по оптимизации. Но если ты действительно хочешь ускорить свои запросы, то начинать нужно с анализа плана выполнения.

Знакомьтесь - EXPLAIN

Команда простая и очевидная, но почему-то ею мало кто пользуется. Может, потому что хочется быстрее решить задачу, а не копаться в том, как база данных обрабатывает запросы. Но когда ты пишешь боевой запрос, от которого зависит работа важной системы, или когда на кластере жесткие лимиты по ресурсам - умение читать EXPLAIN твое все!

Как SQL работает под капотом?

Прежде чем говорить о плане, важно понимать этапы, которые проходит SQL-запрос внутри базы данных (на примере PostgreSQL):

1⃣ Parser - разбирает текст SQL, проверяет синтаксис и строит абстрактное синтаксическое дерево.

2⃣ Analyzer - проверяет существуют ли таблицы, колонки, функции, есть ли права доступа. Если ты опечатался в названии колонки, то ошибка упадет именно здесь.

3⃣ Rewriter - делает логическое преобразование запроса.

4⃣ Planner / Optimizer - самый важный этап для нас. Он перебирает возможные планы выполнения и выбирает тот, у которого наименьшая стоимость.

5⃣ Executor - выполняет план и возвращает результат.

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

Как читать EXPLAIN?

План выполнения - это дерево. Самый глубоко вложенный оператор выполняется первым. Всегда читай план снизу вверх - так ты увидишь реальную последовательность действий.

Команда EXPLAIN показывает, как именно база данных собирается выполнять запрос: какие индексы использовать, как соединять таблицы, будет ли сортировка.

А EXPLAIN ANALYZE еще и выполняет запрос, добавляя фактические цифры: сколько строк прошло через каждый шаг, сколько времени заняло, сколько памяти использовано.

Основные операторы в плане:

◾ Seq Scan - последовательное чтение всей таблицы (для маленьких таблиц - это окей).
✔ Оптимизация: добавить индекс на поля, которые используются в WHERE, JOIN, ORDER BY.

◾ Index Scan - чтение по индексу.
✔ Оптимизация: следи, чтобы индекс реально использовался: без функций, кастов и с правильным порядком колонок.

◾ Sort - дорогая операция, особенно на больших объемах и без индексов.
✔ Оптимизация: если сортировка нужна, то убедись, что есть индекс на поля сортировки. Если не нужна - убери.

◾ Hash Join / Merge Join / Nested Loop - это способы соединить таблицы. Hash Join и Merge Join обычно быстрее на больших данных, Nested Loop - на маленьких.
✔ Оптимизация: главный прием - уменьшить данные до JOIN, а не после.

◾ Cost=1..123 - оценка оптимизатора, где первое число - стоимость получить первую строку, второе - все строки. Чем меньше - тем лучше.
✔ Оптимизация: это лишь оценка оптимизатора, ориентируйся на реальные метрики из EXPLAIN ANALYZE.

Я не буду переписывать сюда всю документацию. Вот ссылки на официальные руководства, где все подробно расписано:
- PostgreSQL
- MySQL
- SQL Server
- Greenplum
- ClickHouse

Твое задание на сегодня

Возьми любой запрос, который работал долго, и выполни перед ним EXPLAIN, найди самый дорогой узел по стоимости и попробуй его оптимизировать.

Интересными кейсами делись в комментариях!

#харды #sql