Как индексировать данные в столбце в БД по условию

Ответ

Индексирование по условию (Partial/Conditional Index) — это продвинутая техника оптимизации базы данных. Она позволяет создать индекс, включающий только строки, соответствующие определённому условию.

1. PostgreSQL — Filtered Index

-- Индекс только для активных пользователей
CREATE INDEX idx_active_users ON users(email)
WHERE status = "active";

-- Индекс для заказов с суммой > 1000
CREATE INDEX idx_high_orders ON orders(user_id)
WHERE total_amount > 1000;

-- Составной индекс с условием
CREATE INDEX idx_recent_deleted ON posts(author_id, created_at)
WHERE deleted_at IS NULL;

2. MySQL — Виртуальные столбцы

MySQL не имеет встроенной поддержки фильтрованных индексов:

-- Добавить столбец-флаг
ALTER TABLE users ADD COLUMN is_active_idx TINYINT 
  GENERATED ALWAYS AS (status = "active") STORED;

CREATE INDEX idx_active_users ON users(is_active_idx, email);

3. SQL Server — Filtered Index

CREATE NONCLUSTERED INDEX idx_active_products 
ON Products(CategoryID, Price)
WHERE Status = "Active";

Миграция в Goose

-- Индекс для активных пользователей
CREATE INDEX idx_users_active_email 
ON users(email) 
WHERE deleted_at IS NULL 
  AND status = "active";

-- Индекс для недавних комментариев
CREATE INDEX idx_comments_recent 
ON comments(post_id, created_at) 
WHERE deleted_at IS NULL;

-- Индекс для незавершённых заказов
CREATE INDEX idx_orders_pending_user 
ON orders(user_id, created_at) 
WHERE status = "pending" 
  AND deleted_at IS NULL;

Java и JPA

@Entity
@Table(name = "tasks")
public class Task {
    @Id
    private Long id;
    
    @Column(name = "user_id")
    private Long userId;
    
    @Column(name = "status")
    private String status;
    
    @Column(name = "priority")
    private Integer priority;
    
    @Column(name = "deleted_at")
    private LocalDateTime deletedAt;
}

@Repository
public interface TaskRepository extends JpaRepository<Task, Long> {
    @Query("SELECT t FROM Task t WHERE t.userId = :userId " +
           "AND t.status IN ("open", "in_progress") " +
           "AND t.deletedAt IS NULL " +
           "ORDER BY t.priority DESC")
    List<Task> findPendingTasks(@Param("userId") Long userId);
}

Анализ индекса

-- PostgreSQL: EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users 
WHERE email = "test@example.com" 
  AND status = "active";

Случаи использования

1. NULL значения: индексируйте только NOT NULL

CREATE INDEX idx_email_not_null ON users(email) WHERE email IS NOT NULL;

1. Активные записи: индексируйте только активные

CREATE INDEX idx_active ON users(id) WHERE is_active = true;

1. Недавние данные: индексируйте только свежие записи

CREATE INDEX idx_recent ON posts(author_id) 
WHERE created_at > NOW() - INTERVAL "30 days";

1. Мягкое удаление: индексируйте только неудалённые

CREATE INDEX idx_not_deleted ON orders(user_id) WHERE deleted_at IS NULL;

Преимущества

• Индекс меньше по размеру
• Быстрее обновляется
• Улучшает кэширование памяти
• Специфичен для частых запросов
• Экономит дисковое пространство

Недостатки

• Сложнее в обслуживании
• Не все базы данных поддерживают (MySQL)
• Требует планирования на основе паттернов запросов

Это мощный инструмент для оптимизации больших таблиц с миллионами записей.