Chiavi nel database

5.1 Introduzione

Internet è pieno di precetti dogmatici su come le chiavi dovrebbero essere scelte e utilizzate nei database relazionali. A volte le dispute si trasformano addirittura in holivar: bisogna usare chiavi naturali o artificiali? Interi con incremento automatico o UUID?

Dopo aver letto sessantaquattro articoli, sfogliato sezioni di cinque libri e posto tonnellate di domande su IRC e StackOverflow, io (Joe "begriffs" Nelson, l'autore dell'articolo originale) sembra aver messo insieme i pezzi del puzzle e può ora riconciliare gli avversari. Molte controversie chiave in realtà nascono da un fraintendimento del punto di vista di qualcun altro.

Smontiamo il problema e rimettiamolo insieme alla fine. Innanzitutto, poniamo la domanda: cos'è una "chiave"?

Dimentichiamoci per un momento delle chiavi primarie, ci interessa un'idea più generale. Una chiave è una colonna (colonna) o colonne che non hanno valori duplicati nelle righe . Inoltre, le colonne devono essere irriducibilmente uniche, cioè nessun sottoinsieme delle colonne ha questa unicità.

Ma prima, un po' di teoria:

chiave primaria

Chiave primariautilizzato direttamente per identificare le righe in una tabella. Deve rispettare le seguenti restrizioni:

• La chiave primaria deve essere sempre univoca .
• Deve essere sempre presente nella tabella e avere un valore.
• Non dovrebbe cambiare frequentemente il suo valore. Idealmente, non dovrebbe modificare affatto il valore .

In genere, una chiave primaria rappresenta una singola colonna di una tabella, ma può anche essere una chiave composta costituita da più colonne.

Chiave composita

Chiave personalizzata- una combinazione di attributi (colonne) che identificano univocamente ogni riga della tabella. Può essere tutte le colonne e diverse e una. In questo caso, le righe che contengono i valori di questi attributi non devono essere ripetute.

Chiave potenziale

chiave candidata- rappresenta la chiave composta minima della relazione (tabella), ovvero un insieme di attributi che soddisfa una serie di condizioni:

• Irriducibilità : non può essere ridotto, contiene il minimo insieme possibile di attributi.
• Unicità : deve avere valori univoci indipendentemente dal cambio di riga.
• Presenza di un valore : Non deve avere un valore nullo, cioè deve avere un valore.

5.2 Il curioso caso delle chiavi primarie

Quelle che abbiamo appena chiamato "chiavi" nella sezione precedente sono comunemente chiamate "chiavi candidate". Il termine "candidato" implica che tutte queste chiavi competono per il ruolo onorario di "chiave primaria" (chiave primaria), e al resto vengono assegnate "chiavi alternative" (chiavi alternative).

Ci è voluto un po' prima che le implementazioni SQL superassero la discrepanza tra le chiavi e il modello relazionale, e i primi database erano orientati verso il concetto di basso livello di una chiave primaria. Le chiavi primarie in tali database erano necessarie per identificare la posizione fisica di una riga su supporti di archiviazione sequenziali. Ecco come lo spiega Joe Celko:

Il termine "chiave" indicava una chiave di ordinamento dei file, necessaria per eseguire qualsiasi operazione di elaborazione su un file system sequenziale. Una serie di schede perforate è stata letta in un unico ordine; era impossibile tornare indietro. Le prime unità a nastro imitavano lo stesso comportamento e non consentivano l'accesso bidirezionale. Ovvero, il Sybase SQL Server originale richiedeva di "riavvolgere" la tabella all'inizio per leggere la riga precedente.

Nel moderno SQL, non è necessario concentrarsi sulla rappresentazione fisica delle informazioni, sulle relazioni tra i modelli di tabelle e l'ordine interno delle righe non è affatto importante. Tuttavia, anche ora SQL Server per impostazione predefinita crea un indice cluster per le chiavi primarie e, secondo la vecchia tradizione, organizza fisicamente l'ordine delle righe.

Nella maggior parte dei database, le chiavi primarie appartengono al passato e forniscono poco più di un riflesso o di una posizione fisica. Ad esempio, in una tabella PostgreSQL, la dichiarazione di una chiave primaria applica automaticamente un vincolo NOT NULLe definisce una chiave esterna predefinita. Inoltre, le chiavi primarie sono le colonne preferite per l'operatore JOIN.

