5.1 Inleiding

Het internet staat vol met dogmatische voorschriften over hoe sleutels moeten worden gekozen en gebruikt in relationele databases. Soms veranderen geschillen zelfs in holivars: moeten natuurlijke of kunstmatige sleutels worden gebruikt? Gehele getallen of UUID's automatisch verhogen?

Na het lezen van vierenzestig artikelen, het bladeren door secties van vijf boeken en het stellen van talloze vragen over IRC en StackOverflow, lijkt het alsof ik (Joe "begriffs" Nelson, de auteur van het originele artikel) de stukjes van de puzzel in elkaar heb gezet en kan nu tegenstanders verzoenen. Veel belangrijke geschillen komen eigenlijk voort uit een verkeerd begrip van het standpunt van iemand anders.

Laten we het probleem uit elkaar halen en aan het eind weer in elkaar zetten. Laten we eerst de vraag stellen - wat is een "sleutel"?

Laten we de primaire sleutels even vergeten, we zijn geïnteresseerd in een meer algemeen idee. Een sleutel is een kolom (kolom) of kolommen die geen dubbele waarden in rijen hebben . Ook moeten de kolommen onherleidbaar uniek zijn, d.w.z. geen enkele subset van de kolommen heeft deze uniciteit.

Maar eerst wat theorie:

hoofdsleutel

Hoofdsleuteldirect gebruikt om rijen in een tabel te identificeren. Het moet voldoen aan de volgende beperkingen:

  • De primaire sleutel moet altijd uniek zijn.
  • Het moet altijd aanwezig zijn in de tabel en een waarde hebben.
  • Het zou de waarde niet vaak moeten veranderen. Idealiter zou het de waarde helemaal niet moeten veranderen .

Meestal vertegenwoordigt een primaire sleutel een enkele kolom van een tabel, maar het kan ook een samengestelde sleutel zijn die uit meerdere kolommen bestaat.

Samengestelde sleutel

Aangepaste sleutel- een combinatie van attributen (kolommen) die elke tabelrij op unieke wijze identificeren. Het kunnen alle kolommen zijn, en meerdere, en één. In dit geval mogen de regels die de waarden van deze attributen bevatten niet worden herhaald.

Potentiële sleutel

kandidaat sleutel- vertegenwoordigt de minimale samengestelde sleutel van de relatie (tabel), dat wil zeggen een set attributen die aan een aantal voorwaarden voldoet:

  • Onherleidbaarheid : het kan niet worden gereduceerd, het bevat de minimaal mogelijke set attributen.
  • Uniciteit : het moet unieke waarden hebben, ongeacht de rijwisseling.
  • Aanwezigheid van een waarde : het mag geen nulwaarde hebben, d.w.z. het moet een waarde hebben.

5.2 Het merkwaardige geval van primaire sleutels

Wat we zojuist "sleutels" noemden in de vorige sectie, wordt gewoonlijk "kandidaatsleutels" genoemd. De term "kandidaat" impliceert dat al dergelijke sleutels strijden om de ererol van "primaire sleutel" (primaire sleutel), en de rest krijgt "alternatieve sleutels" (alternatieve sleutels) toegewezen.

Het duurde even voordat SQL-implementaties de discrepantie tussen sleutels en het relationele model hadden overwonnen, en de vroegste databases waren gericht op het low-level concept van een primaire sleutel. Primaire sleutels in dergelijke databases waren nodig om de fysieke locatie van een rij op sequentiële opslagmedia te identificeren. Hier is hoe Joe Celko het uitlegt:

De term "sleutel" betekende een bestandssorteersleutel, die nodig was om alle verwerkingsbewerkingen op een sequentieel bestandssysteem uit te voeren. Een set ponskaarten werd in één en slechts één volgorde gelezen; het was onmogelijk om terug te gaan. Vroege tapedrives bootsten hetzelfde gedrag na en stonden geen bidirectionele toegang toe. Dat wil zeggen, de oorspronkelijke Sybase SQL Server moest de tabel "terugspoelen" naar het begin om de vorige rij te lezen.

