sql - tablas - ¿Cómo indexar correctamente una tabla de asociación de muchos muchos?
que son los indices en oracle (2)
En un arreglo típico de muchos, muchos como este ...
Movies Actors Movies_Actors ------ ------ ------------- movie_ID actor_ID FK_movie_ID title name FK_actor_ID
... ¿cómo debe ''Movies_Actors'' tabla de asociación ( ''Movies_Actors'' ) para obtener una velocidad de lectura óptima?
Normalmente veo que esto se hace solo con la clave primaria compuesta en la tabla de asociación, de esta manera:
CREATE TABLE Movies_Actors (
FK_movie_ID INTEGER,
FK_actor_ID INTEGER,
PRIMARY KEY (FK_movie_ID, FK_actor_ID)
)
Sin embargo, esto parece que el índice solo será útil al buscar tanto movie_ID como actor_ID (aunque no estoy seguro de si un índice compuesto también funciona para las columnas individuales).
Dado que tanto "qué actores están en la Película X" como "en qué películas ha estado el actor Y" serán las consultas comunes para esta tabla, parece que debería haber un índice individual en cada columna para localizar rápidamente actores y películas por su cuenta . ¿Un índice compuesto hace esto efectivamente? Si no, tener un índice compuesto no tiene sentido en esta tabla. Y si un índice compuesto no tiene sentido, ¿qué hacer con una clave principal? La clave candidata es claramente el compuesto de las dos columnas, pero si el índice compuesto resultante no tiene sentido (¿no debe serlo?) Parece un desperdicio.
Además, este enlace agrega cierta confusión e indica que incluso podría ser útil especificar realmente dos índices compuestos ... uno de ellos como (FK_movie_ID, FK_actor_ID) , y el otro al revés como (FK_actor_ID, FK_movie_ID) , con la opción de que es la clave principal (y, por lo tanto, generalmente agrupada) y que es "solo" un índice compuesto único que se basa en qué dirección se consulta más.
¿Cuál es la historia real? ¿Un índice compuesto indexa automáticamente cada columna para buscar en una u otra? ¿Debería la tabla de asociación óptima (en velocidad de lectura, no en tamaño) tener un índice compuesto en cada dirección y uno en cada columna? ¿Cuáles son los mecanismos detrás de la escena?
EDIT: Encontré esta pregunta relacionada que, por alguna razón, no localicé antes de publicar ... ¿Cómo indexar correctamente una tabla de enlaces para una conexión de muchos a muchos en MySQL?
(aunque no estoy seguro de si un índice compuesto también funciona para las columnas individuales).
Sí puede. Pero solo el prefijo: http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys
Además, este enlace agrega cierta confusión e indica que incluso podría ser útil especificar realmente dos índices compuestos ... uno de ellos como (FK_movie_ID, FK_actor_ID), y el otro al revés como (FK_actor_ID, FK_movie_ID),
Eso es realmente lo que hay que hacer.
Tome uno como índice de agrupamiento y el otro como índice no agrupado que de todos modos incluirá la clave del índice de agrupamiento, por lo que no es necesario incluir esa columna nuevamente (gracias a JNK).
CREATE CLUSTERING INDEX a on Movies_Actors (fk_movie_id, fk_actor_id);
CREATE NONCLUSTERING INDEX b on Movies_Actors (fk_actor_id);
¿Cuál es la historia real?
http://Use-The-Index-Luke.com/ :)
¿Un índice compuesto indexa automáticamente cada columna para buscar en una u otra?
No. Sólo el prefijo del índice. Si tiene un índice (a, b, c), la consulta a =? y b =? Puede utilizar el índice. Sin embargo c =? no puede, ni puede b =? y c = ?.
¿Debería la tabla de asociación óptima (en velocidad de lectura, no en tamaño) tener un índice compuesto en cada dirección y uno en cada columna?
Si necesita unirse en ambas direcciones, sí ("índice compuesto en cada dirección") y no ("uno en cada columna").
¿Cuáles son las mecánicas detrás de la escena?
Bueno, el mismo enlace de nuevo.
Hablando de SQL Server, también podría considerar una vista indexada. Eso es una especie de pre-unirse. Dos índices, como el anterior, también podrían ser lo suficientemente rápidos.
En SQL Server, se puede usar un índice compuesto para una búsqueda de un solo campo solo para la primera columna . Eso significa que debe tener un índice de un campo adicional en FK_actor_id si habrá búsquedas en ese campo sin FK_Movie_id en la misma consulta.