La chiave primaria non esclude la possibilità di dichiarare altre chiavi. Allo stesso tempo, se nessuna chiave è assegnata come primaria, la tabella funzionerà comunque correttamente. I fulmini, in ogni caso, non ti colpiranno.

5.3 Trovare chiavi naturali

Le chiavi discusse sopra sono chiamate "naturali" perché sono proprietà dell'oggetto modellato che sono interessanti di per sé, anche se nessuno vuole farne una chiave.

La prima cosa da ricordare quando si esamina una tabella per possibili chiavi naturali è cercare di non essere troppo furbi. L'utente sqlvogel su StackExchange fornisce i seguenti consigli:

Alcune persone hanno difficoltà a scegliere una chiave "naturale" perché escogitano situazioni ipotetiche in cui una particolare chiave potrebbe non essere univoca. Non capiscono il significato stesso del compito. Il significato della chiave è definire la regola secondo la quale gli attributi in un dato momento devono essere e saranno sempre unici in una particolare tabella. La tabella contiene dati in un contesto specifico e ben compreso (nell'"area tematica" o "area del discorso"), e l'unico significato è l'applicazione della restrizione in quella particolare area.

La pratica mostra che è necessario introdurre un vincolo chiave quando la colonna è univoca con i valori disponibili e rimarrà tale in scenari probabili. E se necessario, la restrizione può essere rimossa (se questo ti dà fastidio, di seguito parleremo della stabilità delle chiavi.)

Ad esempio, un database di membri di un hobby club può avere univocità in due colonne: first_name, last_name. Con una piccola quantità di dati, i duplicati sono improbabili e prima che si verifichi un vero conflitto, è abbastanza ragionevole utilizzare tale chiave.

Man mano che il database cresce e il volume delle informazioni aumenta, la scelta di una chiave naturale può diventare più difficile. I dati che memorizziamo sono una semplificazione della realtà esterna, e non contengono alcuni aspetti che contraddistinguono gli oggetti nel mondo, come le loro coordinate che cambiano nel tempo. Se un oggetto non ha alcun codice, come si possono distinguere due lattine di bibita o due scatole di farina d'avena a parte la loro disposizione spaziale o le leggere differenze di peso o di imballaggio?

Ecco perché gli enti di normazione creano e applicano marchi distintivi ai prodotti. I veicoli sono timbrati con un numero di identificazione del veicolo (VIN) , i libri sono stampati con codici ISBN e gli imballaggi per alimenti hanno codici UPC . Potreste obiettare che questi numeri non sembrano naturali. Allora perché le chiamo chiavi naturali?

La naturalezza o l'artificialità delle proprietà uniche in un database è relativa al mondo esterno. Una chiave che era artificiale quando è stata creata in un ente normativo o in un ente governativo diventa naturale per noi, perché diventa uno standard in tutto il mondo e/o viene stampata sugli oggetti.

Esistono molti standard industriali, pubblici e internazionali per una varietà di argomenti, tra cui valute, lingue, strumenti finanziari, prodotti chimici e diagnosi mediche. Ecco alcuni dei valori che vengono spesso utilizzati come chiavi naturali:

• Codici paese ISO 3166
• Codici lingua ISO 639
• Codici valuta secondo ISO 4217
• Simboli azionari ISIN
• UPC/EAN, VIN, GTIN, ISBN
• nomi di accesso
• indirizzi email
• numeri di camera
• indirizzo mac di rete
• latitudine, longitudine per punti sulla superficie terrestre

Raccomando di dichiarare le chiavi quando possibile e ragionevole, forse anche più chiavi per tabella. Ma ricorda che tutto quanto sopra potrebbe avere delle eccezioni.

• Non tutti hanno un indirizzo email, anche se questo può essere accettabile in alcune condizioni del database. Inoltre, le persone cambiano i loro indirizzi e-mail di volta in volta. (Maggiori informazioni sulla stabilità delle chiavi più avanti.)
• I simboli azionari ISIN cambiano di volta in volta, ad esempio i simboli GOOG e GOOGL non descrivono accuratamente la riorganizzazione dell'azienda da Google ad Alphabet. A volte può sorgere confusione, come con TWTR e TWTRQ, alcuni investitori hanno erroneamente acquistato quest'ultimo durante l'IPO di Twitter.
• I numeri di previdenza sociale sono utilizzati solo dai cittadini statunitensi, hanno restrizioni sulla privacy e vengono riutilizzati dopo la morte. Inoltre, dopo il furto di documenti, le persone possono ottenere nuovi numeri. Infine, lo stesso numero può identificare sia una persona che un identificatore fiscale.
• I codici postali sono una scelta sbagliata per le città. Alcune città hanno un indice comune, o viceversa, ci sono diversi indici in una città.

5.4 Chiavi artificiali

Dato che la chiave è una colonna con valori univoci in ogni riga, un modo per crearla è imbrogliare: puoi scrivere valori univoci fittizi in ogni riga. Si tratta di chiavi artificiali: codice inventato utilizzato per riferirsi a dati o oggetti.

È molto importante che il codice sia generato dal database stesso e sia sconosciuto a chiunque tranne che agli utenti del database. Questo è ciò che distingue le chiavi artificiali dalle chiavi naturali standardizzate.

Mentre le chiavi naturali hanno il vantaggio di proteggere da righe duplicate o incoerenti in una tabella, le chiavi artificiali sono utili perché rendono più facile per gli utenti o altri sistemi fare riferimento alla riga e velocizzano le ricerche e le unioni perché non usano confronti tra stringhe (o più colonne) keys.

Surrogati

Le chiavi artificiali vengono utilizzate come ancore: indipendentemente da come cambiano le regole e le colonne, una riga può sempre essere identificata allo stesso modo. La chiave artificiale utilizzata a tale scopo è denominata "chiave surrogata" e richiede un'attenzione particolare. Prenderemo in considerazione i surrogati di seguito.

Le chiavi artificiali non surrogate sono utili per fare riferimento a una riga dall'esterno del database. Una chiave artificiale identifica brevemente un dato o un oggetto: può essere specificata come URL, allegata a una fattura, dettata telefonicamente, ottenuta da una banca o stampata su una targa. (La targa di un'auto è una chiave naturale per noi, ma progettata dal governo come chiave artificiale.)

