uso - ¿Dos índices de una sola columna frente a un índice de dos columnas en MySQL?

usar indices en consulta sql

Question

Me enfrento a lo siguiente y no estoy seguro de cuál es la mejor práctica.

Considere la siguiente tabla (que se volverá grande):

id PK | giver_id FK | recipient_id FK | fecha

Estoy usando InnoDB y, según tengo entendido, crea índices automáticamente para las dos columnas de clave externa. Sin embargo, también haré muchas consultas en las que necesite hacer coincidir una combinación particular de:

SELECT...WHERE giver_id = x AND recipient_id = t .

Cada combinación será única en la tabla.

¿Hay algún beneficio al agregar un índice de dos columnas sobre estas columnas, o serían los dos índices individuales en teoría suficientes / iguales?

Answer 1

Otra cosa a considerar es que las características de rendimiento de ambos enfoques se basarán en el tamaño y la cardinalidad del conjunto de datos. Puede encontrar que el índice de 2 columnas solo se da cuenta de un rendimiento mayor en un cierto umbral de tamaño de conjunto de datos, o exactamente lo contrario. Nada puede sustituir las métricas de rendimiento para su escenario exacto.

Answer 2

Si tiene dos índices de columna individual, solo se usará uno de ellos en su ejemplo.

Si tiene un índice con dos columnas, la consulta puede ser más rápida (debe medir). Un índice de dos columnas también se puede usar como índice de una sola columna, pero solo para la columna que figura primero.

A veces puede ser útil tener un índice en (A, B) y otro índice en (B). Esto hace que las consultas utilizando una o ambas columnas sean rápidas, pero por supuesto también usa más espacio en el disco.

Al elegir los índices, también debe tener en cuenta el efecto en la inserción, eliminación y actualización. Más índices = actualizaciones más lentas.

Answer 3

Si uno de los índices de clave externa ya es muy selectivo, el motor de la base de datos debería usar ese para la consulta que especificó. La mayoría de los motores de base de datos utilizan algún tipo de heurística para poder elegir el índice óptimo en esa situación. Si ninguno de los índices es altamente selectivo por sí mismo, probablemente tiene sentido agregar el índice creado en ambas teclas, ya que dice que usará mucho ese tipo de consulta.

Otra cosa a considerar es si puede eliminar el campo PK en esta tabla y definir el índice de clave primaria en los campos giver_id y recipient_id . Dijiste que la combinación es única, así que posiblemente funcionaría (dadas muchas otras condiciones que solo tú puedes responder). Normalmente, sin embargo, creo que la complejidad añadida que se agrega no vale la pena.

Answer 4

Un índice de cobertura como:

ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);

... significaría que el índice podría usarse si una consulta se refería a giver_id , o una combinación de giver_id y recipient_id . Tenga en cuenta que los criterios de índice se basan más a la izquierda: una consulta que se refiera únicamente a recipient_id no podrá usar el índice de cobertura en la declaración que proporcioné.

Además, MySQL solo puede usar un índice por SELECCIÓN, por lo que un índice de cobertura sería la mejor manera de optimizar sus consultas.