Nycklar i databasen

5.1 Inledning

Internet är fullt av dogmatiska föreskrifter om hur nycklar ska väljas och användas i relationsdatabaser. Ibland blir tvister till och med till holivar: ska naturliga eller konstgjorda nycklar användas? Automatisk ökning av heltal eller UUID?

Efter att ha läst sextiofyra artiklar, bläddrat igenom avsnitt i fem böcker och ställt massor av frågor om IRC och StackOverflow, verkar jag (Joe "begriffs" Nelson, författaren till den ursprungliga artikeln) ha lagt pusselbitarna ihop och kan nu försona motståndare. Många viktiga tvister uppstår faktiskt från ett missförstånd av någon annans synvinkel.

Låt oss ta isär problemet och sätta ihop det igen i slutet. Låt oss först ställa frågan - vad är en "nyckel"?

Låt oss glömma primärnycklarna för ett ögonblick, vi är intresserade av en mer allmän idé. En nyckel är en kolumn (kolumn) eller kolumner som inte har dubbletter av värden i rader . Kolumnerna måste också vara irreducerbart unika, dvs ingen delmängd av kolumnerna har denna unikhet.

Men först lite teori:

primärnyckel

Primärnyckelanvänds direkt för att identifiera rader i en tabell. Den måste följa följande begränsningar:

• Den primära nyckeln måste vara unik hela tiden.
• Det måste alltid finnas i tabellen och ha ett värde.
• Det bör inte ändra sitt värde ofta. Helst bör det inte ändra värdet alls .

Vanligtvis representerar en primärnyckel en enda kolumn i en tabell, men den kan också vara en sammansatt nyckel som består av flera kolumner.

Kompositnyckel

Anpassad nyckel- en kombination av attribut (kolumner) som unikt identifierar varje tabellrad. Det kan vara alla kolumner, och flera, och en. I det här fallet bör raderna som innehåller värdena för dessa attribut inte upprepas.

Potentiell nyckel

kandidatnyckel- representerar den minsta sammansatta nyckeln för relationen (tabellen), det vill säga en uppsättning attribut som uppfyller ett antal villkor:

• Oreducerbarhet : Den kan inte reduceras, den innehåller minsta möjliga uppsättning attribut.
• Unikhet : Den måste ha unika värden oavsett radändring.
• Närvaro av ett värde : Det får inte ha ett nollvärde, dvs det måste ha ett värde.

5.2 Det märkliga fallet med primärnycklar

Det vi just kallade "nycklar" i föregående avsnitt kallas vanligtvis för "kandidatnycklar". Termen "kandidat" innebär att alla sådana nycklar tävlar om hedersrollen som "primärnyckel" (primärnyckel), och resten tilldelas "alternativa nycklar" (alternativnyckel).

Det tog ett tag för SQL-implementeringar att komma över mismatchen mellan nycklar och relationsmodellen, och de tidigaste databaserna var inriktade på lågnivåkonceptet med en primärnyckel. Primära nycklar i sådana databaser krävdes för att identifiera den fysiska platsen för en rad på sekventiella lagringsmedia. Så här förklarar Joe Celko det:

Termen "nyckel" betydde en filsorteringsnyckel, som behövdes för att utföra alla bearbetningsoperationer på ett sekventiellt filsystem. En uppsättning hålkort lästes i en och endast en ordning; det var omöjligt att gå tillbaka. Tidiga bandenheter efterliknade samma beteende och tillät inte dubbelriktad åtkomst. Det vill säga den ursprungliga Sybase SQL Server som krävs för att "spola tillbaka" tabellen till början för att läsa föregående rad.

I modern SQL behöver du inte fokusera på den fysiska representationen av information, tabellmodellrelationer och den interna ordningen på rader är inte alls viktig. Men redan nu skapar SQL-servern som standard ett klustrat index för primärnycklar och, enligt den gamla traditionen, ordnar ordningen på raderna fysiskt.

I de flesta databaser är primärnycklar ett minne blott och ger inte mycket mer än en reflektion eller fysisk plats. Till exempel, i en PostgreSQL-tabell, upprätthåller en primärnyckel automatiskt en begränsning NOT NULLoch definierar en främmande standardnyckel. Dessutom är primärnycklar de föredragna kolumnerna för operatören JOIN.

