1. Вступ

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

Прочитавши шістдесят чотири статті, перегорнувши розділи п'яти книг і поставивши купу питань у IRC і StackOverflow, я (автор оригінальної статті Joe «begriffs» Nelson), як мені здається, зібрав шматки паззла разом і тепер зможу примирити супротивників. Багато суперечок щодо ключів виникають насправді через неправильне розуміння чужої точки зору.

Давай розділимо проблему на частини, а в кінці зберемо її знову. Для початку поставимо запитання: що ж таке «ключ»? Забудемо на хвилину про первинні ключі, нас цікавить спільніша ідея. Ключ — це колонка (column) або колонки, що не мають у рядках дублюючих значень

. Крім того, колонки повинні бути незвідно унікальними, тобто ніяке підмножина колонок не має такої унікальності. Але для початку трохи теорії:

Первісний ключ

Primary key безпосередньо застосовується для ідентифікації рядків у таблиці. Він повинен відповідати наступним обмеженням:

  • Первинний ключ повинен бути унікальним весь час.
  • Він повинен постійно бути присутнім в таблиці і мати значення.
  • Він не повинен часто змінювати своє значення. В ідеалі він взагалі не повинен змінювати значення.

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

Складений ключ

Custom Key — комбінація атрибутів (стовпців), які унікально ідентифікують кожен рядок таблиці. Це можуть бути всі стовпці, і кілька, і один. При цьому рядки, які містять значення цих атрибутів, не повинні повторюватися.

Потенційний ключ

Candidate key — мінімальний складовий ключ відношення (таблиці), тобто набір атрибутів, який задовольняє низці умов:

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

2. Цікавий випадок первинних ключів

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

Потрібен якийсь час, щоб в реалізаціях SQL зникла невідповідність ключів і реляційної моделі, ранні бази даних були заточені під низькорівневу концепцію первинного ключа. Первинні ключі в таких базах були потрібні для ідентифікації фізичного розташування рядка на носіях з послідовним доступом до даних. Ось як це пояснює Джо Селко: Термін "ключ" означав ключ сортування файлу, який був потрібен для виконання будь-яких операцій обробки в послідовній файловій системі. Набір перфокарт зчитувався в одному і лише в одному порядку; неможливо було "повернутись назад". Перші накопичувачі на магнітних стрічках імітували таку ж поведінку та не дозволяли виконувати двонаправлений доступ. Тобто, початковий Sybase SQL Server для читання попереднього рядка вимагав «перемотування» таблиці на початок.

У сучасному SQL не потрібно орієнтуватися на фізичне подання інформації: таблиці моделюють зв'язки та внутрішній порядок рядків взагалі важливий. Однак, і зараз SQL-сервер за замовчуванням створює кластерний індекс для первинних ключів і, за старою традицією, фізично вибудовує порядок рядків.

У більшості баз даних первинні ключі збереглися як пережиток минулого, і навряд чи забезпечують щось крім відображення чи визначення фізичного розташування. Наприклад, у таблиці PostgreSQL оголошення первинного ключа автоматично накладає обмеження NOT NULL та визначає зовнішній ключ за замовчуванням. До того ж, первинні ключі є кращими стовпцями для оператора JOIN.

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

Java-університет

3. Знаходження природних ключів

Розглянуті вище ключі називаються «природними», тому що вони є властивостями об'єкта, що модулюється, цікавими самі по собі, навіть якщо ніхто не прагне зробити з них ключ.

Перше, про що варто пам'ятати під час дослідження таблиці на предмет можливих природних ключів — варто старатися не перемудрувати. Користувач 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.
  • Номери соціального страхування використовуються лише громадянами США, мають обмеження конфіденційності та повторно використовуються після смерті. До того ж, після кражі документів люди можуть отримати нові номери. Нарешті, один і той самий номер може ідентифікувати і особу, і ідентифікатор податку на прибуток.
  • Поштові індекси — поганий вибір для міст. У деяких міст загальний індекс, або навпаки, в одному місті буває декілька індексів.

4. Штучні ключі

Враховуючи те, що ключ – це колонка, в кожному рядку якої знаходяться унікальні значення, одним зі способів його створення є шахрайство – до кожного рядка можна записати вигадані унікальні значення. Це і є штучні ключі: вигаданий код, що використовується для посиланні на данні або об'єкти.

Дуже важливим є те, що код генерується з самої бази даних та невідомий нікому, окрім користувачів бази даних. Саме це відрізняє штучні ключі від стандартизованих природних ключів.

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

Сурогати

Штучні ключі використовуються як прив'язка — незалежно від зміни правил і стовпчиків, один рядок завжди можна ідентифікувати однаковим способом. Штучний ключ, який використовується для цієї мети, називається «сурогатним ключем» і вимагає особливої уваги. Сурогати ми розглянемо нижче.

Штучні ключі, що не э сурогатами, зручні для посилань на рядок зовні бази даних. Штучний ключ коротко ідентифікує дані або об'єкт: він може бути вказаний як URL-адреса, прикріплена до рахунку, продиктована по телефону, отримана в банку або надрукована на номерному знаку. (Номірний знак автомобіля для нас є природним ключем, але розроблений державою як штучний ключ.)

