Какой индекс стоит использовать для ускорения запроса поиска в БД?

Какой индекс использовать для ускорения поиска

Индексы — это структуры данных для ускорения поиска, но они имеют разные применения. Выбор индекса зависит от типа запроса.

Типы индексов

1. B-Tree индекс (стандартный)

// Применяется для:
// - Равенства: WHERE email = 'john@example.com'
// - Диапазонов: WHERE age > 18 AND age < 65
// - LIKE: WHERE name LIKE 'John%'

@Entity
public class User {
    @Id
    private UUID id;
    
    @Column(columnDefinition = "VARCHAR(255) COLLATE utf8_unicode_ci")
    @Index(name = "idx_email")  // B-Tree индекс
    private String email;
    
    @Column(name = "age")
    @Index(name = "idx_age")     // B-Tree индекс
    private Integer age;
}

// SQL
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_age ON users(age);

// Запросы, которые используют индекс
SELECT * FROM users WHERE email = 'john@example.com';  // Быстро
SELECT * FROM users WHERE age > 18;                    // Быстро
SELECT * FROM users WHERE age BETWEEN 18 AND 65;       // Быстро

Как работает B-Tree:

Без индекса (Seq Scan): O(n) — проверяет каждую строку
С индексом B-Tree: O(log n) — бинарный поиск

Таблица: 1M записей
- Seq Scan: ~1M операций
- B-Tree: ~20 операций (log₂(1M) ≈ 20)

2. Hash индекс

// Применяется только для точного совпадения
// WHERE email = 'john@example.com'  ✓ Быстро
// WHERE email LIKE 'john%'           ✗ Не поддерживается
// WHERE age > 18                      ✗ Не поддерживается

// PostgreSQL
CREATE INDEX idx_email ON users USING HASH (email);

// MySQL — не поддерживает явно (InnoDB использует адаптивно)

Когда использовать: только для точного поиска по одному полю.

3. Composite индекс (индекс по нескольким полям)

// Быстрые запросы по нескольким колонкам
// WHERE country = 'US' AND age > 18

@Entity
public class User {
    @Id
    private UUID id;
    
    private String country;
    private Integer age;
    
    @ManyToOne
    private User manager;
}

// SQL
CREATE INDEX idx_country_age ON users(country, age);

// Этот индекс ускоряет:
SELECT * FROM users WHERE country = 'US';              // OK (левая часть)
SELECT * FROM users WHERE country = 'US' AND age > 18; // Очень хорошо
SELECT * FROM users WHERE country = 'US' AND age = 25; // Очень хорошо

// Но НЕ ускоряет:
SELECT * FROM users WHERE age > 18;                    // Нет (не левая часть)
SELECT * FROM users WHERE age = 25 AND country = 'US'; // Может быть медленнее

Правило: Left-Most Prefix

Индекс (country, age, name)

Ускоряет:
✓ WHERE country = 'US'
✓ WHERE country = 'US' AND age = 25
✓ WHERE country = 'US' AND age = 25 AND name = 'John'

НЕ ускоряет:
✗ WHERE age = 25
✗ WHERE name = 'John'
✗ WHERE age = 25 AND name = 'John'

4. Partial индекс

// Индекс только для активных пользователей
// Экономит место, ускоряет поиск

@Entity
public class User {
    private String email;
    private Boolean active;
}

// SQL
CREATE INDEX idx_email_active ON users(email) 
WHERE active = true;

// Очень быстро (ищет только в активных)
SELECT * FROM users WHERE email = 'john@example.com' AND active = true;

// Не использует индекс
SELECT * FROM users WHERE email = 'john@example.com' AND active = false;

5. Full-Text индекс (для поиска текста)

// Для полнотекстового поиска

@Entity
public class Article {
    private UUID id;
    
    @Column(columnDefinition = "TEXT")
    @Index(name = "idx_content_fulltext")  // Full-Text индекс
    private String content;
}

// SQL
CREATE FULLTEXT INDEX idx_content_fulltext ON articles(content);

// Быстрый поиск текста
SELECT * FROM articles WHERE MATCH(content) AGAINST('java programming' IN BOOLEAN MODE);

// Вместо медленного LIKE
SELECT * FROM articles WHERE content LIKE '%java%' AND content LIKE '%programming%';

