Что такое индексы и для чего их используют?

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

Аналогия из жизни: индекс — это как алфавитный указатель в конце книги. Вместо последовательного пролистывания 500 страниц, вы находите нужное слово в указателе и переходите сразу на нужную страницу.

Как работает индекс

Без индекса СУБД выполняет полное сканирование таблицы (Sequential Scan / Full Table Scan), проверяя каждую строку. С индексом СУБД использует структуру (обычно B-tree), чтобы за O(log N) найти нужные строки.

Преимущества

• Ускорение поиска (WHERE), сортировки (ORDER BY) и соединения (JOIN)
• Обеспечение уникальности данных (UNIQUE INDEX)
• Ускорение работы с диапазонами (BETWEEN, >, <)

Недостатки

• Дополнительное дисковое пространство (индекс может быть соизмерим с самой таблицей)
• Замедление операций INSERT, UPDATE, DELETE (при каждой модификации данных обновляется и индекс)
• Необходимость обслуживания (REINDEX, VACUUM в PostgreSQL)

Когда индексы полезны

• Столбцы, часто используемые в WHERE и JOIN
• Столбцы с высокой кардинальностью (много уникальных значений)
• Столбцы, по которым выполняется сортировка
• Первичный ключ (индекс создаётся автоматически)

Когда индексы бесполезны или вредны

• Маленькие таблицы (полное сканирование быстрее)
• Столбцы с низкой кардинальностью (пол, статус true/false)
• Столбцы, которые редко используются в запросах
• Таблицы с интенсивной записью и редким чтением

На собеседовании: интервьюер хочет услышать про компромисс: индекс ускоряет чтение, но замедляет запись и занимает место. Частая ошибка — забыть про стоимость поддержания индексов при INSERT/UPDATE и считать, что индексы нужно создавать на все столбцы.