Может ли индекс быть составным?

Составные индексы в базах данных

Короткий ответ

Да, может! Составной индекс (composite index, multi-column index) - это индекс по нескольким столбцам одновременно. Это один из самых важных инструментов для оптимизации производительности БД.

Что такое составной индекс?

-- Простой индекс (по одному столбцу)
CREATE INDEX idx_users_email ON users(email);

-- Составной индекс (по нескольким столбцам)
CREATE INDEX idx_users_status_created 
  ON users(status, created_at);

-- С сортировкой
CREATE INDEX idx_orders_user_date 
  ON orders(user_id, order_date DESC);

Реальный пример

-- Таблица с заказами
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20),
    created_at TIMESTAMP,
    amount DECIMAL
);

-- СОСТАВНОЙ ИНДЕКС
CREATE INDEX idx_orders_user_status 
  ON orders(user_id, status);

-- Этот запрос будет ОЧЕНЬ быстрым благодаря индексу
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'COMPLETED';

-- Этот запрос тоже будет быстрым (используется часть индекса)
SELECT * FROM orders 
WHERE user_id = 123;

-- Этот запрос НЕ будет использовать индекс (status - не первая колонка)
SELECT * FROM orders 
WHERE status = 'COMPLETED';  -- ❌ Полный scan таблицы

Правило LEFT PREFIX (очень важно!)

Индекс работает слева направо:

CREATE INDEX idx_test ON table(col1, col2, col3);

-- ✅ Работает - использует весь индекс
WHERE col1 = 'a' AND col2 = 'b' AND col3 = 'c'

-- ✅ Работает - использует col1, col2
WHERE col1 = 'a' AND col2 = 'b'

-- ✅ Работает - использует col1
WHERE col1 = 'a'

-- ❌ НЕ работает - начинает со col2 (пропустил col1)
WHERE col2 = 'b' AND col3 = 'c'

-- ❌ НЕ работает - начинает со col3
WHERE col3 = 'c'

-- ✅ Работает - col1, col2 используется, col3 с LIKE
WHERE col1 = 'a' AND col2 = 'b' AND col3 LIKE 'prefix%'

-- ❌ НЕ работает - col3 с LIKE "в середине" нарушает индекс
WHERE col1 = 'a' AND col2 = 'b' AND col3 LIKE '%middle%'

Как выбрать порядок столбцов?

Стратегия 1: Equality, Range, Sort (ESR)

-- Запрос:
SELECT * FROM users 
WHERE status = 'ACTIVE'      -- Equality
  AND age > 18              -- Range
ORDER BY created_at DESC;   -- Sort

-- Правильный порядок индекса: Equality, Range, Sort
CREATE INDEX idx_esr 
  ON users(status, age, created_at DESC);

Стратегия 2: Selectivity (избирательность)

-- Самые избирательные столбцы (меньше результатов) - первыми

CREATE INDEX idx_bad 
  ON products(category, is_active, name);
  -- category: 100 значений (избирательность 1%)
  -- is_active: 2 значения (избирательность 50%)
  -- name: уникально (избирательность 100%)
  -- ❌ ПЛОХОЙ порядок

CREATE INDEX idx_good 
  ON products(is_active, category, name);
  -- ❌ Сначала избирательнее? Нет!

-- Правильный порядок зависит от запросов
CREATE INDEX idx_correct 
  ON products(category, is_active, name);
  -- Если часто ищем по категории - OK

Примеры на Java/Hibernate

@Entity
@Table(name = "orders", indexes = {
    @Index(name = "idx_user_status", 
            columnList = "user_id,status"),
    @Index(name = "idx_created_date", 
            columnList = "created_at DESC"),
    @Index(name = "idx_user_date_amount", 
            columnList = "user_id,created_at DESC,amount")
})
public class Order {
    @Id
    @GeneratedValue
    private Long id;
    
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
    
    @Enumerated(EnumType.STRING)
    private OrderStatus status;  // PENDING, COMPLETED, CANCELLED
    
    private LocalDateTime createdAt;
    
    private BigDecimal amount;
}

// Запросы, которые будут использовать индексы
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    // Использует idx_user_status
    @Query("SELECT o FROM Order o WHERE o.user.id = ?1 AND o.status = ?2")
    List<Order> findByUserAndStatus(Long userId, OrderStatus status);
    
    // Использует idx_user_date_amount
    @Query("""n        SELECT o FROM Order o 
        WHERE o.user.id = ?1 
          AND o.createdAt >= ?2 
          AND o.createdAt <= ?3 
        ORDER BY o.createdAt DESC
    """)
    List<Order> findUserOrdersByDateRange(
        Long userId, 
        LocalDateTime start, 
        LocalDateTime end
    );
}

