Как найти узкое место в работе БД?

Question

claude-haiku-4.5 · Accepted Answer

## Поиск узких мест в работе БД

Найдение и устранение узких мест в базе данных — критически важный навык для оптимизации приложений. Существует систематический подход к диагностике проблем.

### 1. Анализ медленных запросов

**PostgreSQL: Включаем логирование медленных запросов**

```sql
-- Проверяем текущие параметры
SHOW log_min_duration_statement;

-- Включаем логирование запросов, которые выполняются дольше 1 сек
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- миллисекунды
ALTER SYSTEM SET log_statement = 'all';  -- логируем все запросы

SELECT pg_reload_conf();  -- Перезагружаем конфиг без перезагрузки

-- Проверяем логи
tail -f /var/log/postgresql/postgresql.log
```

**MySQL: Медленный лог запросов**

```sql
-- Включаем slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1 секунда
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Смотрим статистику
SHOW VARIABLES LIKE '%slow%';
```

### 2. EXPLAIN и EXPLAIN ANALYZE

**PostgreSQL: Детальный анализ плана выполнения**

```sql
-- Простой EXPLAIN
EXPLAIN
SELECT u.id, 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 > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name;

-- EXPLAIN ANALYZE — выполняет запрос и показывает реальные цифры
EXPLAIN ANALYZE
SELECT u.id, 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 > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name;

-- Результат показывает:
-- Seq Scan vs Index Scan — полный скан таблицы vs использование индекса
-- Планируемые vs реальные rows — если число строк сильно отличается, нужна статистика
-- Время выполнения в миллисекундах
```

**Что ищем в плане выполнения:**
- **Seq Scan** на большой таблице — нужен индекс
- **Nested Loop** с большим числом строк — может быть медленно
- **Hash Join vs Merge Join** — проверяем есть ли индексы на колонках связи
- **Filter** — условие применяется после скана, может замедлить

### 3. Профилирование запросов в коде Python

```python
import time
from django.db import connection
from django.db.models import Prefetch

# Django ORM профилирование
def get_users_with_orders():
    start = time.time()
    
    # ПЛОХО: N+1 query problem
    users = User.objects.all()  # 1 запрос
    for user in users:          # N дополнительных запросов
        orders = user.orders.all()
    
    print(f'Time: {time.time() - start:.2f}s')
    print(f'Queries: {len(connection.queries)}')
    for query in connection.queries:
        print(query['sql'])

# ХОРОШО: Используем select_related и prefetch_related
def get_users_with_orders_optimized():
    users = User.objects.prefetch_related('orders').all()  # 2 запроса вместо N+1
    return users

# select_related для ForeignKey (JOIN)
users = User.objects.select_related('profile').all()  # 1 запрос с JOIN

# prefetch_related для ManyToMany и Reverse ForeignKey
users = User.objects.prefetch_related(
    'orders',
    'orders__items'
).all()  # 3 запроса, но никаких N+1
```

### 4. Индексы: создание и анализ

```sql
-- Найти наиболее медленные запросы
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Проверить есть ли индекс на колонке
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Создаём индекс
CREATE INDEX idx_users_created_at ON users(created_at DESC);
CREATE INDEX idx_orders_user_id_status ON orders(user_id, status);  -- Составной индекс
CREATE INDEX idx_users_email ON users(LOWER(email));  -- Индекс на функцию

-- Проверяем использование индексов
EXPLAIN
SELECT * FROM users WHERE created_at > NOW() - INTERVAL '30 days';

-- Удаляем неиспользуемые индексы
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;
```

### 5. Анализ размера таблиц и индексов

```sql
-- Размер таблиц 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
LIMIT 20;

-- Размер индексов
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Кэширование: какой процент данных кэширован
SELECT 
    schemaname,
    tablename,
    heap_blks_read,
    heap_blks_hit,
    ROUND(heap_blks_hit::float / (heap_blks_hit + heap_blks_read) * 100, 2) AS cache_hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC;
```

### 6. Статистика таблиц

```sql
-- PostgreSQL: Обновляем статистику (анализатор использует её для плана)
ANALYZE users;
ANALYZE;  -- Все таблицы

-- Проверяем конфиг автоматического анализа
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_vacuum_scale_factor;

-- MySQL: Анализируем таблицы
ANALYZE TABLE users;
ANALYZE TABLE orders;
```

### 7. Проблема N+1 запросов

