Между двумя понятиями уникальный бизнес-ключ и первичный ключ в реляционной базе данных есть тонкая, но важная разница. Например, в таблице "users" "user_id" может быть первичным ключом, а "email_address" (который не должен быть null и unique) может быть уникальным для бизнеса ключом. Столбцы, реализующие каждое понятие, могут совпадать или не совпадать. Этот пост показывает, что, особенно в распределенной базе данных SQL, лучше, чтобы они не совпадали. Другими словами, лучше выбрать суррогатный и, следовательно, сгенерированный первичный ключ. Мы увидим, что стандартный метод d e facto для создания значений суррогатного ключа в монолитной базе данных SQL имеет заметные недостатки в распределенной базе данных, и покажем вам предпочтительную альтернативу.
YugabyteDB – это высокопроизводительная распределенная база данных SQL с открытым исходным кодом, созданная на основе масштабируемой и отказоустойчивой архитектуры, вдохновленной Google Spanner. YugabyteDB использует собственное специальное распределенное хранилище документов под названием DocDB. Но он обеспечивает функциональность SQL и хранимых процедур, повторно используя верхнюю половину стандартного исходного кода PostgreSQL. Это объясняется в двух частях сообщения блога Распределенный PostgreSQL на архитектуре Google Spanner: (1) Уровень хранения; и (2) Слой запроса.
Что такое суррогатные первичные ключи
Я собираюсь принять эти два правила как аксиому:
- Каждая таблица должна иметь определенное ограничение первичного ключа.
- У каждой таблицы должен быть хотя бы один уникальный бизнес-ключ. Один из них может совпадать с первичным ключом; а может и не совпадают. Деловой уникальный ключ, не совпадающий с первичным ключом, должен иметь собственное ограничение уникальности, а не значение NULL.
Я дам определения этим двум терминам, первичный ключ и уникальный бизнес-ключ, и вскоре обосноваю свою точку зрения, что правила, которые я изложил, являются аксиомами.
В огромном количестве учебных курсов по SQL и разработке тестовых примеров используется таблица, созданная следующим образом:
create table users(
user_id int
generated always as identity
constraint items_items_pk primary key,
email_address text
constraint items_name_nn not null
constraint items_name_unq unique,
preferred_given_name varchar,
family_name varchar not null
...);
Определение уникального для бизнеса ключа является операционным: это значение, которое что-то значит в деловом мире; и обычные люди указывают это значение, чтобы получить факты об одной интересующей строке таблицы. Адрес электронной почты пользователя, безусловно, соответствует этим критериям. В конце концов, любой адрес электронной почты по своей сути будет уникальным на всей планете, а теперь и за ее пределами!
Определение первичного ключа является формальным: это список столбцов, который является аргументом для заданного ограничения первичного ключа. И, согласно надлежащей практике, первичный ключ, и только он, может быть целью ограничения внешнего ключа. Адрес электронной почты пользователя также может стать первичным ключом таблицы — по крайней мере, такой выбор может соблазнить новичка.
Я, должно быть, видел сотни, если не тысячи, учетных записей документации, сообщений в блогах, тестовых случаев ошибок и т. д., которые иллюстрируют свою точку зрения с помощью некоторого варианта таблицы users (очень часто специализированной для «сотрудники»). И я никогда не видел ни одного, который использует "email_address" в качестве первичного ключа. Почему это? Это потому, что человек может в любой момент изменить адрес электронной почты, который он хочет использовать в той или иной системе. Распространение таких изменений на столбцы внешнего ключа в других таблицах, как правило, является кошмаром, и по этой причине большинство опытных специалистов по проектированию таблиц придерживаются правила, согласно которому первичные ключи должны быть неизменяемыми — как в отношении значения, так и в отношении типа данных. Опыт — иногда болезненный — показал, что очень трудно определить неизменное, уникальное явление в деловом мире, которое могло бы служить естественным первичным ключом таблицы.
Итак... если ни "email_address", ни любое другое естественное значение нельзя использовать в качестве первичного ключа таблицы "users", что может быть? Единственный оставшийся вариант — сделать первичный ключ синтетическим значением, не имеющим никакого значения в деловом мире, которое моделирует таблица, другими словами, суррогатом.
Таким образом, единственный способ согласовать требования о том, что каждая таблица должна иметь по крайней мере один бизнес-уникальный ключ, и что каждая таблица должна иметь первичный ключ, — это разделить понятия так, чтобы таблица имела оба. Это означает, что первичный ключ, поскольку он содержит синтетическое значение, должен быть сгенерирован за кулисами. (Суррогатный первичный ключ почти всегда представляет собой всего один столбец.) Еще одна очень знакомая иллюстрация этого пункта дается идентификационным номером автомобиля — его VIN. VIN моего автомобиля имеет следующий формат: «xxxx xxxxx xxx xxxx». Он был явно сгенерирован! Это понятие было изобретено именно потому, что естественный уникальный ключ бизнеса, регистрационный знак автомобиля, может меняться в течение срока службы автомобиля.
Операции, которые изменяют содержимое таблицы, такие как «вставка» и «удаление», когда ее уникальный бизнес-ключ и ее первичный ключ различны, очевидно, обходятся дороже, чем когда они совпадают. (Это связано с тем, что уникальный для бизнеса ключ, когда он отличается, нуждается в собственном вторичном индексе.) Иными словами, использование суррогатного первичного ключа обходится дороже, чем использование естественного первичного ключа. Именно по этой причине возникли две религии, я бы даже сказал, два культа. Один допускает и поощряет естественные первичные ключи; а другой запрещает их и настаивает на использовании отдельного столбца для суррогатного первичного ключа. Но жизнь всегда такова: компромисс между краткосрочной целесообразностью и долгосрочной правильной практикой.
Теперь читателям должно быть ясно, на чем я стою по этому поводу: в моей книге культ естественного первичного ключа близок к Обществу Плоской Земли!
Первичные ключи в YugabyteDB
Таблицы в распределенной базе данных SQL, вероятно, будут иметь гораздо большее количество строк, чем таблицы в монолитной базе данных. В конце концов, объем данных является одним из важнейших факторов, определяющих выбор использования YugabyteDB. Это означает, что боль, связанная с изменением первичного ключа и устранением последствий в дочерних таблицах — огромная, как это бывает в монолитной базе данных, — становится почти непреодолимой в распределенной базе данных.
Даже в чрезвычайно редком случае, когда таблица не является целью ограничения единственного внешнего ключа, и когда существующее ограничение, согласно которому первичные ключи в YugabyteDB действительно неизменны, снимается (см. приложение), изменяется структура такой таблицы. первичный ключ будет дорогим. План состоит в том, чтобы построить новую таблицу в фоновом режиме, а затем выполнить быстрый обмен между старой таблицей и ее заменой. Это означает, что время простоя будет сведено к минимуму. Тем не менее, операция захватит ресурсы из обычных задач кластера, так что производительность во время перестроения пострадает.
Как заполнить суррогатный первичный ключ в YugabyteDB
Именно потому, что значения, используемые ограничением суррогатного первичного ключа, не имеют никакого значения в мире пользователя, они должны автоматически генерироваться за кулисами с использованием явной или неявной схемы на основе триггера. И по той же причине (и как уже упоминалось) такое ограничение первичного ключа почти всегда определяется ровно для одного столбца.
Стандартный подход де-факто для монолитной базы данных SQL.
Этот шаблон обычно используется в монолитной базе данных SQL:
create table t(k int generated always as identity, ...);
(Вы можете увидеть что-то более удобное, чем int или его эквивалент в имеющейся системе базы данных, если разработчик ожидает, что таблица со временем будет содержать более четырех миллиардов строк — маловероятно, поскольку это может показаться в контексте монолитной базы данных.)
Синтаксис может различаться в зависимости от поставщика системы баз данных. Эффект «генерируется… как личность» неявно заключается в создании последовательности (вы можете увидеть это с помощью метакоманды \d), которая используется невидимым, сгенерированным системой триггером для заполнения так- аннотированный столбец. Однако, несмотря на то, что последовательность не ведет себя транзакционно (вы все равно «используете» значения, которые она предоставляет, даже при откате), ее использование в распределенной базе данных, тем не менее, влечет за собой затраты на «распределенную транзакцию». Вкратце это следует из требования, что каждый узел должен иметь возможность извлекать уникальное значение из последовательности. Вы можете подумать, что предоставление каждому узлу большого пакета значений от центрального «оракула» каждый раз, когда последний пакет израсходован, может облегчить проблему. Но такая схема по-прежнему сопряжена со значительными проблемами проектирования и связанными с этим затратами, когда общий дизайн требует, чтобы не было единой точки отказа — другими словами, не было центрального «оракула»!
Рекомендуемый подход для YugabyteDB
Из-за относительно низкой производительности последовательностей в распределенной базе данных SQL предпочтение отдается надежному генератору псевдослучайных чисел в YugabyteDB. YugabyteDB наследует возможность создавать так называемые расширения PostgreSQL. И, как поясняется в документации здесь, сделать это (как суперпользователь) очень просто:
create extension pgcrypto;
(Это устанавливает расширение в схему, которая является текущей на момент выдачи инструкции. Затем вы предоставляете «использование» схемы другим пользователям, которым необходимо использовать ее возможности.) Это буквально все, что вам нужно сделать. После этого вы можете создать такую таблицу:
create table t(k uuid default some_schema.gen_random_uuid(), ...);
UUID расшифровывается как Универсальный уникальный идентификатор (см. эту статью в Википедии). И, как следует из названия, функция gen_random_uuid() автоматически заполняет столбец суррогатного первичного ключа таблицы, возвращая разные значения при каждом вызове, независимо от того, на каком компьютере она вызывается — именно то, что вам нужно. в многоузловом кластере YugabyteDB. Вероятность столкновения исчезающе мала — настолько мала, что вам не нужно учитывать риск столкновения с кодом защитного приложения.
Мои неофициальные тесты времени, используя это:
insert into t(v) select * from generate_series(1, 1000000);
после каждого из двух вариантов «создать таблицу», показанных выше, покажите, что «по умолчанию gen_random_uuid()» примерно в два раза быстрее, чем «генерируется всегда как личность».
Примечание. Для теста я использовал кластер YugabyteDB с одним узлом на своем MacBook. Вы можете ожидать увидеть разные соотношения в разных средах. Но вы можете с уверенностью ожидать, что «gen_random_uuid()» будет заметно быстрее, чем «генерируется всегда как идентификатор».
Обратите внимание, что автогенерация столбца суррогатного первичного ключа подразумевает схему «вставить… вернуть k в», когда вы создаете новую главную строку и несколько дочерних строк подробностей для нее в одной транзакции.
Альтернативы «gen_random_uuid()»
Документация здесь объясняет, как создать расширение uuid-ossp. Это потребует от администратора больше усилий, чем установка pgcrypto; но это, безусловно, практичный выбор.
Это расширение предоставляет несколько альтернативных функций генератора псевдослучайных UUID. Одним из примеров является «uuid_generate_v4()». Некоторые люди предпочитают это «gen_random_uuid()», потому что часть возвращаемого значения зависит от MAC-адреса хост-компьютера. Мои временные тесты показывают, что это всего лишь немного быстрее, чем «gen_random_uuid()». Но я ожидаю, что разница исчезнет в шуме при обычном использовании приложения, использующего кластер YugabyteDB.
Примечание. Встроенная функция clock_timestamp() SQL — считывает системные часы и не требует установки расширения — может показаться заманчивым выбором. Однако, поскольку его номинальная точность составляет 1 микросекунду, коллизии должны происходить довольно часто в многоузловой системе, которая принимает строки на каждом узле с высокой скоростью. Во всяком случае, мой неофициальный тест времени показал, что у него примерно такая же скорость, как у «gen_random_uuid()», так что нет причин его рассматривать.
Вывод
В этом посте я дал определения терминам естественный первичный ключ и суррогатный первичный ключ, рассказал вам о религиозной напряженности между сторонниками естественного и суррогатного ключей, а также показал вам, что дискуссия принимает характер «разница в степени становится разницей в характере», когда вы делаете дизайн таблицы для любой распределенной базы данных SQL и, в частности, для кластера YugabyteDB, поскольку ожидается, что количество строк в таблице будет огромным. Вот моя рекомендация:
- Укажите суррогатный первичный ключ для каждой таблицы в вашем проекте.
- Определите хотя бы один ключ, уникальный для бизнеса, и создайте ограничение not null, unique для каждого.
- Определите столбец, участвующий в каждом ограничении первичного ключа, с типом данных "uuid" и заполните его декларативно, используя "default gen_random_uuid()" или, если хотите, один из функции, такие как «uuid_generate_v4()», которые предоставляются расширением «uuid-ossp».
Вы можете думать об этом как о страховом полисе. Вы можете негодовать, платя за дополнительный столбец (столбцы) со вторичным индексом (ами). Но вы соглашаетесь с тем, что скромные затраты оправданы по сравнению с разорительными затратами на изменение определения естественного первичного ключа — когда вы понимаете, что, вопреки вашему первоначальному пониманию мира, который вы моделируете, у вас нет иного выбора, кроме как сделай это.
Что дальше?
- Подробно сравните YugabyteDB с такими базами данных, как CockroachDB, Google Cloud Spanner и MongoDB.
- Начните работу с YugabyteDB в облаке или контейнере по вашему выбору.
- Свяжитесь с нами, чтобы узнать больше о лицензировании, ценах или запланировать технический обзор.
Приложение:
В краткосрочной перспективе первичные ключи в YugabyteDB действительно неизменяемы.
Как упоминалось в обзоре в начале, подсистема SQL YugabyteDB реализована путем повторного использования верхней половины стандартного исходного кода PostgreSQL. Это объясняется в сообщении блога Распределенный PostgreSQL на слое Google Spanner Architecture-Query. Это означает, что синтаксис PostgreSQL принят. Однако иногда вы увидите семантическую ошибку о том, что запрашиваемая функциональность еще не поддерживается. (Текст сообщения включает либо ссылку на проблему в нашем репозитории GitHub, которая отслеживает усилия по реализации отсутствующей функциональности, либо приглашение пользователю зарегистрировать новую проблему и объяснить вариант использования, в котором отсутствующая функциональность вызывает проблемы.) PostgreSQL поддерживает следующие операции со столбцами первичного ключа:
При создании таблицы без первичного ключа:
- Вы можете изменить таблицу, добавив новый столбец, сделав его первичным ключом.
- Вы можете изменить таблицу, добавив новый столбец, сделав его первичным ключом.
При создании таблицы с первичным ключом:
- Вы можете изменить таблицу, чтобы удалить ограничение первичного ключа.
- Вы можете изменить таблицу, чтобы удалить столбец, на который распространяется ограничение первичного ключа.
- Вы можете изменить тип данных столбца (включая изменение ширины столбца varchar), на который распространяется ограничение первичного ключа.
- Вы можете изменить значение в столбце, на который распространяется ограничение первичного ключа.
Вот некоторые примеры:
alter table t add constraint t_pk primary key(non_pk_column); alter table t add column new_column int constraint t_pk primary key; alter table t drop column part_of_the_pk; update t set part_of_the_pk = 2 where part_of_the_pk = 1 and the_other_part_of_the_pk = 2; alter table t alter column k type varchar(20);
Перечисленная функциональность еще не поддерживается в YugabyteDB. Но мы планируем закрыть этот пробел в настоящее время.
Первоначально опубликовано на https://blog.yugabyte.com 19 февраля 2020 г.
