Составные индексы в базах данных: когда и как их следует использовать? Опишите ключевые принципы.

Составной (или композитный) индекс — это индекс, построенный по нескольким колонкам таблицы. Он используется для ускорения запросов, которые фильтруют или сортируют данные по этим колонкам одновременно.

Ключевые принципы использования составных индексов:

1. Порядок колонок имеет решающее значение (Принцип левого префикса)

   • Индекс, созданный для колонок (col_A, col_B, col_C), может быть эффективно использован для запросов, фильтрующих по:
     • col_A
     • col_A и col_B
     • col_A, col_B и col_C
   • Однако он будет бесполезен или малоэффективен для запросов, фильтрующих только по col_B или col_C.

2. Селективность колонок

   • Селективность — это количество уникальных значений в колонке по отношению к общему числу строк. Чем выше селективность, тем эффективнее индекс.
   • При создании составного индекса первыми следует ставить наиболее селективные колонки. Например, в таблице пользователей user_id (уникален для каждой строки) гораздо селективнее, чем status (может иметь всего несколько значений: 'active', 'inactive', 'banned').
   • Правильно: CREATE INDEX ON users (user_id, status);
   • Неправильно: CREATE INDEX ON users (status, user_id);

3. Покрытие запроса индексом (Index-Only Scan)

   • Это самая эффективная ситуация. Если все колонки, запрашиваемые в SELECT, WHERE и ORDER BY, присутствуют в индексе, базе данных не нужно обращаться к самой таблице. Она берет все данные прямо из индекса.
   • Пример: Для запроса SELECT department, created_at FROM users WHERE company_id = ?; идеальным будет индекс (company_id, department, created_at).

4. Влияние на сортировку (ORDER BY)

   • Составной индекс может избавить БД от необходимости выполнять операцию сортировки, если порядок колонок в ORDER BY совпадает с порядком колонок в индексе.
   • Индекс (company_id, created_at DESC) ускорит ... WHERE company_id = ? ORDER BY created_at DESC;.

5. Учитывайте компромиссы

   • Индексы — это не бесплатно. Они занимают место на диске и, что важнее, замедляют операции записи (INSERT, UPDATE, DELETE), так как при каждом изменении данных БД должна обновить и индекс.
   • Не создавайте индексы бездумно. Каждый индекс должен быть оправдан частыми и медленными SELECT-запросами.

Пример для PostgreSQL:

-- Таблица заказов
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    status VARCHAR(20),
    created_at TIMESTAMPTZ
);

-- Мы часто ищем активные заказы конкретного клиента, отсортированные по дате
-- SELECT order_id, created_at FROM orders 
-- WHERE customer_id = 123 AND status = 'active' 
-- ORDER BY created_at DESC;

-- Создаем оптимальный составной индекс:
-- 1. customer_id - наиболее селективная колонка для фильтрации
-- 2. status - вторая колонка для фильтрации
-- 3. created_at - для покрытия сортировки и, возможно, выборки
CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC);