Как работают кластеризованные и некластеризованные индексы и когда их использовать?

Как устроены кластеризованные и некластеризованные индексы и где они применяются?

• Кластеризованный индекс задаёт физический порядок строк таблицы на основе ключа: записи хранятся в отсортированном виде
• Из-за физического ограничения таблица может иметь только один кластеризованный индекс

Такой индекс особенно эффективен при выполнении диапазонных запросов, например BETWEEN, а также при сортировке

Некластеризованный индекс представляет собой отдельную структуру со ссылками на строки; физическая организация таблицы при этом не меняется

• На одной таблице можно создать несколько некластеризованных индексов, чтобы ускорить выборочные запросы по разным колонкам

При вставке обновлении индекс также необходимо обслуживать, и это способно снизить производительность

Применение:

• Кластеризованный индекс выбирают, если требуется быстро получать данные по ключу, выполнять сортировку или работать с диапазоном, например по PK или дате

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

При выборе стратегии важно найти баланс между числом индексов и скоростью операций записи

• Современные СУБД нередко применяют оба типа индексов, чтобы сбалансировать скорость чтения и записи

Итого: кластеризованный индекс задаёт порядок хранения таблицы, а некластеризованный формирует дополнительный путь доступа; подходящий вариант определяется шаблонами запросов и характером нагрузки.

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

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

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

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

• В реляционных БД, таких как SQL Server и MySQL InnoDB, на таблицу приходится только один кластеризованный индекс, поскольку физически отсортировать данные можно лишь по одному критерию. Он хорошо подходит для диапазонных запросов, сортировки и чтения больших объёмов расположенных последовательно данных.
• Количество некластеризованных индексов не ограничивается одним, поэтому их используют для ускорения поиска по различным колонкам без изменения физического порядка данных. Такой индекс создаёт дополнительную структуру на основе B-дерева, которая ускоряет точечные запросы и поиск по нестандартным полям.
• Операции вставки и обновления при наличии кластеризованного индекса могут выполняться несколько медленнее, поскольку системе нужно сохранять физический порядок строк, зато чтение по ключу происходит быстро. Некластеризованные индексы позволяют организовать много дополнительных путей доступа и обычно требуют меньше места, однако при запросе могут потребовать дополнительных переходов по указателям.

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

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