Índices avanzados: compuesto, cubridor, índice ignorado

Domina la regla del prefijo más a la izquierda, los índices cubridores y las trampas que hacen que un índice sea invisible para el planificador.

11 min · 3 preguntas

Un índice compuesto ordena las filas sobre varias columnas, en el orden declarado. La regla decisiva es el prefijo más a la izquierda: un índice sobre (a, b) puede servir a un filtro sobre a, o sobre a y b, pero nunca sobre b solo. El orden de las columnas es por tanto una decisión de diseño, no un detalle. Primero crea el conjunto de datos de abajo.

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

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

Creemos un índice sobre (department_id, salary). Un filtro que empieza por department_id provoca un SEARCH. Pero un filtro solo sobre salary rompe el prefijo más a la izquierda: el planificador no puede saltar directamente a las filas correctas y recae en un SCAN. Compara los dos planes.

CREATE INDEX idx_dept_salary
  ON employees (department_id, salary);

-- Leftmost prefix present: index used
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE department_id = 1
  AND salary > 60000;
-- SEARCH ... USING INDEX idx_dept_salary

-- Leading column missing: index ignored
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE salary > 60000;
-- SCAN employees

Un índice cubridor contiene todas las columnas que lee la consulta. El planificador responde entonces directamente desde el índice, sin abrir la tabla: ese es el significado de USING COVERING INDEX. Aquí, el índice (department_id, salary) cubre una consulta que lee solo esas dos columnas. Recuerda que el id (el rowid) está presente de forma implícita en todos los índices, así que también queda cubierto sin coste.

-- Every column read is in the index
EXPLAIN QUERY PLAN
SELECT department_id, salary
FROM employees
WHERE department_id = 1;
-- SEARCH employees USING COVERING INDEX
-- idx_dept_salary (department_id=?)

Prueba de conocimientos

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

1. Con un índice compuesto sobre (department_id, salary), ¿qué consulta NO puede usarlo?
   • WHERE department_id = 1
   • WHERE department_id = 1 AND salary > 60000
   • WHERE salary > 60000
   • WHERE department_id = 1 AND name = 'Ada'
2. ¿Qué es un índice cubridor?
   • Un índice que contiene todas las columnas leídas, evitando abrir la tabla
   • Un índice que cubre varias tablas a la vez
   • Un índice recreado automáticamente tras cada INSERT
   • Un sinónimo de un índice único sobre la clave primaria
3. ¿Cuál de estas condiciones impide el uso de un índice simple sobre email?
   • WHERE email = 'a@b.co'
   • WHERE email > 'm'
   • WHERE lower(email) = 'a@b.co'
   • WHERE email IN ('a@b.co', 'c@d.co')