MySQL: Optimiza Tus Consultas SQL Con Explain

Si te ha entrado el gusanillo de optimizar tus consultas SQL dentro del motor MySQL, te alegraras en saber que esto es posible, gracias a una utilidad interna que trae MySQL llamada EXPLAIN.

EXPLAIN es una herramienta potente, capaz de demostrarnos todo el proceso que ha de realizar el motor de base de datos antes de retornarnos los registros solicitados en una consulta SQL.

Por ejemplo, si ejecutamos la siguiente consulta:

EXPLAIN SELECT *
FROM user u

INNER JOIN user_profile up ON u.id = up.user_id;

Obtendremos una respuesta como la siguiente:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	up	ALL	user_id_idx	NULL	NULL	NULL	13
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	4	futbol.up.user_id	1

Pero, ¿qué quieren decir estas columnas? ¿qué información nos dan?

1. Table: Nos informa de la tabla a la que nos estamos refiriendo.
2. Type: El tipo de unión que se está usando. Desde la mejor hasta la peor, los tipos de uniones son system, const, eq_ref, ref, range, index, y ALL.
   • System: Tabla con una única fila, por tanto, la respuesta es inmediata.
   • Const: En la tabla coincide una única fila con los criterios indicados. Al sólo haber una fila, el optimizador toma este valor como constante, por este motivo este tipo de tablas son muy rápidas.
   • Eq_ref: Una fila de la tabla 1 será leída por cada combinación de filas de la tabla 2. Este tipo es usado cuando todas las partes de un índice se usan en la consulta y el índice es UNIQUE o PRIMARY KEY.
   • Ref: Todas las filas con valores en el índice que coincidan serán leídos desde esta tabla por cada combinación de filas de las tablas previas. Similar a eq_ref, pero usado cuando usa sólo un prefijo más a la izquierda de la clave o si la clave no es UNIQUE o PRIMARY KEY. Si la clave que es usada coincide sólo con pocas filas, esta union es buena.
   • Range: Sólo serán recuperadas las filas que estén en un rango dado, usando un índice para seleccionar las filas. La columna key indica cual índice es usado, y el valor key_len contiene la parte más grande de la clave que fue usada. La columna ref será NULL para este tipo.
   • Index: Escaneo completo de la tabla para cada combinación de filas de las tablas previas, revisando únicamente el índice.
   • ALL: Escaneo completo de la tabla para cada combinación de filas. Es el peor caso ya que revisará todas las filas para cada combinación.
3. Possible_keys: Posibles indices que utilizará la consulta.
4. Key: Índice utilizado para ejecutar la consulta. Si indica el valor NULL, no se ha escogido ningún índice.
5. Key_len: Cuanto más pequeño sea este valor, más rápida será la consulta, pues nos indica la longitud del índice usado.
6. Ref: Las columnas del índice que se está usando, o una constante si esta es posible.
7. Rows: Número de filas que MySQL debe analizar para devolver los datos solicitados.
8. Extra: Información complementaria sobre como MySQL ejecutará la consulta. Los posibles valores en este campo pueden ser:
   • Distinct: MySQL ha encontrado una fila coincidente con los filtros indicados y no necesita seguir analizando.
   • Not exists: MySQL fue capaz de hacer una optimización LEFT JOIN sobre la consulta y no examinará más filas en la tabla para la combinación de filas previa después de que encuentre una fila que coincida con el criterio LEFT JOIN.
   • Range checked for each record: No se encontró un índice válido. Para cada combinación de filas se hará un chequeo para determinar que indice utilizar y en caso de encontrar alguno válido, lo utilizará.
   • Using filesort: Este valor indica que MySQL necesita hacer un paso extra para encontrar la forma de ordenar las filas. Este tipo de consultas debe ser optimizada.
   • Using index: Recupera la información solicitada utilizando únicamente la información del índice. Esto sucede cuando todas las columnas requeridas forman parte del índice.
   • Using temporary: Para resolver esta consulta, MySQL creará una tabla temporal. Uno de los casos típicos en los que devuelve este valor es cuando usamos un ORDER BY sobre un conjunto de columnas diferentes a las indicadas en la clausula GROUP BY. Este tipo de consultas debe ser optimizada.
   • Where used: Se usará una clausula WHERE para determinar que filas serán comparadas con otra tabla. Si no deseamos regresar todas las filas desde la tabla, y el join es del tipo ALL o index, es muy probable que hayamos escrito algo mal en la consulta.
   Una respuesta en este campo del tipo “Using filesort” o “Using temporary” es susceptible de ser una consulta a optimizar.

Si analizamos la consulta ejecutada anteriormente,vemos que para la tabla con alias ‘up’ está utilizando el tipo de unión `ALL` debido a que no existe ningun filtro en la clausula WHERE (en este caso, es normal ya que queremos devolver todos los valores). Se podría utilizar el índice existente `user_id_idx` pero al no haber ningún filtro, no es necesario usarlo. Para devolver esta consulta MySQL analizará 13 filas.

La segunda fila hace referencia a la tabla con alias ‘u’. Utiliza un tipo de unión `eq_ref` ya que devolverá una fila para cada fila de la tabla `up` pudiendo usar y usando el índice primario de la tabla que tiene una longitud de 4 (muy pequeño). Se está utilizando la columna `user_id` del índice de la tabla `up` y de la base de datos `futbol` analizando una única fila para cada fila de la tabla contigua.
Todos los valores son correctos y podemos asegurar que esta consulta está optimizada!!