Tutorial SQL: Optimización de consultas

Introducción teórica a planes de ejecución

Cuando ejecutamos una consulta SQL, el motor de base de datos no simplemente procesa la instrucción de forma literal. En realidad, realiza un complejo proceso de análisis, planificación y ejecución para determinar la forma más eficiente de obtener los resultados solicitados. Este proceso interno es lo que conocemos como plan de ejecución.

Un plan de ejecución es la representación detallada de la estrategia que el motor de base de datos ha decidido utilizar para resolver una consulta SQL. Funciona como un mapa de ruta que muestra exactamente cómo se accederá a los datos, qué operaciones se realizarán sobre ellos y en qué orden. Comprender estos planes es fundamental para optimizar el rendimiento de nuestras consultas, especialmente cuando trabajamos con bases de datos de gran tamaño.

Componentes de un plan de ejecución

Los planes de ejecución están compuestos por varios elementos que representan las diferentes operaciones que el motor de base de datos realizará:

• Operaciones de acceso a datos: Indican cómo se recuperan los datos de las tablas (escaneo completo, uso de índices, etc.)
• Operaciones de unión: Muestran cómo se combinan los datos de múltiples tablas
• Operaciones de filtrado: Representan cómo se aplican las condiciones WHERE
• Operaciones de ordenación: Indican cómo se ordenan los resultados
• Operaciones de agrupación: Muestran cómo se agrupan los datos

Cada uno de estos componentes tiene un coste asociado, que representa una estimación de los recursos computacionales necesarios para ejecutar esa operación.

El optimizador de consultas

El cerebro detrás de la generación de planes de ejecución es el optimizador de consultas. Este componente del motor de base de datos es responsable de:

1. Analizar la consulta SQL y verificar su sintaxis
2. Evaluar diferentes estrategias posibles para ejecutar la consulta
3. Seleccionar el plan que estima será más eficiente

Para tomar estas decisiones, el optimizador utiliza:

• Estadísticas de la base de datos: Información sobre la distribución de datos, cardinalidad (número de filas), etc.
• Metadatos: Información sobre la estructura de las tablas, índices disponibles, etc.
• Reglas heurísticas: Principios generales sobre qué estrategias suelen ser más eficientes
• Modelos de coste: Fórmulas que estiman el coste computacional de cada operación

Tipos de planes de ejecución

Existen dos tipos principales de planes de ejecución:

• Planes estimados: Son generados antes de ejecutar la consulta, basándose en estadísticas y heurísticas. Muestran lo que el optimizador "piensa" que será la mejor estrategia.
• Planes reales: Se generan durante la ejecución real de la consulta y reflejan lo que realmente ocurrió, incluyendo estadísticas de ejecución como tiempo consumido y número de filas procesadas.

La diferencia entre ambos puede ser significativa, especialmente si las estadísticas de la base de datos no están actualizadas o si existen particularidades en los datos que el optimizador no puede predecir.

Operaciones comunes en planes de ejecución

Al analizar un plan de ejecución, encontraremos diversas operaciones que representan las estrategias utilizadas por el motor de base de datos:

• Table Scan/Full Table Scan: Lectura secuencial de todos los registros de una tabla. Es eficiente para tablas pequeñas, pero costoso para tablas grandes.

-- Consulta que probablemente generará un Table Scan
SELECT * FROM clientes WHERE nombre LIKE '%Juan%';

• Index Scan: Utiliza un índice para localizar registros específicos. Es más eficiente que un Table Scan para consultas selectivas.

-- Consulta que probablemente utilizará un Index Scan
SELECT * FROM clientes WHERE id_cliente = 1234;

• Index Seek: Busca valores específicos en un índice. Es generalmente la operación más eficiente para localizar registros.

-- Consulta que probablemente utilizará un Index Seek
SELECT * FROM pedidos WHERE fecha_pedido = '2023-01-15';

• Nested Loop Join: Une registros de dos tablas comparando cada fila de la primera tabla con cada fila de la segunda. Eficiente cuando una de las tablas es pequeña.

-- Ejemplo de consulta que podría usar Nested Loop Join
SELECT c.nombre, p.producto 
FROM clientes c JOIN pedidos p ON c.id_cliente = p.id_cliente
WHERE c.ciudad = 'Madrid';

• Hash Join: Crea una tabla hash en memoria para una de las tablas y luego compara cada fila de la otra tabla con esta tabla hash. Eficiente para conjuntos de datos medianos a grandes.

