На сколько индексы повышают скорость получения данных

Индексы в БД: влияние на производительность

Индексы кардинально повышают скорость чтения — обычно в 100-1000 раз в зависимости от размера таблицы и типа запроса. Однако это приходит ценой замедления записи и дополнительного потребления памяти.

Порядок величины производительности

Сравнение времени поиска по полю в таблице с 1 миллионом строк:

Как индекс ускоряет поиск

Без индекса: Full Table Scan (O(n))

-- Без индекса на email
SELECT * FROM users WHERE email = 'john@example.com';

-- SQL сканирует ВСЕ 1 млн строк
-- Время: ~100ms

Визуализация:

Таблица: [row1, row2, row3, ... row1000000]
          ↓     ↓     ↓             ↓
      найти? нет  нет    ...      ДА! (в конце)
      
Проверены: 1,000,000 строк

С индексом: Binary Search (O(log n))

-- С индексом B-Tree на email
SELECT * FROM users WHERE email = 'john@example.com';

-- SQL использует индекс (сбалансированное дерево)
-- Время: ~0.1ms

Визуализация:

Индекс B-Tree:
                    'mail'
                   /      \
               'john'      'zack'
               /    \      /     \
            'dave' 'mary' 'paul'  'zoe'
            
↓
Binary search: 1 → 2 → 3 → найдено!
Проверены: ~20 строк (log2(1000000) ≈ 20)

Типы индексов и их влияние

1. Primary Key / UNIQUE Index

Самое быстрое (O(log n)):

@Entity
public class User {
    @Id
    private Long id;  // ← Автоматический индекс
    
    @Column(unique = true)
    private String email;  // ← Уникальный индекс
}

// Запрос по PK: <1ms
SELECT * FROM users WHERE id = 123;

2. Обычный индекс (B-Tree)

Фаворит для большинства случаев (O(log n)):

-- Создание индекса
CREATE INDEX idx_email ON users(email);

-- Запрос: ~0.5ms (вместо 100ms)
SELECT * FROM users WHERE email = 'john@example.com';

-- Диапазонный запрос: ~5ms
SELECT * FROM users WHERE email LIKE 'john%';

3. Составной индекс (Composite)

Для нескольких полей:

-- Оптимизирует запросы с несколькими условиями
CREATE INDEX idx_user_country ON users(country, city);

-- ✅ Быстро: используется индекс
SELECT * FROM users WHERE country = 'USA' AND city = 'NYC';

-- ✅ Быстро: partial index usage
SELECT * FROM users WHERE country = 'USA';

-- ❌ Медленно: неправильный порядок
SELECT * FROM users WHERE city = 'NYC';  -- Индекс не помогает

Реальный пример в Java/Spring

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email"),
    @Index(name = "idx_created_at", columnList = "created_at"),
    @Index(name = "idx_user_status", columnList = "user_id,status")
})
public class User {
    @Id
    private Long id;
    
    @Column(length = 255)
    private String email;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
    
    @ManyToOne
    private Account user;
    
    @Enumerated(EnumType.STRING)
    private Status status;
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // ✅ Быстро: есть индекс на email
    Optional<User> findByEmail(String email);
    
    // ✅ Быстро: есть индекс на created_at
    List<User> findByCreatedAtAfter(LocalDateTime date);
    
    // ✅ Быстро: есть составной индекс (user_id, status)
    List<User> findByUserAndStatus(Account user, Status status);
}

Измерение производительности

-- PostgreSQL: EXPLAIN показывает план выполнения

-- БЕЗ индекса:
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Seq Scan on users  (cost=0.00..10000.00 rows=1)
  Filter: (email = 'john@example.com')
  
-- С индексом:
CREATE INDEX idx_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Index Scan using idx_email on users  (cost=0.29..0.31 rows=1)
  Index Cond: (email = 'john@example.com')

Когда индексы НЕ помогают

-- ❌ LIKE с началом wildcarda (нужен FULL-TEXT INDEX)
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- Индекс не используется

-- ❌ OR без индекса на обоих полях
SELECT * FROM users WHERE email = 'x' OR phone = 'y';  -- Медленно

-- ❌ Функция в WHERE
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';  -- Медленно

-- ❌ Неправильный порядок составного индекса
CREATE INDEX idx ON users(country, city);
SELECT * FROM users WHERE city = 'NYC';  -- Индекс не помогает

Стоимость индексов

-- INSERT/UPDATE медленнее (нужно обновить индекс)

Без индекса:
INSERT INTO users VALUES (...);  -- 0.5ms

С 5 индексами:
INSERT INTO users VALUES (...);  -- 2.5ms (5x медленнее)

-- Память
Таблица (1M rows): ~100MB
Один B-Tree индекс: ~20MB
Пять индексов: +100MB (в худшем случае)

Best Practices

✅ Делай индекс на:

• PRIMARY KEY (автоматически)
• FOREIGN KEY (часто нужен для JOIN)
• Поля в WHERE условиях
• Поля в ORDER BY
• Составные индексы для частых комбинаций

❌ Избегай индексов на:

• Логических полях (boolean) — мало уникальных значений
• VARCHAR с LIKE '%...%' — нужен FULL-TEXT INDEX
• Полях с очень большим объёмом данных без нужды

Оптимизация запроса

// ❌ Медленно: нет индекса
public List<User> findActiveUsers() {
    return em.createQuery(
        "SELECT u FROM User u WHERE u.status = 'ACTIVE'",
        User.class
    ).getResultList();  // Full table scan!
}

// ✅ Быстро: есть индекс на status
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.status = ?1")
    @Index(name = "idx_status", columnList = "status")  // Добавь индекс
    List<User> findByStatus(Status status);
}

// ✅ Ещё лучше: составной индекс
@Query("SELECT u FROM User u WHERE u.status = ?1 AND u.createdAt > ?2")
List<User> findActiveUsersCreatedAfter(Status status, LocalDateTime date);
// Индекс: (status, created_at)

Мониторинг медленных запросов

-- PostgreSQL: slow query log
log_min_duration_statement = 1000  -- Логировать запросы > 1000ms

-- MySQL: slow query log
long_query_time = 1
log_queries_not_using_indexes = ON

Вывод

• ✅ Индексы ускоряют чтение в 10-1000 раз
• ✅ B-Tree индекс: O(log n) вместо O(n)
• ✅ Стоимость: медленнее запись, больше память
• ✅ Используй EXPLAIN для анализа
• ✅ Индексируй поля в WHERE, ORDER BY, JOIN
• ⚠️ Не переиндексируй — каждый индекс замедляет INSERT/UPDATE