5.1 Introducere

Internetul este plin de precepte dogmatice despre cum ar trebui alese și utilizate cheile în bazele de date relaționale. Uneori, disputele se transformă chiar în holivar: ar trebui folosite chei naturale sau artificiale? Incrementare automată numere întregi sau UUID-uri?

După ce am citit șaizeci și patru de articole, răsfoind secțiuni din cinci cărți și am pus o mulțime de întrebări despre IRC și StackOverflow, eu (Joe „îl roagă” pe Nelson, autorul articolului original) par să fi pus cap la cap piesele puzzle-ului și acum pot împăca adversarii. Multe dispute cheie apar de fapt dintr-o înțelegere greșită a punctului de vedere al altcuiva.

Să dezactivăm problema și să o punem la loc la sfârșit. Mai întâi, să punem întrebarea - ce este o „cheie”?

Să uităm pentru un moment de cheile primare, ne interesează o idee mai generală. O cheie este o coloană (coloană) sau coloane care nu au valori duplicate în rânduri . De asemenea, coloanele trebuie să fie ireductibil de unice, adică niciun subset de coloane nu are această unicitate.

Dar mai întâi, o teorie:

cheia principala

Cheia principalautilizat direct pentru a identifica rândurile dintr-un tabel. Acesta trebuie să respecte următoarele restricții:

  • Cheia primară trebuie să fie unică tot timpul.
  • Trebuie să fie întotdeauna prezent în tabel și să aibă o valoare.
  • Nu ar trebui să-și schimbe valoarea frecvent. În mod ideal, nu ar trebui să schimbe deloc valoarea .

De obicei, o cheie primară reprezintă o singură coloană a unui tabel, dar poate fi și o cheie compusă constând din mai multe coloane.

Cheie compusă

Cheie personalizată- o combinație de atribute (coloane) care identifică în mod unic fiecare rând de tabel. Pot fi toate coloanele și mai multe și una. În acest caz, liniile care conțin valorile acestor atribute nu trebuie repetate.

Cheie potențială

cheia candidatului- reprezintă cheia compusă minimă a relației (tabelului), adică un set de atribute care satisface un număr de condiții:

  • Ireductibilitate : Nu poate fi redusă, conține setul minim posibil de atribute.
  • Unicitate : trebuie să aibă valori unice, indiferent de schimbarea rândului.
  • Prezența unei valori : nu trebuie să aibă o valoare nulă, adică trebuie să aibă o valoare.

5.2 Cazul curios al cheilor primare

Ceea ce tocmai am numit „chei” în secțiunea anterioară sunt denumite în mod obișnuit „chei candidate”. Termenul „candidat” implică faptul că toate aceste chei concurează pentru rolul onorific de „cheie primară” (cheie primară), iar celorlalte li se atribuie „chei alternative” (chei alternative).

A durat ceva timp pentru ca implementările SQL să treacă peste nepotrivirea dintre chei și modelul relațional, iar cele mai vechi baze de date au fost orientate către conceptul de nivel scăzut al unei chei primare. Cheile primare din astfel de baze de date erau necesare pentru a identifica locația fizică a unui rând pe medii de stocare secvențială. Iată cum explică Joe Celko:

Termenul „cheie” însemna o cheie de sortare a fișierelor, care era necesară pentru a efectua orice operațiuni de procesare pe un sistem de fișiere secvențial. Un set de cărți perforate a fost citit într-o singură ordine; era imposibil să mă întorc. Unitățile de bandă timpurii imitau același comportament și nu permiteau accesul bidirecțional. Adică, originalul Sybase SQL Server trebuia să „deruleze” tabelul la început pentru a citi rândul anterior.

În SQL-ul modern, nu trebuie să vă concentrați pe reprezentarea fizică a informațiilor, relațiile modelului tabelelor, iar ordinea internă a rândurilor nu este deloc importantă. Cu toate acestea, chiar și acum serverul SQL creează implicit un index grupat pentru cheile primare și, conform vechii tradiții, aranjează fizic ordinea rândurilor.

În majoritatea bazelor de date, cheile primare aparțin trecutului și oferă puțin mai mult decât o reflectare sau o locație fizică. De exemplu, într-un tabel PostgreSQL, declararea unei chei primare impune automat o constrângere NOT NULLși definește o cheie străină implicită. În plus, cheile primare sunt coloanele preferate pentru operator JOIN.

Cheia primară nu anulează posibilitatea de a declara alte chei. În același timp, dacă nicio cheie nu este atribuită ca principală, atunci tabelul va funcționa în continuare bine. Fulgerul, în orice caz, nu te va lovi.