-- Ejemplo de consulta que podría usar Hash Join
SELECT c.nombre, p.producto 
FROM clientes c JOIN pedidos p ON c.id_cliente = p.id_cliente;

• Merge Join: Ordena ambas tablas por la columna de unión y luego las recorre en paralelo. Eficiente cuando ambas tablas ya están ordenadas por la columna de unión.

-- Ejemplo de consulta que podría usar Merge Join
SELECT e.nombre, d.nombre_departamento 
FROM empleados e JOIN departamentos d ON e.id_departamento = d.id_departamento
ORDER BY e.id_departamento;

Diferencias entre MySQL y PostgreSQL

Aunque los conceptos fundamentales son similares, existen diferencias importantes en cómo MySQL y PostgreSQL implementan y visualizan los planes de ejecución:

• MySQL utiliza un optimizador basado principalmente en costes y presenta los planes en formato tabular o de árbol, dependiendo de la herramienta utilizada.

• PostgreSQL ofrece un optimizador más sofisticado y presenta los planes en formato de árbol con información detallada sobre cada nodo de operación, incluyendo costes estimados, número de filas y ancho de cada fila.

-- Ejemplo en PostgreSQL
EXPLAIN SELECT * FROM clientes WHERE ciudad = 'Barcelona';

-- Posible resultado:
-- Seq Scan on clientes  (cost=0.00..35.50 rows=15 width=180)
--   Filter: (ciudad = 'Barcelona'::text)

-- Ejemplo en MySQL
EXPLAIN SELECT * FROM clientes WHERE ciudad = 'Barcelona';

-- Posible resultado:
-- id | select_type | table    | type | key  | rows | Extra
-- 1  | SIMPLE      | clientes | ALL  | NULL | 1000 | Using where

Factores que influyen en los planes de ejecución

Varios factores pueden afectar cómo el optimizador genera los planes de ejecución:

• Estadísticas de la base de datos: Si están desactualizadas, pueden llevar a planes subóptimos.
• Configuración del servidor: Parámetros como el tamaño de buffer o la memoria disponible.
• Estructura de la consulta: La forma en que escribimos nuestras consultas puede influir significativamente.
• Índices disponibles: La presencia o ausencia de índices adecuados es quizás el factor más determinante.
• Cardinalidad de las tablas: El número de filas y su distribución.

Interpretación básica de planes de ejecución

Al analizar un plan de ejecución, debemos prestar atención a:

• Operaciones de alto coste: Identificar qué partes de la consulta consumen más recursos.
• Escaneos de tabla completos: Especialmente en tablas grandes, suelen indicar falta de índices adecuados.
• Estimaciones incorrectas: Si el optimizador estima un número de filas muy diferente al real, puede indicar estadísticas desactualizadas.
• Uso de índices: Verificar si se están utilizando los índices esperados.

-- Ejemplo de consulta con potencial problema de rendimiento
SELECT c.nombre, SUM(p.monto) 
FROM clientes c JOIN pedidos p ON c.id_cliente = p.id_cliente
WHERE p.fecha BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY c.nombre;

En este ejemplo, si no hay un índice en la columna fecha de la tabla pedidos, podríamos ver un escaneo completo de la tabla, lo que sería ineficiente para una tabla grande.

Limitaciones de los planes de ejecución

Es importante entender que los planes de ejecución tienen ciertas limitaciones:

• Basados en estimaciones: El plan real puede diferir si las estadísticas no son precisas.
• Dependientes del estado actual: Cambios en los datos pueden hacer que un plan óptimo hoy sea subóptimo mañana.
• No consideran factores externos: Como la carga del servidor o la contención de recursos.
• Específicos del motor: Los planes pueden variar significativamente entre diferentes motores de base de datos e incluso entre versiones del mismo motor.

Casos de uso para el análisis de planes de ejecución

El análisis de planes de ejecución es especialmente útil en los siguientes escenarios:

• Diagnóstico de consultas lentas: Identificar por qué una consulta está tomando más tiempo del esperado.
• Optimización proactiva: Verificar que las consultas críticas utilizan estrategias eficientes antes de implementarlas en producción.
• Validación de índices: Comprobar si los índices creados están siendo utilizados como se esperaba.
• Educación y aprendizaje: Entender cómo el motor de base de datos interpreta y ejecuta nuestras consultas SQL.

