Cómo detectar consultas lentas en MySQL

Cómo detectar consultas lentas en un servidor MySQL con el objetivo de optimizarlas.

En una primera fase de desarrollo podemos detectar y optimizar las consultas lentas, con la declaración EXPLAIN.
El comando del MySQL Explain explica como se lleva a cabo una consulta , como se usan los índices y como se unen las tablas. Así podemos optimizar la consulta, añadir indices, etc.

También podemos activar el Query Profiler para analizar las sentencias ejecutadas, así ver lo que tarda cada una.

En cambio, cuando pasamos el sistema a producción puede que nos encontremos con alguna sorpresa. Al notar que la base de datos lenta. Pero, ¿cómo podemos saber que consultas se están ejecutando lentamente?
La respuesta es el log de MySQL que registra las consultas lentas. De tal forma que podemos ver todas aquellas consultas que tardan más de 1 segundo en ejecutarse.

Para activar este log tenemos que editar el archivo de configuración del servidor MySQL y añadir (o descomentar) las siguiente líneas:


Con esta configuración le indicamos la ruta del archivo log que contendrá el histórico de consultas lentas, a partir de cuantos segundo se considera que una consulta es lenta, y que también registre aquellas consultas que no utilizan ningún indice para acelerar la extracción de datos (es aconsejable añadir algún índice).

Tras reiniciar el servicio de MySQL tendremos el log activado y registrando en todo momento las consultas lentas.

Cuando el log haya recogido varias consultas lentas tenemos que analizarlo para localizar estas consultas, optimizarlas o sutituirlas por algún método de cacheo de datos. Para este análisis os recomiendo la aplicación mk-query-digest disponible en el tookit Maatkit.