Что такое условный (частичный) индекс в SQL и как его создать?

Условный индекс (Partial/Filtered Index) — это индекс, построенный только для подмножества строк таблицы, удовлетворяющих заданному условию WHERE. Это уменьшает размер индекса и ускоряет запросы, которые используют это же условие.

Создание в PostgreSQL:

-- Индекс только для активных заказов
CREATE INDEX idx_orders_active ON orders (customer_id, created_at)
WHERE status = 'ACTIVE';

-- Индекс для не удаленных записей
CREATE INDEX idx_users_not_deleted ON users (email)
WHERE deleted_at IS NULL;

Особенности и best practices:

• Производительность: Индекс меньше по размеру, быстрее строится и обновляется.
• Использование: Оптимизатор СУБД сможет использовать индекс только если условие в запросе логически влечет условие индекса. Например, индекс WHERE status = 'ACTIVE' не будет использован для запроса WHERE status IN ('ACTIVE', 'PENDING').
• Поддержка СУБД:
  • PostgreSQL, SQLite: Полная поддержка.
  • MySQL (8.0+): Поддерживаются как Functional Indexes, которые можно использовать для той же цели.

    -- Пример в MySQL 8.0
    CREATE INDEX idx_status ON orders ((1)) WHERE status = 'ACTIVE';

  • SQL Server: Использует Filtered Indexes.
• В JPA: Прямого создания через аннотации нет. Условные индексы создаются с помощью нативных SQL-скриптов в инструментах миграции (Flyway, Liquibase).