Оптимизация запросов

В этом после хочу рассказать основы оптимизации запросов в БД. Буду говорить на примере Postgre, но в других БД процесс похож.

Шаг 0. Вспоминаем основы

При выполнении запроса участвуют два процесса:

▪️ Планировщик — составляет план выполнения запроса. Какие таблицы обойти, что проверить и в какой последовательности
▪️ Исполнитель — извлекает данные по заданному плану

Разработчик может создать дополнительные структуры данных — индексы. Индексы помогают быстрее выполнять запросы, но занимают много места. Если данные в таблице занимают 1 ГБ, то индекс с id займёт 250 МБ.

Шаг 1. Ищем, что оптимизировать

Смотрим таблицу pg_stat_statements — там собирается статистика по запросам. Чтобы получить достоверные данные, берём статистику с продакшн базы.

Ищем запросы, которые выполняются часто или долго.

Шаг 2. Работаем с конкретным запросом

Для экспериментов берём тестовую базу с большим количеством данных. Минимум миллион записей, иначе эффект оптимизаций не будет заметен.

Прогоняем запрос через EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM users where name = ’K’;

EXPLAIN пишет только план выполнения запроса. EXPLAIN ANALYZE выполняет запрос и показывает

▪️ planning time — время планирования запроса
▪️ execution time — время выполнения запроса. Работаем с этим значением

Можно поиграть с условиями, порядком соединения таблиц и разными функциями. Обратите внимание на способ обхода таблицы:

Index Scan using name_index on — при выполнении запроса используется индекс, и это отлично

Seq Scan on означает, что происходит долгий последовательный обход таблицы. Причиной может быть
🔸 поиск по условию (where name = …)
🔸 проверка уникальности поля
🔸 проверка внешнего ключа (foreign key)

Решение здесь простое — добавить индекс по проблемному полю. Базовый вариант выглядит так:

CREATE INDEX index_name ON users(name);

Дальше всё просто:

▫️ Запустить EXPLAIN ANALYZE
▫️ Увидеть в плане выполнения новый индекс
▫️ Порадоваться снижению execution time

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

В оптимизации запросов огромное количество нюансов, но большинство проблем решается кэшем и добавлением индекса. Более сложные случаи лучше обсуждать с коллегами DBA😌