Java university

5.3 Găsirea cheilor naturale

Cheile discutate mai sus sunt numite „naturale” deoarece sunt proprietăți ale obiectului modelat care sunt interesante în sine, chiar dacă nimeni nu vrea să facă o cheie din ele.

Primul lucru de reținut atunci când examinezi un tabel pentru posibile chei naturale este să încerci să nu fii prea inteligent. Utilizatorul sqlvogel de pe StackExchange oferă următoarele sfaturi:

Unii oameni au dificultăți în a alege o cheie „naturală”, deoarece vin cu situații ipotetice în care o anumită cheie poate să nu fie unică. Ei nu înțeleg însuși sensul sarcinii. Sensul cheii este de a defini regula conform căreia atributele la un moment dat trebuie să fie și vor fi întotdeauna unice într-un anumit tabel. Tabelul conține date într-un context specific și bine înțeles (în „domeniul de subiect” sau „zona de discurs”), iar singurul sens este aplicarea restricției în acea zonă anume.

Practica arată că este necesar să se introducă o constrângere cheie atunci când coloana este unică cu valorile disponibile și va rămâne așa în scenariile probabile. Și dacă este necesar, restricția poate fi eliminată (dacă acest lucru vă deranjează, atunci mai jos vom vorbi despre stabilitatea cheii.)

De exemplu, o bază de date cu membrii unui club de hobby poate avea unicitate în două coloane - first_name, last_name. Cu o cantitate mică de date, duplicatele sunt puțin probabile și înainte de a apărea un conflict real, este destul de rezonabil să folosiți o astfel de cheie.

Pe măsură ce baza de date crește și volumul de informații crește, alegerea unei chei naturale poate deveni mai dificilă. Datele pe care le stocăm sunt o simplificare a realității externe și nu conțin unele aspecte care disting obiectele din lume, cum ar fi coordonatele acestora care se modifică în timp. Dacă unui obiect îi lipsește vreun cod, cum poți deosebi două cutii de băutură sau două cutii de fulgi de ovăz în afară de aranjarea lor spațială sau de micile diferențe de greutate sau de ambalare?

De aceea, organismele de standardizare creează și aplică mărci distinctive produselor. Vehiculele sunt ștampilate cu un număr de identificare a vehiculului (VIN) , cărțile sunt tipărite cu coduri ISBN , iar ambalajele alimentare au coduri UPC . Puteți obiecta că aceste numere nu par naturale. Deci de ce le numesc chei naturale?

Naturalitatea sau artificialitatea proprietăților unice dintr-o bază de date este relativă la lumea exterioară. O cheie care a fost artificială atunci când a fost creată într-un organism de standardizare sau agenție guvernamentală devine firească pentru noi, deoarece devine un standard în întreaga lume și/sau este imprimată pe obiecte.

Există multe standarde industriale, publice și internaționale pentru o varietate de subiecte, inclusiv valute, limbi, instrumente financiare, substanțe chimice și diagnostice medicale. Iată câteva dintre valorile care sunt adesea folosite ca chei naturale:

  • Codurile de țară ISO 3166
  • Codurile de limbă ISO 639
  • Codurile valutare conform ISO 4217
  • Simboluri bursiere ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • nume de conectare
  • adrese de email
  • numerele camerelor
  • adresa mac de rețea
  • latitudine, longitudine pentru punctele de pe suprafața Pământului

Recomand declararea cheilor ori de câte ori este posibil și rezonabil, poate chiar mai multe chei pe tabel. Dar amintiți-vă că toate cele de mai sus pot avea excepții.

  • Nu toată lumea are o adresă de e-mail, deși aceasta poate fi acceptabilă în anumite condiții ale bazei de date. De asemenea, oamenii își schimbă din când în când adresele de e-mail. (Mai multe despre stabilitatea cheii mai târziu.)
  • Simbolurile bursiere ISIN se modifică din când în când, de exemplu, simbolurile GOOG și GOOGL nu descriu cu exactitate reorganizarea companiei de la Google la Alphabet. Uneori poate apărea confuzie, ca și în cazul TWTR și TWTRQ, unii investitori l-au cumpărat din greșeală pe acesta din urmă în timpul IPO-ului Twitter.
  • Numerele de securitate socială sunt folosite numai de cetățenii americani, au restricții de confidențialitate și sunt refolosite după moarte. În plus, după furtul documentelor, oamenii pot obține numere noi. În cele din urmă, același număr poate identifica atât o persoană, cât și un identificator de impozit pe venit.
  • Codurile poștale sunt o alegere proastă pentru orașe. Unele orașe au un index comun, sau invers, există mai mulți indici într-un oraș.

