5.1 Indledning

Internettet er fyldt med dogmatiske forskrifter om, hvordan nøgler skal vælges og bruges i relationelle databaser. Nogle gange bliver tvister endda til holivarer: skal naturlige eller kunstige nøgler bruges? Automatisk stigning i heltal eller UUID'er?

Efter at have læst fireogtres artikler, bladret gennem sektioner af fem bøger og stillet tonsvis af spørgsmål om IRC og StackOverflow, ser det ud til, at jeg (Joe "begriffs" Nelson, forfatteren til den originale artikel) har lagt brikkerne i puslespillet sammen og kan nu forlige modstandere. Mange centrale tvister opstår faktisk ud fra en misforståelse af en andens synspunkt.

Lad os skille problemet ad og sætte det sammen igen til sidst. Lad os først stille spørgsmålet - hvad er en "nøgle"?

Lad os glemme alt om primære nøgler et øjeblik, vi er interesserede i en mere generel idé. En nøgle er en kolonne (kolonne) eller kolonner, der ikke har duplikerede værdier i rækker . Desuden skal kolonnerne være irreducerbart unikke, dvs. ingen delmængde af kolonnerne har denne unikhed.

Men først lidt teori:

primærnøgle

Primærnøglebruges direkte til at identificere rækker i en tabel. Det skal overholde følgende begrænsninger:

  • Den primære nøgle skal være unik hele tiden.
  • Det skal altid være til stede i tabellen og have en værdi.
  • Det bør ikke ændre sin værdi ofte. Ideelt set bør det slet ikke ændre værdien .

Typisk repræsenterer en primær nøgle en enkelt kolonne i en tabel, men den kan også være en sammensat nøgle, der består af flere kolonner.

Sammensat nøgle

Brugerdefineret nøgle- en kombination af attributter (kolonner), der unikt identificerer hver tabelrække. Det kan være alle kolonner og flere og en. I dette tilfælde bør linjerne, der indeholder værdierne af disse attributter, ikke gentages.

Potentialnøgle

kandidatnøgle- repræsenterer den mindste sammensatte nøgle af relationen (tabellen), det vil sige et sæt attributter, der opfylder en række betingelser:

  • Ureducerbarhed : Den kan ikke reduceres, den indeholder det mindst mulige sæt af attributter.
  • Unikhed : Den skal have unikke værdier uanset rækkeændringen.
  • Tilstedeværelse af en værdi : Den må ikke have en nulværdi, dvs. den skal have en værdi.

5.2 Det mærkelige tilfælde af primære nøgler

Det, vi lige kaldte "nøgler" i det foregående afsnit, omtales almindeligvis som "kandidatnøgler". Udtrykket "kandidat" indebærer, at alle sådanne nøgler konkurrerer om den æresrolle som "primær nøgle" (primær nøgle), og resten tildeles "alternative nøgler" (alternativnøgler).

Det tog et stykke tid for SQL-implementeringer at komme over misforholdet mellem nøgler og den relationelle model, og de tidligste databaser var rettet mod lavniveaukonceptet af en primær nøgle. Primære nøgler i sådanne databaser var påkrævet for at identificere den fysiske placering af en række på sekventielle lagermedier. Her er hvordan Joe Celko forklarer det:

Udtrykket "nøgle" betød en filsorteringsnøgle, som var nødvendig for at udføre enhver behandlingsoperation på et sekventielt filsystem. Et sæt hulkort blev læst i én og kun én rækkefølge; det var umuligt at gå tilbage. Tidlige båndstationer efterlignede den samme adfærd og tillod ikke tovejsadgang. Det vil sige den originale Sybase SQL Server, der kræves for at "spole tilbage" tabellen til begyndelsen for at læse den forrige række.

I moderne SQL behøver du ikke at fokusere på den fysiske repræsentation af information, tabeller modelforhold, og den interne rækkefølge af rækker er slet ikke vigtig. Men selv nu opretter SQL-server som standard et klynget indeks for primærnøgler og arrangerer ifølge den gamle tradition fysisk rækkefølgen af ​​rækkerne.

I de fleste databaser hører primærnøgler fortiden til og giver ikke meget mere end en refleksion eller fysisk placering. For eksempel, i en PostgreSQL-tabel, håndhæver erklæringen af ​​en primær nøgle automatisk en begrænsning NOT NULLog definerer en standard fremmednøgle. Derudover er primærnøgler de foretrukne kolonner for operatøren JOIN.