-- Ejemplo de consulta compleja que se beneficiaría del análisis de su plan
SELECT d.nombre_departamento, COUNT(e.id_empleado) as num_empleados, 
       AVG(e.salario) as salario_promedio
FROM departamentos d 
LEFT JOIN empleados e ON d.id_departamento = e.id_departamento
WHERE e.fecha_contratacion > '2020-01-01' OR e.id_empleado IS NULL
GROUP BY d.nombre_departamento
HAVING COUNT(e.id_empleado) > 5
ORDER BY salario_promedio DESC;

Esta consulta compleja involucra múltiples operaciones (join, filtrado, agrupación, ordenación) y su rendimiento podría variar significativamente dependiendo del plan de ejecución seleccionado.

Sintaxis EXPLAIN y ANALYZE

Una vez comprendida la importancia de los planes de ejecución, necesitamos herramientas prácticas para obtenerlos y analizarlos. Las cláusulas EXPLAIN y ANALYZE son comandos fundamentales que nos permiten visualizar cómo el motor de base de datos procesa nuestras consultas SQL.

Uso básico de EXPLAIN

La sintaxis básica de EXPLAIN es sencilla: simplemente se antepone a cualquier consulta SQL cuyo plan de ejecución queramos examinar:

EXPLAIN SELECT * FROM productos WHERE precio > 100;

Este comando no ejecuta realmente la consulta, sino que muestra el plan que el optimizador ha elegido para resolverla. Es una forma segura de analizar consultas potencialmente costosas sin afectar a los datos ni consumir excesivos recursos.

Diferencias entre MySQL y PostgreSQL

Aunque el concepto es similar, la implementación y la información proporcionada varían significativamente entre estos sistemas de gestión de bases de datos.

EXPLAIN en MySQL

En MySQL, la sintaxis básica de EXPLAIN devuelve una tabla con información estructurada sobre el plan:

EXPLAIN SELECT c.nombre, p.producto 
FROM clientes c 
JOIN pedidos p ON c.id = p.cliente_id 
WHERE c.ciudad = 'Barcelona';

La salida incluirá columnas como:

• id: Identificador secuencial para cada operación de la consulta
• select_type: Tipo de consulta (SIMPLE, PRIMARY, SUBQUERY, etc.)
• table: Tabla a la que se refiere esta parte del plan
• type: Método de acceso (ALL, index, range, ref, eq_ref, const, etc.)
• possible_keys: Índices que podrían utilizarse
• key: Índice realmente utilizado
• key_len: Longitud del índice utilizado
• ref: Columnas o constantes comparadas con el índice
• rows: Estimación de filas examinadas
• Extra: Información adicional (Using index, Using temporary, etc.)

MySQL también ofrece un formato más visual con EXPLAIN FORMAT=TREE:

EXPLAIN FORMAT=TREE SELECT * FROM productos WHERE categoria_id = 5;

EXPLAIN en PostgreSQL

PostgreSQL proporciona una salida en formato de árbol que muestra la jerarquía de operaciones:

EXPLAIN SELECT c.nombre, p.producto 
FROM clientes c 
JOIN pedidos p ON c.id = p.cliente_id 
WHERE c.ciudad = 'Barcelona';

La salida incluye:

• Tipo de operación: Seq Scan, Index Scan, Hash Join, etc.
• Costes estimados: Valores de inicio y total (cost=0.00..10.50)
• Filas estimadas: Número de filas que se espera procesar (rows=100)
• Ancho estimado: Tamaño medio en bytes de cada fila (width=40)
• Filtros y condiciones: Detalles sobre las condiciones aplicadas

PostgreSQL permite formatos alternativos como JSON, XML, YAML o TEXT:

EXPLAIN (FORMAT JSON) SELECT * FROM productos WHERE precio > 100;

Uso de EXPLAIN ANALYZE

Mientras que EXPLAIN muestra el plan estimado, EXPLAIN ANALYZE va un paso más allá: ejecuta realmente la consulta y compara las estimaciones con los valores reales obtenidos.

En MySQL

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE fecha BETWEEN '2023-01-01' AND '2023-01-31';

La salida incluirá el tiempo real de ejecución y estadísticas adicionales sobre cada operación.

En PostgreSQL

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE fecha BETWEEN '2023-01-01' AND '2023-01-31';

PostgreSQL proporciona información detallada sobre:

