5.1 Въведение

Интернет е пълен с догматични предписания за това How трябва да се избират и използват ключове в релационни бази данни. Понякога споровете дори се превръщат в холивари: трябва ли да се използват естествени or изкуствени ключове? Автоматично увеличаване на цели числа or UUID?

След като прочетох шестдесет и четири статии, прелистих секции от пет книги и зададох тонове въпроси в IRC и StackOverflow, аз (Джо "begriffs" Нелсън, авторът на оригиналната статия) изглежда събрах парчетата от пъзела заедно и сега може да помирява противниците. Много ключови спорове всъщност възникват от неразбиране на гледната точка на някой друг.

Нека да разделим проблема и да го сглобим отново накрая. Първо, нека зададем въпроса - Howво е "ключ"?

Нека забравим първичните ключове за момент, интересуваме се от по-обща идея. Ключът е колона (колона) or колони, които нямат дублиращи се стойности в редове . Освен това колоните трябва да бъдат нередуцируемо уникални, т.е. нито едно подмножество от колони няма тази уникалност.

Но първо малко теория:

първичен ключ

Първичен ключизползва се директно за идентифициране на редове в table. Той трябва да отговаря на следните ограничения:

  • Първичният ключ трябва да бъде уникален през цялото време.
  • Винаги трябва да присъства в tableта и да има стойност.
  • Не трябва често да променя стойността си. В идеалния случай не трябва изобщо да променя стойността .

Обикновено първичният ключ представлява една колона от table, но може да бъде и съставен ключ, състоящ се от множество колони.

Композитен ключ

Персонализиран ключ- комбинация от атрибути (колони), които уникално идентифицират всеки ред на tableта. Това могат да бъдат всички колони, няколко и една. В този случай редовете, които съдържат стойностите на тези атрибути, не трябва да се повтарят.

Потенциален ключ

кандидат ключ- представлява минималния съставен ключ на релацията (table), т.е. набор от атрибути, който отговаря на редица условия:

  • Нередуцируемост : Не може да бъде намалена, съдържа минималния възможен набор от атрибути.
  • Уникалност : Трябва да има уникални стойности, независимо от промяната на реда.
  • Наличие на стойност : Не трябва да има нулева стойност, т.е. трябва да има стойност.

5.2 Странният случай на първичните ключове

Това, което току-що нарекохме „ключове“ в предишния раздел, обикновено се наричат ​​„кандидат ключове“. Терминът "кандидат" предполага, че всички такива ключове се състезават за почетната роля на "първичен ключ" (primary key), а на останалите са присвоени "алтернативни ключове" (alternate keys).

Отне известно време на SQL имплементациите да преодолеят несъответствието между ключовете и релационния модел и най-ранните бази данни бяха насочени към концепцията на ниско ниво за първичен ключ. Първичните ключове в такива бази данни бяха необходими за идентифициране на физическото местоположение на ред на последователни носители за съхранение. Ето How Джо Челко го обяснява:

Терминът "ключ" означава ключ за сортиране на файлове, който е необходим за извършване на всяHowви операции по обработка на последователна файлова система. Набор от перфокарти беше прочетен в един и само един ред; беше невъзможно да се върна. Ранните лентови устройства имитираха същото поведение и не позволяваха двупосочен достъп. Това означава, че оригиналният Sybase SQL Server изисква „превъртане“ на tableта до началото, за да прочете предишния ред.

В съвременния SQL не е необходимо да се фокусирате върху физическото представяне на информацията, таблиците моделират връзки и вътрешният ред на редовете изобщо не е важен. Но дори и сега SQL сървърът по подразбиране създава клъстърен индекс за първичните ключове и според старата традиция физически подрежда реда на редовете.

В повечето бази данни първичните ключове са нещо от миналото и предоставят малко повече от отражение or физическо местоположение. Например в table на PostgreSQL декларирането на първичен ключ автоматично налага ограничение NOT NULLи дефинира външен ключ по подразбиране. Освен това първичните ключове са предпочитаните колони за оператора JOIN.

