Краткий ответ

В СУБД, где некластеризованный индекс использует кластеризованный ключ как локатор строки, широкий кластеризованный ключ фактически добавляется к листовым записям каждого такого индекса. Это увеличивает их размер, глубину и стоимость чтения, записи и обслуживания, даже если запросы напрямую не используют все поля кластеризованного ключа.

Исторический контекст

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

Такой подход позволяет сохранять связь некластеризованного индекса со строкой при перемещении данных внутри кластеризованной структуры. Цена удобства — зависимость размера всех вторичных индексов от ширины кластеризованного ключа.

Постановка проблемы

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

В результате увеличиваются объём индексов, число страниц, требуемая память для кэша и количество операций ввода-вывода. Вставки, удаления и изменения ключевых значений также становятся дороже, потому что затрагивают более объёмные индексные записи.

Подробное решение

В SQL Server кластеризованный ключ включается в некластеризованные индексы как локатор строки. Если нужные столбцы уже являются ключевыми столбцами некластеризованного индекса, дублирования для них не требуется; остальные компоненты кластеризованного ключа хранятся на листовом уровне.

Если кластеризованный индекс не уникален, SQL Server добавляет внутренний идентификатор уникальности, чтобы различать строки с одинаковым ключом. Это тоже увеличивает размер локатора.

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

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

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

CREATE TABLE dbo.Orders (
    OrderID bigint NOT NULL,
    TenantCode varchar(40) NOT NULL,
    ExternalNumber varchar(80) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID),
    CONSTRAINT UQ_Orders_External UNIQUE (TenantCode, ExternalNumber)
);

CREATE INDEX IX_Orders_Tenant ON dbo.Orders (TenantCode);

Здесь узкий OrderID используется как кластеризованный локатор, а бизнес-уникальность вынесена в отдельное ограничение. Индекс по TenantCode не наследует широкий составной бизнес-ключ как локатор строки.

Ситуация из практики

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

Рассматривались три варианта: оставить схему и увеличить память, сократить число вторичных индексов или заменить кластеризованный ключ. Первый вариант скрывал причину и повышал стоимость инфраструктуры, второй ухудшал планы отдельных запросов, а замена требовала миграции и перестроения индексов.

Выбрали узкий неизменяемый числовой кластеризованный ключ, сохранив уникальное ограничение на естественный идентификатор. После перестроения индексов уменьшились их объём и стоимость обслуживания; при этом проверка бизнес-уникальности продолжила выполняться отдельным индексом.

Что кандидаты часто упускают

1. Обязательно ли каждому некластеризованному индексу хранить весь кластеризованный ключ?

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

2. Почему широкий кластеризованный ключ может ухудшить даже поиск по узкому некластеризованному индексу?

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

3. Всегда ли узкий числовой кластеризованный ключ лучше естественного ключа?

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

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