Как можно проанализировать SQL запрос?

Как можно проанализировать SQL запрос

Оптимизация SQL запросов — критическая задача для производительности приложения. Существует несколько инструментов и методов для анализа.

1. EXPLAIN — основной инструмент анализа

-- Базовый EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Расширенный EXPLAIN с дополнительной информацией
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- PostgreSQL с форматом JSON
EXPLAIN (FORMAT JSON, ANALYZE) 
SELECT * FROM users WHERE email = 'user@example.com';

-- MySQL с форматом JSON
EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE email = 'user@example.com'\G

Результат показывает:

• seq scan vs index scan — полное сканирование или индекс
• rows — примерное/реальное количество строк
• cost — единица измерения обработки (lower = better)
• filter vs index condition — где применяется условие

2. Интеграция EXPLAIN в Java приложение

@Service
public class QueryAnalysisService {
    
    private final JdbcTemplate jdbcTemplate;
    
    public void analyzeQuery(String sql) {
        String explainQuery = "EXPLAIN ANALYZE " + sql;
        
        try {
            List<Map<String, Object>> results = jdbcTemplate
                .queryForList(explainQuery);
            
            System.out.println("=== Query Plan ===");
            results.forEach(row -> {
                System.out.println(row.get("QUERY PLAN"));
            });
        } catch (DataAccessException e) {
            log.error("Failed to analyze query", e);
        }
    }
    
    public void findSlowQueries() {
        // Для MySQL: включи slow query log
        // Для PostgreSQL: включи log_min_duration_statement
        
        String query = """
            SELECT query, count(*), mean_time, max_time
            FROM pg_stat_statements
            WHERE mean_time > 100  -- запросы дольше 100ms
            ORDER BY mean_time DESC
        """;
        
        jdbcTemplate.queryForList(query).forEach(row -> {
            log.warn("Slow query: {} ({}ms avg)", 
                row.get("query"), row.get("mean_time"));
        });
    }
}

3. Ключевые метрики при анализе

┌─ Seq Scan - ПЛОХО (Full table scan)
│  ├─ Cost: high (1000.00..35000.00)
│  ├─ Rows: all (1000000)
│  ├─ Reason: No index on column
│  └─ Fix: CREATE INDEX
│
└─ Index Scan - ХОРОШО
   ├─ Cost: low (0.42..8.44)
   ├─ Rows: 1-100
   ├─ Reason: Index on WHERE column
   └─ Plan: Use existing index

4. Примеры анализа реальных запросов

Пример 1: Запрос без индекса

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

-- Результат:
Seq Scan on orders  (cost=0.00..35000.00 rows=500000)
  Filter: (status = 'PENDING' AND created_at > '2024-01-01')
  Planning time: 0.123 ms
  Execution time: 2500.456 ms  ← СЛИШКОМ ДОЛГО!

Решение: создаём индекс

CREATE INDEX idx_orders_status_created 
ON orders(status, created_at DESC);

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

-- Результат после индекса:
Index Scan using idx_orders_status_created (cost=0.42..156.23 rows=1200)
  Index Cond: (status = 'PENDING' AND created_at > '2024-01-01')
  Planning time: 0.089 ms
  Execution time: 8.234 ms  ← НАМНОГО БЫСТРЕЕ!

Пример 2: Проблема с JOIN

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'COMPLETED'
GROUP BY u.id, u.name;

-- Может показать Hash Join vs Nested Loop
-- Hash Join быстрее для больших наборов данных

5. Hibernate Query Analysis

// Включи логирование SQL в application.yml
spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true
        generate_statistics: true

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE
    org.hibernate.stat: DEBUG

// Получение статистики
@Service
public class HibernateStatisticsService {
    
    @PersistenceUnit
    private EntityManagerFactory emf;
    
    public void printQueryStats() {
        SessionFactory sf = emf.unwrap(SessionFactory.class);
        Statistics stats = sf.getStatistics();
        
        System.out.println("Total queries: " + stats.getQueryExecutionCount());
        System.out.println("Cache hits: " + stats.getCacheHitCount());
        System.out.println("Cache misses: " + stats.getCacheMissCount());
        
        for (String query : stats.getQueries()) {
            long count = stats.getQueryExecutionCount(query);
            long avgTime = stats.getQueryExecutionAvgTime(query);
            System.out.println(query + " - " + count + " times, avg " + avgTime + "ms");
        }
    }
}

