Pregunta Mysql cubriendo vs índice compuesto versus columna


En la siguiente consulta

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'
  AND   col4='value2'

Si tengo 2 índices separados uno en col3 y el otro en col4, ¿Cuál de ellos se usará en esta consulta?

Leí en alguna parte que para cada tabla en la consulta solo se usa un índice. ¿Eso significa que no hay forma de que la consulta use ambos índices?

En segundo lugar, si creé un índice compuesto usando ambos col3 y col4 juntos pero usados ​​solo col3 en el DÓNDE cláusula será peor para el rendimiento?  ejemplo:

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'

Por último, ¿es mejor simplemente usar índices de cobertura en todos los casos? y ¿difiere entre MYISAM y los motores de almacenamiento innodb?


32
2017-11-21 14:19


origen


Respuestas:


Un índice de cobertura no es lo mismo que un índice compuesto.

Si tengo 2 índices separados uno en col3 y el otro en col4, ¿cuál de ellos se usará en esta consulta?

El índice con la cardinalidad más alta.
MySQL mantiene estadísticas sobre qué índice tiene qué propiedades.
Se usará el índice que tenga el poder más discriminatorio (como se evidencia en las estadísticas de MySQL).

Leí en alguna parte que para cada tabla en la consulta solo se usa un índice. ¿Eso significa que no hay forma de que la consulta use ambos índices?

Puedes usar una subselección.
O mejor aún use un índice compuesto que incluya tanto col3 como col4.

En segundo lugar, si creé un índice compuesto usando tanto col3 como col4 juntos, ¿pero usé solo col3 en la cláusula WHERE que será peor para el rendimiento? ejemplo:

Índice compuesto
El término correcto es compound índice, no compuesto.
Solo el el más a la izquierda parte del índice compuesto será utilizado.
Entonces, si el índice se define como

index myindex (col3, col4)  <<-- will work with your example.
index myindex (col4, col3)  <<-- will not work. 

Ver: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

Tenga en cuenta que si selecciona un campo de la izquierda, puede salirse con la suya sin utilizar esa parte del índice en su cláusula where.
Imagina que tenemos un índice compuesto

Myindex(col1,col2)

SELECT col1 FROM table1 WHERE col2 = 200  <<-- will use index
SELECT * FROM table1 where col2 = 200     <<-- will NOT use index.  

El motivo por el que esto funciona es que la primera consulta usa el índice de cobertura y realiza un análisis al respecto.
La segunda consulta necesita acceder a la tabla y, por esa razón, escanear el índice no tiene sentido.
Esto solo funciona en InnoDB.

¿Qué es un índice de cobertura?
Un índice de cobertura se refiere al caso en que todos los campos seleccionados en una consulta son covered por un índice, en ese caso InnoDB (no MyISAM) nunca leerá los datos en la tabla, sino que solo usará los datos en el índice, acelerando significativamente la selección.
Tenga en cuenta que en InnoDB, la clave primaria se incluye en todos los índices secundarios, de modo que todos los índices secundarios son índices compuestos.
Esto significa que si ejecuta la siguiente consulta en InnoDB:

SELECT indexed_field FROM table1 WHERE pk = something

MySQL siempre usará un índice de cobertura y no accederá a la tabla real.


39
2017-11-21 14:28



Yo voté La respuesta de Johan para completar, pero Creo que la siguiente afirmación que hace con respecto a los índices secundarios es incorrectay / o confuso;

Note that in InnoDB the primary key is included in all secondary indexes, 
so in a way all secondary indexes are compound indexes.

This means that if you run the following query on InnoDB:

SELECT indexed_field FROM table1 WHERE pk = something

MySQL will always use a covering index and will not access the actual table.

Aunque estoy de acuerdo, la clave principal es INCLUIDO en el índice secundario, No estoy de acuerdo con MySQL "siempre usaré un índice de cobertura" en la consulta SELECCIONADA especificada aquí.

Para ver por qué, tenga en cuenta que un índice completo de "escaneo" siempre se requiere en este caso. Esto no es lo mismo que una operación de "búsqueda", sino que es un escaneo al 100% del contenido del índice secundario. Esto se debe al hecho de que el índice secundario es no ordenado por la clave primaria; está ordenado por "indexed_field" (de lo contrario, no sería de mucha utilidad como índice.)

A la luz de este último hecho, habrá casos en que sea más eficiente "buscar" la clave principal, y luego extraer indexed_field "de la tabla actual", no desde el índice secundario.


4
2017-11-21 19:46



Esta es una pregunta que escucho mucho y hay mucha confusión en torno a los problemas debido a:

  • Las diferencias en mySQL en los últimos años. Índices y soporte de índice múltiple cambiados a lo largo de los años (hacia el soporte)

  • las diferencias InnoDB / myISAM Hay algunas diferencias clave (abajo) pero no creo que los índices múltiples sean uno de ellos

MyISAM es más antiguo pero probado. Los datos en las tablas MyISAM se dividen en tres archivos diferentes para: - formato de tabla, datos e índices.
InnoDB es relativamente más nuevo que MyISAM y es seguro para transacciones. InnoDB también proporciona bloqueo de fila en lugar de bloqueo de tabla, lo que aumenta la concurrencia y el rendimiento de múltiples usuarios. InnoDB también tiene restricciones de clave externa.
Debido a su función de bloqueo de filas, InnoDB es muy adecuado para entornos de carga elevada.

Para estar seguro de las cosas, asegúrese de usar Explain_plan para analizar la ejecución de la consulta.


1
2017-11-21 14:36



Compuesto índice no es lo mismo que compuesto índice.

  • El índice compuesto cubre todas las columnas de su filtro, se unen y seleccionan los criterios. Todas estas columnas se almacenan en todas las páginas de índice en consecuencia en todo el árbol B del índice.
  • El índice compuesto cubre todas las columnas de clave de filtro y unión en el árbol B, pero mantiene el seleccionar columnas solo en las páginas de la hoja, ya que no se buscarán, ¡solo se extraerán! Esto ahorra espacio y, en consecuencia, crea menos páginas de índice y, por lo tanto, una E / S más rápida.

0
2018-06-03 12:34