По какому принципу выбирать столбцы для индексирования?

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

Понимаете ли вы, что индекс подбирается под запрос, а не под колонку. Вопрос любят задавать бэкендерам, потому что через ORM индекс не виден, и связь между кодом и планом запроса теряется.

Как отвечать

Отталкиваться надо от реальных запросов и планов, а не от списка колонок:

• кандидаты видны в where, в условиях соединения, в order by и group by. Отдельно внешние ключи: во многих СУБД индекс под них сам не создаётся, и удаление родительской строки уходит в полное сканирование;
• решает селективность: индекс помогает, когда условие отсекает малую долю таблицы. На колонке вроде статуса выигрыш даёт частичный индекс с условием (например, только по активным строкам) или включение колонки в составной;
• в составном индексе важен порядок: работает левый префикс, впереди ставятся равенства, затем диапазон и сортировка. Три отдельных индекса не заменяют один правильный составной;
• покрывающий индекс отвечает на запрос, не заглядывая в таблицу;
• функция над колонкой убивает обычный индекс, под неё нужен индекс по выражению;
• цена: каждый индекс замедляет вставку и обновление, занимает место и требует обслуживания. Неиспользуемые индексы надо находить по статистике и удалять.

Хорошо звучит фраза про проверку: план до правки, план после, замер на объёме, похожем на боевой.

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

Индексируют колонку, а не сценарий. Навешивают отдельный индекс на каждое поле из формы поиска, а оптимизатор всё равно возьмёт один, и запрос по двум условиям остаётся медленным.

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

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