Uso de la sentencia EXPLAIN en MySQL

3
62650

En este tutorial vamos a ver para qué se usa la sentencia EXPLAIN en MySQL a la hora de optimizar nuestras consultas.

Índice de contenidos

1. Introducción

En el siguiente tutorial explicaremos, de forma general, para qué se emplea la sentencia EXPLAIN. Esta sentencia nos permitirá obtener información sobre el plan de ejecución de nuestras consultas realizadas contra nuestra la base de datos. De esta forma, podremos analizar este plan de ejecución para saber cómo optimizar la ejecución de dichas consultas.

Para una información más exhaustiva sobre la sentencia EXPLAIN, se recomienda visitar el manual de MySQL con la versión de MySQL correspondiente.

2. Entorno

El tutorial se ha realizado usando el siguiente entorno:

  • Hardware: Portátil MacBook Pro Retina 15′ (2.5 Ghz Intel Core I7, 16GB DDR3).
  • Sistema Operativo: Mac OS Yosemite 10.10

3. La sentencia EXPLAIN

La sentencia EXPLAIN devuelve una tabla con una serie de filas con información sobre cada una de las tablas empleadas en la consulta a la que acompaña. EXPLAIN se puede emplear en las siguientes consultas: SELECT, DELETE, INSERT, REPLACE y UPDATE. Para emplear esta sentencia, bastará con poner EXPLAIN seguido de la consulta que queremos analizar. Por ejemplo:

EXPLAIN SELECT * FROM mi_tabla;

La tabla que devuelve la sentencia EXPLAIN lista las tablas en el orden en el que MySQL las leería procesando la consulta (la primera fila que aparece sería la primera tabla que se lee y la última fila sería la última tabla que sería leída). Este orden es importante conocerlo ya que nos indicará el plan de ejecución de la consulta y, por tanto, información relevante para realizar nuestras optimizaciones.

De forma adicional, EXPLAIN se puede combinar con EXTENDED para obtener más información del plan de ejecución. EXTENDED proporciona una columna más a la salida producida por EXPLAIN. Esta columna será la de «filtered» y nos indicará el porcentaje aproximado de filas que han sido filtradas por la condición empleada en la tabla. Para emplear esta sentencia, bastará con poner EXPLAIN EXTENDED seguido de la consulta que queremos analizar. Por ejemplo:

EXPLAIN EXTENDED SELECT * FROM mi_tabla;

Si empleamos EXTENDED, podemos también emplear SHOW WARNINGS después de ejecutar la consulta EXPLAIN. La sentencia SHOW WARNINGS nos mostrará la consulta reescrita tras la actuación del optimizador. Para emplear esta sentencia, bastará con poner SHOW WARNINGS después de ejecutar la consulta EXPLAIN EXTENDED. Por ejemplo:

EXPLAIN SELECT * FROM mi_tabla;
SHOW WARNINGS;

También, EXPLAIN se puede combinar con PARTITIONS para obtener información sobre las tablas particionadas de la consulta. Para emplear esta sentencia, bastará con poner EXPLAIN EXTENDED seguido de la consulta que queremos analizar. Por ejemplo:

EXPLAIN PARTITIONS SELECT * FROM mi_tabla;

4. Resultado de la ejecución de EXPLAIN

Como hemos mencionado anteriormente, la sentencia EXPLAIN nos devuelve una tabla con una serie de filas con información sobre cada una de las tablas empleadas en la consulta a la que acompaña. Esta tabla estará compuesta por una serie de columnas que nos proporcionarán información relevante sobre el plan de ejecución de dicha consulta.