5.4 Chei artificiale

Având în vedere că cheia este o coloană cu valori unice în fiecare rând, o modalitate de a o crea este să trișați - puteți scrie valori unice fictive în fiecare rând. Acestea sunt chei artificiale: cod inventat folosit pentru a se referi la date sau obiecte.

Este foarte important ca codul să fie generat din baza de date în sine și să fie necunoscut de nimeni, cu excepția utilizatorilor bazei de date. Acesta este ceea ce distinge cheile artificiale de cheile naturale standardizate.

În timp ce cheile naturale au avantajul de a proteja împotriva rândurilor duplicate sau inconsecvente dintr-un tabel, cheile artificiale sunt utile deoarece fac mai ușor pentru oameni sau alte sisteme să se refere la rând și accelerează căutările și alăturarile deoarece nu folosesc comparații cu șiruri de caractere (sau pe mai multe coloane).

Înlocuitori

Cheile artificiale sunt folosite ca ancore - indiferent de modul în care regulile și coloanele se schimbă, un rând poate fi întotdeauna identificat în același mod. Cheia artificială folosită în acest scop se numește „cheie surogat” și necesită o atenție specială. Vom lua în considerare surogate mai jos.

Cheile artificiale non-surogat sunt utile pentru referirea unui rând din afara bazei de date. O cheie artificială identifică pe scurt o dată sau un obiect: poate fi specificată ca URL, atașată unei facturi, dictată la telefon, obținută de la o bancă sau tipărită pe o plăcuță de înmatriculare. (Placa de înmatriculare a unei mașini este o cheie naturală pentru noi, dar concepută de guvern ca o cheie artificială.)

Cheile sintetice trebuie alese ținând cont de posibilele mijloace de transmitere pentru a minimiza greșelile de scriere și erorile. Trebuie remarcat faptul că cheia poate fi rostită, citită tipărită, trimisă prin SMS, citită scrisă de mână, tastata de la tastatură și încorporată într-un URL. În plus, unele chei artificiale, cum ar fi numerele de card de credit, conțin o sumă de control, astfel încât, dacă apar anumite erori, acestea pot fi cel puțin recunoscute.

Exemple:

  • Pentru plăcuțele de înmatriculare din SUA, există reguli cu privire la utilizarea caracterelor ambigue, cum ar fi O și 0.
  • Spitalele și farmaciile trebuie să fie deosebit de atente, având în vedere scrierea de mână a medicilor.
  • Trimiți un cod de confirmare prin mesaj text? Nu depășiți setul de caractere GSM 03.38.
  • Spre deosebire de Base64, care codifică date arbitrare de octeți, Base32 folosește un set limitat de caractere care este convenabil de utilizat și manipulat de oameni pe sistemele computerizate mai vechi.
  • Proquints sunt identificatori care pot fi citite, care pot fi scrise și care pot fi pronunțate. Acestea sunt QUINT-upleturi PRO-nuncabile ale consoanelor și vocalelor înțelese fără ambiguitate.

Rețineți că, de îndată ce vă introduceți cheia artificială în lume, oamenii vor începe în mod ciudat să-i acorde o atenție specială. Uită-te doar la plăcuțele de înmatriculare „hoții” sau la sistemul de creare a identificatorilor pronunțabili, care a devenit infamul generator automat de blesteme.

Chiar dacă ne limităm la taste numerice, există tabuuri precum etajul al treisprezecelea. În timp ce proquints au o densitate mai mare de informații pe silabă rostită, numerele sunt bune și în multe feluri: în adrese URL, tastaturi și note scrise de mână, atâta timp cât destinatarul știe că cheia sunt doar numere.

Cu toate acestea, vă rugăm să rețineți că nu ar trebui să utilizați ordinea secvențială în tastele numerice publice, deoarece acest lucru vă permite să scotoci prin resurse (/videos/1.mpeg, /videos/2.mpeg și așa mai departe) și, de asemenea, să scurgeți informații despre număr. date. Suprapuneți o rețea Feistel pe o secvență de numere și păstrați unicitatea în timp ce ascundeți ordinea numerelor.

Singurul argument împotriva declarării cheilor suplimentare este că fiecare nouă aduce cu sine un alt index unic și crește costul scrierii în tabel. Desigur, depinde de cât de importantă este corectitudinea datelor pentru tine, dar, cel mai probabil, cheile ar trebui totuși declarate.

