Ключи в базе данных

5.1 Введение

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

Давайте разделим проблему на части и соберём её снова. Сначала зададим вопрос: что такое «ключ»?

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

Но для начала немного теории:

Первичный ключ

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

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

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

Составной ключ

Custom Key - это комбинация атрибутов (столбцов), которая уникально идентифицирует каждую строку таблицы. Это могут быть либо все столбцы, либо несколько, либо даже один. Значения этих атрибутов не должны повторяться в строках.

Потенциальный ключ

Candidate Key - это минимальный составной ключ для отношения (таблицы). Это набор атрибутов, который удовлетворяет ряду условий.

• Неприводимость: Он не может быть укорочен и имеет минимум атрибутов.
• Уникальность: Он должен содержать уникальные значения, независимо от изменений.
• Наличие значения: Он должен иметь значение, не может быть NULL.

5.2 Любопытный случай первичных ключей

В прошлом разделе мы называли ключи просто "ключами". Обычно они называются "потенциальными ключами". Один из них называется "первичный ключ". Остальные называются "альтернативными ключами".

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

Термин «ключ» означает ключ сортировки файла, нужный для выполнения действий с файлами. Первые накопители на магнитных лентах могли считывать файлы только в одном порядке. В современном SQL не нужно заботиться о физическом представлении информации, порядок строк не важен. Однако SQL-сервер по умолчанию создаёт кластерный индекс для первичных ключей и порядок строк физически выстраивает по традиции.

Первичные ключи в большинстве баз данных представляют собой остатки прошлого и обычно предоставляют лишь представление или определение местоположения. Например, при объявлении первичного ключа в таблице PostgreSQL мы автоматически получаем ограничение NOT NULL и внешний ключ по умолчанию. Также первичные ключи являются идеальными полями для оператора JOIN.

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

5.3 Нахождение естественных ключей

Называемые «естественными» ключи обусловлены свойствами моделируемого объекта. Они интересны сами по себе, даже если не используются для создания ключа.

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

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

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

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

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

Поэтому органы стандартизации создают и наносят на продукцию различительные метки. На автомобилях штампуется Vehicle Identification Number (VIN), в книгах печатается ISBN, на упаковке пищевых товаров есть UPC. Вы можете возразить, что эти числа не кажутся естественными. Однако их можно назвать естественными ключами, потому что они становятся стандартами и/или печатаются на объектах.

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

• Коды стран по ISO 3166
• Коды языков по ISO 639
• Коды валют по ISO 4217
• Биржевые обозначения ISIN
• UPC/EAN, VIN, GTIN, ISBN
• имена логинов
• адреса электронной почты
• номера комнат
• mac-адрес в сети
• широта, долгота для точек на поверхности Земли

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

• Не все имеют адрес электронной почты, хотя в некоторых случаях может быть допустимо. Также люди меняют свои адреса время от времени. (О стабильности ключей подробнее позже.)
• Биржевые обозначения ISIN иногда меняются. Например, символы GOOG и GOOGL не точно отражают реорганизацию компании Google в Alphabet. Это может привести к путанице, например, при покупке TWTR и TWTRQ во время IPO Twitter. Некоторые инвесторы ошибочно покупали последние.
• Номера соц. страхования для граждан США с конфиденциальностью и повторной идентификацией после смерти. После кражи могут быть заменены. Один номер может идентифицировать лицо и налоговый идентификатор.
• Индексы городов — плохой выбор. Общий индекс или несколько индексов в одном городе.

5.4 Искусственные ключи

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

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

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

Суррогаты

Искусственные ключи используются для идентификации строк независимо от изменений правил и колонок. Такие ключи называются «суррогатными ключами» и требуют особого внимания. О них мы расскажем позже.

Искусственные ключи, не являющиеся суррогатами, удобны для ссылок на строки вне базы данных. Они кратко идентифицируют данные или объекты: могут быть указаны в URL, прикреплены к счету, продиктованы по телефону, получены в банке или напечатаны на номерном знаке. (Номерной знак автомобиля является для нас естественным ключом, разработанным государством как искусственный ключ.)

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

Примеры:

• Для номерных знаков США действуют правила по использованию O и 0.
• Больницы и аптеки должны быть особенно аккуратны, учитывая требования врачей.
• Передаете код подтверждения в формате ESEMES? Не используйте символы, отличные от GSM 03.38.
• В отличие от Base64, который кодирует произвольные байтовые данные, Base32 использует ограниченный набор символов, который удобно использовать людям и проще обрабатывать на старых компьютерных системах.
• Proquints - читаемые, записываемые и произносимые идентификаторы. Это произносимые пятёрки из однозначно понимаемых согласных и гласных букв.

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

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

Однако, обратите внимание, что не следует использовать последовательный порядок в публично доступных числовых ключах. Это позволит производить поиск в ресурсах (например, /videos/1.mpeg, /videos/2.mpeg и т. д.), а также предоставит информацию о количестве данных. Для получения уникальности и скрытия порядка чисел следует использовать шифрование Фейстеля.

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

Также стоит указать несколько искусственных ключей, если они имеются. Например, у организации есть кандидаты на работу (Applicants) и сотрудники (Employees). Каждый сотрудник был кандидатом и имеет свой собственный идентификатор, который также может быть использован в качестве ключа сотрудника. Ещё один пример: можно задать идентификатор сотрудника и имя логина как два ключа в таблице Employees.

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

