Что такое уникальный ключ
Время чтения: 12 мин.
База данных (БД) — это организованная структура, предназначенная для хранения, изменения и обработки информации. Это структурированное хранилище данных.
Какие типы БД знаешь?
Реляционные (SQL)
Характерны тем, что данные могут быть связанными между собой с помощью отношений (relation — связь, отношение), например — значение одной колонки может ссылаться на какую-либо колонку в другой таблице (aka “внешние ключи”). Данные хранятся в виде набора таблиц, состоящих из столбцов и строк. В каждом столбце (column) хранятся данные определенного типа.
Схема таблиц объявляется при её создании.
Примеры: MySQL (C++) (или её форк MariaDB), PostgreSQL (C), SQLite (C)
Нереляционные (NoSQL)
NoSQL — Not Only Structured Query Language
Обладают гибкими схемами, т.е. объявлять структуру помещаемых данных заранее часто не является обязательным условием.
Key/Value
Хранилища предоставляют доступ к данным, которые хранятся по уникальным ключам в “плоском” представлении. Чтобы запросить данные, нужно знать их ключ. Часто используется хранение данных в виде JSON-объектов, но для СУБД это просто некоторый случайный набор байт (blob-объект).
Примеры: Redis (C), KeyDB (C++), memcached (C), etcd (Go)
Документные (документ-ориентированные)
Используют базовую семантику доступа и поиска хранилищ ключей и значений, часто имеют структуру дерева (иногда леса). Такие БД также используют ключ для уникальной идентификации данных. Разница между хранилищами “Key/Value” и документными БД заключается в том, что вместо хранения blob-объектов, документ-ориентированные базы хранят данные в структурированных форматах – JSON , BSON или XML .
- Документы могут быть организованы (сгруппированы) в коллекции (их можно считать отдалённым аналогом таблиц реляционных СУБД)
- База данных не предписывает определенный формат или схему
- Каждый документ может иметь свою внутреннюю структуру
- Документные БД являются хорошим выбором для быстрой разработки
- В любой момент можно менять свойства данных, не изменяя структуру или сами данные
Примеры: MongoDB (C++), RethinkDB (C++), Elasticsearch (Java), Aerospike (C)
Колоночные
Внешне похожи на реляционные БД (хранят данные, используя строки и столбцы), но с иной связью между элементами. Данные группируются не по строкам, а по столбцам. В ней “соседними” являются не данные из двух столбцов одной и той же строки, а данные из одного и того же столбца, но из разных строк.
Особенностью является высокая скорость и гибкость выполнения сложных запросов. Действительно, в “строчной” СУБД при поиске и считывании значений сканируется вся таблица по строкам и столбцам, а затем извлекаются строки целиком, даже в том случае, если нужно только одно значение каждой из них. Колоночные базы данных позволяют искать значения по отдельным столбцам и извлекать только те значения, которые требуются.
Примеры: ClickHouse (C++), Cassandra (Java)
Графовые
Вместо сопоставления связей с таблицами и внешними ключами, графовые базы данных устанавливают связи, используя узлы, рёбра и свойства. Они представляют данные в виде отдельных узлов, которые могут иметь любое количество связанных с ними свойств.
Примеры: Neo4j (Java), Dgraph (Go), RedisGraph (C)
Time series (временны́х рядов)
Созданы для сбора и управления элементами, меняющимися с течением времени. Большинство таких БД организованы в структуры, которые записывают значения для одного элемента (например, временная метка и значение температуры процессора). В таблице может быть несколько метрик. Оптимизированы для быстрой записи данных.
Примеры: Prometheus (Go), InfluxDB (Go)
Что такое CAP-теорема (теорема Брюера)?
Утверждение о том, что в любой реализации распределённых вычислений возможно обеспечить не более двух из трёх следующих свойств:
- Согласованность данных ( consistency ) — как только мы успешно записали данные в наше распределенное хранилище, любой клиент при запросе получит эти последние данные
- Доступность ( availability ) — в любой момент клиент может получить данные из нашего хранилища, или получить ответ об их отсутствии, если их никто еще не сохранял
- Устойчивость к разделению ( partition tolerance ) — потеря сообщений между компонентами системы (возможно даже потеря всех сообщений) не влияет на работоспособность системы
С точки зрения теоремы CAP, распределённые системы в зависимости от пары практически поддерживаемых свойств из трёх возможных распадаются на три класса:
- CA ( consistency + availability ) — во всех узлах данные согласованы и обеспечена доступность, при этом она жертвует устойчивостью к распаду на секции
- CP ( consistency + partition tolerance ) — в каждый момент обеспечивает целостный результат и способна функционировать в условиях распада, но достигает этого в ущерб доступности — может не выдавать отклик на запрос
- AP ( availability + partition tolerance ) — не гарантируется целостность, но при этом выполнены условия доступности и устойчивости к распаду на секции
Что такое свойство ACID в базе данных?
ACID используется для обеспечения надежной обработки транзакций данных в СУБД и означает:
- Атомарность ( Atomicity ) — гарантирует, что транзакция будет полностью выполнена или потерпит неудачу, где транзакция представляет одну логическую операцию данных (“всё или ничего”)
- Согласованность или консистентность ( Consistency ) — гарантирует, что данные должны соответствовать всем правилам валидации
- Изолированность ( Isolation ) — контроль механизма параллельного изменения данных
- Долговечность или стойкость ( Durability ) — если транзакция была подтверждена ( COMMIT ), произошедшие в рамках транзакции изменения сохранятся независимо от того, что может встать у них на пути (например, потеря питания, сбой или ошибки любого рода)
В базах данных, следующих принципу ACID, данные остаются целостными и консистентными, несмотря на любые ошибки.
NoSQL базы данных часто предназначены для обеспечения высокой доступности в кластере, а обычно это означает, что в некоторой степени жертвуют согласованностью ( consistency ) и/или стойкостью ( durability ). Хотя, разработчики такие как MarkLogic, OrientDB и Neo4j предлагают ACID-совместимые СУБД.
Вопросы по SQL
Из каких подмножеств состоит SQL?
- DDL ( Data Definition Language , язык описания данных) -позволяет выполнять различные операции с базой данных, такие как CREATE (создание), ALTER (изменение) и DROP (удаление объектов)
- DML ( Data Manipulation Language , язык управления данными) -позволяет получать доступ к данным и манипулировать ими, например, вставлять INSERT , обновлять UPDATE , удалять DELETE и извлекать данные SELECT из базы данных
- DCL ( Data Control Language , язык контролирования данных) -позволяет контролировать доступ к базе данных; примеры — GRANT (предоставить права), REVOKE (отозвать права)
Что подразумевается под таблицей и полем в SQL?
Таблица — организованный набор данных в виде строк и столбцов. Поле — это столбцы в таблице.
В чем разница между операторами DELETE и TRUNCATE ?
- Delete удаляет строку в таблице, а truncate удаляет все строки
- После delete возможно откатить изменения, а после truncate как правило нет
- Truncate работает быстрее
Что такое соединения ( JOIN ) в SQL?
Это объединение строк из двух (или более) таблиц на основе связанного между ними столбца. Примеры JOIN-ов:
- INNER JOIN (внутреннее соединение) — пересечение двух таблиц, то есть строки, общие для каждой из них(пример: select * from a INNER JOIN b on a.a = b.b; )
- LEFT JOIN (левое соединение) — все строки из первой (левой) таблицы плюс все строки второй таблицы, имеющие совпадение со строками из первой таблицы (пример: select * from a LEFT OUTER JOIN b on a.a = b.b; )
- RIGHT JOIN (правое соединение) — все строки из второй (правой) таблицы плюс все строки первой таблицы, имеющие совпадение со строками из второй таблицы (пример: select * from a RIGHT OUTER JOIN b on a.a = b.b; )
- FULL JOIN (полное соединение) — полное соединение обеих таблиц (т.е. все строки из первой и второй таблиц); если соответствия нет, то значение null (пример: select * from a FULL OUTER JOIN b on a.a = b.b; )
В чем разница между типом данных CHAR и VARCHAR в SQL?
И CHAR , и VARCHAR служат символьными типами данных, но VARCHAR используется для строк символов переменной длины, тогда как CHAR используется для строк фиксированной длины. Например, CHAR(10) может хранить только 10 символов и не сможет хранить строку любой другой длины, тогда как VARCHAR(10) может хранить строку любой длины до 10, т.е. например 6, 8 или 2.
Что такое первичный ключ ( Primary key )?
Первичный ключ — столбец (или набор столбцов), которые однозначно идентифицируют каждую (одну) строку в таблице. Нулевые ( null ) значения не допускаются. Автоматически индексируются.
Что такое ограничения ( Constraints )?
Ограничения ( constraints ) используются для указания ограничения на тип данных таблицы. Они могут быть указаны при создании или изменении таблицы. Пример ограничений:
- NOT NULL — значение не может быть null
- CHECK — произвольные проверки на значение, например constraint uuid_is_valid check (is_uuid(uuid)) проверяет на валидность UUID идентификатор, а constraint login_min_length check (char_length(login) >= 3) на минимальную длину строки поля логина
- DEFAULT — устанавливает значение по умолчанию для поля в колонке
- UNIQUE — обязывает значение бить уникальным в рамках таблицы
- PRIMARY KEY — объявляет первичный ключ
- FOREIGN KEY — объявляет внешний ключ (связывает таблицы отношением), например constraint user_uuid_foreign_key foreign key (user_uuid) references users (uuid) on update cascade on delete cascade обязывает содержать значение в user_uuid только для существующей записи в таблице users и автоматически обновится если оно будет изменено в таблице users , а так же заставит запись удалиться при удалении записи о пользователе
Что такое уникальный ключ ( Unique key )?
Уникальный ключ однозначно идентифицирует одну строку в таблице (таблица не может содержать дубликатов). В одной таблице может быть несколько уникальных ключей. Возможны нулевые ( null ) значения.
Что такое внешний ключ ( Foreign key )?
Внешний ключ поддерживает ссылочную целостность, обеспечивая связь между данными в двух таблицах. Внешний ключ в дочерней таблице ссылается на первичный ключ в родительской таблице. Ограничение внешнего ключа предотвращает действия, которые разрушают связи между дочерней и родительской таблицами.
Что подразумевается под целостностью данных?
Целостность данных определяет точность, а также согласованность данных, хранящихся в базе данных. Она также определяет ограничения целостности для обеспечения соблюдения бизнес-правил для данных, когда они вводятся в приложение или базу данных.
Например, вес детали должен быть положительным; количество знаков в телефонном номере не должно превышать 15; возраст родителей не может быть меньше возраста их биологического ребёнка и так далее.
Какие уровни изолированности транзакций можешь назвать?
Транзакция — это N (N≥1) запросов к БД, которые выполнятся успешно все вместе или не выполнятся вовсе. Изолированность же транзакции показывает то, насколько сильно влияют друг на друга параллельно выполняющиеся транзакции.
- Read uncommitted (самая плохая согласованность данных, но самая высокая скорость выполнения) — каждая транзакция видит незафиксированные изменения другой транзакции (феномен “грязного чтения”). На данном уровне нельзя использовать данные, на основе которых делаются важные для приложения выводы и критические решения
- Read committed (используется по умолчанию в PostgreSQL) — параллельно исполняющиеся транзакции видят только зафиксированные изменения из других транзакций (защита от “грязного чтения”). Т.е. для транзакции, работающей на этом уровне, запрос SELECT (без предложения FOR UPDATE / SHARE ) видит только те данные, которые были зафиксированы до начала запроса; она никогда не увидит незафиксированных данных или изменений, внесённых в процессе выполнения запроса параллельными транзакциями. Этот уровень подвержен феномену неповторяющегося чтения
- Repeatable read (используется по умолчанию в MySQL) — уровень, позволяющий предотвратить феномен неповторяющегося чтения. Т.е. мы не видим в исполняющейся транзакции измененные и удаленные записи другой транзакцией. Но все еще видим вставленные записи из другой транзакции. Чтение фантомов никуда не уходит.
- Serializable (самая низкая скорость выполнения и самая высокая согласованность) — транзакции ведут себя как будто ничего более не существует, никакого влияния друг на друга нет. В классическом представлении этот уровень избавляет от эффекта чтения фантомов
Что вы подразумеваете под денормализацией?
Денормализация — техника, которая используется для преобразования из высших к низшим нормальным формам. Она помогает разработчикам баз данных повысить производительность всей инфраструктуры, поскольку вносит избыточность в таблицу. Она добавляет избыточные данные в таблицу, учитывая частые запросы к базе данных, которые объединяют данные из разных таблиц в одну таблицу.
Напишите SQL-запрос для отображения текущей даты?
Есть несколько способов (проверял на MySQL 8.0):
- select CURDATE(); select CURRENT_DATE вернёт 2022-03-16
- select NOW() вернёт 2022-03-16 12:21:10 , т.е. дату с текущим временем
- select UNIX_TIMESTAMP() вернёт 1647433317 , т.е. временную метку в UNIX-формате
Индексы
Индекс создает отдельную структуру для индексируемого поля и, следовательно, позволяет быстрее получать данные (аналогично тому, как указатель в книге помогает вам быстро найти необходимую информацию).
В чем разница между кластеризованным и не кластеризованным индексами в SQL?
Различия между кластеризованным и не кластеризованным индексами в SQL:
- Кластеризованный индекс используется для простого и быстрого извлечения данных из базы данных, тогда как чтение из не кластеризованного индекса происходит относительно медленнее
- Кластеризованный индекс изменяет способ хранения записей в базе данных — он сортирует строки по столбцу, который установлен как кластеризованный индекс, тогда как в не кластеризованном индексе он не меняет способ хранения, но создает отдельный объект внутри таблицы, который указывает на исходные строки таблицы при поиске
- Одна таблица может иметь только один кластеризованный индекс, тогда как не кластеризованных у нее может быть много
Какие типы индексов знаешь?
PostgreSQL поддерживает несколько типов индексов: B-Tree , Hash , GiST , SP-GiST , GIN и BRIN . Для разных типов индексов применяются разные алгоритмы, ориентированные на определённые типы запросов. По умолчанию команда CREATE INDEX создаёт индексы типа B-Tree , эффективные в большинстве случаев.
-
B-Tree (self-balancing tree data structure) — хорошо работают в условиях на равенство и в проверках диапазонов с данными, которые можно отсортировать в некотором порядке (операторы < , <= , = , >= , > ). Также оптимизатор может использовать индексы этого типа в запросах с операторами сравнения по шаблону LIKE и
, если этот шаблон определяется константой и он привязан к началу строки — например, col LIKE ‘foo%’ или col
Как устроен B-Tree индекс?
Семейство B-Tree (B значит balanced) индексов — это наиболее часто используемый тип индексов, организованных как сбалансированное дерево, упорядоченных ключей. Они поддерживаются практически всеми СУБД как реляционными, так нереляционными, и практически для всех типов данных.
19) Первичный ключ против уникального ключа
Ограничение первичного ключа – это столбец или группа столбцов в таблице, которые однозначно определяют каждую строку в этой таблице. Первичный ключ не может быть дубликатом, то есть одно и то же значение не может появляться в таблице более одного раза.
Таблица должна иметь более одного первичного ключа. Первичный ключ может быть определен на уровне столбца или таблицы. Если вы создаете составной первичный ключ, он должен быть определен на уровне таблицы.
В этом уроке вы узнаете:
Что такое уникальный ключ?
Уникальный ключ – это группа из одного или нескольких полей или столбцов таблицы, которые однозначно идентифицируют запись базы данных.
Уникальный ключ такой же, как первичный ключ, но он может принимать одно нулевое значение для столбца таблицы. Он также не может содержать одинаковые значения. На уникальные ограничения ссылается внешний ключ других таблиц.
Зачем использовать первичный ключ?
Вот важные причины для использования первичного ключа:
- Основная цель первичного ключа – идентифицировать каждую запись в таблице базы данных.
- Вы можете использовать первичный ключ, если не разрешаете кому-либо вводить нулевые значения.
- Если вы удалите или обновите запись, то указанное вами действие будет предпринято для обеспечения целостности данных базы данных.
- Выполните операцию ограничения для отклонения операции удаления или обновления для родительской таблицы.
- Данные организуются в последовательности кластеризованного индекса всякий раз, когда вы физически организуете таблицу СУБД.
Зачем использовать уникальный ключ?
Вот важные причины для использования уникального ключа:
- Цель уникального ключа – убедиться, что информация в столбце для каждой записи таблицы уникальна.
- Когда вы позволяете пользователю ввести нулевое значение.
- Уникальный ключ используется, потому что он создает некластеризованный индекс по умолчанию.
- Уникальный ключ может быть использован, когда вам нужно сохранить нулевые значения в столбце.
- Когда одно или несколько полей / столбцов таблицы, однозначно идентифицируют запись в таблице базы данных.
Особенности первичного ключа
Вот важные особенности первичного ключа:
- Первичный ключ реализует целостность объекта таблицы.
- Вы можете сохранить только один основной элемент в таблице.
- Первичный ключ содержит один или несколько столбцов таблицы.
- Столбцы определены как не нулевые.
Особенности Уникального ключа
Вот важные особенности уникального ключа:
- Вы можете определить более одного уникального ключа в таблице.
- По умолчанию уникальные ключи находятся в некластеризованных уникальных индексах.
- Он состоит из одного или нескольких столбцов таблицы.
- Столбец таблицы может быть нулевым, но предпочтительным является только один нулевой на столбец.
- На ограничение уникальности можно легко ссылаться с помощью ограничения внешнего ключа.
Пример создания первичного ключа
В следующем примере описано, что существует таблица с именем student. Он содержит пять атрибутов: 1) StudID, 2) Roll No, 3) Имя, 4) Фамилия и 5) Электронная почта.
Атрибут Roll No никогда не может содержать повторяющегося или нулевого значения. Это потому, что каждый студент, зачисленный в университет, может иметь уникальный номер броска. Вы можете легко идентифицировать каждый ряд таблицы по номеру ролика студента. Так что это считается первичным ключом.

