Что такое составной индекс (composite index) в базах данных?

Question

Что такое составной индекс (composite index) в базах данных?

Тип вопроса: Базы данных Вероятность: 10% Категория: Java Разработчик

Ответ

Составной индекс (composite index, multi-column index) — это индекс, созданный по двум или более столбцам таблицы. Он оптимизирует запросы, которые используют эти столбцы в условиях WHERE, ORDER BY или JOIN.

Ключевые особенности:

Порядок столбцов критически важен. Индекс (A, B, C) эффективен для запросов с условиями на (A), (A, B) или (A, B, C), но не для запросов только по (B) или (C).
Сокращает количество индексов. Один составной индекс может заменить несколько отдельных индексов для частых комбинаций условий.
Поддерживает сортировку. Может эффективно использоваться для ORDER BY по первому или нескольким первым столбцам индекса.

Пример создания в SQL:

-- Создание составного индекса по столбцам `last_name` и `first_name`
CREATE INDEX idx_name ON employees (last_name, first_name);

Пример эффективного запроса:

-- Этот запрос сможет использовать индекс полностью
SELECT * FROM employees 
WHERE last_name = 'Smith' AND first_name = 'John';

-- Этот запрос использует только первую часть индекса (last_name)
SELECT * FROM employees 
WHERE last_name = 'Smith' 
ORDER BY first_name;

Практическое правило: Столбцы в индексе следует располагать в порядке от наиболее селективного (с наибольшим разнообразием значений) к наименее селективному, а также в соответствии с частыми паттернами запросов.

Ответ 18+ 🔞

Давай разберём эту тему про составные индексы, а то многие думают, что это какая-то хуйня непонятная, а на деле — просто огонь, если правильно приложить.

Представь себе, что индекс — это алфавитный указатель в книге. Обычный индекс — это как искать только по фамилии. А составной — это когда у тебя указатель сразу по фамилии и имени. То есть, открываешь на «Иванов», а там уже внутри по алфавиту все «Александры», «Борисы» и «Владимиры» разложены. Красота, да?

Вот в чём соль, ёпта:

Порядок столбцов — это пиздец как важно. Если ты создал индекс (фамилия, имя), то он сработает, когда ты ищешь по фамилии или по фамилии и имени вместе. А вот если ты пришёл и такой: «Дай мне всех Васей» — то этот индекс тебе нихуя не поможет. База посмотрит на этот индекс, увидит, что «Вася» — это второй столбец, и пойдёт читать всю таблицу целиком, потому что искать Васю в указателе, отсортированном по фамилиям, — это ебать какой неэффективный пиздец.
Экономия, мать его, места и времени. Вместо того чтобы городить три отдельных индекса на фамилию, на имя и на (фамилия, имя), можно сделать один умный составной. База будет меньше тратить времени на обслуживание этих индексов при вставке новых данных.
Сортировку за тебя сделает. Нужно выбрать всех Ивановых и отсортировать по имени? Да хуле там, они в индексе уже в таком порядке и лежат! База просто пройдётся по нужной ветке индекса и сразу отдаст данные в правильном порядке. Скорость — овердохуища.

Вот как это в коде выглядит, тут всё просто:

-- Делаем наш крутой указатель по фамилии и имени
CREATE INDEX idx_name ON employees (last_name, first_name);

А вот как этим пользоваться, чтобы не облажаться:

-- Запрос 1: Идеально! Использует весь индекс. База сразу тыкает в нужное место.
SELECT * FROM employees 
WHERE last_name = 'Smith' AND first_name = 'John';

-- Запрос 2: Тоже отлично! Использует первую часть индекса (фамилию), а имена внутри уже отсортированы.
SELECT * FROM employees 
WHERE last_name = 'Smith' 
ORDER BY first_name;

Главное практическое правило, чтобы не вышло как всегда: Ставь столбцы в таком порядке: сначала тот, по которому чаще всего ищешь, и который имеет больше всего уникальных значений (например, фамилия), а потом уже все остальные (имя, отчество). Думай, как твои запросы обычно выглядят, и подгоняй индекс под них, а не наоборот. Иначе получится мартышлюшка с молотком: вроде инструмент есть, а толку — хуй с горы.