Primärnyckeln åsidosätter inte möjligheten att deklarera andra nycklar. Samtidigt, om ingen nyckel är tilldelad som primär, kommer tabellen fortfarande att fungera bra. Blixten kommer i alla fall inte att träffa dig.

5.3 Hitta naturliga nycklar

De nycklar som diskuterats ovan kallas "naturliga" eftersom de är egenskaper hos det modellerade objektet som är intressanta i sig, även om ingen vill göra en nyckel av dem.

Det första du bör komma ihåg när du undersöker en tabell för möjliga naturliga nycklar är att försöka att inte vara för smart. Användaren sqlvogel på StackExchange ger följande råd:

Vissa människor har svårt att välja en "naturlig" nyckel eftersom de kommer upp i hypotetiska situationer där en viss nyckel kanske inte är unik. De förstår inte själva innebörden av uppgiften. Innebörden av nyckeln är att definiera regeln enligt vilken attributen vid varje given tidpunkt måste vara och alltid kommer att vara unika i en viss tabell. Tabellen innehåller data i ett specifikt och välförstått sammanhang (i "ämnesområdet" eller "diskursområdet"), och den enda innebörden är tillämpningen av begränsningen inom just det området.

Övning visar att det är nödvändigt att införa en nyckelrestriktion när kolumnen är unik med tillgängliga värden och kommer att förbli så i sannolika scenarier. Och vid behov kan begränsningen tas bort (om detta stör dig, kommer vi nedan att prata om nyckelstabilitet.)

Till exempel kan en databas med hobbyklubbmedlemmar ha unika egenskaper i två kolumner - first_name, last_name. Med en liten mängd data är dubbletter osannolikt, och innan en verklig konflikt uppstår är det ganska rimligt att använda en sådan nyckel.

När databasen växer och informationsvolymen ökar, kan det bli svårare att välja en naturlig nyckel. Datan vi lagrar är en förenkling av den yttre verkligheten, och innehåller inte några aspekter som särskiljer objekt i världen, såsom deras koordinater som förändras över tiden. Om ett föremål saknar kod, hur kan du skilja två burkar med dryck eller två lådor havregryn från deras rumsliga arrangemang eller små skillnader i vikt eller förpackning?

Det är därför standardiseringsorgan skapar och använder särskiljande märken på produkter. Fordon stämplas med ett fordonsidentifikationsnummer (VIN), böcker är tryckta med ISBN och livsmedelsförpackningar har UPC . Du kanske invänder att dessa siffror inte verkar naturliga. Så varför kallar jag dem naturliga nycklar?

Naturligheten eller artificiteten hos unika egenskaper i en databas är relativt till omvärlden. En nyckel som var konstgjord när den skapades i ett standardiseringsorgan eller statlig myndighet blir naturlig för oss, eftersom den blir en standard i hela världen och/eller trycks på föremål.

Det finns många industristandarder, offentliga och internationella standarder för en mängd olika ämnen, inklusive valutor, språk, finansiella instrument, kemikalier och medicinska diagnoser. Här är några av värdena som ofta används som naturliga nycklar:

• ISO 3166 landskoder
• ISO 639 språkkoder
• Valutakoder enligt ISO 4217
• Aktiesymboler ISIN
• UPC/EAN, VIN, GTIN, ISBN
• inloggningsnamn
• mejladresser
• rumsnummer
• nätverks mac-adress
• latitud, longitud för punkter på jordens yta

Jag rekommenderar att du deklarerar nycklar när det är möjligt och rimligt, kanske till och med flera nycklar per tabell. Men kom ihåg att alla ovanstående kan ha undantag.

• Alla har inte en e-postadress, även om detta kan vara acceptabelt under vissa databasförhållanden. Dessutom ändrar människor sina e-postadresser då och då. (Mer om nyckelstabilitet senare.)
• ISIN-aktiesymboler ändras från tid till annan, till exempel beskriver symbolerna GOOG och GOOGL inte omorganisationen av företaget från Google till Alphabet. Ibland kan förvirring uppstå, som med TWTR och TWTRQ, vissa investerare köpte av misstag det sistnämnda under Twitter IPO.
• Personnummer används endast av amerikanska medborgare, har integritetsbegränsningar och återanvänds efter döden. Dessutom, efter stöld av dokument, kan människor få nya nummer. Slutligen kan samma nummer identifiera både en person och ett inkomstskattenummer.
• Postnummer är ett dåligt val för städer. Vissa städer har ett gemensamt index, eller vice versa, det finns flera index i en stad.

