sql server - ¿Cuáles son las mejores prácticas para usar un GUID como clave principal, específicamente con respecto al rendimiento?

sql-server entity-framework

Question

Tengo una aplicación que usa GUID como clave principal en casi todas las tablas y he leído que hay problemas sobre el rendimiento al usar GUID como clave principal. Honestamente, no he visto ningún problema, pero estoy a punto de iniciar una nueva aplicación y todavía quiero usar los GUID como claves principales, pero estaba pensando en usar una clave principal compuesta (el GUID y quizás otro campo) .)

Estoy usando un GUID porque son agradables y fáciles de administrar cuando tiene diferentes entornos como las bases de datos de "producción", "prueba" y "dev", y también para la migración de datos entre bases de datos.

Usaré Entity Framework 4.3 y quiero asignar el Guid en el código de la aplicación, antes de insertarlo en la base de datos. (es decir, no quiero que SQL genere el Guid).

¿Cuál es la mejor práctica para crear claves primarias basadas en GUID, para evitar los supuestos éxitos de rendimiento asociados con este enfoque?

Answer 1

Actualmente estoy desarrollando una aplicación web con EF Core y aquí está el patrón que utilizo:

Todas mis clases (tablas) y un int PK y FK. Tengo una columna adicional con el tipo Guid (generado por el constructor c #) con un índice no agrupado.

Todas las uniones de la tabla dentro de EF se gestionan a través de las teclas int, mientras que todos los accesos desde el exterior (controladores) se realizan con las Guías.

Esta solución permite no mostrar las claves int en las URL, pero mantener el modelo ordenado y rápido.

Answer 2

Este enlace lo dice mejor de lo que podía y me ayudó en la toma de decisiones. Normalmente opto por un int como clave principal, a menos que tenga una necesidad específica de no hacerlo y también permito que el servidor SQL genere / mantenga este campo a menos que tenga alguna razón específica para no hacerlo. En realidad, las preocupaciones de rendimiento deben determinarse en función de su aplicación específica. Hay muchos factores en juego aquí que incluyen, entre otros, el tamaño de db esperado, la indexación adecuada, la consulta eficiente y más. Aunque la gente puede estar en desacuerdo, creo que en muchos escenarios no notará una diferencia con ninguna de las opciones y debe elegir qué es más apropiado para su aplicación y qué le permite desarrollarse de manera más fácil, más rápida y más eficaz ¿Qué diferencia hace el resto :).

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

PD: No estoy seguro de por qué usaría un PK compuesto o qué beneficio cree que le daría.

Answer 3

He estado utilizando GUID como PK desde 2005. En este mundo de base de datos distribuida, es absolutamente la mejor manera de combinar datos distribuidos. Puede disparar y olvidarse de las tablas de combinación sin la preocupación de que las coordenadas coincidan en las tablas unidas. Las combinaciones de GUID se pueden copiar sin ninguna preocupación.

Esta es mi configuración para usar GUIDs:

1. PK = GUID. Los GUID se indexan de manera similar a las cadenas, por lo que las tablas de filas altas (más de 50 millones de registros) pueden necesitar particiones de tablas u otras técnicas de rendimiento. SQL Server se está volviendo extremadamente eficiente, por lo que las preocupaciones de rendimiento son cada vez menos aplicables.

2. PK Guid es un índice NO agrupado. Nunca agrupe el índice de un GUID a menos que sea NewSequentialID. Pero incluso entonces, un reinicio del servidor causará grandes interrupciones en el pedido.

3. Agregue ClusterID Int a cada tabla. Este es su índice CLUSTERED ... que ordena su mesa.

4. Unirme a ClusterIDs (int) es más eficiente, pero trabajo con 20-30 millones de tablas de registro, por lo que unirme a GUID no afecta visiblemente el rendimiento. Si desea un rendimiento máximo, use el concepto ClusterID como su clave principal y únase a ClusterID.

Aquí está mi tabla de correo electrónico ...

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)

Answer 4

Los GUIDs pueden parecer una elección natural para su clave principal, y si realmente debe hacerlo, probablemente podría argumentar que lo usará para la CLAVE PRIMARIA de la tabla. Lo que recomiendo encarecidamente que no haga es usar la columna GUID como la clave de agrupación en clúster , que SQL Server hace de forma predeterminada, a menos que específicamente le indique que no lo haga.

