Каковы преимущества и недостатки индексов в базе данных

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

Понимаете ли вы, что индекс это компромисс, и умеете ли назвать цену, а не только выгоду.

Как отвечать

Преимущества. Индекс это отдельная упорядоченная структура, обычно B-дерево, поэтому поиск идёт за логарифм вместо полного сканирования. На таблице в миллионы строк это три или четыре обращения к страницам вместо чтения всей таблицы. Ускоряется не только поиск по равенству:

• диапазоны, потому что листья дерева связаны и читаются подряд;
• сортировка: ORDER BY по индексированной колонке не требует отдельной сортировки;
• соединения: индекс на внешнем ключе превращает перебор в точечный поиск;
• уникальность: ограничение реализовано именно индексом;
• чтение только по индексу, когда все нужные колонки в него входят.

Недостатки, и это половина ответа:

• замедление записи: каждый INSERT, UPDATE и DELETE обновляет ещё и все индексы таблицы. Десять индексов означают десятикратную дополнительную работу на вставке;
• место на диске и в памяти: индексы конкурируют с данными за буферный кэш, и лишние вытесняют полезные;
• стоимость поддержки: индексы раздуваются, требуют перестроения, а их создание на большой таблице без параллельного режима блокирует запись;
• нагрузка на планировщик: он перебирает варианты, и большое число индексов усложняет выбор, иногда приводя к худшему плану;
• бесполезность в части случаев: функция над колонкой (WHERE lower(email) = ...), шаблон с ведущим процентом, низкая селективность. Такой индекс только тратит ресурсы на записи и не используется при чтении.

Практический вывод: индексы ставят под конкретные запросы, а не «на всякий случай». Неиспользуемые находят по системной статистике (pg_stat_user_indexes, где число сканирований нулевое) и удаляют.

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

Называют только преимущества.

Вешают индекс на каждую колонку.

Не знают, что составной индекс работает по первым колонкам и порядок в нём важен.

Никогда не проверяют, используются ли индексы вообще.