usar - Mysql cubriendo vs índice compuesto versus columna

tamaño de indices mysql

Question

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 tanto col3 como col4 juntos, ¿pero usé solo col3 en la cláusula WHERE que 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?

Answer 1

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 (a continuación) 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.

Answer 2

Subí la respuesta de Johan para completarla, pero creo que la siguiente afirmación que hace con respecto a los índices secundarios es incorrecta y / o confusa;

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 con que la clave primaria está INCLUIDA en el índice secundario, no estoy de acuerdo con que MySQL "siempre use un índice de cobertura" en la consulta SELECCIONADA especificada aquí.

Para ver por qué, tenga en cuenta que siempre se requiere un "análisis" de índice completo 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 no está 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.

Answer 3

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 índice compound , no compuesto.
Solo se usará la parte más a la izquierda del índice compuesto.
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 cuando todos los campos seleccionados en una consulta están 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 seleccionar.
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.

Answer 4

El índice compuesto no es lo mismo que un índice compuesto .

• 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 filtro y de clave de unión en el árbol B, pero mantiene las columnas de selección solo en las páginas de la hoja, ya que no se buscarán, sino que 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.