Как определить индексацию по запросу?

# Как определить необходимость индексации по запросу

Индексация — это критическая оптимизация баз данных, которая ускоряет поиск данных. Разработчик должен уметь анализировать запросы и определять, когда индекс будет полезен. Вот систематический подход к определению необходимости индексации.

1. Анализ плана выполнения запроса (EXPLAIN)

Самый надежный способ — использовать EXPLAIN для анализа плана выполнения.

PostgreSQL

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

-- Детальный анализ с временем выполнения
EXPLAIN ANALYZE
SELECT u.id, u.name, o.total_price
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = user@example.com
AND o.created_at > NOW() - INTERVAL 30 day;

На что смотреть:

Seq Scan on users  (cost=0.00..35.50 rows=1 width=32)  ← ПЛОХО: полное сканирование таблицы
  Filter: (email = user@example.com)

Index Scan using users_email_idx on users  (cost=0.29..8.30 rows=1 width=32)  ← ХОРОШО: использует индекс
  Index Cond: (email = user@example.com)

MySQL

EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE email = user@example.com\G

EXPLAIN FORMAT=TREE
SELECT * FROM users WHERE email = user@example.com\G

2. Ключевые метрики в плане выполнения

Признаки отсутствия индекса (требуется оптимизация)

✗ Full Table Scan / Seq Scan — сканирование всех строк таблицы
✗ Cost слишком высокий — > 1000000
✗ Rows много — сканируются миллионы строк
✗ Filter: — условие применяется ПОСЛЕ сканирования (неэффективно)

Признаки использования индекса (хорошо)

✓ Index Scan / Index Seek — использует индекс
✓ Cost низкий — < 100
✓ Index Cond — условие применяется ВО ВРЕМЯ доступа к индексу
✓ Rows мало — сканируются только нужные строки

3. Java приложение: практический анализ

Включение логирования SQL

# application.yml
spring:
  jpa:
    hibernate:
      ddl-auto: validate
  properties:
    hibernate:
      dialect: org.hibernate.dialect.PostgreSQL13Dialect
      generate_statistics: true  # Включить статистику Hibernate
      jdbc:
        fetch_size: 50
        batch_size: 20
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.stat: DEBUG
    org.hibernate.type.descriptor.sql: TRACE

Анализ с HibernateStatistics

@Service
@RequiredArgsConstructor
public class QueryAnalysisService {
    private final EntityManager entityManager;
    
    /**
     * Анализирует выполнение JPA запроса
     */
    public void analyzeQuery() {
        // Получаем сессию Hibernate
        Session session = entityManager.unwrap(Session.class);
        SessionFactory sessionFactory = session.getSessionFactory();
        Statistics stats = sessionFactory.getStatistics();
        stats.setStatisticsEnabled(true);
        
        long startTime = System.currentTimeMillis();
        
        // Выполняем запрос
        TypedQuery<User> query = entityManager.createQuery(
            "SELECT u FROM User u WHERE u.email = :email",
            User.class
        );
        query.setParameter("email", "user@example.com");
        List<User> results = query.getResultList();
        
        long executionTime = System.currentTimeMillis() - startTime;
        
        // Анализируем статистику
        QueryStatistics queryStats = stats.getQueryStatistics(
            "SELECT u FROM User u WHERE u.email = :email"
        );
        
        System.out.println("Execution time: " + executionTime + "ms");
        System.out.println("Query executions: " + queryStats.getExecutionCount());
        System.out.println("Average time: " + queryStats.getExecutionAvgTime() + "ms");
        System.out.println("Rows fetched: " + queryStats.getExecutionRowCount());
    }
}

4. Диагностический запрос для выявления недостающих индексов

PostgreSQL: выявление медленных запросов

-- Таблица: pg_stat_statements (требует расширение)
SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

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

-- Таблицы со множеством seq scans
SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_scan DESC;

5. Правила создания индексов

Когда нужен индекс

✓ Колонка часто используется в WHERE
✓ Колонка часто используется в JOIN ON
✓ Таблица содержит > 1000 строк
✓ Выборка < 10% строк таблицы
✓ Колонка имеет высокую cardinality (много уникальных значений)

Когда индекс не нужен

✗ Выборка > 10-20% строк таблицы
✗ Колонка редко используется
✗ Таблица маленькая (< 1000 строк)
✗ Колонка имеет низкую cardinality (мало уникальных значений)
✗ Нужны частые INSERT/UPDATE (индексы замедляют запись)

6. Примеры создания индексов

На одну колонку

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Составной индекс (для нескольких условий)

-- Часто ищем по user_id И created_at
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- Запрос использует оба условия
SELECT * FROM orders 
WHERE user_id = 123 AND created_at > 2024-01-01;

Индекс на LIKE запросы (PostgreSQL)

CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);

SELECT * FROM users WHERE name ILIKE %john%;  -- Использует индекс

Partial индекс (для условного индексирования)

CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

SELECT * FROM users WHERE deleted_at IS NULL AND email = user@example.com;

7. JPA/Hibernate: определение индексов

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email", unique = true),
    @Index(name = "idx_created_at", columnList = "created_at DESC"),
    @Index(name = "idx_user_status", columnList = "user_id,status")
})
public class User {
    @Id
    private Long id;
    
    @Column(unique = true)  // Автоматически создаёт индекс
    private String email;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
    
    private String status;
}

8. Мониторинг производительности в Java

@Service
@RequiredArgsConstructor
public class PerformanceMonitorService {
    private final JdbcTemplate jdbcTemplate;
    
    /**
     * Проверяет индексы и их использование
     */
    public void checkIndexHealth() {
        String sql = """
            SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
            FROM pg_stat_user_indexes
            ORDER BY idx_scan ASC
            """;
        
        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql);
        
        for (Map<String, Object> row : results) {
            Long scans = (Long) row.get("idx_scan");
            if (scans == 0) {
                System.out.println("⚠️  Неиспользуемый индекс: " + row.get("indexname"));
            }
        }
    }
}

Алгоритм принятия решения

1. ВЫПОЛНИ EXPLAIN запрос
   ↓
2. Видишь Seq Scan? ДА ↓ НЕТ → индекс возможно не нужен
   ↓
3. Таблица большая (> 1000)? ДА ↓ НЕТ → индекс не критичен
   ↓
4. Выборка < 10%? ДА ↓ НЕТ → индекс может не помочь
   ↓
5. Cardinality высокая? ДА ↓ НЕТ → индекс не поможет
   ↓
6. СОЗДАЙ ИНДЕКС и проверь улучшение

Резюме

Определение необходимости индексации — это процесс:

1. Анализа планов выполнения (EXPLAIN)
2. Наблюдения метрик БД (pg_stat_statements, slow query log)
3. Понимания карактеристик данных (размер, cardinality, частота запросов)
4. Тестирования и мониторинга (до/после создания индекса)

Запомните: лучший индекс — это индекс, который фактически используется и улучшает производительность реальных запросов.