El plan de ejecución es una herramienta fundamental para optimizar consultas SQL. Sin comprenderlo, es prácticamente imposible realizar ajustes de rendimiento efectivos. ¿Qué es exactamente? Es la ruta que el optimizador de MySQL decide seguir para ejecutar una sentencia SQL.

Para obtenerlo, se utiliza la palabra clave EXPLAIN antes de la consulta. También es posible combinarlo con SHOW WARNINGS para obtener información adicional, como por qué no se utilizó cierto índice.

EXPLAIN SELECT * FROM usuario;

-- SHOW WARNINGS aclara, por ejemplo, por qué un índice no fue usado
EXPLAIN SELECT * FROM usuario;
SHOW WARNINGS;

La parte crítica es interpretar el resultado. Las columnas devueltas son:

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

A continuación, se explica cada una en detalle.

Campo id

Identifica el orden de ejecución de las consultas o subconsultas. Un número de id más alto indica mayor prioridad de ejecución. Se presentan tres escenarios:

• Idénticos: Cuando todos los id son iguales, las tablas se procesan en el orden en que aparecen en el plan, de arriba hacia abajo.
• Diferentes: Si los id varían, el que tenga el valor más alto se ejecuta primero.
• Mixtos: Se ejecutan primero los id más altos, y luego, entre los del mismo nivel, se sigue el orden ascendente de la lista.

Campo select_type

Indica el tipo de consulta. Los valores más comunes son:

• SIMPLE: Consulta simple sin subconsultas ni UNION.
• PRIMARY: Consulta principal en una subconsulta.
• SUBQUERY: Subconsulta dentro de SELECT o WHERE.
• DERIVED: Subconsulta en la cláusula FROM; MySQL la ejecuta y guarda el resultado en una tabla temporal.
• UNION: Segundo o posteriores SELECT en una UNION.
• DEPENDENT UNION: Similar a UNION, pero depende del resultado de la consulta externa.
• UNION RESULT: Operación que fusiona los resultados de una UNION.

Campo table

Muestra la tabla a la que se refiere la fila. Si aparece entre corchetes angulares, como <derivedN>, es una tabla temporal generada internamente, donde N es el id de la consulta que la produce.

Campo partitions

Indica las particiones que coinciden con la consulta. En tablas no particionadas, el valor es NULL.

Campo type

El método de acceso a los datos. Ordenados de mejor a peor rendimiento:

system > const > eq_ref > ref > range > index > ALL

Se recomienda alcanzar al menos range; idealmente ref o superior.

• system: Solo una fila en la tabla (caso especial de const). Es muy raro.
• const: Búsqueda por clave primaria o índice único que retorna una sola fila.

EXPLAIN SELECT * FROM producto WHERE id = 127;

• eq_ref: En joins, cuando se usa una clave primaria o índice único como condición de enlace. Para cada fila de la tabla anterior, solo hay una coincidencia.

EXPLAIN SELECT * FROM inventario, producto WHERE producto.id = inventario.producto_id;

• ref: Búsqueda por índice no único que devuelve varias filas que coinciden con un valor.

EXPLAIN SELECT * FROM regla WHERE plan_id = 127;

• range: Búsqueda por rango usando un índice (operadores como BETWEEN, IN, >, <).

EXPLAIN SELECT * FROM regla WHERE plan_id > 127;

• index: Recorrido completo del índice (full index scan). Si en Extra aparece Using index, la consulta se resuelve solo con el índice. Si no, puede incluir lecturas de la tabla.

EXPLAIN SELECT id FROM regla;

• index_merge: Combinación de varios índices para resolver la consulta.

EXPLAIN SELECT * FROM regla WHERE plan_id = 5 OR id = 10;

• ALL: Escaneo completo de la tabla; debe evitarse.

Campo possible_keys

Lista los índices que MySQL podría usar. No garantiza que realmente los utilice.

Campo key

El índice que MySQL eligió realmente.

Campo key_len

Longitud en bytes del índice utilizado. Cuanto más corto, mejor (siempre que no se pierda precisión). Depende de la estructura de la tabla, no de los datos reales.

Fórmulas aproximadas para calcular key_len:

• Cadenas:
  charsize * column_length + 1 (si permite NULL) + 2 (si es varchar)
  Donde charsize es 4 para utf8mb4, 3 para utf8, 2 para gbk, 1 para latin1.
• Numéricos:
  tinyint: 1, smallint: 2, int: 4, bigint: 8. Se suma 1 si permite NULL.
• Fechas:
  date: 3, timestamp: 4, datetime: 8. Se suma 1 si permite NULL.

Campo ref

Muestra las columnas o constantes usadas para buscar en el índice indicado en key. Los valores comunes son const (constante) o el nombre de una columna.

Campo rows

Estimación del número de filas que MySQL examinará para ejecutar la consulta.

Campo filtered

Porcentaje estimado de filas que cumplen las condiciones de la consulta. Multiplicando rows * filtered / 100 se obtiene una estimación del número de filas que se pasarán a la siguiente etapa.

Campo Extra

Contiene información adicional sobre la ejecución.

• Using filesort: MySQL necesita realizar una ordenación adicional que no puede hacer solo con el índice. Es una operación costosa que conviene optimizar. El espacio de memoria para la ordenación está controlado por sort_buffer_size (por defecto 2 MB). Si es insuficiente, se usan archivos temporales. Para monitorear el número de fusiones:

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

Si el valor es alto, considere aumenatr sort_buffer_size.

• Using temporary: Se crea una tabla temporal para almacenar resultados intermdeios, a menudo en consultas con GROUP BY u ordenación compleja. Debe evitarse. El límite de tamaño para tablas temporales en memoria es el menor entre tmp_table_size y max_heap_table_size. Si se supera, se usa disco. Para monitorear:

SHOW GLOBAL STATUS LIKE '%tmp%';

Ejemplo de salida:

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 4025    |
| Created_tmp_files       | 6366    |
| Created_tmp_tables      | 2096332 |
+-------------------------+---------+

Created_tmp_disk_tables es el total de tablas temporales creadas en disco.

• Using index: La consulta se resuelve completamente con el índice (covering index), sin acceder a la tabla. Si aparece junto a Using where, el índice se usó tanto para la búsqueda como para la recuperación de datos.
• Using where: Se aplica un filtro WHERE después de leer los datos. Puede indicar que no se usan solo índices.
• Using join buffer: En un JOIN, cuando no se usa un índice en la condición de enlace, MySQL necesita un buffer para almacenar filas intermedias. Se recomienda agregar un índice adecuado.
• Impossible WHERE: La condición WHERE siempre es falsa; no se devolverán filas.

Para profundizar, consulte la documentación oficial de MySQL sobre planes de ejecución.