5.1 Introduksjon

Internett er fullt av dogmatiske forskrifter om hvordan nøkler skal velges og brukes i relasjonsdatabaser. Noen ganger blir tvister til og med holivarer: bør naturlige eller kunstige nøkler brukes? Automatisk økning av heltall eller UUID-er?

Etter å ha lest sekstifire artikler, bladd gjennom deler av fem bøker og stilt tonnevis av spørsmål om IRC og StackOverflow, ser det ut til at jeg (Joe "begriffs" Nelson, forfatteren av den originale artikkelen) har lagt bitene i puslespillet sammen og kan nå forsone motstandere. Mange sentrale tvister oppstår faktisk fra en misforståelse av andres synspunkt.

La oss ta problemet fra hverandre og sette det sammen igjen til slutt. Først, la oss stille spørsmålet - hva er en "nøkkel"?

La oss glemme primærnøkler for et øyeblikk, vi er interessert i en mer generell idé. En nøkkel er en kolonne (kolonne) eller kolonner som ikke har dupliserte verdier i rader . Dessuten må kolonnene være irreduserbart unike, dvs. ingen undergruppe av kolonnene har denne unikheten.

Men først, litt teori:

primærnøkkel

Primærnøkkelbrukes direkte til å identifisere rader i en tabell. Den må overholde følgende restriksjoner:

  • Primærnøkkelen må være unik hele tiden.
  • Den må alltid være tilstede i tabellen og ha en verdi.
  • Den bør ikke endre verdien ofte. Ideelt sett bør den ikke endre verdien i det hele tatt .

Vanligvis representerer en primærnøkkel en enkelt kolonne i en tabell, men den kan også være en sammensatt nøkkel som består av flere kolonner.

Sammensatt nøkkel

Egendefinert nøkkel- en kombinasjon av attributter (kolonner) som unikt identifiserer hver tabellrad. Det kan være alle kolonner, og flere, og én. I dette tilfellet bør linjene som inneholder verdiene til disse attributtene ikke gjentas.

Potensiell nøkkel

kandidatnøkkel- representerer den minste sammensatte nøkkelen til relasjonen (tabellen), det vil si et sett med attributter som tilfredsstiller en rekke betingelser:

  • Irreducibility : Den kan ikke reduseres, den inneholder et minimum mulig sett med attributter.
  • Unikhet : Den må ha unike verdier uavhengig av radendringen.
  • Tilstedeværelse av en verdi : Den må ikke ha en nullverdi, dvs. den må ha en verdi.

5.2 Det merkelige tilfellet med primærnøkler

Det vi nettopp kalte "nøkler" i forrige seksjon blir ofte referert til som "kandidatnøkler". Begrepet "kandidat" innebærer at alle slike nøkler konkurrerer om æresrollen som "primærnøkkel" (primærnøkkel), og resten tildeles "alternativnøkler" (alternativnøkler).

Det tok en stund før SQL-implementeringer kom over misforholdet mellom nøkler og relasjonsmodellen, og de tidligste databasene var rettet mot lavnivåkonseptet med en primærnøkkel. Primærnøkler i slike databaser var nødvendig for å identifisere den fysiske plasseringen av en rad på sekvensielle lagringsmedier. Her er hvordan Joe Celko forklarer det:

Begrepet "nøkkel" betydde en filsorteringsnøkkel, som var nødvendig for å utføre eventuelle behandlingsoperasjoner på et sekvensielt filsystem. Et sett med hullkort ble lest i én og bare én rekkefølge; det var umulig å gå tilbake. Tidlige båndstasjoner etterlignet den samme oppførselen og tillot ikke toveis tilgang. Det vil si den originale Sybase SQL Server som kreves for å "spole tilbake" tabellen til begynnelsen for å lese forrige rad.

I moderne SQL trenger du ikke å fokusere på den fysiske representasjonen av informasjon, tabellmodellrelasjoner, og den interne rekkefølgen av rader er ikke viktig i det hele tatt. Men selv nå oppretter SQL-server som standard en klynget indeks for primærnøkler og, i henhold til den gamle tradisjonen, ordner rekkefølgen av radene fysisk.

I de fleste databaser er primærnøkler en saga blott, og gir lite mer enn en refleksjon eller fysisk plassering. For eksempel, i en PostgreSQL-tabell, håndhever deklarering av en primærnøkkel automatisk en begrensning NOT NULLog definerer en standard fremmednøkkel. I tillegg er primærnøkler de foretrukne kolonnene for operatøren JOIN.