A continuación, explicamos cada una de esas columnas:

  • id: muestra el número secuencial que identificará cada una de las tablas que se procesan en la consulta realizada.
  • select_type: muestra el tipo de SELECT que se ha ejecutado. En función del tipo de SELECT nos permitirá identificar qué tipo de consulta se trata. Existen varios tipos distintos: SIMPLE, PRIMARY, UNION, DERIVED…
  • table: muestra el nombre de la tabla a la que se refiere la información de la fila. También, puede ser alguno de los siguientes valores:
    • : tabla resultante de la unión de las tabas cuyos campos id son M y N.
    • : tabla resultante de una tabla derivada cuyo id es N. Una tabla derivada puede ser, por ejemplo, una subconsulta en la cláusula FROM.
    • : tabla resultante de una subconsulta materializada cuyo id es N.
  • partitions: muestra las particiones cuyos registros coinciden con los de la consulta. Esta columna sólo se muestra cuando se emplea EXPLAIN PARTITIONS.
  • type: muestra el tipo de unión que se ha empleado.
  • possible_keys: muestra qué índices puede escoger MySQL para buscar las filas de la tabla. Si esta columna es NULL, significa que no hay índices que puedan ser usados para mejorar la consulta. En este caso, podría ser interesante examinar las columnas empleadas en el WHERE para analizar si hay alguna columna que pueda emplearse para construir un índice.
  • key: muestra el índice que MySQL ha decido usar para ejecutar la consulta. Es posible que el nombre del índice no esté presente en la lista de possible_keys.
  • key_len: muestra el tamaño del índice que MySQL ha decidido usar para ejecutar la consulta.
  • ref: muestra qué columnas o constantes son comparadas con el índices especificado en la columna key.
  • rows: muestra el número de filas que MySQL cree que deben ser examinadas para ejecutar la consulta. Este número es un número aproximado.
  • filtered: muestra el porcentaje estimado de filas que serán filtradas por la condición de la consulta. Esta columna sólo se muestra cuando se emplea EXPLAIN EXTENDED.
  • Extra: muestra información adicional sobre cómo MySQL ejecuta la consulta.

5. Ejemplo de análisis

A continuación, vamos a ver un ejemplo de cómo usar EXPLAIN para identificar las posibles consultas lentas en nuestra base de datos.

Para este ejemplo, vamos a tener dos tablas: la tabla A y la tabla B. La tabla A va a tener las columnas X e Y mientras que la tabla B va a tener las columnas X y Z. El valor de X va a relacionar los datos Y y Z de ambas tablas. Vamos a poblar las tablas con unos 10.000 registros cada una. En un primer momento, no vamos a crear ningún índice para comprobar el rendimiento de la consulta.

CREATE TABLE A (
	X VARCHAR(10),
	Y VARCHAR(10)
);

CREATE TABLE B (
	X VARCHAR(10),
	Z VARCHAR(10)
);

Vamos a ejecutar la siguiente consulta:

EXPLAIN SELECT
	*
FROM
	A INNER JOIN B ON A.X = B.X
WHERE
	A.X = '100';

A continuación, el resultado de la ejecución de la consulta:

img1

Como podemos observar en la tabla obtenida como resultado, para calcular el resultado de la consulta se han escaneado 10.063 filas de cada una de las tablas. También observamos que no se ha usado ningún índice ya que no hay ninguno disponible. Este análisis nos indica que la consulta es poco óptima ya que se recorren todos los registros de la tabla para obtener el resultado.

Ahora, vamos a crear unos índices en ambas tablas:

CREATE UNIQUE INDEX a_unique_index ON A (X);
CREATE UNIQUE INDEX b_unique_index ON B (X);
ALTER TABLE B ADD CONSTRAINT fk_b_x FOREIGN KEY (X) REFERENCES A(X);

Por último, volvemos a realizar la consulta y analizamos el resultado:

img2

Como podemos observar en la tabla obtenida como resultado, para calcular el resultado de la consulta se ha escaneado 1 fila (en comparación a las 10.063 de la consulta anterior) de cada una de las tablas. También observamos que que se han usado los índices que hemos creado. Este análisis nos indica que la consulta se ha optimizado correctamente gracias a los índices.

6. Conclusiones

Como hemos visto en este tutorial, la sentencia EXPLAIN nos permite ver el plan de ejecución de la consulta que hemos realizado. De esta forma, podemos identificar qué pasos está ejecutando MySQL para devolvernos el resultado esperado. Viendo los pasos podemos identificar qué partes se pueden optimizar para conseguir un mejor resultado.

7. Referencias

3 COMENTARIOS

  1. […] Antes de empezar a cambiar nada, lo primero que recomendamos es ejecutar el comando EXPLAIN sobre esa consulta. De esta manera podemos saber qué pasos sigue el gestor de la base de datos para realizarla, y de qué manera accede a las tablas en cada uno de ellos. Con esto podremos identificar posibles cuellos de botella. Si no estáis familiarizados con este comando podéis echar un vistazo a este tutorial de introducción. […]

DEJA UNA RESPUESTA

Por favor ingrese su comentario!

He leído y acepto la política de privacidad

Por favor ingrese su nombre aquí

Información básica acerca de la protección de datos

  • Responsable:
  • Finalidad:
  • Legitimación:
  • Destinatarios:
  • Derechos:
  • Más información: Puedes ampliar información acerca de la protección de datos en el siguiente enlace:política de privacidad