Пример создания уникального ключа
Рассмотрим ту же таблицу учеников с атрибутами: 1) StudID, 2) Roll No, 3) Имя, 4) Фамилия и 5) Электронная почта.
Идентификатор студента может иметь уникальное ограничение, при котором записи в столбце Идентификатор студента могут быть уникальными, поскольку каждый студент университета должен иметь уникальный идентификационный номер. В случае, если студент меняет университет, в этом случае у него или нее не будет никакого студенческого билета. Запись может иметь нулевое значение, так как в ограничении уникального ключа допускается только одно нулевое значение.
Топ вопросы по SQL
Реляционные базы данных являются одними из наиболее часто используемых баз данных по сей день, и поэтому навыки работы с SQL для большинства должностей являются обязательными. В этой статье с вопросами по SQL с собеседований я познакомлю вас с наиболее часто задаваемыми вопросами по SQL (Structured Query Language — язык структурированных запросов). Эта статья является идеальным руководством для изучения всех концепций, связанных с SQL, Oracle, MS SQL Server и базой данных MySQL.
Что подразумевается под СУБД? Какие существуют типы СУБД?
База данных — структурированная коллекция данных. Система управления базами данных (СУБД) — программное обеспечение, которое взаимодействует с пользователем, приложениями и самой базой данных для сбора и анализа данных. СУБД позволяет пользователю взаимодействовать с базой данных. Данные, хранящиеся в базе данных, могут быть изменены, извлечены и удалены. Они могут быть любых типов, таких как строки, числа, изображения и т. д.
Существует два типа СУБД:
- Реляционная система управления базами данных: данные хранятся в отношениях (таблицах). Пример — MySQL.
- Нереляционная система управления базами данных: не существует понятия отношений, кортежей и атрибутов. Пример — MongoDB.
Из каких подмножеств состоит SQL?
Data Definition Language, DDL (Операторы определения данных)
DDL — это часть SQL, которая служит для определения структуры данных в начальном состоянии, когда база данных только создается. Операторы определения данных используются, главным образом, для создания и реструктуризации объектов базы данных. К этим операторам относятся CREATE, ALTER и DROP.
CREATE служит для создания объектов базы данных, ALTER — для их изменения, DROP — для удаления.
Data Manipulation Language, DML (Операторы манипуляции данными)
DML используется для работы с уже существующими данными, содержащимися в базе данных. С помощью этих операторов пользователи могут получать данные из базы и совершать над ними какие-то манипуляции. К этим операторам относятся SELECT, INSERT, UPDATE, DELETE.
Оператор INSERT позволяет вносить данные в базу данных, SELECT — выбирать их, UPDATE — обновлять их, DELETE — удалять данные из базы.
Data Control Language, DCL (Операторы определения доступа к данным)
DCL используется для контроля доступа к данным в базе данных. Команды DCL обычно служат для создания объектов, имеющих отношение к доступу пользователей к базе, а также к распределению разрешений между пользователями. Для этих операций используются операторы GRANT и REVOKE. Первый служит для выдачи разрешений, а второй — для их отзыва.
Transaction Control Language, TCL (Операторы управления транзакциями)
TCL используется для контроля изменений, осуществленных при помощи DML. Также с помощью TCL происходит объединение операторов в логические транзакции. К операторам управления транзакциями относятся COMMIT, ROLLBACK, SAVEPOINT, BEGIN, TRANSACTION.
В чем разница между операторами DELETE и TRUNCATE?
- Используется для удаления строки в таблице;
- Вы можете восстановить данные после удаления;
- DML-команда;
- Медленнее, чем оператор TRUNCATE;
- Используется для удаления всех строк из таблицы;
- Вы не можете восстановить данные (операции логируются по разному, но в SQL Server есть возможность сделать откат);
- DDL-команда;
- Быстрее;
Что такое соединения в SQL?
Для соединения строк из двух или более таблиц на основе связанного между ними столбца используется оператор JOIN. Он используется для объединения двух таблиц или получения данных оттуда. В SQL есть 4 типа соединения, а именно:
- Inner Join (Внутреннее соединение). В MySQL является наиболее распространенным типом. Оно используется для возврата всех строк из нескольких таблиц, для которых выполняется условие соединения.
- Right Join (Правое соединение). В MySQL используется для возврата всех строк из правой (второй) таблицы и только совпадающих строк из левой (первой) таблицы, для которых выполняется условие соединения.
- Left Join (Левое соединение). В MySQL используется для возврата всех строк из левой (первой) таблицы и только совпадающих строк из правой (второй) таблицы, для которых выполняется условие соединения.
- Full Join (Полное соединение). Возвращает все записи, для которых есть совпадение в любой из таблиц. Следовательно, он возвращает все строки из левой таблицы и все строки из правой таблицы.
В чем разница между типом данных CHAR и VARCHAR в SQL?
И Char, и Varchar служат символьными типами данных, но varchar используется для строк символов переменной длины, тогда как Char используется для строк фиксированной длины. Например, char(10) может хранить только 10 символов и не сможет хранить строку любой другой длины, тогда как varchar(10) может хранить строку любой длины до 10, т.е. например 6, 8 или 2.
Что такое первичный ключ (PRIMARY KEY)?
- Первичный ключ — столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице.
- Однозначно идентифицирует одну строку в таблице.
- Нулевые (Null) значения не допускаются.
PRIMARY KEY — это первичный ключ, который используется в качестве основного ключа и может быть использован для связи с дочерней таблицей, содержащей внешний ключ.
Что такое ограничения (Constraints)?
SQL-ограничения (constraints) указываются при создании или изменении таблицы. Это правила для ограничения типа данных, которые могут храниться в таблице. Действие с данными не будет выполнено, если нарушаются установленные ограничения. Пример ограничений:
- NOT NULL — значение не может быть NULL;
- CHECK — значения столбца должны соответствовать заданным условиям;
- DEFAULT — предоставляет столбцу значения по умолчанию;
- UNIQUE— гарантирует уникальность значений в столбце;
Что такое уникальный ключ (UNIQUE KEY)?
- Однозначно идентифицирует одну строку в таблице.
- Допустимо множество уникальных ключей в одной таблице.
- Допустимы NULL-значения.
Что такое внешний ключ (FOREIGN KEY)?
Внешний ключ или FOREIGN KEY также является атрибутом ограничения и обеспечивает связь двух таблиц. По сути, это поле или несколько полей, которые ссылаются на PRIMARY KEY в родительской таблице.
- Внешний ключ поддерживает ссылочную целостность, обеспечивая связь между данными в двух таблицах.
- Внешний ключ в дочерней таблице ссылается на первичный ключ в родительской таблице.
- Ограничение внешнего ключа предотвращает действия, которые разрушают связи между дочерней и родительской таблицами.
Что подразумевается под целостностью данных?
Целостность данных определяет точность, а также согласованность данных, хранящихся в базе данных. Она также определяет ограничения целостности для обеспечения соблюдения бизнес-правил для данных, когда они вводятся в приложение или базу данных.
В чем разница между кластеризованным и некластеризованным индексами в SQL?
- Кластерный индекс используется для простого и быстрого извлечения данных из базы данных, тогда как чтение из некластеризованного индекса происходит относительно медленнее.
- Кластеризованный индекс изменяет способ хранения записей в базе данных — он сортирует строки по столбцу, который установлен как кластеризованный индекс, тогда как в некластеризованном индексе он не меняет способ хранения, но создает отдельный объект внутри таблицы, который указывает на исходные строки таблицы при поиске.
- Одна таблица может иметь только один кластеризованный индекс, тогда как некластеризованных у нее может быть много.
Напишите SQL-запрос для отображения текущей даты.
В SQL есть встроенная функция GetDate(), которая помогает возвращать текущий timestamp/дату.
Что вы подразумеваете под денормализацией?
Денормализация — техника, которая используется для преобразования из высших к низшим нормальным формам. Она помогает разработчикам баз данных повысить производительность всей инфраструктуры, поскольку вносит избыточность в таблицу. Она добавляет избыточные данные в таблицу, учитывая частые запросы к базе данных, которые объединяют данные из разных таблиц в одну таблицу.
Что такое сущности и отношения?
Сущности: человек, место или объект в реальном мире, данные о которых могут храниться в базе данных. В таблицах хранятся данные, которые представляют один тип сущности. Например — база данных банка имеет таблицу клиентов для хранения информации о клиентах. Таблица клиентов хранит эту информацию в виде набора атрибутов (столбцы в таблице) для каждого клиента.
Отношения: отношения или связи между сущностями, которые имеют какое-то отношение друг к другу. Например — имя клиента связано с номером учетной записи клиента и контактной информацией, которая может быть в той же таблице. Также могут быть отношения между отдельными таблицами (например, клиент к счетам).
Что такое индекс?
Индексы помогают ускорить поиск в базе данных. Если в выражении WHERE не задан индекс никакого столбца, SQL-сервер в поиске соответствия будет проверять каждую строку во всей таблице. Если у вас много данных, эта операция может оказаться довольно медленной.
Индексы используются для поиска всех строк, совпадающих по каким-то столбцам, а затем уже в этих выборках ведется поиск нужных данных.
Опишите различные типы индексов.
Простые индексы. Создаются только для одного столбца таблицы.
Составные индексы. Создаются для двух или большего количества столбцов таблицы.
Уникальные индексы. Используются для поддержания целостности данных таблицы. Они не дают вставлять в таблицу несколько значений.
Что такое нормализация и каковы ее преимущества?
Нормализация — процесс организации данных, цель которого избежать дублирования и избыточности. Некоторые из преимуществ:
- Лучшая организация базы данных;
- Больше таблиц с небольшими строками;
- Эффективный доступ к данным;
- Большая гибкость для запросов;
- Быстрый поиск информации;
- Проще реализовать безопасность данных;
- Позволяет легко модифицировать;
- Сокращение избыточных и дублирующихся данных;
- Более компактная база данных;
- Обеспечивает согласованность данных после внесения изменений;
Объясните различные типы нормализации.
Существует много последовательных уровней нормализации. Это так называемые нормальные формы. Каждая последующая нормальная форма включает предыдущую. Первых трех нормальных форм обычно достаточно.
- Первая нормальная форма (1NF) — нет повторяющихся групп в строках;
- Вторая нормальная форма (2NF) — каждое неключевое (поддерживающее) значение столбца зависит от всего первичного ключа;
- Третья нормальная форма (3NF) — каждое неключевое значение зависит только от первичного ключа и не имеет зависимости от другого неключевого значения столбца;
В чем разница между командами DROP и TRUNCATE?
Команда DROP удаляет саму таблицу, и нельзя сделать Rollback команды, тогда как команда TRUNCATE удаляет все строки из таблицы.
Что такое свойство ACID в базе данных?
ACID означает атомарность (Atomicity), согласованность (Consistency), изолированность (Isolation), долговечность (Durability). Он используется для обеспечения надежной обработки транзакций данных в системе базы данных.
Атомарность. Гарантирует, что транзакция будет полностью выполнена или потерпит неудачу, где транзакция представляет одну логическую операцию данных. Это означает, что при сбое одной части любой транзакции происходит сбой всей транзакции и состояние базы данных остается неизменным.
Согласованность. Гарантирует, что данные должны соответствовать всем правилам валидации. Проще говоря, вы можете сказать, что ваша транзакция никогда не оставит вашу базу данных в недопустимом состоянии.
Изолированность. Основной целью изолированности является контроль механизма параллельного изменения данных.
Долговечность. Долговечность подразумевает, что если транзакция была подтверждена (COMMIT), произошедшие в рамках транзакции изменения сохранятся независимо от того, что может встать у них на пути (например, потеря питания, сбой или ошибки любого рода).
Что вы подразумеваете под «триггером» в SQL?
Триггер в SQL — особый тип хранимых процедур, которые предназначены для автоматического выполнения в момент или после изменения данных. Это позволяет вам выполнить пакет кода, когда вставка, обновление или любой другой запрос выполняется к определенной таблице.
Какие операторы доступны в SQL?
В SQL доступно три типа оператора, а именно:
- Арифметические операторы;
- Логические операторы;
- Операторы сравнения;
Совпадают ли значения NULL со значениями нуля или пробела?
Значение NULL вовсе не равно нулю или пробелу. Значение NULL представляет значение, которое недоступно, неизвестно, присвоено или неприменимо, тогда как ноль — это число, а пробел — символ.
В чем разница между перекрестным (Cross Join) и естественным (Natural Join) соединением?
Перекрестное соединение создает перекрестное или декартово произведение двух таблиц, тогда как естественное соединение основано на всех столбцах, имеющих одинаковое имя и типы данных в обеих таблицах.
Что такое подзапрос в SQL?
Подзапрос — это запрос внутри другого запроса, в котором определен запрос для извлечения данных или информации из базы данных. В подзапросе внешний запрос называется основным запросом, тогда как внутренний запрос называется подзапросом. Подзапросы всегда выполняются первыми, а результат подзапроса передается в основной запрос. Он может быть вложен в SELECT, UPDATE или любой другой запрос. Подзапрос также может использовать любые операторы сравнения, такие как >, < или =.
Какие бывают типы подзапросов?
Существует два типа подзапросов, а именно: коррелированные и некоррелированные.
- Коррелированный подзапрос: это запрос, который выбирает данные из таблицы со ссылкой на внешний запрос. Он не считается независимым запросом, поскольку ссылается на другую таблицу или столбец в таблице.
- Некоррелированный подзапрос: этот запрос является независимым запросом, в котором выходные данные подзапроса подставляются в основной запрос.
Перечислите способы получить количество записей в таблице?
Для подсчета количества записей в таблице вы можете использовать следующие команды:
Поясните, в чем разница между выражениями HAVING и WHERE?
- Реализовано в строковых операциях;
- Относится к отдельной строке;
- Используется для выборки конкретных данных из определенных строк, согласно заданным условиям;
- Не может содержать агрегатные функции;
- Может использоваться с SELECT, UPDATE и DELETE;
- Выражение GROUP BY идет после выражения WHERE;
- Реализовано в столбцовых операциях;
- Относится к суммированной строке или группам;
- Используется для выборки всех данных и отделения тех из них, которые соответствуют заданным условиям;
- Может содержать агрегатные функции;
- Не может использоваться без оператора SELECT;
- Выражение GROUP BY идет перед выражением HAVING;
Как бы вы нашли вторую по величине зарплату?
Расскажите, чем отличаются SQL и PL/SQL?
- SQL это структурированный язык запросов к базам данных.
- SQL это отдельный запрос, который используется для выполнения команд DML и DDL.
- SQL это декларативный язык, ориентированный на данные.
- Используется главным образом для манипуляций с данными.
- Предоставляет возможность взаимодействия с сервером базы данных.
- Не может содержать в себе код PL/SQL.
- Это язык программирования для баз данных, использующий SQL.
- PL/SQL это блок кодов, используемый для написания всей процедуры или функции.
- PL/SQL это процедурный язык, ориентированный на приложение.
- Используется для создания приложения.
- Не предоставляет возможности взаимодействия с сервером базы данных.
- Может содержать SQL, поскольку сам является расширением SQL.
Как вы понимаете символьные функции?
Символьные функции используются для манипуляций с символами. К ним относятся:
UPPER
Возвращает строку в верхнем регистре. Синтаксис:
LOWER
Возвращает строку в нижнем регистре. Синтаксис:
CONCAT
Используется для конкатениции (объединения) двух строк. Синтаксис:
LENGTH
Используется для получения длины строки. Синтаксис:
Что такое AUTO_INCREMENT?
AUTO_INCREMENT используется в SQL для автоматической генерации уникального номера при каждом добавлении записи в таблицу. Поскольку первичный ключ уникален для каждой записи, мы добавляем это поле в качестве AUTO_INCREMENT поля, таким образом при каждой вставке новой записи номер будет увеличиваться автоматически.
По умолчанию значение AUTO_INCREMENT начинается с 1 и увеличивается на 1 при каждом добавлении новой записи.
Для чего используется ключевое слово ORDER BY?
Для сортировки данных в порядке возрастания (ASC) или убывания (DESC). Пример использования:
Выбираются пользователи, которые будут отсортированы по имени в порядке убывания. Дополните ответ на этот вопрос по SQL тем, что без указания DESC данные были бы отсортированы по умолчанию — в порядке возрастания:
Для чего нужен оператор UNION?
Он используется для объединения полученных данных из двух или более запросов, которые должны иметь одинаковое количество столбцов с одинаковыми типами данных и расположенных в том же порядке. Пример использования:
Как работают подстановочные знаки?
Это специальные символы, которые нужны для замены каких-либо знаков в запросе. Они используются вместе с оператором LIKE, с помощью которого можно отфильтровать запрашиваемые данные.
Какими бывают подстановочные знаки?
- % — заменить ноль или более символов;
- _ — заменить один символ.
Данный запрос позволяет найти данные всех пользователей, имена которых содержат в себе «test».
А в этом случае имена искомых пользователей начинаются на «t», после содержат какой-либо символ и «est» в конце.
Что делают псевдонимы Aliases?
SQL-псевдонимы нужны для того, чтобы дать временное имя таблице или столбцу. Это нужно, когда в запросе есть таблицы или столбцы с неоднозначными именами. В этом случае для удобства в составлении запроса используются псевдонимы. SQL-псевдоним существует только на время запроса. Пример:
Для чего нужен оператор INSERT INTO SELECT?
Данный оператор копирует данные из одной таблицы и вставляет их в другую, при этом типы данных в обеих таблицах должны соответствовать. Пример использования:
Как выбрать записи с нечётными id?
SQL запрос для выбора записей с нечётными id должен выглядеть следующим образом:
Если остаток от деления id на 2 равен нулю, перед нами чётное значение, и наоборот.
Как найти дубли в поле email?
Функция COUNT() возвращает количество строк из поля email. Оператор HAVING работает почти так же, как и WHERE, вот только применяется не для всех столбцов, а для набора, созданного оператором GROUP BY.
При выборке из таблицы прибавьте к дате 1 день.
Функция DATE_ADD() прибавляет к дате заданный промежуток времени. Синтаксис выглядит следующим образом:
Выберите только уникальные имена.
SELECT DISTINCT возвращает разные значения, даже если в выбранном столбце есть дубли.
Найдите в таблице среднюю зарплату работников.
Функция AVG() применяется только к числовым типам данных и возвращает среднее значение по столбцу.
А теперь получите список сотрудников с зарплатой выше средней.
Даны таблицы workers и departments. Найдите все департаменты без единого сотрудника.
Замените в таблице зарплату работника на 1000, если она равна 900, и на 1500 в остальных случаях.
Замена значений — одна из наиболее часто встречаемых задач по SQL. Решить её не сложно:
Оператор UPDATE используется для изменения существующих записей. Но ответы на подобные вопросы с собеседований по SQL должны быть более развёрнутыми. Уточните, что после UPDATE следует указать, какие записи должны быть обновлены. В противном случае обновятся все записи в таблице.
В нашем примере условие задаётся через оператор CASE: если текущая зарплата равна 900, изменяем её на 1000, в остальных случаях — на 1500.
При выборке из таблицы пользователей создайте поле, которое будет включать в себя и имена, и зарплату.
Функция CONCAT() используется для конкатенации (объединения) строк, неявно преобразуя при этом любые типы данных в строки.
Переименуйте таблицу.
ALTER TABLE first_table RENAME second_table;
С помощью оператора ALTER TABLE можно добавлять, удалять, изменять столбцы, а также изменять название таблицы.
Что вернет условие 2 <> NULL?
Сравнение с NULL — это, наверное, первый подводный камень, на который натыкаются люди при работе с базой данных. Вопреки привычной логике условие:
возвращает ложь (FALSE), как впрочем и условие:
Дело здесь в том, что тип значения NULL в SQL имеет несколько другой оттенок значения, чем в прикладных языках программирования. Если в С-подобных языках NULL значит отсутствие какого-то значения, то в SQL он значит лишь то, что мы не знаем этого значения. По этой причине любое сравнение с NULL возвращает ложь.
Возвращаясь к сути вопроса, мы не можем сказать «Два не равно NULL» потому, что мы не знаем значения справа от знака неравенства, а там как раз может оказаться двойка.
Что вернет условие 3 NOT IN (1, 2, NULL)?
Здесь та же история, что и в предыдущем случае. Условие:
возвращает ложь (FALSE), как и условие:
Причина этого заключается в особенностях работы оператора IN. Проверяя, что определенное значение входит в коллекцию, оператор IN просто сравнивает это значение с каждым элементом коллекции.
это то же самое, что и
В случае с NOT IN условие:
это то же самое, что и
Как мы знаем из предыдущего примера, 3 <> NULL возвращает ложь, а значит и все условие:
тоже будет ложным.
Почему не выполнится этот запрос?
Запрос не выполнится из-за обращения к псевдониму year_of_birth в выражении WHERE. Дело в том, что псевдонимы полей в SQL используются для форматирования данных уже полученных из базы. Поэтому их можно использовать только в выражениях, которые отвечают за оформление результата, таких как GROUP BY, ORDER BY и HAVING. В выражениях, отвечающих за получение данных, таких как WHERE, нужно использовать оригинальные имена полей.
Имеет ли значение порядок колонок в составном индексе?
это не то же самое, что
При создании составного индекса сначала строится обычный индекс для его первой колонки, а потом уже на основе него строится индекс для второй колонки и так дальше. Другими словами, колонки, по которым поиск выполняется чаще всего, должны стоять в составном индексе первыми.
SQL ключи во всех подробностях
В Интернете полно догматических заповедей о том, как нужно выбирать и использовать ключи в реляционных базах данных. Иногда споры даже переходят в холивары: использовать естественные или искусственные ключи? Автоинкрементные целые или UUID?
Прочитав шестьдесят четыре статьи, пролистав разделы пяти книг и задав кучу вопросов в IRC и StackOverflow, я (автор оригинальной статьи Joe «begriffs» Nelson), как мне кажется, собрал куски паззла воедино и теперь смогу примирить противников. Многие споры относительно ключей возникают, на самом деле, из-за неправильного понимания чужой точки зрения.
Содержание
Что же такое «ключи»?
Забудем на минуту о первичных ключах, нас интересует более общая идея. Ключ — это колонка (column) или колонки, не имеющие в строках дублирующих значений. Кроме того, колонки должны быть неприводимо уникальными, то есть никакое подмножество колонок не обладает такой уникальностью.
Для примера рассмотрим таблицу для подсчёта карт в карточной игре:
Если мы отслеживаем одну колоду (то есть без повторяющихся карт), то сочетание рубашки и лица уникально и нам бы не хотелось вносить в таблицу одинаковые рубашку и лицо дважды, потому что это будет избыточно. Если карта есть в таблице, то мы видели её, в противном случае — не видели.
Мы можем и должны задать базе данных это ограничение, добавив следующее:
Сами по себе ни suit (рубашка), ни face (лицо) не являются уникальными, мы можем увидеть разные карты с одинаковыми рубашкой или лицом. Поскольку (suit, face) уникально, а отдельные колонки не уникальны, можно утверждать, что их сочетание неприводимо, а (suit, face) является ключом.
В более общей ситуации, когда нужно отслеживать несколько колод карт, можно добавить новое поле и записывать сколько раз мы видели карту:
Хотя тройка (suit, face, seen) получается уникальной, она не является ключом, потому что подмножество (suit, face) тоже должно быть уникальным. Это необходимо, поскольку две строки с одинаковыми рубашкой и лицом, но разными значениями seen будут противоречащей информацией. Поэтому ключом является (suit, face) , и больше в этой таблице нет никаких ключей.
Ограничения уникальности
Также в таблице без проблем может быть несколько ключей, и мы должны объявить их все, чтобы соблюдать их уникальность в базе данных.
Вот два примера таблиц с несколькими ключами.
Ради краткости в примерах отсутствуют любые другие ограничения, которые были бы на практике. Например, у карт не должно быть отрицательное число просмотров, и значение NULL недопустимо для большинства рассмотренных колонок (за исключением колонки max_income для налоговых групп, в которой NULL может обозначать бесконечность).
Любопытный случай первичных ключей
То, что в предыдущем разделе мы назвали просто «ключами», обычно называется «потенциальными ключами» (candidate keys). Термин «candidate» подразумевает, что все такие ключи конкурируют за почётную роль «первичного ключа» (primary key), а оставшиеся назначаются «альтернативными ключами» (alternate keys).
Потребовалось какое-то время, чтобы в реализациях SQL пропало несоответствие ключей и реляционной модели, самые ранние базы данных были заточены под низкоуровневую концепцию первичного ключа. Первичные ключи в таких базах требовались для идентификации физического расположения строки на носителях с последовательным доступом к данным. Вот как это объясняет Джо Селко:
В современном SQL не нужно ориентироваться на физическое представление информации, таблицы моделируют связи и внутренний порядок строк вообще не важен. Однако, и сейчас SQL-сервер по умолчанию создаёт кластерный индекс для первичных ключей и, по старой традиции, физически выстраивает порядок строк.
В большинстве баз данных первичные ключи сохранились как пережиток прошлого, и едва ли обеспечивают что-то, кроме отражения или определения физического расположения. Например, в таблице PostgreSQL объявление первичного ключа автоматически накладывает ограничение NOT NULL и определяет внешний ключ по умолчанию. К тому же первичные ключи являются предпочтительными столбцами для оператора JOIN.
Первичный ключ не отменяет возможности объявления и других ключей. В то же время, если ни один ключ не назначен первичным, то таблица все равно будет нормально работать. Молния, во всяком случае, в вас не ударит.
Нахождение естественных ключей
Рассмотренные выше ключи называются «естественными», потому что они являются свойствами моделируемого объекта интересными сами по себе, даже если никто не стремится сделать из них ключ.
Первое, что стоит помнить при исследовании таблицы на предмет возможных естественных ключей — нужно стараться не перемудрить. Пользователь sqlvogel на StackExchange даёт следующий совет:
Практика показывает, что нужно вводить ограничение по ключу, когда колонка уникальна при имеющихся значениях и будет оставаться такой при вероятных сценариях. А при необходимости ограничение можно устранить (если это вас беспокоит, то ниже мы расскажем о стабильности ключа.)
Например, база данных членов хобби-клуба может иметь уникальность в двух колонках — 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.
- Номера социального страхования используются только гражданами США, имеют ограничения конфиденциальности и повторно используются после смерти. Кроме того, после кражи документов люди могут получить новые номера. Наконец, один и тот же номер может идентифицировать и лицо, и идентификатор налога на прибыль.
- Почтовые индексы — плохой выбор для городов. У некоторых городов общий индекс, или наоборот в одном городе бывает несколько индексов.
Искусственные ключи
С учётом того, что ключ – это колонка, в каждой строке которой находятся уникальные значения, одним из способов его создания является жульничество – в каждую строку можно записать выдуманные уникальные значения. Это и есть искусственные ключи: придуманный код, используемый для ссылки на данные или объекты.
Очень важно то, что код генерируется из самой базы данных и неизвестен никому, кроме пользователей базы данных. Именно это отличает искусственные ключи от стандартизированных естественных ключей.
Преимущество естественных ключей заключается в защите от дублирования или противоречивости строк таблицы, искусственные же ключи полезны потому, что они позволяют людям или другим системам проще ссылаться на строку, а также повышают скорость операций поиска и объединения, так как не используют сравнения строковых (или многостолбцовых) ключей.
Суррогаты
Не являющиеся суррогатами искусственные ключи удобны для ссылок на строку снаружи базы данных. Искусственный ключ кратко идентифицирует данные или объект: он может быть указан как URL, прикреплён к счёту, продиктован по телефону, получен в банке или напечатан на номерном знаке. (Номерной знак автомобиля для нас является естественным ключом, но разработан государством как искусственный ключ.)
Искусственные ключи нужно выбирать, учитывая возможные способы их передачи, чтобы минимизировать опечатки и ошибки. Надо учесть, что ключ могут произносить, читать напечатанным, отправлять по SMS, читать написанным от руки, вводить с клавиатуры и встраивать в URL. Дополнительно, некоторые искусственные ключи, например, номера кредитных карт, содержат контрольную сумму, чтобы при возникновении определённых ошибок их можно было хотя бы распознать.
- Для номерных знаков США существуют правила об использовании неоднозначных признаков, например O и 0 .
- Больницы и аптеки должны быть особенно аккуратны, учитывая почерк врачей.
- Передаёте эсэмэской код подтверждения? Не выходите за пределы набора символов GSM 03.38.
- В отличие от Base64, кодирующего произвольные байтовые данные, Base32 использует ограниченный набор символов, который удобно использовать людям и обрабатывать на старых компьютерных системах. – это читаемые, записываемые и произносимые идентификаторы. Это произносимые (PRO-nouncable) пятёрки (QUINT-uplets) однозначно понимаемых согласных и гласных букв.
Даже, если ограничиться числовыми ключами, есть табу типа тринадцатого этажа. Несмотря на то, что proquints обладают большей плотностью информации на произносимый слог, числа тоже неплохи во многих случаях: в URL, пин-клавиатурах и написанных от руки записях, если получатель знает, что ключ состоит только из цифр.
Однако, обратите внимание, что не стоит использовать последовательный порядок в публично открытых числовых ключах, поскольку это позволяет рыться в ресурсах ( /videos/1.mpeg , /videos/2.mpeg , и так далее), а также создаёт утечку информации о количестве данных. Наложите на последовательность чисел сеть Фейстеля и сохраните уникальность, скрыв при этом порядок чисел.
В wiki PostgreSQL есть пример функции псевдошифрования:
Эта функция является обратной самой себе (т.е. pseudo_encrypt(pseudo_encrypt(x)) = x ). Точное воспроизведение функции является своего рода безопасностью через неясность, и если кто-нибудь догадается, что вы использовали сеть Фейстеля из документации PostgreSQL, то ему будет легко получить исходную последовательность. Однако вместо (((1366 * r1 + 150889) % 714025) / 714025.0) можно использовать другую функцию с областью значений от 0 до 1, например, просто поэкспериментировать с числами в предыдущем выражении.
Вот, как использовать pseudo_encrypt:
Такое решение сохраняет случайные значения в столбце short_id , если же важно поддерживать высокие скорости обработки данных, то можно хранить в таблице саму инкрементную последовательность и преобразовывать её при запросе отображения с помощью pseudo_encrypt . Как мы увидим позже, индексирование рандомизированных значений может привести к увеличению объёма записи.
В предыдущем примере для short_id использовались целые значения обычного размера, для bigint есть другие функции Фейстеля, например XTEA.
Ещё один способ запутать последовательность целых чисел заключается в преобразовании её в короткие строки. Попробуйте воспользоваться расширением pg_hashids:
Здесь снова будет быстрее хранить в таблице сами целые числа и преобразовывать их по запросу, но замерьте производительность и посмотрите, имеет ли это смысл на самом деле.
Теперь, чётко разграничив смысл искусственных и естественных ключей, мы видим, что споры «естественные против искусственных» являются ложной дихотомией. Искусственные и естественные ключи не исключают друг друга! В одной таблице могут быть и те, и другие. На самом деле, таблица с искусственным ключом должна обеспечивать и естественный ключ, за редким исключением, когда не существует естественного ключа (например, в таблице кодов купонов):
Если у вас есть искусственный ключ и вы не объявляете естественные ключи, когда они существуют, то оставляете последние незащищёнными:
Единственным аргументом против объявления дополнительных ключей является то, что каждый новый несёт за собой ещё один уникальный индекс и увеличивает затраты на запись в таблицу. Конечно, зависит от того, насколько вам важна корректность данных, но, скорее всего, ключи все же стоит объявлять.
Также стоит объявлять несколько искусственных ключей, если они есть. Например, у организации есть кандидаты на работу (Applicants) и сотрудники (Employees). Каждый сотрудник когда-то был кандидатом, и относится к кандидатам по своему собственному идентификатору, который также должен быть и ключом сотрудника. Ещё один пример, можно задать идентификатор сотрудника и имя логина как два ключа в Employees.
Суррогатные ключи
Как уже упоминалось, важный тип искусственного ключа называется «суррогатный ключ». Он не должен быть кратким и передаваемым, как другие искусственные ключи, а используется как внутренняя метка, всегда идентифицирующая строку. Он используется в SQL, но приложение не обращается к нему явным образом.
Если вам знакомы системные колонки (system columns) из PostgreSQL, то вы можете воспринимать суррогаты почти как параметр реализации базы данных (вроде ctid), который однако никогда не меняется. Значение суррогата выбирается один раз для каждой строки и потом никогда не изменяется.
Суррогатные ключи отлично подходят в качестве внешних ключей, при этом необходимо указать каскадные ограничения ON UPDATE RESTRICT , чтобы соответствовать неизменности суррогата.
С другой стороны, внешние ключи к публично передаваемым ключам должны быть помечены ON UPDATE CASCADE , чтобы обеспечить максимальную гибкость. (Каскадное обновление выполняется на том же уровне изоляции, что и окружающая его транзакция, поэтому не беспокойтесь о проблемах с параллельным доступом – база данных справится, если выбрать строгий уровень изоляции.)
Не делайте суррогатные ключи «естественными». Как только вы покажете значение суррогатного ключа конечным пользователям, или, что хуже, позволите им работать с этим значением (в частности через поиск), то фактически придадите ключу значимость. Потом показанный ключ из вашей базы данных может стать естественным ключом в чьей-то чужой БД.
Принуждение внешних систем к использованию других искусственных ключей, специально предназначенных для передачи, позволяет нам при необходимости изменять эти ключи в соответствии с меняющимися потребностями, в то же время поддерживая внутреннюю целостность ссылок с помощью суррогатов.
Автоинкрементные bigint
Чаще всего для суррогатных ключей используют автоинкрементную колонку «bigserial», также известную как IDENTITY . (На самом деле, PostgreSQL 10 теперь, как и Oracle, поддерживает конструкцию IDENTITY, см. CREATE TABLE.)
Однако, я считаю, что автоинкрементное целое плохой выбор для суррогатных ключей. Такое мнение непопулярно, поэтому позвольте мне объясниться.
Недостатки последовательных ключей:
- Если все последовательности начинаются с 1 и постепенно увеличиваются, то у строк из разных таблиц будут одинаковые значения ключей. Такой вариант неидеален, предпочтительнее все же использовать непересекающиеся множества ключей в таблицах, чтобы, например, запросы не смогли бы случайно перепутать константы в JOIN и вернуть неожиданные результаты. (Как вариант для обеспечения отсутствия пересечений, можно составить каждую последовательность из чисел, кратных различным простым, но это будет довольно трудоёмко.)
- Вызов nextval() для генерации последовательности в современных распределённых SQL, приводит к тому, что вся система хуже масштабируется.
- Поглощение данных из базы данных, в которой тоже использовались последовательные ключи, приведет к конфликтам, потому что последовательные значения не будут уникальными в разных системах.
- С философской точки зрения последовательное увеличение чисел связано со старыми системами, в которых подразумевался порядок строк. Если же вы теперь хотите упорядочить строки, то делайте это явным образом, с помощью колонки меток времени или чего-то имеющего смысл в ваших данных. В противном случае нарушается первая нормальная форма.
- (Слабая причина, но) эти короткие идентификаторы так и тянет сообщить кому-нибудь.
Давайте рассмотрим другой вариант: использование больших целых чисел (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. Я использовал следующую конфигурацию для замера.
- Экземпляр EC2 с запущенным ami-aa2ea6d0
- Ubuntu Server 16.04 LTS (HVM)
- EBS General Purpose (SSD)
- c3.xlarge
- vCPU: 4
- RAM GiB: 7.5
- Disk GB: 2 x 40 (SSD)
- max_wal_size=‘10GB’;
- checkpoint_timeout=‘2h’;
- synchronous_commit=‘off’;
Перед тек, как добавить UUID в каждую таблицу, находим текущую позицию write-ahead log.
Я использовал такую позицию, чтобы получить статистику об использовании WAL после проведения бенчмарка. Так мы получим статистику событий, выполняемых последовательно после начальной позиции:
Я провёл тесты трёх сценариев:
- Добавление UUID, сгенерированных алгоритмом gen_random_uuid() (pgcrypto)
- Добавление из uuid_generate_v1() (предоставленного [uuid-ossp] (https://www.postgresql.org/docs/10/static/uuid-ossp.html)
- Снова добавление из gen_random_uuid() , но теперь с параметром full_page_writes=’off’ в конфигурации БД. Это покажет, насколько всё будет быстрее без увеличения FPW.
И вот результаты замеров скорости:
График скорости вставки UUID
Вот статистика WAL для каждого из способов:
Результаты подтверждают, что gen_random_uuid создаёт существенную активность в WAL из-за полностраничных образов (full-page images, FPI), а другие способы этим не страдают. Конечно, в третьем методе я просто запретил базе данных делать это. Однако запрет FPW совсем не то, что стоило бы использовать в реальности, если только вы не полностью уверены в файловой системе и конфигурации дисков. В этой статье утверждается, что ZFS может быть безопасным для отключения FPW, но пользуйтесь им с осторожностью.
Явным победителем в моём бенчмарке оказался uuid_generate_v1() – он быстр и не замедляется при накоплении строк. Расширение uuid-ossp по умолчанию установлено в таких облачных базах данных, как RDS и Citus Cloud, и будет доступно без дополнительных усилий.
В документация есть предупреждение о uuid_generate_v1:
Однако я не думаю, что настоящая проблема, потому что суррогатный ключ не передаётся. Если же это всё-таки важно для вас, в библиотеке есть uuid_generate_v1mc() , скрывающий mac-адрес компьютера.
Итоги и рекомендации
Теперь, когда мы познакомились с различными типами ключей и вариантами их использования, я хочу перечислить мои рекомендации по применению их в ваших базах данных.
Для каждой таблицы:
- Определите и объявите все естественные ключи.
- Создайте суррогатный ключ <table_name>_id типа uuid со значением по умолчанию в 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 . Выбирайте естественные ключи, исходя из собственной логики.
Такой подход обеспечивает стабильность внутренних ключей, в то же время допуская и даже защищая естественные ключи. К тому же, видимые искусственные ключи не становятся к чему-либо привязанными. Правильно во всем разобравшись, можно не зацикливаться только на «первичных ключах» и пользоваться всеми возможностями применения ключей.
Обсуждать подобные профессиональные вопросы мы предлагаем на наших конференциях. Если у вас за плечами большой опыт в ИТ-сфере, наболело, накипело и хочется высказаться, поделиться опытом или где-то попросить совета, то на майском фестивале конференций РИТ++ будут для этого все условия, 8 тематических направлений начиная от фронтенда и мобильной разработки, и заканчивая DevOps и управлением. Подать заявку на выступление можно здесь.