Стоит ли создавать индексы на все поля сразу или только по мере необходимости
Что проверяют
Понимаете ли вы, что лишний индекс это не нейтральный балласт, а постоянная плата, и обоснуете ли выбор данными, а не интуицией. Заодно смотрят, знаете ли вы про составные индексы: их наличие снимает половину вопроса.
Как отвечать
Индекс на каждое поле не делают, потому что запросы почти никогда не фильтруют по одной колонке. Работает другое правило: индекс строится под запрос, а не под колонку.
Объясните про составной индекс и его левый префикс. Индекс по нескольким колонкам обслуживает запросы, где условия идут по первым колонкам подряд. Значит один составной закрывает сразу несколько запросов и заменяет два или три одиночных, а отдельный индекс по второй колонке составного обычно не нужен вовсе.
Что стоит завести сразу, не дожидаясь жалоб:
- индексы под внешние ключи, иначе удаление родительской строки превращается в сканирование дочерней таблицы, а местами ещё и в долгую блокировку;
- уникальные индексы под бизнес-ключи, тут на первом месте целостность, а скорость приятный побочный эффект;
- индекс под очевидный ключевой доступ, по которому приложение ходит на каждый запрос.
Остальное по мере надобности, но надобность определяется планами запросов и накопленной статистикой обращений к индексам, которую СУБД ведёт сама. Оттуда же берутся кандидаты на удаление: ни разу не использованные и полностью перекрытые другими.
Назовите и цену лишних: место на диске, замедление записи и то, что планировщик перебирает больше вариантов при разборе каждого запроса.
Где спотыкаются
Считают, что индекс ускоряет любой запрос, и вешают его на колонку, по которой ищут шаблоном с ведущим символом подстановки. Такой поиск обычным индексом не закрывается, и он остаётся мёртвым грузом.
Второе: добавляют индекс на нагруженной таблице обычной командой и получают блокировку записи на всё время построения. Там, где есть построение без блокировки, берут его, и всё равно делают это в тихие часы.
Третье: заводят индекс и больше к нему не возвращаются. Запросы меняются, фичу выключили, а индекс остался и продолжает дорожать вместе с ростом таблицы.