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

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

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

Реализация
Как правило, суррогатный ключ — это просто числовое поле, в которое заносятся значения из возрастающей числовой последовательности. Это может делаться при помощи триггеров (типичный способ порождения ключей в Oracle). В ряде СУБД (например, PostgreSQL, Sybase, MySQL[1] или SQL Server [2]) существует специальный тип данных для таких полей — числовое поле, в которое при добавлении записи в таблицу автоматически записывается уникальное для этой таблицы числовое значение — т.н. «автоинкремент» (англ. autoincrement) или serial в терминологии PostgreSQL. В последнее время появилась тенденция использования в качестве суррогатных первичных ключей значений UUID в той или иной форме.

Использование
Главное достоинство суррогатного ключа состоит в том, что он никогда не изменяется, поскольку не является информативным полем таблицы (не несёт никакой информации об описываемом записью объекте).
Работа с таблицей, содержащей суррогатный ключ, организуется так, чтобы при добавлении новой записи в поле суррогатного ключа было записано заведомо уникальное значение. Для этого используются либо специальные типы данных, либо триггеры и последовательности. После создания новой записи значение этого поля используется в качестве первичного ключа. Именно на него указывают все ссылки в связанных таблицах.
Использовать суррогатный первичный ключ имеет смысл, если естественный первичный ключ (составленный из информативных полей таблицы) — составной, и на него придётся ссылаться во внешних ключах многих таблиц. В этом случае проще написать запрос: SELECT * FROM p, c WHERE p.primary_key = c.foreign_key;
чем SELECT * FROM p, c WHERE p.id1 = c.fk1 AND p.id2 = c.fk2 AND p.id3 = c.fk3;
Кроме того первый вариант работает быстрее.
Также использовать суррогатный ключ имеет смысл в случае, когда возможны изменения полей, составляющих (естественный) первичный ключ (в особенности если этот ключ — составной). В этом случае возникает проблема т. н. «каскадных изменений» — при изменении полей, входящих в ключ, для сохранения ссылочной целостности необходимо в одной транзакции внести адекватные изменения во все записи, ссылающиеся на изменяемую. При использовании же суррогатного ключа в качестве первичного изменять его не придётся, не придётся делать и каскадные изменения.

Поиск Яндекса по сайту

Для поиска базы данных access введите слово, например, «сотрудники» и нажмите кнопку

Источник

Автоинкрементные первичные ключи (суррогатные ключи) = зло?

В этой статье я приведу взгляд (отрицательный по большей части) Джоша Беркуса, CEO компании PostgreSQL Experts Inc. на использование суррогатных ключей для таблиц базы данных, тех самых INT NOT NULL AUTO_INCREMENT PRIMARY KEY, к которым мы привыкли. Фактически, это будет вольный, сильно сокращенный перевод его статьи на ittoolbox.

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

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

Если вы опытный DBA, наверное, вам стоит пройти мимо, чтобы не расстраиваться.

Но обо всем по порядку. Сначала ОЧЕНЬ сокращенный перевод:

«Суррогатные числовые ключи попали в стандарт SQL89 для сохранения со старыми приложениями, которым требовались номера строк. Впоследствии в разговоре с Джо Селко, Кодд сказал, что жалеет, что допустил это.

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

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

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

Какие причины могут побудить нас использовать суррогатные ключи?

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

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

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

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

Следование SQL стандарту и принципам хорошего дизайна. Очень плохая причина. Она полностью основана на невежестве. Обычно, ей следуют потому, что где-то услышали, как кто-то прочитал в блоге кого-то, кто учится в УНИВЕРСИТЕТЕ, что использование суррогатных ключей – это стандарт в индустрии. Имейте ввиду, что ни современные стандарты SQL, ни сама реляционная теория не содержит даже упоминания о суррогатных ключах.

Возможность легкого изменения. Неясно. Действительно, некоторые СУБД не умеют выполнять ON UPDATE CASCADE или делают это слишком неэффективно (кстати, подумайте об этом как о причине смены СУБД). И в этом случае, данная причина может оказаться весомой. Однако иногда разработчики говорят о том, что ключи [первичные] для записи меняться не должны и должны оставаться одинаковыми на всей протяженности жизненного цикла записи. Имейте ввиду, что это утверждение яйца выеденного не стоит и уж, разумеется, полностью отсутствует в реляционной теории.

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

Для баз данных мега-объема результирующий размер таблицы также может иметь значение. Но для этого база должна быть уж очень большой.

