Должен ли составной первичный ключ кластеризоваться в SQL Server?

Рассмотрим эту примерную таблицу (при условии, что SQL Server 2005):

create table product_bill_of_materials
(
    parent_product_id int not null,
    child_product_id int not null,
    quantity int not null
)

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

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

Ответ 1

Как уже было сказано несколькими другими, это зависит от того, как вы будете обращаться к таблице. Имейте в виду, что любые РСУБД там должны иметь возможность использовать кластеризованный индекс для поиска по одному столбцу, пока этот столбец появится первым. Например, если ваш кластерный индекс включен (parent_id, child_id), вам не нужен другой отдельный индекс (parent_id).

Лучшим вариантом может быть кластеризованный индекс (parent_id, child_id), который также является первичным ключом, с отдельным некластеризованным индексом (child_id).

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

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

В вашем случае я, скорее всего, просто поместил бы кластерный PK (parent_id, child_id), чтобы начать с него, а затем добавить некластеризованный индекс, только если увидел проблему с производительностью, которая ему поможет.

Ответ 2

"То, что вы запрашиваете чаще всего", не обязательно является лучшей причиной для выбора индекса для кластеризации. Самое главное, что вы запрашиваете для получения нескольких строк. Кластеризация - это стратегия, подходящая для того, чтобы эффективно получать несколько строк в наименьшем количестве чтений на диске.

Лучшим примером является история продаж для клиента.

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

Первичный ключ, OTOH, может быть суррогатным ключом или SalesId, но уникальным значением в любом случае. Если бы это было сгруппировано, это было бы бесполезно по сравнению с обычным уникальным индексом.

EDIT: Позвольте взять эту конкретную таблицу для обсуждения - она покажет еще более тонкости.

"Естественный" первичный ключ - это, вероятно, parentid + childid. Но в какой последовательности? Parentid + childid не более уникален, чем childid + parentid. Для целей кластеризации, упорядочение является более подходящим? Можно предположить, что это должен быть родитель + ребенок, так как мы хотим спросить: "Для данного предмета, каковы его составляющие"? Но разве это вряд ли захочет пойти другим путем и спросить: "Для какого-то конкретного существа, из каких предметов это компонент?".

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

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

Даже для такого ясного примера, в принципе, лучше оставить decidintg по поводу других индексов, пока вы не сможете проверить их реалистичными данными (очевидно, до производства); но спрашивать здесь о спекуляциях бессмысленно. Тестирование всегда даст вам правильный ответ.

Забудьте о том, чтобы беспокоиться о замедлении вставок, пока у вас не возникнет проблема (что в большинстве случаев никогда не произойдет), и может проверить, чтобы дать возможность отказаться от полезных индексов для измеримого преимущества.

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

Кстати, я ожидаю, что это закончится с PK на parentid + childid; неуникальный индекс для childid; и первый сгруппированный. Если вы предпочитаете суррогатную PK, вам по-прежнему нужен уникальный индекс для parentid + childid, кластеризованный. Кластеризация суррогатного ключа вряд ли будет оптимальной.

Ответ 3

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

Ответ 4

Поскольку вы говорите: "Я рассматриваю составной первичный ключ", возможно, настало время передумать. Я использовал много составных клавиш, и я продолжаю искать причины, которых я не хотел. Возможно, другие со мной не согласятся.

Я согласен с ответом Митчела, кластер продолжает все, что вы будете запрашивать чаще всего.

Ответ 5

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

Что касается кластера, вы должны подумать о том, как лучше всего использовать таблицу. Если ваша таблица подвержена множеству запросов диапазона (WHERE col1 IS BETWEEN a AND b), тогда класть таблицу таким образом, чтобы запросы диапазона уже были настроены по порядку на диске. В SQL Server иногда мы получаем кластер бесплатно с ПК, и мы забываем о том, что лучше всего сгруппировать для начала.

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

Отличный вопрос.