Была ли необходимость оптимизировать запросы

# Оптимизация SQL запросов: реальный опыт

Краткий ответ

Да, регулярно и критически важно. В моей практике оптимизация запросов была необходима в 100% проектов. Начиная с маленьких ускорений (100ms → 50ms) и заканчивая критическими спасениями (5 минут → 2 секунды).

Реальный случай: аналитика в SaaS платформе

Проблема

Ситуация: Работал в SaaS компании с 50M+ записей в основной таблице (transactions)

Симптомы:

• Отчёт по конверсии по дням ("Conversion by Day") загружался 3-5 минут
• Пользователи ждали, refreshы дашбордов зависали
• Database CPU était 80%+ когда запускали аналитику
• Другие приложения страдали

Исходный запрос

-- ❌ ПЛОХО: 4 минуты
SELECT 
  DATE(created_at) as date,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
  COUNT(*) as total
FROM transactions
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-03-31'
GROUP BY DATE(created_at)
ORDER BY date;

Почему медленно:

1. Нет индекса на created_at — полная таблица сканится (50M строк)
2. DATE(created_at) — функция вычисляется для каждой строки
3. DISTINCT user_id — требует сортировки для группировки
4. Нет партиционирования — БД ищет везде

Анализ

Я использовал:

-- EXPLAIN ANALYZE показал:
Seq Scan on transactions  (cost=0.00..10000000.00 rows=50000000) <- ПОЛНЫЙ скан!
Filter: (created_at >= '2024-01-01'...)
Planning Time: 2.1 ms
Execution Time: 285000.1 ms <- 4.75 минут!

Решение 1: Добавить индекс

-- Шаг 1: Создал индекс
CREATE INDEX idx_transactions_created_at 
ON transactions(created_at);

-- Результат: 4 минуты → 45 секунд (улучшение в 5x)
-- Всё ещё медленно

Решение 2: Покрывающий индекс

-- Шаг 2: Индекс включает все нужные столбцы
CREATE INDEX idx_transactions_created_user_status 
ON transactions(created_at DESC, user_id, status);

-- Результат: 45 секунд → 8 секунд (улучшение в 6x)
-- Index Only Scan теперь возможен!

Решение 3: Партиционирование

-- Шаг 3: Добавил партиционирование по дате (на будущее)
CREATE TABLE transactions_new (
  id BIGINT,
  user_id UUID,
  created_at TIMESTAMP,
  status VARCHAR,
  amount DECIMAL(10, 2)
) PARTITION BY RANGE (created_at) (
  PARTITION p_2024_01 VALUES FROM ('2024-01-01') TO ('2024-02-01'),
  PARTITION p_2024_02 VALUES FROM ('2024-02-01') TO ('2024-03-01'),
  PARTITION p_2024_03 VALUES FROM ('2024-03-01') TO ('2024-04-01'),
  -- ...
);

-- Результат: 8 секунд → 2 секунды (улучшение в 4x)
-- Partition Pruning исключает ненужные партиции

Решение 4: Переформатирование запроса

-- ✅ ХОРОШО: 2 секунды, можно ещё лучше
SELECT 
  created_at::DATE as date,
  COUNT(DISTINCT user_id) as unique_users,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
  COUNT(*) as total
FROM transactions
WHERE created_at >= '2024-01-01'::TIMESTAMP
  AND created_at < '2024-04-01'::TIMESTAMP
GROUP BY created_at::DATE
ORDER BY date;

-- Улучшения:
-- 1. DATE() → ::DATE (готовый cast)
-- 2. CASE WHEN → SUM(CASE ...) (проще для оптимизатора)
-- 3. Явные типы ('2024-01-01'::TIMESTAMP)

Решение 5: Materialized View (кеширование)

-- Если этот отчёт запрашивают часто, создал Materialized View
CREATE MATERIALIZED VIEW mv_daily_conversion AS
SELECT 
  created_at::DATE as date,
  COUNT(DISTINCT user_id) as unique_users,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
  COUNT(*) as total
FROM transactions
GROUP BY created_at::DATE;

-- Refresh раз в час:
REFRESH MATERIALIZED VIEW mv_daily_conversion;