In moderne SQL hoeft u zich niet te concentreren op de fysieke weergave van informatie, relaties tussen tabellen en de interne volgorde van rijen is helemaal niet belangrijk. Maar zelfs nu maakt de SQL-server standaard een geclusterde index voor primaire sleutels en regelt, volgens de oude traditie, de volgorde van de rijen fysiek.

In de meeste databases behoren primaire sleutels tot het verleden en bieden ze weinig meer dan een weerspiegeling of fysieke locatie. In een PostgreSQL-tabel wordt bijvoorbeeld door het declareren van een primaire sleutel automatisch een beperking afgedwongen NOT NULLen wordt een standaard externe sleutel gedefinieerd. Daarnaast zijn primaire sleutels de voorkeurskolommen voor de operator JOIN.

De primaire sleutel heeft geen voorrang op de mogelijkheid om andere sleutels te declareren. Tegelijkertijd, als er geen sleutel als primair is toegewezen, zal de tabel nog steeds goed werken. Bliksem zal je in ieder geval niet treffen.

Java webinar

5.3 Natuurlijke sleutels vinden

De hierboven besproken sleutels worden "natuurlijk" genoemd omdat het eigenschappen zijn van het gemodelleerde object die op zichzelf interessant zijn, zelfs als niemand er een sleutel van wil maken.

Het eerste dat u moet onthouden bij het onderzoeken van een tabel op mogelijke natuurlijke sleutels, is proberen niet te slim te zijn. Gebruiker sqlvogel op StackExchange geeft het volgende advies:

Sommige mensen hebben moeite met het kiezen van een "natuurlijke" sleutel omdat ze hypothetische situaties bedenken waarin een bepaalde sleutel misschien niet uniek is. Ze begrijpen de betekenis van de taak niet. De betekenis van de sleutel is om de regel te definiëren volgens welke de attributen op een gegeven moment uniek moeten zijn en altijd zullen zijn in een bepaalde tabel. De tabel bevat gegevens in een specifieke en goed begrepen context (in het "onderwerpgebied" of "discoursgebied"), en de enige betekenis is de toepassing van de beperking in dat specifieke gebied.

De praktijk leert dat het noodzakelijk is om een ​​sleutelbeperking in te voeren wanneer de kolom uniek is met de beschikbare waarden en dat ook zal blijven in waarschijnlijke scenario's. En indien nodig kan de beperking worden verwijderd (als dit u stoort, zullen we het hieronder hebben over sleutelstabiliteit.)

Een database met leden van een hobbyclub kan bijvoorbeeld uniek zijn in twee kolommen - first_name, last_name. Met een kleine hoeveelheid gegevens zijn duplicaten onwaarschijnlijk, en voordat er een echt conflict ontstaat, is het redelijk om zo'n sleutel te gebruiken.

Naarmate de database groeit en het volume aan informatie toeneemt, kan het moeilijker worden om een ​​natuurlijke sleutel te kiezen. De gegevens die we opslaan zijn een vereenvoudiging van de externe realiteit en bevatten niet enkele aspecten die objecten in de wereld onderscheiden, zoals hun coördinaten die in de loop van de tijd veranderen. Als een object geen enkele code heeft, hoe kun je dan twee blikjes drank of twee dozen havermout onderscheiden van hun ruimtelijke opstelling of kleine verschillen in gewicht of verpakking?

Daarom creëren normalisatie-instellingen onderscheidende merken op producten en brengen deze aan. Voertuigen zijn gestempeld met een voertuigidentificatienummer (VIN) , boeken zijn gedrukt met ISBN's en voedselverpakkingen hebben UPC's . U kunt tegenwerpen dat deze cijfers niet natuurlijk lijken. Dus waarom noem ik ze natuurlijke sleutels?

De natuurlijkheid of kunstmatigheid van unieke eigenschappen in een database is relatief ten opzichte van de buitenwereld. Een sleutel die kunstmatig was toen deze werd gemaakt in een normalisatie-instelling of overheidsinstantie, wordt voor ons vanzelfsprekend, omdat deze een standaard wordt in de hele wereld en / of wordt afgedrukt op objecten.