Java webinar

Den primære nøgle tilsidesætter ikke muligheden for at erklære andre nøgler. På samme tid, hvis ingen nøgle er tildelt som primær, vil tabellen stadig fungere fint. Lynet vil under alle omstændigheder ikke ramme dig.

5.3 At finde naturlige nøgler

Nøglerne diskuteret ovenfor kaldes "naturlige", fordi de er egenskaber ved det modellerede objekt, der er interessante i sig selv, selvom ingen ønsker at lave en nøgle ud af dem.

Den første ting, du skal huske, når du undersøger en tabel for mulige naturlige nøgler, er at prøve ikke at være for smart. Bruger sqlvogel på StackExchange giver følgende råd:

Nogle mennesker har svært ved at vælge en "naturlig" nøgle, fordi de kommer op i hypotetiske situationer, hvor en bestemt nøgle måske ikke er unik. De forstår ikke selve meningen med opgaven. Betydningen af ​​nøglen er at definere den regel, ifølge hvilken attributterne til enhver tid skal være og altid vil være unikke i en bestemt tabel. Tabellen indeholder data i en specifik og velforstået sammenhæng (i "emneområdet" eller "diskursområdet"), og den eneste betydning er anvendelsen af ​​begrænsningen på det pågældende område.

Praksis viser, at det er nødvendigt at indføre en nøglebegrænsning, når kolonnen er unik med de tilgængelige værdier og vil forblive det i sandsynlige scenarier. Og om nødvendigt kan begrænsningen fjernes (hvis dette generer dig, så vil vi nedenfor tale om nøglestabilitet.)

For eksempel kan en database med hobbyklubmedlemmer have unikhed i to kolonner - first_name, last_name. Med en lille mængde data er dubletter usandsynlige, og før en reel konflikt opstår, er det ganske rimeligt at bruge sådan en nøgle.

Efterhånden som databasen vokser og mængden af ​​information stiger, kan det blive sværere at vælge en naturlig nøgle. De data, vi gemmer, er en forenkling af den ydre virkelighed og indeholder ikke nogle aspekter, der adskiller objekter i verden, såsom deres koordinater, der ændrer sig over tid. Hvis en genstand mangler nogen kode, hvordan kan du så skelne mellem to dåser med drikke eller to kasser havregryn fra deres rumlige placering eller små forskelle i vægt eller emballage?

Det er grunden til, at standardiseringsorganer skaber og anvender særpræg på produkter. Køretøjer er stemplet med et Vehicle Identification Number (VIN) , bøger er trykt med ISBN'er , og fødevareemballage har UPC'er . Du kan indvende, at disse tal ikke virker naturlige. Så hvorfor kalder jeg dem naturlige nøgler?

Naturligheden eller kunstigheden af ​​unikke egenskaber i en database er i forhold til omverdenen. En nøgle, der var kunstig, da den blev skabt i et standardiseringsorgan eller en offentlig myndighed, bliver naturlig for os, fordi den bliver en standard i hele verden og/eller er trykt på genstande.

Der er mange industri-, offentlige og internationale standarder for en række emner, herunder valutaer, sprog, finansielle instrumenter, kemikalier og medicinske diagnoser. Her er nogle af de værdier, der ofte bruges som naturlige nøgler:

  • ISO 3166 landekoder
  • ISO 639 sprogkoder
  • Valutakoder i henhold til ISO 4217
  • Aktiesymboler ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • login navne
  • email adresse
  • rumnumre
  • netværks mac-adresse
  • breddegrad, længdegrad for punkter på jordens overflade

Jeg anbefaler at erklære nøgler, når det er muligt og rimeligt, måske endda flere nøgler pr. tabel. Men husk, at alle ovenstående kan have undtagelser.

  • Ikke alle har en e-mailadresse, selvom dette kan være acceptabelt under nogle databaseforhold. Også, folk ændrer deres e-mailadresser fra tid til anden. (Mere om nøglestabilitet senere.)
  • ISIN-aktiesymboler ændrer sig fra tid til anden, for eksempel beskriver symbolerne GOOG og GOOGL ikke nøjagtigt omorganiseringen af ​​virksomheden fra Google til Alphabet. Nogle gange kan der opstå forvirring, som med TWTR og TWTRQ, købte nogle investorer ved en fejl sidstnævnte under Twitter-børsnoteringen.
  • CPR-numre bruges kun af amerikanske statsborgere, har privatlivsbegrænsninger og genbruges efter døden. Derudover kan folk efter tyveri af dokumenter få nye numre. Endelig kan det samme nummer identificere både en person og en indkomstskatteidentifikator.
  • Postnummer er et dårligt valg for byer. Nogle byer har et fælles indeks, eller omvendt er der flere indeks i en by.

