High Performance MySQL [Cap. 6: Optimización del rendimiento de consultas]

Por qué una consulta es lenta

Una consulta es una secuencia de tareas: analizar, planificar, ejecutar y enviar resultados. La ejecución domina, porque ahí es donde el servidor pide filas al motor de almacenamiento y luego las ordena, agrupa y filtra. Acelerar una consulta significa eliminar tareas, ejecutarlas menos veces o abaratarlas, y la palanca más grande es casi siempre la cantidad de datos que se tocan.

¿La aplicación pide demasiado?

• Más filas de las que muestra. MySQL construye el resultado completo aunque el cliente solo lea las diez primeras. Usa LIMIT.
• Todas las columnas. SELECT * arrastra filas completas a través de los joins y descarta los índices de cobertura. Nombra las columnas que usas.
• Los mismos datos, una y otra vez. Obtener el nombre de un usuario, luego su email y luego su avatar en tres consultas son tres viajes de ida y vuelta para una sola fila.
• Consultas N+1. Los ORM que cargan asociaciones de forma perezosa convierten "listar proyectos y sus bugs" en una consulta más otra por cada proyecto. Carga la asociación de antemano con un único WHERE project_id IN (...).

¿El servidor examina demasiado?

Tres números describen el coste de una consulta, y los tres aparecen en el slow query log: tiempo de respuesta, filas examinadas y filas devueltas. El tiempo de respuesta es el más ruidoso. Incluye esperas por bloqueos y E/S, y varía según lo que esté haciendo el servidor en ese momento. La proporción entre filas examinadas y filas devueltas dice más: 1:1 es lo ideal, los joins y los agregados la elevan de forma legítima, y una proporción de miles significa que el servidor lee filas solo para descartarlas.

El mejor indicador es el tipo de acceso, la columna type de EXPLAIN:

La columna Extra indica dónde ocurre el filtrado. Using index significa que un índice de cobertura respondió la consulta sin tocar filas. Using index condition significa que el motor filtró por columnas del índice antes de leer las filas. Using where significa que se leyeron las filas y luego el servidor las filtró, que es la forma cara.

Cuando el tipo de acceso es incorrecto, la solución es un índice que la consulta pueda usar, reescribir la consulta para que use uno existente o un cambio de esquema, como una tabla de resumen.

Reestructurar consultas

El objetivo es obtener la misma respuesta con menos trabajo o, a veces, una respuesta ligeramente distinta que sea aceptable y mucho más barata.

¿Una consulta compleja o varias simples? La regla antigua era minimizar los viajes de ida y vuelta. MySQL conecta y responde rápido a las consultas pequeñas, y las redes dentro de un centro de datos son rápidas, así que dividir el trabajo suele estar bien y es más fácil de mantener. Valóralo caso por caso.

Divide las escrituras grandes. Un único DELETE de millones de filas antiguas mantiene los bloqueos durante toda su ejecución, infla el undo log y frena las réplicas. Borra en lotes y, si hace falta, haz pausas entre ellos:

-- una sentencia, una transacción enorme
DELETE FROM bugs WHERE created_at < NOW() - INTERVAL 1 YEAR;

-- repetir hasta que afecte a 0 filas
DELETE FROM bugs WHERE created_at < NOW() - INTERVAL 1 YEAR LIMIT 5000;

Descompón los joins. Ejecutar una consulta por tabla y hacer el join en la aplicación puede ayudar cuando los resultados se pueden cachear (el id de una etiqueta rara vez cambia), cuando consultas más cortas reducen la contención de bloqueos o cuando una lista IN (...) de ids ordenados lee las filas de forma más secuencial que un bucle anidado. Es una herramienta, no la opción por defecto.

Del árbol sintáctico al plan

El parser convierte el SQL en un árbol y rechaza los errores de sintaxis. El preprocesador comprueba lo que la gramática no puede: que las tablas y columnas existen, que los nombres no son ambiguos y que el usuario tiene privilegios para la sentencia. Después, el optimizador busca un plan barato.

El optimizador de MySQL está basado en costes, con algunos atajos basados en reglas. Estima el coste de los planes candidatos a partir de estadísticas (tamaño de tablas e índices, cardinalidad de los índices, distribución de las claves) y elige el más barato. Puede elegir un plan peor por razones predecibles: las estadísticas son estimaciones y los recuentos de filas de InnoDB son aproximados; su modelo de costes no sabe qué páginas ya están en caché; y no puede ver el coste del trabajo que no controla, como las funciones almacenadas. El coste estimado de la última consulta está en Last_query_cost.

Lo que el optimizador hace por ti

• Reordena los joins para empezar por la tabla que produce menos filas.
• Convierte outer joins en inner joins cuando la cláusula WHERE rechaza de todos modos las filas con NULL.
• Simplifica expresiones: 5 = 5 AND a > 5 se convierte en a > 5, y las condiciones imposibles terminan la consulta antes de tiempo.
• Resuelve primero las constantes. Una búsqueda por clave primaria en una tabla se convierte en una constante que usa el resto del plan.
• Propaga las igualdades entre columnas de join, de modo que un filtro sobre una tabla también acota la otra.
• Responde MIN() y MAX() desde un extremo del índice: una sola lectura al principio o al final del B-Tree.
• Ordena las listas IN() y hace búsqueda binaria en ellas en lugar de recorrerlas.
• Se detiene pronto con LIMIT, DISTINCT y NOT EXISTS en cuanto conoce la respuesta.
• Reescribe subconsultas como semi-joins o búsquedas en índice cuando puede.

Cómo ejecuta MySQL los joins

MySQL trata cualquier operación con varias tablas, incluidas las tablas derivadas y UNION, como un join, y su algoritmo tradicional es el bucle anidado (nested loop): por cada fila de la primera tabla, busca las filas que coinciden en la segunda; por cada una de ellas, busca coincidencias en la tercera; emite una fila cuando todas las tablas han coincidido y luego retrocede. Un LEFT JOIN es el mismo bucle, que emite NULL cuando la tabla interna no tiene coincidencia; RIGHT JOIN se reescribe como un left join. El modelo no puede expresar FULL OUTER JOIN, que MySQL sigue sin admitir. Desde MySQL 8.0.18, los equi-joins sin un índice utilizable se ejecutan como hash join en lugar de un bucle anidado.

Como el coste es "filas de la tabla externa × búsquedas por fila", el orden de los joins importa muchísimo. Hacer join de 5.000 estudiantes con sus notas cuesta 5.000 búsquedas en el índice de notas, escribas como escribas la cláusula FROM; el optimizador elige el orden con menos sondeos. STRAIGHT_JOIN fuerza el orden en que lo escribiste, lo que es útil para probar y, de vez en cuando, para corregir una mala estimación. Con muchas tablas, el número de órdenes posibles se dispara (10 tablas ya tienen 3.628.800), así que más allá de optimizer_search_depth MySQL pasa a una búsqueda voraz.

El plan final no es bytecode. Es un árbol de operaciones que recorre el ejecutor, y EXPLAIN FORMAT=TREE lo muestra directamente.

Cómo se refleja en tu veredicto

Cada reto se evalúa por las filas examinadas frente a un plan de referencia, que es este capítulo resumido en un número. Hacer join con una tabla que no necesitas, agrupar filas que ya son únicas o filtrar después de un recorrido en lugar de durante una búsqueda en índice se nota como lecturas handler de más, y como CORRECT_BUT_INEFFICIENT.

Preguntas frecuentes