Как глубоко погружался в чистый SQL за рамками ORM?

Как глубоко погружался в чистый SQL за рамками ORM?

Это хороший вопрос, потому что многие разработчики полагаются на ORM и упускают важные знания о чистом SQL. Я могу рассказать про мой реальный опыт.

Мотивация: Когда ORM недостаточно

Moя поворотная точка была, когда нужно было оптимизировать запрос, который возвращал данные за 5+ секунд. ORM (Sequelize) генерировал неэффективный SQL:

// Sequelize ORM (неэффективно)
const orders = await Order.findAll({
  include: [{
    model: User,
    include: [{ model: Profile }]
  }],
  where: { status: 'completed' }
});
// Генерирует 1 запрос + N запросов для каждого пользователя (N+1 problem)

Я понял, что без глубокого понимания SQL, я не смогу правильно оптимизировать.

Уровень 1: Базовые CRUD запросы

SELECT с условиями:

-- Простой SELECT
SELECT id, name, email FROM users WHERE status = 'active';

-- С сортировкой и лимитом
SELECT id, name, email FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20;

-- С агрегацией
SELECT COUNT(*) as total, status FROM users GROUP BY status;

INSERT:

-- Простой INSERT
INSERT INTO users (name, email, status) 
VALUES ('John', 'john@example.com', 'active');

-- Множественный INSERT
INSERT INTO users (name, email, status) VALUES
('John', 'john@example.com', 'active'),
('Jane', 'jane@example.com', 'active'),
('Bob', 'bob@example.com', 'pending');

-- INSERT с возвратом ID
INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
RETURNING id, created_at;

UPDATE и DELETE:

UPDATE users SET status = 'inactive', updated_at = NOW() 
WHERE last_login < NOW() - INTERVAL '6 months';

DELETE FROM users WHERE id = 123;

Уровень 2: JOINs (самая важная часть)

Это то, что многие junior разработчики неправильно используют в ORM.

INNER JOIN (только пересечение):

-- Все заказы с данными пользователя
SELECT o.id, o.total, u.name, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed';

LEFT JOIN (все из левой таблицы):

-- Все пользователи, у которых есть заказы (или NULL если нет)
SELECT u.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.id, u.name;

Множественные JOINs (реальный пример):

-- Получить все заказы с деталями пользователя, товаров и отзывов
SELECT 
  o.id as order_id,
  o.total,
  u.id as user_id,
  u.name,
  oi.product_id,
  p.title,
  r.rating,
  r.comment
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
LEFT JOIN reviews r ON o.id = r.order_id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

Уровень 3: Subqueries (подзапросы)

Subquery в WHERE:

-- Найти пользователей, чей средний заказ выше среднего
SELECT u.id, u.name, AVG(o.total) as avg_order_value
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING AVG(o.total) > (SELECT AVG(total) FROM orders);

Subquery в FROM (derived table):

-- Рейтинг пользователей по количеству заказов
SELECT 
  t.user_id,
  t.user_name,
  t.order_count,
  RANK() OVER (ORDER BY t.order_count DESC) as user_rank
FROM (
  SELECT u.id as user_id, u.name as user_name, COUNT(o.id) as order_count
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
) t;

IN subquery:

-- Найти товары, которые никогда не покупались
SELECT id, title, price FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

Уровень 4: Window Functions (оконные функции) — мое любимое

Оконные функции — это очень мощная, но часто неиспользуемая функция. Я потратил время на их изучение и это открыло глаза.

ROW_NUMBER (нумерация строк):

-- Получить последний заказ каждого пользователя
WITH ranked_orders AS (
  SELECT 
    o.*,
    u.name,
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) as rn
  FROM orders o
  JOIN users u ON o.user_id = u.id
)
SELECT * FROM ranked_orders WHERE rn = 1;

RANK и DENSE_RANK (ранжирование с учётом дублей):

-- Топ-10 товаров по сумме продаж
SELECT 
  p.id,
  p.title,
  SUM(oi.quantity * oi.price) as total_sales,
  RANK() OVER (ORDER BY SUM(oi.quantity * oi.price) DESC) as sales_rank
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.title
LIMIT 10;

LAG и LEAD (доступ к соседним строкам):

-- Отслеживание изменения цены товара
SELECT 
  p.id,
  p.title,
  ph.price,
  ph.updated_at,
  LAG(ph.price) OVER (PARTITION BY p.id ORDER BY ph.updated_at) as prev_price,
  ph.price - LAG(ph.price) OVER (PARTITION BY p.id ORDER BY ph.updated_at) as price_change
FROM price_history ph
JOIN products p ON ph.product_id = p.id
ORDER BY p.id, ph.updated_at DESC;

