Módulo 5 · 03 | Vistas e índices

MÓDULO 5 · CLASE 3

Vistas e índices

Clase práctica. Usar y crear vistas para reportes y Power BI, vistas actualizables con WITH CHECK OPTION, e índices medidos con EXPLAIN.

Idea clave

Una vista guarda una consulta con nombre para reutilizarla como si fuera una tabla. Un índice es una estructura ordenada que permite encontrar filas sin leer toda la tabla. La vista simplifica; el índice acelera.

Seguimos con USE tablas_libro;

Parte A · Vistas

1. Qué es una vista, paso a paso

La vista no copia datos: cada vez que la consultás, MySQL ejecuta su SELECT sobre las tablas base.

Escribís una consulta que funciona (por ejemplo, el JOIN de alumnos, alumnos_cursos y cursos).
Le ponés nombre con CREATE VIEW nombre AS + la consulta.
La usás como una tabla: SELECT ... FROM nombre_vista, con WHERE, GROUP BY, etc.
Los datos siempre están al día, porque se leen de las tablas base en cada consulta.

2. Las vistas que ya trae tablas_libro

SHOW FULL TABLES WHERE Table_type = 'VIEW';
-- ALUMNOS_CURSOS_V · EMPLEADOS_V · MASCOTAS_V · TOTALES_SALARIO_V

-- Total de salarios por sexo, ya resuelto en la vista
SELECT * FROM TOTALES_SALARIO_V;
-- H 2900 · M 2625.5

-- MASCOTAS_V traduce los códigos ('P' → 'Perro', 'A' → 'Alta'...)
SELECT ESPECIE, COUNT(*) AS cantidad
FROM MASCOTAS_V
GROUP BY ESPECIE;
-- Gato 7 · Perro 9

-- Ver cómo está definida una vista
SHOW CREATE VIEW MASCOTAS_V;

Convención de nombres

En esta base las vistas terminan en _V. Usar un sufijo o prefijo fijo (_V, v_) permite reconocerlas a simple vista en Workbench y en Power BI.

3. Crear una vista lista para Power BI

Con LEFT JOIN incluimos también el curso sin profesor y sin alumnos. En Power BI elegís esta vista al conectarte a MySQL (Obtener datos → Base de datos MySQL) y no tenés que repetir la lógica de los JOIN en Power Query.

CREATE OR REPLACE VIEW v_cursos_profesores AS
SELECT c.ID_CURSO, c.TITULO,
       CONCAT(p.NOMBRE, ' ', p.APELLIDOS) AS profesor,
       COUNT(ac.ID_ALUMNO)                AS inscriptos
FROM cursos AS c
LEFT JOIN profesores     AS p  ON p.ID_PROFE  = c.ID_PROFE
LEFT JOIN alumnos_cursos AS ac ON ac.ID_CURSO = c.ID_CURSO
GROUP BY c.ID_CURSO, c.TITULO, p.NOMBRE, p.APELLIDOS;

SELECT * FROM v_cursos_profesores;

ID_CURSO	TITULO	profesor	inscriptos
1	Programación PHP	Rosa Honrosa Pérez	3
2	Modelos abstracto de datos	Rosa Honrosa Pérez	1
3	SQL desde cero	Federico Gasco Daza	2
4	Dibujo técnico	Ana Saura Trenzo	2
5	SQL avanzado	NULL	0

4. Administrar vistas

-- Modificarla: CREATE OR REPLACE VIEW ... (o ALTER VIEW ...)

-- Eliminarla (no borra datos de las tablas base)
DROP VIEW IF EXISTS v_cursos_profesores;

5. Vistas actualizables y WITH CHECK OPTION

Una vista sobre una sola tabla, sin GROUP BY, DISTINCT, funciones de agregación ni UNION, es actualizable: podés hacer INSERT o UPDATE a través de ella. WITH CHECK OPTION impide cargar filas que la vista no podría mostrar.

