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

Индекс в БД

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

Как работает индекс

Без индекса база данных использует полный скан таблицы (Full Table Scan): проверяет каждую строку, чтобы найти совпадение. С индексом база может быстро найти нужные строки.

Без индекса:
┌──────────────────────────────────────┐
│ ID | Name    | Email              │
├──────────────────────────────────────┤
│ 1  │ Alice   │ alice@example.com   │ ← Full scan
│ 2  │ Bob     │ bob@example.com     │ ← проверяем каждую
│ 3  │ Charlie │ charlie@example.com │ ← строку
│ 4  │ David   │ david@example.com   │ ← O(n) сложность
└──────────────────────────────────────┘

С индексом (B-tree):
┌─────────────────┐
│    Index        │
│                 │
│    [Alice]───→ Row 1
│    [Bob]───→ Row 2
│    [Charlie]─→ Row 3
│    [David]───→ Row 4
└─────────────────┘
O(log n) сложность

Создание индекса

// SQL для создания индекса
CREATE INDEX idx_email ON users(email);

// Уникальный индекс
CREATE UNIQUE INDEX idx_username ON users(username);

// Составной индекс (несколько колонок)
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

// Индекс с сортировкой
CREATE INDEX idx_salary_desc ON employees(salary DESC);

Использование индекса в Java (JPA)

@Entity
@Table(name = "users")
public class User {
    @Id
    private Long id;
    
    @Column(name = "email", unique = true)  // Создаёт индекс
    private String email;
    
    @Column(name = "username")
    @Index(name = "idx_username")  // Явный индекс
    private String username;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

Быстрый поиск с индексом

// Поиск по индексированному полю (БЫСТРО)
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByEmail(String email);  // Использует idx_email
}

// Java код
Optional<User> user = userRepository.findByEmail("alice@example.com");  // O(log n)

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

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

Самый распространённый тип. Хорошо для:

• Точного поиска: WHERE id = 5
• Диапазонного поиска: WHERE salary > 50000
• Сортировки: ORDER BY name

// Эффективно
List<Employee> employees = employeeRepository.findBySalaryGreaterThan(50000);  // Использует индекс

2. Hash индекс

Подходит только для точного поиска (=), не для диапазонов.

-- Hash индекс
CREATE INDEX idx_hash ON users USING HASH(email);

-- Быстро для этого:
SELECT * FROM users WHERE email = 'alice@example.com';

-- Но не работает для этого:
SELECT * FROM users WHERE email LIKE 'alice%';  -- Hash индекс не поможет

3. Full-text индекс

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

@Entity
public class Article {
    @Id
    private Long id;
    
    @Column(columnDefinition = "TEXT")
    private String content;
    
    // Full-text индекс в БД
    // CREATE FULLTEXT INDEX idx_content ON articles(content);
}

// Поиск
List<Article> results = entityManager.createNativeQuery(
    "SELECT * FROM articles WHERE MATCH(content) AGAINST(? IN BOOLEAN MODE)"
).setParameter(1, "java").getResultList();

4. Составной индекс

Индекс по нескольким колонкам. Порядок важен!

@Entity
@Table(indexes = {
    @Index(name = "idx_dept_salary", columnList = "department_id, salary")
})
public class Employee {
    @Id
    private Long id;
    
    private Long departmentId;
    private BigDecimal salary;
}

// Быстро (используется индекс)
List<Employee> employees = entityManager.createQuery(
    "SELECT e FROM Employee e WHERE e.departmentId = :deptId AND e.salary > :minSalary"
).setParameter("deptId", 1L)
 .setParameter("minSalary", new BigDecimal("50000"))
 .getResultList();

Плюсы и минусы индексов

Плюсы:

• Значительно ускоряют SELECT запросы
• Улучшают производительность WHERE, JOIN, ORDER BY
• Могут ускорить DELETE/UPDATE (нужно найти строки)

Минусы:

• Замедляют INSERT, UPDATE, DELETE (нужно обновить индекс)
• Занимают дополнительное место на диске
• Требуют поддержки (дефрагментация, пересчёт статистики)

Когда создавать индекс

// ✅ Создайте индекс
// - Часто используется в WHERE
// - Большие таблицы (> 10000 строк)
// - Используется в JOIN
// - Сортировка по полю (ORDER BY)

// ❌ Не создавайте индекс
// - Малые таблицы (< 1000 строк)
// - Булевы поля (мало уникальных значений)
// - Колонки с NULL (часто имеют низкую селективность)
// - Редко используемые колонки

Анализ производительности

// EXPLAIN показывает план выполнения
// PostgreSQL
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
/*
Seq Scan on users  (cost=0.00..35.50 rows=1 width=32)  -- Без индекса
Filter: (email = 'alice@example.com')
*/

EXPLAIN SELECT * FROM users WHERE id = 5;
/*
Index Scan using users_pkey on users  (cost=0.29..8.30 rows=1 width=32)  -- С индексом
Index Cond: (id = 5)
*/

Статистика и поддержка индексов

-- Пересчёт статистики (улучшает планы запросов)
ANALYZE table_name;

-- Дефрагментация индекса (PostgreSQL)
REINDEX INDEX idx_email;

-- Удаление неиспользуемых индексов
DROP INDEX idx_old_index;

Практический пример: оптимизация медленного запроса

// Медленный запрос (без индекса)
String sql = "SELECT * FROM orders o WHERE o.customer_id = ? AND o.status = ? AND o.created_at > ?";
// Execution time: 5 seconds (Full Table Scan на таблице с миллионами строк)

// Решение: создать составной индекс
CREATE INDEX idx_orders_lookup ON orders(customer_id, status, created_at DESC);

// Повторный запрос с индексом
// Execution time: 50ms (1000x ускорение!)

Ограничения индексов

Индекс не используется, если:

-- LIKE с подстановкой в начале (не использует индекс)
SELECT * FROM users WHERE email LIKE '%example.com';  -- ❌

-- Функции на индексируемом поле
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';  -- ❌

-- OR без индексов для всех условий
SELECT * FROM users WHERE email = 'a@example.com' OR non_indexed_field = 'value';  -- ⚠️

-- Автоматическое преобразование типов
SELECT * FROM users WHERE id = '123';  -- ❌ (string vs integer)

Индексы — критическая часть оптимизации базы данных для Java-приложений.