Что влияет на время выполнения запроса?

Оптимизация SQL запросов: что влияет на скорость

Быстрые запросы — основа хорошей аналитики. Давайте разберёмся что замедляет запросы и как их ускорять.

1. Основные факторы, влияющие на скорость

Фактор 1: Размер данных (объём строк)

-- МЕДЛЕННО: Сканирую всю таблицу 10M строк
SELECT * FROM orders;
-- Время: 5 сек

-- БЫСТРО: Фильтрую по дате (partition pruning)
SELECT * FROM orders 
WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
-- Время: 0.2 сек (25x быстрее!)

Почему разница?

• Первый запрос читает все 10M строк
• Второй запрос использует partition pruning — читает только релевантные парты
• В BigQuery/PostgreSQL с partition by date это критическая оптимизация

Фактор 2: Индексирование

-- БЕЗ индекса
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  created_at TIMESTAMP,
  amount DECIMAL
);

SELECT * FROM orders WHERE user_id = 123;
-- Время: 3 сек (full table scan — смотрю все 10M строк)

-- С индексом
CREATE INDEX idx_orders_user_id ON orders(user_id);

SELECT * FROM orders WHERE user_id = 123;
-- Время: 0.01 сек (1000x быстрее!)
-- Почему: индекс — это отсортированный список (user_id, row_pointer)
-- Могу использовать binary search вместо linear scan

Фактор 3: JOIN операции

-- ОЧЕНЬ МЕДЛЕННО: Cartesian product (без условия join)
SELECT * FROM orders o, users u;
-- Результат: 10M × 1M = 10B строк (!)
-- Время: forever

-- МЕДЛЕННО: JOIN без индекса на foreign key
SELECT o.*, u.name FROM orders o 
JOIN users u ON o.user_id = u.id;
-- Результат: 10M строк
-- Время: 10 сек (hash join, но медленный)

-- БЫСТРО: JOIN с индексом
CREATE INDEX idx_users_id ON users(id);

SELECT o.*, u.name FROM orders o 
JOIN users u ON o.user_id = u.id;
-- Время: 1 сек

-- Вариант JOINа:
-- 1. Nested Loop (медленный, используется для маленьких таблиц)
-- 2. Hash Join (быстрый для больших таблиц без индекса)
-- 3. Merge Sort Join (быстрый если обе таблицы отсортированы)

Фактор 4: Агрегации (GROUP BY)

-- МЕДЛЕННО: GROUP BY без индекса на поле
SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id;
-- Время: 4 сек (sort-based или hash-based grouping)

-- БЫСТРО: GROUP BY с индексом
CREATE INDEX idx_orders_user_id ON orders(user_id);

SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id;
-- Время: 1 сек (может использовать index scan)

Фактор 5: Сложные вычисления

-- МЕДЛЕННО: Вычисления в SELECT для каждой строки
SELECT 
  id,
  SUBSTRING(user_data, 1, 100),
  CAST(amount * 1.15 AS DECIMAL),
  CASE WHEN status = 'A' THEN 'Active' ELSE 'Inactive' END,
  user_id
FROM orders;
-- Время: 2 сек (вычисляю функции 10M раз)

-- БЫСТРО: Минимальные вычисления
SELECT id, user_id, amount, status FROM orders;
-- Время: 0.3 сек
-- Вычисления сделаю в Python после

2. EXPLAIN ANALYZE: как понять что медленно

-- Запрос
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.country = 'USA'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;

-- Анализ плана выполнения
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.country = 'USA'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;

-- Результат:
-- Limit (cost=12345..15678 rows=100)
-- -> Sort (cost=12345..15678 rows=10000)
--   -> GroupAggregate (cost=9876..12345 rows=10000) ← узкое место!
--     -> Hash Join (cost=5000..9876)
--       -> Filter (cost=2000..5000)
--         -> Seq Scan on users u (cost=2000..2500)
--       -> Hash (cost=3000..3500)
--         -> Seq Scan on orders o (cost=3000..3500)

-- Что видно:
-- 1. Seq Scan on users — полное сканирование (медленно)
-- 2. Нет индекса на country
-- 3. Hash Join работает нормально

Как читать результаты EXPLAIN:

Cost = estimated_startup_cost .. estimated_total_cost
Rows = estimated количество результирующих строк
Actual time = реальное время в миллисекундах

Если rows (estimated) >> rows (actual) → плохая оценка плана
Если rows (estimated) << rows (actual) → очень плохая оценка

3. Практические техники оптимизации

Техника 1: Создание правильных индексов

-- Анализирую slow queries логи (SHOW SLOW QUERIES)
-- Вижу что медленные запросы фильтруют по этим полям:
-- WHERE user_id = ?
-- WHERE created_at >= ?
-- ORDER BY created_at DESC

-- Создаю composite index (для всех условий сразу)
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

-- Postgres может использовать этот индекс для:
SELECT * FROM orders 
WHERE user_id = 123 
AND created_at >= '2024-01-01'
ORDER BY created_at DESC;

-- Сложность улучшена с O(n log n) до O(log n)

Техника 2: Partition и Cluster (для BigQuery)

-- ДО: огромная таблица 100GB
CREATE TABLE events_old (
  user_id INT64,
  event_date DATE,
  event_type STRING,
  amount DECIMAL64
);

-- Запрос может сканировать всю таблицу
SELECT * FROM events_old 
WHERE event_date = '2024-03-15';
-- Время: 30 сек, цена: $0.50

-- ПОСЛЕ: партиционирую по дате и кластеризую по user_id
CREATE OR REPLACE TABLE events
PARTITION BY event_date
CLUSTER BY user_id
AS SELECT * FROM events_old;

