Рубрики
Без рубрики

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

Автор оригинала: Vlad Mihalcea.

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

  • УНИКАЛЬНЫЙ
  • НЕ РАВНО НУЛЮ
  • НЕИЗМЕННЫЙ

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

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

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

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

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

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

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

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

Нечисловые ключи менее эффективны, чем числовые (целочисленные, bigint), как для индексирования, так и для объединения. Естественный ключ CHAR(17) (например, идентификационный номер транспортного средства) занимает 17 байт, а не 4 байта (32 – разрядное целое число) или 8 байт (64-разрядный бигинт).

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

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

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

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

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

Если мы хотим контролировать генерацию суррогатных ключей, мы можем использовать 128-разрядный GUID или UUID . Это упрощает пакетирование и может повысить производительность вставки, поскольку больше не требуется дополнительная обработка генерации ключей базы данных. Однако, будучи больше, чем автоматически увеличивающееся число, выбор идентификатора UUID не лишен недостатков. Кроме того, для кластеризованных индексов, которые используются по умолчанию в MySQL и SQL Server, случайно сгенерированный идентификатор потребует дополнительной балансировки индекса кластера , может работать против предварительного выделения записей индекса и может привести к раздуванию индекса.

Когда ответственность за создание идентификатора базы данных ложится на систему баз данных, существует несколько стратегий автоматического увеличения суррогатных ключей:

Оракул ПОСЛЕДОВАТЕЛЬНОСТЬ, ИДЕНТИЧНОСТЬ (Oracle 12c)
SQL Server ИДЕНТИФИКАТОР, ПОСЛЕДОВАТЕЛЬНОСТЬ (SQL Server 2012)
PostgreSQL ПОСЛЕДОВАТЕЛЬНОСТЬ, ПОСЛЕДОВАТЕЛЬНЫЙ ТИП
MySQL АВТО_ИНКРЕМЕНТ
DB2 ИДЕНТИЧНОСТЬ, ПОСЛЕДОВАТЕЛЬНОСТЬ
HSQLDB ИДЕНТИЧНОСТЬ, ПОСЛЕДОВАТЕЛЬНОСТЬ

Аспекты проектирования

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

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

И тип ИДЕНТИФИКАТОРА, и генератор ПОСЛЕДОВАТЕЛЬНОСТЕЙ определяются стандартом SQL:2003 , поэтому они стали стандартными стратегиями генератора первичных ключей.

Некоторые механизмы баз данных позволяют вам выбирать между ИДЕНТИФИКАТОРОМ и ПОСЛЕДОВАТЕЛЬНОСТЬЮ, поэтому вам нужно решить, какой из них лучше соответствует вашим текущим требованиям к схеме.

Режим гибернации отключает пакетирование вставок JDBC при использовании стратегии генератора идентификаторов.