Производительность соединений или сортировки также имеет значение на большом количестве данных, в зависимости от типа первичного ключа и числа его компонентов. Однако мой опыт показывает, что когда называют эту причину, за ней очень редко стоят реальные расчеты или замеры производительности. Например, www.bricolage.cc использует 14-байтовые числовые первичные ключи для своих таблиц много лет. Однако и в этом случае, после появления пользователя с трехмиллионной записью в истории, когда встал вопрос об изменении первичных ключей ради производительности, эта проблема была решена переписыванием запросов. Было достигнуто примерно 10-кратное увеличение производительности.

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

Если я упустил что-то важное в переводе, пожалуйста, скажите мне об этом. Добавлю.

Теперь немного о том, что я сам думаю.

Все же статья показалось мне немного драматизирующей проблему. Мне кажется, что суррогатные ключи выбираются все чаще всего именно из-за того, чтобы избежать проблем с производительностью впоследствии и в последнее время все так к ним привыкли, что они насаждаются на уровне самих СУБД. Например, InnoDB, если вы не создадите первичный ключ, просто создаст его сама. Кстати, в случае с InnoDB выбор первичного ключа имеет серьезные последствия с точки зрения производительности, поскольку по нему производится кластеризация (соответственно, выбор естественного ключа может как улучшить, так и ухудшить ситуацию).

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

Эта статья открыла мне глаза в том плане, что я всегда считал естественным не искать себе особых кандидатов в первичные ключи, а просто создать INT NOT NULL AUTO_INCREMENT PRIMARY KEY поле и сидеть спокойно. Разумеется, я знал о том, что в качестве первичного ключа можно выбрать любой уникальный ключ, но я никогда на этом не акцентировался. Я никогда особо не задумывался о том, что вообще по-настоящему делает данную строку базы данных уникальной и почему это важно. Как выяснилось, зря.

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

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

Структура базы данных

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

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

Ответ очень простой. Комбинация из ID пользователя и времени возникновения события. И вот тут я неожиданно увидел ситуацию с другой стороны. Практически во всех моих старых проектах поле DATETIME используется для хранения времени в логах. Просто потому, что это удобно. Да, я знал, что оно хранится с точностью до секунды и меня это полностью устраивало. Сейчас, когда я начал искать естественные ключи, мне неожиданно пришло в голову, какие последствия это несет. Торрент-трекер, о котором идет речь, нагружен очень сильно и в течение секунды многое может произойти. Фактически, если в логе с этим чертовым суррогатным ключом окажется несколько событий с одним и тем же временем и они произошли друг за другом очень быстро, я смогу сказать, какое из них произошло первым, а какое последним только ориентируясь по автоинкременту суррогатного ключа. Само поле информации о дате, которое создано именно затем, чтобы сообщать такие вещи, мне ничем не поможет. А точно выяснить интервал между событиями я не смогу вообще.

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

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

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

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

Таблица session. По некоторой причине я не полагался на сессии PHP полностью, а частично реализовал свои. В качестве первичного ключа этой таблицы выступает случайное значение. Мало того, что это просто глупо использовать 40-символьные случайные последовательности, так оно тут вообще не очень нужно. Что выступает в качестве естественного ключа для записей в этой таблице? В этом проекте пользователю не позволялось быть залогиненым с нескольких компов одновременно. Гм. user_id? Все остальное по отношению к данному значению вторично. Я не буду сейчас анализировать, что вытекает из этого простого утверждения. Много чего вплоть до удаления таблицы сессий и реализации другого механизма. Вариантов тут много.

Перейдем к таблице torrent.

Это поле в таблице peer называется peer_info_hash. А в таблице torrent это поле torrent_info_hash. torrent_id там лишний. Совсем. Обратите внимание, что в таблице peer torrent_id тоже есть. Непонятно зачем.

Ну и таблица user. Казалось бы, тут я просто не мог сделать ошибки. Ошибался.

В системе авторизации, торрент-трекеров используется GET параметр с уникальным для пользователя значением. В таблице это значение user_torrent_uid. Вот спросите меня, кто мешал использовать это значение в качестве естественного ключа в том или ином варианте? Да, оно может измениться. В очень редком случае. Ну и что? Если 8 байт – слишком длинно, можно было взять обычный случайный INT и конвертировать его в текст, как на Flickr делают умные люди. Можно было… Да много чего можно было.

Источник

Создание суррогатных ключей с использованием свойства IDENTITY и выделенного пула SQL в Azure Synapse Analytics

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

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

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

