Индексы замедляют или ускоряют чтение и запись в базе данных

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

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

Как отвечать

Базовая часть: индекс это отдельная структура, чаще всего B-tree, которую база обновляет при каждой записи. INSERT добавляет запись во все индексы таблицы, поэтому пять индексов означают шесть записей вместо одной. Дальше уточнения на примере PostgreSQL, раз в Go-сервисах он встречается чаще всего:

• UPDATE и MVCC: обновление создаёт новую версию строки, и новые записи появляются во всех индексах. Исключение составляет HOT-обновление: если не менялась ни одна индексированная колонка и на странице есть место, индексы не трогаются. Лишний индекс на часто обновляемой колонке лишает таблицу HOT;
• запись тоже читает: UPDATE и DELETE с WHERE сначала ищут строки, и без индекса это полный скан. Индекс на колонке внешнего ключа ускоряет удаление родительской записи;
• чтение не всегда быстрее: при низкой селективности планировщик выберет Seq Scan, случайные чтения по индексу обошлись бы дороже;
• диагностика: EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements для тяжёлых запросов, idx_scan = 0 в pg_stat_user_indexes для неиспользуемых индексов.

Для Go стоит упомянуть массовую загрузку: CopyFrom из pgx работает через протокол COPY и быстрее пачки INSERT, а при заливке миллионов строк индексы иногда удаляют и строят заново. Новый индекс на проде создают через CREATE INDEX CONCURRENTLY: он не блокирует запись, но не работает внутри транзакции, поэтому в goose такую миграцию помечают -- +goose NO TRANSACTION.

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

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

Забывают, что UPDATE и DELETE без подходящего индекса сами становятся медленными, и предлагают убрать индексы ради скорости записи.

Создают индекс обычным CREATE INDEX на большой таблице в рабочее время и блокируют запись на всё время построения.