Какие виды индексов существуют

Что проверяют

Понимаете ли вы, что индекс это не «ускоритель на всякий случай», а структура данных со своей ценой. Спрашивают почти всегда парой: какие бывают и когда индекс не нужен.

Как отвечать

Перечислять стоит не все возможные типы, а те, что реально встречаются, и сразу с назначением:

• B-tree: тип по умолчанию почти во всех базах. Держит порядок, поэтому работает и на равенстве, и на диапазонах (>, BETWEEN), и на ORDER BY;
• хэш: только точное равенство, зато поиск дешевле. Диапазон и сортировку не умеет;
• составной (композитный): несколько колонок сразу. Работает слева направо: индекс по (city, age) помогает запросу по city и по паре, но не запросу по одному age;
• уникальный: не столько про скорость, сколько про ограничение целостности, дубли просто не запишутся;
• полнотекстовый: поиск по словам и формам внутри текста, обычный LIKE '%слово%' индексом не покрывается;
• пространственный (GiST, R-tree): координаты, расстояния, пересечения областей;
• частичный: индекс не на всю таблицу, а на строки по условию, например только where is_active = true. Занимает меньше и обслуживается дешевле.

Дальше назовите цену. Каждый индекс это копия данных, которую база обязана обновлять при каждом INSERT, UPDATE и DELETE. Отсюда правило: индексы ускоряют чтение и замедляют запись, а на таблице с интенсивной вставкой лишний индекс дороже, чем выигрыш от него.

Где спотыкаются

Говорят «повесим индекс на каждую колонку». На таблице с большой записью это заметная просадка, а планировщик всё равно возьмёт один или два.

Забывают про селективность: индекс по колонке со значениями «да/нет» базе почти бесполезен, она быстрее прочитает таблицу целиком.

Не знают, что функция над колонкой убивает индекс: WHERE lower(email) = ... обычный индекс по email не использует, нужен индекс по выражению.