CREATE OR REPLACE VIEW v_mascotas_e04 AS
SELECT ID_MASCOTA, NOMBRE, ESPECIE, SEXO, UBICACION, ESTADO
FROM mascotas
WHERE UBICACION = 'E04'
WITH CHECK OPTION;

INSERT INTO v_mascotas_e04 VALUES (17, 'Lola', 'G', 'H', 'E01', 'A');
-- Error 1369: CHECK OPTION failed 'tablas_libro.v_mascotas_e04'

Ojo

Si probás un INSERT válido (con UBICACION ‘E04’), la fila se guarda en mascotas. Borrala después con DELETE FROM mascotas WHERE ID_MASCOTA = 17; para que los resultados del curso sigan coincidiendo.

Seguridad

Las vistas sirven para dar acceso parcial: GRANT SELECT ON tablas_libro.EMPLEADOS_V TO 'analista'@'%'; permite consultar la vista sin ver la tabla completa.

Parte B · Índices

6. Qué hace un índice, paso a paso

Buscar WHERE F_NACIMIENTO = ‘1992-11-23’ sin índice y con un índice B-Tree.

Sin índice, MySQL lee todas las filas y compara una por una (full table scan).
Con índice, MySQL mantiene una copia ordenada de la columna en forma de árbol (B-Tree), con punteros a cada fila.
Baja por el árbol comparando: 1992-11-23 ≥ 1992-11-23, entonces va a la rama derecha.
Llega a la hoja y salta directo a la fila (Marta). Con millones de filas, la diferencia es de segundos a milisegundos.

7. Ver los índices que ya existen

SHOW INDEX FROM cursos;
-- PRIMARY             → ID_CURSO   (lo crea la PRIMARY KEY)
-- cursos_id_profe_fk  → ID_PROFE   (InnoDB lo crea solo para la FOREIGN KEY)

SHOW INDEX FROM alumnos_cursos;
-- PRIMARY                     → (ID_ALUMNO, ID_CURSO)  clave compuesta
-- alumnos_cursos_id_curso_fk  → ID_CURSO

MySQL ya indexa las claves primarias y las foráneas, por eso los JOIN del curso son rápidos aunque no hayas creado índices. Fijate en alumnos_cursos: no creó un índice aparte para la FK ID_ALUMNO, porque la PRIMARY KEY ya empieza con esa columna (regla del prefijo izquierdo, punto 9).

8. Crear un índice y medirlo con EXPLAIN

-- 1) Plan ANTES del índice
EXPLAIN SELECT * FROM alumnos WHERE F_NACIMIENTO = '1992-11-23';
-- type: ALL · key: NULL · rows: 6  → lee toda la tabla

-- 2) Crear el índice
CREATE INDEX idx_alumnos_nacimiento ON alumnos (F_NACIMIENTO);

-- 3) Plan DESPUÉS del índice
EXPLAIN SELECT * FROM alumnos WHERE F_NACIMIENTO = '1992-11-23';
-- type: ref · key: idx_alumnos_nacimiento · rows: 1  → usa el índice

Columna de EXPLAIN	Qué mirar
`type`	De mejor a peor: `const` → `eq_ref` → `ref` → `range` → `index` → `ALL`
`key`	El índice elegido (NULL = ninguno)
`rows`	Filas que MySQL estima que va a leer

Tablas chicas

Con muy pocas filas, el optimizador a veces prefiere leer toda la tabla aunque exista el índice, porque le sale más barato. Los índices se notan en tablas grandes. En MySQL 8.0.18+ podés usar EXPLAIN ANALYZE para ver tiempos reales.

9. Índices compuestos: la regla del prefijo izquierdo

CREATE INDEX idx_mascotas_especie_sexo ON mascotas (ESPECIE, SEXO);

Funciona como una guía telefónica ordenada por apellido, nombre: sirve para buscar por apellido, o por apellido y nombre, pero no solo por nombre.

