Как навесить индекс

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

Индекс — это структура данных, которая ускоряет поиск и сортировку записей в БД. Рассмотрю как создавать и использовать индексы в PostgreSQL и как их описывать в Java приложениях.

1. Создание индекса в SQL (Миграция)

Простой индекс по одному столбцу

-- Миграция: 0001_create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Индекс по email для быстрого поиска пользователя
CREATE INDEX idx_users_email ON users(email);

-- Индекс по created_at для сортировки по дате
CREATE INDEX idx_users_created_at ON users(created_at DESC);

Составной индекс (по нескольким столбцам)

-- Таблица заказов
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Составной индекс: часто ищем заказы по user_id и status
CREATE INDEX idx_orders_user_id_status ON orders(user_id, status);

-- Индекс для диапазонного поиска
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Индекс с условием (Partial Index)

-- Индекс только по активным заказам (для быстрого поиска)
CREATE INDEX idx_orders_active ON orders(user_id)
    WHERE status = ACTIVE;

-- Индекс по удаленным записям (для мягкого удаления)
CREATE INDEX idx_users_deleted ON users(deleted_at)
    WHERE deleted_at IS NOT NULL;

Уникальный индекс

-- Гарантирует уникальность, может быть несколько NULL
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Полнотекстовый индекс (для поиска по тексту)
CREATE INDEX idx_articles_body_fts ON articles
    USING GIN (to_tsvector(russian, body));

2. Описание индексов в JPA/Hibernate

Простой индекс

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false, unique = true)
    private String email;
    
    @Column(nullable = false)
    @Index(name = "idx_users_username")  // Индекс на поле
    private String username;
    
    @Column(nullable = false)
    private LocalDateTime createdAt;
    
    // Getters, setters
}

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

import jakarta.persistence.*;

@Entity
@Table(
    name = "orders",
    indexes = {
        @Index(
            name = "idx_orders_user_status",
            columnList = "user_id,status",
            unique = false
        ),
        @Index(
            name = "idx_orders_created_at",
            columnList = "created_at DESC"
        )
    }
)
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;
    
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private OrderStatus status;
    
    @Column(nullable = false)
    private LocalDateTime createdAt;
    
    @Column(nullable = false)
    private BigDecimal amount;
    
    // Getters, setters
}

public enum OrderStatus {
    ACTIVE, COMPLETED, CANCELLED
}

3. Миграции индексов (Goose)

Рекомендуемый подход для production:

-- migrations/0001_create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Индексы в отдельной миграции
-- migrations/0002_add_users_indexes.sql
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email 
    ON users(email);

CREATE INDEX CONCURRENTLY idx_users_username 
    ON users(username);

CREATE INDEX CONCURRENTLY idx_users_created_at 
    ON users(created_at DESC);

-- migrations/0003_create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL DEFAULT ACTIVE,
    amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- migrations/0004_add_orders_indexes.sql
CREATE INDEX CONCURRENTLY idx_orders_user_id 
    ON orders(user_id);

CREATE INDEX CONCURRENTLY idx_orders_user_status 
    ON orders(user_id, status);

CREATE INDEX CONCURRENTLY idx_orders_created_at 
    ON orders(created_at DESC);

-- Индекс по активным заказам
CREATE INDEX CONCURRENTLY idx_orders_active 
    ON orders(user_id) WHERE status = ACTIVE;

4. Практические примеры в коде

Пример 1: Поиск пользователя по email

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Запрос использует индекс idx_users_email
    Optional<User> findByEmail(String email);
}

SQL, который будет выполнен:

-- PostgreSQL использует индекс для быстрого поиска
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = ?;

-- Index Scan using idx_users_email
-- Index Cond: (email = user@example.com)

Пример 2: Поиск заказов с фильтрацией

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    // Использует составной индекс idx_orders_user_status
    List<Order> findByUserIdAndStatus(Long userId, OrderStatus status);
    
    // Использует индекс idx_orders_created_at
    List<Order> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);
}

Пример 3: Сложный запрос

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    // Использует idx_orders_user_status и idx_orders_created_at
    @Query("""n        SELECT o FROM Order o 
        WHERE o.user.id = :userId 
        AND o.status = :status 
        AND o.createdAt >= :startDate 
        ORDER BY o.createdAt DESC
        """)
    List<Order> findUserOrdersFiltered(
        @Param("userId") Long userId,
        @Param("status") OrderStatus status,
        @Param("startDate") LocalDateTime startDate
    );
}

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

EXPLAIN ANALYZE в PostgreSQL

-- Проверяем, использует ли запрос индекс
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = user@example.com;

-- Результат должен показать:
-- Index Scan using idx_users_email on users (cost=...)
-- Index Cond: (email = user@example.com)

Запрос для проверки индексов

-- Посмотреть все индексы таблицы
SELECT indexname, tablename 
FROM pg_indexes 
WHERE tablename = users;

-- Посмотреть неиспользуемые индексы
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as "scans"
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Посмотреть размер индекса
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelname)) AS size
FROM pg_indexes
JOIN pg_class ON pg_class.relname = indexname
WHERE tablename = orders
ORDER BY pg_relation_size(indexrelid) DESC;

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

7. Best Practices

Правильно

-- ✅ Индексируем колонки, по которым часто ищем
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- ✅ Используем CONCURRENTLY для больших таблиц
CREATE INDEX CONCURRENTLY idx_articles_slug 
    ON articles(slug);

-- ✅ Составной индекс в порядке запроса
CREATE INDEX idx_orders_user_date 
    ON orders(user_id, created_at DESC);

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

Неправильно

-- ❌ Индекс на редко используемое поле
CREATE INDEX idx_user_middle_name ON users(middle_name);

-- ❌ Слишком много индексов на одной таблице
CREATE INDEX idx1 ON orders(user_id);
CREATE INDEX idx2 ON orders(status);
CREATE INDEX idx3 ON orders(created_at);
CREATE INDEX idx4 ON orders(amount);
-- Лучше: один составной индекс

-- ❌ Индекс на большой колонке текста
CREATE INDEX idx_description ON products(full_description);
-- Используй GIST или полнотекстовый поиск вместо этого

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

✅ Нужны индексы:
- Поля WHERE (в 99% случаев)
- Поля JOIN (внешние ключи)
- Поля ORDER BY (если таблица большая)
- Поля GROUP BY (если таблица большая)
- UNIQUE ограничения

❌ Не нужны индексы:
- Маленькие таблицы (< 1000 записей)
- Поля с низкой селективностью (boolean, статусы)
- Часто обновляемые поля
- Редко используемые поля

9. Мониторинг индексов

@Service
public class DatabaseHealthCheck {
    
    @Scheduled(fixedRate = 3600000)  // Каждый час
    public void checkUnusedIndexes() {
        // Находим неиспользуемые индексы
        // Их нужно удалить, чтобы не замедлять UPDATE/INSERT
    }
    
    @Scheduled(fixedRate = 3600000)
    public void checkIndexFragmentation() {
        // Для больших индексов проверяем фрагментацию
        // REINDEX может улучшить производительность
    }
}

Выводы

1. Индексируй WHERE, JOIN, ORDER BY — самые важные места
2. Составные индексы часто эффективнее отдельных
3. Проверяй с EXPLAIN ANALYZE — видишь, использует ли БД индекс
4. Не переусложняй — каждый индекс замедляет INSERT/UPDATE
5. Удаляй неиспользуемые — они только занимают место
6. Для больших таблиц используй CONCURRENTLY при создании
7. Регулярно мониторь — неиспользуемые индексы нужно удалять