Какие бы спроектировал индексы для поиска сотрудников, принятых в течение недели по заданному условию

Какие бы спроектировал индексы для поиска сотрудников, принятых в течение недели

Таблица сотрудников

CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    department VARCHAR(100),
    position VARCHAR(100),
    hire_date DATE,
    status VARCHAR(50),
    salary DECIMAL(10,2),
    is_active BOOLEAN
);

1. Базовый поиск по дате найма

SELECT * FROM employees 
WHERE hire_date BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE
ORDER BY hire_date DESC;

CREATE INDEX idx_hire_date ON employees(hire_date DESC);

2. Поиск по отделу и дате найма

SELECT * FROM employees 
WHERE department = IT 
  AND hire_date >= CURRENT_DATE - 7
  AND is_active = true
ORDER BY hire_date DESC;

CREATE INDEX idx_dept_hire_date_active ON employees(
    department,           -- Фильтр по отделению
    hire_date DESC,       -- Range и сортировка
    is_active            -- Фильтр
);

3. Covering Index (оптимизация)

CREATE INDEX idx_employees_covering ON employees(
    department,
    hire_date DESC
) INCLUDE (
    id, name, email, position, salary
);

-- Index-only scan (без доступа к таблице)

4. Full Text поиск по имени

CREATE INDEX idx_name_fulltext ON employees USING GIN(
    to_tsvector(english, name)
);

5. Рекомендуемая стратегия

-- Основной индекс
CREATE INDEX idx_hire_date ON employees(hire_date DESC);

-- Составной индекс
CREATE INDEX idx_dept_hire_date ON employees(
    department, hire_date DESC
) INCLUDE (name, email, position);

-- По статусу
CREATE INDEX idx_status_hire_date ON employees(
    is_active, hire_date DESC
);

-- Unique на email
CREATE UNIQUE INDEX idx_email ON employees(email);

-- Full text
CREATE INDEX idx_name_fulltext ON employees 
USING GIN(to_tsvector(english, name));

6. Leftmost Prefix Rule

Для индекса (department, position, hire_date):

-- Использует индекс
WHERE department = IT AND position = Manager;

-- Использует индекс (partial)
WHERE department = IT;

-- НЕ использует индекс (пропущен first column)
WHERE position = Manager;

7. Spring Data JPA

@Entity
@Table(indexes = {
    @Index(name = "idx_hire_date", columnList = "hire_date DESC"),
    @Index(name = "idx_dept_hire", columnList = "department, hire_date DESC"),
    @Index(name = "idx_email", columnList = "email", unique = true)
})
public class Employee {
    @Id private Long id;
    @Column(unique = true) private String email;
    private String name;
    private String department;
    private LocalDate hireDate;
    private boolean isActive;
}

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    List<Employee> findByDepartmentAndHireDateBetweenOrderByHireDateDesc(
        String department, LocalDate start, LocalDate end);
}

Проверка индексов

EXPLAIN ANALYZE
SELECT * FROM employees 
WHERE department = IT
  AND hire_date >= CURRENT_DATE - 7
ORDER BY hire_date DESC;

-- Хорошо: "Index Scan" или "Index Only Scan"
-- Плохо: "Seq Scan" (без индекса)

Когда НЕ индексировать

❌ Избегай:

• Булевы столбцы (low selectivity)
• Часто обновляемые столбцы
• BLOB/TEXT поля (используй Full Text)
• Столбцы с 30%+ NULL

✅ Индексируй:

• WHERE фильтры
• JOIN условия
• ORDER BY столбцы
• Уникальные значения