primaria - id compuesto sql server
¿Debería una clave primaria compuesta agruparse en SQL Server? (5)
Considere esta tabla de ejemplo (suponiendo que SQL Server 2005):
create table product_bill_of_materials
(
parent_product_id int not null,
child_product_id int not null,
quantity int not null
)
Estoy considerando una clave primaria compuesta que contenga las dos columnas product_id (definitivamente querré una restricción única) a diferencia de una columna de ID única separada. La pregunta es, desde el punto de vista del rendimiento, ¿debería agruparse esa clave principal?
¿Debo también crear un índice en cada columna de ID para que las búsquedas de las claves externas sean más rápidas? Creo que a esta tabla se la va a golpear mucho más en las lecturas que en las escrituras.
"Lo que consulta más a menudo" no es necesariamente la mejor razón para elegir un índice para agrupar. Lo que más importa es lo que consulta para obtener múltiples filas. La agrupación en clúster es la estrategia adecuada para hacer que sea eficiente obtener múltiples filas en el menor número de lecturas de disco.
El mejor ejemplo es el historial de ventas para un cliente.
Supongamos que tiene dos índices en la tabla Ventas, uno en Cliente (y tal vez la fecha, pero el punto se aplica de cualquier manera). Si consulta la tabla con más frecuencia en CustomerID, querrá que todos los registros de ventas del cliente le proporcionen una o dos lecturas de disco para todos los registros.
La clave principal, OTOH, podría ser una clave sustituta, o SalesId, pero un valor único en cualquier caso. Si esto estuviera agrupado, no sería beneficioso en comparación con un índice único normal.
EDITAR: Tomemos esta mesa en particular para discusión; revelará aún más sutilezas.
La clave primaria "natural" es con toda probabilidad parentid + childid. Pero, ¿en qué secuencia? Parentid + childid no es más exclusivo que childid + parentid. Para fines de agrupamiento, ¿qué ordenamiento es más apropiado? Uno supondría que debe ser parentid + childid, ya que querremos preguntar: "Para un artículo dado, ¿cuáles son sus componentes? Pero ¿no es poco probable que quiera ir por el otro lado, y pregunte "por un contenido dado, de qué elementos es un componente?".
Agregue la consideración de "índices de cobertura", que contienen, dentro del índice, toda la información necesaria para satisfacer la consulta. Si eso es cierto, entonces nunca necesitará leer el resto del registro; entonces la agrupación no es beneficiosa; solo leer el índice es suficiente. (Por cierto, eso significa dos índices en el mismo par de campos, en orden opuesto, que puede ser lo que se debe hacer en casos como este, o al menos un índice compuesto en uno, y un índice de campo único en el otro. )
Pero eso todavía no dicta qué debería agruparse; que finalmente sería determinado por qué consultas, de hecho, necesitarían tomar el registro para el campo Cantidad.
Incluso para un ejemplo tan claro, en principio es mejor dejar la decisión sobre otros índices hasta que pueda probarlos con datos realistas (obviamente antes de la producción); pero pedir aquí especulación no tiene sentido. Las pruebas siempre le darán la respuesta adecuada.
Olvídese de preocuparse por la ralentización de las inserciones hasta que tenga un problema (que en la mayoría de los casos nunca sucederá), y puede realizar pruebas para asegurarse de renunciar a índices útiles para obtener un beneficio mensurable.
Sin embargo, las cosas aún no son ciertas porque las tablas de unión como esta también están frecuentemente involucradas en muchos otros tipos de consultas. Así que solo elegiría uno y lo evaluaría según fuera necesario a medida que se gelifica la aplicación, y el volumen de datos para las pruebas estará disponible.
Por cierto, espero que termine con un PK en parentid + childid; un índice no único en childid; y el primero se arracimó Si prefiere un PK sustituto, entonces todavía querrá un índice único en parentid + childid, agrupado. Es muy poco probable que la agrupación de la clave sustituta sea óptima.
Como ya han dicho otros, depende de cómo accederá a la mesa. Sin embargo, tenga en cuenta que cualquier RDBMS debería poder usar el índice agrupado para buscar en una sola columna, siempre que esa columna aparezca primero. Por ejemplo, si su índice agrupado está activado (parent_id, child_id) no necesita otro índice separado en (parent_id).
Su mejor apuesta puede ser un índice agrupado en (parent_id, child_id), que también es la clave principal, con un índice separado no agrupado en (child_id).
En última instancia, la indexación debe abordarse después de que tenga una idea de cómo se accederá a la base de datos. Realice algunas pruebas de estrés de rendimiento estándar si puede y luego analice el comportamiento utilizando una herramienta de creación de perfiles (SQL Profiler para SQL Server) y la sintonía de rendimiento desde allí. Si no tiene la experiencia o el conocimiento para hacerlo con anticipación, intente obtener una versión (con suerte limitada) de la aplicación, recopile las métricas de rendimiento y vea dónde necesita mejorar el rendimiento y descubra qué índices ayudarán .
Si hace las cosas bien, debería poder capturar el perfil "típico" de cómo se accede a la base de datos y luego puede volver a ejecutarlo una y otra vez en un servidor de prueba mientras prueba varios enfoques de indexación.
En su caso, probablemente solo pondría un PK agrupado (parent_id, child_id) para comenzar y luego agregaría el índice no agrupado solo si veía un problema de rendimiento que pudiera ser de ayuda.
La verdadera pregunta aquí es ¿a qué le consultarán más? Si va a buscar ambos valores todo el tiempo, entonces el clúster debe estar en el par. Si va a realizar una consulta más exhaustiva sobre una u otra, querría agruparla en una específica.
Me gustaría centrarme en su última declaración. "Creo que esta mesa recibirá mucho más impacto en las lecturas que en las escrituras". Si este es el caso, entonces puede querer index-heavy. La razón por la que no indexamos mucho todo es que pagas multas por rendimiento para las actualizaciones y los insertos en la mesa. Cuando tenemos tablas que sirven más lectura que escritura, entonces pague el precio de los índices.
En cuanto a qué agrupar, debería pensar en cómo se usará mejor la mesa. Si su tabla está sujeta a una gran cantidad de consultas de rango (DONDE col1 ESTÁ ENTRE aY b) a continuación, agrupe la tabla para que las consultas de rango ya estén configuradas en orden en el disco. En SQL Server a veces obtenemos el clúster de forma gratuita con los PK y, para empezar, nos olvidamos de qué es lo mejor para clúster.
En cuanto a las limitaciones de FK en la tabla, ya que dijo más lecturas que escrituras esto puede ser aceptable. Si esta fuera una tabla con muchas inserciones, cada restricción FK requiere validación con respecto a la tabla principal y es posible que no le proporcione el rendimiento que desea.
Gran pregunta
Dado que dices "Estoy considerando una clave primaria compuesta", aún puede haber tiempo para cambiar de opinión. He usado muchas claves compuestas y sigo encontrando razones para desear no haberlo hecho. Quizás otros estarán en desacuerdo conmigo.
Estoy de acuerdo con la respuesta de Mitchel, el clúster sigue lo que sea que va a consultar más a menudo.