Штучні ключі потрібно вибирати, враховуючи можливі способи їх передачі, щоб мінімізувати помилки та помилки. Треба врахувати, що ключі можуть вимовляти, читати надрукованим, відправляти по SMS, читати написаним від руки, вводити з клавіатури та вбудовувати в URL. Додатково деякі штучні ключі, наприклад номери кредитних карток, містять контрольну суму, щоб при виникненні певних помилок їх можна було хоча б розпізнати.

Приклади:

  • Для номерних знаків США існують правила про використання неоднозначних ознак, наприклад O та 0.
  • Лікарні та аптеки повинні бути особливо акуратними, враховуючи почерк лікарів.
  • Передаєте sms-код підтвердження? Не виходьте за межі набору символів GSM 03.38.
  • На відміну від Base64, що кодує довільні байтові дані, Base32 використовує обмежений набір символів, який зручно використовувати людям та обробляти на старих комп'ютерних системах.
  • Proquints — це ідентифікатори, що читаються, записуються та вимовляються. Це вимовляються (PRO-nouncable) п'ятірки (QUINT-uplets) однозначно зрозумілих приголосних і голосних букв.

Май на увазі, що як тільки ти познайомиш світ зі своїм штучним ключем, люди дивним чином почнуть надавати йому особливу увагу. Достатньо подивитися на «блатні» номерні знаки або на систему створення ідентифікаторів, що вимовляються, яка перетворилася на сумнозвісний автоматизований генератор лайок.

Навіть, якщо обмежитися числовими ключами, є табу типу тринадцятого поверху. Незважаючи на те, що proquints мають більшу щільність інформації на склад, що вимовляється, числа теж непогані в багатьох випадках: в URL, пін-клавіатурах і написаних від руки записах, якщо одержувач знає, що ключ складається тільки з цифр.

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

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

Також варто оголошувати кілька штучних ключів, якщо вони є. Наприклад, організація має кандидатів на роботу (Applicants) і співробітників (Employees). Кожен співробітник колись був кандидатом, і належить до кандидатів за власним ідентифікатором, який також повинен бути і ключем співробітника. Ще один приклад, можна задати ідентифікатор співробітника та ім'я логіна як два ключі в Employees. Він не повинен бути коротким і переданим, як інші штучні ключі, а використовується як внутрішня мітка, яка завжди ідентифікує рядок. Він використовується в SQL, але додаток не звертається до нього явним чином.

Якщо тобі знайомі системні колонки (system columns) з PostgreSQL, то ти можеш сприймати майже як параметр реалізації бази даних (на кшталт ctid), який, проте, будь-коли змінюється. Значення сурогату вибирається один раз для кожного рядка і потім ніколи не змінюється.

Сурогатні ключі відмінно підходять як зовнішні ключі, при цьому необхідно вказати каскадні обмеження ON UPDATE RESTRICT, щоб відповідати незмінності сурогату.

З іншого боку, зовнішні ключі до публічно переданих ключів повинні бути позначені ON UPDATE CASCADE, щоб забезпечити максимальну гнучкість. Каскадне оновлення виконується на тому ж рівні ізоляції, що й навколишня транзакція, тому не турбуйтеся про проблеми з паралельним доступом – база даних впорається, якщо вибрати суворий рівень ізоляції.

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

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

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

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

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

Недоліки послідовних ключів:

  • Якщо всі послідовності починаються з 1 і поступово збільшуються, то рядки з різних таблиць мають однакові значення ключів. Такий варіант неідеальний, краще все ж таки використовувати непересічні безлічі ключів у таблицях, щоб, наприклад, запити не змогли б випадково переплутати константи в JOIN і повернути несподівані результати. (Як варіант для забезпечення відсутності перетинів, можна скласти кожну послідовність з чисел, кратних різним простим, але це буде досить трудомістко.)
  • Виклик nextval()для генерації послідовності в сучасних розподілених SQL призводить до того, що вся система гірше масштабується.
  • Поглинання даних з бази даних, в якій також використовувалися послідовні ключі, призведе до конфліктів, тому що послідовні значення не будуть унікальними в різних системах.
  • З філософської погляду послідовне збільшення чисел пов'язане зі старими системами, у яких було передбачено порядок рядків. Якщо ж ти тепер хочеш впорядкувати рядки, то роби це явно, за допомогою колонки міток часу або чогось, що має сенс у ваших даних. В іншому випадку порушується перша нормальна форма.
  • Слабка причина, але ці короткі ідентифікатори так і тягне повідомити когось.

UUID

Давай розглянемо інший варіант: використання великих цілих чисел (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 наївно сприйме таку дію завершеною, то при подальшому завантаженні системи база даних буде пошкоджена. сторінки в журнал із попереджувальним записом (write-ahead log), який можна використовувати для відновлення після можливого збою. Індексування сильно рандомізованих значень на зразок UUID зазвичай зачіпає купу різних сторінок диска і призводить до запису повного розміру сторінки (зазвичай 4 або 8 КБ) WAL для кожного нового запису. Це так званий повносторінковий запис (full-page write, FPW).

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

Для кожної таблиці:

  • Визнач та оголоси усі природні ключі.
  • Створи сурогатний ключ < ;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. Обирай природні ключі, виходячи з власної логіки.

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