Как ты улучшишь производительность базы данных?

Как улучшить производительность базы данных

Улучшение производительности БД — это комплексный процесс, включающий анализ, оптимизацию и мониторинг. Рассмотрим стратегический подход к решению этой проблемы.

Фаза 1: Диагностика и анализ

Найди узкие места

Наиболее медленные запросы дают самый большой прирост производительности.

-- PostgreSQL: включи логирование медленных запросов
ALTER SYSTEM SET log_min_duration_statement = 100;  -- логировать запросы > 100ms

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;

Используй EXPLAIN

Анализируй план выполнения для каждого медленного запроса.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as orders_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

Ищи:

• Seq Scan (полное сканирование таблицы) — признак отсутствия индекса
• High planning/execution time
• Неожиданное количество строк

Собирай метрики

-- PostgreSQL: размер таблиц
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;

-- Количество строк
SELECT schemaname, tablename, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Фаза 2: Оптимизация индексов

Добавь недостающие индексы

-- На колонках в WHERE
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- На колонках JOIN
CREATE INDEX idx_order_details_order_id ON order_details(order_id);

-- На колонках ORDER BY
CREATE INDEX idx_orders_date_desc ON orders(created_at DESC);

Используй составные индексы

-- Если часто фильтруешь по нескольким полям
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at);

-- Порядок полей важен (equality перед range)
WHERE user_id = ? AND status = ? AND created_at > ?

Удали неиспользуемые индексы

-- PostgreSQL: найди неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Удали
DROP INDEX idx_unused;

Фаза 3: Оптимизация запросов

Переделай медленные запросы

Плохо: полное сканирование и множество условий

SELECT * FROM orders 
WHERE YEAR(created_at) = 2024 AND status = 'completed'
ORDER BY created_at DESC;

Хорошо: индексы и нужные колонки

SELECT id, user_id, total, created_at FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

Избегай проблем

-- Плохо: функция на индексированной колонке
WHERE UPPER(email) = 'JOHN@EXAMPLE.COM'  -- индекс не используется

-- Хорошо
WHERE email = 'john@example.com'  -- индекс используется

-- Плохо: подзапрос в SELECT
SELECT id, (SELECT COUNT(*) FROM orders WHERE user_id = users.id)
FROM users;  -- N+1 запрос

-- Хорошо: JOIN
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Фаза 4: Кеширование

Redis для горячих данных

# Кеширование результата запроса
def get_user_with_cache(user_id):
    cache_key = f"user:{user_id}"
    
    # Проверить кеш
    user = redis.get(cache_key)
    if user:
        return json.loads(user)
    
    # Запросить из БД если нет в кеше
    user = db.query("SELECT * FROM users WHERE id = ?", user_id)
    
    # Сохранить в кеш на 1 час
    redis.setex(cache_key, 3600, json.dumps(user))
    
    return user

Query result cache

# Кеширование результатов аналитических запросов
def get_monthly_stats():
    cache_key = "stats:monthly"
    
    stats = redis.get(cache_key)
    if not stats:
        stats = db.query("""
            SELECT month, revenue, orders_count
            FROM monthly_stats
            WHERE month = DATE_TRUNC('month', NOW())
        """)
        redis.setex(cache_key, 3600, json.dumps(stats))  # кеш на 1 час
    
    return stats

Фаза 5: Партиционирование и архивирование

Партиционирование больших таблиц

-- По датам для таблицы логов
CREATE TABLE logs (
    id INT,
    message VARCHAR(255),
    created_at DATE
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- Преимущества:
-- - Запросы работают только с нужной партицией
-- - Удаление старых данных быстрое
-- - Индексы меньше

Архивирование старых данных

-- Переместить в архив
INSERT INTO orders_archive
SELECT * FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- Удалить из основной таблицы
DELETE FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

Фаза 6: Масштабирование

Read replicas

Master DB (write)
    ↓ (репликация)
Slave DB 1 (read only)
Slave DB 2 (read only)
Slave DB 3 (read only)

Читай аналитику из реплик, запись в master.

Шардирование

Вместо одной огромной таблицы разделить на несколько:

users_1 (user_id % 4 = 0)
users_2 (user_id % 4 = 1)
users_3 (user_id % 4 = 2)
users_4 (user_id % 4 = 3)

Фаза 7: Конфигурация БД

PostgreSQL

# postgresql.conf
shared_buffers = 25% RAM  # 8GB для 32GB сервера
effective_cache_size = 75% RAM
work_mem = 4MB
maintenance_work_mem = 1GB

MySQL/MariaDB

# my.cnf
innodb_buffer_pool_size = 80% RAM
innodb_log_file_size = 512M
query_cache_size = 0  # отключить, используй Redis вместо
max_connections = 200

Фаза 8: Мониторинг

Создай дашборд

Мониторить:
- Query execution time (p50, p95, p99)
- Slow queries (> 100ms)
- Table sizes и growth
- Index size и usage
- Cache hit rate
- Connection count
- Disk usage
- Lock waits

Настрой алерты

- Если query_time > 1 сек
- Если disk free < 10%
- Если connections > 80%
- Если cache hit rate < 80%

Чеклист улучшения производительности

✅ Найдены медленные запросы (slow query log) ✅ Проанализирован EXPLAIN план ✅ Добавлены недостающие индексы ✅ Удалены неиспользуемые индексы ✅ Оптимизированы запросы ✅ Кеширование настроено (Redis) ✅ Большие таблицы партиционированы ✅ Старые данные архивированы ✅ Настроены read replicas ✅ Конфигурация БД оптимизирована ✅ Мониторинг работает

Значение для System Analyst

System Analyst должен:

• Понимать как работают индексы
• Уметь читать EXPLAIN план
• Знать когда использовать кеширование
• Планировать партиционирование и масштабирование
• Определять требования к производительности
• Мониторить и оптимизировать по метрикам