--- title: "Cómo optimizar consultas lentas en MySQL y PostgreSQL" description: "Activa el slow query log de MySQL, ordena las consultas con pt-query-digest, interpreta EXPLAIN y corrígelas con índices y reescrituras, también en PostgreSQL." category: "Gestión de Bases de Datos" language: es source: https://cubepath.com/es/docs/gestión-de-bases-de-datos/analisis-y-optimizacion-de-consultas-lentas updated: 2026-09-25 --- # Cómo optimizar consultas lentas en MySQL y PostgreSQL La mayoría de los problemas de rendimiento de una base de datos vienen de unas pocas consultas que leen muchas más filas de las que devuelven. La forma fiable de corregirlas es un ciclo: registrar las consultas lentas, ordenarlas por coste total, leer el plan de ejecución de la peor, cambiar un índice o la consulta y volver a medir. En este tutorial recorrerás ese ciclo con MySQL 8.0 en Ubuntu 24.04 sobre una tabla de ejemplo y después verás las herramientas equivalentes en PostgreSQL. ## Requisitos previos Para seguir este tutorial necesitas: - Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath. - Un usuario no root con privilegios `sudo`. - MySQL 8.0 instalado con `sudo apt install mysql-server`. En Ubuntu, la cuenta `root` de MySQL usa autenticación por socket, así que `sudo mysql` abre una sesión de root sin contraseña. - Para la sección de PostgreSQL, PostgreSQL 16 instalado con `sudo apt install postgresql`. Prueba los ejemplos primero en un servidor de pruebas o en una copia de tus datos. Crear un índice sobre una tabla grande en producción consume E/S y, en versiones antiguas o con ciertos tipos de columna, puede bloquear las escrituras. ## Paso 1: Crear una tabla de ejemplo Un ejemplo realista hace que los planes sean más fáciles de seguir. Abre una sesión de MySQL: ```bash sudo mysql ``` Crea una base de datos y una tabla `orders` con clave primaria y ningún otro índice: ```sql CREATE DATABASE perfdemo; USE perfdemo; CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(20) NOT NULL, total DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL ); ``` Rellénala con 500.000 filas mediante una expresión de tabla común (CTE) recursiva. MySQL limita la recursión a 1.000 niveles por defecto, así que primero sube el límite para esta sesión: ```sql SET SESSION cte_max_recursion_depth = 1000000; INSERT INTO orders (customer_id, status, total, created_at) WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 500000 ) SELECT FLOOR(1 + RAND() * 50000), ELT(1 + FLOOR(RAND() * 3), 'pending', 'paid', 'shipped'), ROUND(RAND() * 500, 2), NOW() - INTERVAL FLOOR(RAND() * 365) DAY FROM seq; ``` Comprueba el número de filas: ```sql SELECT COUNT(*) FROM orders; ``` ```output +----------+ | COUNT(*) | +----------+ | 500000 | +----------+ ``` Sal de la sesión con `exit`. ## Paso 2: Activar el registro de consultas lentas El registro de consultas lentas (slow query log) guarda cada sentencia que tarda más de `long_query_time` segundos, junto con cuántas filas examinó y cuántas devolvió. Crea un archivo de configuración aparte para que las actualizaciones del paquete no sobrescriban tus ajustes: ```bash sudo nano /etc/mysql/mysql.conf.d/slow-query.cnf ``` ```ini [mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = ON log_throttle_queries_not_using_indexes = 60 ``` Qué hace cada opción: - `long_query_time = 1` registra las sentencias que duran más de un segundo. Bájalo a `0.5` o `0.2` cuando hayas corregido las peores. - `log_queries_not_using_indexes` registra también consultas rápidas que recorren una tabla entera. Hoy pueden ir bien y mañana ser lentas, cuando la tabla crezca. - `log_throttle_queries_not_using_indexes = 60` escribe como mucho 60 de esas consultas por minuto, para que el log no llene el disco. Reinicia MySQL para cargar el archivo: ```bash sudo systemctl restart mysql ``` Comprueba los ajustes: ```bash sudo mysql -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('slow_query_log', 'slow_query_log_file', 'long_query_time');" ``` ```output +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | long_query_time | 1.000000 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | +---------------------+-------------------------------+ ``` > Consejo: puedes cambiar estas variables en caliente con `SET GLOBAL slow_query_log = ON;` sin reiniciar, pero el cambio se pierde en el siguiente reinicio si no está también en el archivo de configuración. ## Paso 3: Capturar y ordenar las consultas lentas En la tabla de ejemplo casi todas las consultas terminan en bastante menos de un segundo, así que para la demostración baja el umbral solo en tu sesión. `long_query_time` se puede fijar por sesión, algo útil también para perfilar un proceso concreto en producción: ```bash sudo mysql perfdemo ``` ```sql SET SESSION long_query_time = 0; SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10; SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2026; SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 400000; ``` En otra terminal, mira el final del log: ```bash sudo tail -n 6 /var/log/mysql/mysql-slow.log ``` Cada entrada muestra el tiempo empleado y, sobre todo, `Rows_examined` frente a `Rows_sent`: ```output # Time: 2026-09-25T10:15:32.123456Z # User@Host: root[root] @ localhost [] Id: 12 # Query_time: 0.214530 Lock_time: 0.000004 Rows_sent: 10 Rows_examined: 500010 SET timestamp=1790331332; SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10; ``` Leer 500.010 filas para devolver 10 es la firma de un índice que falta. En un servidor real el log acumula miles de entradas enseguida. Agrúpalas por forma de consulta con `pt-query-digest`, del Percona Toolkit, que está empaquetado en Ubuntu: ```bash sudo apt install percona-toolkit sudo pt-query-digest /var/log/mysql/mysql-slow.log > ~/slow-report.txt ``` Abre `~/slow-report.txt`. La sección `Profile` del principio ordena las consultas por tiempo de respuesta total, que es el orden correcto para trabajar: una consulta de 50 ms que se ejecuta 100.000 veces al día cuesta más que un informe de 5 segundos que se ejecuta una vez. Debajo, cada consulta tiene su propio bloque con el número de llamadas, la distribución de tiempos, las filas examinadas y una sentencia de muestra. Si no puedes instalar paquetes adicionales, `mysqldumpslow` viene con MySQL y ofrece un resumen más sencillo ordenado por tiempo total: ```bash sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log ``` ### Usar Performance Schema en lugar del log MySQL 8.0 también agrega estadísticas de cada sentencia normalizada en Performance Schema, haya superado o no `long_query_time`. El esquema `sys` las presenta de forma legible: ```sql SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg FROM sys.statement_analysis WHERE db = 'perfdemo' ORDER BY total_latency DESC LIMIT 5; ``` Una gran diferencia entre `rows_examined_avg` y `rows_sent_avg` indica el mismo problema que el slow log. La vista `sys.statements_with_full_table_scans` lista solo las sentencias que no usaron ningún índice. ## Paso 4: Leer el plan de ejecución con EXPLAIN `EXPLAIN` muestra cómo planea MySQL ejecutar una consulta sin ejecutarla. Lánzalo sobre la primera consulta: ```sql EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10; ``` ```output +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 498700 | 10.00 | Using where; Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ ``` Las columnas más importantes: | Columna | Qué buscar | |---|---| | `type` | `ALL` es un recorrido completo de la tabla. `index` recorre un índice entero. `range`, `ref`, `eq_ref` y `const` son cada vez mejores. | | `key` | El índice que realmente se usa. `NULL` significa ninguno. | | `rows` | Filas que estima examinar. Compáralo con las filas que devuelve la consulta. | | `Extra` | `Using filesort` indica un paso de ordenación adicional. `Using temporary`, una tabla temporal interna. `Using index`, que el índice por sí solo respondió la consulta. | `EXPLAIN ANALYZE` ejecuta la consulta e informa de tiempos y filas reales en cada paso del plan. Úsalo cuando las estimaciones y la realidad puedan diferir: ```sql EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10\G ``` La salida es un árbol que se lee desde la línea más interna hacia fuera. En esta consulta verás un `Table scan on orders` que lee las 500.000 filas, un `Filter` que se queda con unas diez y un `Sort` encima. Como `EXPLAIN ANALYZE` ejecuta la sentencia, no lo uses nunca con un `UPDATE` o `DELETE` que no quieras aplicar. ## Paso 5: Añadir el índice adecuado La consulta filtra por `customer_id` y ordena por `created_at`. Un índice compuesto sobre ambas columnas, en ese orden, permite a MySQL saltar directamente a las filas del cliente y leerlas ya ordenadas: ```sql CREATE INDEX idx_customer_created ON orders (customer_id, created_at); ``` El orden de las columnas importa: primero las que se comparan con `=` y después la que se usa en un rango o en el `ORDER BY`. Un índice sobre `(created_at, customer_id)` apenas ayudaría a esta consulta. Vuelve a comprobar el plan: ```sql EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10; ``` ```output +----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+ | 1 | SIMPLE | orders | NULL | ref | idx_customer_created | idx_customer_created | 4 | const | 10 | 100.00 | Backward index scan | +----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+ ``` El recorrido completo ha desaparecido (`type` es `ref`), MySQL estima 10 filas en lugar de unas 500.000 y el filesort ya no está porque el índice devuelve las filas en orden. Ejecuta de nuevo la consulta y el tiempo baja de cientos de milisegundos a alrededor de un milisegundo. Antes de llenar la base de datos de índices, ten en cuenta el coste: cada índice ralentiza los `INSERT`, `UPDATE` y `DELETE` de la tabla y ocupa disco y memoria. Crea índices para las consultas que el ranking del paso 3 señala como caras y elimina los que nadie usa: ```sql SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'perfdemo'; SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'perfdemo'; ``` `schema_unused_indexes` se basa en estadísticas recogidas desde el último reinicio, así que fíate de ella solo en un servidor que lleve un tiempo con su carga de trabajo normal. ## Paso 6: Reescribir consultas que no pueden usar un índice Algunas consultas siguen siendo lentas aunque exista un índice, por la forma en que están escritas. ### Funciones sobre columnas indexadas La segunda consulta envuelve la columna en una función: ```sql SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2026; ``` MySQL no puede usar un índice sobre `created_at` en este caso, porque tendría que calcular `YEAR()` en cada fila. Añade un índice sobre la columna y reescribe la condición como un rango: ```sql CREATE INDEX idx_created ON orders (created_at); SELECT COUNT(*) FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'; ``` `EXPLAIN` muestra ahora `type: range` con `Using index`. La misma regla se aplica a `DATE(col) = ...`, `LOWER(col) = ...` y a operaciones como `col + 1 = 10`. Si no puedes cambiar la consulta, MySQL 8.0 admite índices funcionales, por ejemplo `CREATE INDEX idx_email_lower ON users ((LOWER(email)));`. ### Comodines al principio `WHERE email LIKE '%@example.com'` no puede usar un índice B-tree porque el valor no tiene un prefijo conocido. `LIKE 'john%'` sí puede. Para búsquedas por sufijo o de texto libre, guarda una columna aparte (por ejemplo el dominio del correo) e indéxala, o usa un índice `FULLTEXT`. ### Paginación con OFFSET profundo La tercera consulta pide la página 40.001 de 10 filas: ```sql SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 400000; ``` MySQL tiene que leer y descartar 400.000 filas para encontrar las diez que quieres, y cada página es más lenta que la anterior. Usa paginación por clave (keyset): recuerda el último `id` de la página anterior y continúa desde él: ```sql SELECT * FROM orders WHERE id > 400000 ORDER BY id LIMIT 10; ``` Así se leen exactamente diez filas a través de la clave primaria, sin importar la profundidad de la página. ### Seleccionar solo lo necesario `SELECT *` obliga a MySQL a leer todas las columnas de la fila, aunque un índice ya contenga las que necesitas. Cuando una consulta solo necesita columnas indexadas, el índice puede responderla por sí solo (un índice de cobertura, que aparece como `Using index` en `Extra`): ```sql EXPLAIN SELECT customer_id, created_at FROM orders WHERE customer_id = 4242; ``` ## Paso 7: Mantener las estadísticas al día El optimizador elige los planes a partir de las estadísticas de la tabla. InnoDB las recalcula automáticamente cuando cambia alrededor del 10 % de la tabla, pero tras cargas masivas o borrados grandes las estimaciones de `EXPLAIN` pueden alejarse mucho de la realidad. Actualízalas a mano: ```sql ANALYZE TABLE orders; ``` ```output +-----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------+---------+----------+----------+ | perfdemo.orders | analyze | status | OK | +-----------------+---------+----------+----------+ ``` Si un plan sigue siendo malo después de `ANALYZE TABLE`, compara las estimaciones de `EXPLAIN` con las filas reales de `EXPLAIN ANALYZE` para encontrar el paso en el que divergen. ## Encontrar consultas lentas en PostgreSQL El método es el mismo en PostgreSQL; solo cambian las herramientas. ### Registrar las sentencias lentas Registra cada sentencia que tarde más de 500 ms. `ALTER SYSTEM` escribe el ajuste en `postgresql.auto.conf` y una recarga lo aplica: ```bash sudo -u postgres psql -c "ALTER SYSTEM SET log_min_duration_statement = '500ms';" sudo -u postgres psql -c "SELECT pg_reload_conf();" ``` Las sentencias lentas aparecen en `/var/log/postgresql/postgresql-16-main.log` con una línea como `LOG: duration: 812.345 ms statement: SELECT ...`. ### Ordenar consultas con pg_stat_statements `pg_stat_statements` es el equivalente en PostgreSQL de las tablas de digests de Performance Schema. Viene incluido en los paquetes de PostgreSQL de Ubuntu, pero hay que cargarlo al arrancar. Edita el archivo de configuración principal: ```bash sudo nano /etc/postgresql/16/main/postgresql.conf ``` Busca la línea `shared_preload_libraries`, descoméntala y déjala así (si ya carga otras bibliotecas, añade esta separada por una coma): ```ini shared_preload_libraries = 'pg_stat_statements' ``` Reinicia PostgreSQL y crea la extensión en la base de datos que quieres analizar: ```bash sudo systemctl restart postgresql sudo -u postgres psql -d your_database -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" ``` Abre una sesión con `sudo -u postgres psql -d your_database` y lista las sentencias con mayor tiempo de ejecución total: ```sql SELECT calls, round(total_exec_time::numeric, 1) AS total_ms, round(mean_exec_time::numeric, 1) AS mean_ms, rows, left(query, 80) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``` Después de aplicar una corrección, pon los contadores a cero con `SELECT pg_stat_statements_reset();` para medir la nueva referencia. ### Leer los planes de PostgreSQL Usa `EXPLAIN (ANALYZE, BUFFERS)` para ejecutar la consulta y ver los tiempos reales y cuántas páginas vinieron de la caché (`shared hit`) o del disco (`read`): ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10; ``` Sobre una tabla como la `orders` del ejemplo, busca `Seq Scan` en tablas grandes, una gran diferencia entre las filas estimadas (`rows=`) y las reales (`actual ... rows=`), y `Sort Method: external merge Disk`, que indica que la ordenación no cupo en `work_mem`. La corrección del índice es la misma que en MySQL: `CREATE INDEX CONCURRENTLY idx_customer_created ON orders (customer_id, created_at);`. `CONCURRENTLY` construye el índice sin bloquear las escrituras. Actualiza las estadísticas con `ANALYZE orders;`. ## Solución de problemas - **El archivo del slow log sigue vacío.** Comprueba que `slow_query_log` está en `ON` y que el usuario `mysql` puede escribir en la ruta. En Ubuntu, AppArmor solo permite a MySQL escribir logs dentro de `/var/log/mysql/`, así que deja el archivo ahí. - **MySQL ignora un índice nuevo.** El optimizador puede estimar que un recorrido completo es más barato, y tiene razón cuando la condición coincide con una gran parte de la tabla (por ejemplo `status = 'paid'` en un tercio de las filas). Ejecuta `ANALYZE TABLE` y compara con `EXPLAIN ANALYZE`. Las columnas con poca selectividad rara vez se benefician de un índice propio. - **Las consultas son lentas pero examinan pocas filas.** El tiempo se va en esperas, no en lecturas. Revisa `Lock_time` en el slow log y busca transacciones que bloquean con `SELECT * FROM sys.innodb_lock_waits;`. - **CPU alta con muchas consultas cortas.** Ordena por `exec_count` en `sys.statement_analysis` en lugar de por latencia. Un patrón N+1 en la aplicación (una consulta por cada fila de una lista) es una causa habitual, y la corrección está en el código de la aplicación. ## Conclusión Has activado el registro de consultas lentas, has ordenado las consultas por coste total, has leído planes de ejecución y has corregido las tres causas más habituales de lentitud: índices compuestos que faltan, funciones sobre columnas indexadas y paginación con `OFFSET` profundo. Repite el ciclo después de cada despliegue, porque el código nuevo trae formas de consulta nuevas. Como siguientes pasos, comprueba que la rotación de logs cubre `/var/log/mysql/mysql-slow.log`, revisa `innodb_buffer_pool_size` para que los datos de trabajo quepan en memoria y sigue la latencia de las consultas a lo largo del tiempo en tu sistema de monitorización.