5.4 Konstgjorda nycklar

Med tanke på att nyckeln är en kolumn med unika värden i varje rad, är ett sätt att skapa den att fuska - du kan skriva fiktiva unika värden i varje rad. Dessa är konstgjorda nycklar: uppfunnen kod som används för att referera till data eller objekt.

Det är mycket viktigt att koden genereras från själva databasen och är okänd för alla utom användarna av databasen. Det är detta som skiljer konstgjorda nycklar från standardiserade naturliga nycklar.

Medan naturliga nycklar har fördelen att skydda mot dubbletter eller inkonsekventa rader i en tabell, är konstgjorda nycklar användbara eftersom de gör det lättare för människor eller andra system att referera till raden, och de påskyndar uppslagningar och kopplingar eftersom de inte använder sträng- (eller flera kolumner) jämförelser.

Surrogat

Konstgjorda nycklar används som ankare - oavsett hur reglerna och kolumnerna ändras kan en rad alltid identifieras på samma sätt. Den konstgjorda nyckeln som används för detta ändamål kallas "surrogatnyckel" och kräver särskild uppmärksamhet. Vi kommer att överväga surrogat nedan.

Icke-surrogat konstgjorda nycklar är användbara för att referera till en rad utanför databasen. En konstgjord nyckel identifierar kort en data eller ett objekt: det kan anges som en URL, bifogas en faktura, dikteras via telefon, erhålls från en bank eller tryckas på en registreringsskylt. (En bils registreringsskylt är en naturlig nyckel för oss, men designad av regeringen som en konstgjord nyckel.)

Syntetiska nycklar bör väljas med hänsyn till möjliga överföringssätt för att minimera stavfel och fel. Det bör noteras att nyckeln kan läsas upp, läsas ut, skickas via SMS, läsas handskriven, skrivas från tangentbordet och bäddas in i en URL. Dessutom innehåller vissa konstgjorda nycklar, såsom kreditkortsnummer, en kontrollsumma så att om vissa fel uppstår kan de åtminstone kännas igen.

Exempel:

• För amerikanska registreringsskyltar finns det regler om användningen av tvetydiga tecken, som O och 0.
• Sjukhus och apotek måste vara extra försiktiga med tanke på läkarnas handstil.
• Skickar du en bekräftelsekod via sms? Gå inte längre än teckenuppsättningen GSM 03.38.
• Till skillnad från Base64, som kodar godtyckliga bytedata, använder Base32 en begränsad teckenuppsättning som är bekväm för människor att använda och hantera på äldre datorsystem.
• Proquints är läsbara, skrivbara och uttalbara identifierare. Dessa är PRO-nouncable QUINT-uplets av otvetydigt förstådda konsonanter och vokaler.

Tänk på att så fort du introducerar din konstgjorda nyckel till världen, kommer folk konstigt nog att börja ge den särskild uppmärksamhet. Titta bara på "tjuvarnas" registreringsskyltar eller på systemet för att skapa uttalbara identifierare, som har blivit den ökända automatiserade förbannelsegeneratorn.

Även om vi begränsar oss till numeriska tangenter, finns det tabun som den trettonde våningen. Medan proquints har en högre täthet av information per talad stavelse, är siffror också bra på många sätt: i webbadresser, pin-tangentbord och handskrivna anteckningar, så länge som mottagaren vet att nyckeln bara är siffror.

Observera dock att du inte bör använda sekventiell ordning i offentliga numeriska nycklar, eftersom detta gör att du kan rota igenom resurser (/videos/1.mpeg, /videos/2.mpeg, och så vidare) och även läcker information om numret data. Lägg ett Feistel-nät över en sekvens av nummer och bevara unikheten samtidigt som du döljer ordningen på siffrorna.

Det enda argumentet mot att deklarera ytterligare nycklar är att varje ny tar med sig ytterligare ett unikt index och ökar kostnaden för att skriva till bordet. Naturligtvis beror det på hur viktig informationens riktighet är för dig, men troligtvis bör nycklarna fortfarande deklareras.