• Tiempo real: Duración de cada operación
• Filas reales: Número exacto de filas procesadas
• Bucles: Número de iteraciones para cada operación
• Memoria utilizada: En operaciones como Hash Join o Sort

Opciones avanzadas de EXPLAIN

Ambos sistemas ofrecen opciones adicionales para obtener información más detallada.

Opciones en MySQL

MySQL 8.0+ permite combinar varias opciones:

EXPLAIN FORMAT=JSON 
SELECT * FROM productos p 
JOIN categorias c ON p.categoria_id = c.id;

Otras opciones útiles incluyen:

• ANALYZE: Como ya vimos, ejecuta la consulta y muestra estadísticas reales
• FORMAT=JSON/TREE: Cambia el formato de salida
• EXTENDED: (Obsoleto en versiones recientes) Mostraba información adicional

Opciones en PostgreSQL

PostgreSQL ofrece un conjunto más amplio de opciones que pueden combinarse:

EXPLAIN (ANALYZE TRUE, BUFFERS TRUE, COSTS TRUE, TIMING TRUE, FORMAT JSON)
SELECT * FROM productos WHERE categoria_id IN (SELECT id FROM categorias WHERE activo = true);

Las opciones más útiles son:

• ANALYZE: Ejecuta la consulta y muestra estadísticas reales
• BUFFERS: Muestra información sobre el uso de caché
• COSTS: Incluye estimaciones de coste (activado por defecto)
• TIMING: Muestra el tiempo real de cada operación
• VERBOSE: Incluye el esquema completo de columnas
• SETTINGS: Muestra parámetros no predeterminados que afectan al plan
• WAL: Muestra estadísticas de generación de WAL (Write-Ahead Logging)
• FORMAT: Especifica el formato de salida (TEXT, XML, JSON, YAML)

Interpretación práctica de la salida de EXPLAIN

Veamos algunos ejemplos prácticos de cómo interpretar la salida de EXPLAIN:

Ejemplo 1: Identificar un escaneo de tabla completo

En MySQL:

EXPLAIN SELECT * FROM clientes WHERE apellido = 'García';

-- Posible salida:
-- id | select_type | table    | type | key  | rows  | Extra
-- 1  | SIMPLE      | clientes | ALL  | NULL | 10000 | Using where

El tipo ALL indica un escaneo completo de tabla, lo que sugiere que no hay un índice adecuado en la columna "apellido".

En PostgreSQL:

EXPLAIN SELECT * FROM clientes WHERE apellido = 'García';

-- Posible salida:
-- Seq Scan on clientes  (cost=0.00..220.00 rows=15 width=120)
--   Filter: (apellido = 'García'::text)

El Seq Scan (escaneo secuencial) indica lo mismo: falta un índice apropiado.

Ejemplo 2: Verificar el uso de índices

En MySQL:

EXPLAIN SELECT * FROM productos WHERE id = 1234;

-- Posible salida:
-- id | select_type | table     | type  | key     | rows | Extra
-- 1  | SIMPLE      | productos | const | PRIMARY | 1    | NULL

El tipo const y el uso de la clave PRIMARY indican un acceso eficiente mediante el índice de clave primaria.

En PostgreSQL:

EXPLAIN SELECT * FROM productos WHERE id = 1234;

-- Posible salida:
-- Index Scan using productos_pkey on productos  (cost=0.42..8.44 rows=1 width=120)
--   Index Cond: (id = 1234)

El Index Scan muestra que se está utilizando el índice de clave primaria.

Ejemplo 3: Analizar una unión (JOIN)

En MySQL:

EXPLAIN SELECT c.nombre, p.fecha
FROM clientes c JOIN pedidos p ON c.id = p.cliente_id;

-- Posible salida simplificada:
-- id | table | type  | key       | rows  | Extra
-- 1  | c     | ALL   | NULL      | 1000  | NULL
-- 1  | p     | ref   | cliente_id| 10    | NULL

Esto muestra que se está haciendo un escaneo completo de la tabla clientes, y luego para cada cliente se buscan sus pedidos usando el índice cliente_id.

En PostgreSQL:

EXPLAIN SELECT c.nombre, p.fecha
FROM clientes c JOIN pedidos p ON c.id = p.cliente_id;