Първичният ключ не отменя възможността за деклариране на други ключове. В същото време, ако нито един ключ не е зададен като основен, тогава tableта ще продължи да работи добре. Светкавицата във всеки случай няма да ви удари.

Java university

5.3 Намиране на естествени ключове

Обсъдените по-горе ключове се наричат ​​"естествени", защото те са свойства на моделирания обект, които са интересни сами по себе си, дори ако никой не иска да направи ключ от тях.

Първото нещо, което трябва да запомните, когато разглеждате table за възможни естествени ключове, е да се опитате да не бъдете прекалено умни. Потребителят sqlvogel в StackExchange дава следния съвет:

Някои хора срещат трудности при избора на "естествен" ключ, защото измислят хипотетични ситуации, в които определен ключ може да не е уникален. Те не разбират самия смисъл на задачата. Значението на ключа е да дефинира правилото, според което атрибутите във всеки даден момент трябва да бъдат и винаги ще бъдат уникални в определена table. Таблицата съдържа данни в специфичен и добре разбираем контекст (в „областта на предмета“ or „областта на дискурса“) и единственото meaning е прилагането на ограничението в тази конкретна област.

Практиката показва, че е необходимо да се въведе ключово ограничение, когато колоната е уникална с наличните стойности и ще остане такава при вероятни сценарии. И ако е необходимо, ограничението може да бъде премахнато (ако това ви притеснява, тогава по-долу ще говорим за ключова стабилност.)

Например база данни с членове на хоби клуб може да има уникалност в две колони - first_name, last_name. При малко количество данни дубликатите са малко вероятни и преди да възникне реален конфликт, е доста разумно да се използва такъв ключ.

Тъй като базата данни расте и обемът на информацията се увеличава, изборът на естествен ключ може да стане по-труден. Данните, които съхраняваме, са опростяване на външната реалност и не съдържат някои аспекти, които отличават обектите в света, като например техните координати, които се променят с времето. Ако на обект липсва Howъвто и да е code, How можете да различите две кутии напитки or две кутии овесени ядки освен пространственото им разположение or леки разлики в теглото or опаковката?

Ето защо органите по стандартизация създават и прилагат отличителни знаци върху продуктите. Превозните средства са подпечатани с идентификационен номер на превозното средство (VIN) , книгите са отпечатани с ISBN номера , а опаковките на храни имат UPC номера . Може да възразите, че тези числа не изглеждат естествени. Така че защо ги наричам естествени ключове?

Естествеността or изкуствеността на уникалните свойства в база данни е относителна към външния свят. Ключ, който е бил изкуствен, когато е бил създаден в орган по стандартизация or правителствена агенция, става естествен за нас, защото става стандарт в целия свят и/or се отпечатва върху обекти.

Има много индустриални, обществени и международни стандарти за различни теми, включително валути, езици, финансови инструменти, химикали и медицински диагнози. Ето някои от стойностите, които често се използват като естествени ключове:

  • ISO 3166 codeове на държави
  • ISO 639 езикови codeове
  • Валутни codeове съгласно ISO 4217
  • Борсови символи ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • имена за вход
  • имейл addressи
  • номера на стаи
  • мрежов mac address
  • ширина, дължина за точки на земната повърхност

Препоръчвам да декларирате ключове, когато е възможно и разумно, може би дори няколко ключа на table. Но не забравяйте, че всичко по-горе може да има изключения.

  • Не всеки има имейл address, въпреки че това може да е приемливо при някои условия на базата данни. Освен това хората променят имейл addressите си от време на време. (Повече за стабилността на ключовете по-късно.)
  • ISIN борсовите символи се променят от време на време, например символите GOOG и GOOGL не описват точно реорганизацията на компанията от Google към Alphabet. Понякога може да възникне объркване, Howто при TWTR и TWTRQ, някои инвеститори погрешно са купor последното по време на IPO на Twitter.
  • Социалноосигурителните номера се използват само от граждани на САЩ, имат ограничения за поверителност и се използват повторно след смъртта. Освен това след кражба на documentи хората могат да получат нови номера. И накрая, един и същ номер може да идентифицира Howто лице, така и идентификатор за данък върху доходите.
  • Пощенските codeове са лош избор за градовете. Някои градове имат общ индекс or обратното, в един град има няколко индекса.

