Vistas e índices

Una vista es una consulta guardada con nombre que se usa después como si fuera una tabla. No almacena datos: lo que guarda es la definición del SELECT, de modo que cada vez que alguien la consulta el gestor ejecuta esa consulta contra las tablas reales y devuelve el resultado del momento.

CREATE VIEW rendimiento_alumno AS
SELECT    a.id_alumno,
          a.nombre,
          COUNT(i.id_intento) AS intentos,
          AVG(i.aciertos)     AS media
FROM      alumno a
LEFT JOIN intento i ON i.id_alumno = a.id_alumno
GROUP BY  a.id_alumno, a.nombre;

-- Y a partir de ahí se consulta como una tabla más
SELECT nombre, media
FROM   rendimiento_alumno
WHERE  intentos > 0;

• Simplifican: una combinación de cuatro tablas con su agrupación se escribe una vez y se consulta luego en una línea.
• Aíslan: si cambia la estructura de las tablas de debajo, se ajusta la vista y las consultas que la usaban siguen funcionando igual.
• Dan seguridad por filas y por columnas: se concede permiso sobre la vista y no sobre las tablas, de manera que el usuario solo alcanza los datos que la vista deja ver.

Algunas vistas admiten INSERT, UPDATE y DELETE, que el gestor traslada a la tabla de debajo. Solo puede hacerlo si sabe a qué fila real corresponde cada fila de la vista, así que las que llevan agrupaciones, funciones de agregado, DISTINCT o combinaciones de varias tablas no suelen ser actualizables.

CREATE VIEW alumno_express AS
SELECT id_alumno, nombre, modalidad
FROM   alumno
WHERE  modalidad = 'express'
WITH CHECK OPTION;

-- Rechazado: la fila resultante ya no se vería por la vista
UPDATE alumno_express SET modalidad = 'largo' WHERE id_alumno = 101;

WITH CHECK OPTION es la cláusula que cierra el agujero. Sin ella se puede insertar o modificar a través de la vista una fila que después no cumple su condición: entra en la tabla y desaparece de la vista, con lo que el usuario ve fracasar algo que en realidad ha funcionado. Con ella, el gestor rechaza toda inserción o modificación cuyo resultado no seguiría siendo visible por la vista.

La vista no guarda datos, guarda una consulta. La excepción es la vista materializada, que sí almacena el resultado y hay que refrescar para que no se quede viejo.

Un índice es una estructura auxiliar que el gestor mantiene aparte para localizar filas sin recorrer la tabla entera. Funciona como el índice de un libro: ordena las claves y para cada una guarda dónde está la fila, de modo que buscar deja de costar proporcionalmente al tamaño de la tabla.

CREATE INDEX ix_intento_alumno_fecha
  ON intento (id_alumno, realizado);

En un índice de varias columnas el orden importa. Ese ejemplo sirve para buscar por alumno, y también por alumno y fecha a la vez, pero no sirve para buscar solo por fecha: es el mismo motivo por el que una guía telefónica ordenada por apellido y luego por nombre no ayuda a encontrar a todos los que se llaman Ana.

Los índices no son gratis. Cada uno ocupa espacio y, sobre todo, hay que actualizarlo en cada INSERT, UPDATE y DELETE que toque sus columnas. Por eso aceleran las consultas y frenan las escrituras, y por eso indexar por sistema todas las columnas de una tabla empeora el conjunto en lugar de mejorarlo.

La implementación habitual es un árbol equilibrado de la familia B, y en concreto el árbol B+. La diferencia con el árbol B a secas es que en el B+ los datos están solo en las hojas y las hojas van encadenadas entre sí, lo que permite dos cosas a la vez: bajar desde la raíz hasta una clave concreta en muy pocos saltos y, una vez abajo, recorrer un rango de valores siguiendo la cadena sin volver a subir.

Como el árbol se mantiene equilibrado, todas las hojas quedan a la misma profundidad y el coste de una búsqueda es logarítmico y estable. Traducido a lo que importa: en una tabla de millones de filas se llega a la que se busca leyendo un puñado de páginas, no la tabla entera.

No es el único tipo de índice. Los hay de dispersión, que resuelven la igualdad muy rápido pero no sirven para rangos ni para ordenar; los de mapa de bits, pensados para columnas con pocos valores distintos y típicos de entornos de análisis; y los de texto completo, para buscar palabras dentro de campos largos.

Como SQL es declarativo, quien escribe la consulta no decide el camino. Lo decide el optimizador, y EXPLAIN PLAN es la sentencia que permite verlo antes de ejecutarla: muestra la secuencia de operaciones que el gestor piensa realizar para resolverla. Se puede pedir sobre SELECT, INSERT, UPDATE y DELETE.

EXPLAIN PLAN FOR
SELECT   a.nombre, COUNT(*)
FROM     intento i
JOIN     alumno a ON a.id_alumno = i.id_alumno
WHERE    i.fallos > 5
GROUP BY a.nombre;

Es la herramienta con la que se diagnostica una consulta lenta. Lo que se busca en el plan es si el gestor va a recorrer la tabla entera o va a apoyarse en un índice, en qué orden va a combinar las tablas y en qué punto va a ordenar o agrupar. Si una consulta que debería usar un índice no lo usa, el plan lo enseña y ahí empieza el trabajo de ajuste.

El plan no es fijo: depende de los datos y de las estadísticas que el gestor tenga de ellos, así que la misma consulta puede resolverse de una forma hoy y de otra dentro de un mes.