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:

sudo mysql

Crea una base de datos y una tabla orders con clave primaria y ningún otro índice:

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:

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:

SELECT COUNT(*) FROM orders;

+----------+
| 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:

sudo nano /etc/mysql/mysql.conf.d/slow-query.cnf

[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:

sudo systemctl restart mysql

Comprueba los ajustes:

sudo mysql -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('slow_query_log', 'slow_query_log_file', 'long_query_time');"

+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| long_query_time     | 1.000000                      |
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+

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:

sudo mysql perfdemo

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:

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:

# 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:

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:

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:

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:

EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 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:

ColumnaQué buscar
typeALL es un recorrido completo de la tabla. index recorre un índice entero. range, ref, eq_ref y const son cada vez mejores.
keyEl índice que realmente se usa. NULL significa ninguno.
rowsFilas que estima examinar. Compáralo con las filas que devuelve la consulta.
ExtraUsing 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:

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:

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:

EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;

+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+
| 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:

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:

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:

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:

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:

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):

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:

ANALYZE TABLE orders;

+-----------------+---------+----------+----------+
| 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:

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:

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):

shared_preload_libraries = 'pg_stat_statements'

Reinicia PostgreSQL y crea la extensión en la base de datos que quieres analizar:

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:

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):

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.