Det är också värt att deklarera flera konstgjorda nycklar, om några. Till exempel har en organisation jobbkandidater (sökande) och anställda (anställda). Varje anställd var en gång en kandidat och hänvisar till kandidater med sin egen identifierare, som också ska vara medarbetarens nyckel. Ett annat exempel, du kan ställa in anställds ID och inloggningsnamn som två nycklar i Medarbetare.

5.5 Surrogatnycklar

Som redan nämnts kallas en viktig typ av konstgjord nyckel för "surrogatnyckel". Den behöver inte vara kortfattad och acceptabel som andra konstgjorda nycklar, utan används som en intern etikett som alltid identifierar strängen. Den används i SQL, men applikationen kommer inte explicit åt den.

Om du är bekant med PostgreSQL:s systemkolumner, kan du tänka på surrogat nästan som en databasimplementeringsparameter (som ctid), som dock aldrig ändras. Surrogatvärdet väljs en gång per rad och ändras aldrig därefter.

Surrogatnycklar är bra som främmande nycklar, och kaskadbegränsningar måste anges ON UPDATE RESTRICTför att matcha surrogatets oföränderlighet.

Å andra sidan bör främmande nycklar till offentligt delade nycklar märkas med , ON UPDATE CASCADEför att ge maximal flexibilitet. En kaskaduppdatering körs på samma isoleringsnivå som den omgivande transaktionen, så oroa dig inte för samtidighetsproblem – databasen kommer att fungera bra om du väljer en strikt isoleringsnivå.

Gör inte surrogatnycklar "naturliga". När du väl visar värdet av surrogatnyckeln för slutanvändare, eller ännu värre, låter dem arbeta med det värdet (särskilt genom en uppslagning), ger du nyckeln ett värde. Då kan den visade nyckeln från din databas bli en naturlig nyckel i någon annans databas.

Genom att tvinga externa system att använda andra konstgjorda nycklar som är speciellt utformade för överföring kan vi ändra dessa nycklar efter behov för att möta förändrade behov, samtidigt som vi bibehåller intern referensintegritet med surrogat.

Automatisk ökning av INT/BIGINT

Den vanligaste användningen av surrogatnycklar är den automatiskt ökande "bigserial" -kolumnen , även känd som IDENTITY . (Faktum är att PostgreSQL 10 nu stöder IDENTITY-konstruktionen, liksom Oracle, se SKAPA TABELL.)

Jag tror dock att ett heltal som ökar automatiskt är ett dåligt val för surrogatnycklar. Denna åsikt är impopulär, så låt mig förklara.

Nackdelar med seriella nycklar:

• Om alla sekvenser börjar på 1 och ökar stegvis, kommer rader från olika tabeller att ha samma nyckelvärden. Det här alternativet är inte idealiskt, det är fortfarande att föredra att använda disjunkta uppsättningar av nycklar i tabeller, så att till exempel frågor inte av misstag kan förväxla konstanter i JOINoch returnera oväntade resultat. (Alternativt, för att säkerställa att det inte finns några skärningspunkter, kan man konstruera varje sekvens från multiplar av olika primtal, men det skulle vara ganska mödosamt.)
• Uppmaningen nextval() att generera en sekvens i dagens distribuerade SQL resulterar i att hela systemet inte skalas bra.
• Att konsumera data från en databas som också använde sekventiella nycklar kommer att resultera i konflikter eftersom sekventiella värden inte kommer att vara unika över system.
• Ur en filosofisk synvinkel är den sekventiella ökningen av antal förknippad med gamla system där ordningen på raderna antyddes. Om du nu vill beställa raderna, gör det då explicit med en tidsstämpelkolumn eller något som är vettigt i din data. Annars kränks den första normala formen.
• Svag anledning, men dessa korta identifierare är frestande att berätta för någon.

UUID

Låt oss titta på ett annat alternativ: att använda stora heltal (128-bitars) genererade enligt ett slumpmässigt mönster. Algoritmer för att generera sådana universellt unika identifierare (UUID) har en extremt låg sannolikhet att välja samma värde två gånger, även när de körs på två olika processorer samtidigt.

I så fall verkar UUID som ett naturligt val att använda som surrogatnycklar, eller hur? Om du vill märka rader på ett unikt sätt så är det inget som slår en unik etikett!