De asemenea, merită să declarați mai multe chei artificiale, dacă există. De exemplu, o organizație are candidați pentru locuri de muncă (Solicitanți) și angajați (Angajați). Fiecare angajat a fost cândva candidat și se referă la candidați prin propriul identificator, care ar trebui să fie și cheia angajatului. Un alt exemplu, puteți seta ID-ul angajatului și numele de conectare ca două chei în Angajați.

5.5 Chei surogat

După cum sa menționat deja, un tip important de cheie artificială se numește „cheie surogat”. Nu trebuie să fie concis și accesibil ca alte chei artificiale, dar este folosit ca o etichetă internă care identifică întotdeauna șirul. Este folosit în SQL, dar aplicația nu îl accesează în mod explicit.

Dacă sunteți familiarizat cu coloanele de sistem PostgreSQL, atunci vă puteți gândi la surogate aproape ca la un parametru de implementare a bazei de date (cum ar fi ctid), care, totuși, nu se schimbă niciodată. Valoarea surogat este selectată o dată pe rând și nu se modifică niciodată ulterior.

Cheile surogat sunt excelente ca chei străine, iar constrângerile în cascadă trebuie specificate ON UPDATE RESTRICTpentru a se potrivi cu imuabilitatea surogatului.

Pe de altă parte, cheile externe pentru cheile partajate public ar trebui să fie marcate cu ON UPDATE CASCADE, pentru a oferi flexibilitate maximă. O actualizare în cascadă rulează la același nivel de izolare ca tranzacția din jur, așa că nu vă faceți griji cu privire la problemele de concurență - baza de date va fi bine dacă alegeți un nivel de izolare strict.

Nu faceți cheile surogat „naturale”. Odată ce afișați valoarea cheii surogat utilizatorilor finali sau, mai rău, îi lăsați să lucreze cu acea valoare (în special printr-o căutare), îi dați efectiv cheii o valoare. Atunci cheia afișată din baza ta de date poate deveni o cheie naturală în baza de date a altcuiva.

Forțarea sistemelor externe să utilizeze alte chei artificiale special concepute pentru transmisie ne permite să schimbăm acele chei după cum este necesar pentru a răspunde nevoilor în schimbare, menținând în același timp integritatea referențială internă cu surogate.

Incrementare automată INT/BIGINT

Cea mai frecventă utilizare pentru cheile surogat este coloana „bigserial” cu incrementare automată , cunoscută și sub numele de IDENTITATE . (De fapt, PostgreSQL 10 acceptă acum construcția IDENTITY, la fel ca și Oracle, vezi CREATE TABLE.)

Cu toate acestea, cred că un număr întreg cu incrementare automată este o alegere proastă pentru cheile surogat. Această opinie este nepopulară, așa că permiteți-mi să vă explic.

Dezavantajele cheilor seriale:

  • Dacă toate secvențele încep de la 1 și cresc treptat, atunci rândurile din tabele diferite vor avea aceleași valori cheie. Această opțiune nu este ideală, este totuși de preferat să folosiți seturi disjunse de chei în tabele, astfel încât, de exemplu, interogările să nu confunde accidental constantele JOINși să nu returneze rezultate neașteptate. (Ca alternativă, pentru a vă asigura că nu există intersecții, s-ar putea construi fiecare secvență din multipli de numere prime diferite, dar acest lucru ar fi destul de laborios.)
  • Apelul nextval() de a genera o secvență în SQL-ul distribuit de astăzi are ca rezultat faptul că întregul sistem nu se scala bine.
  • Consumul de date dintr-o bază de date care a folosit și chei secvenţiale va duce la conflicte, deoarece valorile secvenţiale nu vor fi unice între sisteme.
  • Din punct de vedere filozofic, creșterea secvențială a numerelor este asociată cu vechile sisteme în care era implicată ordinea liniilor. Dacă acum doriți să ordonați rândurile, atunci faceți acest lucru în mod explicit cu o coloană de marcaj temporal sau ceva care are sens în datele dvs. În caz contrar, prima formă normală este încălcată.
  • Motiv slab, dar acești identificatori scurti sunt tentanți să spună cuiva.

UUID

Să ne uităm la o altă opțiune: utilizarea numerelor întregi mari (128 de biți) generate după un model aleator. Algoritmii pentru generarea unor astfel de identificatori unici universal (UUID) au o probabilitate extrem de scăzută de a alege aceeași valoare de două ori, chiar și atunci când rulează pe două procesoare diferite în același timp.

În acest caz, UUID-urile par o alegere naturală de folosit ca chei surogat, nu-i așa? Dacă doriți să etichetați rândurile într-un mod unic, atunci nimic nu bate o etichetă unică!

