В чем разница между составным и обычным индексом в БД?

Разница между составным и обычным индексом в БД

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

Обычный индекс (Simple Index)

Обычный индекс создается на одном столбце таблицы. Это самый простой и часто используемый тип индекса.

-- Создание обычного индекса
CREATE INDEX idx_user_email ON users(email);

-- Или через таблицу создания
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100),
    created_at TIMESTAMP
);

Когда используется:

• Поиск по одному полю: SELECT * FROM users WHERE email = 'test@example.com'
• Сортировка: SELECT * FROM users ORDER BY username
• WHERE условия для одного столбца

public class UserRepository {
    // Использует idx_user_email индекс
    public Optional<User> findByEmail(String email) {
        return userRepository.findByEmail(email);
    }
    
    // Использует индекс на id (PRIMARY KEY)
    public Optional<User> findById(Long id) {
        return userRepository.findById(id);
    }
}

Плюсы:

• Простота — легко создавать и управлять
• Быстро для одного столбца
• Занимает меньше памяти

Минусы:

• Неэффективен для множественных условий
• Не оптимален для сложных запросов

Составной индекс (Composite/Multi-column Index)

Составной индекс создается на нескольких столбцах одновременно. Порядок столбцов критичен.

-- Создание составного индекса
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- Или в определении таблицы
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    status VARCHAR(50),
    created_at TIMESTAMP,
    email VARCHAR(255),
    UNIQUE KEY idx_email_status (email, status)
);

Когда используется:

• Поиск с несколькими условиями WHERE
• Сложные JOIN операции
• Покрывающие индексы (covering index)

public class UserRepository {
    // Отлично использует idx_user_status_created индекс
    public List<User> findActiveUsersSinceDate(String status, LocalDateTime date) {
        return userRepository.findByStatusAndCreatedAtGreaterThan(status, date);
    }
    
    // SQL запрос:
    // SELECT * FROM users WHERE status = 'ACTIVE' AND created_at > '2024-01-01'
}

Практический пример: Обычный vs Составной

Сценарий: Нужно найти активных пользователей, зарегистрированных после определенной даты

// Неоптимально - два отдельных индекса
public class UserService {
    // Индекс 1: idx_status ON users(status)
    // Индекс 2: idx_created_at ON users(created_at)
    
    public List<User> getActiveUsersSinceDate(LocalDateTime date) {
        // СУБОПТИМАЛЬНО: SQL может использовать либо idx_status, либо idx_created_at
        // Обычно выбирает более селективный индекс, но все равно медленнее
        return userRepository.findByStatusAndCreatedAtGreaterThan("ACTIVE", date);
    }
}

// ОПТИМАЛЬНО - составной индекс
public class OptimizedUserService {
    // Составной индекс: idx_status_created ON users(status, created_at)
    
    public List<User> getActiveUsersSinceDate(LocalDateTime date) {
        // ОПТИМАЛЬНО: индекс полностью покрывает оба условия WHERE
        // База данных может использовать индекс целиком
        return userRepository.findByStatusAndCreatedAtGreaterThan("ACTIVE", date);
    }
}

Правило порядка столбцов (Column Order Matters)

Порядок столбцов в составном индексе КРИТИЧЕН. Используется правило "Equality, Range, Sort" (ERS).

-- Хороший порядок: status (equality) -> created_at (range) -> name (sort)
CREATE INDEX idx_good_order ON users(status, created_at, name);

-- Плохой порядок: создаст проблемы
CREATE INDEX idx_bad_order ON users(created_at, status, name);

public class QueryOptimization {
    // Запрос 1: Хорошо использует idx_good_order
    // SELECT * FROM users WHERE status = 'ACTIVE' 
    //   AND created_at > '2024-01-01' 
    //   ORDER BY name
    public List<User> query1() {
        return userRepository
            .findByStatusAndCreatedAtGreaterThanOrderByName("ACTIVE", date);
    }
    
    // Запрос 2: Плохо использует idx_bad_order (created_at не может быть ключом диапазона)
    // SELECT * FROM users WHERE created_at > '2024-01-01' 
    //   AND status = 'ACTIVE'
    public List<User> query2() {
        return userRepository
            .findByCreatedAtGreaterThanAndStatus(date, "ACTIVE");
    }
}

Покрывающий индекс (Covering Index)

Составной индекс, который содержит ВСЕ столбцы для конкретного запроса.

public class CoveringIndexExample {
    // Запрос нужен только id, email, status
    // SELECT id, email, status FROM users WHERE status = 'ACTIVE'
    
    // Обычный индекс требует двух обращений:
    // 1. Найти в индексе idx_status
    // 2. Вернуться к таблице за всеми столбцами
    
    // Покрывающий индекс (covering index):
    // CREATE INDEX idx_covering ON users(status, email, id);
    // Результат: база обращается ТОЛЬКО к индексу, не к таблице!
    // Это называется "Index-only scan"
}

Сравнение в таблице

EXPLAIN/EXPLAIN ANALYZE

Как проверить, использует ли база ваш индекс:

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM users WHERE status = 'ACTIVE' AND created_at > '2024-01-01';

-- Результат должен содержать:
-- Index Scan using idx_user_status_created on users  (good!)
-- Seq Scan on users (bad - полное сканирование таблицы)

public class JpaQueryLogging {
    // Логирование SQL в Spring Boot (application.properties)
    // spring.jpa.show-sql=true
    // spring.jpa.properties.hibernate.format_sql=true
    // logging.level.org.hibernate.SQL=DEBUG
    // logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
}

Best Practices

1. Используй составные индексы для часто выполняемых многоусловных запросов
2. Соблюдай порядок ERS: Equality → Range → Sort
3. Избегай избыточных индексов — они замедляют INSERT/UPDATE/DELETE
4. Проверяй EXPLAIN — убедись, что индекс используется
5. Покрывающие индексы — отличный способ ускорить SELECT запросы
6. Монитори медленные запросы — логируй долгие запросы и добавляй индексы

Пример полной оптимизации

@Entity
@Table(
    name = "orders",
    indexes = {
        // Обычный индекс для поиска по пользователю
        @Index(name = "idx_user_id", columnList = "user_id"),
        
        // Составной индекс для сложного поиска
        @Index(name = "idx_status_created", columnList = "status,created_at"),
        
        // Покрывающий индекс
        @Index(name = "idx_covering", columnList = "user_id,status,total_price")
    }
)
public class Order {
    @Id
    private Long id;
    
    @Column(name = "user_id")
    private Long userId;
    
    @Column(name = "status")
    private String status;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
    
    @Column(name = "total_price")
    private BigDecimal totalPrice;
}