Er zijn veel industriële, openbare en internationale normen voor een verscheidenheid aan onderwerpen, waaronder valuta's, talen, financiële instrumenten, chemicaliën en medische diagnoses. Hier zijn enkele van de waarden die vaak worden gebruikt als natuurlijke sleutels:

  • ISO 3166 landcodes
  • ISO 639 taalcodes
  • Valutacodes volgens ISO 4217
  • Voorraadsymbolen ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • login namen
  • e-mailadressen
  • kamer nummers
  • netwerk mac-adres
  • breedtegraad, lengtegraad voor punten op het aardoppervlak

Ik raad aan om waar mogelijk en redelijk sleutels te declareren, misschien zelfs meerdere sleutels per tafel. Maar vergeet niet dat al het bovenstaande uitzonderingen kan hebben.

  • Niet iedereen heeft een e-mailadres, hoewel dit onder sommige databaseomstandigheden acceptabel kan zijn. Ook veranderen mensen van tijd tot tijd hun e-mailadres. (Later meer over sleutelstabiliteit.)
  • ISIN-aandelensymbolen veranderen van tijd tot tijd, de symbolen GOOG en GOOGL beschrijven bijvoorbeeld niet nauwkeurig de reorganisatie van het bedrijf van Google naar Alphabet. Soms kan er verwarring ontstaan, zoals bij TWTR en TWTRQ, sommige investeerders kochten de laatste ten onrechte tijdens de beursgang van Twitter.
  • Burgerservicenummers worden alleen gebruikt door Amerikaanse burgers, hebben privacybeperkingen en worden na overlijden opnieuw gebruikt. Bovendien kunnen mensen na diefstal van documenten nieuwe nummers krijgen. Ten slotte kan hetzelfde nummer zowel een persoon als een identificatienummer voor de inkomstenbelasting identificeren.
  • Postcodes zijn een slechte keuze voor steden. Sommige steden hebben een gemeenschappelijke index, of vice versa, er zijn meerdere indexen in één stad.

5.4 Kunstmatige sleutels

Aangezien de sleutel een kolom is met unieke waarden in elke rij, is een manier om deze te maken vals spelen - u kunt fictieve unieke waarden in elke rij schrijven. Dit zijn kunstmatige sleutels: verzonnen code die wordt gebruikt om naar gegevens of objecten te verwijzen.

Het is erg belangrijk dat de code uit de database zelf wordt gegenereerd en voor niemand bekend is, behalve voor de gebruikers van de database. Dit is wat kunstmatige sleutels onderscheidt van gestandaardiseerde natuurlijke sleutels.

Hoewel natuurlijke sleutels het voordeel hebben dat ze bescherming bieden tegen dubbele of inconsistente rijen in een tabel, zijn kunstmatige sleutels nuttig omdat ze het voor mensen of andere systemen gemakkelijker maken om naar de rij te verwijzen, en ze het opzoeken en samenvoegen versnellen omdat ze geen gebruik maken van string (of multi-column) vergelijkingen sleutels.

surrogaten

Kunstmatige sleutels worden gebruikt als ankers - ongeacht hoe de regels en kolommen veranderen, één rij kan altijd op dezelfde manier worden geïdentificeerd. De hiervoor gebruikte kunstmatige sleutel wordt een "surrogaatsleutel" genoemd en vereist speciale aandacht. We zullen hieronder surrogaten overwegen.

Niet-vervangende kunstmatige sleutels zijn handig om van buiten de database naar een rij te verwijzen. Een kunstmatige sleutel identificeert in het kort een data of object: het kan gespecificeerd worden als een URL, toegevoegd aan een factuur, gedicteerd via de telefoon, verkregen van een bank, of afgedrukt op een kentekenplaat. (Het kenteken van een auto is voor ons een natuurlijke sleutel, maar door de overheid ontworpen als een kunstmatige sleutel.)

Bij de keuze van synthetische sleutels moet rekening worden gehouden met de mogelijke wijze van verzending om typefouten en fouten te minimaliseren. Opgemerkt moet worden dat de sleutel kan worden uitgesproken, gelezen, afgedrukt, verzonden via sms, handgeschreven gelezen, getypt vanaf het toetsenbord en ingebed in een URL. Bovendien bevatten sommige kunstmatige sleutels, zoals creditcardnummers, een checksum, zodat als er bepaalde fouten optreden, deze in ieder geval kunnen worden herkend.