5.4 Изкуствени ключове

Като се има предвид, че ключът е колона с уникални стойности във всеки ред, един от начините да го създадете е да мамите - можете да пишете фиктивни уникални стойности във всеки ред. Това са изкуствени ключове: изобретен code, използван за препратка към данни or обекти.

Много е важно codeът да се генерира от самата база данни и да не е известен на никого освен на потребителите на базата данни. Това е, което отличава изкуствените ключове от стандартизираните естествени ключове.

Докато естествените ключове имат предимството да предпазват от дублиращи се or непоследователни редове в table, изкуствените ключове са полезни, защото улесняват хората or други системи да се позовават на реда и ускоряват търсенето и свързването, тъй като не използват низови (or многоколонни) сравнения.

Сурогати

Изкуствените ключове се използват като котви - без meaning How се променят правилата и колоните, един ред винаги може да бъде идентифициран по един и същи начин. Изкуственият ключ, използван за тази цел, се нарича "сурогатен ключ" и изисква специално внимание. По-долу ще разгледаме сурогатите.

Изкуствените ключове, които не са сурогати, са полезни за рефериране на ред извън базата данни. Изкуственият ключ идентифицира накратко данни or обект: може да бъде посочен като URL address, прикачен към фактура, продиктуван по телефона, получен от банка or отпечатан на регистрационен номер. (Регистрационният номер на автомобил е естествен ключ за нас, но проектиран от правителството като изкуствен ключ.)

Синтетичните ключове трябва да се избират, като се вземат предвид възможните средства за предаване, за да се сведат до минимум печатните и грешките. Трябва да се отбележи, че ключът може да се изговаря, да се чете отпечатан, да се изпраща чрез SMS, да се чете на ръка, да се въвежда от клавиатурата и да се вгражда в URL. Освен това някои изкуствени ключове, като номера на кредитни карти, съдържат контролна сума, така че ако възникнат определени грешки, те поне могат да бъдат разпознати.

Примери:

  • За американските регистрационни табели има правила относно използването на двусмислени знаци, като O и 0.
  • Болниците и аптеките трябва да бъдат особено внимателни, предвид почерка на лекарите.
  • Изпращате ли code за потвърждение с текстово съобщение? Не надхвърляйте набора от символи GSM 03.38.
  • За разлика от Base64, който codeира произволни byteови данни, Base32 използва ограничен набор от знаци, който е удобен за използване и работа от хората на по-стари компютърни системи.
  • Proquints са четими, записваеми и произносими идентификатори. Това са PRO-наименувани QUINT-uplets от недвусмислено разбираеми съгласни и гласни.

Имайте предвид, че веднага щом представите своя изкуствен ключ на света, хората странно ще започнат да му обръщат специално внимание. Просто погледнете регистрационните номера на „крадците“ or системата за създаване на произносими идентификатори, превърнала се в прословутия автоматичен генератор на проклятия.

Дори и да се ограничим до цифровите клавиши, има табута като тринадесетия етаж. Докато проквинтите имат по-висока плътност на информация за изговорена сричка, числата също са добри по много начини: в URL addressи, клавиатури с щифтове и ръкописни бележки, стига получателят да знае, че ключът е само цифри.

Моля, имайте предвид обаче, че не трябва да използвате последователен ред в публичните цифрови ключове, тъй като това ви позволява да се ровите в ресурси (/videos/1.mpeg, /videos/2.mpeg и т.н.) и също така изтича информация за номера данни. Наслагване на мрежа на Feistel върху поредица от числа и запазване на уникалността, като скриете реда на числата.

Единственият аргумент срещу декларирането на допълнителни ключове е, че всеки нов носи със себе си друг уникален индекс и увеличава разходите за запис в tableта. Разбира се, зависи от това колко важна е коректността на данните за вас, но най-вероятно ключовете все още трябва да бъдат декларирани.

