Чем отличаются кластеризованный и некластеризованный индексы и когда применять каждый из них?

Разница между кластеризованным и некластеризованным индексом

• индексы — структуры данных, ускоряющие поиск в БД
• кластеризованный индекс задаёт физический порядок строк в таблице
• для таблицы можно создать только один кластеризованный индекс
• в кластеризованном индексе данные размещаются в порядке ключа
• некластеризованный индекс представляет собой отдельную структуру со ссылками на строки
• для одной таблицы допускается много некластеризованных индексов
• кластеризованный индекс особенно эффективен для диапазонных запросов
• некластеризованный индекс подходит для поиска по уникальным или неупорядоченным полям
• таблица не становится кластеризованной без явного указания этого свойства или настройки по умолчанию
• верно выбранный индекс повышает скорость запросов и общую производительность в разных сценариях использования

Подробный ответ

Основной ответ

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

Ключевые моменты

• При поиске по диапазону кластеризованный индекс обычно работает быстрее, поскольку строки уже расположены последовательно. Поэтому он удобен для запросов с сортировкой или фильтрацией по интервалу, например по дате или ID.
• Некластеризованный индекс позволяет быстро найти нужные ключи, однако для получения всей строки может потребоваться дополнительный переход к данным (lookup).
• Кластеризованные индексы целесообразно выбирать для колонок с высокой селективностью и частыми диапазонными запросами. Некластеризованные индексы подходят для отдельных столбцов с высокой селективностью, по которым регулярно выполняется точечный поиск.

Практический контекст

В PostgreSQL кластеризованный индекс создаётся на основе индекса типа B-tree и может быть связан с таблицей с помощью CLUSTER команды, однако изменение физического порядка выполняется вручную. В SQL Server кластеризованный индекс определяет основную физическую организацию таблицы, тогда как некластеризованный индекс создаётся отдельно и хранится как самостоятельная структура. В реальных проектах кластеризованный индекс часто создают по первичному ключу, а для часто фильтруемых колонок добавляют некластеризованные индексы, ускоряющие точечные запросы и join-операции.