Le chiavi sintetiche dovrebbero essere scelte tenendo conto dei possibili mezzi di trasmissione per ridurre al minimo refusi ed errori. Va notato che la chiave può essere pronunciata, letta stampata, inviata tramite SMS, letta scritta a mano, digitata dalla tastiera e incorporata in un URL. Inoltre, alcune chiavi artificiali, come i numeri di carta di credito, contengono un checksum in modo che se si verificano determinati errori, possono almeno essere riconosciuti.

Esempi:

• Per le targhe statunitensi esistono regole sull'uso di caratteri ambigui, come O e 0.
• Ospedali e farmacie devono prestare particolare attenzione, data la calligrafia dei medici.
• Inviate un codice di conferma tramite SMS? Non andare oltre il set di caratteri GSM 03.38.
• A differenza di Base64, che codifica dati di byte arbitrari, Base32 utilizza un set di caratteri limitato che è conveniente per gli esseri umani da utilizzare e gestire su sistemi informatici meno recenti.
• I Proquint sono identificatori leggibili, scrivibili e pronunciabili. Questi sono pro-nouncable QUINT-uplets di consonanti e vocali comprese in modo inequivocabile.

Tieni presente che non appena presenterai la tua chiave artificiale al mondo, le persone inizieranno stranamente a prestarle un'attenzione speciale. Basta guardare le targhe dei "ladri" o il sistema per la creazione di identificatori pronunciabili, diventato il famigerato generatore automatico di maledizioni.

Anche se ci limitiamo ai tasti numerici, ci sono dei tabù come il tredicesimo piano. Sebbene i proquint abbiano una maggiore densità di informazioni per sillaba parlata, anche i numeri vanno bene in molti modi: negli URL, nelle tastiere e nelle note scritte a mano, purché il destinatario sappia che la chiave è solo numeri.

Tuttavia, tieni presente che non dovresti utilizzare l'ordine sequenziale nei tasti numerici pubblici, in quanto ciò ti consente di rovistare tra le risorse (/videos/1.mpeg, /videos/2.mpeg e così via) e inoltre fa trapelare informazioni sul numero dati. Sovrapponi una rete di Feistel a una sequenza di numeri e preserva l'unicità nascondendo l'ordine dei numeri.

L'unico argomento contro la dichiarazione di chiavi aggiuntive è che ogni nuova porta con sé un altro indice univoco e aumenta il costo di scrittura sulla tabella. Certo, dipende da quanto sia importante per te la correttezza dei dati, ma, molto probabilmente, le chiavi dovrebbero comunque essere dichiarate.

