Что такое индексы в базах данных и зачем они нужны

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

Понимаете ли вы, что индекс это компромисс: быстрее чтение, дороже запись и место. Дальше почти всегда спрашивают, когда индекс не сработает.

Как отвечать

Индекс это отдельная структура, которая хранит значения колонки в упорядоченном виде вместе со ссылками на строки. Без него база читает всю таблицу, с ним находит нужное за логарифмическое число шагов.

Основной тип это B-дерево: подходит для равенства, диапазонов и сортировки. Есть и другие: хеш (только равенство), GIN (полнотекстовый поиск, массивы, JSON), частичный (индексируется часть строк по условию), составной (несколько колонок).

Важное про составной индекс: порядок колонок решает всё. Индекс по (company, created_at) помогает запросу по компании и по паре «компания и дата», но не помогает запросу по одной дате. Это правило левого префикса.

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

Полезно назвать инструмент проверки: EXPLAIN ANALYZE показывает, использовался ли индекс и сколько строк прочитано.

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

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

Думают, что индекс всегда ускоряет. На маленькой таблице или при выборке большой доли строк планировщик сознательно выберет полное сканирование, и это правильно.

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