Quali sono le migliori pratiche per l’utilizzo di un GUID come chiave primaria, in particolare per quanto riguarda le prestazioni?

Ho un’applicazione che utilizza GUID come chiave primaria in quasi tutte le tabelle e ho letto che ci sono problemi relativi alle prestazioni quando si utilizza il GUID come chiave primaria. Onestamente, non ho visto alcun problema, ma sto per iniziare una nuova applicazione e voglio ancora utilizzare i GUID come chiavi primarie, ma stavo pensando di usare una chiave primaria composta (il GUID e forse un altro campo .)

Utilizzo un GUID perché sono semplici e facili da gestire quando si dispone di ambienti diversi come i database di “produzione”, “test” e “dev” e anche per i dati di migrazione tra database.

Userò Entity Framework 4.3 e voglio assegnare il Guid nel codice dell’applicazione, prima di inserirlo nel database. (cioè non voglio lasciare che SQL generi il Guid).

Qual è la migliore pratica per la creazione di chiavi primarie basate su GUID, al fine di evitare i presunti risultati di prestazioni associati a questo approccio?

GUID può sembrare una scelta naturale per la tua chiave primaria – e se proprio devi, probabilmente potresti argomentare di usarlo per la PRIMARY KEY del tavolo. Quello che consiglio vivamente di non fare è usare la colonna GUID come chiave di clustering , quale SQL Server fa per impostazione predefinita, a meno che non lo si specifichi espressamente.

Hai davvero bisogno di tenere separati due aspetti:

  1. la chiave primaria è un costrutto logico, una delle chiavi candidate che identifica in modo univoco e affidabile ogni riga della tabella. Questo può essere qualsiasi cosa, davvero: un INT , un GUID , una stringa: scegli ciò che ha più senso per il tuo scenario.

  2. la chiave di clustering (la colonna o le colonne che definiscono il “indice cluster” sulla tabella) – questa è una cosa fisica legata all’archiviazione, e qui, un piccolo, stabile, sempre crescente tipo di dati è la scelta migliore – INT o BIGINT come opzione predefinita.

Per impostazione predefinita, la chiave primaria su una tabella di SQL Server viene anche utilizzata come chiave di clustering, ma non è necessario che sia così! Personalmente ho assistito a massicci guadagni in termini di prestazioni quando suddivido la precedente chiave primaria / basata su GUID in due chiavi separate: la chiave primaria (logica) sul GUID e la chiave di clustering (ordinamento) su INT IDENTITY(1,1) separata INT IDENTITY(1,1) colonna.

Come Kimberly Tripp – la Regina dell’Indicizzazione – e altri hanno dichiarato molte volte – un GUID come chiave di clustering non è ottimale, poiché a causa della sua casualità, porterà a una massiccia frammentazione di pagine e indici e generalmente a cattive prestazioni.

Sì, lo so – c’è newsequentialid() in SQL Server 2005 e versioni successive – ma anche questo non è veramente e completamente sequenziale e quindi soffre anche degli stessi problemi del GUID – solo un po ‘meno prominente.

Poi c’è un altro problema da considerare: la chiave di clustering su una tabella verrà aggiunta a ogni voce di ogni singolo indice non cluster sul tuo tavolo, quindi vuoi veramente assicurarti che sia la più piccola ansible. In genere, un INT con oltre 2 miliardi di righe dovrebbe essere sufficiente per la stragrande maggioranza delle tabelle e, confrontato con un GUID come chiave di clustering, è ansible risparmiare centinaia di megabyte di spazio su disco e nella memoria del server.

Calcolo rapido: utilizzando INT e GUID come chiave primaria e di clustering:

  • Base Table con 1’000’000 righe (3,8 MB rispetto a 15,26 MB)
  • 6 indici non cluster (22,89 MB rispetto a 91,55 MB)

TOTALE: 25 MB contro 106 MB – e questo è solo su un unico tavolo!

Qualche altro spunto di riflessione – roba eccellente di Kimberly Tripp – leggilo, rileggilo, digeriscilo! È il gospel dell’indicizzazione di SQL Server, davvero.

  • GUID come PRIMARY KEY e / o chiave cluster
  • Il dibattito sull’indice cluster continua
  • Chiave di cluster sempre crescente: il dibattito sull’indice clusterizzato ………. di nuovo!
  • Lo spazio su disco è economico – non è questo il punto!

PS: certo, se hai a che fare con poche centinaia o poche migliaia di righe, la maggior parte di questi argomenti non avrà davvero un impatto su di te. Tuttavia: se entri in decine o centinaia di migliaia di righe o inizi a contare in milioni, questi punti diventano molto cruciali e molto importanti da capire.

Aggiornamento: se si desidera che la colonna PKGUID sia la chiave primaria (ma non la chiave di clustering) e un’altra colonna MYINT ( INT IDENTITY ) come chiave di clustering, utilizzare questo:

 CREATE TABLE dbo.MyTable (PKGUID UNIQUEIDENTIFIER NOT NULL, MyINT INT IDENTITY(1,1) NOT NULL, .... add more columns as needed ...... ) ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (PKGUID) CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable(MyINT) 