Също така си струва да декларирате няколко изкуствени ключа, ако има такива. Например една организация има кандидати за работа (Кандидати) и служители (Служители). Всеки служител някога е бил кандидат и се отнася към кандидатите по техния собствен идентификатор, който също трябва да бъде ключът на служителя. Друг пример, можете да зададете идентификатора на служителя и името за вход като два ключа в Служители.

5.5 Сурогатни ключове

Както вече беше споменато, важен тип изкуствен ключ се нарича "сурогатен ключ". Не е необходимо да е кратък и проходим като другите изкуствени ключове, но се използва като вътрешен етикет, който винаги идентифицира низа. Използва се в SQL, но приложението няма изричен достъп до него.

Ако сте запознати със системните колони на PostgreSQL, тогава можете да мислите за сурогатите почти като параметър за внедряване на база данни (като ctid), който обаче никога не се променя. Сурогатната стойност се избира веднъж на ред и никога не се променя след това.

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

От друга страна, външните ключове към публично споделените ключове трябва да бъдат маркирани с ON UPDATE CASCADE, за да се осигури максимална гъвкавост. Каскадната актуализация се изпълнява на същото ниво на изолация като заобикалящата транзакция, така че не се притеснявайте за проблеми с паралелността - базата данни ще бъде наред, ако изберете строго ниво на изолация.

Не правете сурогатните ключове "естествени". След като покажете стойността на сурогатния ключ на крайните потребители or още по-лошо, оставите ги да работят с тази стойност (особено чрез търсене), вие на практика давате стойност на ключа. Тогава показаният ключ от вашата база данни може да стане естествен ключ в базата данни на някой друг.

Принуждаването на външните системи да използват други изкуствени ключове, специално проектирани за предаване, ни позволява да променяме тези ключове според нуждите, за да отговорим на променящите се нужди, като същевременно поддържаме вътрешна референтна цялост със сурогати.

Автоматично нарастване INT/BIGINT

Най-честата употреба на сурогатни ключове е автоматично увеличаващата се колона "bigserial" , известна също като ИДЕНТИЧНОСТ . (Всъщност PostgreSQL 10 вече поддържа конструкцията IDENTITY, Howто и Oracle, вижте CREATE TABLE.)

Вярвам обаче, че автоматично нарастващо цяло число е лош избор за сурогатни ключове. Това мнение е непопулярно, така че нека обясня.

Недостатъци на серийните ключове:

  • Ако всички последователности започват от 1 и нарастват постепенно, тогава редове от различни таблици ще имат еднакви ключови стойности. Тази опция не е идеална, все пак е за предпочитане да се използват несвързани набори от ключове в таблици, така че например заявките да не могат случайно да объркат константите JOINи да върнат неочаквани резултати. (Алтернативно, за да се гарантира, че няма пресичания, може да се конструира всяка последователност от множество различни прости числа, но това би било доста трудоемко.)
  • Извикването nextval() за генериране на последователност в днешния разпределен SQL води до това, че цялата система не се мащабира добре.
  • Консумирането на данни от база данни, която също използва последователни ключове, ще доведе до конфликти, тъй като последователните стойности няма да бъдат уникални в различните системи.
  • От философска гледна точка последователното нарастване на числата се свързва със стари системи, в които се подразбира редът на редовете. Ако сега искате да подредите редовете, направете го изрично с колона с клеймо за време or нещо, което има смисъл във вашите данни. В противен случай се нарушава първата нормална форма.
  • Слаба причина, но тези кратки идентификатори са изкушаващи да кажат на някого.

UUID

Нека да разгледаме друга опция: използване на големи цели числа (128-битови), генерирани по случаен модел. Алгоритмите за генериране на такива универсално уникални идентификатори (UUID) имат изключително ниска вероятност да изберат една и съща стойност два пъти, дори когато работят на два различни процесора едновременно.

В такъв случай UUID изглеждат като естествен избор за използване като сурогатни ключове, нали? Ако искате да етикетирате редове по уникален начин, тогава нищо не може да надмине уникалния етикет!