Filtro	¿Usa el índice (ESPECIE, SEXO)?
`WHERE ESPECIE = 'P'`	✔ Sí (primera columna)
`WHERE ESPECIE = 'P' AND SEXO = 'H'`	✔ Sí (las dos columnas, en orden)
`WHERE SEXO = 'H'`	✘ No por sí solo (salta la primera columna)

10. Errores comunes que anulan un índice

-- ✘ Función sobre la columna indexada: no usa idx_alumnos_nacimiento (type: ALL)
SELECT NOMBRE FROM alumnos WHERE YEAR(F_NACIMIENTO) = 1992;

-- ✔ Mismo filtro escrito como rango: sí puede usarlo → Marta
SELECT NOMBRE FROM alumnos
WHERE F_NACIMIENTO >= '1992-01-01' AND F_NACIMIENTO < '1993-01-01';

-- (suponiendo un índice sobre alumnos.APELLIDOS)
-- ✘ LIKE con comodín al inicio no aprovecha el índice
SELECT * FROM alumnos WHERE APELLIDOS LIKE '%Mata';
-- ✔ Comodín al final sí
SELECT * FROM alumnos WHERE APELLIDOS LIKE 'Lomas%';

No indexes todo: cada índice ocupa espacio y hace más lentos los INSERT, UPDATE y DELETE.
Priorizá columnas que aparecen seguido en WHERE, JOIN y ORDER BY, y que tienen muchos valores distintos.
Un índice UNIQUE también evita duplicados: CREATE UNIQUE INDEX uq_equipos_nombre ON equipos (EQUIPO);
Para borrar uno: DROP INDEX idx_alumnos_nacimiento ON alumnos;

11. Práctica

Creá un índice sobre mascotas(UBICACION) y compará EXPLAIN antes y después para WHERE UBICACION = 'E02'.
Creá la vista v_cursos_sin_alumnos con los cursos que no tienen inscriptos.
Reescribí WHERE YEAR(F_SESION) = 2009 AND MONTH(F_SESION) = 3 (tabla poker) para que pueda usar un índice.

-- Soluciones
-- 1) Antes: type ALL, rows 16 · Después: type ref, key idx_mascotas_ubicacion, rows 5
EXPLAIN SELECT * FROM mascotas WHERE UBICACION = 'E02';
CREATE INDEX idx_mascotas_ubicacion ON mascotas (UBICACION);
EXPLAIN SELECT * FROM mascotas WHERE UBICACION = 'E02';

-- 2) Resultado: SQL avanzado
CREATE OR REPLACE VIEW v_cursos_sin_alumnos AS
SELECT c.ID_CURSO, c.TITULO
FROM cursos AS c
WHERE NOT EXISTS (SELECT 1 FROM alumnos_cursos AS ac WHERE ac.ID_CURSO = c.ID_CURSO);
SELECT * FROM v_cursos_sin_alumnos;

-- 3) 4 sesiones de marzo de 2009
SELECT * FROM poker
WHERE F_SESION >= '2009-03-01' AND F_SESION < '2009-04-01';

Bibliografía y recursos online

MySQL 8.0 Reference Manual. Using Views, Updatable and Insertable Views y The View WITH CHECK OPTION Clause.
MySQL 8.0 Reference Manual. How MySQL Uses Indexes y Multiple-Column Indexes.
MySQL 8.0 Reference Manual. Optimizing Queries with EXPLAIN y EXPLAIN Output Format.
MySQL Tutorial. MySQL Views y MySQL Index.
W3Schools. MySQL Views y CREATE INDEX.
Winand, M. Use The Index, Luke. Libro online y gratuito sobre índices, disponible en español (versión web de SQL Performance Explained).
Libro: Silva, R. (2023). MySQL Crash Course: A Hands-on Introduction to Database Development. No Starch Press. Cap. 10: Creating Views.
Libro: Grippa, V. M. y Kuzmichev, S. (2021). Learning MySQL (2.ª ed.). O’Reilly Media.

Los recursos en inglés se pueden leer con el traductor del navegador.

Siguiente: Cuestionario del Módulo 5 y luego Módulo 6 · SQL + Inteligencia Artificial.