Unique sql что это
UNIQUE
Ограничение UNIQUE
Ограничение UNIQUE в SQL позволяет идентифицировать каждую запись в таблице.
Если помещается ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк, значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL), так как не имеет смысла позволить одной строке таблицы иметь значение NULL, а затем исключать другие строки с NULL значениями как дубликаты.
SQL Server / Oracle / Access
Пример создания таблицы SQL с ограничением UNIQUE:
CREATE TABLE Student
( Kod_stud integer NOT NULL UNIQUE,
Fam char (30) NOT NULL UNIQUE,
Когда обьявляется поле Fam уникальным, две Смирновых Марии могут быть введены различными способами — например, Смирнова Мария и Смирнова М. Столбцы (не первичные ключи), чьи значения требуют уникальности, называются ключами-кандидатами или уникальными ключами. Можно определить группу полей как уникальную с помощью команды ограничения таблицы — UNIQUE. Объявление группы полей уникальной, отличается от объявления уникальными индивидуальных полей, так как это комбинация значений, а не просто индивидуальное значение, которое обязано быть уникальным. Уникальность группы заключается в том, что пары строк со значениями столбцов «a», «b» и «b», «a» рассматривались отдельно одна от другой.
Если база данных определяет, что каждая специальность принадлежит одному и только одному факультету, то каждая комбинация кода факультета(Kod_f) и кода специальности(Kod_spec) в таблице Spec должна быть уникальной. Например:
CREATE TABLE Spec
( Kod_spec integer NOT NULL,
Kod_f integer NOT NULL,
Nazv_spec char (50) NOT NULL,
UNIQUE (Kod_spec, Kod_f));
Оба поля в ограничении таблицы UNIQUE все еще используют ограничение столбца — NOT NULL. Если бы использовалось ограничение столбца UNIQUE для поля Kod_spec, такое ограничение таблицы было бы необязательным. Если значения поля Kod_spec различно для каждой строки, то не может быть двух строк с идентичной комбинацией значений полей Kod_spec и Kod_f. Ограничение таблицы UNIQUE наиболее полезно, когда индивидуальные поля не обязательно должны быть уникальными.
MySQL UNIQUE
Пример создания таблицы Persons в MySQL с ограничением UNIQUE:
CREATE TABLE Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
);
Удалить ограничение UNIQUE
Если после создания ограничения UNIQUE и в том случае, когда ограничение UNIQUE не имеет смысла, UNIQUE можно удалить. Для этого используйте следующий SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE table_name DROP CONSTRAINT uc_PersonID;
MySQL:
ALTER TABLE table_name DROP INDEX uc_PersonID;
Вы должны войти, чтобы оставить комментарий.
jtest.ru
HTML, CSS, JavaScript, JQuery, PHP, MySQL
SQL для начинающих. Часть 2
Представляю Вашему вниманию вольный перевод статьи SQL for Beginners Part 2
Для каждого веб-разработчика важно уметь взаимодействовать с базами данных. Во второй части мы продолжаем изучение языка SQL и применяем свои навыки к СУБД MySQL. Мы познакомимся с индексами, типами данных и более сложными запросами.
Что вам нужно
Обратитесь, пожалуйста, к разделу «Что вам нужно» первой части, которая находится здесь.
Если Вы хотите выполнять приведенные примеры на своем сервере, сделайте следующее:
Индексы
Индексы (или ключи) обычно используются для повышения скорости выполнения операторов, которые выбирают данные (такие как SELECT) из таблиц.
Они являются важной частью хорошей архитектуры баз данных, сложно их отнести к «оптимизации». Обычно индексы добавляются изначально, но могут быть добавлены позднее с помощью запроса ALTER TABLE.
Основные доводы в пользу индексации столбцов базы данных:
Первичный ключ (PRIMARY KEY)
Почти у всех таблиц есть первичный ключ, обычно это целое число с опцией автоинкремента (AUTO_INCREMET).
Если Вы вспомните, в первой части, мы создали поле user_id в таблице пользователей (users) и он был первичным ключом. В веб-приложениях, мы можем обратиться ко всем пользователям по их номеру id.
Значения, хранящиеся в столбце первичный ключей, должны быть уникальными. В каждой таблице может быть не более одного столбца с первичными ключами.
Давайте рассмотрим простой запрос, который создает таблицу для хранения списка штатов США:
Можно записать его так:
Уникальный ключ (UNIQUE)
Поскольку мы предполагаем, что имя штата уникально, нам надо немного изменить предыдущий запрос:
По-умолчанию индекс будет назван также как и столбец. Если Вы хотите добавить произвольное имя для индекса, используйте такой запрос:
Теперь индекс называется «state_name», а не «name».
Индекс (INDEX)
Допустим мы хотим добавить столбец для хранения года, в котором каждый штат вошел в состав США.
Я добавил столбец «join_year» и проиндексировал его. Этот тип индекса не накладывает ограничение на уникальность.
Вы можете использовать KEY вместо INDEX.
Подробнее о производительности
Добавление индекса снижает производительность запросов INSERT и UPDATE. Потому что каждый раз при вставке новых данных в таблицу, данные индекса тоже обновляются, что требует выполнения дополнительной работы. Увеличение производительности для запросов SELECT обычно перевешивают эти недостатки. Однако не надо добавлять индексы к каждому столбцу таблицы, не подумав о том какие запросы будут выполняться.
Пример таблицы
Перед тем как идти дальше, я хотел бы создать таблицу с некоторыми данными.
Это будет список штатов с датами их присоединения (дата ратификации штатом Конституции США и принятия его в Союз) и текущей численностью населения. Вы можете скопировать этот листинг в консоль MySQL:
GROUP BY: Группировка данных
Предложение GROUP BY группирует результирующий набор данных. Пример:
Итак, что произошло? В таблице у нас 50 строк, а запрос вернул только 34. Так произошло, потому что результат сгруппирован по столбцу «join_year». Другими словами, мы видим только уникальные позиции столбца join_year. Некоторые штаты имеют одно и тоже значение join_year, поэтому мы получили менее 50 строк.
Например, присутствует только одно значение с 1787 годом, хотя в эту группу входят 3 штата:
Вот эти три штата, но только название штата Delaware показано после выполнения запроса с группировкой. На самом деле мы могли бы увидеть название любого из трех штатов. Тогда в чем же смысл использования предложении GROUP BY?
Оно было бы бесполезно без использования совместно с агрегирующими функциями, например с COUNT(). Давайте посмотрим что делает эта функция и как с ее помощью извлечь полезные данные.
COUNT(*): Подсчет строк
Это, пожалуй, наиболее часто используемая функция в запросах совместно с предложением GROUP BY. Она возвращает количество строк в каждой группе.
Например, мы можем использовать ее для подсчета количества штатов для каждого join_year:
Группировка всего
Если вы используете GROUP BY с агрегирующей функцией, но не уточняете предложение GROUP BY, то результат будет помещен в одну группу.
Количество строк во всей таблице:
Количество строк удовлетворяющие условию WHERE:
MIN(), MAX() и AVG()
Эти функции возвращают минимальное, максимальное и среднее значения:
GROUP_CONCAT()
Эта функция объединяет все значения внутри группы в одну строку с разделителем.
В первом запросе с GROUP BY только один штат для каждого года. Используя данную функцию, мы можем видеть все имена в каждой группе:
Вы можете использовать эту функцию для сложения численных значений.
IF() & CASE: Управление потоком выполнения
Как и в других языках программирования, в SQL есть конструкции для управления потоком выполнения.
Вот более практический пример использования функции SUM():
Результат выполнения запроса:
CASE (Выбор)
CASE работает подобно условию switch-case в других языках программирования.
Предположим, что мы хотим разделить штаты на три возможные категории.
Как Вы видите, мы можем применить GROUP BY для значений, возвращаемых после условия CASE. Вот что произошло:
HAVING: Условие для скрытых полей
Условие HAVING применяется для «скрытых» полей, таких как результат, возвращаемый агрегирующей функцией. Обычно оно используется совместно с GROUP BY.
Для примера давайте рассмотрим запрос, который мы использовали для подсчета штатов по году их присоединения:
Теперь, скажем, что нас интересуют только строки с количеством больше 1. Мы не можем использовать условие WHERE для этого:
На помощь приходит HAVING:
Помните, что такая возможность доступна не во всех СУБД.
Подзапросы
Можно использовать результат одного запроса в другом.
В этом примере мы получим штат с наибольшим населением:
Внутренний запрос возвращает наибольшую численность населения из всех штатов. Во внешнем запросе снова происходит поиск в таблице этого значения.
Наверное вы подумаете, что это плохой пример, и я с вами соглашусь. Такой запрос можно записать эффективнее так:
Хотя результаты и одинаковы, есть большое различие в этих запросах. Следующий пример это демонстрирует более наглядно.
В этом примере мы получим штат, который был присоединен к Союзу последним:
Возможно Вы захотите использовать несколько резульатов, возвращаемых внутренним запросом.
Следущий запрос находит года, в которых было присоединено к Союзу сразу несколько штатов, и возвращает их список:
Подробнее о подзапросах
Подзапросы могуть быть очень сложными, поэтому я не буду слишком углубляться в них в этой статье. Если Вы хотите почитать о них более подробно, обратитесь к руководству по MySQL.
Очень часто подзапросы ведут к значительному снижению производительности, поэтому используйте их с осторожностью.
UNION: Совмещение данных
Используя запрос UNION, можно объединять результаты нескольких напросов SELECT.
В этом примере объединяются штаты, название которых начинается с буквы «N», со штатами с большим населением:
Обратите внимание, что штат New York принадлежит крупным и начинается с буквы «N». Однако в списке он встречается один раз, т.к. дубликаты удаляются автоматически.
Так же прелесть запросов UNION заключается в том, что их можно использовать для объединения запросов к разным таблицам.
Например, у нас есть таблицы employees (сотрудники), managers (менеджеры) и customers (клиенты). В каждой таблице есть поле с адресом электронной почты. Если мы хотим получить все E-mail адреса в одном запросе, то можем поступить следующим образом:
Выполнив этот запрос, мы получим почтовые адреса всех сотрудников и менеджеров, и только тех клиентов, которые подписаны на рассылку.
INSERT Продолжение
Мы уже говорили о запросе INSERT в предыдущей статье. После того как мы рассмотрели индексы, мы можем поговорить о дополнительных возможностях запросов INSERT.
Это наиболее часто используемое условие. Сначала запрос пытается выполнить INSERT, и если запрос терпит неудачу в следствии дублирования первичного (PRIMARY KEY) или уникального (UNIQUE KEY) ключа, то выполняется запрос UPDATE.
Давайте сначала создадим тестовую таблицу.
Это таблица для хранения продуктов. Поле «stock» хранит количество продуктов доступных на складе.
Теперь попробуем вставить уже существующее значение в таблицу и посмотрим что произойдет.
Мы получили ошибку.
Допустим, мы получили новую хлебопекарню и хотим обновить базу данных, но не знаем есть ли уже запись в базе данных. Мы можем сначала проверить существование записи, а потом выполнить другой запрос для вставки. Или можно выполнить все в одном простом запросе:
REPLACE INTO
Работает также как и INSERT, но с одной важной особенностью. Если запись уже существует, то она удаляется, а потом выполняется запрос INSERT, при этом мы не получим никаких сообщений об ошибке.
Обратите внимание, т.к. вставляется совершенно новая строка, поле автоинкремента увеличивается на единицу.
INSERT IGNORE
Это способ предотвращения появления ошибки о дублировании, прежде всего для того, чтобы не останавливать выполнение приложения. Может понадобится вставить новую строку без вывода каких-либо ошибок, если даже произошло дублирование.
Нет никаких ошибок и обновленных строк.
Типы данных
Каждый столбец в таблице должен быть определенного типа. Мы уже использовали типы INT, VARCHAR и DATE, но не останавливались на них подробно. Также мы рассмотрим еще несколько типов данных.
Начнем с числовых типов данных. Я разделяю из на две группы: Целые и дробные.
Целые
Столбец с типом целые может хранить только натуральные числа (без десятичной точки). По-умолчанию они могут быть положительными или отрицательными. Если выбрана опция UNSIGNED, то могут храниться только положительные числа.
MySQL поддерживает 5 типов целых чисел разных размеров и диапазонов:
Дробные числовые типы данных
Эти типы могут хранить дробные числа: FLOAT, DOUBLE и DECIMAL.
FLOAT занимает 4 байта, DOUBLE занимает 8 байт и аналогичен предыдущему. DOUBLE более точный.
Например, DECIMAL(13,4) имеет 9 знаков до запятой и 4 после.
Строковые типы данных
По названию можно догадаться, что в них можно хранить строки.
CHAR(N) может хранить N символов и имеет фиксированную величину. Например, CHAR(50) должен всегда содержать 50 символов в каждой строке во всем столбце. Максимально возможное значениен 255 символов
Разновидности типа TEXT больше подходят для длинных строк. TEXT имеет ограничение в 65535 символов, MEDIUMTEXT в 16.7 миллионов, и LONGTEXT в 4.3 миллиарда символов. MySQL обычно хранит их в отдельных хранилищах на сервере, для того что бы главное хранилище было по возможности меньше и быстрее.
Тип DATE (Дата)
Тип DATE хранит даты и показывает их в формате «YYYY-MM-DD», но не хранит информацию о времени. Имеет диапазон от 1001-01-01 до 9999-12-31.
Тип DATETIME содержит дату и время и имеет формат «YYYY-MM-DD HH:MM:SS». Имеет диапазон от «1000-01-01 00:00:00» до «9999-12-31 23:59:59». Занимает 8 байт.
TIMESTAMP работает как DATETIME с некоторыми отличаями. Он занимает только 4 байта и имеет диапазон «1970-01-01 00:00:01» UTC до «2038-01-19 03:14:07» UTC. Например, он не очень подходит для хранения дат рождения.
Тип TIME хранит только время, а YEAR только год.
Другое
Другие типы данных, поддерживаемые MySQL. Посмотреть их список можно здесь. Так же обратите внимание на размеры хранимых данных каждого типа.
Заключение
Ограничения уникальности и проверочные ограничения
В этом разделе содержатся следующие подразделы.
Ограничения UNIQUE
Ограничения представляют собой правила, которые принудительно применяются в Компонент SQL Server Database Engine от имени пользователя. Например, ограничения UNIQUE можно использовать для обеспечения того, чтобы в указанные столбцы, не входящие в состав первичного ключа, не вводились повторяющиеся значения. Хотя уникальность значений ограничения UNIQUE и PRIMARY KEY гарантируют в равной степени, в случае, когда необходимо обеспечить уникальность в столбце или комбинации столбцов, которые не являются первичными ключевыми, вместо ограничения PRIMARY KEY следует использовать ограничение UNIQUE.
В отличие от PRIMARY KEY, ограничения UNIQUE допускают значение NULL. Однако, как и всякое другое значение столбца с ограничением UNIQUE, NULL может встречаться только один раз. На ограничение UNIQUE могут ссылаться ограничения FOREIGN KEY.
При добавлении ограничения UNIQUE на уже существующий столбец или группу столбцов в таблице, компонент Компонент Database Engine по умолчанию проверяет уникальность всех существующих значений в указанных столбцах. При попытке добавить ограничение UNIQUE к столбцу, содержащему повторяющиеся значения, компонент Компонент Database Engine возвращает ошибку, а ограничение не добавляется.
Компонент Компонент Database Engine автоматически создает индекс UNIQUE, что обеспечивает выполнение требований уникальности значений для ограничений UNIQUE. Поэтому, при попытке вставки в таблицу строки с повторяющимися данными, компонент Компонент Database Engine выдает сообщение об ошибке, в котором сообщается о нарушении ограничения UNIQUE, а строка в таблицу не вставляется. Для обеспечения выполнения ограничения UNIQUE по умолчанию создается уникальный некластеризованный индекс, если явно не указано создание кластеризованного индекса.
Ограничения CHECK
Проверочные ограничения подобны ограничениям внешнего ключа, так как они управляют значениями, которые присваиваются столбцу. Однако они по-разному определяют допустимые значения: ограничения внешнего ключа получают список допустимых значений из другой таблицы, а проверочные ограничения определяют допустимые значения по логическому выражению.
Ограничения, которые включают явное или неявное преобразование данных, могут вызывать ошибки в операциях такого рода. Например, ограничения, заданные для таблиц, которые являются исходными при переключении секций, могут приводить к ошибкам при использовании оператора ALTER TABLE. SWITCH. Следует избегать преобразования типов данных в определениях ограничений.
Ограничения проверочных ограничений
Проверочные ограничения отклоняют значения, вычисляемые в FALSE. Поскольку значения NULL вычисляются как UNKNOWN, то их наличие в выражениях может переопределить ограничение. Например, предположим, что на столбец MyColumn типа int установлено следующее ограничение: MyColumn может содержать только значение 10 (MyColumn=10). При вставке значения NULL в столбец MyColumn компонент Компонент Database Engine вставит значение NULL и не возвратит ошибку.
Проверочное ограничение возвращает TRUE, если для проверяемого условия в любой строке таблицы отсутствует значение FALSE. Проверочное ограничение работает на уровне строки. Если в только что созданной таблице отсутствуют строки, то любое проверочное ограничение на эту таблицу считается допустимым. В результате могут возвращаться неожиданные результаты, как в следующем примере.
Ограничение CHECK показывает, что в таблице CheckTbl должна быть хотя бы одна строка. Однако поскольку в таблице нет ни одной строки, над которой можно было бы произвести проверку ограничения, инструкция ALTER TABLE завершается успешно.
Инструкция DELETE выполняется успешно, даже если ограничение CHECK определяет, что в таблице CheckTbl должна быть хотя бы 1 строка.
Связанные задачи
Если таблица опубликована для репликации, то изменения схемы следует проводить при помощи инструкции языка Transact-SQL ALTER TABLE или объектов SMO. При изменении схемы с помощью конструктора таблиц или конструктора диаграмм баз данных конструктор пытается удалить и затем вновь создать таблицу. Но поскольку удалять опубликованные объекты нельзя, изменения схемы не будут применены.
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 и управлением. Подать заявку на выступление можно здесь.