Vale anche la pena dichiarare diverse chiavi artificiali, se presenti. Ad esempio, un'organizzazione ha candidati (candidati) e dipendenti (dipendenti). Ogni dipendente era una volta un candidato e si riferisce ai candidati tramite il proprio identificatore, che dovrebbe essere anche la chiave del dipendente. Un altro esempio, è possibile impostare l'ID dipendente e il nome di accesso come due chiavi in ​​Dipendenti.

5.5 Chiavi surrogate

Come già accennato, un importante tipo di chiave artificiale è chiamato "chiave surrogata". Non ha bisogno di essere conciso e passabile come altre chiavi artificiali, ma viene utilizzato come etichetta interna che identifica sempre la stringa. Viene utilizzato in SQL, ma l'applicazione non vi accede in modo esplicito.

Se hai familiarità con le colonne di sistema di PostgreSQL, puoi pensare ai surrogati quasi come a un parametro di implementazione del database (come ctid), che, tuttavia, non cambia mai. Il valore surrogato viene selezionato una volta per riga e successivamente non viene più modificato.

Le chiavi surrogate sono ottime come chiavi esterne e i vincoli a cascata devono essere specificati ON UPDATE RESTRICTper corrispondere all'immutabilità del surrogato.

D'altra parte, le chiavi esterne alle chiavi condivise pubblicamente dovrebbero essere contrassegnate con ON UPDATE CASCADE, per fornire la massima flessibilità. Un aggiornamento a cascata viene eseguito allo stesso livello di isolamento della transazione circostante, quindi non preoccuparti dei problemi di concorrenza: il database andrà bene se scegli un livello di isolamento rigoroso.

Non rendere le chiavi surrogate "naturali". Una volta che mostri il valore della chiave surrogata agli utenti finali, o peggio, lasci che lavorino con quel valore (in particolare attraverso una ricerca), stai effettivamente assegnando un valore alla chiave. Quindi la chiave mostrata dal tuo database può diventare una chiave naturale nel database di qualcun altro.

Forzare i sistemi esterni a utilizzare altre chiavi artificiali specificamente progettate per la trasmissione ci consente di modificare tali chiavi secondo necessità per soddisfare le mutevoli esigenze, mantenendo al contempo l'integrità referenziale interna con i surrogati.

Incremento automatico INT/BIGINT

L'uso più comune per le chiavi surrogate è la colonna "bigserial" con incremento automatico , nota anche come IDENTITY . (In effetti, PostgreSQL 10 ora supporta il costrutto IDENTITY, così come Oracle, vedi CREATE TABLE.)

Tuttavia, credo che un numero intero con incremento automatico sia una scelta sbagliata per le chiavi surrogate. Questa opinione è impopolare, quindi lasciatemi spiegare.

Svantaggi delle chiavi seriali:

• Se tutte le sequenze iniziano da 1 e aumentano in modo incrementale, le righe di tabelle diverse avranno gli stessi valori di chiave. Questa opzione non è l'ideale, è comunque preferibile utilizzare insiemi di chiavi disgiunti nelle tabelle, in modo che, ad esempio, le query non possano confondere accidentalmente le costanti JOINe restituire risultati imprevisti. (In alternativa, per garantire che non ci siano intersezioni, si potrebbe costruire ogni sequenza da multipli di diversi numeri primi, ma questo sarebbe piuttosto laborioso.)
• La chiamata nextval() per generare una sequenza nell'SQL distribuito di oggi fa sì che l'intero sistema non si ridimensioni bene.
• Il consumo di dati da un database che utilizzava anche chiavi sequenziali provocherà conflitti perché i valori sequenziali non saranno univoci tra i sistemi.
• Da un punto di vista filosofico, l'aumento sequenziale dei numeri è associato a vecchi sistemi in cui era implicito l'ordine delle linee. Se ora vuoi ordinare le righe, fallo esplicitamente con una colonna timestamp o qualcosa che abbia senso nei tuoi dati. In caso contrario, viene violata la prima forma normale.
• Motivo debole, ma questi brevi identificatori sono tentati di dirlo a qualcuno.

UUID

Diamo un'occhiata a un'altra opzione: utilizzare numeri interi grandi (128 bit) generati secondo uno schema casuale. Gli algoritmi per la generazione di tali identificatori univoci universali (UUID) hanno una probabilità estremamente bassa di scegliere lo stesso valore due volte, anche quando vengono eseguiti contemporaneamente su due processori diversi.

In tal caso, gli UUID sembrano una scelta naturale da utilizzare come chiavi surrogate, vero? Se vuoi etichettare le righe in un modo univoco, niente è meglio di un'etichetta univoca!

