Ранее я рассказывал, почему оконные функции могут тормозить, и дал 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