Deci, de ce nu toată lumea le folosește în PostgreSQL? Există mai multe motive inventate pentru aceasta și unul logic care poate fi rezolvat și voi prezenta puncte de referință pentru a ilustra punctul meu.

În primul rând, voi vorbi despre motivele exagerate. Unii oameni cred că UUID-urile sunt șiruri de caractere deoarece sunt scrise în notație hexazecimală tradițională cu o liniuță: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Într-adevăr, unele baze de date nu au un tip uuid compact (128 de biți), dar PostgreSQL are și are o dimensiune de doi bigint, adică, în comparație cu cantitatea de alte informații din baza de date, suprasarcina este neglijabilă.

UUID-urile sunt, de asemenea, acuzate pe nedrept că sunt greoaie, dar cine le va pronunța, le va tasta sau le va citi? Am spus că are sens ca cheile artificiale să fie afișate, dar nimeni (prin definiție) nu ar trebui să vadă UUID-ul surogat. Este posibil ca UUID-ul să fie tratat de un dezvoltator care rulează comenzi SQL în psql pentru a depana sistemul, dar cam atât. Și dezvoltatorul se poate referi și la șiruri folosind chei mai convenabile, dacă sunt date.

Adevărata problemă cu UUID-urile este că valorile extrem de randomizate duc la amplificarea scrierii datorită scrierilor de pagină completă în jurnalul de scriere anticipată (WAL) . Cu toate acestea, degradarea performanței depinde de fapt de algoritmul de generare UUID.

Să măsurăm amplificarea scrisului . Într-adevăr, problema este în sistemele de fișiere mai vechi. Când PostgreSQL scrie pe disc, schimbă „pagina” de pe disc. Dacă opriți alimentarea computerului, majoritatea sistemelor de fișiere vor raporta în continuare o scriere reușită înainte ca datele să fie stocate în siguranță pe disc. Dacă PostgreSQL percepe naiv o astfel de acțiune ca fiind finalizată, atunci baza de date va fi coruptă la următoarea pornire a sistemului.

Deoarece PostgreSQL nu poate avea încredere în majoritatea sistemelor de operare/sisteme de fișiere/configurații de disc pentru a oferi continuitate, baza de date salvează starea completă a paginii de disc modificate într-un jurnal de scriere anticipată care poate fi folosit pentru a recupera după o posibilă blocare. Indexarea valorilor extrem de randomizate, cum ar fi UUID-urile, implică de obicei o grămadă de pagini de disc diferite și are ca rezultat scrierea dimensiunii întregii pagini (de obicei 4 sau 8 KB) în WAL pentru fiecare intrare nouă. Aceasta este așa-numita scriere pe pagină completă (scriere pe pagină completă, FPW).

Unii algoritmi de generare UUID (cum ar fi „fulg de zăpadă” de pe Twitter sau uuid_generate_v1() în extensia uuid-ossp a PostgreSQL) generează valori crescătoare monoton pe fiecare mașină. Această abordare consolidează scrierile în mai puține pagini de disc și reduce FPW.

5.6 Concluzii și recomandări

Acum că am văzut diferitele tipuri de chei și utilizările lor, vreau să enumerez recomandările mele pentru utilizarea lor în bazele de date.

Pentru fiecare tabel:

  • Definiți și declarați toate cheile naturale.
  • Creați o cheie surogat <table_name>_idde tip UUID cu o valoare implicită de uuid_generate_v1(). Puteți chiar să o marcați ca cheie primară. Dacă adăugați numele tabelului la acest identificator, acest lucru va simplifica JOIN, adică primi JOIN foo USING (bar_id)in loc de JOIN foo ON (foo.bar_id = bar.id). Nu transmiteți această cheie clienților și nu o expuneți deloc în afara bazei de date.
  • Pentru tabelele intermediare care trec prin JOIN, declarați toate coloanele de chei externe ca o singură cheie primară compusă.
  • Opțional, adăugați o cheie artificială care poate fi utilizată în adresa URL sau în alte indicații de referință de șir. Utilizați o grilă Feistel sau pg_hashids pentru a masca numerele întregi cu incrementare automată.
  • Specificați o constrângere în cascadă ON UPDATE RESTRICTfolosind UUID-uri surogat ca chei străine și pentru chei străine artificiale ON UPDATE CASCADE. Alegeți cheile naturale bazate pe propria logică.

Această abordare asigură stabilitatea cheilor interne, permițând și chiar protejând cheile naturale. În plus, cheile artificiale vizibile nu se atașează de nimic. După ce ați înțeles totul corect, nu puteți să vă blocați doar „cheile primare” și să utilizați toate posibilitățile de utilizare a cheilor.