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

Если очень коротко отвечать на этот вопрос в режиме блица на собеседовании, то:

Плюсы:

1) Поиск строк в базе ускорится в некоторых случаях. Если у вас есть индекс на колонке таблицы и вы делаете SELECT с точным значением, которые вы хотите найти или с диапазоном значений, то такой запрос будет работать быстрее. Особенно если значение уникальное или очень редкое. Если же у вас у всех строк в этой колонке одинаковое значение, то ускорения работы можно не ожидать. Ускорение поиска может также отразится не только на ускорении некоторых SELECT запросов, но и DELETE и UPDATE, если в них есть фильтры на проиндексированные колонки. Т.к. прежде, чем изменить строку, ее надо сначала найти.
2) SELECT с ORDER BY и GROUP BY в некоторых случаях будет работать быстрее .
3) Поддержание уникальных значений в колонке. Если вы хотите, чтобы в какой-то колонке было уникальное значение, то unique index для этого подойдет идеально. Например, это может быть необходимо для primary key (в этом случае для него автоматически будет создан unique index).

Минусы:
1) Для non-clustered индексов потребуется больше места на диске для хранения индекса. Кроме самой таблицы нам нужно также хранить еще и индекс. Про разницу clustered и non-clustered индексов можно тут посмотреть: Difference between Clustered and Non-clustered index
2) INSERT, UPDATE, DELETE будут работать медленнее для проиндексированных колонок, т.к. кроме изменения самой таблицы нужно изменить и индекс.
3) При использовании clustered индексов UPDATE будет работать медленно, если вы делаете UPDATE проиндексированного поля. Т.к. для этого нужно строку удалить и заинсертить в новое место. Но на практике это встречается редко. Т.к. они в основном используются для primary ключей, которые не меняются.