Оптимизация запросов
В этом после хочу рассказать основы оптимизации запросов в БД. Буду говорить на примере Postgre, но в других БД процесс похож.
Шаг 0. Вспоминаем основы
При выполнении запроса участвуют два процесса:
▪️ Планировщик — составляет план выполнения запроса. Какие таблицы обойти, что проверить и в какой последовательности
▪️ Исполнитель — извлекает данные по заданному плану
Разработчик может создать дополнительные структуры данных — индексы. Индексы помогают быстрее выполнять запросы, но занимают много места. Если данные в таблице занимают 1 ГБ, то индекс с id займёт 250 МБ.
Шаг 1. Ищем, что оптимизировать
Смотрим таблицу
Ищем запросы, которые выполняются часто или долго.
Шаг 2. Работаем с конкретным запросом
Для экспериментов берём тестовую базу с большим количеством данных. Минимум миллион записей, иначе эффект оптимизаций не будет заметен.
Прогоняем запрос через EXPLAIN ANALYZE:
▪️ planning time — время планирования запроса
▪️ execution time — время выполнения запроса. Работаем с этим значением
Можно поиграть с условиями, порядком соединения таблиц и разными функциями. Обратите внимание на способ обхода таблицы:
🔸 поиск по условию (where name = …)
🔸 проверка уникальности поля
🔸 проверка внешнего ключа (foreign key)
Решение здесь простое — добавить индекс по проблемному полю. Базовый вариант выглядит так:
▫️ Запустить
▫️ Порадоваться снижению execution time
Для оптимизаций популярных и тяжёлых запросов добавление индекса оправдано. Разумеется, не нужно добавлять индексы для всех запросов и всех условий. Индексы занимают много места и замедляют запись в базу.
В оптимизации запросов огромное количество нюансов, но большинство проблем решается кэшем и добавлением индекса. Более сложные случаи лучше обсуждать с коллегами DBA😌
В этом после хочу рассказать основы оптимизации запросов в БД. Буду говорить на примере 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😌