Voorbeelden:

  • Voor Amerikaanse kentekenplaten zijn er regels over het gebruik van dubbelzinnige tekens, zoals O en 0.
  • Ziekenhuizen en apotheken moeten extra voorzichtig zijn, gezien het handschrift van artsen.
  • Stuur je een bevestigingscode per sms? Ga niet verder dan de tekenset GSM 03.38.
  • In tegenstelling tot Base64, dat willekeurige bytegegevens codeert, gebruikt Base32 een beperkte tekenset die handig is voor mensen om te gebruiken en te verwerken op oudere computersystemen.
  • Proquints zijn leesbare, beschrijfbare en uitspreekbare identifiers. Dit zijn PRO-nouncable QUINT-uplets van ondubbelzinnig begrepen medeklinkers en klinkers.

Houd er rekening mee dat zodra u uw kunstmatige sleutel aan de wereld introduceert, mensen er vreemd genoeg speciale aandacht aan zullen besteden. Kijk maar eens naar de kentekenplaten van de "dieven" of naar het systeem voor het maken van uitspreekbare identifiers, dat de beruchte geautomatiseerde vloekgenerator is geworden.

Ook al beperken we ons tot cijfertoetsen, er zijn taboes zoals de dertiende verdieping. Hoewel proquints een hogere informatiedichtheid per gesproken lettergreep hebben, zijn cijfers ook in veel opzichten prima: in URL's, pin-toetsenborden en handgeschreven notities, zolang de ontvanger weet dat de sleutel alleen cijfers is.

Houd er echter rekening mee dat u geen sequentiële volgorde moet gebruiken in openbare numerieke toetsen, omdat u hierdoor door bronnen kunt snuffelen (/videos/1.mpeg, /videos/2.mpeg, enzovoort) en ook informatie over het nummer lekt gegevens. Plaats een Feistel-net op een reeks getallen en behoud de uniciteit terwijl u de volgorde van de getallen verbergt.

Het enige argument tegen het declareren van extra sleutels is dat elke nieuwe een andere unieke index met zich meebrengt en de kosten van het schrijven naar de tabel verhoogt. Het hangt er natuurlijk van af hoe belangrijk de juistheid van de gegevens voor u is, maar hoogstwaarschijnlijk moeten de sleutels toch worden opgegeven.

Het is ook de moeite waard om meerdere kunstmatige sleutels te declareren, indien aanwezig. Een organisatie heeft bijvoorbeeld sollicitanten (Sollicitanten) en werknemers (Werknemers). Elke werknemer was ooit een kandidaat en verwijst naar kandidaten met zijn eigen identificatiecode, die ook de sleutel van de werknemer zou moeten zijn. Een ander voorbeeld, u kunt de medewerker-id en inlognaam instellen als twee sleutels in Medewerkers.

5.5 Surrogaatsleutels

Zoals reeds vermeld, wordt een belangrijk type kunstmatige sleutel een "surrogaatsleutel" genoemd. Het hoeft niet beknopt en redelijk te zijn zoals andere kunstmatige sleutels, maar wordt gebruikt als een intern label dat altijd de tekenreeks identificeert. Het wordt gebruikt in SQL, maar de toepassing heeft er geen expliciete toegang toe.

Als je bekend bent met de systeemkolommen van PostgreSQL, dan kun je surrogaten bijna zien als een database-implementatieparameter (zoals ctid), die echter nooit verandert. De surrogaatwaarde wordt eenmaal per rij geselecteerd en daarna nooit meer gewijzigd.

Surrogaatsleutels zijn geweldig als externe sleutels, en trapsgewijze beperkingen moeten worden gespecificeerd ON UPDATE RESTRICTom overeen te komen met de onveranderlijkheid van het surrogaat.

Aan de andere kant moeten externe sleutels van openbaar gedeelde sleutels worden gemarkeerd met ON UPDATE CASCADE, om maximale flexibiliteit te bieden. Een trapsgewijze update draait op hetzelfde isolatieniveau als de omringende transactie, dus maak je geen zorgen over gelijktijdigheidsproblemen - de database voldoet prima als je een strikt isolatieniveau kiest.

