SQL-clave primaria de tabla muchos a muchos
Esta pregunta surge después de leer un comentario en esta pregunta:
Cuando crea una tabla de muchos a muchos, ¿debería crear una clave primaria compuesta en las dos columnas de clave foránea, o crear una clave primaria sustitutiva "ID" de incremento automático, y simplemente poner índices en sus dos columnas FK (y tal vez una restricción única)? ¿Cuáles son las implicaciones en el desempeño para la inserción de nuevos registros / reindexación en cada caso?
Básicamente, esto:
PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)
Vs. esto:
PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)
El comentarista dice:
Hacer de los dos ID el PK significa la tabla está ordenada físicamente en el disco en ese orden. Así que si insertamos (Part1 / Device1), (Part1 / Device2), (Part2 / Device3), luego (Part 1 / Device3) la base de datos tendrá que romper el tabla aparte e inserte la última entre las entradas 2 y 3. Para muchos registros, esto se vuelve muy problemático ya que implica barajar cientos, miles, o millones de registro cada vez que se añade uno. Por el contrario, un PK autoincrementing permite el nuevo discos para ser clavados hasta el final.
La razón por la que pregunto es porque siempre he estado inclinado a hacer la clave primaria compuesta sin columna de auto-incremento sustitutiva, pero no estoy seguro de si la clave sustitutiva es realmente más performante.
5 answers
Con una simple asignación de muchos a muchos de dos columnas, no veo ninguna ventaja real en tener una clave sustituta. Tener una clave primaria en (col1,col2) se garantiza única (suponiendo que sus valores col1 y col2 en las tablas referenciadas son únicos) y un índice separado en (col2,col1) capturará aquellos casos en los que el orden opuesto se ejecutaría más rápido. La madre de alquiler es un desperdicio de espacio.
No necesitará índices en las columnas individuales ya que la tabla solo debe usarse para unir las dos referencias mesas juntas.
Ese comentario al que se refiere en la pregunta no vale la pena los electrones que utiliza, en mi opinión. Parece que el autor piensa que la tabla se almacena en una matriz en lugar de una estructura de árbol de múltiples vías balanceada de rendimiento extremadamente alto.
Para empezar, nunca es necesario almacenar u obtener en la tabla ordenada, solo el índice. Y el índice no se almacenará secuencialmente, se almacenará de manera eficiente para poder ser recuperado pronto.
Además, la gran mayoría de las tablas de la base de datos se leen mucho más a menudo que escritas. Eso hace que cualquier cosa que hagas en el lado de selección sea mucho más relevante que cualquier cosa en el lado de inserción.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-04-30 03:16:05
No se necesita ninguna clave sustituta para las tablas de enlaces.
Un PK en (col1, col2) y otro índice único en (col2, col1) es todo lo que necesita
A menos que use un OR que no pueda hacer frente y dicte el diseño de la base de datos por usted...
Editar: Respondí lo mismo aquí: SQL: ¿Necesita una clave primaria auto-incremental para Muchas-Muchas tablas?
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:54:43
Podría ser necesaria una clave primaria incremental si se hace referencia a la tabla. Podría haber detalles en la tabla de muchos a muchos que necesitaran ser extraídos de otra tabla usando la clave primaria incremental.
Por ejemplo
PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)
Other Details
Es fácil extraer los 'Otros detalles' usando PartDevice.ID como el FK. Por lo tanto, es necesario el uso de la clave primaria incremental.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-26 12:41:20
La forma más corta y directa de responder a su pregunta es decir que habrá un impacto en el rendimiento si las dos tablas que está vinculando no tienen claves primarias secuenciales. Como ha indicado / citado, el índice de la tabla de enlaces se fragmentará o el DBMS trabajará más duro para insertar registros si la tabla de enlaces no tiene su propia clave primaria secuencial. Esta es la razón por la que la mayoría de las personas ponen una clave primaria que aumenta secuencialmente en las tablas de enlaces.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-03 07:24:42
Así que parece que si el ÚNICO trabajo es vincular las dos tablas, el mejor PK sería el PK de doble columna.
Pero si sirve para otros propósitos, agregue otro NDX como PK con claves foráneas y un segundo índice único.
Index o PK es la mejor manera de asegurarse de que no haya duplicados. PK permite que herramientas como Microsoft Management Studio hagan parte del trabajo (creando vistas) por usted
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-09 18:28:46