ver - ¿Cómo indexar correctamente una tabla de vinculación para la conexión muchos a muchos en MySQL?

tipos de indices en base de datos

Question

Digamos que tengo una tabla de varios a muchos entre las tablas "tabla1" y "tabla2" que consta de dos campos int: "tabla1-id" y "tabla2-id". ¿Cómo debería indexar esta tabla de enlaces?

Solía ​​hacer un índice compuesto primario (table1-id, table2-id), pero leí que este índice podría no funcionar si cambia el orden de los campos en la consulta. Entonces, ¿cuál es la solución óptima? ¿Hacer índices independientes para cada campo sin un índice primario?

Gracias.

Answer 1

@Quassnoi, en su primera consulta, en realidad está usando solo la clave tt.table_1 como podemos ver en la cláusula WHERE t1.id = @id : WHERE t1.id = @id . Y en la segunda consulta, solo tt.table_2 .

Por lo tanto, el índice de varias columnas podría ser útil solo en la tercera consulta debido a WHERE table_1 = @id1 AND table_2 = @id2 . Si las consultas de este tipo no van a ser utilizadas, ¿cree que vale la pena usar dos índices separados de una columna?

Answer 2

Depende de cómo busques.

Si busca de esta manera:

/* Given a value from table1, find all related values from table2 */
SELECT *
FROM table1 t1
JOIN table_table tt ON (tt.table_1 = t1.id)
JOIN table2 t2 ON (t2.id = tt.table_2)
WHERE t1.id = @id

entonces necesitas:

ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)

En este caso, la table1 estará liderando en NESTED LOOPS y su índice solo se podrá utilizar cuando la table1 se indexe primero.

Si busca de esta manera:

/* Given a value from table2, find all related values from table1 */
SELECT *
FROM table2 t2
JOIN table_table tt ON (tt.table_2 = t2.id)
JOIN table1 t1 ON (t1.id = tt.table_1)
WHERE t2.id = @id

entonces necesitas:

ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)

por las razones anteriores.

No necesita índices independientes aquí. Un índice compuesto se puede usar en cualquier lugar donde se pueda usar un índice simple en la primera columna. Si usa índices independientes, no podrá buscar de manera eficiente ambos valores:

/* Check if relationship exists between two given values */
SELECT 1
FROM table_table
WHERE table_1 = @id1
  AND table_2 = @id2

Para una consulta como esta, necesitará al menos un índice en ambas columnas.

Nunca es malo tener un índice adicional para el segundo campo:

ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)
CREATE INDEX ix_table2 ON table_table (table_2)

La clave principal se usará para las búsquedas on both values y para las búsquedas basadas en el valor de la table_1 , se table_1 un índice adicional para las búsquedas basadas en el valor de la table_2 .

Answer 3

Mientras especifiques ambas claves en la consulta, no importa el orden que tengan en la consulta, ni importa en qué orden las especifiques en el índice.

Sin embargo, no es poco probable que a veces solo tenga una u otra de las claves. Si a veces solo tiene id_1, entonces debería ser el primero (pero aún así solo necesita un índice).

Si a veces tiene uno, a veces el otro, a veces ambos, necesitará un índice con ambas claves, y un segundo índice (no único) con un campo - la más selectiva de las dos claves - y el índice compuesto primario debería comienza con la otra tecla.