Indices EXPLAIN en MySQL

La instrucción explain muestra la forma en la que MySQL procesa las instrucciones select, utiliza índices y combina tablas. Con esta instrucción nos hara más fácil seleccionar mejores índices y combinación de tablas así como escribior consultas opcionales.

Para utilizarla la pondremos delante de select:

Vemos que nos devuelve una tabla con una serie de filas con información sobre cada una de las tablas empleadas en la consulta.

Veamos el significado de las columnas:

  • id.- Muestra un número secuencial que identificará cada una de las tablas que son procesadas en la consulta realizada.
  • select_type.- Muestra el tipo de select que se ha ejecutado. Esta función de tipo select nos permitirá identificar de que tipo de consulta se trata.
  • table.- Muestra a que tabla se refiere el resto de la columna (esto es útil cuando se combina más de una tabla en una columna).
  • type.- Esta columna muestra el tipo de combinación que se está utilizando. Los tipos de combinación, ordenados de mejor a peor, son:
    • const
    • eq_ref
    • ref
    • range
    • index
    • all
  • possible_keys.- Muestra los índices que se podrían aplicar a la tabla. Si está vacia, no tendrá índices disponibles. Podemos disponer de uno si realizamos una busqueda sobre un campo relevante desde la clausula where.
  • key.- Muestra el índice que se está utilizando. Si el valor de esta columna fuera NULL, es que no estamos utilizando ningún índice. Ocasionalmente MySQL selecciona un índice que resulta menos optimo que otro. En este caso podemos obligar a MySQL a seleccionar otros índice utilizando use index (nombre del índice) dentro de la construcción select o a ignorar un índice con ignore index (nombre de índice).
  • key_len.- Muestra la longitud del índice utilizado. Cuanto menor sea su tamaño, mejor.
  • ref.- Indica la columna del índice que se está utilizando o una constante, si fuera posible.
  • rows.- Número de filas que MySQL considera que debe examinar para poder devolver los datos requeridos.
  • extra.- Información extra de cómo ha ido la consulta.
    • Distinct.- Cuando MySQL encuentra una fila que coincide con la combinación de file, deja de buscar otras.
    • Not exist.- MySQL optimizó la combinación por la izquierda y trás encontrar una fila que cumpla los criterios de esta combinación, deja de buscar otras.
    • range checked for each record (index map: #).- Al no encontrar un índice ideal, MySQL comprueba el índice que debe utilizar y lo utiliza para recuperar filas de la tabla. De hecho es una de las combinaciones más lentas con un índice.
    • Using filesort.- Si vemos esta descripción, la consulta debe ser optimizada. MySQL necesitará realizar un paso extra para determinar como ordenar las filas que devuelve. Para ordenarlas, recorre todas las filas en función del tipo de combinación almacenando la clave de ordenación en un puntero señalando a la fila de todas las filas que cumplan la condición. Acto seguido ordena todas las claves y, finalmente, devuelve las filas en un orden determinado.
    • Using index.- Los datos de la tabla se devuelven desde la tabla usando solamente la información del índice sin que necesite leer la fila. Esto ocurre cuando todas las columnas requeridas de la tabla forman parte del mismo índice.
    • Using temporary.- Si vemos esta descripción, debemos saber que la consulta debe optimizarse. MySQL creará una tabla temporal para recoger los datos, igualmente cuando realizamos una operación de ordenación sobre un conjunto de columnas diferentes a las agrupadas.
    • Where used.- La clausula where se usa para restringir las filas que se utiizaran en la selección de la tabla siguiente o que se devolvera al cliente. Si no queremos recuperar todas las filas de la tabla y el tipo de combinación es ALL o index, debería aparecer esta descripción; de lo contrario, es posible que su consulta presente algún problema.

Diferentes tipos de combinación

Veamos los diferentes tipos de combinación ordenados cd mayor a menor por eficacia.

  • system.- La tabla solo tiene una fila: es una tabla de sistema. Este es un caso especial del tipo de combinación const.
  • const.- El número máximo de coincidencias que puede extraer esta consulta de la tabla es de un registro (el índice será una clave primaria o un índice exclusivo). Si solo contiene una fila, el valor es una constante, ya que MySQL lee el valor y, a continuación, lo utiliza de forma identica a una constante.
  • eq_ref.- En una combinación, MySQL lee un registro de la tabla para cada combinación de registros de las tablas anteriores de la consulta. Se usa cuando la consulta utiliza partes de un índice que sea una clave primaria o una clave única.
  • ref.- Esto ocurre si la consulta utilizada en esta combinación es una clave que no es única o primaria o que solo forme parte de uno de estos tipos (por ejemplo, si utilizamos el sistema de prefijación más a la izquierda). Todos los registros que coincidan seran leidos de la tabla para cada combinación de filas de las tablas anteriores. Este tipo de combinación depende enormemente de la gran cantidad de registros que coincidan con el índice.
  • index.- Este tipo de combinación examina el índice completo para cada combinación de registros de las tablas anteriores (lo que resulta mejor que la opción ALL, ya que el tamaño de los indices suelen ser menor que los datos de la tabla).
  • ALL.- Esta combinación examina toda la tabla para cada combinación de registros de las tablas anteriores. Por regla general esta combinación no es nada buena y debemos evitarla siempre que resulte posible.

Podemos consultar con explain varias tablas:

Las tablas mostradas estan en el orden en el que MySQL las leería procesando la consulta (la primera fila sería de la primera tabla que se lee y la última fila sería la última tabla que sería leida).

Adicionalmente podemos combinar explain con extended para obtener más información del plan de ejecución.

Vemos que extended proporcionó una columna más llamada filtrered, que nos indica el porcentaje aprosimado de filas que han sido filtradas por la condición empleada en la tabla. Si empleamos extended, podemos usar tambien show warnings despues de ejecutar la consulta explain.

Tambien podemos combinar explain con partitions para obtener información sobre las tablas particioinadas de la consulta.

Vamos a crear dos tablas para los ejemplos, una la llamaremos A y la otra B. En la tabla A tendremos las columnas X e Y mientras que en la tabla B tendremos las columnas X y Z. El valor de X va a estar relacionado con los datos Y y Z de ambas tablas.

Tabla A:

Tabla B:

Ejecutamos una consulta:

A continuación observamos que ha escaneado un registro, así como, no usó ningún índice ya que no hay ninguno disponible. Este analisis nos muestra que la consulta no ha sido óptima ya que recorrerá todos los registros de una tabla para obteber ub resultado.

La sentencia explain nos permite ver el plan de ejecución de la consulta que hemos realizado para poder identificar los pasos que MySQL está siguiendo devolver el resultado esperado.

  • Hemos visto otra forma de mostrar información de una tabla con explain select.
  • Hemos explicado el significado de las columnas presentadas por explain.
  • Hemos consultado varias tablas con explain.
  • Se combinó explain con extended para obtener una columna más de información.
  • Hemos visto la información de las particiones con explain partitions.