--- title: 'Optimización de consultas de bases de datos: clase maestra | DevSense' description: 'Aprenda a leer EXPLAIN y EXPLAIN ANALYZE, optimizar JOINs y CTEs, implementar paginación keyset y comprender los comportamientos de carga MVCC del motor de base de datos.' faq: - { question: '¿Cuál es la diferencia entre un escaneo secuencial (Sequential Scan) y un escaneo de índice (Index Scan)?', answer: 'Un escaneo secuencial lee cada página de la tabla de principio a fin para encontrar las filas coincidentes. Un escaneo de índice recorre el árbol del índice para ubicar las entradas coincidentes y luego extrae solo las páginas correspondientes del Heap. Para tablas pequeñas o consultas que recuperan un gran porcentaje de filas, Seq Scan suele ser más rápido que las E/S aleatorias de un Index Scan.' } - { question: '¿Qué son Nested Loops, Hash Joins y Merge Joins?', answer: 'Los Nested Loops unen tablas escaneando la tabla externa y buscando filas coincidentes en la tabla interna (óptimo para conjuntos pequeños). Los Hash Joins construyen una tabla hash en memoria a partir de la relación más pequeña y escanean la relación más grande para encontrar coincidencias (óptimo para grandes conjuntos no ordenados). Los Merge Joins ordenan ambas relaciones en la clave de unión y las escanean en paralelo (óptimo para grandes conjuntos preordenados o cuando los escaneos de índices pueden evitar la ordenación).' } - { question: '¿Por qué la paginación keyset es más rápida que LIMIT/OFFSET?', answer: 'Las consultas LIMIT/OFFSET deben leer y descartar todas las filas hasta el valor de desplazamiento (offset), lo que provoca una degradación del rendimiento de O(N) a medida que las páginas se vuelven más profundas. La paginación keyset utiliza un filtro WHERE en una columna única ordenada (como `WHERE id > ?`) para saltar directamente a la fila de destino utilizando un índice, logrando una búsqueda en tiempo constante O(log N).' } - { question: '¿Qué es una barrera de optimización (optimization fence) de CTE?', answer: 'In versiones anteriores de Postgres (anteriores a la 12), y opcionalmente en las más nuevas usando `MATERIALIZED`, las expresiones de tabla comunes (CTEs) actúan como una barrera de optimización donde la base de datos ejecuta la CTE primero, guarda el resultado en un espacio temporal y luego realiza la unión. Esto evita que el optimizador inserte predicados de la consulta externa (como cláusulas WHERE) dentro de la CTE (push-down), lo que puede provocar una ejecución lenta.' } published: '2026-05-31' --- # Optimización de consultas de bases de datos: clase maestra Una sola consulta lenta puede derribar una aplicación empresarial completa. En producción, el rendimiento de la base de datos rara vez se trata de la potencia de la CPU; se trata de la eficiencia con la que el planificador de consultas navega por las páginas del disco, construye tablas de unión en memoria y maneja la concurrencia. Cuando escribes una consulta SQL, estás describiendo *qué* datos deseas, no *cómo* obtenerlos. El optimizador de consultas del motor de la base de datos es responsable de trazar el plan de ejecución. Para escribir aplicaciones de alto rendimiento, debes aprender a pensar como el optimizador. En esta clase maestra, examinaremos planes de ejecución, diseccionaremos algoritmos de unión, exploraremos la escalabilidad de la paginación y analizaremos la mecánica MVCC de PostgreSQL y MySQL. --- ## 1. Decodificación de EXPLAIN y EXPLAIN ANALYZE Para optimizar una consulta, primero debes inspeccionar su plan de ejecución usando `EXPLAIN`. Sin embargo, un `EXPLAIN` estándar solo muestra la *estimación* del optimizador sobre el costo de la consulta. Para ver lo que realmente sucedió durante la ejecución, debes usar `EXPLAIN ANALYZE` (que realmente ejecuta la consulta). ### Comprender los indicadores de costo (formato de Postgres) Un plan de ejecución muestra los costos en el formato: `cost=0.00..431.25 rows=10500 width=244`. * **Costo de inicio (`0.00`):** El costo incurrido antes de que se pueda devolver la primera fila (por ejemplo, construir una tabla hash o clasificar nodos de índice). * **Costo total (`431.25`):** El costo estimado para devolver todas las filas. Esto se mide en unidades arbitrarias de lecturas de páginas (típicamente `1.0` para lectura de página secuencial, `4.0` para lectura de página aleatoria). * **Filas (`10500`):** El número estimado de filas devueltas (rows). * **Ancho (`244`):** El tamaño promedio de las filas devueltas en bytes (width). ### Tipos de escaneo de nodos 1. **Sequential Scan (Seq Scan):** El motor lee la tabla completa de principio a fin. Es óptimo para tablas pequeñas o al recuperar el $> 20\%$ de las filas de la tabla. 2. **Index Scan:** El motor recorre el árbol B del índice, recupera los TIDs/PKs coincidentes y luego recupera las páginas correspondientes del Heap/tabla. Esto implica acceso aleatorio al disco. 3. **Index Only Scan:** Si todas las columnas seleccionadas están en el índice mismo, la base de datos omite por completo las lecturas de la tabla. 4. **Bitmap Index Scan & Bitmap Heap Scan (Postgres):** Al recuperar múltiples filas coincidentes a través de un índice, Postgres primero construye un mapa de bits de las páginas coincidentes en memoria (Bitmap Index Scan) y luego lee esas páginas en orden físico secuencial (Bitmap Heap Scan). Esto convierte las E/S aleatorias lentas en E/S secuenciales más rápidas. ### Algoritmos de unión (Join) * **Nested Loop:** La base de datos escanea la tabla externa y, para cada fila, busca filas coincidentes en la tabla interna. Esto es extremadamente rápido para conjuntos de datos pequeños, especialmente si la columna de unión de la tabla interna está indexada. * **Hash Join:** La base de datos escanea la tabla más pequeña, construye una tabla hash en memoria a partir de las claves de unión y luego escanea la tabla más grande, aplicando hash a sus claves para encontrar coincidencias inmediatas. Es óptimo para conjuntos de datos grandes y no ordenados. * **Merge Join:** Ambas tablas se ordenan por sus claves de unión y la base de datos las escanea en paralelo, fusionando las filas coincidentes. Esto es muy eficiente para conjuntos de datos muy grandes, particularmente si ya están preordenados por índices. --- ## 2. Optimización avanzada de uniones y CTEs No todos los joins se crean de la misma manera. La forma en que escribes las subconsultas y las CTEs afecta en gran medida la capacidad del optimizador para podar rutas de ejecución. ### Inner vs. Outer Joins y Anti-Joins Un anti-join busca registros en una tabla que *no* existen en otra. Los desarrolladores a menudo escriben esto usando `NOT IN`, lo que tiene un rendimiento terrible y falla si la subconsulta devuelve `NULL`. Un enfoque altamente optimizado es usar `NOT EXISTS`. ```sql -- database/queries/anti_join_bad.sql -- Malo: NOT IN escanea la subconsulta y se comporta de manera inesperada si hay NULLs presentes SELECT id, name FROM users WHERE id NOT IN (SELECT user_id FROM orders); -- database/queries/anti_join_good.sql -- Bueno: NOT EXISTS se traduce en un Hash Anti Join o Merge Anti Join altamente optimizado SELECT u.id, u.name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id ); ``` ### Uniones LATERAL (Subconsultas correlacionadas) Una unión `LATERAL` actúa como un bucle `foreach` de SQL. Permite que una subconsulta haga referencia a columnas de tablas anteriores en la cláusula `FROM`. Esto es increíblemente útil para obtener los \"Top N\" registros por grupo. ```sql -- database/queries/lateral_join.sql -- Obtener las últimas 3 órdenes para cada usuario (Postgres y MySQL 8.0.14+) SELECT u.id, u.name, recent_orders.id AS order_id, recent_orders.amount, recent_orders.created_at FROM users u CROSS JOIN LATERAL ( SELECT o.id, o.amount, o.created_at FROM orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 3 ) AS recent_orders; ``` ### Barreras de optimización de CTE Las expresiones de tabla comunes (CTEs) hacen que el código de la consulta sea limpio, pero históricamente actuaron como **barreras de optimización**. * **Postgres (anterior a 12):** Las CTEs siempre se materializaban (se calculaban y escribían en un espacio temporal) antes de ejecutar la consulta externa. Esto evitaba el uso de índices de las cláusulas WHERE externas. * **Postgres (12+):** Las CTEs ahora se integran (inline) por defecto a menos que sean recursivas o tengan efectos secundarios. Puedes forzar o evitar explícitamente la materialización usando `MATERIALIZED` o `NOT MATERIALIZED`. ```sql -- database/queries/cte_fence.sql -- Evitar la materialización para permitir que el planificador inserte el predicado user_id hacia abajo WITH user_stats AS NOT MATERIALIZED ( SELECT user_id, COUNT(*) as total_orders, SUM(amount) as total_spent FROM orders GROUP BY user_id ) SELECT u.name, s.total_orders, s.total_spent FROM users u JOIN user_stats s ON u.id = s.user_id WHERE u.id = 42; ``` --- ## 3. Escalado de rutas de consulta: paginación keyset y particionado A medida que las tablas de tu base de datos crecen a miles de millones de filas, las consultas simples se degradarán a menos que cambies la forma en que escaneas las rutas. ### Paginación Keyset (basada en cursor) frente a LIMIT / OFFSET La paginación offset (`LIMIT 10 OFFSET 100000`) obliga a la base de datos a leer y descartar 100,000 filas solo para devolver 10. El rendimiento escala linealmente ($O(N)$), degradándose fuertemente en páginas profundas. La paginación keyset filtra las filas vistas anteriormente usando una cláusula WHERE en un índice ordenado, escalando a una búsqueda en tiempo constante de $O(\log N)$. ```sql -- database/queries/offset_pagination_bad.sql -- Malo: Escanea y descarta 100,000 registros antes de devolver 10 SELECT id, amount, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 100000; -- database/queries/keyset_pagination_good.sql -- Bueno: Salta directamente al último keyset visto usando un escaneo de índice compuesto SELECT id, amount, created_at FROM orders WHERE (created_at, id) < ('2026-05-30 15:30:00', 987654) ORDER BY created_at DESC, id DESC LIMIT 10; ``` > [!NOTE] > La paginación keyset requiere un ordenamiento determinista. Si ordenas por una columna no única como `created_at`, debes agregar una columna única de desempate como `id` para evitar filas duplicadas u omitidas. ### Particionado de tablas El particionado de tablas divide una tabla grande en tablas físicas más pequeñas (particiones) basadas en una clave (por ejemplo, rangos de fechas), mientras mantiene una única interfaz lógica. * **Poda de consultas (Query Pruning):** Cuando una consulta filtra por la clave de partición, el planificador ignora por completo las particiones irrelevantes, escaneando solo la partición coincidente. ```sql -- database/migrations/2026_05_31_partitioned_orders.sql -- Particionado de rango declarativo en PostgreSQL CREATE TABLE orders_partitioned ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (created_at, id) ) PARTITION BY RANGE (created_at); -- Crear tablas de particiones individuales CREATE TABLE orders_2026_q1 PARTITION OF orders_partitioned FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2026-04-01 00:00:00'); ``` --- ## 4. MVCC, Vacuuming y retraso de purga (Purge Lag) La concurrencia en las bases de datos modernas se logra mediante el control de concurrencia de versiones múltiples o **Multi-Version Concurrency Control (MVCC)**. En lugar de bloquear filas para lectura, las bases de datos mantienen múltiples versiones de una fila de forma concurrente. ### PostgreSQL MVCC y Autovacuum En Postgres, cada fila (tupla) se almacena en el disco con encabezados de metadatos que incluyen `xmin` (el ID de transacción que creó la fila) y `xmax` (el ID de transacción que eliminó/expiró la fila). * **Actualizaciones:** Un `UPDATE` no sobrescribe la fila. Escribe una tupla completamente nueva en el Heap y establece `xmax` en la tupla antigua para apuntar a la transacción de actualización. * **Bloat (Inflado):** La versión antigua de la fila se convierte en una \"tupla muerta\" (dead tuple) una vez que ninguna transacción activa puede verla. * **Vacuuming:** Postgres requiere `VACUUM` (administrado por el demonio autovacuum) para escanear páginas, reclamar el espacio ocupado por tuplas muertas y actualizar el mapa de visibilidad. Si autovacuum no puede mantener el ritmo de las cargas de trabajo de escritura pesadas, el inflado de tablas e índices degradará el rendimiento de las consultas. ### MySQL InnoDB MVCC y Undo Logs El motor InnoDB de MySQL maneja MVCC de manera diferente. * **Actualizaciones en índice agrupado:** InnoDB realiza actualizaciones en el lugar (in-place) dentro del nodo hoja del índice agrupado. * **Undo Logs y segmentos de rollback:** La versión antigua de la fila no se almacena en la tabla principal. En su lugar, InnoDB escribe el estado histórico de la fila en el **Undo Log** y almacena un puntero de rollback dentro de la fila actualizada. Cuando una transacción lectora necesita una versión más antigua, lee la fila actual y reconstruye el estado antiguo usando los registros de deshacer (undo logs). * **Hilos de purga (Purge Threads):** A medida que finalizan las transacciones antiguas, un proceso en segundo plano llamado **Purge Thread** limpia los registros de deshacer y elimina los registros que se marcaron para eliminación. Bajo cargas de trabajo de escritura pesadas, puede ocurrir un retraso de purga o **Purge Lag**, lo que lleva a un crecimiento masivo del archivo de registros de deshacer y a la degradación del rendimiento. --- ## 5. Errores comunes ### Error 1: Paginación profunda con OFFSET **Mala práctica:** ```sql -- database/queries/bad_pagination.sql SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 50000; ``` *Por qué es malo:* La base de datos debe escanear el índice/heap para 50,000 registros, cargarlos en memoria y descartarlos, consumiendo CPU y E/S de disco. **Buena práctica:** ```sql -- database/queries/good_pagination.sql -- Paginación keyset usando el último ID visto (por ejemplo, 50000) SELECT * FROM users WHERE id > 50000 ORDER BY id LIMIT 20; ``` *Por qué es bueno:* La base de datos realiza una búsqueda de índice para saltar directamente a `id > 50000` en tiempo $O(\log N)$, evitando escaneos de filas innecesarios. ### Error 2: Falta de índice en claves foráneas **Mala práctica:** ```sql -- database/migrations/bad_foreign_key.sql CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT, FOREIGN KEY (user_id) REFERENCES users(id) ); ``` *Por qué es malo:* PostgreSQL y MySQL no crean automáticamente índices en las columnas de claves foráneas. Si une tablas con frecuencia en `user_id` o elimina usuarios (lo que desencadena comprobaciones en cascada), el motor debe realizar un escaneo secuencial en la tabla hija. --- ## 6. Cuestionario de autoevaluación ### Pregunta 1 Durante una ejecución de `EXPLAIN ANALYZE` en Postgres, observa un nodo etiquetado como `Bitmap Heap Scan` después de un `Bitmap Index Scan`. ¿Qué indica esto?
Mostrar respuestas **Respuesta:** Indica que Postgres decidió que leer filas directamente a través de búsquedas de índice individuales daría como resultado una E/S aleatoria demasiado lenta. En su lugar: 1. El `Bitmap Index Scan` escaneó el índice y construyó un mapa de bits en memoria de las direcciones de las páginas de la tabla que contienen filas coincidentes. 2. El `Bitmap Heap Scan` luego leyó las páginas de la tabla en orden físico secuencial, accediendo a las filas coincidentes en esas páginas. Esto convierte las E/S aleatorias en lecturas de disco secuenciales más rápidas.
--- ### Pregunta 2 Bajo una carga de escritura pesada en MySQL InnoDB, ¿por qué aumenta el tamaño del espacio de tablas del registro de deshacer (undo log), y cómo afecta esto a las consultas de lectura que necesitan datos más antiguos?
Mostrar respuestas **Respuesta:** Cuando las escrituras son muy intensas, el Purge Thread puede retrasarse (Purge Lag) en la limpieza de los segmentos de rollback históricos. En consecuencia, el espacio del undo log crece rápidamente para realizar un seguimiento de las versiones de fila anteriores. Las transacciones de lectura más antiguas experimentarán un rendimiento más lento porque deben recorrer una larga cadena de registros de deshacer para reconstruir el estado de los datos a partir del momento de inicio de su transacción.
--- ### Pregunta 3 ¿Por qué una CTE definida con una cláusula `WITH` estándar en Postgres 10 puede causar un cuello de botella en el rendimiento si la consulta externa filtra por un ID específico?
Mostrar respuestas **Respuesta:** En Postgres 10 (y versiones anteriores), las CTEs actúan como una barrera de optimización. El motor evalúa la CTE de forma independiente y materializa sus resultados completos en memoria o disco. Solo entonces ejecuta la consulta externa y aplica el filtro `WHERE id = 42`. En Postgres 12+ (o al escribir `WITH ... AS NOT MATERIALIZED`), el optimizador puede incorporar en línea (inline) la CTE, lo que le permite insertar el filtro `WHERE id = 42` en la subconsulta, habilitando escaneos basados en índices.