SQL-Ex blog

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

Суррогатный ключ

Суррогатный ключ - это генерируемое системой (может быть GUID, последовательность, уникальный идентификатор и т.д.) значение, не имеющее смысла в терминах предметной области и которое используется для однозначной идентификации записи в таблице. Сам ключ может быть составлен из одного или большего числа столбцов (т.е. составной ключ). На следующем рисунке показан пример таблицы с суррогатным ключом (столбец AddressID) и другими данными. Заметим, что сам ключ не имеет бизнес-смысла . Он просто последовательное целое, служащее уникальным ключом.

Естественный ключ

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

Сравнение естественного и суррогатного ключа при проектировании базы данных

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

За естественный ключ

• Значения ключа имеют смысл в предметной области и могут использоваться в качестве поискового ключа в запросах к таблице.


• Столбцы и индекс на первичном ключе уже существуют, поэтому не нужно дополнительного пространства на диске для дополнительного столбца/индекса, которые бы могли использоваться для суррогатного ключа.


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

Против естественного ключа

• Может потребоваться поменять/переделать ключ, если изменятся бизнес-требования. Например, если вы использовали SSN для сотрудников, как в примере выше, и ваша компания выходит за пределы США, то не все сотрудники могут иметь SSN, поэтому появляется необходимость в новом ключе для таблиц вашей базы данных.


• Сложней обслуживать, если ключ состоит их нескольких столбцов. Со стороны приложений много проще работать с единственным ключевым столбцом.


• Ухудшение производительности, поскольку значение ключа обычно больше по величине и/или состоит из нескольких столбцов. БОльшие ключи потребуют больше операций ввода/вывода при вставке/обновлении данных, а также при выборке.


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


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

За суррогатный ключ

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


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


• Лучше производительность, поскольку ключ меньших размеров. Требуется меньшее число дисковых операций ввода/вывода при доступе к индексам на базе единственного столбца с точки зрения оптимизации.


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


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

Против суррогатного ключа

• Дополнительный столбец (столбцы) на суррогатный ключ потребуют дополнительного места на диске.


• Дополнительный столбец (столбцы) на суррогатный ключ потребуют лишних операций ввода/вывода при вставке/обновлении данных.


• Потребуется больше соединений таблиц с дочерними таблицами, поскольку сами ключи не имеют смысла.


• Можно получить дубликаты естественного ключа в таблице, если отсутствуют другие ограничения уникальности на естественных ключах.


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


• Ключевые значения не имеют отношения к данным, поэтому технически схема нарушает требования 3НФ (т.е. нормализацию).


• Значение суррогатного ключа не может использоваться в качестве поискового ключа.


• Требуются различные реализации для разных платформ баз данных. Например, применение столбцов identity в SQL Server несколько отличается от их применения в Postgres или DB2.

Заключение

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