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

Почему запросы становятся медленными? 🐢

Представьте, что база данных — это библиотека без каталога. Каждый раз, когда вам нужна книга, вы просматриваете все полки. Примерно так работает SQL-запрос без оптимизации!

Основные причины тормозов:

Полное сканирование таблиц (TABLE SCAN)
Отсутствие индексов там, где они критичны
Сложные JOIN с неправильным порядком таблиц
Избыточные данные в выборке

-- Антипример: запрос, который заставит вашу БД плакать
SELECT * FROM orders 
WHERE total_amount BETWEEN 100 AND 200
ORDER BY created_at DESC;

EXPLAIN — ваш детектор проблем 🔍

Перед оптимизацией нужно понять, как выполняется запрос. В этом поможет EXPLAIN:

EXPLAIN ANALYZE 
SELECT customers.name, COUNT(orders.id) 
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

Что смотрим в выводе:

Seq Scan — тревожный звоночек (полное сканирование)
Cost — оценка "стоимости" операции
Execution Time — фактическое время выполнения

Индексы — реактивные ускорители 🚀

Индексы — это специальные структуры данных, которые ускоряют поиск. Важные типы:

B-tree — стандартный индекс для сравнений и сортировок
Hash — молниеносный поиск по точному совпадению
GIN/GiST — для сложных данных (JSON, массивы, геоданные)

Когда создавать:

На часто используемых WHERE условиях
На столбцах в JOIN
На полях для сортировки (ORDER BY)

-- Создаём индекс для проблемного запроса
CREATE INDEX idx_orders_amount_date ON orders(total_amount, created_at);

-- Составные индексы работают как телефонная книга:
-- сначала сортировка по фамилии, потом по имени

Оптимизация JOIN: порядок имеет значение ⚖️

SQL — декларативный язык, но порядок таблиц в JOIN влияет на производительность!

Золотое правило: сначала таблицы с сильной фильтрацией (меньше строк).

-- Медленно: большой orders сканируется первым
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'RU';

-- Быстро: сначала фильтрация customers
SELECT * FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE customers.country = 'RU';

Реальные кейсы оптимизации 🛠️

Кейс 1: Избыточные данные

-- До: выбираем всё, хотя нужны только имена
SELECT * FROM users WHERE status = 'active';

-- После: берём только необходимые поля
SELECT username FROM users WHERE status = 'active';

Кейс 2: Проблемы с LIKE

-- Медленно: поиск по началу строки
SELECT * FROM products WHERE name LIKE '%apple%';

-- Быстрее: поиск по началу строки (использует индекс)
SELECT * FROM products WHERE name LIKE 'apple%';

-- Решение: полнотекстовый поиск для сложных случаев

Мониторинг ресурсов: не доводите до предела ⚠️

Используйте системные представления для диагностики:

-- Топ самых прожорливых запросов
SELECT query, total_exec_time 
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- Статистика использования индексов
SELECT indexrelname, idx_scan 
FROM pg_stat_user_indexes;

Важные метрики:

CPU usage — >90% = тревога
Disk I/O — много чтения = возможна нехватка RAM
Lock contention — блокировки тормозят систему

Паттерны для сложных запросов 🧩

1. Декомпозиция: Разбивайте сложные запросы на простые CTE

WITH active_users AS (
    SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT user_id, SUM(amount) FROM orders 
    WHERE user_id IN (SELECT id FROM active_users)
    GROUP BY user_id
)
SELECT * FROM user_orders;

2. Материализация: Кэшируйте промежуточные результаты

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', created_at) AS month, 
       SUM(total) AS revenue
FROM orders
GROUP BY month;

Главные правила производительности 🏆

Измеряйте перед оптимизацией — не угадывайте, используйте EXPLAIN
Индексы — панацея? Нет! Каждый индекс замедляет INSERT/UPDATE
Денормализация — иногда нарушайте нормальные формы ради скорости
Партицирование — делите большие таблицы на логические части
Кэширование — Redis или материализованные представления спасут от повторных вычислений