-- Тот же запрос
SELECT * FROM events 
WHERE event_date = '2024-03-15';
-- Время: 2 сек, цена: $0.05 (10x дешевле!)
-- Partition pruning читает только нужный день

Техника 3: Denormalization (денормализация)

-- НОРМАЛИЗОВАНО (медленно для аналитики)
SELECT 
  o.order_id,
  u.name,
  u.country,
  u.tier,  -- В отдельной таблице users
  SUM(oi.amount)
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN users u ON o.user_id = u.id
GROUP BY o.order_id, u.name, u.country, u.tier;
-- Время: 5 сек (3 таблицы, 2 JOIN'а)

-- ДЕНОРМАЛИЗОВАНО (быстро для аналитики)
CREATE TABLE orders_denormalized AS
SELECT 
  o.order_id,
  o.user_id,
  u.name,
  u.country,
  u.tier,
  o.amount,
  o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id;

SELECT 
  order_id,
  name,
  country,
  tier,
  SUM(amount)
FROM orders_denormalized
GROUP BY order_id, name, country, tier;
-- Время: 0.5 сек (1 таблица, 0 JOIN'ов)

-- Компромисс: немного redundancy данных, но 10x быстрее

Техника 4: Query Optimization (переписывание запроса)

-- МЕДЛЕННО: Subquery в WHERE
SELECT * FROM orders o
WHERE user_id IN (
  SELECT id FROM users WHERE country = 'USA'
);
-- Время: 3 сек (subquery выполняется для каждой строки)

-- БЫСТРО: JOIN вместо IN
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'USA';
-- Время: 0.5 сек

-- МЕДЛЕННО: NOT IN (может быть O(n^2))
SELECT * FROM orders
WHERE user_id NOT IN (SELECT id FROM blacklist);

-- БЫСТРО: LEFT JOIN с IS NULL
SELECT o.* FROM orders o
LEFT JOIN blacklist b ON o.user_id = b.id
WHERE b.id IS NULL;

Техника 5: Materialized Views (предвычисленные данные)

-- Если часто считаю одно и то же:
SELECT 
  DATE_TRUNC('day', created_at) as day,
  user_country,
  COUNT(*) as orders,
  SUM(amount) as revenue,
  AVG(amount) as avg_order_value
FROM orders
GROUP BY day, user_country;
-- Время: 10 сек (каждый раз пересчитываю)

-- Создаю materialized view
CREATE MATERIALIZED VIEW daily_country_revenue AS
SELECT 
  DATE_TRUNC('day', created_at)::date as day,
  user_country,
  COUNT(*) as orders,
  SUM(amount) as revenue,
  AVG(amount) as avg_order_value
FROM orders
GROUP BY day, user_country;

-- Обновляю каждый день в 2 AM
REFRESH MATERIALIZED VIEW daily_country_revenue;

-- Теперь запрос мгновенный
SELECT * FROM daily_country_revenue
WHERE day >= '2024-03-01';
-- Время: 0.1 сек (pre-computed)

4. Практический чеклист оптимизации

Когда запрос медленный:

1. EXPLAIN ANALYZE
   ↓
2. Ищу Seq Scan (full table scan) ← обычно это проблема
   ↓
3. Проверяю есть ли индекс на поле в WHERE
   ├─ Нет → CREATE INDEX
   └─ Есть → ANALYZE TABLE (update statistics)
   ↓
4. Проверяю JOIN'ы
   ├─ Много JOIN'ов? → Denormalization
   └─ JOIN без индекса? → CREATE INDEX on foreign key
   ↓
5. Проверяю GROUP BY
   ├─ Много групп? → Может быть медленно
   └─ Индекс есть? → Хорошо
   ↓
6. Проверяю ORDER BY
   ├─ Без индекса? → CREATE INDEX
   └─ На большой набор? → Может быть медленно
   ↓
7. Если всё равно медленно
   ├─ PARTITION (если BigQuery)
   ├─ CLUSTER (если BigQuery)
   ├─ Materialized view
   └─ Кэширование результата (Redis)

5. Инструменты профилирования

PostgreSQL:

-- Включу логирование медленных запросов
SET log_min_duration_statement = 1000;  -- логировать > 1 сек

-- Смотрю статистику
SELECT 
  query,
  calls,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

BigQuery:

from google.cloud import bigquery

client = bigquery.Client()
job_config = bigquery.QueryJobConfig(
    use_query_cache=False  # Отключаю кэш для честного измерения
)

query_job = client.query(sql_query, job_config=job_config)
query_job.result()  # Ждусь выполнения

print(f"Bytes processed: {query_job.total_bytes_processed / 1e9:.2f} GB")
print(f"Time: {query_job.total_slot_ms / 1000:.2f} sec")

6. Реальный пример оптимизации

Было:

SELECT u.user_id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY order_count DESC;

-- EXPLAIN показал:
-- Sort → GroupAggregate → Hash Join → Seq Scan
-- Время: 8 сек

Анализ:

• Seq Scan на users (10M rows) — медленно
• Нет индекса на orders.user_id
• GROUP BY + ORDER BY требуют сортировки

Решение:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id);

REANALYZE;  -- Update statistics

SELECT u.user_id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY order_count DESC;

-- Время: 0.5 сек (16x быстрее!)

Вывод

Что влияет на скорость запроса:

1. Data volume — фильтруй по дате/partition
2. Индексы — создавай для WHERE, JOIN, ORDER BY
3. JOIN'ы — денормализуй если нужно
4. Агрегации — используй индексы
5. Сложность запроса — переписывай более эффективно

Золотое правило: Сначала оптимизирую количество данных (WHERE с filter), потом структуру запроса (индексы, JOIN'ы). Это даёт 80% улучшения.