Ключі у базі даних

5.1 Вступ

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

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

Давайте розділимо проблему на частини, а наприкінці зберемо її знову. Для початку поставимо запитання – що ж таке «ключ»?

Забудемо на хвабону про первинні ключі, нас цікавить спільніша ідея. Ключ - це колонка (column) або колонки, які не мають у рядках дублюючих значень . Крім того, колонки повинні бути непривідно унікальними, тобто ніяке підмножина колонок не має такої унікальності.

Але для початку трохи теорії:

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

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

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

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

Складовий ключ

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

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

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

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

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

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

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

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

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

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

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

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

5.4 Штучні ключі

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

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

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

Сурогати

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

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

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

Приклади:

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

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

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

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

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

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

5.5 Сурогатні ключі

Як згадувалося, важливий тип штучного ключа називається «сурогатний ключ». Він не повинен бути коротким і переданим, як інші штучні ключі, а використовується як внутрішня мітка, яка завжди ідентифікує рядок. Він використовується в 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 наївно сприйме таку дію завершеною, то при завантаженні системи база даних буде пошкоджена.

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

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

5.6 Підсумки та рекомендації

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

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

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

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