«Суррогатный ключ» - важный тип искусственного ключа. Он не должен быть кратким и передаваемым, как другие искусственные ключи. Он используется как внутренняя метка для идентификации строк. В SQL он используется, но приложение не обращается к нему напрямую.

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

Суррогатные ключи отлично подходят для внешних ключей. Для сохранения неизменности суррогата необходимо указать каскадное ограничение ON UPDATE RESTRICT.

С другой стороны, внешние ключи к публично передаваемым ключам должны быть помечены ON UPDATE CASCADE, чтобы обеспечить максимальную гибкость. Для достижения этого цели используется каскадное обновление на том же уровне изоляции, что и окружающая его транзакция. Это означает, что проблемы с параллельным доступом можно избежать, если выбрать строгий уровень изоляции.

Не присваивайте суррогатным ключам «естественный» статус. Как только вы покажете значение суррогатного ключа конечным пользователям или, что хуже, дадите им доступ к работе с этим значением (например, через поиск), то этот ключ приобретет значимость. Затем показанный ключ из вашей базы данных может стать естественным ключом в чьей-то другой БД.

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

Автоинкрементные INT/BIGINT

Чаще всего для суррогатных ключей используется автоинкрементная колонка bigserial, также известная как IDENTITY. (На самом деле, PostgreSQL 10 теперь, как и Oracle, поддерживает конструкцию IDENTITY, см. CREATE TABLE.)

Однако, автоинкрементное целое не является хорошим выбором для суррогатных ключей.

Недостатки последовательных ключей:

Если все последовательности начинаются с 1 и постепенно увеличиваются, то у строк из разных таблиц будут одинаковые значения ключей. Это не идеально. Лучше использовать непересекающиеся множества ключей в таблицах. Это поможет избежать ошибок при выполнении запросов, например, при соединении таблиц с помощью JOIN. Как альтернатива, можно составить последовательности из чисел, кратных различным простым. Однако это будет достаточно трудоёмко.

Вызов nextval() для генерации последовательности в современных распределённых SQL приводит к ухудшению масштабируемости системы.

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

С философской точки зрения, последовательное увеличение чисел связано со старыми системами, в которых подразумевался порядок строк. Чтобы упорядочить строки, используйте явный метод, например, колонку меток времени или что-то имеющее значение для ваших данных. В противном случае нарушается первая нормальная форма.

UUID

Давайте рассмотрим другой вариант: использование больших целых чисел (128-битных), генерируемых согласно случайному шаблону. Алгоритмы генерации таких универсальных уникальных идентификаторов (UUID) имеют очень малую вероятность повторного выбора одного и того же значения даже при одновременном выполнении на двух разных процессорах.

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

Некоторые люди предполагают, что UUID - это строки, поскольку они записываются в традиционном шестнадцатеричном формате с дефисом: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Однако в PostgreSQL имеется компактный (128-битный) тип uuid, который занимает два bigint, т.е. издержки по сравнению с объемом другой информации в базе данных незначительны.

UUID часто обвиняют в громоздкости, но когда нужно произносить, печатать или читать их? Мы говорили, что это имеет смысл для показываемых искусственных ключей. Но никто (по определению) не должен видеть суррогатный UUID. Разработчик может использовать UUID для запуска команд SQL в psql для отладки системы. Он может также ссылаться на строки и использовать более удобные ключи, если они заданы.

Реальная проблема с UUID состоит в том, что сильно рандомизированные значения приводят к увеличению объёма записи (write amplification) из-за записей полных страниц в журнал с упреждающей записью (write-ahead log, WAL). Давайте измерим этот write amplification. На самом деле, проблема в старых файловых системах. Когда PostgreSQL выполняет запись на диск, он изменяет «страницу» на диске. При отключении питания большинство файловых систем всё равно сообщает об успешной записи, даже до того, как данные безопасно сохранятся на диске. Если PostgreSQL наивно примет это за завершённое действие, то при последующей загрузке системы база данных будет повреждена.

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

Индексирование сильно рандомизированных значений, например UUID, обычно затрагивает множество различных страниц диска. Это приводит к записи полного размера страницы (обычно 4 или 8 КБ) в WAL для каждой новой записи (так называемая полностраничная запись, FPW).

Некоторые алгоритмы генерации UUID (например, snowflake от Twitter или uuid_generate_v1() из расширения uuid-ossp для PostgreSQL) создают на каждой машине монотонно увеличивающиеся значения. Этот подход сокращает количество страниц диска, затрагиваемых записью, и снижает FPW.

5.6 Итоги и рекомендации

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

Для каждой таблицы:

• Определите все естественные ключи и объявите их.
• Создайте суррогатный ключ типа UUID с именем <table_name>_id и значением по умолчанию uuid_generate_v1(). Вы можете даже пометить его как первичный ключ. Добавьте название таблицы в этот идентификатор, чтобы упростить JOIN, т.е. получите JOIN foo USING (bar_id) вместо JOIN foo ON (foo.bar_id = bar.id). Не передавайте этот ключ клиентам и не выводите его за пределы базы данных.
• Для промежуточных таблиц, через которые происходит JOIN, объявите все колонки внешних ключей как единый составной первичный ключ.
• Для добавления дополнительной защиты можно использовать искусственный ключ в URL или других указаниях ссылки на строку. Для замаскирования автоинкрементных целых можно использовать сетку Фейстеля или pg_hashids.
• Укажите каскадное ограничение ON UPDATE RESTRICT, используя UUID в качестве внешних ключей. Для внешних искусственных ключей используйте ON UPDATE CASCADE. Выберите естественные ключи, соответствующие вашей логике.

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