Почему при использовании кластерного индекса приходится дополнительно обращаться к таблице для получения данных

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

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

Как отвечать

Кластерный индекс это и есть таблица. В листьях его дерева лежат сами строки целиком, упорядоченные по ключу индекса. Дойдя по кластерному индексу до листа, движок уже держит все колонки строки, и второе обращение не нужно. Поэтому кластерный индекс у таблицы ровно один: физический порядок может быть только один.

Лишнее обращение порождает некластерный индекс. В его листе лежит ключ индекса плюс ссылка на строку: значение кластерного ключа, если таблица кластеризована, или идентификатор строки, если это куча. Запросили колонку, которой в индексе нет, значит на каждую найденную строку будет отдельный переход в кластерный индекс или в кучу. Это случайный доступ, и на выборке в тысячи строк оптимизатор часто решает, что дешевле прочитать таблицу целиком, и переключается на скан.

Лечится покрывающим индексом: недостающие колонки кладут в INCLUDE, и они оказываются в листе.

CREATE NONCLUSTERED INDEX ix_orders_customer
    ON orders (customer_id) INCLUDE (status, total);

Уместно добавить, что в InnoDB картина та же: таблица физически хранится по первичному ключу, вторичный индекс хранит значение этого ключа, и промах по покрытию превращается во второй спуск по дереву.

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

Принимают посылку на веру и уверенно объясняют то, чего не происходит. Интервьюер обычно именно это и проверяет, а вежливое «тут наоборот» ценится выше гладкого пересказа.

Убирают лукапы, дописывая колонки в ключ индекса вместо INCLUDE. Ключ участвует в сортировке и попадает на все уровни дерева, поэтому индекс раздувается, а выигрыш тот же самый.

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