Когда индекс помогает/не помогает

✅ Индекс ПОМОГАЕТ

-- Поиск по точному совпадению
SELECT * FROM users WHERE email = 'user@example.com';

-- Поиск по диапазону
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Сортировка (если индекс в правильном порядке)
SELECT * FROM posts ORDER BY user_id, created_at DESC;

-- IN с небольшим количеством значений
SELECT * FROM users WHERE status IN ('ACTIVE', 'PENDING');

-- Частичные совпадения (слева)
SELECT * FROM users WHERE email LIKE 'user%';

❌ Индекс НЕ ПОМОГАЕТ

-- Функции нарушают индекс
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Нужен индекс на LOWER(email) (functional index)

-- Математические операции
SELECT * FROM orders WHERE amount * quantity > 1000;

-- Отрицание
SELECT * FROM users WHERE status != 'INACTIVE';

-- LIKE с подстановкой слева
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- OR (сложнее использовать несколько индексов)
SELECT * FROM users WHERE email = 'a' OR status = 'b';

-- Более 30% строк таблицы (БД выбирает full scan)
SELECT * FROM users WHERE status = 'ACTIVE';  -- если ACTIVE большинство

Практический пример оптимизации

// ПЛОХО: медленный запрос без индекса
@Entity
@Table(name = "users")
public class User {
    @Id private Long id;
    private String email;
    private String status;
    private LocalDateTime registeredAt;
    private BigDecimal score;
}

// Часто запрашиваем: активные пользователи, зарегистрированные в диапазоне
// SELECT * FROM users 
// WHERE status = 'ACTIVE' 
//   AND registered_at BETWEEN ? AND ?
// ORDER BY score DESC
// LIMIT 10;
// Это МЕДЛЕННО без индекса!

// ХОРОШО: добавляем составной индекс
@Entity
@Table(name = "users", indexes = {
    @Index(
        name = "idx_users_status_date_score",
        columnList = "status,registered_at,score DESC",
        unique = false
    )
})
public class User {
    // ... остальной код ...
}

// Теперь этот запрос будет БЫСТРЫМ!
// Индекс: (status, registered_at, score DESC)
// - status = 'ACTIVE' - фильтрует по первому столбцу
// - registered_at BETWEEN - работает диапазон
// - ORDER BY score DESC - индекс уже отсортирован!

EXPLAIN для отладки

-- PostgreSQL / MySQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'COMPLETED'
ORDER BY created_at DESC;

-- Результат без индекса:
-- Seq Scan on orders (cost=0.00..35000.00)
-- Filter: (user_id = 123 AND status = 'COMPLETED')
-- ❌ Полный scan 35000 операций

-- Результат с индексом:
-- Index Scan using idx_user_status on orders (cost=0.29..50.00)
-- Index Cond: (user_id = 123 AND status = 'COMPLETED')
-- ✅ Только 50 операций!

Best Practices

1. Порядок имеет значение:

   • Equality columns первыми
   • Range columns вторыми
   • Sort columns в конце

2. Выбирай нужные индексы:

   • Анализируй EXPLAIN для медленных запросов
   • Не создавай 100 индексов (замедляют INSERT/UPDATE)
   • Каждый индекс - extra storage и maintenance

3. Помни про LEFT PREFIX:

   • idx(a, b, c) помогает: WHERE a=? AND b=? AND c=?
   • idx(a, b, c) помогает: WHERE a=?
   • idx(a, b, c) НЕ помогает: WHERE b=? AND c=?

4. Мониторь индексы:

   • Неиспользуемые индексы замедляют записи
   • Периодически чистить мёртвые индексы

Вывод

Составные индексы - мощный инструмент:

• ✅ Значительно ускоряют запросы (100x в лучшем случае)
• ✅ Могут помочь с сортировкой (ORDER BY)
• ✅ Используются для фильтрации множества условий
• ❌ Замедляют INSERT, UPDATE, DELETE
• ❌ Требуют дополнительное дисковое пространство

Используй EXPLAIN, анализируй медленные запросы, и создавай индексы по мере необходимости!