Что такое индексы в базах данных и в чем разница кластерного и некластерного индексов

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

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

Как отвечать

Индекс это структура, ускоряющая поиск ценой места и замедления записи. Основной тип это B-дерево.

Кластерный индекс определяет физический порядок строк в таблице. Он один: у таблицы не может быть двух разных физических порядков. Сами данные лежат в его листьях, поэтому поиск по такому ключу не требует второго обращения, а диапазонный запрос читает подряд идущие страницы. В SQL Server кластерный индекс создаётся по первичному ключу по умолчанию, в MySQL с InnoDB тоже.

Некластерный индекс это отдельная структура, в листьях которой лежит ключ и ссылка на строку. Их может быть много. После поиска в индексе часто нужен ещё один переход к самой строке (в SQL Server это lookup), и если таких переходов много, планировщик предпочтёт просмотр таблицы.

Отсюда практика: кластерный ключ должен быть узким и возрастающим. Широкий ключ увеличивает каждый некластерный индекс, потому что ссылка на строку это и есть кластерный ключ. Случайный ключ вроде GUID вызывает расщепление страниц и фрагментацию при вставке.

Отдельно стоит сказать, что в PostgreSQL кластерных индексов в этом смысле нет: все индексы вторичные, а команда CLUSTER это разовая физическая перестройка, которая не поддерживается автоматически.

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

Считают, что кластерных индексов может быть несколько.

Не знают, что некластерный индекс хранит кластерный ключ и раздувается вместе с ним.

Берут GUID кластерным ключом и получают фрагментацию.

Переносят термин на PostgreSQL, где он значит другое.