Allora perché non tutti li usano in PostgreSQL? Ci sono diversi motivi inventati per questo e uno logico che può essere aggirato, e presenterò punti di riferimento per illustrare il mio punto.

Innanzitutto, parlerò delle ragioni inverosimili. Alcune persone pensano che gli UUID siano stringhe perché sono scritti nella tradizionale notazione esadecimale con un trattino: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. In effetti, alcuni database non hanno un tipo uuid compatto (128 bit), ma PostgreSQL lo ha e ha una dimensione di due bigint, ovvero, rispetto alla quantità di altre informazioni nel database, l'overhead è trascurabile.

Gli UUID sono anche ingiustamente accusati di essere ingombranti, ma chi li pronuncerà, li digiterà o li leggerà? Abbiamo detto che ha senso mostrare le chiavi artificiali, ma nessuno (per definizione) dovrebbe vedere l'UUID surrogato. È possibile che l'UUID venga gestito da uno sviluppatore che esegue comandi SQL in psql per eseguire il debug del sistema, ma questo è tutto. E lo sviluppatore può anche fare riferimento a stringhe utilizzando chiavi più convenienti, se fornite.

Il vero problema con gli UUID è che i valori altamente randomizzati portano all'amplificazione della scrittura a causa delle scritture a pagina intera nel registro write-ahead (WAL) . Tuttavia, il degrado delle prestazioni dipende in realtà dall'algoritmo di generazione dell'UUID.

Misuriamo l'amplificazione in scrittura . In verità, il problema è nei file system più vecchi. Quando PostgreSQL scrive su disco, cambia la "pagina" su disco. Se si spegne il computer, la maggior parte dei file system segnalerà comunque una scrittura riuscita prima che i dati vengano archiviati in modo sicuro sul disco. Se PostgreSQL percepisce ingenuamente tale azione come completata, il database verrà danneggiato durante il successivo avvio del sistema.

Poiché PostgreSQL non può fidarsi della maggior parte dei sistemi operativi/filesystem/configurazioni del disco per fornire continuità, il database salva lo stato completo della pagina del disco modificata in un registro write-ahead che può essere utilizzato per il ripristino da un possibile arresto anomalo. L'indicizzazione di valori altamente randomizzati come gli UUID in genere comporta una serie di diverse pagine del disco e comporta la scrittura dell'intera dimensione della pagina (di solito 4 o 8 KB) nel WAL per ogni nuova voce. Questa è la cosiddetta scrittura a pagina intera (scrittura a pagina intera, FPW).

Alcuni algoritmi di generazione di UUID (come "snowflake" di Twitter o uuid_generate_v1() nell'estensione uuid-ossp di PostgreSQL) generano valori monotonicamente crescenti su ogni macchina. Questo approccio consolida le scritture in meno pagine su disco e riduce FPW.

5.6 Conclusioni e raccomandazioni

Ora che abbiamo visto i diversi tipi di chiavi e i loro usi, voglio elencare i miei consigli per usarli nei tuoi database.

Per ogni tavolo:

• Definire e dichiarare tutte le chiavi naturali.
• Crea una chiave surrogata <table_name>_iddi tipo UUID con un valore predefinito di uuid_generate_v1(). Puoi persino contrassegnarlo come chiave primaria. Se aggiungi il nome della tabella a questo identificatore, questo semplificherà JOIN, cioè ricevere JOIN foo USING (bar_id)invece di JOIN foo ON (foo.bar_id = bar.id). Non passare questa chiave ai client e non esporla al di fuori del database.
• Per le tabelle intermedie che passano attraverso JOIN, dichiarare tutte le colonne della chiave esterna come un'unica chiave primaria composita.
• Facoltativamente, aggiungi una chiave artificiale che può essere utilizzata nell'URL o in altre indicazioni di riferimento di stringa. Usa una griglia di Feistel o pg_hashids per mascherare i numeri interi con incremento automatico.
• Specificare un vincolo a cascata ON UPDATE RESTRICTutilizzando UUID surrogati come chiavi esterne e per chiavi esterne artificiali ON UPDATE CASCADE. Scegli le chiavi naturali in base alla tua logica.

Questo approccio garantisce la stabilità delle chiavi interne consentendo e persino proteggendo le chiavi naturali. Inoltre, le chiavi artificiali visibili non si attaccano a nulla. Avendo capito tutto correttamente, non puoi rimanere bloccato solo sulle "chiavi primarie" e sfruttare tutte le possibilità di utilizzo delle chiavi.