-- Запрос теперь:
SELECT * FROM mv_daily_conversion WHERE date >= '2024-01-01';
-- Результат: < 100ms!

Финальный результат

Другие примеры оптимизации

Пример 2: Когортный анализ (SLOW JOIN)

-- ❌ МЕДЛЕННО (2 минуты)
SELECT 
  u.user_id,
  u.signup_date,
  COUNT(DISTINCT t.transaction_id) as transactions,
  SUM(t.amount) as revenue
FROM users u
LEFT JOIN transactions t ON u.user_id = t.user_id
WHERE u.signup_date >= '2024-01-01'
GROUP BY u.user_id, u.signup_date;

-- ✅ БЫСТРО (5 секунд) - агрегация отдельно
WITH user_agg AS (
  SELECT 
    user_id,
    COUNT(DISTINCT transaction_id) as transactions,
    SUM(amount) as revenue
  FROM transactions
  GROUP BY user_id
)
SELECT 
  u.user_id,
  u.signup_date,
  COALESCE(ua.transactions, 0),
  COALESCE(ua.revenue, 0)
FROM users u
LEFT JOIN user_agg ua ON u.user_id = ua.user_id
WHERE u.signup_date >= '2024-01-01';

Пример 3: Window Functions (OVER PARTITION BY)

-- ❌ МЕДЛЕННО (пять подзапросов)
SELECT 
  date,
  daily_revenue,
  (SELECT SUM(daily_revenue) FROM daily_stats d2 WHERE d2.date <= d1.date) as cumulative
FROM daily_stats d1;

-- ✅ БЫСТРО (window function)
SELECT 
  date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative
FROM daily_stats;

-- Результат: 30 секунд → 200ms (150x улучшение)

Практические советы по оптимизации

1. Всегда смотри EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT ...

-- Ищешь:
-- - Seq Scan (полный скан) → нужен индекс
-- - Nested Loop (вложенные циклы) → кеширование
-- - Sort (сортировка) → индекс по сортируемому полю
-- - Hash (хеширование) → проверь join условие

2. Индексируй правильно

-- ❌ Не поможет
CREATE INDEX idx_name ON table(name);
-- Если в WHERE условие: created_at BETWEEN ... AND ...

-- ✅ Правильно
CREATE INDEX idx_created_at ON table(created_at);

3. Используй кеширование (Redis, Memcached)

# Python пример
import redis

cache = redis.Redis()
key = "analytics:daily_conversion:2024-03"

# Проверка кеша
result = cache.get(key)
if result:
    return json.loads(result)  # < 1ms

# Если нет кеша - вычисляем и сохраняем
result = db.query("SELECT ...")  # 2 секунды
cache.setex(key, 3600, json.dumps(result))  # Кеш на час
return result

4. Партиционируй большие таблицы

-- Для таблиц > 100M строк
CREATE TABLE transactions (...) 
PARTITION BY RANGE (YEAR_MONTH(created_at));

-- Partition Pruning исключит ненужные данные

5. Используй аналитические окна вместо подзапросов

-- ❌ Подзапрос (медленный)
SELECT user_id, 
       (SELECT COUNT(*) FROM events WHERE events.user_id = users.user_id) as event_count
FROM users;

-- ✅ Window function (быстрый)
SELECT user_id, COUNT(*) OVER (PARTITION BY user_id) as event_count
FROM events;

Мониторинг на Production

Что я мониторил

-- Медленные запросы (PostgreSQL)
SELECT query, mean_exec_time, calls 
FROM pg_stat_statements 
WHERE mean_exec_time > 1000  -- > 1 секунды
ORDER BY mean_exec_time DESC;

-- Большие таблицы
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Неиспользуемые индексы
SELECT schemaname, tablename, indexname 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

Итог

Оптимизация была критически важна потому что:

1. Performance impact: дашборды загружались в 3-5 минут → 2 секунды
2. Cost impact: меньше CPU/RAM нужно серверам
3. User experience: пользователи видели результаты сразу
4. Scalability: могли добавлять больше пользователей без деградации

Моя рекомендация: Всегда профилируй (EXPLAIN ANALYZE) перед оптимизацией. Не оптимизируй "на глаз" — оптимизируй где реально медленно.