Java university

Primærnøkkelen overstyrer ikke muligheten for å deklarere andre nøkler. Samtidig, hvis ingen nøkkel er tilordnet som primær, vil tabellen fortsatt fungere fint. Lynet vil i alle fall ikke treffe deg.

5.3 Finne naturlige nøkler

Nøklene omtalt ovenfor kalles "naturlige" fordi de er egenskaper ved det modellerte objektet som er interessante i seg selv, selv om ingen ønsker å lage en nøkkel ut av dem.

Det første du må huske når du undersøker en tabell for mulige naturlige nøkler, er å prøve å ikke være for smart. Bruker sqlvogel på StackExchange gir følgende råd:

Noen mennesker har problemer med å velge en "naturlig" nøkkel fordi de kommer opp med hypotetiske situasjoner der en bestemt nøkkel kanskje ikke er unik. De forstår ikke selve meningen med oppgaven. Betydningen av nøkkelen er å definere regelen som attributtene til enhver tid må være og alltid vil være unike i en bestemt tabell etter. Tabellen inneholder data i en spesifikk og godt forstått kontekst (i "fagområdet" eller "diskursområdet"), og den eneste betydningen er anvendelsen av begrensningen på det aktuelle området.

Praksis viser at det er nødvendig å innføre en nøkkelbegrensning når kolonnen er unik med de tilgjengelige verdiene og vil forbli det i sannsynlige scenarier. Og om nødvendig kan begrensningen fjernes (hvis dette plager deg, vil vi nedenfor snakke om nøkkelstabilitet.)

For eksempel kan en database med hobbyklubbmedlemmer ha unikhet i to kolonner - first_name, last_name. Med en liten mengde data er duplikater usannsynlig, og før en reell konflikt oppstår er det ganske rimelig å bruke en slik nøkkel.

Etter hvert som databasen vokser og informasjonsvolumet øker, kan det bli vanskeligere å velge en naturlig nøkkel. Dataene vi lagrer er en forenkling av den ytre virkeligheten, og inneholder ikke noen aspekter som skiller objekter i verden, for eksempel deres koordinater som endres over tid. Hvis en gjenstand mangler noen kode, hvordan kan du skille to bokser med drikke eller to bokser med havregryn fra deres romlige plassering eller små forskjeller i vekt eller emballasje?

Det er grunnen til at standardiseringsorganer oppretter og bruker særpreg på produkter. Kjøretøyer er stemplet med et kjøretøyidentifikasjonsnummer (VIN) , bøker er trykt med ISBN-er , og matemballasje har UPC- er . Du kan innvende at disse tallene ikke virker naturlige. Så hvorfor kaller jeg dem naturlige nøkler?

Naturligheten eller kunstigheten til unike egenskaper i en database er i forhold til omverdenen. En nøkkel som var kunstig da den ble opprettet i et standardiseringsorgan eller et offentlig organ, blir naturlig for oss, fordi den blir en standard i hele verden og/eller trykkes på gjenstander.

Det finnes mange industristandarder, offentlige og internasjonale standarder for en rekke fag, inkludert valutaer, språk, finansielle instrumenter, kjemikalier og medisinske diagnoser. Her er noen av verdiene som ofte brukes som naturlige nøkler:

  • ISO 3166 landskoder
  • ISO 639 språkkoder
  • Valutakoder i henhold til ISO 4217
  • Aksjesymboler ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • påloggingsnavn
  • e-post adresse
  • romnummer
  • nettverks mac-adresse
  • breddegrad, lengdegrad for punkter på jordoverflaten

Jeg anbefaler å erklære nøkler når det er mulig og rimelig, kanskje til og med flere nøkler per tabell. Men husk at alle de ovennevnte kan ha unntak.

  • Ikke alle har en e-postadresse, selv om dette kan være akseptabelt under enkelte databaseforhold. Folk endrer også e-postadresser fra tid til annen. (Mer om nøkkelstabilitet senere.)
  • ISIN-aksjesymboler endres fra tid til annen, for eksempel beskriver ikke symbolene GOOG og GOOGL nøyaktig omorganiseringen av selskapet fra Google til Alphabet. Noen ganger kan det oppstå forvirring, som med TWTR og TWTRQ, noen investorer kjøpte feilaktig sistnevnte under Twitter IPO.
  • Personnummer brukes bare av amerikanske statsborgere, har personvernbegrensninger og gjenbrukes etter døden. I tillegg, etter tyveri av dokumenter, kan folk få nye nummer. Til slutt kan samme nummer identifisere både en person og en inntektsskattidentifikator.
  • Postnummer er et dårlig valg for byer. Noen byer har en felles indeks, eller omvendt, det er flere indekser i en by.