5.4 Kunstige nøgler

Da nøglen er en kolonne med unikke værdier i hver række, er en måde at skabe den på at snyde - du kan skrive fiktive unikke værdier i hver række. Disse er kunstige nøgler: opfundet kode, der bruges til at referere til data eller objekter.

Det er meget vigtigt, at koden er genereret fra selve databasen og er ukendt for alle undtagen brugerne af databasen. Det er det, der adskiller kunstige nøgler fra standardiserede naturlige nøgler.

Mens naturlige nøgler har fordelen ved at beskytte mod duplikerede eller inkonsistente rækker i en tabel, er kunstige nøgler nyttige, fordi de gør det lettere for mennesker eller andre systemer at henvise til rækken, og de fremskynder opslag og joinforbindelser, fordi de ikke bruger strenge (eller multi-kolonne) sammenligninger nøgler.

Surrogater

Kunstige nøgler bruges som ankre – uanset hvordan reglerne og kolonnerne ændres, kan én række altid identificeres på samme måde. Den kunstige nøgle, der bruges til dette formål, kaldes en "surrogatnøgle" og kræver særlig opmærksomhed. Vi vil overveje surrogater nedenfor.

Ikke-surrogat kunstige nøgler er nyttige til at henvise til en række uden for databasen. En kunstig nøgle identificerer kort en data eller et objekt: det kan angives som en URL, vedhæftes en faktura, dikteres over telefonen, fås fra en bank eller printes på en nummerplade. (En bils nummerplade er en naturlig nøgle for os, men designet af regeringen som en kunstig nøgle.)

Syntetiske nøgler bør vælges under hensyntagen til de mulige transmissionsmetoder for at minimere tastefejl og fejl. Det skal bemærkes, at nøglen kan læses, læses udskrives, sendes via SMS, læses håndskrevet, tastes fra tastaturet og indlejres i en URL. Derudover indeholder nogle kunstige nøgler, såsom kreditkortnumre, en kontrolsum, så hvis visse fejl opstår, kan de i det mindste genkendes.

Eksempler:

  • For amerikanske nummerplader er der regler om brugen af ​​tvetydige tegn, såsom O og 0.
  • Hospitaler og apoteker skal være særligt forsigtige i betragtning af lægernes håndskrift.
  • Sender du en bekræftelseskode på sms? Gå ikke ud over GSM 03.38-tegnsættet.
  • I modsætning til Base64, som koder for vilkårlige bytedata, bruger Base32 et begrænset tegnsæt, som er praktisk for mennesker at bruge og håndtere på ældre computersystemer.
  • Proquints er læsbare, skrivbare og udtalelige identifikatorer. Disse er PRO-nouncable QUINT-uplets af utvetydigt forståede konsonanter og vokaler.

Husk på, at så snart du introducerer din kunstige nøgle til verden, vil folk mærkeligt nok begynde at give den særlig opmærksomhed. Se bare på "tyvene"-nummerpladerne eller på systemet til at skabe udtalelige identifikatorer, som er blevet den berygtede automatiserede forbandelsesgenerator.

Selvom vi begrænser os til numeriske taster, er der tabuer som den trettende etage. Mens proquints har en højere tæthed af information pr. talt stavelse, er tal også fine på mange måder: i URL'er, pin-tastaturer og håndskrevne noter, så længe modtageren ved, at nøglen kun er tal.

Bemærk dog, at du ikke bør bruge sekventiel rækkefølge i offentlige numeriske taster, da dette giver dig mulighed for at rode gennem ressourcer (/videos/1.mpeg, /videos/2.mpeg, og så videre) og også lækker information om nummeret data. Læg et Feistel-net over en række tal og bevar det unikke, mens du skjuler rækkefølgen af ​​tallene.

Det eneste argument mod at erklære yderligere nøgler er, at hver ny medfører et andet unikt indeks og øger omkostningerne ved at skrive til bordet. Det afhænger selvfølgelig af, hvor vigtig dataenes rigtighed er for dig, men højst sandsynligt skal nøglerne stadig erklæres.