6. QueryDSL для оптимальных запросов

@Repository
public class UserRepositoryCustom {
    
    private final JPAQueryFactory queryFactory;
    
    public List<User> findActiveUsers(String searchTerm) {
        // QueryDSL генерирует оптимальные SQL запросы
        return queryFactory
            .selectFrom(QUser.user)
            .where(QUser.user.status.eq("ACTIVE")
                .and(QUser.user.name.containsIgnoreCase(searchTerm)))
            .fetch();
    }
}

7. N+1 Problem Detection

// Плохо: N+1 запросов
@Service
public class BadUserService {
    private final UserRepository userRepository;
    
    public List<UserDTO> getUsersWithOrders() {
        // 1 запрос: SELECT * FROM users
        List<User> users = userRepository.findAll();
        
        // N запросов: SELECT * FROM orders WHERE user_id = ?
        return users.stream()
            .map(u -> new UserDTO(
                u.getId(),
                u.getName(),
                u.getOrders()  // Lazy load - доп запрос на каждого юзера!
            ))
            .collect(Collectors.toList());
    }
}

// Хорошо: Eager loading
@Repository
public interface UserRepository extends JpaRepository<User, UUID> {
    @Query("SELECT u FROM User u LEFT JOIN FETCH u.orders WHERE u.id IN :ids")
    List<User> findWithOrdersBatch(@Param("ids") Collection<UUID> ids);
    
    @EntityGraph(attributePaths = "orders")
    List<User> findAllWithOrders();
}

@Service
public class GoodUserService {
    private final UserRepository userRepository;
    
    public List<UserDTO> getUsersWithOrders() {
        // 1 запрос с JOIN
        List<User> users = userRepository.findAllWithOrders();
        
        return users.stream()
            .map(u -> new UserDTO(u.getId(), u.getName(), u.getOrders()))
            .collect(Collectors.toList());
    }
}

8. Мониторинг в production

@Component
public class QueryPerformanceLoggingAspect {
    
    private static final long SLOW_QUERY_THRESHOLD = 500; // ms
    
    @Around("execution(* com.example.repository..*.*(..))") 
    public Object logQueryPerformance(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();
        
        try {
            return joinPoint.proceed();
        } finally {
            long duration = System.currentTimeMillis() - startTime;
            
            if (duration > SLOW_QUERY_THRESHOLD) {
                log.warn("SLOW QUERY: {} took {}ms", 
                    joinPoint.getSignature(), duration);
            }
        }
    }
}

9. Инструменты для анализа

┌─ MySQL
│  ├─ EXPLAIN FORMAT=JSON
│  ├─ Slow Query Log (log_slow_queries)
│  ├─ Performance Schema
│  └─ MySQL Workbench
│
├─ PostgreSQL
│  ├─ EXPLAIN ANALYZE
│  ├─ pg_stat_statements
│  ├─ QUERY logs с log_min_duration_statement
│  ├─ pgAdmin (визуально)
│  └─ DBeaver (анализ плана)
│
└─ SQL Server
   ├─ SET STATISTICS IO, TIME ON
   ├─ Query Execution Plans
   └─ SQL Server Management Studio

10. Чеклист оптимизации

• Запустить EXPLAIN на медленном запросе
• Проверить наличие индексов на WHERE, JOIN, ORDER BY колонках
• Убедиться что используется индекс (Index Scan, не Seq Scan)
• Проверить затраты (cost) — оптимизировать если > 1000
• Выявить N+1 проблемы (SELECT в loop)
• Использовать JOIN FETCH или @EntityGraph
• Профилировать с помощью Hibernat statistics
• Добавить кэширование если запрос выполняется часто
• Рассмотреть денормализацию для сложных запросов
• Мониторить производительность в production

Эффективный анализ SQL — это путь к высокопроизводительному приложению!