Optimización (EXPLAIN QUERY PLAN) y motores de bases de datos — Aprende SQL

Aprende SQL · lección gratuita

Lección 43 · Optimización (EXPLAIN QUERY PLAN) y motores de bases de datos

Resumen

Antes de optimizar una consulta hay que entender cómo la ejecuta el motor. EXPLAIN QUERY PLAN muestra el plan que SQLite eligió: si recorre la tabla entera (SCAN) o busca de forma dirigida con un índice (SEARCH), si usa tablas temporales para ordenar/agrupar, y en qué orden hace los joins. Junto con las herramientas de introspección (sqlite_version(), PRAGMA table_info, sqlite_master), te dan visibilidad total sobre el esquema y el rendimiento.

EXPLAIN QUERY PLAN SELECT ...: devuelve el plan de ejecución en lenguaje legible (no ejecuta la consulta de datos, solo describe cómo lo haría).
SCAN vs SEARCH: SCAN tabla recorre todas las filas (lento si la tabla es grande); SEARCH tabla USING INDEX salta directo a las filas relevantes (rápido). Crear un índice adecuado convierte un SCAN en SEARCH.
sqlite_version(): devuelve la versión del motor; útil para saber qué funciones (window functions, CTEs recursivos, etc.) están disponibles.
PRAGMA table_info('tabla'): lista columnas, tipos, NOT NULL, valor por defecto y si forman parte de la PK.
sqlite_master: catálogo del sistema; contiene la definición (sql) de cada tabla, índice, vista y trigger.

Sintaxis / Conceptos

EXPLAIN QUERY PLAN antepone esas tres palabras a cualquier SELECT. La salida es un árbol: cada nodo es una operación. Frases clave a reconocer:

SCAN t → recorrido completo de la tabla t.
SEARCH t USING INDEX idx (col=?) → búsqueda por índice.
SEARCH t USING INTEGER PRIMARY KEY (rowid=?) → acceso por clave primaria (lo más rápido).
USE TEMP B-TREE FOR ORDER BY / GROUP BY → SQLite construye una estructura temporal para ordenar o agrupar; suele indicar que falta un índice que cubra ese orden.
USING COVERING INDEX → la consulta se resuelve solo con el índice, sin tocar la tabla.

El flujo de optimización es: ejecutar EXPLAIN QUERY PLAN, detectar SCAN sobre columnas filtradas, crear un índice, y volver a ejecutar EXPLAIN QUERY PLAN para confirmar que ahora aparece SEARCH ... USING INDEX.

SELECT nombre, salario, NTILE(4) OVER (ORDER BY salario) AS cuartil FROM empleados ORDER BY salario

Ejemplos

-- Introspección del motor y del esquema
SELECT sqlite_version() AS version;

-- Estructura de la tabla empleados: columnas, tipos, NOT NULL, PK
PRAGMA table_info('empleados');

-- El catálogo del sistema: definición de cada objeto
SELECT type, name FROM sqlite_master
WHERE type IN ('table','index','view')
ORDER BY type, name;

-- Plan SIN índice: recorrido completo (SCAN empleados)
DROP INDEX IF EXISTS idx_emp_depto;
EXPLAIN QUERY PLAN
SELECT * FROM empleados WHERE departamento_id = 4;

-- Mismo filtro, ahora CON índice: el plan cambia a SEARCH ... USING INDEX
DROP INDEX IF EXISTS idx_emp_depto;
CREATE INDEX idx_emp_depto ON empleados(departamento_id);
EXPLAIN QUERY PLAN
SELECT * FROM empleados WHERE departamento_id = 4;

💡 EXPLAIN QUERY PLAN describe el plan; EXPLAIN (sin QUERY PLAN) muestra el bytecode interno de la máquina virtual de SQLite (muy detallado, rara vez necesario). Para tunear, casi siempre te basta EXPLAIN QUERY PLAN.

Motores de bases de datos: SQLite vs PostgreSQL vs MySQL vs MariaDB

SQLite es un motor embebido (corre dentro de tu app, sin servidor), pero en producción coexisten otros motores cliente-servidor. Esta comparación es conceptual (bloque NO ejecutable):

Motor	Arquitectura	Tipado	Licencia	Cuándo usarlo
SQLite	Embebido, un archivo `.db`, sin servidor	Dinámico (type affinity): una columna acepta cualquier tipo	Dominio público	Apps móviles/desktop, prototipos, tests, datos locales, sitios de bajo tráfico
PostgreSQL	Cliente-servidor, multiproceso	Estricto y muy rico (JSONB, arrays, tipos geométricos, ENUM, tipos propios)	PostgreSQL License (permisiva, tipo BSD)	Apps complejas, analítica, integridad fuerte, extensiones (PostGIS), alta concurrencia de escritura
MySQL	Cliente-servidor, motores de almacenamiento (InnoDB por defecto)	Estricto (según `sql_mode`)	GPL + comercial (propiedad de Oracle)	Apps web tradicionales (LAMP), lecturas masivas, ecosistema maduro, replicación sencilla
MariaDB	Cliente-servidor, fork de MySQL, compatible	Estricto, similar a MySQL + motores extra (Aria, ColumnStore)	GPL (comunidad, sin control de Oracle)	Reemplazo directo de MySQL queriendo licencia libre y mejoras de rendimiento/funciones

Reglas prácticas de elección:
- ¿Necesitas un servidor y muchos usuarios concurrentes escribiendo? → NO SQLite (usa Postgres/MySQL/MariaDB).
- ¿Datos locales, una sola app, cero administración? → SQLite.
- ¿Máxima riqueza de tipos, JSONB, integridad y analítica? → PostgreSQL.
- ¿Stack web clásico, hosting compartido, mucho contenido? → MySQL/MariaDB.
- ¿Quieres MySQL pero 100% libre y sin Oracle? → MariaDB.

Diferencias de tipado clave:
- SQLite usa "type affinity": INTEGER, TEXT, REAL, BLOB, NUMERIC. Una columna TEXT puede
  guardar un número; la conversión es flexible. No existe un tipo BOOLEAN ni DATE nativos
  (se usan 0/1 y TEXT 'YYYY-MM-DD').
- Postgres/MySQL/MariaDB son estrictos: VARCHAR(n), DECIMAL(p,s), BOOLEAN, DATE, TIMESTAMP
  reales, y rechazan valores que no encajen en el tipo declarado.

Cheatsheet

Comando	Qué hace
`EXPLAIN QUERY PLAN SELECT ...`	Muestra el plan de ejecución (SCAN/SEARCH/joins)
`EXPLAIN SELECT ...`	Bytecode interno de la VM de SQLite (avanzado)
`sqlite_version()`	Versión del motor SQLite
`PRAGMA table_info('t')`	Columnas, tipos, NOT NULL y PK de la tabla
`SELECT * FROM sqlite_master`	Catálogo: definición de tablas, índices, vistas
`ANALYZE`	Recolecta estadísticas para mejorar las decisiones del optimizador

---

← Transacciones: BEGIN, COMMIT, ROLLBACK y SAVEPOINT Triggers y automatización →

Ver todas las lecciones de Aprende SQL →