¿Qué es una buena clave natural?
Esta es una pregunta muy difícil para la mayoría de las entidades cuando diseñas tu esquema. En algunos casos raros, parece haber un candidato "obvio", como una variedad de normas ISO, que incluyen:
Pero incluso en esos casos, puede haber excepciones y lo peor que puede suceder es un cambio clave. La mayoría de los diseños de bases de datos son seguros y utilizan claves sustitutas en su lugar. Nada de malo con eso. Pero …
Tablas de relaciones
Hay una excepción donde una clave sustituta nunca es realmente necesaria. Esas son tablas de relaciones. Por ejemplo, en la base de datos de Sakila todas las tablas de relaciones carecen de una clave sustituta y usan sus claves externas respectivas como una clave primaria "natural" compuesta en su lugar:
] Entonces, la tabla FILM_ACTOR por ejemplo, se define como tal:
CREATE TABLE film_actor ( actor_id int NO NULL REFERENCIAS actor, film_id int NOT NULL REFERENCES film, CONSTRAINT film_actor_pkey CLAVE PRIMARIA (actor_id, film_id) );
Realmente no tiene sentido agregar otra columna FILM_ACTOR_ID o ID para una fila individual en esta tabla, incluso si muchos ORM y esquemas no definidos por ORM funcionarán esto, simplemente por razones de "consistencia" (y en algunos casos, porque no pueden manejar claves compuestas).
Ahora, la presencia o ausencia de una clave sustituta de este tipo generalmente no es muy relevante en el trabajo diario con esta tabla. Si está utilizando un ORM, es probable que no afecte al código del cliente. Si está utilizando SQL, definitivamente no lo hace. Simplemente nunca use esa columna adicional.
¡Pero en términos de rendimiento, puede hacer una gran diferencia!
Índices agrupados
En muchos RDBMS, al crear una tabla, puede elegir si usar un "índice agrupado" o un diseño de tabla "índice no agrupado" . La principal diferencia es:
Índice agrupado
… es un índice de clave principal que "agrupa" los datos, que pertenecen juntos. En otras palabras:
- Todos los valores de columna de índice están contenidos en la estructura del árbol de índice
- Todos los demás valores de columna están contenidos en los nodos de hoja de índice
La ventaja de este diseño de tabla es que las búsquedas de clave primaria pueden ser mucho más rápido porque su fila completa está ubicada en el índice, lo que requiere menos E / S de disco que el índice no agrupado para las búsquedas de claves primarias . El precio para esto es búsquedas de índice secundario más lentas (por ejemplo, buscar apellidos). Las complejidades algorítmicas son:
O (registro N)para búsquedas de claves primariasO (registro N)para búsquedas de claves secundarias másO ( M log N)para proyecciones de columnas sin clave secundaria (un precio bastante alto a pagar)
… donde
Nes el tamaño de la tablaMes el número de filas que se buscan en claves secundarias
El uso de OLTP a menudo se beneficia de índices agrupados.
Índice no agrupado
… es un índice de clave principal que reside "Fuera" de la estructura de la tabla, que es una tabla de montón. En otras palabras:
- Todos los valores de la columna del índice están contenidos en la estructura del árbol del índice
- Todos los valores de la columna del índice y otros valores de la columna están contenidos en la tabla del montón
La ventaja de esto El diseño de la tabla es que todas las búsquedas son igualmente rápidas, independientemente de si está utilizando una búsqueda de clave principal o una búsqueda de clave secundaria. Siempre hay una búsqueda de tabla de pila de tiempo adicional y constante. Las complejidades algorítmicas son:
O (log N)para búsquedas de clave primaria másO (1)para proyecciones de columnas de clave no primaria (precio moderado a pagar)O (log N)para búsquedas de clave secundaria másO (M)para proyecciones de columnas de clave no secundaria (un precio moderado a pagar )
El uso de OLAP definitivamente se beneficia de las tablas de pila.
Predeterminados
- InnoDB de MySQL ofrece índices agrupados solamente.
- MyISAM de MySQL solo ofrece tablas de montón.
- Oracle ofrece ambas opciones y valores predeterminados para apilar tablas
- PostgreSQL ofrece tanto como valores predeterminados como para apilar tablas
- SQL Server ofrece valores predeterminados para índices agrupados
Tenga en cuenta que Oracle llama a los índices agrupados "índice de tablas organizadas"
Rendimiento
En este artículo, estoy comprobando el rendimiento de MySQL ya que InnoDB de MySQL no ofrece cambiar la tabla e diseño Curiosamente, los problemas que se muestran a continuación no se pudieron reproducir en PostgreSQL como lo muestra el usuario reddit / u / ForeverAlot . Detalles aquí .
Con las complejidades algorítmicas anteriores, podemos adivinar fácilmente lo que estoy tratando de sugerir aquí. En presencia de un índice agrupado, debemos evitar búsquedas costosas de claves secundarias cuando sea posible. Por supuesto, estas búsquedas no siempre se pueden evitar, pero si revisamos el diseño alternativo de estas dos tablas:
CREAR TABLA film_actor_surrogate ( ID int no nulo, actor_id int NO NULL REFERENCIAS actor, film_id int NOT NULL REFERENCES film, CONSTRAINT film_actor_surrogate_pkey CLAVE PRIMARIA (id) ); CREAR MESA film_actor_natural ( actor_id int NO NULL REFERENCIAS actor, film_id int NOT NULL REFERENCES film, CONSTRAINT film_actor_pkey CLAVE PRIMARIA (actor_id, film_id) );
… podemos ver que si estamos usando un índice agrupado aquí, la agrupación se realizará en función de:
FILM_ACTOR_SURROGATE.IDque es una agrupación muy inútil(FILM_ACTOR_NATURAL.ACTOR_ID, FILM_ACTOR_NATURAL.FILM_ID)que es un agrupamiento muy útil
En este último caso, cada vez que miramos las películas de un actor, podemos usar el índice de agrupamiento como un índice de cobertura ] independientemente de si proyectamos algo adicional de esa tabla o no.
En el primer caso, tenemos que confiar en un índice de clave secundaria adicional que contiene (ACTOR_ID, FILM_ID) y es probable que ese índice secundario no cubre si tenemos proyecciones adicionales.
El agrupamiento de claves sustituto es realmente inútil, porque nunca usamos la tabla de esta manera.
¿Importa?
Podemos diseñar fácilmente un punto de referencia para este caso. Puede encontrar el código completo de referencia aquí en GitHub para validar los resultados en su entorno. El punto de referencia utiliza este diseño de base de datos:
crea la tabla parent_1 (id int no es clave principal nula); crear tabla parent_2 (id int no null clave primaria); crear tabla child_surrogate ( id int auto_increment, parent_1_id int no nulo hace referencia a parent_1, parent_2_id int no nulo hace referencia a parent_2, payload_1 int, payload_2 int, clave primaria (id), único (parent_1_id, parent_2_id) ) - MOTOR = MyISAM / * descomentar para usar MyISAM (tablas del montón) * / ; crear tabla child_natural ( parent_1_id int no nulo hace referencia a parent_1, parent_2_id int no nulo hace referencia a parent_2, payload_1 int, payload_2 int, clave principal (parent_1_id, parent_2_id) ) - MOTOR = MyISAM / * descomentar para usar MyISAM (tablas del montón) * / ;
A diferencia de la base de datos de Sakila ahora estamos agregando algo de "carga útil" a la tabla de relaciones, lo cual no es improbable. Las versiones recientes de MySQL se establecerán de forma predeterminada en InnoDB, que solo admite un diseño de índice agrupado. Puede descomentar la cláusula de almacenamiento ENGINE para ver cómo funcionaría con MyISAM, que solo admite tablas de almacenamiento dinámico.
La referencia agrega:
- 10 000 filas en
PARENT_1[19659016] 100 filas enPARENT_2 - 1 000 000 filas en ambas tablas
CHILD(solo una unión cruzada de las anteriores)
Y luego, se ejecutan 5 iteraciones de 10000 repeticiones de las siguientes dos consultas, siguiendo nuestra técnica estándar de referencia de SQL :
- Consulta 1 SELECCIONE c.payload_1 + c.payload_2 COMO a FROM parent_1 AS p1 ÚNETE a child_surrogate AS c ON p1.id = c.parent_1_id DONDE p1.id = 4; - Consulta 2 SELECCIONE c.payload_1 + c.payload_2 COMO a FROM parent_1 AS p1 ÚNETE a child_natural AS c ON p1.id = c.parent_1_id DONDE p1.id = 4;
Observe que MySQL no implementa la eliminación de la combinación de lo contrario, la unión inútil a PARENT_1 se eliminaría. Los resultados de referencia son muy claros:
Usando InnoDB (índices agrupados)
Ejecución 0, Declaración 1: 3104 Run 0, Statement 2: 1910 Ejecutar 1, Declaración 1: 3097 Run 1, Statement 2: 1905 Ejecutar 2, Declaración 1: 3045 Run 2, Statement 2: 2276 Ejecutar 3, Declaración 1: 3589 Run 3, Statement 2: 1910 Ejecutar 4, Declaración 1: 2961 Ejecutar 4, Declaración 2: 1897
Usando MyISAM (tablas de montón)
Ejecución 0, declaración 1: 3473 Ejecutar 0, Declaración 2: 3288 Ejecutar 1, Declaración 1: 3328 Ejecución 1, Declaración 2: 3341 Ejecutar 2, Declaración 1: 3674 Ejecutar 2, Declaración 2: 3307 Ejecutar 3, Declaración 1: 3373 Ejecutar 3, Declaración 2: 3275 Ejecutar 4, Declaración 1: 3298 Ejecutar 4, Declaración 2: 3322
No debe leer esto como una comparación entre InnoDB y MyISAM en general, sino como una comparación de las diferentes estructuras de tablas dentro de los límites del mismo motor. Muy obviamente, la complejidad de búsqueda adicional del índice mal agrupado en CHILD_SURROGATE provoca una ejecución de consultas un 50% más lenta en este tipo de consulta, sin ganar nada .
En el caso de La tabla de heap, la columna de clave sustituta adicional no tuvo ningún efecto significativo.
De nuevo, el punto de referencia completo se puede encontrar aquí en GitHub, si desea repetirlo .
Conclusión [19659005] No todos están de acuerdo con lo que generalmente es mejor: índices agrupados o no agrupados. No todos están de acuerdo con la utilidad de las claves sustitutas en cada tabla . Estas son discusiones bastante discutidas.
Pero este artículo mostró claramente que en tablas de relaciones que tienen una clave de candidato muy clara, es decir, el conjunto de claves externas salientes que definen la relación de muchos a muchos, la clave sustituta no solo no agrega valor, sino que también perjudica su rendimiento en un conjunto de consultas cuando su tabla usa un índice agrupado.
InnoDB de MySQL y SQL Server usan índices agrupados por defecto así que si está usando cualquiera de esos RDBMS, verifique si tiene espacio para una mejora significativa al soltar sus llaves sustitutas.
No comments:
Post a Comment