Det er også værd at angive flere kunstige nøgler, hvis nogen. For eksempel har en organisation jobkandidater (ansøgere) og ansatte (medarbejdere). Hver medarbejder var engang en kandidat, og refererer til kandidater ved deres egen identifikator, som også skal være medarbejderens nøgle. Et andet eksempel, du kan indstille medarbejder-id og login-navn som to nøgler i Medarbejdere.

5.5 Surrogatnøgler

Som allerede nævnt kaldes en vigtig type kunstig nøgle en "surrogatnøgle". Den behøver ikke at være kortfattet og acceptabel ligesom andre kunstige nøgler, men bruges som en intern etiket, der altid identificerer strengen. Det bruges i SQL, men applikationen har ikke eksplicit adgang til det.

Hvis du er bekendt med PostgreSQL's systemkolonner, så kan du tænke på surrogater nærmest som en databaseimplementeringsparameter (som ctid), som dog aldrig ændres. Surrogatværdien vælges én gang pr. række og ændres aldrig derefter.

Surrogatnøgler er gode som fremmednøgler, og kaskadebegrænsninger skal specificeres ON UPDATE RESTRICTfor at matche surrogatens uforanderlighed.

På den anden side bør fremmednøgler til offentligt delte nøgler markeres med ON UPDATE CASCADE, for at give maksimal fleksibilitet. En kaskadende opdatering kører på samme isolationsniveau som den omgivende transaktion, så du skal ikke bekymre dig om samtidighedsproblemer – databasen vil være i orden, hvis du vælger et strengt isolationsniveau.

Gør ikke surrogatnøgler "naturlige". Når du først viser værdien af ​​surrogatnøglen til slutbrugere, eller endnu værre, lader dem arbejde med denne værdi (især gennem et opslag), giver du i virkeligheden nøglen en værdi. Så kan den viste nøgle fra din database blive en naturlig nøgle i en andens database.

At tvinge eksterne systemer til at bruge andre kunstige nøgler, der er specielt designet til transmission, giver os mulighed for at ændre disse nøgler efter behov for at imødekomme skiftende behov, samtidig med at vi opretholder intern referenceintegritet med surrogater.

Auto-increment INT/BIGINT

Den mest almindelige brug for surrogatnøgler er den automatiske "bigserial" -kolonne , også kendt som IDENTITY . (Faktisk understøtter PostgreSQL 10 nu IDENTITY-konstruktionen, ligesom Oracle, se OPRET TABEL.)

Jeg mener dog, at et heltal med automatisk stigning er et dårligt valg for surrogatnøgler. Denne udtalelse er upopulær, så lad mig forklare.

Ulemper ved serielle nøgler:

  • Hvis alle sekvenser starter ved 1 og stiger trinvist, vil rækker fra forskellige tabeller have de samme nøgleværdier. Denne mulighed er ikke ideel, det er stadig at foretrække at bruge usammenhængende nøglesæt i tabeller, så forespørgsler for eksempel ikke ved et uheld kan forveksle konstanter i JOINog returnere uventede resultater. (Alternativt, for at sikre, at der ikke er nogen skæringspunkter, kunne man konstruere hver sekvens ud fra multipla af forskellige primtal, men det ville være ret besværligt.)
  • Opfordringen nextval() til at generere en sekvens i dagens distribuerede SQL resulterer i, at hele systemet ikke skaleres godt.
  • Indtagelse af data fra en database, der også brugte sekventielle nøgler, vil resultere i konflikter, fordi sekventielle værdier ikke vil være unikke på tværs af systemer.
  • Fra et filosofisk synspunkt er den sekventielle stigning i antallet forbundet med gamle systemer, hvor rækkefølgen af ​​linjerne var underforstået. Hvis du nu vil bestille rækkerne, så gør det eksplicit med en tidsstempelkolonne eller noget, der giver mening i dine data. Ellers overtrædes den første normale form.
  • Svag grund, men disse korte identifikatorer er fristende at fortælle nogen.

UUID