5.4 Kunstige nøkler

Gitt at nøkkelen er en kolonne med unike verdier i hver rad, er en måte å lage den på å jukse - du kan skrive fiktive unike verdier i hver rad. Dette er kunstige nøkler: oppfunnet kode som brukes til å referere til data eller objekter.

Det er svært viktig at koden genereres fra selve databasen og er ukjent for alle unntatt brukerne av databasen. Det er dette som skiller kunstige nøkler fra standardiserte naturlige nøkler.

Mens naturlige nøkler har fordelen av å beskytte mot dupliserte eller inkonsekvente rader i en tabell, er kunstige nøkler nyttige fordi de gjør det lettere for mennesker eller andre systemer å referere til raden, og de øker hastigheten på oppslag og sammenføyninger fordi de ikke bruker streng (eller flerkolonne) sammenligninger.

Surrogater

Kunstige nøkler brukes som ankere – uansett hvordan reglene og kolonnene endres, kan én rad alltid identifiseres på samme måte. Den kunstige nøkkelen som brukes til dette formålet kalles en "surrogatnøkkel" og krever spesiell oppmerksomhet. Vi vil vurdere surrogater nedenfor.

Ikke-surrogat kunstige nøkler er nyttige for å referere til en rad utenfor databasen. En kunstig nøkkel identifiserer kort en data eller et objekt: det kan spesifiseres som en URL, legges ved en faktura, dikteres over telefon, hentes fra en bank eller skrives ut på et nummerskilt. (En bils skilt er en naturlig nøkkel for oss, men designet av myndighetene som en kunstig nøkkel.)

Syntetiske nøkler bør velges med hensyn til mulige overføringsmåter for å minimere skrivefeil og feil. Det skal bemerkes at nøkkelen kan leses, skrives ut, sendes via SMS, leses håndskrevet, skrives fra tastaturet og legges inn i en URL. I tillegg inneholder noen kunstige nøkler, for eksempel kredittkortnumre, en kontrollsum slik at hvis visse feil oppstår, kan de i det minste gjenkjennes.

Eksempler:

  • For amerikanske bilskilt er det regler om bruk av tvetydige tegn, som O og 0.
  • Sykehus og apotek må være spesielt forsiktige, gitt legenes håndskrift.
  • Sender du en bekreftelseskode på tekstmelding? Ikke gå utover GSM 03.38-tegnsettet.
  • I motsetning til Base64, som koder for vilkårlige bytedata, bruker Base32 et begrenset tegnsett som er praktisk for mennesker å bruke og håndtere på eldre datasystemer.
  • Proquints er lesbare, skrivbare og uttalbare identifikatorer. Dette er PRO-nouncable QUINT-uplets av utvetydig forståtte konsonanter og vokaler.

Husk at så snart du introduserer din kunstige nøkkel til verden, vil folk merkelig nok begynne å gi den spesiell oppmerksomhet. Bare se på "tyvene"-skiltene eller på systemet for å lage uttalbare identifikatorer, som har blitt den beryktede automatiserte forbannelsesgeneratoren.

Selv om vi begrenser oss til numeriske taster, er det tabuer som den trettende etasje. Mens proquints har en høyere tetthet av informasjon per talt stavelse, er tall også fine på mange måter: i URL-er, pin-tastaturer og håndskrevne notater, så lenge mottakeren vet at nøkkelen bare er tall.

Vær imidlertid oppmerksom på at du ikke bør bruke sekvensiell rekkefølge i offentlige numeriske taster, da dette lar deg rote gjennom ressurser (/videos/1.mpeg, /videos/2.mpeg, og så videre) og også lekker informasjon om nummeret data. Legg et Feistel-nett over en tallsekvens og bevar unikheten mens du skjuler rekkefølgen på tallene.

Det eneste argumentet mot å erklære ekstra nøkler er at hver nye bringer med seg en annen unik indeks og øker kostnadene ved å skrive til bordet. Selvfølgelig avhenger det av hvor viktig dataenes korrekthet er for deg, men mest sannsynlig bør nøklene fortsatt erklæres.