```python
# ПЛОХО: N+1 query
users = User.objects.all()
for user in users:  # Если 1000 users, 1000 запросов!
    print(user.profile.bio)  # Каждый доступ — отдельный запрос

# ХОРОШО: Используем select_related
users = User.objects.select_related('profile').all()  # 1 запрос с JOIN
for user in users:
    print(user.profile.bio)  # Данные уже загружены

# ПЛОХО: Для ManyToMany
users = User.objects.all()
for user in users:
    orders = user.orders.all()  # Каждый раз запрос

# ХОРОШО
users = User.objects.prefetch_related('orders').all()  # 2 запроса
for user in users:
    orders = user.orders.all()  # Данные из памяти

# Используем Prefetch для более сложных случаев
from django.db.models import Prefetch

prefer_active = Prefetch(
    'orders',
    queryset=Order.objects.filter(status='active')
)
users = User.objects.prefetch_related(prefer_active).all()
```

### 8. Кэширование результатов

```python
import hashlib
from django.core.cache import cache

def get_expensive_data(user_id):
    # Генерируем ключ кэша
    cache_key = f'user_data:{user_id}'
    
    # Проверяем кэш
    data = cache.get(cache_key)
    if data:
        return data
    
    # Если нет — выполняем дорогой запрос
    data = User.objects.select_related('profile').prefetch_related('orders').get(id=user_id)
    
    # Сохраняем в кэш на 1 час
    cache.set(cache_key, data, timeout=3600)
    
    return data

# Или используем @cache_page для представлений
from django.views.decorators.cache import cache_page

@cache_page(60 * 5)  # 5 минут
def user_detail(request, user_id):
    user = get_expensive_data(user_id)
    return render(request, 'user_detail.html', {'user': user})
```

### 9. Batch операции вместо циклов

```python
# ПЛОХО: сотни запросов
for user_id in user_ids:
    user = User.objects.get(id=user_id)
    user.is_active = True
    user.save()

# ХОРОШО: один запрос
User.objects.filter(id__in=user_ids).update(is_active=True)

# Batch insert
users = [
    User(name='User1', email='user1@example.com'),
    User(name='User2', email='user2@example.com'),
    User(name='User3', email='user3@example.com'),
]
User.objects.bulk_create(users, batch_size=1000)

# Batch update
users = User.objects.filter(status='inactive')
for user in users:
    user.status = 'active'
    user.updated_at = timezone.now()

User.objects.bulk_update(users, ['status', 'updated_at'], batch_size=1000)
```

### 10. Мониторинг в продакшене

```python
# Инструменты мониторинга
# 1. Django Debug Toolbar — для разработки
DEBUG = True  # Only in development!

# 2. django-silk — профилирование запросов
# 3. New Relic, DataDog — для продакшена
# 4. Prometheus + Grafana — метрики

# Пример метрики с Prometheus
from prometheus_client import Histogram, Counter

query_duration = Histogram(
    'db_query_duration_seconds',
    'Database query duration',
    buckets=(0.1, 0.5, 1.0, 2.5, 5.0)
)

query_count = Counter('db_queries_total', 'Total database queries')

# Используем
with query_duration.time():
    users = User.objects.all()
    query_count.inc()
```

### 11. Лучшие практики оптимизации

- **Профилируйте перед оптимизацией** — measure first
- **Используйте EXPLAIN ANALYZE** для каждого медленного запроса
- **Создавайте индексы на колонках в WHERE, JOIN, ORDER BY**
- **Избегайте N+1 запросов** — используйте select/prefetch_related
- **Кэшируйте дорогие операции** — Redis, Memcached
- **Batch операции** — bulk_create, bulk_update вместо циклов
- **Обновляйте статистику** — ANALYZE таблиц регулярно
- **Мониторьте в продакшене** — New Relic, DataDog
- **Читайте логи медленных запросов** — они вам помогут
- **Тестируйте на реальных данных** — производительность может отличаться

Систематический подход к оптимизации БД даёт наибольший результат.

Как найти узкое место в работе БД?

Комментарии (1)

Поиск узких мест в работе БД

1. Анализ медленных запросов

2. EXPLAIN и EXPLAIN ANALYZE

3. Профилирование запросов в коде Python

4. Индексы: создание и анализ

5. Анализ размера таблиц и индексов

6. Статистика таблиц

7. Проблема N+1 запросов

8. Кэширование результатов

9. Batch операции вместо циклов

10. Мониторинг в продакшене

11. Лучшие практики оптимизации