Какие знаешь способы оптимизации БД?

Способы оптимизации базы данных

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

Индексирование (Indexing)

B-Tree индексы (по умолчанию):

• Для диапазонных запросов и сортировки
• Быстрый поиск, но медленная вставка
• CREATE INDEX idx_user_email ON users(email);

Hash индексы:

• Только для точного совпадения
• Быстрее B-Tree для equality
• Не поддерживают диапазоны

Composite индексы:

• Индекс на несколько колонок
• Порядок колонок важен
• CREATE INDEX idx_order_user_date ON orders(user_id, created_at);

Частичные индексы:

• Индекс только на subset данных
• CREATE INDEX idx_active_users ON users(id) WHERE deleted_at IS NULL;

Анализ индексов:

• EXPLAIN ANALYZE для query planning
• Искать Sequential Scans вместо Index Scans
• Удалять неиспользуемые индексы

Запросы и Query Optimization

Использование EXPLAIN:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01';

• Seq Scan (плохо) vs Index Scan (хорошо)
• Rows estimated vs actual
• Обнаруживать N+1 проблемы

Оптимизация JOIN:

• Использовать INNER JOIN вместо LEFT когда возможно
• Проверить что join условия используют индексированные колонки
• Избегать JOIN в WHERE условиях

Aggregations:

• Использовать GROUP BY вместо много SELECT
• Добавлять индексы на группируемые колонки
• Материализованные представления для часто используемых агрегаций

Limit и Offset:

• LIMIT эффективен
• OFFSET медленный с большими числами — использовать keyset pagination

Денормализация

Когда денормализировать:

• Часто читаемые, редко изменяемые данные
• Для избежания дорогих JOIN операций
• Для расчета COUNT/SUM без полного сканирования

Примеры:

• Сохранять total_price в заказе вместо расчета каждый раз
• Сохранять user_count в категории для быстрого отображения
• Кэшировать последний статус заказа в main таблице

Недостатки:

• Синхронизация данных сложнее
• Требует триггеров или приложения логики
• Больше памяти

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

Range партиционирование:

• По датам для историческихданных
• Partition by RANGE (created_at)
• Быстрые archival и deletion

Hash партиционирование:

• Равномерное распределение
• Partition by HASH (user_id)

List партиционирование:

• По статусу, странам, типам
• Partition by LIST (country)

Преимущества:

• Улучшенная производительность на больших таблицах
• Параллелизм запросов
• Облегчено обслуживание

Кэширование

Redis кэш слой:

• Cache frequently accessed queries
• Cache user sessions
• Cache calculations

Query результат кэширование:

• Cache SELECTS с длительными результатами
• Invalidate при UPDATE
• TTL for auto-expiry

Application level кэширование:

• ORM query caching
• Function result caching
• Memoization для дорогих вычислений

Архитектурные изменения

Read replicas:

• Разделить read и write трафик
• Несколько реплик для чтения
• Master только для write

Sharding:

• Горизонтальное разделение данных
• Шард по user_id, customer_id
• Параллельная обработка

Columnar storage:

• Для аналитических запросов
• ClickHouse, Redshift
• Лучше для GROUP BY, SUM

OLTP vs OLAP:

• Разные БД для разных паттернов
• PostgreSQL для OLTP
• Redshift/BigQuery для OLAP

Конфигурация БД

PostgreSQL параметры:

• shared_buffers — размер кэша
• work_mem — память для операций
• maintenance_work_mem — для VACUUM, CREATE INDEX
• random_page_cost — параметр query planner

VACUUM и ANALYZE:

• VACUUM удаляет мертвые строки
• ANALYZE обновляет статистику для planner
• Настроить autovacuum параметры

Мониторинг

Slow Query Logs:

• Логировать запросы > N ms
• Анализировать most common slow queries
• Инструменты: pg_stat_statements

Метрики для мониторинга:

• Cache hit ratio
• Query execution time
• Lock contention
• Disk I/O
• Connection count

Инструменты:

• pgAdmin — для PostgreSQL
• DataGrip — мощный анализ
• New Relic, Datadog — APM с БД insights

Типичные bottlenecks

1. Missing indexes — диагностика: Seq Scan в EXPLAIN
2. N+1 queries — диагностика: много похожих запросов в логе
3. Lock contention — диагностика: pg_locks
4. Memory issues — диагностика: OOM errors, slow performance
5. Disk I/O — диагностика: iostat, disk wait time

Итеративный процесс

1. Мониторить и обнаружить проблему
2. Использовать EXPLAIN ANALYZE для диагностики
3. Выбрать решение (индекс, денормализация, кэш)
4. Реализовать и тестировать
5. Мониторить результаты
6. Повторить

Оптимизация БД — это непрерывный процесс. Главное — правильно мониторить и измерять эффект от изменений.