Maak surrogaatsleutels niet "natuurlijk". Zodra u de waarde van de surrogaatsleutel aan eindgebruikers laat zien, of erger nog, hen met die waarde laat werken (vooral door op te zoeken), geeft u de sleutel in feite een waarde. Dan kan de getoonde sleutel uit jouw database een natuurlijke sleutel worden in de database van iemand anders.

Door externe systemen te dwingen andere kunstmatige sleutels te gebruiken die speciaal zijn ontworpen voor verzending, kunnen we die sleutels naar behoefte wijzigen om aan veranderende behoeften te voldoen, terwijl de interne referentiële integriteit met surrogaten behouden blijft.

Automatische toename INT/BIGINT

Het meest gebruikelijke gebruik van surrogaatsleutels is de automatisch oplopende "bigserial" -kolom , ook wel bekend als IDENTITY . (In feite ondersteunt PostgreSQL 10 nu de IDENTITY-constructie, net als Oracle, zie CREATE TABLE.)

Ik ben echter van mening dat een automatisch oplopend geheel getal een slechte keuze is voor surrogaatsleutels. Deze mening is niet populair, dus laat me het uitleggen.

Nadelen van seriële sleutels:

  • Als alle reeksen beginnen bij 1 en stapsgewijs toenemen, hebben rijen uit verschillende tabellen dezelfde sleutelwaarden. Deze optie is niet ideaal, het verdient nog steeds de voorkeur om onsamenhangende sleutelsets in tabellen te gebruiken, zodat query's bijvoorbeeld niet per ongeluk constanten kunnen verwarren JOINen onverwachte resultaten kunnen opleveren. (Als alternatief, om ervoor te zorgen dat er geen intersecties zijn, zou men elke reeks kunnen construeren uit veelvouden van verschillende priemgetallen, maar dit zou nogal omslachtig zijn.)
  • De aanroep nextval() om een ​​reeks te genereren in de huidige gedistribueerde SQL resulteert in het niet goed schalen van het hele systeem.
  • Het consumeren van gegevens uit een database die ook sequentiële sleutels gebruikte, leidt tot conflicten omdat sequentiële waarden niet uniek zijn voor alle systemen.
  • Vanuit een filosofisch oogpunt wordt de opeenvolgende toename van getallen geassocieerd met oude systemen waarin de volgorde van de lijnen werd geïmpliceerd. Als u de rijen nu wilt ordenen, doe dat dan expliciet met een tijdstempelkolom of iets dat logisch is in uw gegevens. Anders wordt de eerste normaalvorm geschonden.
  • Zwakke reden, maar deze korte identifiers zijn verleidelijk om iemand te vertellen.

UUID