Така че защо не всички ги използват в PostgreSQL? Има няколко измислени причини за това и една логична, която може да бъде заобиколена, и ще представя показатели, за да илюстрирам мнението си.

Първо, ще говоря за пресилените причини. Някои хора смятат, че UUID са низове, защото са написани в традиционна шестнадесетична система с тире: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Наистина, някои бази данни нямат компактен (128-битов) uuid тип, но PostgreSQL има и има размер от две bigint, т.е. в сравнение с количеството друга информация в базата данни, режийните разходи са незначителни.

UUID също са несправедливо обвинявани, че са тромави, но кой ще ги произнесе, въведе or прочете? Казахме, че има смисъл да се показват изкуствени ключове, но никой (по дефиниция) не трябва да вижда сурогатния UUID. Възможно е UUID да бъде обработен от разработчик, изпълняващ SQL команди в psql за отстраняване на грешки в системата, но това е всичко. И разработчикът може също да се позовава на низове, използвайки по-удобни ключове, ако са дадени.

Истинският проблем с UUID е, че силно рандомизираните стойности водят до усилване на записа поради запис на цяла page в журнала за предварителен запис (WAL) . Въпреки това, влошаването на производителността всъщност зависи от алгоритъма за генериране на UUID.

Нека измерим усилването при запис . Всъщност проблемът е в по-старите файлови системи. Когато PostgreSQL пише на диск, той променя "pageта" на диска. Ако изключите захранването на компютъра, повечето файлови системи пак ще докладват за успешен запис, преди данните да бъдат безопасно съхранени на диска. Ако PostgreSQL наивно възприема такова действие като завършено, тогава базата данни ще бъде повредена по време на следващото зареждане на системата.

Тъй като PostgreSQL не може да се довери на повечето операционни системи/файлови системи/дискови конфигурации за осигуряване на непрекъснатост, базата данни записва пълното състояние на променената дискова page в журнал за предварително записване, който може да се използва за възстановяване от възможен срив. Индексирането на силно рандомизирани стойности като UUID обикновено включва куп различни дискови страници и води до запис на пълния размер на pageта (обикновено 4 or 8 KB) в WAL за всеки нов запис. Това е така нареченото писане на цяла page (full-page write, FPW).

Някои алгоритми за генериране на UUID (като "снежинката" на Twitter or uuid_generate_v1() в разширението uuid-ossp на PostgreSQL) генерират монотонно нарастващи стойности на всяка машина. Този подход консолидира записите в по-малко дискови страници и намалява FPW.

5.6 Изводи и препоръки

Сега, след като видяхме различните типове ключове и техните употреби, искам да изброя моите препоръки за използването им във вашите бази данни.

За всяка маса:

  • Дефинирайте и декларирайте всички естествени ключове.
  • Създайте сурогатен ключ <table_name>_idот тип UUID със стойност по подразбиране uuid_generate_v1(). Можете дори да го маркирате като първичен ключ. Ако добавите името на tableта към този идентификатор, това ще опрости JOIN, т.е. получавате JOIN foo USING (bar_id)instead of JOIN foo ON (foo.bar_id = bar.id). Не предавайте този ключ на клиенти и изобщо не го излагайте извън базата данни.
  • За междинни таблици, които минават през JOIN, декларирайте всички колони с външен ключ като единичен съставен първичен ключ.
  • По желание добавете изкуствен ключ, който може да се използва в URL or други указания за препратка към низ. Използвайте мрежа на Feistel or pg_hashids, за да маскирате автоматично увеличаващите се цели числа.
  • Посочете каскадно ограничение, ON UPDATE RESTRICTкато използвате сурогатни UUID като външни ключове и за изкуствени външни ключове ON UPDATE CASCADE. Изберете естествени ключове въз основа на вашата собствена логика.

Този подход гарантира стабилността на вътрешните ключове, като същевременно позволява и дори защитава естествените ключове. В допълнение, видимите изкуствени ключове не се прикрепят към нищо. След като сте разбрали всичко правилно, не можете да се закачите само на „първични ключове“ и да използвате всички възможности за използване на ключове.