Lad os se på en anden mulighed: at bruge store heltal (128-bit) genereret i henhold til et tilfældigt mønster. Algoritmer til at generere sådanne universelt unikke identifikatorer (UUID'er) har en ekstrem lav sandsynlighed for at vælge den samme værdi to gange, selv når de kører på to forskellige processorer på samme tid.

I så fald virker UUID'er som et naturligt valg at bruge som surrogatnøgler, ikke? Hvis du vil mærke rækker på en unik måde, så er der intet, der slår en unik etiket!

Så hvorfor bruger alle dem ikke i PostgreSQL? Der er flere konstruerede årsager til dette og en logisk, der kan arbejdes rundt, og jeg vil præsentere benchmarks for at illustrere min pointe.

Først vil jeg tale om de vidtløftige årsager. Nogle mennesker tror, ​​at UUID'er er strenge, fordi de er skrevet i traditionel hexadecimal notation med en bindestreg: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Nogle databaser har faktisk ikke en kompakt (128-bit) uuid-type, men PostgreSQL har og har en størrelse på to bigint, dvs. sammenlignet med mængden af ​​anden information i databasen er overheaden ubetydelig.

UUID'er bliver også uretfærdigt beskyldt for at være besværlige, men hvem vil udtale dem, skrive dem eller læse dem? Vi sagde, at det giver mening, at kunstige nøgler vises, men ingen (per definition) bør se surrogat-UUID. Det er muligt, at UUID'et vil blive behandlet af en udvikler, der kører SQL-kommandoer i psql for at fejlsøge systemet, men det er det hele. Og udvikleren kan også henvise til strenge ved hjælp af mere praktiske nøgler, hvis de er givet.

Det virkelige problem med UUID'er er, at meget randomiserede værdier fører til skriveforstærkning på grund af helsideskrivninger til skrive-ahead-loggen (WAL) . Ydeevneforringelsen afhænger dog faktisk af UUID-genereringsalgoritmen.

Lad os måle skriveforstærkning . I sandhed er problemet i ældre filsystemer. Når PostgreSQL skriver til disk, ændrer den "siden" på disken. Hvis du slukker for computeren, vil de fleste filsystemer stadig rapportere en vellykket skrivning, før dataene er sikkert gemt på disken. Hvis PostgreSQL naivt opfatter en sådan handling som afsluttet, vil databasen blive ødelagt under den næste systemopstart.

Da PostgreSQL ikke kan stole på, at de fleste operativsystemer/filsystemer/diskkonfigurationer giver kontinuitet, gemmer databasen den fulde tilstand af den ændrede diskside til en fremskrivningslog, der kan bruges til at gendanne efter et muligt nedbrud. Indeksering af meget randomiserede værdier som UUID'er involverer typisk en masse forskellige disksider og resulterer i, at den fulde sidestørrelse (normalt 4 eller 8 KB) bliver skrevet til WAL for hver ny post. Dette er den såkaldte helsidesskrivning (helsidesskrivning, FPW).

Nogle UUID-genereringsalgoritmer (såsom Twitters "snefnug" eller uuid_generate_v1() i PostgreSQL's uuid-ossp-udvidelse) genererer monotont stigende værdier på hver maskine. Denne tilgang konsoliderer skrivninger til færre disksider og reducerer FPW.

5.6 Konklusioner og anbefalinger

Nu hvor vi har set de forskellige typer nøgler og deres anvendelser, vil jeg liste mine anbefalinger til brugen af ​​dem i dine databaser.

For hver tabel:

  • Definer og erklær alle naturlige nøgler.
  • Opret en surrogatnøgle <table_name>_idaf typen UUID med en standardværdi på uuid_generate_v1(). Du kan endda markere det som en primær nøgle. Hvis du tilføjer tabellens navn til denne identifikator, vil dette forenkle JOIN, dvs. modtage JOIN foo USING (bar_id)i stedet for JOIN foo ON (foo.bar_id = bar.id). Giv ikke denne nøgle videre til klienter, og udsæt den slet ikke uden for databasen.
  • For mellemliggende tabeller, der passerer gennem JOIN, skal du erklære alle fremmednøglekolonner som en enkelt sammensat primærnøgle.
  • Tilføj eventuelt en kunstig nøgle, der kan bruges i URL'en eller andre strengreferenceindikationer. Brug et Feistel-gitter eller pg_hashids til at maskere automatisk stigende heltal.
  • Angiv en kaskadebegrænsning ON UPDATE RESTRICTved hjælp af surrogat-UUID'er som fremmednøgler og for kunstige fremmednøgler ON UPDATE CASCADE. Vælg naturlige nøgler baseret på din egen logik.

Denne tilgang sikrer stabiliteten af ​​interne nøgler, mens den tillader og endda beskytter naturlige nøgler. Desuden bliver synlige kunstige nøgler ikke knyttet til noget. Når du har forstået alt korrekt, kan du ikke kun blive hængt på "primære nøgler" og bruge alle mulighederne for at bruge nøgler.