Estoy enfrentado con la siguiente y no estoy seguro de cuál es la mejor práctica.

Considere la siguiente tabla (que tendrán que ser grande):

id PK | giver_id FK | recipient_id FK | fecha

Estoy usando InnoDB y de lo que entiendo, lo que crea índices de forma automática por las dos columnas de clave externa. Sin embargo, también voy a estar haciendo un montón de consultas donde tengo que coincidir con una combinación particular de:

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

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

Tiene algún beneficio de la adición de un dos-índice de columna sobre estas columnas, o los dos índices individuales, en teoría, ser suficiente o el mismo?

  • Si la combinación de las dos columnas es único, puede crear dos índices de columna con la única característica que no sólo aumentará la velocidad de su consulta, sino también añadir consistencia a su mesa.
  • «MySQL puede utilizar varios índices de las columnas para las consultas que probar todas las columnas en el índice, o consultas que prueba la primera columna, las primeras dos columnas, la primera de tres columnas, y así sucesivamente. Si especifica las columnas en el orden correcto en la definición del índice, de un único índice compuesto puede acelerar varios tipos de consultas en la misma mesa». – Varios Índices de las Columnas
  • Para extrapolar en @user1585784; Si la combinación de las dos columnas es único, creo que uno debe utilizar una clave única para ellos. De hecho, si uno quiere hacer cumplir la singularidad en el nivel de base de datos, una clave única es la manera más fácil de ir…
InformationsquelleAutor Tom | 2010-02-28

4 Comentarios

  1. 113

    Si usted tiene dos índices de las columnas, sólo uno de ellos será usado en tu ejemplo.

    Si usted tiene un índice con dos columnas, la consulta podría ser más rápido (se debe medir). Una de dos columnas de índice también puede ser utilizado como un único índice de columna, pero sólo para la columna primero de la lista.

    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 de las columnas rápido, pero, por supuesto, utiliza también más espacio en disco.

    Cuando la elección de los índices, también es necesario considerar el efecto sobre la inserción, eliminación y actualización. Más índices = más lento actualizaciones.

    • «MySQL puede utilizar varios índices de las columnas para las consultas que probar todas las columnas en el índice, o consultas que prueba la primera columna, las primeras dos columnas, la primera de tres columnas, y así sucesivamente. Si especifica las columnas en el orden correcto en la definición del índice, de un único índice compuesto puede acelerar varios tipos de consultas en la misma mesa». – Varios Índices de las Columnas
  2. 25

    Un índice de cobertura como:

    ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);

    …significaría que el índice podría ser utilizado si la consulta se refiere a giver_id, o una combinación de giver_id y recipient_id. Cuenta que el índice de criterios de la izquierda basada en una consulta en referencia a sólo recipient_id no sería capaz de utilizar el índice de cobertura en la declaración que he proporcionado.

    Además, MySQL sólo puede utilizar un índice por SELECCIONAR un índice de cobertura sería el mejor medio para la optimización de las consultas.

    • MySQL can only use one index per SELECT esto ya no es cierto, sería agradable si usted ha editado su respuesta a actualizarse.
    • ¿Te importaría explicar por qué el índice de cobertura no sería capaz de ser utilizado por recipient_id?
    • Multi columna de los índices en MySQL vamos a utilizar todos los campos en el índice de izquierda a derecha. Por ejemplo, si usted tiene un INDEX (col1, col2, col3, col4), el índice se aplicará para las búsquedas con un WHERE cláusula como col1 = 'A' o col1 = 'A' AND col2 = 'B' o col1 = 'A' AND col2 ='B' AND col3 = 'C' AND col4 = 'D', pero este índice en particular no se utiliza para nada como WHERE col2 = 'B' o WHERE col3 = 'C' AND col4 = 'D' porque los campos de búsqueda no están más a la izquierda en la definición del índice. Habría que añadir índices adicionales para cubrir esos campos.
    • «un índice por SELECCIONAR», es cierto esto todavía para mariadb 10.1?
    • No. ver el comentario por Davor arriba.
  3. 4

    Si uno de los extranjeros índices de clave ya es muy selectiva, a continuación, el motor de base de datos debe usar para la consulta especificada. La mayoría de los motores de base de datos uso de algún tipo de heurística para ser capaz de elegir el índice óptimo en esa situación. Si ni el índice es muy selectiva, por sí mismo, probablemente tiene sentido agregar el índice construido en ambas teclas ya que dicen que va a usar ese tipo de consulta mucho.

    Otra cosa a considerar es si se puede eliminar el PK campo en esta tabla y definir el índice de clave principal en la giver_id y recipient_id campos. Usted dijo que la combinación es única, por lo que, posiblemente, el trabajo (dado un montón de otras condiciones que sólo usted puede responder). Normalmente, sin embargo, creo que la mayor complejidad que añade es que no vale la pena la molestia.

    • Gracias Marcos, una de las claves es, de hecho, muy selectiva, por lo que debe estar bien. He optado por mantener las dos (automático) los índices en su lugar y ver cómo se comporta a lo largo del tiempo. También pensé en un combinado dador:destinatario de la clave principal, pero como cada campo también debe ser consultada de forma individual, sería simplemente añadir php sobrecarga. También, la nueva clave sería una (o más) de cadena en lugar de una (corta) entero.
  4. 1

    Otra cosa a considerar es que las características de rendimiento de ambos enfoques se basan en el tamaño y la cardinalidad del conjunto de datos. Usted puede encontrar que el 2-índice de columna sólo se hace notar más eficiente en un determinado conjunto de datos de tamaño de umbral, o el opuesto exacto. Nada puede sustituir a las métricas de rendimiento para su situación exacta.

    • podría usted por favor, enlace a la documentación de todo esto. Gracias.

Dejar respuesta

Please enter your comment!
Please enter your name here