Уровень 5: Common Table Expressions (CTE) и рекурсивные запросы

CTE (WITH) для читаемости:

WITH active_users AS (
  SELECT id, name, email FROM users WHERE status = 'active'
),
user_orders AS (
  SELECT au.id, au.name, COUNT(o.id) as order_count, SUM(o.total) as lifetime_value
  FROM active_users au
  LEFT JOIN orders o ON au.id = o.user_id
  GROUP BY au.id, au.name
)
SELECT * FROM user_orders
WHERE lifetime_value > 1000
ORDER BY lifetime_value DESC;

Рекурсивный CTE (например, иерархия категорий):

WITH RECURSIVE category_hierarchy AS (
  -- Базовый случай: все корневые категории
  SELECT id, name, parent_id, 0 as level
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  -- Рекурсивный случай
  SELECT c.id, c.name, c.parent_id, ch.level + 1
  FROM categories c
  JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT id, name, parent_id, level FROM category_hierarchy;

Уровень 6: Оптимизация и EXPLAIN ANALYZE

Это критически важный навык для production системе.

EXPLAIN ANALYZE (анализ плана выполнения):

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

Вывод показывает:

• Sequential Scan vs Index Scan (плохо vs хорошо)
• Estimated rows vs Actual rows
• Execution time
• Buffer hits

Реальный пример оптимизации, которую я сделал:

До: 8 секунд, Sequential Scan по таблице из 5 млн строк

-- Плохо
SELECT * FROM orders WHERE created_at > '2023-01-01';

После: 50 мс, Index Scan

-- Хорошо
CREATE INDEX idx_orders_created_at ON orders(created_at);
SELECT * FROM orders WHERE created_at > '2023-01-01';

Уровень 7: Мой реальный проект

Задача: Оптимизировать report, который выполнялся 30 секунд

Проблема:

• ORM генерировал N+1 запросы
• Отсутствовали нужные индексы
• Было множество ненужных JOIN'ов

Решение: Я переписал на чистый SQL с:

• Правильными INDEX'ами
• Одним оптимизированным запросом
• Window Functions для расчётов

Результат:

До:  30 секунд + множество запросов
После: 300 мс + один запрос
Улучшение: в 100 раз

Код:

WITH order_stats AS (
  SELECT 
    u.id,
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.total) as lifetime_value,
    AVG(o.total) as avg_order_value,
    MAX(o.created_at) as last_order_date,
    RANK() OVER (ORDER BY SUM(o.total) DESC) as customer_rank
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.status = 'active'
  GROUP BY u.id, u.name
)
SELECT * FROM order_stats
WHERE customer_rank <= 100
ORDER BY customer_rank;

Как я применяю это в Node.js

Использование raw SQL в Express.js:

const { Pool } = require('pg');

const pool = new Pool();

app.get('/api/reports/top-customers', async (req, res) => {
  try {
    const query = `
      WITH order_stats AS (
        SELECT u.id, u.name, COUNT(o.id) as order_count, SUM(o.total) as lifetime_value
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.status = $1
        GROUP BY u.id, u.name
      )
      SELECT * FROM order_stats
      ORDER BY lifetime_value DESC
      LIMIT $2;
    `;
    
    const result = await pool.query(query, ['active', 100]);
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

С использованием параметризованных запросов (защита от SQL injection):

// ПРАВИЛЬНО — параметризованный запрос
await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

// НЕПРАВИЛЬНО — уязвимо
await pool.query(`SELECT * FROM users WHERE id = ${userId}`);

Мой путь обучения

1. SQL Tutorial (базовые CRUD) — 1 неделя
2. JOINs и Subqueries (LeetCode SQL задачи) — 2 недели
3. Window Functions (ежедневная практика) — 3 недели
4. EXPLAIN ANALYZE (оптимизация реальных запросов) — 2 недели
5. Производственный опыт (написание сложных отчётов) — 2+ месяца

Почему это важно

Better SQL knowledge позволяет мне:

• Писать быстрые запросы вместо того, чтобы полагаться на ORM
• Отлаживать production issues быстро
• Проектировать БД правильно с самого начала
• Общаться с DBA на одном языке
• Быть более самостоятельным в работе

Лучшие ресурсы для обучения

1. LeetCode SQL problems — практика
2. PostgreSQL документация — reference
3. Explain.depesz.com — анализ EXPLAIN вывода
4. SQL Performance Explained (книга) — глубокое понимание
5. Практика на production данных — лучший способ

Вывод: Глубокое понимание SQL — это не опция для senior разработчика, это обязательство. Я потратил на это время и окупил это сотни раз через оптимизацию и решение сложных проблем.