senior

Как оптимизировать SQL-запросы?

Оптимизация SQL-запросов — процесс улучшения производительности запросов, основанный на измерениях (EXPLAIN ANALYZE), а не на догадках. Ниже приведён практический чеклист.

Сводный чеклист

Приём	Когда применять
Создать индекс	Медленный `WHERE`, `JOIN`, `ORDER BY`
Убрать `SELECT *`	Всегда
Устранить N+1	Циклические запросы в коде
Keyset pagination	Глубокая пагинация (`OFFSET` > 1000)
`EXISTS` вместо `IN`	Подзапрос возвращает много строк
Избегать функций в `WHERE`	Функция на индексированном столбце
Пул соединений	Всегда
`EXPLAIN ANALYZE`	Любой медленный запрос
Денормализация	Чтение >> запись
Materialized View	Тяжёлые агрегатные запросы

1. Используйте индексы с умом

Пример

-- Плохо: функция на индексированном столбце — индекс не используется
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';

-- Хорошо: функциональный индекс
CREATE INDEX idx_users_email_upper ON users(UPPER(email));

-- Ещё лучше: хранить данные в нужном формате
SELECT * FROM users WHERE email = 'user@example.com';

2. Избегайте SELECT *

Пример

-- Плохо: читаются все столбцы, даже ненужные
SELECT * FROM orders WHERE status = 'pending';

-- Хорошо: только нужные столбцы (может использовать Index Only Scan)
SELECT id, amount, created_at FROM orders WHERE status = 'pending';

3. Устраните проблему N+1

Пример

// Плохо (N+1): один запрос на список + N запросов на детали
List<User> users = query("SELECT * FROM users");
for (User u : users) {
    List<Order> orders = query("SELECT * FROM orders WHERE user_id = ?", u.id);
}

// Хорошо: один запрос с JOIN
query("SELECT u.*, o.* FROM users u LEFT JOIN orders o ON o.user_id = u.id");

4. Используйте keyset pagination вместо OFFSET

Пример

-- Медленно на глубоких страницах (СУБД читает и отбрасывает 10000 строк)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;

-- Быстро: keyset pagination (курсорная пагинация)
SELECT * FROM products WHERE id > 10020 ORDER BY id LIMIT 20;

5. Предпочитайте EXISTS вместо IN для подзапросов

Пример

-- EXISTS останавливается при первом совпадении
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

6. Не применяйте функции к индексированным столбцам

Пример

-- Плохо: индекс на created_at не используется
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2026;

-- Хорошо: диапазонный запрос, индекс используется
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

7. Используйте пул соединений

Создание TCP-соединения к PostgreSQL — дорогая операция (~100-200ms). Используйте пул: PgBouncer (внешний пулер), HikariCP (в Java-приложениях), или встроенный пул Spring Boot.

8. Материализованные представления

Пример с Materialized View

CREATE MATERIALIZED VIEW mv_daily_stats AS
SELECT
    DATE(created_at) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE(created_at);

CREATE INDEX idx_mv_daily_stats_day ON mv_daily_stats(day);

-- Использование (мгновенный ответ)
SELECT * FROM mv_daily_stats WHERE day >= '2026-01-01';

-- Обновление данных (CONCURRENTLY — без блокировки чтения)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats;

9. Денормализация как компромисс

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

Важное

Оптимизация должна быть основана на измерениях, а не на догадках
Не оптимизируйте преждевременно — сначала убедитесь, что запрос является узким местом
Регулярно запускайте ANALYZE для актуальной статистики
pg_stat_statements — расширение для отслеживания самых медленных и частых запросов
При высокой нагрузке рассмотрите read replicas для разделения чтения и записи

Частые ошибки

Создают индексы «на всякий случай» без проверки их использования
Используют OFFSET для глубокой пагинации — крайне медленно
Кешируют в приложении то, что лучше кешировать на уровне СУБД (materialized views)
Оптимизируют запрос на тестовых данных (100 строк), а на production (10 млн строк) план совершенно другой

На собеседовании: не перечисляйте все приёмы — опишите системный подход: измерить (EXPLAIN ANALYZE) -> найти узкое место -> применить конкретный приём. Частая ошибка — начать с «добавить индекс» без понимания, где именно проблема.

#sql