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

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

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

Как отвечать

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

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

Что стоит знать помимо определения:

• составной индекс работает слева направо: по (a, b) поиск только по b его не использует;
• частичный индекс покрывает подмножество строк (where is_active), занимает меньше и обслуживается дешевле;
• покрывающий индекс содержит все нужные запросу колонки, и база не ходит в саму таблицу;
• селективность решает: индекс по колонке с двумя значениями обычно бесполезен, планировщик предпочтёт последовательное чтение.

Цена: каждая вставка и обновление индексируемой колонки обновляет и индекс, а сам он занимает место. Индексы «на всякий случай» замедляют запись и ничего не ускоряют.

Проверяется всё это одним способом: EXPLAIN ANALYZE до и после.

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

Отвечают «добавить индекс» на любой вопрос про скорость, не глядя в план.

Не знают правила левого префикса и заводят лишние составные индексы.

Забывают, что функция над колонкой (lower(email)) отключает обычный индекс, для этого нужен функциональный.

Не упоминают стоимость записи и раздувание таблицы.