Det er også verdt å deklarere flere kunstige nøkler, hvis noen. For eksempel har en organisasjon jobbkandidater (søkere) og ansatte (ansatte). Hver ansatt var en gang en kandidat, og refererer til kandidater ved sin egen identifikator, som også skal være den ansattes nøkkel. Et annet eksempel, du kan angi ansatt-ID og påloggingsnavn som to nøkler i Ansatte.

5.5 Surrogatnøkler

Som allerede nevnt kalles en viktig type kunstig nøkkel en "surrogatnøkkel". Den trenger ikke være kortfattet og farbar som andre kunstige nøkler, men brukes som en intern etikett som alltid identifiserer strengen. Den brukes i SQL, men applikasjonen har ikke eksplisitt tilgang til den.

Hvis du er kjent med PostgreSQLs systemkolonner, kan du tenke på surrogater nesten som en databaseimplementeringsparameter (som ctid), som imidlertid aldri endres. Surrogatverdien velges én gang per rad og endres aldri deretter.

Surrogatnøkler er gode som fremmednøkler, og kaskadebegrensninger må spesifiseres ON UPDATE RESTRICTfor å matche surrogatens uforanderlighet.

På den annen side bør fremmednøkler til offentlig delte nøkler merkes med ON UPDATE CASCADE, for å gi maksimal fleksibilitet. En gjennomgripende oppdatering kjører på samme isolasjonsnivå som den omkringliggende transaksjonen, så ikke bekymre deg for samtidighetsproblemer - databasen vil være bra hvis du velger et strengt isolasjonsnivå.

Ikke gjør surrogatnøkler "naturlige". Når du viser verdien av surrogatnøkkelen til sluttbrukere, eller enda verre, lar dem jobbe med den verdien (spesielt gjennom et oppslag), gir du nøkkelen en verdi. Da kan den viste nøkkelen fra databasen din bli en naturlig nøkkel i andres database.

Ved å tvinge eksterne systemer til å bruke andre kunstige nøkler som er spesielt utviklet for overføring, kan vi endre disse nøklene etter behov for å møte endrede behov, samtidig som vi opprettholder intern referanseintegritet med surrogater.

Automatisk økning av INT/BIGINT

Den vanligste bruken av surrogatnøkler er den automatisk økende "bigserial" -kolonnen , også kjent som IDENTITY . (Faktisk støtter PostgreSQL 10 nå IDENTITY-konstruksjonen, det samme gjør Oracle, se LAG TABELL.)

Imidlertid tror jeg at et automatisk økende heltall er et dårlig valg for surrogatnøkler. Denne oppfatningen er upopulær, så la meg forklare.

Ulemper med serienøkler:

  • Hvis alle sekvenser starter på 1 og øker trinnvis, vil rader fra forskjellige tabeller ha de samme nøkkelverdiene. Dette alternativet er ikke ideelt, det er fortsatt å foretrekke å bruke usammenhengende sett med nøkler i tabeller, slik at for eksempel spørringer ikke ved et uhell kan forveksle konstanter i JOINog returnere uventede resultater. (Alternativt, for å sikre at det ikke er noen kryss, kan man konstruere hver sekvens fra multipler av forskjellige primtall, men dette ville være ganske arbeidskrevende.)
  • Oppfordringen nextval() til å generere en sekvens i dagens distribuerte SQL resulterer i at hele systemet ikke skaleres godt.
  • Å konsumere data fra en database som også brukte sekvensielle nøkler vil resultere i konflikter fordi sekvensielle verdier ikke vil være unike på tvers av systemer.
  • Fra et filosofisk synspunkt er den sekvensielle økningen i antall assosiert med gamle systemer der rekkefølgen på linjene ble antydet. Hvis du nå vil bestille radene, så gjør det eksplisitt med en tidsstempelkolonne eller noe som gir mening i dataene dine. Ellers brytes den første normalformen.
  • Svak grunn, men disse korte identifikatorene er fristende å fortelle noen.

UUID

La oss se på et annet alternativ: å bruke store heltall (128-bit) generert i henhold til et tilfeldig mønster. Algoritmer for å generere slike universelt unike identifikatorer (UUID) har ekstremt lav sannsynlighet for å velge samme verdi to ganger, selv når de kjøres på to forskjellige prosessorer samtidig.

I så fall virker UUID- er som et naturlig valg å bruke som surrogatnøkler, gjør de ikke? Hvis du vil merke rader på en unik måte, er det ingenting som slår en unik etikett!

