EXPLAIN y los planes de consulta: lee lo que la base de datos hace realmente

Descifra el plan que el planificador de SQLite elige realmente para tus consultas con EXPLAIN QUERY PLAN.

10 min · 3 preguntas

SQL es declarativo: describes el resultado, no cómo obtenerlo. Es el planificador de consultas el que decide cómo leer los datos. Para verlo trabajar, SQLite ofrece dos comandos: EXPLAIN QUERY PLAN da un plan legible de alto nivel, mientras que EXPLAIN a secas vuelca el bytecode de la máquina virtual VDBE. Instala la herramienta de línea de comandos sqlite3, o abre sqliteonline.com en tu navegador, y reproduce cada ejemplo que sigue.

SQL

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000);

El conjunto de datos que debes crear antes que nada.

Coloca EXPLAIN QUERY PLAN delante de cualquier consulta SELECT. Cada línea del resultado describe cómo se lee una tabla. Dos palabras clave dominan: SCAN significa que la tabla se recorre por completo, fila por fila, mientras que SEARCH indica un acceso dirigido guiado por un índice. Sin un índice adecuado, incluso una simple igualdad ya provoca un recorrido completo.

SQL

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Result: SCAN employees
-- (no index: the whole table is read)

Sin un índice, el planificador recorre la tabla entera.

Crea un índice sobre la columna filtrada y vuelve a ejecutar exactamente la misma consulta: el plan pasa de SCAN a SEARCH ... USING INDEX. Otras menciones también son valiosas: USE TEMP B-TREE revela una ordenación o agrupación materializada en memoria (a menudo señal de que falta un índice de ordenación), y USING COVERING INDEX indica que la tabla ni siquiera se abrió.

SQL

CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)

El mismo SELECT, pero ahora guiado por el índice.

El planificador se apoya en estadísticas. Ejecuta ANALYZE: SQLite rellena la tabla interna sqlite_stat1 con la distribución de los valores, lo que le ayuda a elegir el índice correcto cuando hay varios candidatos. Sin estadísticas, aplica heurísticas por defecto y puede equivocarse en tablas grandes.

EXPLAIN QUERY PLAN no ejecuta la consulta y no mide ninguna duración: muestra el plan *estimado*. Ese plan puede cambiar a medida que crecen los datos, tras un ANALYZE, o si añades un índice. Comprueba siempre el plan con un volumen realista, no con tres filas de prueba.

Prueba de conocimientos

Comprueba que has retenido los puntos clave de esta lección.

En SQLite, ¿qué devuelve EXPLAIN QUERY PLAN en comparación con EXPLAIN a secas?
- El bytecode de la VDBE, instrucción por instrucción
- Un plan de alto nivel (SCAN, SEARCH, ordenación temporal)
- El tiempo de ejecución real medido de la consulta
- La lista de los índices existentes en la tabla
En un plan de SQLite, ¿qué significa la línea SCAN employees?
- La tabla se recorre por completo, fila por fila
- Se usa un índice cubridor
- Se hace una búsqueda dirigida mediante un índice
- La tabla se carga en la caché de memoria
¿Para qué sirve el comando ANALYZE?
- Reconstruye todos los índices de la base de datos
- Recopila estadísticas en sqlite_stat1 para guiar al planificador
- Ejecuta la consulta y mide su duración
- Bloquea la tabla durante el análisis