Så varför använder inte alla dem i PostgreSQL? Det finns flera konstruerade skäl till detta och en logisk som kan kringgås, och jag kommer att presentera riktmärken för att illustrera min poäng.

Först ska jag prata om de långsökta anledningarna. Vissa människor tror att UUID är strängar eftersom de är skrivna i traditionell hexadecimal notation med ett bindestreck: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Visserligen har vissa databaser inte en kompakt (128-bitars) uuid-typ, men PostgreSQL har och har en storlek på två , bigintdvs. jämfört med mängden annan information i databasen är omkostnaden försumbar.

UUID:er anklagas också orättvist för att vara besvärliga, men vem kommer att uttala dem, skriva dem eller läsa dem? Vi sa att det är vettigt att konstgjorda nycklar visas, men ingen (per definition) borde se surrogat-UUID. Det är möjligt att UUID kommer att hanteras av en utvecklare som kör SQL-kommandon i psql för att felsöka systemet, men det är ungefär det. Och utvecklaren kan också hänvisa till strängar med mer praktiska nycklar, om de ges.

Det verkliga problemet med UUID:er är att mycket randomiserade värden leder till skrivförstärkning på grund av helsidesskrivningar till WAL-loggen . Men prestandaförsämringen beror faktiskt på UUID-genereringsalgoritmen.

Låt oss mäta skrivförstärkning . I själva verket är problemet i äldre filsystem. När PostgreSQL skriver till disk ändrar den "sidan" på disken. Om du stänger av strömmen till datorn kommer de flesta filsystem fortfarande att rapportera en lyckad skrivning innan data lagras säkert på disken. Om PostgreSQL naivt uppfattar en sådan åtgärd som slutförd, kommer databasen att skadas under nästa systemstart.

Eftersom PostgreSQL inte kan lita på de flesta operativsystem/filsystem/diskkonfigurationer för att tillhandahålla kontinuitet, sparar databasen hela tillståndet för den ändrade skivsidan till en skriv-ahead-logg som kan användas för att återställa från en eventuell krasch. Att indexera mycket slumpmässiga värden som UUID innebär vanligtvis en massa olika disksidor och resulterar i att hela sidstorleken (vanligtvis 4 eller 8 KB) skrivs till WAL för varje ny post. Detta är den så kallade helsidesskrivningen (helsidesskrivning, FPW).

Vissa UUID-genereringsalgoritmer (som Twitters "snöflinga" eller uuid_generate_v1() i PostgreSQL:s uuid-ossp-tillägg) genererar monotont ökande värden på varje maskin. Detta tillvägagångssätt konsoliderar skrivningar till färre disksidor och minskar FPW.

5.6 Slutsatser och rekommendationer

Nu när vi har sett de olika typerna av nycklar och deras användning vill jag lista mina rekommendationer för att använda dem i dina databaser.

För varje bord:

• Definiera och deklarera alla naturliga nycklar.
• Skapa en surrogatnyckel <table_name>_idav typen UUID med standardvärdet uuid_generate_v1(). Du kan till och med markera den som en primärnyckel. Om du lägger till tabellens namn till denna identifierare kommer detta att förenkla , JOINdvs. ta emot JOIN foo USING (bar_id)istället för JOIN foo ON (foo.bar_id = bar.id). Skicka inte denna nyckel till klienter och exponera den inte utanför databasen alls.
• För mellanliggande tabeller som passerar genom JOIN, deklarera alla kolumner för främmande nyckel som en enda sammansatt primärnyckel.
• Lägg eventuellt till en konstgjord nyckel som kan användas i URL:en eller andra strängreferensindikationer. Använd ett Feistel-rutnät eller pg_hashids för att maskera automatiskt ökande heltal.
• Ange en kaskadrestriktion ON UPDATE RESTRICTmed hjälp av surrogat-UUID som främmande nycklar och för konstgjorda främmande nycklar ON UPDATE CASCADE. Välj naturliga nycklar baserat på din egen logik.

Detta tillvägagångssätt säkerställer stabiliteten hos interna nycklar samtidigt som de tillåter och till och med skyddar naturliga nycklar. Dessutom fäster inte synliga konstgjorda nycklar till någonting. Efter att ha förstått allt korrekt kan du inte bara hänga på "primära nycklar" och använda alla möjligheter att använda nycklar.