6. JSON индекс (PostgreSQL)

// Для поиска в JSON данных

@Entity
public class Document {
    @Id
    private UUID id;
    
    @Type(JsonType.class)  // Hibernate Types
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> metadata;
}

// SQL PostgreSQL
CREATE INDEX idx_metadata_version ON documents USING GIN (metadata);

// Быстрый поиск
SELECT * FROM documents WHERE metadata->>'version' = '1.0';

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

1. Проанализируй queries перед созданием

// EXPLAIN в PostgreSQL/MySQL показывает план выполнения
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

// Вывод без индекса:
// Seq Scan on users  (cost=0.00..5000.00 rows=1)

// Вывод с индексом:
// Index Scan using idx_email on users  (cost=0.29..8.30 rows=1)

2. Используй индексы для WHERE условий

// WHERE — самые частые запросы
@Query("SELECT u FROM User u WHERE u.email = :email")
User findByEmail(@Param("email") String email);
// Нужен индекс на email

@Query("SELECT u FROM User u WHERE u.createdAt > :date ORDER BY u.createdAt DESC")
List<User> findRecentUsers(@Param("date") LocalDateTime date);
// Нужен индекс на createdAt

3. Используй индексы для JOIN условий

-- Без индекса на user_id: медленно
SELECT u.*, p.* 
FROM users u 
JOIN posts p ON u.id = p.user_id;

-- Нужны индексы
CREATE INDEX idx_posts_user_id ON posts(user_id);

4. Используй индексы для ORDER BY

-- Без индекса: Seq Scan + Sort (медленно)
SELECT * FROM users ORDER BY created_at DESC;

-- С индексом: Index Scan (быстро)
CREATE INDEX idx_created_at_desc ON users(created_at DESC);
SELECT * FROM users ORDER BY created_at DESC;

5. Будь осторожен с индексами на текстовых полях

// Плохо: индекс на VARCHAR(500)
@Entity
public class Article {
    @Column(length = 500)
    @Index(name = "idx_description")  // ❌ Тяжело
    private String description;  // Занимает много места
}

// Хорошо: только для начала текста
SELECT * FROM articles WHERE description LIKE 'Java%';  // Используется индекс
SELECT * FROM articles WHERE description LIKE '%Java%'; // Не используется индекс

// Лучше: Full-Text индекс для поиска текста
CREATE FULLTEXT INDEX idx_description ON articles(description);
SELECT * FROM articles WHERE MATCH(description) AGAINST('Java');

Стратегия индексирования

1. Индекс для запроса поиска

// Запрос
@Query("SELECT u FROM User u WHERE u.email = :email AND u.active = true")
User findActiveUserByEmail(@Param("email") String email);

// Индекс
CREATE INDEX idx_email_active ON users(email, active);

2. Индекс для сортировки с WHERE

// Запрос
SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;

// Оптимальный индекс: WHERE поле, потом ORDER BY
CREATE INDEX idx_age_created ON users(age, created_at DESC);

3. Covering индекс (покрывающий индекс)

// Содержит все поля для запроса (не нужно доступ к таблице)
@Query("SELECT u.id, u.email FROM User u WHERE u.email = :email")

// Covering индекс
CREATE INDEX idx_email_covering ON users(email, id);
// Или в PostgreSQL
CREATE INDEX idx_email_covering ON users(email) INCLUDE (id);

Когда НЕ использовать индексы

// 1. Таблица очень маленькая (< 1000 строк)
List<User> findAll();  // Seq Scan быстрее

// 2. Колонка с малым количеством уникальных значений
SELECT * FROM users WHERE active = true;  // 90% активны → нет смысла в индексе

// 3. Колонка часто изменяется
private LocalDateTime lastLogin;  // UPDATE требует переиндексирования

Итоги

1. B-Tree — стандартный, для равенства и диапазонов
2. Hash — только для точного совпадения
3. Composite — для нескольких полей одновременно
4. Partial — экономия места и скорости
5. Full-Text — для поиска текста
6. Covering — содержит все поля запроса
7. Анализируй queries перед созданием индексов
8. Не переусложняй — слишком много индексов замедляют INSERT/UPDATE