Как оптимизировать медленный SQL-запрос? Какие инструменты и методы вы используете?

Оптимизация медленных SQL-запросов: Инструменты и методы

Медленные SQL-запросы - главная причина низкой производительности систем обработки данных. Оптимизация требует системного подхода: анализ плана выполнения, индексирование, рефакторинг и профилирование.

Шаг 1: Диагностика проблемы

EXPLAIN / EXPLAIN ANALYZE

EXPLAIN показывает план выполнения запроса (PostgreSQL, MySQL):

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_at > '2024-01-01'
GROUP BY c.id, c.name;

Вывод:

HashAggregate (cost=15.50..15.75 rows=20 width=36)
  -> Hash Left Join (cost=10.00..14.50 rows=100 width=36)
        Hash Cond: (o.customer_id = c.id)
        -> Seq Scan on orders o (cost=0.00..5.00 rows=1000 width=8)
        -> Hash (cost=8.00..8.00 rows=200 width=40)
              -> Seq Scan on customers c (cost=0.00..8.00 rows=200 width=40)
                    Filter: (created_at > '2024-01-01')

Что смотреть:

• Seq Scan (последовательное сканирование) вместо Index Scan - добавить индекс
• cost увеличивается быстро - может быть неправильное соединение
• rows (ожидаемые строки) отличается от фактических - статистика старая

Query Profiling

SET log_min_duration_statement = 1000; -- Логировать запросы > 1 сек

-- PostgreSQL: включить профилирование
SET log_statement = 'all';

-- MySQL: SLOW QUERY LOG
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Шаг 2: Распространённые проблемы и решения

1. Отсутствие индексов

Проблема:

SELECT * FROM orders WHERE customer_id = 123; -- Seq Scan

Решение:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Теперь будет Index Scan (быстрее в 100+ раз)

Индексы на:

• Foreign keys (JOIN условия)
• WHERE условия
• ORDER BY столбцы

2. Неправильные JOIN'ы

Проблема: Картезианское произведение

SELECT *
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN customers c ON o.customer_id = c.id
JOIN categories cat ON p.category_id = cat.id
WHERE o.created_at > '2024-01-01'; -- БЕЗ индексов

Решение:

-- Добавить индексы на все FK
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

3. Избыточные данные в SELECT

Проблема:

SELECT * FROM huge_table; -- Загружаем все 100 столбцов

Решение:

SELECT id, name, email FROM customers; -- Только нужные

4. Вложенные подзапросы в WHERE

Проблема:

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'Premium'
); -- Подзапрос выполняется для каждой строки

Решение: JOIN вместо IN

SELECT DISTINCT o.*
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'Premium'; -- Выполняется один раз

5. Использование функций в WHERE

Проблема:

SELECT *
FROM orders
WHERE YEAR(created_at) = 2024; -- Функция на каждой строке

Решение:

SELECT *
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01'; -- Range, использует индекс

6. Отсутствие LIMIT

Проблема:

SELECT * FROM logs; -- 1 млн строк, даже если нужны только 10

Решение:

SELECT * FROM logs LIMIT 10; -- Получить только нужное

Шаг 3: Методы оптимизации

Статистика БД

-- PostgreSQL
ANALYZE table_name;
ANALYZE; -- Вся БД

-- MySQL
ANALYZE TABLE table_name;

Паралелизм запроса

-- PostgreSQL: увеличить параллелизм
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;

SELECT COUNT(*) FROM huge_table; -- Выполнится параллельно

Партиционирование

-- PostgreSQL: партиционировать по дате
CREATE TABLE orders_partitioned (
  id BIGINT,
  customer_id INT,
  created_at TIMESTAMP
) PARTITION BY RANGE (DATE_TRUNC('month', created_at));

CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Теперь запросы на конкретный месяц ищут только в одной партиции
SELECT * FROM orders_partitioned
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Денормализация (для OLAP)

-- Вместо многих JOIN'ов, создать денормализованную таблицу
CREATE TABLE sales_fact AS
SELECT
  o.id,
  o.created_at,
  c.name as customer_name,
  c.segment,
  p.name as product_name,
  p.category,
  oi.quantity,
  oi.unit_price * oi.quantity as total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- Теперь запрос без JOIN'ов (быстрый)
SELECT category, SUM(total) FROM sales_fact GROUP BY category;

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

-- PostgreSQL
CREATE MATERIALIZED VIEW customer_stats AS
SELECT
  customer_id,
  COUNT(*) as order_count,
  SUM(total) as lifetime_value,
  MAX(created_at) as last_order
FROM orders
GROUP BY customer_id;

CREATE INDEX idx_customer_stats ON customer_stats(customer_id);

-- Использование (вместо подсчёта каждый раз)
SELECT * FROM customer_stats WHERE order_count > 10;

-- Обновление
REFRESH MATERIALIZED VIEW customer_stats;

Шаг 4: Инструменты профилирования

pgBadger (PostgreSQL)

# Анализ лог файла
pgbadger /var/log/postgresql/postgresql.log

# Генерирует HTML отчёт с графиками

MySQL Workbench

Menu -> Tools -> Database Profiler
-> Select queries
-> Analyze execution time

Snowflake Query Profile

-- Встроенный анализ
SELECT * FROM TABLE(RESULT_SCAN('query_id'));

Чеклист оптимизации

1. EXPLAIN ANALYZE - понять план выполнения
2. Индексы на JOIN/WHERE - добавить индексы
3. Статистика - ANALYZE для корректного плана
4. Подзапросы - заменить на JOIN если возможно
5. Функции в WHERE - заменить на range conditions
6. **SELECT *** - выбрать только нужные столбцы
7. Партиционирование - для больших таблиц
8. Кэширование - сохранить результаты
9. Денормализация - для OLAP хранилищ
10. Мониторинг - логировать медленные запросы

Пример: До и после оптимизации

ДО (12 сек):

SELECT *
FROM orders
WHERE YEAR(created_at) = 2024
  AND customer_id IN (SELECT id FROM customers WHERE status = 'Premium')
  AND EXISTS (SELECT 1 FROM order_items WHERE order_id = orders.id
              AND quantity * unit_price > 100);

ПОСЛЕ (200 мс):

SELECT DISTINCT o.*
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at >= '2024-01-01'
  AND o.created_at < '2025-01-01'
  AND c.status = 'Premium'
  AND oi.quantity * oi.unit_price > 100;

-- Индексы:
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_customers_status ON customers(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Удаление узких мест в SQL запросах - критический навык Data Engineer для обеспечения производительности хранилища данных.