В Azure Synapse Analytics значение IDENTITY увеличивается независимым образом в каждом распределении и не пересекается со значениями IDENTITY в других распределениях. Значение IDENTITY в Synapse не обязательно будет уникальным, если пользователь явно вставляет повторяющееся значение с помощью конструкции SET IDENTITY_INSERT ON или повторно заполняет свойство IDENTITY. Дополнительные сведения см. в разделе CREATE TABLE (Transact-SQL) IDENTITY (свойство).

Создание таблицы со столбцом IDENTITY

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

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

Затем можно использовать INSERT..SELECT для заполнения таблицы.

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

Распределение значений

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

Ниже приведен характерный пример.

Неравномерные данные

SELECT..INTO

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

Если любое из этих условий выполняется, столбец создается как NOT NULL и не наследует свойство IDENTITY.

CREATE TABLE AS SELECT

Вставка значений в столбец IDENTITY явным образом

Приведенный ниже сценарий показывает, как явным образом добавить эту строку с помощью инструкции SET IDENTITY_INSERT.

Загрузка данных

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

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

В следующем примере представлена базовая схема.

В настоящее время невозможно использовать CREATE TABLE AS SELECT при загрузке данных в таблицу со столбцом IDENTITY.

Системные представления

Можно использовать представление каталога sys.identity_columns для поиска столбца со свойством IDENTITY.

Для лучшего понимания схемы базы данных в этом примере показано, как интегрировать sys.identity_column с остальными представлениями системных каталогов.

Ограничения

Свойство IDENTITY не может быть использовано:

В выделенном пуле SQL не поддерживаются следующие связанные функции:

Стандартные задачи

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

Столбец C1 является столбцом IDENTITY во всех следующих задачах.

Поиск максимального распределенного значения в таблице

Поиск начального значения и шага приращения для свойства IDENTITY

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

Источник

SQL ключи во всех подробностях

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

Прочитав шестьдесят четыре статьи, пролистав разделы пяти книг и задав кучу вопросов в IRC и StackOverflow, я (автор оригинальной статьи Joe «begriffs» Nelson), как мне кажется, собрал куски паззла воедино и теперь смогу примирить противников. Многие споры относительно ключей возникают, на самом деле, из-за неправильного понимания чужой точки зрения.

Содержание

Что же такое «ключи»?

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

Для примера рассмотрим таблицу для подсчёта карт в карточной игре:

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

Мы можем и должны задать базе данных это ограничение, добавив следующее:

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

В более общей ситуации, когда нужно отслеживать несколько колод карт, можно добавить новое поле и записывать сколько раз мы видели карту:

Ограничения уникальности

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

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

Вот два примера таблиц с несколькими ключами.

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

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

То, что в предыдущем разделе мы назвали просто «ключами», обычно называется «потенциальными ключами» (candidate keys). Термин «candidate» подразумевает, что все такие ключи конкурируют за почётную роль «первичного ключа» (primary key), а оставшиеся назначаются «альтернативными ключами» (alternate keys).

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

Термин «ключ» означал ключ сортировки файла, который был нужен для выполнения любых операций обработки в последовательной файловой системе. Набор перфокарт считывался в одном и только в одном порядке; невозможно было «вернуться назад». Первые накопители на магнитных лентах имитировали такое же поведение и не позволяли выполнять двунаправленный доступ. Т.е., первоначальный Sybase SQL Server для чтения предыдущей строки требовал «перемотки» таблицы на начало.

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

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

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

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

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

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

У некоторых людей возникают сложности с выбором «естественного» ключа из-за того, что они придумывают гипотетические ситуации, в которых определённый ключ может и не быть уникальным. Они не понимают самого смысла задачи. Смысл ключа в том, чтобы определить правило, по которому атрибуты в любой момент времени должны быть и всегда будут уникальными в конкретной таблице. Таблица содержит данные в конкретном и хорошо понимаемом контексте (в «предметной области» или в «области дискурса») и единственное значение имеет применение ограничения в этой конкретной области.

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

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

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

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

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

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

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

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

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

Суррогаты

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

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

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

Эта функция является обратной самой себе (т.е. pseudo_encrypt(pseudo_encrypt(x)) = x ). Точное воспроизведение функции является своего рода безопасностью через неясность, и если кто-нибудь догадается, что вы использовали сеть Фейстеля из документации PostgreSQL, то ему будет легко получить исходную последовательность. Однако вместо (((1366 * r1 + 150889) % 714025) / 714025.0) можно использовать другую функцию с областью значений от 0 до 1, например, просто поэкспериментировать с числами в предыдущем выражении.

