Какие знаешь способы поиска проблем в БД?

Способы поиска и решения проблем в БД

В работе с базами данных важно уметь диагностировать и решать проблемы производительности, целостности данных и корректности запросов. Расскажу о проверенных методах и инструментах, которые я применял в production среде.

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

Первый и самый важный способ — логирование slow query log. В MySQL и PostgreSQL можно настроить порог времени выполнения:

-- MySQL: запросы медленнее 1 секунды
SET GLOBAL slow_query_log = 'ON'; 
SET GLOBAL long_query_time = 1;

-- PostgreSQL: через log_min_duration_statement
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1 секунда в миллисекундах
SELECT pg_reload_conf();

Эти логи показывают точный SQL, время выполнения и количество обработанных строк. Потом анализируешь их утилитами вроде pt-query-digest (Percona Toolkit).

2. EXPLAIN / EXPLAIN ANALYZE

Для понимания плана выполнения запроса использую EXPLAIN:

-- MySQL
EXPLAIN SELECT u.id, u.name, o.total 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2024-01-01'
ORDER BY u.id DESC;

-- PostgreSQL (более информативный)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM users WHERE email LIKE '%test%';  

Обращу внимание на:

• Sequential Scan — полное сканирование таблицы (плохо на больших таблицах)
• Index Scan — использование индекса (хорошо)
• Seq Scan + Filter — сканирование + фильтрация в памяти
• Высокое значение Buffers.Hits — запрос работает из памяти

3. Индексирование

Проблема медленности часто решается правильным индексированием. Ищу колонки с частыми условиями WHERE, JOIN и ORDER BY:

-- Проверяю используемые индексы
SHOW INDEX FROM users WHERE Seq_in_index = 1 AND Column_name = 'email';

-- Создаю составной индекс для частых фильтров
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

-- В PostgreSQL проверяю неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
ORDER BY idx_blks_read DESC;

4. Мониторинг подключений и блокировок

Проблемы возникают когда процессы конкурируют за ресурсы. Проверяю активные подключения и блокировки:

-- MySQL: текущие запросы
SHOW PROCESSLIST;
SHOW OPEN TABLES WHERE In_use > 0; -- таблицы в блокировке

-- PostgreSQL: более подробно
SELECT pid, usename, state, query_start, query 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY query_start;

-- Проверяю блокировки
SELECT DISTINCT blockingl.locktype, blockingl.database, 
       blockingl.relation::regclass, page, tuple, virtualxid, 
       transactionid, classid, objid, objsubid, 
       blockingl.pid, blockingp.usename, blockingp.application_name, 
       blockingp.client_addr 
FROM pg_locks blockingl 
JOIN pg_stat_activity blockingp ON blockingl.pid = blockingp.pid 
WHERE NOT blockingl.granted;

5. Профилирование через код (Java)

Часто проблемы в N+1 запросах. Использую логирование на уровне приложения:

// Spring Data JPA + Hibernate
public interface UserRepository extends JpaRepository<User, Long> {
    // Плохо: N+1 проблема - для каждого пользователя отдельный запрос за заказы
    @Query("SELECT u FROM User u") 
    List<User> findAllLazy();
    
    // Хорошо: одним JOIN запросом
    @Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.orders")
    List<User> findAllWithOrders();
}

// Логирование SQL через Hibernate properties
// spring.jpa.properties.hibernate.show_sql=true
// spring.jpa.properties.hibernate.format_sql=true
// logging.level.org.hibernate.SQL=DEBUG

Отключу lazy loading где возможно, используя @Query с FETCH JOIN или @EntityGraph.

6. Проверка целостности данных

Иногда проблемы не в производительности, а в консистентности:

-- Ищу orphaned записи (нарушение FK)
SELECT o.* FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.id IS NULL;

-- Проверяю дубликаты
SELECT email, COUNT(*) as cnt 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

-- Статистику таблиц (PostgreSQL)
ANALYZE users; -- обновляю статистику
VACUUM users;  -- чищу мёртвые строки

7. Инструменты мониторинга

В production среде используются специализированные инструменты:

• DBeaver — IDE для баз данных с EXPLAIN визуализацией
• pgAdmin — веб-интерфейс для PostgreSQL
• MySQLWorkbench — GUI для MySQL
• Datadog / New Relic — APM с метриками БД
• Prometheus + Grafana — для самостоятельного мониторинга

8. Проверка конфигурации БД

Недооптимизированные параметры влияют на производительность:

-- MySQL: размер буфера
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- PostgreSQL: shared_buffers (25% ОЗУ), work_mem ( 256MB+)
SHOW shared_buffers;
SHOW work_mem;

Мой подход в реальных проектах

1. Сначала метрики — slow logs, APM данные
2. EXPLAIN запросов — понимаю план выполнения
3. Индексирование — добавляю индексы под частые операции
4. Кэширование — Redis для горячих данных
5. Оптимизация кода — избегаю N+1, использую batch операции
6. Масштабирование — replication, sharding при необходимости

Важно помнить: оптимизация начинается с измерения, а не с предположений.