Решал ли задачи производительности БД

Оптимизация производительности БД: реальные примеры и методология

Да, я регулярно решаю задачи оптимизации производительности базы данных. Это одна из ключевых частей работы Backend разработчика, так как БД часто становится узким местом приложения.

Инструменты для диагностики

Первый шаг — найти проблему:

// 1. Slow Query Log в PostgreSQL
SET log_min_duration_statement = 1000; // Логируем запросы > 1 сек

// 2. Использование EXPLAIN для анализа плана выполнения
EXPLAIN (ANALYZE, BUFFERS)
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
ORDER BY order_count DESC;

// 3. В Node.js приложении использую для профилирования
const query = 'SELECT...';
const start = Date.now();
const result = await db.query(query);
console.log(`Query took ${Date.now() - start}ms`);

Проблема 1: N+1 Queries

Ситуация: Для каждого пользователя отдельный запрос за заказы.

// ❌ Плохой код (N+1 проблема):
const users = await User.findAll();
const usersWithOrders = await Promise.all(
  users.map(async (user) => {
    const orders = await Order.findAll({ where: { userId: user.id } });
    return { ...user, orders };
  })
);
// Это создаёт 1 запрос за пользователя + 1 общий = N+1 запросов!

// ✅ Решение 1: Eager loading
const usersWithOrders = await User.findAll({
  include: [{
    model: Order,
    attributes: ['id', 'total', 'createdAt']
  }]
});
// Только 2 запроса: users + orders

// ✅ Решение 2: Используем DataLoader для батчинга
import DataLoader from 'dataloader';

const orderLoader = new DataLoader(async (userIds) => {
  const orders = await Order.findAll({
    where: { userId: userIds }
  });
  
  // Группируем по userId
  return userIds.map(userId => 
    orders.filter(o => o.userId === userId)
  );
});

// В GraphQL resolver
const getOrders = (user) => orderLoader.load(user.id);

Проблема 2: Отсутствие индексов

Ситуация: Запрос работает медленно, сканирует всю таблицу.

// ❌ Медленный запрос - 5 сек на 1M записей
SELECT * FROM orders WHERE user_id = 123;

// ✅ Добавляем индекс
CREATE INDEX idx_orders_user_id ON orders(user_id);
// Теперь 5ms - 1000x быстрее!

// ✅ Составной индекс для более сложных запросов
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

// Полезный запрос для проверки индексов
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan as "index_scans",
  idx_tup_read as "tuples_read",
  idx_tup_fetch as "tuples_fetched"
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Проблема 3: Неоптимальные JOIN'ы

// ❌ Неоптимальный запрос
SELECT u.id, u.name, o.id, p.name, p.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.status = 'active'
AND o.created_at > '2024-01-01';
// Может быть медленно из-за отсутствия индексов

// ✅ Оптимизированный вариант
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
CREATE INDEX idx_products_id ON products(id);

// И правильный порядок фильтрации
SELECT u.id, u.name, o.id, p.name, p.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
WHERE u.status = 'active'
AND o.created_at > '2024-01-01';

Проблема 4: Большие результирующие наборы

// ❌ Загружаем все записи (100K строк)
const allOrders = await Order.findAll();

// ✅ Пагинация
const getOrders = async (page = 1, pageSize = 20) => {
  const offset = (page - 1) * pageSize;
  return Order.findAndCountAll({
    limit: pageSize,
    offset,
    order: [['createdAt', 'DESC']]
  });
};

// ✅ Курсор-основанная пагинация для больших наборов
const getOrdersWithCursor = async (afterId = null, limit = 20) => {
  const query = Order.findAll({
    limit: limit + 1,
    order: [['id', 'DESC']]
  });
  
  if (afterId) {
    query.where = { id: { [Op.lt]: afterId } };
  }
  
  const orders = await query;
  const hasMore = orders.length > limit;
  return {
    orders: orders.slice(0, limit),
    hasMore,
    nextCursor: hasMore ? orders[limit - 1].id : null
  };
};

Проблема 5: Неправильное использование DISTINCT/GROUP BY

// ❌ Дорогая операция
SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON u.id = o.user_id;
// Много дублей без индексов

// ✅ Оптимизация
SELECT DISTINCT ON (u.id) u.id, u.name
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.id;

// Или используем GROUP BY с правильным индексом
SELECT u.id, u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING COUNT(o.id) > 0;

Проблема 6: Отсутствие кэширования результатов

// ❌ Каждый раз запрашиваем из БД
router.get('/stats', async (req, res) => {
  const stats = await db.query(
    'SELECT COUNT(*) as total_users FROM users WHERE created_at > NOW() - INTERVAL 30 DAY'
  );
  res.json(stats);
});

// ✅ Кэшируем результат
const NodeCache = require('node-cache');
const cache = new NodeCache({ stdTTL: 3600 });

router.get('/stats', async (req, res) => {
  const cacheKey = 'stats_monthly';
  
  // Проверяем кэш
  let stats = cache.get(cacheKey);
  
  if (!stats) {
    // Загружаем из БД только если нет в кэше
    stats = await db.query(
      'SELECT COUNT(*) as total_users FROM users WHERE created_at > NOW() - INTERVAL 30 DAY'
    );
    cache.set(cacheKey, stats);
  }
  
  res.json(stats);
});

// Инвалидируем кэш при создании пользователя
router.post('/users', async (req, res) => {
  const newUser = await createUser(req.body);
  cache.del('stats_monthly'); // Очищаем кэш
  res.json(newUser);
});

Проблема 7: Блокировки и deadlock'и

// ❌ Риск deadlock'а
const transferMoney = async (fromUserId, toUserId, amount) => {
  await db.query(
    'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2',
    [amount, fromUserId]
  );
  
  await db.query(
    'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2',
    [amount, toUserId]
  );
};

// ✅ Исправленный вариант с правильным порядком UPDATE'ов
const transferMoney = async (fromUserId, toUserId, amount) => {
  // Всегда обновляем в одинаковом порядке (по ID) для избежания deadlock'ов
  const [first, second] = fromUserId < toUserId
    ? [fromUserId, toUserId]
    : [toUserId, fromUserId];
  
  await db.query('BEGIN TRANSACTION');
  
  try {
    await db.query(
      'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2 FOR UPDATE',
      [amount, fromUserId]
    );
    
    await db.query(
      'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2 FOR UPDATE',
      [amount, toUserId]
    );
    
    await db.query('COMMIT');
  } catch (error) {
    await db.query('ROLLBACK');
    throw error;
  }
};

Мой процесс оптимизации БД

1. Измерение — найти узкие места с EXPLAIN и slow query log
2. Анализ — понять причину (N+1, отсутствие индекса, плохой JOIN и т.д.)
3. Решение — выбрать оптимальный способ оптимизации
4. Тестирование — проверить на реальных данных
5. Мониторинг — отслеживать улучшения и регрессии

Результаты из реальных проектов

• Добавление индексов сократило время запроса с 5 сек до 50 мс
• Решение N+1 проблемы уменьшило количество БД запросов на 80%
• Кэширование стат данных упало нагрузку на БД на 60%
• Оптимизация JOIN'ов сократила время отклика API на 40%

Оптимизация БД — это не одноразовая работа, а постоянный процесс совершенствования по мере роста приложения.