Laten we eens kijken naar een andere optie: het gebruik van grote gehele getallen (128-bits) gegenereerd volgens een willekeurig patroon. Algoritmen voor het genereren van dergelijke Universally Unique Identifiers (UUID's) hebben een extreem lage kans om twee keer dezelfde waarde te kiezen, zelfs als ze tegelijkertijd op twee verschillende processors worden uitgevoerd.

In dat geval lijken UUID's een natuurlijke keuze om als surrogaatsleutels te gebruiken, nietwaar? Als u rijen op een unieke manier wilt labelen, dan gaat er niets boven een uniek label!

Dus waarom gebruikt niet iedereen ze in PostgreSQL? Daar zijn verschillende gekunstelde redenen voor en één logische die kan worden omzeild, en ik zal benchmarks presenteren om mijn punt te illustreren.

Eerst zal ik het hebben over de vergezochte redenen. Sommige mensen denken dat UUID's strings zijn omdat ze zijn geschreven in de traditionele hexadecimale notatie met een streepje: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Sommige databases hebben inderdaad geen compact (128-bits) uuid-type, maar PostgreSQL wel en heeft een grootte van twee bigint, d.w.z. vergeleken met de hoeveelheid andere informatie in de database is de overhead te verwaarlozen.

UUID's worden er ook ten onrechte van beschuldigd omslachtig te zijn, maar wie zal ze uitspreken, typen of lezen? We zeiden dat het zinvol is om kunstmatige sleutels weer te geven, maar dat niemand (per definitie) de surrogaat-UUID zou moeten zien. Het is mogelijk dat de UUID wordt afgehandeld door een ontwikkelaar die SQL-opdrachten uitvoert in psql om het systeem te debuggen, maar dat is het dan ook. En de ontwikkelaar kan ook naar strings verwijzen met behulp van handigere toetsen, als die worden gegeven.

Het echte probleem met UUID's is dat sterk gerandomiseerde waarden leiden tot schrijfversterking vanwege het schrijven van een volledige pagina naar het vooruitschrijflogboek (WAL) . De verslechtering van de prestaties hangt echter af van het algoritme voor het genereren van de UUID.

Laten we de schrijfversterking meten . In werkelijkheid zit het probleem in oudere bestandssystemen. Wanneer PostgreSQL naar schijf schrijft, verandert het de "pagina" op schijf. Als u de computer uitschakelt, zullen de meeste bestandssystemen nog steeds melden dat het schrijven is gelukt voordat de gegevens veilig op schijf zijn opgeslagen. Als PostgreSQL zo'n actie naïef als voltooid beschouwt, zal de database tijdens de volgende systeemstart beschadigd raken.

Aangezien PostgreSQL de meeste besturingssystemen/bestandssystemen/schijfconfiguraties niet kan vertrouwen om continuïteit te bieden, slaat de database de volledige status van de gewijzigde schijfpagina op in een vooruitschrijflogboek dat kan worden gebruikt om te herstellen van een mogelijke crash. Het indexeren van zeer willekeurige waarden zoals UUID's omvat doorgaans een aantal verschillende schijfpagina's en resulteert in het wegschrijven van de volledige paginagrootte (meestal 4 of 8 KB) naar de WAL voor elk nieuw item. Dit is de zogenaamde full-page write (full-page write, FPW).

Sommige algoritmen voor het genereren van UUID's (zoals Twitter's "snowflake" of uuid_generate_v1() in de uuid-ossp-extensie van PostgreSQL) genereren monotoon toenemende waarden op elke machine. Deze aanpak consolideert schrijfbewerkingen in minder schijfpagina's en vermindert FPW.

5.6 Conclusies en aanbevelingen

Nu we de verschillende soorten sleutels en hun gebruik hebben gezien, wil ik mijn aanbevelingen voor het gebruik ervan in uw databases opsommen.

Voor elke tafel:

  • Definieer en declareer alle natuurlijke sleutels.
  • Maak een vervangende sleutel <table_name>_idvan het type UUID met een standaardwaarde van uuid_generate_v1(). U kunt het zelfs markeren als een primaire sleutel. Als u de naam van de tabel aan deze identifier toevoegt, wordt dit vereenvoudigd JOIN, d.w.z. ontvangen JOIN foo USING (bar_id)in plaats van JOIN foo ON (foo.bar_id = bar.id). Geef deze sleutel niet door aan clients en stel deze helemaal niet buiten de database bloot.
  • Voor tussenliggende tabellen die passeren JOIN, declareert u alle externe-sleutelkolommen als één samengestelde primaire sleutel.
  • Voeg optioneel een kunstmatige sleutel toe die kan worden gebruikt in de URL of andere tekenreeksreferentie-indicaties. Gebruik een Feistel-raster of pg_hashids om automatisch oplopende gehele getallen te maskeren.
  • Geef een trapsgewijze beperking op ON UPDATE RESTRICTmet behulp van surrogaat-UUID's als externe sleutels en voor kunstmatige externe sleutels ON UPDATE CASCADE. Kies natuurlijke sleutels op basis van uw eigen logica.

Deze benadering zorgt voor de stabiliteit van interne sleutels terwijl natuurlijke sleutels worden toegestaan ​​en zelfs beschermd. Bovendien hechten zichtbare kunstmatige sleutels nergens aan. Als je alles goed hebt begrepen, kun je niet alleen blijven hangen aan "primaire sleutels" en alle mogelijkheden van het gebruik van sleutels gebruiken.