Realmente necesitas separar dos cuestiones:

1. la clave principal es una construcción lógica, una de las claves candidatas que identifica de forma única y confiable cada fila de su tabla. En realidad, esto puede ser cualquier cosa: una INT , un GUID , una cadena, lo que tiene más sentido para su situación.

2. la clave de agrupamiento (la columna o columnas que definen el "índice agrupado" en la tabla): esto es algo relacionado con el almacenamiento físico , y aquí, un tipo de datos pequeño, estable y en constante crecimiento es su mejor opción: INT o BIGINT como su opción por defecto.

De forma predeterminada, la clave principal en una tabla de SQL Server también se usa como la clave de agrupación en clúster, ¡pero no es necesario que sea así! Personalmente, he visto mejoras masivas en el rendimiento al dividir la clave principal / agrupada basada en GUID anterior en dos claves separadas: la clave primaria (lógica) en el GUID y la clave de agrupamiento (ordenamiento) en una INT IDENTITY(1,1) separada INT IDENTITY(1,1) columna.

Como Kimberly Tripp , la reina de la indexación, y otros han dicho muchas veces, un GUID como clave de agrupamiento no es óptimo, ya que, debido a su aleatoriedad, dará lugar a una fragmentación masiva de páginas e índices y, en general, a un mal rendimiento.

Sí, lo sé, hay newsequentialid() en SQL Server 2005 y newsequentialid() posteriores, pero incluso eso no es verdaderamente secuencial y, por lo tanto, también tiene los mismos problemas que el GUID , solo un poco menos prominente.

Luego, hay otro tema que considerar: la clave de agrupación en una tabla se agregará a cada entrada en cada índice no agrupado en su tabla, por lo que realmente desea asegurarse de que sea lo más pequeño posible. Por lo general, una INT con más de 2 mil millones de filas debería ser suficiente para la gran mayoría de las tablas y, en comparación con un GUID como clave de agrupación, puede ahorrar cientos de megabytes de almacenamiento en el disco y en la memoria del servidor.

Cálculo rápido: uso de INT vs. GUID como clave principal y de agrupación:

• Tabla base con 1''000''000 filas (3.8 MB vs. 15.26 MB)
• 6 índices no agrupados (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB frente a 106 MB , ¡y eso es solo en una sola mesa!

Algo más para pensar - excelentes cosas de Kimberly Tripp - ¡léelo, léelo de nuevo, digértelo! Es el evangelio de indexación de SQL Server, realmente.

• GUIDs como CLAVE PRIMARIA y / o clave agrupada
• El debate del índice agrupado continúa
• Clave de agrupamiento cada vez mayor: el Debate del índice agrupado .......... otra vez!
• El espacio en disco es barato, ¡ese no es el punto!

PD: por supuesto, si estás tratando con unos pocos cientos o miles de filas, la mayoría de estos argumentos realmente no tendrán mucho impacto en ti. Sin embargo, si te sumas a las decenas o cientos de miles de filas, o comienzas a contar en millones, esos puntos se vuelven cruciales y muy importantes de entender.

Actualización: si desea tener su columna PKGUID como su clave principal (pero no su clave de agrupamiento), y otra columna MYINT ( INT IDENTITY ) como su clave de agrupamiento - use esto:

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)

Básicamente: solo tiene que indicar explícitamente a la restricción PRIMARY KEY que NONCLUSTERED (de lo contrario, se crea como su índice agrupado, de manera predeterminada), y luego crea un segundo índice que se define como CLUSTERED

Esto funcionará, y es una opción válida si tiene un sistema existente que necesita ser "rediseñado" para el rendimiento. Para un nuevo sistema, si empiezas desde cero y no estás en un escenario de replicación, entonces siempre elegiré ID INT IDENTITY(1,1) como mi clave principal agrupada, ¡mucho más eficiente que cualquier otra cosa!

Answer 5

Si usa GUID como clave principal y crea un índice agrupado, sugiero usar el valor predeterminado de NEWSEQUENTIALID ()