-- Posible salida:
-- Hash Join  (cost=35.50..1135.50 rows=10000 width=36)
--   Hash Cond: (p.cliente_id = c.id)
--   -> Seq Scan on pedidos p  (cost=0.00..770.00 rows=10000 width=12)
--   -> Hash  (cost=22.00..22.00 rows=1000 width=24)
--        -> Seq Scan on clientes c  (cost=0.00..22.00 rows=1000 width=24)

PostgreSQL ha elegido un Hash Join, creando primero una tabla hash con los clientes y luego comparando cada pedido con esa tabla hash.

Casos prácticos de optimización con EXPLAIN

Caso 1: Identificar y resolver un problema de índice faltante

Supongamos que tenemos una consulta lenta:

EXPLAIN ANALYZE
SELECT * FROM facturas WHERE fecha_emision BETWEEN '2023-01-01' AND '2023-01-31';

Si vemos un escaneo completo de tabla, podemos crear un índice:

CREATE INDEX idx_facturas_fecha ON facturas(fecha_emision);

Y luego verificar la mejora:

EXPLAIN ANALYZE
SELECT * FROM facturas WHERE fecha_emision BETWEEN '2023-01-01' AND '2023-01-31';

Ahora deberíamos ver un Index Scan o Index Range Scan en lugar del escaneo completo.

Caso 2: Optimizar una consulta con ORDER BY

Analicemos una consulta con ordenación:

EXPLAIN ANALYZE
SELECT * FROM productos ORDER BY precio DESC LIMIT 10;

Si vemos una operación de filesort o Sort, podríamos crear un índice que incluya el orden:

CREATE INDEX idx_productos_precio ON productos(precio DESC);

La nueva ejecución debería mostrar un Index Scan sin operación de ordenación adicional.

Caso 3: Mejorar una consulta con JOIN y filtros

Para una consulta compleja:

EXPLAIN ANALYZE
SELECT c.nombre, SUM(d.cantidad * d.precio) as total
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN detalles_pedido d ON p.id = d.pedido_id
WHERE p.fecha > '2023-01-01'
GROUP BY c.nombre
ORDER BY total DESC;

Podríamos necesitar múltiples índices:

CREATE INDEX idx_pedidos_fecha ON pedidos(fecha);
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);
CREATE INDEX idx_detalles_pedido ON detalles_pedido(pedido_id);

Buenas prácticas al usar EXPLAIN

• Analiza consultas reales: Usa consultas y volúmenes de datos representativos de tu entorno de producción.
• Compara antes y después: Guarda los planes de ejecución antes de realizar cambios para poder comparar.
• Presta atención a las estimaciones: Grandes discrepancias entre filas estimadas y reales pueden indicar estadísticas desactualizadas.
• Actualiza estadísticas regularmente: En MySQL con ANALYZE TABLE y en PostgreSQL con ANALYZE.
• Considera el contexto completo: Un plan aparentemente subóptimo puede ser la mejor opción dadas las circunstancias.
• Usa EXPLAIN ANALYZE con precaución: Recuerda que ejecuta realmente la consulta, lo que puede ser problemático para operaciones de escritura o consultas muy pesadas.

Limitaciones a tener en cuenta

• Variabilidad: Los planes pueden cambiar con actualizaciones del motor, cambios en los datos o en la configuración.
• Complejidad: Interpretar planes complejos requiere experiencia y conocimiento del motor específico.
• Contexto: EXPLAIN no muestra factores externos como carga del servidor o bloqueos.
• Consultas parametrizadas: El plan puede variar según los valores de los parámetros.

En MySQL, puedes usar la característica de plan estabilizado para almacenar y forzar planes específicos:

-- Capturar un plan
EXPLAIN FORMAT=JSON SELECT * FROM productos WHERE categoria_id = 5 INTO @query_plan;

-- Almacenar el plan
CALL sys.ps_set_query_plan('plan_productos_categoria', @query_plan);

En PostgreSQL, puedes influir en el optimizador con parámetros de coste o deshabilitando ciertas estrategias:

-- Desactivar Hash Joins para una consulta específica
SET LOCAL enable_hashjoin = off;
EXPLAIN SELECT * FROM clientes c JOIN pedidos p ON c.id = p.cliente_id;

El dominio de EXPLAIN y ANALYZE es un proceso gradual que mejora con la práctica y la experiencia. Estas herramientas son esenciales para cualquier desarrollador o administrador de bases de datos que busque optimizar el rendimiento de sus consultas SQL.