Как выбрать поля для составного индекса при медленном запросе с фильтрацией по нескольким полям?

Для оптимизации запроса с фильтрацией по нескольким полям создается составной (композитный) индекс. Правила формирования:

Основные принципы:

1. Порядок полей критичен. Индекс (A, B, C) эффективен для запросов с условиями WHERE A = ? AND B = ?, но не для WHERE B = ?.
2. Первым идет поле с наивысшей селективностью (наибольшее количество уникальных значений).
3. Затем добавляются остальные поля из WHERE в порядке их использования.
4. Если есть ORDER BY или GROUP BY, поля из этих clauses добавляются в конец индекса.

Пример для PostgreSQL/MySQL:

-- Медленный запрос:
SELECT id, total FROM orders
WHERE user_id = 100 
  AND status = 'shipped' 
  AND created_at > '2024-01-01'
ORDER BY created_at DESC;

-- Оптимальный составной индекс:
CREATE INDEX idx_orders_user_status_created 
ON orders(user_id, status, created_at DESC);
-- user_id (высокая селективность), затем status, затем created_at для сортировки.

Важные нюансы:

• Индекс не будет использоваться для условий с LIKE '%prefix' (но будет для LIKE 'prefix%').
• Условия с IS NULL или функциями (UPPER(column)) обычно обходят индекс.
• Каждый дополнительный индекс замедляет операции INSERT, UPDATE, DELETE и увеличивает объем хранилища.