Должны ли первичные ключи всегда назначаться как кластерный индекс
У меня есть таблица SQLServer, в которой хранятся сведения о сотрудниках, идентификатор столбца имеет тип GUID, а столбец EmployeeNumber типа INT. Большую часть времени я буду иметь дело с EmployeeNumber при выполнении объединений и выборе критериев.
Мой вопрос заключается в том, имеет ли смысл назначать PrimaryKey столбцу идентификатора, а ClusteredIndex - EmployeeNumber?
6 ответов
Идеальный ключ кластеризованного индекса:
- последовательный
- Выборочный (без дуплейсов, уникален для каждой записи)
- узкий
- Используется в запросах
В общем, очень плохая идея использовать GUID в качестве ключа кластеризованного индекса, так как это приводит к фрагментации mucho при добавлении строк.
РЕДАКТИРОВАТЬ ДЛЯ ЯСНОСТИ:
PK и Clustered key действительно являются отдельными понятиями. Ваш PK не должен быть вашим ключом кластерного индекса.
В практических приложениях, исходя из моего собственного опыта, та же область, в которой находится ваш ПК, должна быть / будет вашим кластерным ключом, поскольку она соответствует тем же критериям, которые перечислены выше.
Да, возможно иметь некластеризованный первичный ключ, и возможно иметь кластеризованный ключ, который полностью не связан с первичным ключом. По умолчанию первичные ключи также становятся ключами кластерного индекса, но это не является обязательным требованием.
Первичный ключ - это логическая концепция: это ключ, используемый в вашей модели данных для ссылки на сущности.
Ключ кластеризованного индекса - это физическая концепция: это порядок, в котором вы хотите, чтобы строки хранились на диске.
Выбор другого кластеризованного ключа определяется различными факторами, такими как ширина ключа, когда вам нужен более узкий кластеризованный ключ, чем первичный ключ (потому что кластеризованный ключ реплицируется в каждом некластеризованном индексе. Или поддержка частых сканирований диапазона (обычно в временные ряды), когда к данным часто обращаются с такими запросами, как date between '20100101' and '20100201'
(ключ кластерного индекса на date
было бы уместно).
Эта тема уже обсуждалась здесь до тошноты, см. Также В какой столбец следует кластеризовать индекс?,
Во-первых, я должен сказать, что у меня есть опасения по поводу выбора GUID в качестве первичного ключа для этой таблицы. Я придерживаюсь мнения, что EmployeeNumber, вероятно, будет лучшим выбором, и что-то уникальное в сотруднике, естественно, будет лучше, чем SSN (или ATIN), который работодатели должны в любом случае получить на законных основаниях (по крайней мере, в США).
Оставляя это в стороне, вы никогда не должны основывать кластерный индекс на столбце GUID. Кластерный индекс определяет физический порядок строк в таблице. Так как значения GUID (в теории) абсолютно случайны, каждая новая строка попадает в случайное место. Это очень плохо для производительности. Существует нечто, называемое "последовательными" GUID, но я бы посчитал это хаком.
Использование закрытого индекса для чего-то другого, кроме первичного ключа, улучшит производительность запроса SELECT, который будет использовать этот индекс.
Но вы потеряете производительность по запросу UPDATE, потому что в большинстве сценариев они используют первичный ключ для поиска конкретной строки, которую вы хотите обновить.
Запрос CREATE также может привести к потере производительности, поскольку при добавлении новой строки в середину индекса большое количество строк необходимо переместить (физически). Это не произойдет с первичным ключом с приращением, так как новая запись всегда будет добавляться в конце и не будет перемещать другие строки.
Если вы не знаете, какая операция требует наибольшей производительности, я рекомендую оставить кластеризованный индекс на первичном ключе и использовать некластеризованный индекс для общих критериев поиска.
Поскольку EmployeeNumber уникален, я бы сделал его PK. В SQL Server PK часто является кластерным индексом.
Присоединения по GUID просто ужасны. @JNK отвечает на это хорошо.
Кластерные индексы приводят к физическому сохранению данных в таком порядке. По этой причине при тестировании диапазонов последовательных строк очень помогают кластерные индексы.
GUID - это действительно плохие кластерные индексы, поскольку их порядок не соответствует разумному порядку. Столбцы Int Identity не намного лучше, если не помогает порядок ввода (например, самые последние наймы)
Поскольку вы, вероятно, не ищете диапазоны сотрудников, вероятно, не имеет большого значения, какой именно кластеризованный индекс, если только вы не можете сегментировать блоки сотрудников, которые вас часто не интересуют (например, даты увольнения)