Вот, как использовать pseudo_encrypt:

В предыдущем примере для short_id использовались целые значения обычного размера, для bigint есть другие функции Фейстеля, например XTEA.

Ещё один способ запутать последовательность целых чисел заключается в преобразовании её в короткие строки. Попробуйте воспользоваться расширением pg_hashids:

Здесь снова будет быстрее хранить в таблице сами целые числа и преобразовывать их по запросу, но замерьте производительность и посмотрите, имеет ли это смысл на самом деле.

Теперь, чётко разграничив смысл искусственных и естественных ключей, мы видим, что споры «естественные против искусственных» являются ложной дихотомией. Искусственные и естественные ключи не исключают друг друга! В одной таблице могут быть и те, и другие. На самом деле, таблица с искусственным ключом должна обеспечивать и естественный ключ, за редким исключением, когда не существует естественного ключа (например, в таблице кодов купонов):

Если у вас есть искусственный ключ и вы не объявляете естественные ключи, когда они существуют, то оставляете последние незащищёнными:

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

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

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

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

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

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

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

Автоинкрементные bigint

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

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

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

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

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

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

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

Реальная проблема с UUID в том, что сильно рандомизированные значения приводят к увеличению объёма записи (write amplification) из-за записей полных страниц в журнал с упреждающей записью (write-ahead log, WAL). Однако, на самом деле снижение производительности зависит от алгоритма генерации UUID.

Давайте измерим write amplification. По правде говоря, проблема в старых файловых системах. Когда PostgreSQL выполняет запись на диск, она изменяет «страницу» на диске. При отключении питания компьютера большинство файловых систем всё равно сообщит об успешной записи ещё до того, как данные безопасно сохранились на диске. Если PostgreSQL наивно воспримет такое действие завершённым, то при последующей загрузке системы база данных будет повреждена.

Раз PostgreSQL не может доверять большинству ОС/файловых систем/конфигураций дисков в вопросе обеспечения неразрывности, база данных сохраняет полное состояние изменённой дисковой страницы в журнал с упреждающей записью (write-ahead log), который можно будет использовать для восстановления после возможного сбоя. Индексирование сильно рандомизированных значений наподобие UUID обычно затрагивает кучу различных страниц диска и приводит к записи полного размера страницы (обычно 4 или 8 КБ) в WAL для каждой новой записи. Это так называемая полностраничная запись (full-page write, FPW).

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

Давайте измерим влияние FPW для различных алгоритмов генерации UUID, а также исследуем статистику WAL. Я использовал следующую конфигурацию для замера.

Перед тек, как добавить UUID в каждую таблицу, находим текущую позицию write-ahead log.

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

Я провёл тесты трёх сценариев:

И вот результаты замеров скорости:

График скорости вставки UUID

Вот статистика WAL для каждого из способов:

Результаты подтверждают, что gen_random_uuid создаёт существенную активность в WAL из-за полностраничных образов (full-page images, FPI), а другие способы этим не страдают. Конечно, в третьем методе я просто запретил базе данных делать это. Однако запрет FPW совсем не то, что стоило бы использовать в реальности, если только вы не полностью уверены в файловой системе и конфигурации дисков. В этой статье утверждается, что ZFS может быть безопасным для отключения FPW, но пользуйтесь им с осторожностью.

Явным победителем в моём бенчмарке оказался uuid_generate_v1() – он быстр и не замедляется при накоплении строк. Расширение uuid-ossp по умолчанию установлено в таких облачных базах данных, как RDS и Citus Cloud, и будет доступно без дополнительных усилий.

В документация есть предупреждение о uuid_generate_v1:

В нём используется MAC-адрес компьютера и метка времени. Учитывайте, что UUID такого типа раскрывают информацию о компьютере, который создал идентификатор, и время его создания, что может быть неприемлемым, когда требуется высокая безопасность.

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

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

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

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

Обсуждать подобные профессиональные вопросы мы предлагаем на наших конференциях. Если у вас за плечами большой опыт в ИТ-сфере, наболело, накипело и хочется высказаться, поделиться опытом или где-то попросить совета, то на майском фестивале конференций РИТ++ будут для этого все условия, 8 тематических направлений начиная от фронтенда и мобильной разработки, и заканчивая DevOps и управлением. Подать заявку на выступление можно здесь.

Источник