Så hvorfor bruker ikke alle dem i PostgreSQL? Det er flere konstruerte årsaker til dette og en logisk en som kan omgås, og jeg vil presentere benchmarks for å illustrere poenget mitt.

Først skal jeg snakke om de langsøkte årsakene. Noen mennesker tror at UUID-er er strenger fordi de er skrevet i tradisjonell heksadesimal notasjon med en bindestrek: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Noen databaser har faktisk ikke en kompakt (128-biters) uuid-type, men PostgreSQL har og har en størrelse på to , det vil si at bigintsammenlignet med mengden annen informasjon i databasen er overheaden ubetydelig.

UUID-er blir også urettferdig anklaget for å være tungvint, men hvem skal uttale dem, skrive dem eller lese dem? Vi sa at det er fornuftig at kunstige nøkler vises, men ingen (per definisjon) skal se surrogat-UUID. Det er mulig at UUID vil bli behandlet av en utvikler som kjører SQL-kommandoer i psql for å feilsøke systemet, men det er omtrent det. Og utvikleren kan også referere til strenger ved å bruke mer praktiske nøkler, hvis de er gitt.

Det virkelige problemet med UUID-er er at svært randomiserte verdier fører til skriveforsterkning på grunn av skriving av helside til WAL-loggen . Imidlertid avhenger ytelsesdegraderingen faktisk av UUID-genereringsalgoritmen.

La oss måle skriveforsterkning . I sannhet er problemet i eldre filsystemer. Når PostgreSQL skriver til disk, endrer den "siden" på disken. Hvis du slår av datamaskinens strøm, vil de fleste filsystemer fortsatt rapportere en vellykket skriving før dataene er trygt lagret på disken. Hvis PostgreSQL naivt oppfatter en slik handling som fullført, vil databasen bli ødelagt ved neste systemoppstart.

Siden PostgreSQL ikke kan stole på de fleste operativsystemer/filsystemer/diskkonfigurasjoner for å gi kontinuitet, lagrer databasen hele tilstanden til den endrede disksiden til en fremskrivningslogg som kan brukes til å gjenopprette etter en mulig krasj. Indeksering av svært randomiserte verdier som UUID-er involverer vanligvis en haug med forskjellige disksider og resulterer i at hele sidestørrelsen (vanligvis 4 eller 8 KB) blir skrevet til WAL for hver ny oppføring. Dette er den såkalte helsideskrivingen (helsidesskriving, FPW).

Noen UUID-genereringsalgoritmer (som Twitters "snowflake" eller uuid_generate_v1() i PostgreSQLs uuid-ossp-utvidelse) genererer monotont økende verdier på hver maskin. Denne tilnærmingen konsoliderer skriving til færre disksider og reduserer FPW.

5.6 Konklusjoner og anbefalinger

Nå som vi har sett de forskjellige typene nøkler og deres bruk, vil jeg liste opp mine anbefalinger for bruk av dem i databasene dine.

For hvert bord:

  • Definer og erklær alle naturlige nøkler.
  • Opprett en surrogatnøkkel <table_name>_idav typen UUID med standardverdien uuid_generate_v1(). Du kan til og med merke den som en primærnøkkel. Hvis du legger til navnet på tabellen i denne identifikatoren, vil dette forenkle JOIN, dvs. motta JOIN foo USING (bar_id)i stedet for JOIN foo ON (foo.bar_id = bar.id). Ikke gi denne nøkkelen til klienter og ikke eksponer den utenfor databasen i det hele tatt.
  • For mellomtabeller som går gjennom JOIN, erklærer du alle fremmednøkkelkolonner som en enkelt sammensatt primærnøkkel.
  • Du kan eventuelt legge til en kunstig nøkkel som kan brukes i URL-en eller andre strengreferanseindikasjoner. Bruk et Feistel-rutenett eller pg_hashids for å maskere automatisk økende heltall.
  • Spesifiser en kaskadebegrensning ON UPDATE RESTRICTved å bruke surrogat-UUID-er som fremmednøkler og for kunstige fremmednøkler ON UPDATE CASCADE. Velg naturlige nøkler basert på din egen logikk.

Denne tilnærmingen sikrer stabiliteten til interne nøkler samtidig som den tillater og til og med beskytter naturlige nøkler. I tillegg fester ikke synlige kunstige nøkler seg til noe. Etter å ha forstått alt riktig, kan du ikke bare henge deg på "primærnøkler" og bruke alle mulighetene for å bruke nøkler.