Fondamentalmente: devi solo dire esplicitamente al vincolo PRIMARY KEY che è NONCLUSTERED (altrimenti è creato come indice cluster, per impostazione predefinita) – e quindi crei un secondo indice definito come CLUSTERED

Funzionerà – ed è un’opzione valida se si dispone di un sistema esistente che deve essere “riprogettato” per le prestazioni. Per un nuovo sistema, se si parte da zero e non ci si trova in uno scenario di replica, sceglierei sempre ID INT IDENTITY(1,1) come chiave primaria in cluster – molto più efficiente di qualsiasi altra cosa!

Utilizzo i GUID come PK dal 2005. In questo mondo di database distribuiti, è assolutamente il modo migliore per unire i dati distribuiti. Puoi sparare e dimenticare le tabelle di unione senza la preoccupazione di dover intonare le tabelle unite. I join GUID possono essere copiati senza problemi.

Questa è la mia configurazione per l’utilizzo di GUID:

  1. PK = GUID. I GUID sono indicizzati in modo simile alle stringhe, pertanto le tabelle di righe alte (oltre 50 milioni di record) potrebbero richiedere il partizionamento delle tabelle o altre tecniche di esecuzione. SQL Server sta diventando estremamente efficiente, quindi i problemi di prestazioni sono sempre meno applicabili.

  2. PK Guid è indice NON-Clustered. Non indicizzare mai un indice GUID a meno che non sia NewSequentialID. Ma anche in questo caso, un riavvio del server causerà gravi interruzioni nell’ordinazione.

  3. Aggiungi ClusterID Int a ogni tabella. Questo è il tuo indice CLUSTERED … che ordina il tuo tavolo.

  4. Partecipare a ClusterIDs (int) è più efficiente, ma lavoro con 20-30 milioni di tabelle di record, quindi l’adesione ai GUID non influisce in modo visibile sulle prestazioni. Se si desidera ottenere prestazioni massime, utilizzare il concetto ClusterID come chiave primaria e partecipare a ClusterID.

Ecco la mia tabella di email …

 CREATE TABLE [Core].[Email] ( [EmailID] UNIQUEIDENTIFIER CONSTRAINT [DF_Email_EmailID] DEFAULT (newsequentialid()) NOT NULL, [EmailAddress] NVARCHAR (50) CONSTRAINT [DF_Email_EmailAddress] DEFAULT ('') NOT NULL, [CreatedDate] DATETIME CONSTRAINT [DF_Email_CreatedDate] DEFAULT (getutcdate()) NOT NULL, [ClusterID] INT NOT NULL IDENTITY, CONSTRAINT [PK_Email] PRIMARY KEY NonCLUSTERED ([EmailID] ASC) ); GO CREATE UNIQUE CLUSTERED INDEX [IX_Email_ClusterID] ON [Core].[Email] ([ClusterID]) GO CREATE UNIQUE NonCLUSTERED INDEX [IX_Email_EmailAddress] ON [Core].[Email] ([EmailAddress] Asc) 

Se si utilizza GUID come chiave primaria e si crea indice cluster, suggerisco di utilizzare il valore predefinito NEWSEQUENTIALID () per esso

Questo link lo dice meglio di quanto potrei e mi ha aiutato nel mio processo decisionale. Io di solito optiamo per un int come chiave primaria, a meno che io non abbia una necessità specifica di non farlo e io lascio anche che il server SQL generi automaticamente / mantieni questo campo a meno che non abbia qualche motivo specifico per non farlo. In realtà, i problemi di rendimento devono essere determinati in base alla tua app specifica. Ci sono molti fattori in gioco qui compresi, ma non limitati a, dimensioni db previste, indicizzazione corretta, query efficiente e altro. Anche se le persone potrebbero non essere d’accordo, penso che in molti scenari non noterai alcuna differenza con entrambe le opzioni e dovresti scegliere ciò che è più appropriato per la tua app e cosa ti consente di svilupparti più facilmente, più rapidamente e in modo più efficace (se non completi mai l’app che differenza fa il resto 🙂

https://web.archive.org/web/20120812080710/http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

PS Non sono sicuro del motivo per cui utilizzeresti un PK composito o quali benefici ritieni che potresti darti.

Attualmente sto sviluppando un’applicazione web con EF Core ed ecco lo schema che utilizzo:

Tutte le mie classi (tabelle) e un int PK e FK. Ho una colonna aggiuntiva con il tipo Guid (generato dal costruttore c #) con un indice non cluster su di esso.

Tutti i join della tabella all’interno di EF vengono gestiti tramite i tasti int mentre tutti gli accessi dall’esterno (controller) vengono eseguiti con i Guids.

Questa soluzione consente di non mostrare le chiavi int sugli url ma di mantenere il modello ordinato e veloce.